January 6, 2011 at 7:11 pm
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 with 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 our 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-> click the failover option 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…
January 6, 2011 at 8:42 pm
Forget SSIS. Tell your boss that the easiest way to set up an automatic failover is to have a witness server. It can even be the FREE edition of SQL Server, installed on any old computer!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
January 6, 2011 at 10:30 pm
Thanks for the reply.. I understand it and i know it, unfortunately thats not an option..... any other way ???
January 6, 2011 at 10:39 pm
I agree with Wayne, if you look into requirement of your boss then seems like he/she is trying to reinvent the wheel of automating the failover.
Tell him clearly the process already there and by redesigning it to other way, not going to help him/her win Oscar or noble prize.
Or if he/she still insists ask for good amount of money and time for this work. We are here to support SQL Server not for redesigning the SQL functionality.
Sorry if this reply hurt your boss.
TGIF(Thank GOD its Friday) :-D:-D:-D
----------
Ashish
January 7, 2011 at 12:22 am
qur7 (1/6/2011)
Thanks for the reply.. I understand it and i know it, unfortunately thats not an option..... any other way ???
What is the specific reason for not being an option to use a witness server concept?
Obviously, the server is already there (the one you're running SSIS on). as Wayne mentioned, even if there wouldn't be an installation of SQL Server on that system you could install the free express edition to. Configure it and you're ready to run. Task completed. Max effort: a few hours at most (including download and installation).
If you need to use SSIS and it's urgent you should immediately start writing an RFQ and send it out to some SSIS specialists. Be prepared to spend more than just a little money on it...
As a side note: "ASAP" and "...this is not an option..." are some kind of contradictory in this scenario...
January 7, 2011 at 12:35 am
Of course using a witness, like WayneS proposed, is the more natural and preferred way.
The one thing overlooked many times, is the fact of how many databases you plan to mirror and the consequences of one db failing over with regards to what you want to do with the other mirrored databases of that instance.
When a witness is used, you can just implement a job that is triggered by mirror activation events, that handles the failover for the other databases, so all databases will be active at a single instance.
(I would put such a job in place with any topology when multiple databases are to be mirrored.)
However, since you opt not to go for that topology, I would advise to just check a single database in SSIS and fail that one over if you detect something "unavailable" for one of your mirrored db.
The job activated by the mirroring events will to the rest for you 😉
Actually, no SSIS needed, just schedule a local sqlagent job - on each instance - to check that for you.
Let it check the remote instance if local database are in mirrored recovery state !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 7, 2011 at 2:31 am
Thanks for the help guys,
how to go about adding witness server ...
first install the free express addition on the server ??... can it be installed on the same windows server that already has a MSS enterpise edition?
witness server is going to be add on in the already running mirroring so how to go about it.... it is the same as when u config a new mirroring setup?
Thanks in advance.
January 7, 2011 at 4:12 am
IMO you need to read a little bit on mirroring and the role of a witness ! ( even if it is trivial )
In our typical use cases, we implemented mirroring between two sqlserver instances on different boxes.
We implemented the witness on a separate little box (can be a desktop) - using express edition -
Most important with this is that this witness can be "seen" by both mirroring partners.
We payed attention this witness is not on the same power circuit than the other servers and uses redundant networking, so its availability is not dependant of the others.
If both mirroring servers have a quorum (i.e. can communicate) the witness is not used.
The witness only polls by itself to see the state of the mirroring sessions.
So, in normal situations, you might even shut down the witness without that having any influence on your databases at all.
However, when mirroring failover should occur, the witness server MUST be available.
(that's why we did the previous efford)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 7, 2011 at 9:12 am
Just want to let every one know.. with all of ur inputs I was finally able to convince her to go for witness server..... on express edition.
Thanks for all ur help.
January 7, 2011 at 9:23 am
great to hear.
I have read this book quiet long back but the words are still fresh.....Learn how to say NO.
----------
Ashish
January 7, 2011 at 12:02 pm
Two advantages:
1) the best solution for your company :kiss:
2) the sweet taste of victory for you 😀
ps
don't just make it work, study it so you know how it works.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 21, 2011 at 10:30 am
just give the failover in connection string.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply