| Author |
Topic |
|
Analyzer
Posting Yak Master
115 Posts |
 Posted - 2009-02-10 : 10:30:48
|
| Have been tasked to setup a non SAN DR solution to replace log shipping. Replication Vs Database Mirroring both have pro's & cons when considering the constraints I have which are:- Not all our tables have PK's which rules Replication out - There may be data type changes which rules Replication out- Our environment is subject to regular schema changes which rulesMirroring out (or so my research tells me)Is there a suitable compromise which can handle a real time copy of production, also replicate schema changes and not have all the qualification criteria of Replication? Any guidance would be highly appreciated. |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-02-10 : 13:00:02
|
| Your last statement regarding mirroring is not correct. DDL and DML changes are both copied to the mirror server. I'd be curious to know where you found something that said otherwise.Mike"oh, that monkey is going to pay" |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-10 : 13:05:47
|
quote:
Originally posted by Analyzer
Have been tasked to setup a non SAN DR solution to replace log shipping. Replication Vs Database Mirroring both have pro's & cons when considering the constraints I have which are:- Not all our tables have PK's which rules Replication out - There may be data type changes which rules Replication out- Our environment is subject to regular schema changes which rulesMirroring out (or so my research tells me)Is there a suitable compromise which can handle a real time copy of production, also replicate schema changes and not have all the qualification criteria of Replication? Any guidance would be highly appreciated.
If you have issues with Primary Key,then you can implement Merge Replication. It replicates schema changes as well for SQL 2005. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-02-10 : 13:33:06
|
| We use database mirroring for all of our DR needs. We database mirror probably 50 databases to a site located over 300 miles away. Database mirroring definitely copies schema changes. A mirror is an exact copy of the principal database, might just be latent if you are using asynchronous database mirrorig like us. Latency is very, very minimal though.There are no disadvantages to database mirroring except you can't mirror to more than one database for each principal. I'd only use log shipping if I needed more than one "copy".Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Analyzer
Posting Yak Master
115 Posts |
Posted - 2009-02-11 : 03:20:06
|
| Appreciate everyones responses. I too thought DB mirroring would provide a complete DR solution, however did some digging around and there are mixed reviews on what exactly DB Mirroing provides and why I posted this question. These guys http://www.sunbeltsoftware.com/documents/Double-Take-Why-Protect-SQL-Server-with-DT.pdf amongst others explicitly say Metadata (logins, security, schema etc)are not included in the mirror. I'll run with the mirrored approach and back-test. Many thanks |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-11 : 08:41:51
|
quote:
Originally posted by Analyzer
Appreciate everyones responses. I too thought DB mirroring would provide a complete DR solution, however did some digging around and there are mixed reviews on what exactly DB Mirroing provides and why I posted this question. These guys http://www.sunbeltsoftware.com/documents/Double-Take-Why-Protect-SQL-Server-with-DT.pdf amongst others explicitly say Metadata (logins, security, schema etc)are not included in the mirror. I'll run with the mirrored approach and back-test. Many thanks
Correct. You can't mirror system databases. You need to manually apply on mirror server. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-02-11 : 14:33:24
|
quote:
Originally posted by Analyzer
Appreciate everyones responses. I too thought DB mirroring would provide a complete DR solution, however did some digging around and there are mixed reviews on what exactly DB Mirroing provides and why I posted this question. These guys http://www.sunbeltsoftware.com/documents/Double-Take-Why-Protect-SQL-Server-with-DT.pdf amongst others explicitly say Metadata (logins, security, schema etc)are not included in the mirror. I'll run with the mirrored approach and back-test. Many thanks
That is correct that logins are not mirrored as that stuff is stored in the master database. But schema changes are mirrored as that is included in the principal database. It is very easy to move logins between two servers. Logins shouldn't change too much though, so you should just have a job setup to "mirror" that type of stuff.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
Which DR option best suits schema changes