March 22, 2011 at 9:22 am
I am designing High availability on a SQL instance and what i have decided to do it possible look at creating a SQL 2005 single instance multi node failover cluster, but one of the databases has to be on compatability mode 80. Are there any potential problems with this approach ?
Also, Is it possible to mirror a database which is on compatability mode 80 ?
In a log shipping scenario, after a failover to a secondary DR database, is it possible to switch roles and failback without loosing any data ?
The situation is as follows
Maintain local high availability: This is being met by using SQL failover clusters
Maintain Disaster recovery site: I am looking at Mirroring, Log shipping and SQL replication. SQL replication wouldnt fit the bill because in DR mode, I cannot failback to the publisher without loosing data. This leaves me with log shipping and Mirroring, Mirroring my concern is the compatability level restriction and for log shipping, there are only 2 sites and the shared transaction log folder is a single point of failure, or maybe that could be a cluster disk ?
Any ideas ?
Thanks in advance
March 22, 2011 at 9:02 pm
Database compatability does not have any affect on the way SQL Server stores data. It only influences how the query optimiser handle the T-SQL queries. Hence, you can safely ignore database compatability in all of your potential DR solutions.
Log shipping is intended to handle failover to the secondary and back again. You will need to make sure that all of the log files are applied when switching otherwise you may loose data.
Personally, I do not include replication in my thoughts on DR - it is simply too cumbersome and does not guarentee that you will get all of the data to the subscriber when a failure occurs (that also applies to log shipping and may also apply to database mirroring depending on how these things are configured).
Personally, I would think about using database mirroring. This can be configured so that you loose nothing and can be setup to automatically failover (as can the cluster solution). You will need to handle some things yourself such as logins etc which are in the master database (you cannot mirror system databases).
If you are using database mirroring, you will need to confirm whether your applications can handle it correctly. When a database failsover, there is a break in service - your applications will need to re-connect to the "failover partner". This is similar to what happens when a node in a cluster fails except that you re-connect to the same server and the cluster software deals with connecting you to the correct physical server.
As for your shared txn log folder, you could backup to a local drive and then copy the file to a folder on the secondary server. I believe that this is the way that log shipping is intended to operate (I have not actually used log shipping so this may be inaccurate)
March 24, 2011 at 7:44 am
If you are responsible for your company's database server HA you REALLY shouldn't be asking the types of questions you are asking! PLEASE do yourself and your company a favor and hire a qualified professional to assist you in determining what your needs really are and then coming up with options to achieve those goals and then actually implementing AND testing the plan AND setting up ongoing monitoring. Anything less than that and you are truly setting yourself up to be another 'statistic' that comes back to a forum (or more likely Microsoft support) with a "please help me we are offline . . ." horror story.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 24, 2011 at 8:09 am
On the secondary server, logshipping installs a copy job and a restore job.
You can set the copy job to run as often as you like. If you are worried about loss, then you could maybe run it every minute.
March 24, 2011 at 11:59 pm
I suggest to combine mirroring , log shipping and incremental backups offsite
There are many DR scenarios and first step starts with initial server build. (I had once two drives failed on raid 5 - total loss of server)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply