January 17, 2007 at 2:30 am
Newbie here. Some advice if I may...although I'm sure someone has asked this in the past.
I have 2 new identical servers (spec wise. Win2003, SQL2005 etc) and plan to deploy 1 with the other as backup. Now, currently there will be about 50 small databases each with a different login (this will grow with new dbs along with a new login). I basically want a hot spare ready just incase the primary completely collapses. This means SQL logins will need to be copied automatically as well.
I've looked at replication and database mirroring but will either do the job (clustering is out im afraid)?
January 17, 2007 at 5:24 am
I think you can import the login from the master database and the jobs form the msdb.
January 18, 2007 at 2:19 am
Thanks for that. What method do I use to do that? Will replication copy over the logins, permissions and db itself?
January 19, 2007 at 6:07 am
Mirroring will copy the permissions, etc. You will have to set up the mirroring for each db as it won't automatically mirror a new database when you create it. The logins will not be copied when mirroring, but you can script a job to copy these, there might even be a maintenance plan for this already.
For a true hot-spare with automatic failover when mirroring you will need a 3rd server to act as a witness (I think this one can be sql express). Also the clients might have to support the new database drivers to recognise the mirror and failover automatically without interruption.
Without the witness then you can still use mirroring for a warm-standby, but someone will have to run the failover (this can be scripted), and the clients will need to be pointed to the new ip.
What I've done for a warm-standby is to have a second ip address for the active server. When the server fails just have the standby use this ip address (don't forget to flush the arp tables on the router).
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply