May 16, 2012 at 7:59 am
Hi,
Just started a new job. They have a SQL server local and a DR SQL server in another state. both boxes are SQL2005.
This is an accounting firm and they do end of month batch type of operations. So at the end of a bliing cycle there could be huge numbers of transactions all at once.
Will transaction replication be able to handle this batch type of transactions? The pip between the two locations is a fat one with low ping times.
What kind of tests do I need to preform prior to setting up transactional replication? (that's assuming transactional replication is best..??)
If we have a hurricane and have to shut down the server here, will that break the remote DR server?
May 16, 2012 at 8:37 am
you say the other server in another state is for DR.
why would you use replication? database mirroring could provide DR for you across the WAN.
do users in the DR state need to read the data?
May 16, 2012 at 8:42 am
Geoff A (5/16/2012)
you say the other server in another state is for DR.why would you use replication? database mirroring could provide DR for you across the WAN.
do users in the DR state need to read the data?
+1
mirroring is by far a better option if you are using this for DR
MVDBA
May 16, 2012 at 9:26 am
Yes, our users would be re-directed to the server in the other state when there is a chance of a hurricane (florida).
Do I still want to look at Db mirroring?
May 16, 2012 at 9:30 am
krypto69 (5/16/2012)
Yes, our users would be re-directed to the server in the other state when there is a chance of a hurricane (florida).Do I still want to look at Db mirroring?
absolutely - with mirroring you just bring the mirror online when you have an issue. then when you want it back in the original place you just fail it back
with replication you would have to re-create all of the replication on the secondary server and issue a snapshot back to your original server to get back where you were - absolutely forget replication for this
and if your application can make use of the mirroring parameters in the connectionstring then you don't have to redirect your clients - if they can't see the primary they will head for the secondary
the mirror can be made available for read only access using snapshots (if you have enterprisse edition) when you are still running the primary, but since this is for DR would stay away from that
MVDBA
May 16, 2012 at 9:30 am
Thanks Mike..
So (just so I'm clear) I would only have read only access if there was a failure on the primary DB?
If it's read only access, that wouldn't work because the users would need to be able to update the DB.
May 16, 2012 at 9:39 am
when you failover because of a hurricane, the recovery database comes online.
the app they use to connect to the database would have to be given the new instance name to connect.
May 16, 2012 at 9:43 am
Oh sorry...I was a little confused..
So during a hurricane the mirror would become primary..do I have to enable this..or would it failover automatically once I take the primary 'Offline'?
And then re-direct clients? correct?
I'm stuck with standard edition on both ends.
May 16, 2012 at 9:46 am
with standard edition, you would have to "failover" manually.
the connection strings that the app uses would either need to be changed then or you can make use of the feature with some applications to define a "failover partner".
then the app will just 'automatically' connect when the failover occurs.
May 16, 2012 at 9:49 am
does mirroring require primary keys?
May 16, 2012 at 9:50 am
you should read the MS documentation
http://msdn.microsoft.com/en-us/library/ms189852.aspx
look at the article and take note of the configurations involving witnesses
also look at high safety mode and high performance mode - i think you will most likely want performance mode
MVDBA
May 16, 2012 at 9:51 am
no
May 16, 2012 at 1:23 pm
Thanks everyone for your help
Most appreciated.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply