Losing Temp Tables Between Tasks

I have a control flow setup with 5 tasks.

1. Create Temp Table (Execute SQL)
2. Load Temp Table (Execute SQL)
3. Data Flow Task using the temp table
4. Data Flow Task using the temp table
5. Drop Temp Table (Execute SQL)

Tasks 3 & 4 are currently setup to run parallel/concurrently - meaning they both branch directly off of task #2. They both also lead to task #5. Also, my connection manager is set to retain its connection.

Here's the problem. When executing this flow, task #3 will sometimes fail, other times it will succeed - without me changing anything between runs (I simply end debugging and run it again). When it does fail it is always because it cannot find the temp table. One thing to note is that task #4 (running at the same time as #3 and using the same temp table) always finishes first because it does quite a bit less work. Last thing to note: if I set up task 4 to run after 3 it works everytime, but of course it takes much longer for the total package to execute.

How is it that the task can find the temp table on some runs, but not on others? If this is just quirky behavior that I am going to have to live with for the time being, is there a way to force a task to re-run X number of times before giving up and moving on?

Thanks,
David Martin

Temp tables are only guaranteed to remain in existence as long as they are referenced. Is it possible that the connection is being dropped and reopened by SSIS for some reason between tasks?

Or that there is some time during the processing where Sql Server cannot detect the temp table is still being referrenced, so drops it even if the connection is still open?

Seems like using temp tables is risky if you don't have full and explicit control over their existence. I want to use them also but not if I cannot guarantee they will be there when I need them!

|||

Make sure the "RetainSameConnection" property is set to "True" for the connection. This will allow the Execute SQL tasks to share the connection so the temp table can be accessed.

Frank

sql

Losing config file between child packages and *$##@!- Passwords!

I have a parent package that uses Execute Package Task against three child packages. They are all setup to use the same configuration file, and the packages are all located in the same folder. Purpose of the configuation is to use the same connection strings for each child package.

I removed the path from the config. file specification to facilitate deployment. During debugging, the child packages cannot find the config file when they are run via the parent unless the full path is specified. If run standalone, a child package can find the config. file without the path. As I said, all dtsx filaes and the config file is in the same folder.

Also, the password-removal issue is killing me in development. Everytime I touch the config. file, it removes the password from the connection string. I have "protection level" set to "don't save sensitive" - I'd appreciate help with this too.

thanks

Mark Challen wrote:

I have a parent package that uses Execute Package Task against three child packages. They are all setup to use the same configuration file, and the packages are all located in the same folder. Purpose of the configuation is to use the same connection strings for each child package.

I removed the path from the config. file specification to facilitate deployment. During debugging, the child packages cannot find the config file when they are run via the parent unless the full path is specified. If run standalone, a child package can find the config. file without the path. As I said, all dtsx filaes and the config file is in the same folder.

Also, the password-removal issue is killing me in development. Everytime I touch the config. file, it removes the password from the connection string. I have "protection level" set to "don't save sensitive" - I'd appreciate help with this too.

thanks

When you have it set to "don't save sensitive" that's what's supposed to happen. It's not saving sensitive information.|||As far as your paths go, you have to use absolute paths. So, you might be better off using an environment variable or something to store the path to the config file.

Relative paths have been requested: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=253232|||

Phil Brammer wrote:

When you have it set to "don't save sensitive" that's what's supposed to happen. It's not saving sensitive information.

Do you happen to know which setting will allow the saving of a password so I can work on the packages in development without having to reset the password everytime I touch the configuration? I can understand removing the password when building the solution for deployment, having it auto-magically disappear while staying in the designer it makes no sense.

|||http://msdn2.microsoft.com/en-us/library/ms141747.aspx|||

Mark Challen wrote:

I removed the path from the config. file specification to facilitate deployment. During debugging, the child packages cannot find the config file when they are run via the parent unless the full path is specified. If run standalone, a child package can find the config. file without the path. As I said, all dtsx filaes and the config file is in the same folder.

In my current project I am using that technique; removing the path of the config file from the package configuration and it has worked fine in both ways when called from the aprent package or standalone....

Config file has to be in same folder than the apckages though.

|||

Thanks for the link Phil. Doesn't answer the question, and I'm getting increasingly frustrated by the lack of control with SSIS. Not to get off on a rant, but I personally would rather be responsible for setting security, not having MS dictate that I encrypt everything.

Anyhow, I have found a workaround - once the config. file is established on the parent I set it's "Read-only" property to true in the file system. A low-tech approach, but it seems to work.

The issue of the requirement for an absolute path for the config. file is going to be a showstopper for me (no way am I setting an environment variable; I don't see the thinking behind that - how do I establish the variable in the first place?). I will probably execute my child packages independently from the command line from my own custom wrapper code, passing in my connection strings on the command line. Too bad, I wanted to use the Configuration feature and I spent a lot of time trying to make it work. This seems to be endemic to SSIS (like checkpointing) - a cool feature at first glance, but try to use it and you hit a brick wall. </Rant>

Rafael Salas wrote:

In my current project I am using that technique; removing the path of the config file from the package configuration and it has worked fine in both ways when called from the aprent package or standalone....

Config file has to be in same folder than the apckages though.

Are you sure the package is successfully locating the config file and overriding the connection strings, or is it defaulting back to your original connection manager settings in the child?

|||

Mark Challen wrote:

The issue of the requirement for an absolute path for the config. file is going to be a showstopper for me (no way am I setting an environment variable; I don't see the thinking behind that - how do I establish the variable in the first place?).

You set up the environment variable by way of the system properties in Windows. Once it's set up, you simply reference it thereby making your configurations portable. This is a good way, in my opinion to handle changes across environments that have different configurations (file paths, etc...)

For me, I use SQL Server based configurations and environment variables. I've pretty much got everything setup so that I never have to make changes to a package when promoting it from development into QA and finally into production. Even though file paths may change (commonly C: drive to D: drive, perhaps), the packages continue to work with little interaction. This helps given that we can't promote to production -- another group does that. (Thank you Sarbanes-Oxley!)|||

Mark Challen wrote:

Are you sure the package is successfully locating the config file and overriding the connection strings, or is it defaulting back to your original connection manager settings in the child?

I am 100% sure that it works; actually it is very easy to see if it is not; each time a configuration does not take place; a warning is generated and can be seen in the progress tab of BIDS or in the logging table/file when logging is enable. I rather prefer using indirect configurations; but I have gotten a client where Env variable are not an option....

lookup table relationships best practice

What is the best way to setup relationships between one lookup table
and many other tables. The tables did not have any lookup table
relationships which I am adding. One lookup table is used for same data
in several different places.

To use one lookup tables with several tables, I had to disable "Cascade
Update" and only have "enforce relationships for updates and inserts"
checked.

Any pros/cons?

Thanks in advance.
P"Lookup table" doesn't mean anything in a relational database. There is only
one type of table.

You define relationships between tables with a foreign key. Unfortunately
SQL Server will only allow cascaded updates and deletes on one key per
table. However, in many cases cascaded updates are undesirable and since
it's fairly easy to code the same you probably won't miss this feature most
of the time.

--
David Portas
SQL Server MVP
--

Looking for tool to export data/definitions for later SQL setup.

Any plugable options out there that I can use to export database/table setup
and actual data to xml?
Best yet, would be a .Net class that I could use to control the process for
creating a setup file and then using my own install program to import the
defs/data.
Thanks in Advance,
Donald Adams
"Donald Adams" <BDA_2003@.hotmail.com> wrote in message
news:O0rFG$CIEHA.520@.tk2msftngp13.phx.gbl...
> Any plugable options out there that I can use to export database/table
setup
> and actual data to xml?
> Best yet, would be a .Net class that I could use to control the process
for
> creating a setup file and then using my own install program to import the
> defs/data.
Don't know of any offhand, but you could use SQLXML to export table
definitions from SQL Server (and then you could deserialize them into .Net
classes).
Bryant

Looking for suggestions on specs

Hello,
We are wanting to setup a sql2k5 std cluster and are curious if we have
good specs drawn out.
SAN has 3 arrays
SQL Group has the following resources:
Network name
IP Address
physical disks for data (RAID 5)
physical disk for logs (RAID 1)
Cluster Group has the following resources:
Network Name
IP Address
physical disk for quorum (RAID 1)
MS DTC resource
Is this a valid setup or a better suggestion? Thanks in advance.
Jake
If you can avoid RAID5 and go with RAID10, that would be an improvement. As
for logs, if you have just one DB, then RAID1 is likely sufficient.
However, if you have many DB's with update activity, consider putting the
logs on RAID10.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"Jake Smythe" <someone@.ms.com> wrote in message
news:ecIngdRCHHA.144@.TK2MSFTNGP02.phx.gbl...
Hello,
We are wanting to setup a sql2k5 std cluster and are curious if we have
good specs drawn out.
SAN has 3 arrays
SQL Group has the following resources:
Network name
IP Address
physical disks for data (RAID 5)
physical disk for logs (RAID 1)
Cluster Group has the following resources:
Network Name
IP Address
physical disk for quorum (RAID 1)
MS DTC resource
Is this a valid setup or a better suggestion? Thanks in advance.
Jake
|||Tom,
Thanks for the reply. I was curious if I have my cluster groups setup
correctly. Is it ok to have the quorum and the MS DTC within the same group
that share the same physical disk?
Jake
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:um4CsgRCHHA.4892@.TK2MSFTNGP04.phx.gbl...
> If you can avoid RAID5 and go with RAID10, that would be an improvement.
> As
> for logs, if you have just one DB, then RAID1 is likely sufficient.
> However, if you have many DB's with update activity, consider putting the
> logs on RAID10.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> .
> "Jake Smythe" <someone@.ms.com> wrote in message
> news:ecIngdRCHHA.144@.TK2MSFTNGP02.phx.gbl...
> Hello,
> We are wanting to setup a sql2k5 std cluster and are curious if we have
> good specs drawn out.
> SAN has 3 arrays
> SQL Group has the following resources:
> Network name
> IP Address
> physical disks for data (RAID 5)
> physical disk for logs (RAID 1)
> Cluster Group has the following resources:
> Network Name
> IP Address
> physical disk for quorum (RAID 1)
> MS DTC resource
>
> Is this a valid setup or a better suggestion? Thanks in advance.
> Jake
>
|||It depends. ;-) If you use a lot of distributed transactions, consider
putting the DTC on its own disk. If you use little or no distributed
transactions, then you can put it on the same disk as the quorum.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"Jake Smythe" <someone@.ms.com> wrote in message
news:Oqqz4jRCHHA.3916@.TK2MSFTNGP06.phx.gbl...
Tom,
Thanks for the reply. I was curious if I have my cluster groups setup
correctly. Is it ok to have the quorum and the MS DTC within the same group
that share the same physical disk?
Jake
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:um4CsgRCHHA.4892@.TK2MSFTNGP04.phx.gbl...
> If you can avoid RAID5 and go with RAID10, that would be an improvement.
> As
> for logs, if you have just one DB, then RAID1 is likely sufficient.
> However, if you have many DB's with update activity, consider putting the
> logs on RAID10.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> .
> "Jake Smythe" <someone@.ms.com> wrote in message
> news:ecIngdRCHHA.144@.TK2MSFTNGP02.phx.gbl...
> Hello,
> We are wanting to setup a sql2k5 std cluster and are curious if we have
> good specs drawn out.
> SAN has 3 arrays
> SQL Group has the following resources:
> Network name
> IP Address
> physical disks for data (RAID 5)
> physical disk for logs (RAID 1)
> Cluster Group has the following resources:
> Network Name
> IP Address
> physical disk for quorum (RAID 1)
> MS DTC resource
>
> Is this a valid setup or a better suggestion? Thanks in advance.
> Jake
>
|||As a best practice I never put DTC in the Cluster Group (and therefore not
on the quorum disk), my logic is that when you cluster, you want to omit as
much as possible anything which can terminate the cluster.
Example:
if for some odd reason the DTC fails, this immediately prompt the Cluster
Group to move nodes. If again for some odd reason the DTC fails to com
online on the other node, you might run the risk of loosing your cluster
completely.
In my opinion I would leave the cluster group the cluster group, and create
a seperate group with an IP, NetName, Disk(small) and DTC. This can then
move independently from your cluster group, and in case the DTC has
problems, you are able to work and troubleshoot this without effecting
anything in the cluster group.
As you are in a SAN, it should be possible to present a small disk to this
cluster for DTC purposes.
... my two cents ...
Rgds,
Edwin.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23QFj1oRCHHA.3396@.TK2MSFTNGP02.phx.gbl...
> It depends. ;-) If you use a lot of distributed transactions, consider
> putting the DTC on its own disk. If you use little or no distributed
> transactions, then you can put it on the same disk as the quorum.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> .
> "Jake Smythe" <someone@.ms.com> wrote in message
> news:Oqqz4jRCHHA.3916@.TK2MSFTNGP06.phx.gbl...
> Tom,
> Thanks for the reply. I was curious if I have my cluster groups setup
> correctly. Is it ok to have the quorum and the MS DTC within the same
group[vbcol=seagreen]
> that share the same physical disk?
> Jake
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:um4CsgRCHHA.4892@.TK2MSFTNGP04.phx.gbl...
the[vbcol=seagreen]
have
>
|||Guys,
Thanks for the input I truly appreciate it. One last question, hopefully
, in the documentation I see that the quorum can be a relatively small
size (something like 50 MB) what is your recommendation? I am assuming you
guys have had a box in production so you'd know better than the docs I am
reading. Thanks again for the input.
Jake
"Edwin vMierlo" <EdwinvMierlo@.discussions.microsoft.com> wrote in message
news:eFSfboWCHHA.4024@.TK2MSFTNGP04.phx.gbl...
> As a best practice I never put DTC in the Cluster Group (and therefore not
> on the quorum disk), my logic is that when you cluster, you want to omit
> as
> much as possible anything which can terminate the cluster.
> Example:
> if for some odd reason the DTC fails, this immediately prompt the Cluster
> Group to move nodes. If again for some odd reason the DTC fails to com
> online on the other node, you might run the risk of loosing your cluster
> completely.
> In my opinion I would leave the cluster group the cluster group, and
> create
> a seperate group with an IP, NetName, Disk(small) and DTC. This can then
> move independently from your cluster group, and in case the DTC has
> problems, you are able to work and troubleshoot this without effecting
> anything in the cluster group.
> As you are in a SAN, it should be possible to present a small disk to this
> cluster for DTC purposes.
> ... my two cents ...
> Rgds,
> Edwin.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23QFj1oRCHHA.3396@.TK2MSFTNGP02.phx.gbl...
> group
> the
> have
>
|||"Edwin vMierlo" <EdwinvMierlo@.discussions.microsoft.com> wrote in message
news:eFSfboWCHHA.4024@.TK2MSFTNGP04.phx.gbl...
> As a best practice I never put DTC in the Cluster Group (and therefore not
> on the quorum disk), my logic is that when you cluster, you want to omit
> as
> much as possible anything which can terminate the cluster.
I agree with this to a point. However, the cost of the disk for the MSDTC is
pretty high when you consider other applications in larger clusters that can
cause you to run out of drive letters.
Also, it is wasted resources in my opinion if it isn't ever used.
Since the Cluster team has extensively tested MSDTC in the default cluster
group and have given it their blessing, I don't see any need for it having
its own resources. Except, when the application uses it heavily.

> Example:
> if for some odd reason the DTC fails, this immediately prompt the Cluster
> Group to move nodes. If again for some odd reason the DTC fails to com
> online on the other node, you might run the risk of loosing your cluster
> completely.
Set the Affect Group to disabled for the MSDTC resource and that concern
goes away.
Russ Kaufmann
MVP - Windows Server - Clustering
ClusterHelp.com, a Microsoft Certified Gold Partner
Web http://www.clusterhelp.com
Blog http://msmvps.com/clusterhelp
|||Jake,
Microsoft still (since a long time) recommends 500MB for its quorum, see KB
280345
That recommendation might be larger than actually needed, however with 500MB
I have never seen any problems !
I have alway deployed a quorum disk of 500MB minimum.
Rgds,
Edwin.
"Jake Smythe" <someone@.ms.com> wrote in message
news:OVgH1KZCHHA.4808@.TK2MSFTNGP03.phx.gbl...
> Guys,
> Thanks for the input I truly appreciate it. One last question,
hopefully[vbcol=seagreen]
> , in the documentation I see that the quorum can be a relatively small
> size (something like 50 MB) what is your recommendation? I am assuming you
> guys have had a box in production so you'd know better than the docs I am
> reading. Thanks again for the input.
>
> Jake
>
> "Edwin vMierlo" <EdwinvMierlo@.discussions.microsoft.com> wrote in message
> news:eFSfboWCHHA.4024@.TK2MSFTNGP04.phx.gbl...
not[vbcol=seagreen]
Cluster[vbcol=seagreen]
this[vbcol=seagreen]
consider[vbcol=seagreen]
setup
>
|||Edwin,
Thanks.
Jake
"Edwin vMierlo" <EdwinvMierlo@.discussions.microsoft.com> wrote in message
news:%23yfvWgZCHHA.3540@.TK2MSFTNGP03.phx.gbl...
> Jake,
> Microsoft still (since a long time) recommends 500MB for its quorum, see
> KB
> 280345
> That recommendation might be larger than actually needed, however with
> 500MB
> I have never seen any problems !
> I have alway deployed a quorum disk of 500MB minimum.
> Rgds,
> Edwin.
>
> "Jake Smythe" <someone@.ms.com> wrote in message
> news:OVgH1KZCHHA.4808@.TK2MSFTNGP03.phx.gbl...
> hopefully
> not
> Cluster
> this
> consider
> setup
>
|||Russ,
I have considered all that before I posted, and must say you are absolutely
right, and KB 301600 details the microsft view on this.
Where on one hand they say "leave it in the cluster group" and in the same
article they say "create a sepereate group".
For drive letters, and the "cost" of a small disk, I think each should look
at their own config of SAN and cluster to determine if an additional disk is
feasible.
Then again... if you likely to run out of drive-letters, you might be
pushing a solution with mountpoints already.
In regards to disabling the "affect group" property, you should always do
that if you add the MSDTC into cluster group, however I have seen people
forget this, or even admins who noticed this disabled and enabled it,
because they did not understand the purpose of disabling.
in my opinion, a seperate group for MSDTC will never jeaporadise resources
in the cluster group, while MSDTC in the cluster group might (in some
circumstances)... its about minimising risk... and uptime !
Rgds,
Edwin.
"Russ Kaufmann" <russ@.clusterhelp.com> wrote in message
news:OpK7UNZCHHA.468@.TK2MSFTNGP06.phx.gbl...[vbcol=seagreen]
> "Edwin vMierlo" <EdwinvMierlo@.discussions.microsoft.com> wrote in message
> news:eFSfboWCHHA.4024@.TK2MSFTNGP04.phx.gbl...
not
> I agree with this to a point. However, the cost of the disk for the MSDTC
is
> pretty high when you consider other applications in larger clusters that
can[vbcol=seagreen]
> cause you to run out of drive letters.
> Also, it is wasted resources in my opinion if it isn't ever used.
> Since the Cluster team has extensively tested MSDTC in the default cluster
> group and have given it their blessing, I don't see any need for it having
> its own resources. Except, when the application uses it heavily.
Cluster
> Set the Affect Group to disabled for the MSDTC resource and that concern
> goes away.
>
> --
> Russ Kaufmann
> MVP - Windows Server - Clustering
> ClusterHelp.com, a Microsoft Certified Gold Partner
> Web http://www.clusterhelp.com
> Blog http://msmvps.com/clusterhelp
>

Looking for step by step guide/white paper to install MS SQL Clust

Hello,
I need to install a MS SQL 2000 Cluster server which will include 2 nodes
(setup as standard).
Where can I find a step by step documentation about that and
hardware/software requirements?
Thanks,
Don DiegoHi
There is quite alot of information in books online:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_clustering_2icn.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_clustering_2icn.asp
Not a step by step guide but check out the following and links in Appendix B
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/32bitconsolidation.mspx
John
"dondiego" wrote:
> Hello,
> I need to install a MS SQL 2000 Cluster server which will include 2 nodes
> (setup as standard).
> Where can I find a step by step documentation about that and
> hardware/software requirements?
> Thanks,
> Don Diego|||Thanks John - I'll have a look at the links.
"John Bell" wrote:
> Hi
> There is quite alot of information in books online:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_clustering_2icn.asp
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_clustering_2icn.asp
> Not a step by step guide but check out the following and links in Appendix B
> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/32bitconsolidation.mspx
> John
> "dondiego" wrote:
> > Hello,
> >
> > I need to install a MS SQL 2000 Cluster server which will include 2 nodes
> > (setup as standard).
> > Where can I find a step by step documentation about that and
> > hardware/software requirements?
> > Thanks,
> > Don Diego|||Hi
You may also want to look at
http://www.microsoft.com/windows2000/techinfo/planning/server/clustersteps.asp
John
"dondiego" wrote:
> Thanks John - I'll have a look at the links.
> "John Bell" wrote:
> > Hi
> >
> > There is quite alot of information in books online:
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_clustering_2icn.asp
> >
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_clustering_2icn.asp
> >
> > Not a step by step guide but check out the following and links in Appendix B
> > http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/32bitconsolidation.mspx
> >
> > John
> >
> > "dondiego" wrote:
> >
> > > Hello,
> > >
> > > I need to install a MS SQL 2000 Cluster server which will include 2 nodes
> > > (setup as standard).
> > > Where can I find a step by step documentation about that and
> > > hardware/software requirements?
> > > Thanks,
> > > Don Diego|||Hi
Other things to look at!
http://support.microsoft.com/default.aspx?scid=kb;en-us;259267&sd=tech
John
"John Bell" wrote:
> Hi
> You may also want to look at
> http://www.microsoft.com/windows2000/techinfo/planning/server/clustersteps.asp
> John
> "dondiego" wrote:
> > Thanks John - I'll have a look at the links.
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > There is quite alot of information in books online:
> > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_clustering_2icn.asp
> > >
> > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_clustering_2icn.asp
> > >
> > > Not a step by step guide but check out the following and links in Appendix B
> > > http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/32bitconsolidation.mspx
> > >
> > > John
> > >
> > > "dondiego" wrote:
> > >
> > > > Hello,
> > > >
> > > > I need to install a MS SQL 2000 Cluster server which will include 2 nodes
> > > > (setup as standard).
> > > > Where can I find a step by step documentation about that and
> > > > hardware/software requirements?
> > > > Thanks,
> > > > Don Diego

Looking for some magic

... well, perhaps some sophistication. The setup:

Server A publishes to server B via transactional replication. Server A is to be shut down while some electrical work is done. So we fail over to server B for a couple days. My underpowered brain says to shut down replication, possibly fail over well in advance of the power outage.

Then, when the electrical work is done and server A is restarted: 1) stop work on server B, 2) ?copy? the database on B to A (I suppose after deleting it from A), and 3) redoing the replication from scratch.

Can anyone recommend a better process?

I am very gratefull for any enlightenment.

If you're on SQL Server 2005, you can consider a 2 node Peer to Peer transactional replication. Transactional Replication with queued updating or bidirectional replication may also fit your scenario if you're on SQL Server 2000. For more information, please visit SQL Server Books Online about those topics.

With above replicaiton configuration, there is no need for you to redo replication setup from scratch, just stop/restart replication should be enough

Zhiqiang Feng

This posting is provided "AS IS" with no warranties, and confers no rights.

Looking for references for querying Active Directory (AD) through SQL Server

Does anyone know of any good references (books or web sites) that provide examples of querying AD from SQL Server? I have the database link setup and have done two very simple queries against AD but I would like to see more in-depth examples.

Thanks.

There are a lot of examples out there:

http://codebetter.com/blogs/brendan.tompkins/archive/2003/12/19/4746.aspx
http://support.microsoft.com/kb/299410

http://blogs.msdn.com/ikovalenko/archive/2007/03/22/how-to-avoid-1000-rows-limitation-when-querying-active-directory-ad-from-sql-2005-with-using-custom-code.aspx

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com