| Author |
Topic |
|
sqlserverdbam
Yak Posting Veteran
54 Posts |
 Posted - 2007-07-16 : 18:35:18
|
| I've following requirement from client.Current SQLSERVER VERSION 2005 on windows XP sp2The way it is set up is that they have two DB servers in NY that are the primary systems, one of those is active and the other is passive. They are clustered using Microsoft OS.Then they have a secondary site in CA with one DB server. NY is active and CA is passive with a DNS script controlling the cutover if the primary site(NY) goes down.The client question is how to best move the database changes from the primary system in NY to the secondary system in NY and the secondary site in CA? I beleived we can use either of following technology.1) Database Mirroring2) Log Shipping(Warm Standby Database)3) Replication Please suggest.thanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-07-16 : 18:39:53
|
| Since the servers in NY are clustered, you don't need to worry about the database changes from NYserver1 and NYserver2. All of that moves over when the instance fails to the other node.Use Database Mirroring to sync the NY instance with the CA instance.We have this same setup except our primary servers are in San Diego and secondary servers are in Las Vegas. We have 4-node clusters at both sites with 11 instances. For the production instances, we database mirror from SD to LV.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
sqlserverdbam
Yak Posting Veteran
54 Posts |
Posted - 2007-07-17 : 12:41:10
|
| If we use database mirroring then how to best move the database changes from the primary system in NY(NYSERVER1)to the secondary site in CA? and how to best move the database changes from the primary system in NY(NYSERVER2)to the secondary site in CA? Thanks, |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-07-17 : 12:46:30
|
| Database mirroring takes care of everything between the primary instance and the mirror instance.You don't move changes between NYSERVER1 and NYSERVER2 since the servers are clustered.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
sqlserverdbam
Yak Posting Veteran
54 Posts |
Posted - 2007-07-17 : 12:52:12
|
| Thanks Tara!Do you think any special requirement for Database mirroring? or any suggestions before setting up Database mirroring?thx |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-07-17 : 13:00:17
|
| You'll need to decide which type of database mirroring to use: synchronous or asynchronous. We use asynchronous since our sites are over 300 miles apart. Your sites are even further apart, so you'll probably need to use asynchronous also.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
sqlserverdbam
Yak Posting Veteran
54 Posts |
Posted - 2007-07-17 : 13:20:40
|
| Do you think I need more maintenance work to maintain both site?Please advice,thx |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-07-17 : 13:24:27
|
| I don't understand what you mean.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
sqlserverdbam
Yak Posting Veteran
54 Posts |
Posted - 2007-07-17 : 13:29:28
|
| Do both required more maintenance work?thx |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-07-17 : 13:32:58
|
| You need check mirror status, no daily maintenance (backup, dbcc, reindex and so) on target db since you can't access it. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-07-17 : 13:34:30
|
| Well you should run backup and dbcc checkdb on mirror server on the system databases. You certainly wouldn't want to failover to a system with data corruption.You should maintain all SQL Servers, regardless if they run production or not.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
sqlserverdbam
Yak Posting Veteran
54 Posts |
Posted - 2007-07-17 : 18:23:58
|
| In case of failure of NYSERVER1 and NYSERVER2,does CASERVER1 will take automatically switchover or we need to point site to CASERVER1 manually?thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-07-17 : 18:28:19
|
| You need to read up on database mirroring. What you have described is only available with synchronous mirroring, which is probably going to be a performance problem for you due to how far apart your servers are.Since we are using asynchronous database mirroring, the failovers are manual. We have lots of scripts that were written that just need to be run when a failure or disaster occurs.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
sqlserverdbam
Yak Posting Veteran
54 Posts |
Posted - 2007-07-17 : 19:49:20
|
| If you have sceipt can you pl send it.thanks for your help! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-07-18 : 00:59:55
|
| Just take a look at ALTER DATABASE in BOL to see what you'll need to run to cause a failover. If you are using asynchronous database mirroring, then you'll need two ALTER DATABASE commands instead of one.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
|
High Availability