Protecting SQL Servers using SRM

[esexon]

Hello All,

I would like to know your thoughts around protecting SQL Server VM's using SRM.

Is this something that is supported and is anyone actually doing this?

I understand that SRM will successfully bring the VM online but SQL might come up in recovery mode and we would have to rely on SQL backups to get things back.

Am I correct in my understanding.

Many thanks,

Evan

[OscarDavey]

Yes you can use SRM to replicate the SQL server and the simplest way is to use some replication product and replicate all Datastores to your DR site , and then your users will start complaining about network heavy traffic . so when you create a Microsoft SQL Server database, you must configure it correctly to support SRM , i will explain you how .

You use SQL Server Management Studio to create and configure an SQL Server database for SRM to use and this information provides the general steps that you must perform to configure an SQL Server database for SRM to use.

Procedure

1	Select an authentication mode when you create the database instance. Option Description Windows authentication The database user account must be the same user account that you use to run the SRM service. SQL Authentication Leave the default local system user.
2	Create the SRM database user account.
3	Grant the SRM database user account the bulk insert,  connect, and create table permissions.
4	Create the database schema. The SRM database schema must have the same name as the database user account.
5	Set the SRM  database user as the owner of the SRM  database schema.
6	Set the SRM  database schema as the default schema for the SRM database user. Hope this helped . Best regards

Your Oscar

[TheITHollow · Accepted solution]

I have done it in the past and the SQL server boots as though it had just recovered from an unexpected shutdown.

Basically you are starting up the server with the data from the last replication.

Several things to consider:

     1.     Put your TempDB and page files on a different disk and don't replicate it.  It will chew up your bandwidth for very little reason.  You may need to add a disk on startup at the DR site though so that SQL can create a new TempDB when it needs to.

     2.     Make sure your MDF and LDF are replicated at the same point.  Having these replication on different schedules will cause you problems.

     3.     Consider your SQL maintenance.  Reindexing your database will cause most of the database blocks to change even though the data doesn't.  This will mean a lot of replication traffic.

Hope this helps.

[esexon]

Thanks for the quick responses guys.

TheITHollow - Do you ever have issues during a unplanned DR failover where the SQL server comes up and the databases are in Recovery mode? Do you also rely on having a full SQL backup?

Cheers,

Evan

[TimOudin]

TheITHollow is spot on, with emphasis for consistency on all datastore housing data and log files.  I've done both planned and unplanned recovery of SQL Servers with SRM many times and have not experienced an issue yet, but the server's files must be consistent.  Best thing to do is to test, replicate some SQL Servers and perform failover tests.

I would never forego backups simply because of replication for operational recovery needs.  I would do my best to not replicate backups via typical SAN replication for the same replication traffic reason mentioned earlier.

[TheITHollow]

Thanks Tim,

Yes, SRM is NOT a solution for backups, and should not take the place of them.

You may even consider using log shipping for the back drives to a "dummy" SQL server in your DR environment.

If you need to, you can SRM the system drives of the production sql server to your DR site, and then at failover time, detach drives from your dummy VM and attach them to your Prodution VM.

In some use cases, this may make more sense depending on your maintenance schedule for your SQL environment.

I hope this has been helpful.

[esexon]

Thanks guys.

[gotts]

@THEITHOLLOW

With regard to your solution, step 2, i am going to be using vsphere replication to replicate the entire VM.

My plan is to stop all sql services on the source machine , perform a final sync, then run a test failover and at a later date, assuming all went well perform a planned migration.

Is there some other specific step I need to have our DBA take related to your step 2?

My apologies for such a ignorant question but I just don't know SQL well enough to make this call without some input.

Message was edited by: gotts

[TheITHollow]

Using vSphere replication and replicating the entire VM should take care of you.  Especially if you are running a sync after you power of the VM.

If you're replicating disks on the same VM on different schedules, that will get you into trouble.  But it sounds like you're fine.