December 11, 2008 at 6:06 am
Greetings all
We operate 4 SQL Servers:
SQL Server 8 X 2 - About 50 different databases, with 2 main ones (that do alot of over night processing)
SQL Server 9 X 2 - Heavily Loaded, 300-400GB worth of data
I am just throwing ideas about and was just after some opinions in regards to DR.
At the minute there are several things on the table:
1 - ESX/VMWare storing data on SAN (all this would need purchasing from scratch) and replicating the volumes with ESX, if one fails, switch over to a mirrors volume.
2 - Create 1 KING Master DR Server to either:
--------Replicate all 4 servers via transactional replication
--------Log Ship all 4 servers to the King DR Server
3 - Use a 3rd Party backup solution to create backups and restore to the King Server? Could Redgate SQL Backup do something like this?
Basically we just want a cache of live-ish mdf files that we can throw about as needed?
Just to give you an idea of the situation, i am Developer who has ended up spending half my time in DBA, so i dont have 100% of my time to sit and oversee Replication etc. Log Shipping is nice, but we are talking warhehouse, finance and payroll databases here so any log shipping would need to be at the maximum 30 minutes behind the LIVE version... Is this possible on this kind of scale?
What would be my best way to go here? I realise i may have missed some obvious facts here, so please prompt accordingly.
Knock Knock, Who's There?, sp underscore, sp underscore who?
spid1, spid2, spid3....
December 11, 2008 at 6:18 am
jordonpilling (12/11/2008)
Greetings allWe operate 4 SQL Servers:
SQL Server 8 X 2 - About 50 different databases, with 2 main ones (that do alot of over night processing)
SQL Server 9 X 2 - Heavily Loaded, 300-400GB worth of data
I am just throwing ideas about and was just after some opinions in regards to DR.
At the minute there are several things on the table:
1 - ESX/VMWare storing data on SAN (all this would need purchasing from scratch) and replicating the volumes with ESX, if one fails, switch over to a mirrors volume.
2 - Create 1 KING Master DR Server to either:
--------Replicate all 4 servers via transactional replication
--------Log Ship all 4 servers to the King DR Server
3 - Use a 3rd Party backup solution to create backups and restore to the King Server? Could Redgate SQL Backup do something like this?
Basically we just want a cache of live-ish mdf files that we can throw about as needed?
Just to give you an idea of the situation, i am Developer who has ended up spending half my time in DBA, so i dont have 100% of my time to sit and oversee Replication etc. Log Shipping is nice, but we are talking warhehouse, finance and payroll databases here so any log shipping would need to be at the maximum 30 minutes behind the LIVE version... Is this possible on this kind of scale?
What would be my best way to go here? I realise i may have missed some obvious facts here, so please prompt accordingly.
I admit that what I’m about to write, causes many arguments between me and other DBAs, but I don’t see replication as something that has to do with DR. I think that when I build something for DR it shouldn’t be modified each time that I modify the database. With replication each time that I create a new table, I need to create a replication for it. Also there are things that can not be modified by replication. For example if I create a new user, it will not be replicated. Also if I modify the database and create a new file or file group, it will not be done at the replicated database. Log shipping on the other hand has to be defined once and it will include all modifications on the database.
As for your question about the time interval between log backups, you can do it even every minute (but I don't recommend it:-))
By the way I would also look into mirroring and try to decide between mirroring and log shipping (but I don’t have any real experience with mirroring)
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 11, 2008 at 1:43 pm
the dis advantage of log shipping is that the best state you can get is read only, the database will be unavailable when log restores take place. Replication will produce an available database.
The VMWare ESX and SAN solution will provide (depending on the config) high availability and fault tolerance, it costs though.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 12, 2008 at 4:02 pm
Option 4 is to purchase a third party CDC tool that writes to a remote server from your active log file.
Just putting it out there.
~BOT
Craig Outcalt
December 16, 2008 at 9:49 am
This may sound like 'master of the obvious', but here goes:
The more clearly you can define your objectives, the easier it is to choose between alternatives. Is the objective true DR, or is it high availability, or both? What weight does cost have?
Are there hidden agendas - i.e., is this a pretext to acquire fancy equipment for other purposes, as long as the 'stated' objective is also met? In a metaphor, is this the dog wagging the tail, or the tail being wagged?
Assuming your question is straightforward with no hidden thrusts, then you need to be careful about whether you mean mirroring by 'replication' or actually mean 'replication'. Generally, one useful criterion for choosing between the two is the amount of delay that can be accepted between the two instances - mirroring cannot support high latency, whereas replication can. (I am deliberately NOT defining 'high')
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply