Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
deal11deal1
Starting Member
34 Posts |
 Posted - 2011-01-06 : 20:24:51
|
| Hello All,Yesterday my new boss gave me this assignment on SSIS , I am hoping some one of you might have gone through it or has an idea, since I am a junior DBA and new to SSIS any help will me on this will be greatly appreciated.I have a test scenario of DR in the dev boxes in SQL2005 with DB mirroring. Set up a principal and mirror server both with same service account without any witness server with manual fail over when the need arises. Let’s say in over case principal is server A and mirror is server B. (Was feeling pretty good about myself when did that ), but suddenly now my boss wants me to design a SSIS package in the case of DR will look for the databases in server A and failover them over to server B automatically.(without going to actually go to Task->mirror->failover manually). The job will run as needed and the package also needs to read the name of the databases (one or multiple) from a excel file. Guys I hope someone please able to help me in this... … If you already have done something like that or know what to do please help me ASAP… |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-01-06 : 21:12:12
|
If you set up Database mirroring with a witness, if server A is unavailable, it would automatically revert to Server B (In high availability). if you have both servers identical, including SSIS packages, and config file set appropriately..you could have your 1st step of schedule agent jobs 1st check to see if the db is acting as the principal.It sounds like what you could do is have the destination server be an environment variable or runtime variable which can be set based on the value in a DR/Production accessible location. The SSIS packages would simply have to be configured to read in the destination server name from that location.This is not high availability and there no witness?If you don't have a witness, and you need to programmatically determine a failover situation, this could also be done in code..but the script task would have to be set up to attempt a connection, and on failure to one, use the other.The bigger issue will be determining if the DR server is up to date. Absent a witness, having the DR server in-synch with the production server is not arbitrary.If you had a witness, which would make this easier.In the case of DR, without a witness, you would also have to account for synchronizing the DR server.... without a witness, this is likely done most accurately manually so you can restore backups and translogs properly.How does data get from Production to the DR server now?
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
deal11deal1
Starting Member
34 Posts |
Posted - 2011-01-07 : 00:58:23
|
| Thank you for your reply.. yes there is no witness server and I alresdy tired it and they dont want the witness server. second, the failyover situation doesnt need to happen automatically the ssis package or the job will be running by someone but when who ever run the job , the job needs to do the failover ( some fail over script in ssis I guess?).the data from production to dr moves in mirroring and we use mirrironing moniter so see if its runing smothly.hope it helps |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-01-07 : 07:23:14
|
But you have to make sure they are fully synchronized before your failover, be it manually, or via code that someone manually runs.Check through Tara's links here http://weblogs.sqlteam.com/tarad/archive/2008/05/04/60585.aspx
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
|
|
|
|
|
SSIS package to do manual failover in case of DR