MSSQL Application Consistent Snapshots

[SElaschuk]

This is more of an SQL question than a nimble specific one but I'm thinking the brilliant people here may be able to help.

Where should the system database/logs (ie. master.mdf/master.lfg) reside? If for example your SQL server is running on VMware and you have 3 separate LUNs (OS, SQL Data, SQL Log) and SQL Data/Log, all of which are running as VMDK on VMFS (no RDM). User databases reside on the SQL Data/Log LUNs, but System databases still reside in the default OS location. Volume snapshots for SQL Data/Log are set to use VMware integration to snapshot the VM before taking volume snapshots, but the OS volume is not. Is this setup going to cause a problem, as the system databases by default will not quiesce when nimble snapshots are taken?

Thanks!

[marc136]

Happy Friday Stephen.

I am forwarding this to our Awesome Technical Marketing Team here at Nimble Storage.

More to follow.

Thank you.

Marc

[nschoonover110 · Accepted solution]

In-general, you can put them on your database/log volumes, you definitely want to get them off of your C: drive to ensure that they're properly protected along with the data. Many implementations don't see a lot of activity in the system databases, so you can save yourself some management by not putting them on their own volumes.

One exception is TempDB which get busy on systems with large databases and lots of scratch activity like joins, etc.; and for those systems, you may want to put TempDB and it's associated log file on their own volumes. Keep in mind that these are typically 500GB and larger databases, but of course all databases are unique.

My number one rule of thumb is to not try and over-engineer with Nimble, as it does the tuning work for you. So keep it simple and you'll have less to manage and can focus more on the high value work, like tuning your SQL queries. ;-)

Thanks for the question, Stephen!

[david_tan2]

Not sure why so many users setup their SQL Servers with VMDK's on Nimble. You are compromising the performance and flexibility by doing so in my opinion. Perhaps its just easier to manage?

I wrote a brief SQL Server on Nimble storage guide a while back but its aimed at iSCSI direct attached volumes

http://blog.cosiemodo.com/2013/11/05/sql-server-on-nimble-storage--storage-layout.aspx

The same principles still apply for performance profiles and snapshots.

With regards to your setup you should definitely backup your system databases - even once a week. An occasional snapshot of C: should at least cover your arse While it might not be the end of the world losing your master or msdb, having to rebuild system databases is very painful.

[marktheblue45]

I'm with David with his suggestions and comments. The reason that some of the Nimble end users use VMFS Datastores is driven by the Maximum volume limit (256). We have 56 SQL servers at the last count and on a 4 SQL volume model (User/System DB, Log, Temp DB & Temp Log) that would consume 224 volumes leaving just 32 for the other stuff. We use VM Datastores with caching off for anything log related.

[marktheblue45]

In guest iSCSI Volumes allow SAN mode backups with the majority of major backup vendors since the backup appliance can be given access to these volumes over the storage network and their backup agents can quiesce the app in a consistent manner also. In ESX environments it takes the load away from Vcenter and negates the need for LAN based backup..... = fast.