2009-04-27 02:39 PM
We have 3 SQL servers, and I was thinking of only creating one volume for the ‘.mdf’ and ‘.ldf’ of the databases. I'm trying to figure out if SnapDrive for Windows will let me map a drive on each of the SQL server to that same volume. Could for instance detach a database on one SQL server and then re-attach that database with one of the other SQL server (i.e. it sees the same volume)?
2009-04-27 02:40 PM
NTFS isn't a cluster file system, so while technically yes you can do this (although not with SnapDrive), you will end up with data corruption on this drive.
However if this is a Microsoft Cluster, then you can use SnapDrive to create a shared resource disk. This is still technically not seen on both machines as MS Cluster is more of an active/passive system for data drives, only one would be active at any time.
Another thing you could try is have one of the systems as read only. Using SnapDrive you can connect to a snapshot of a LUN. I have several customers using this method to do reporting against a production database without interfering with the production system.
2009-04-27 02:42 PM
Okay, with the extra info on board
I would recommend you want to put this in as a full MS Cluster. This is the best way to get what you want (basically a 3 node cluster with 1 node active). The databases would be fully moveable between the hosts without any issues.
If you can't (due to licensing or some other reason), then I'd recommend you get familiar with the SDCLI (SnapDrive Command Line Interface). You could easily create a script that would map a LUN to a host and reconnect the disks. So if you need to move a database between SQL servers you can do easily from a scripted interface. If that makes sense?
2009-04-27 02:57 PM
More background information
We are planning on using SnapMirror for SQL, but the IT person does not want to create a separate volume (to store the data) for each SQL server. It is my understanding that SnapMirror for SQL requires that use of SnapDrive (i.e. to store the data files). Based on your answers I need to tell the IT person that he will have to deal with multiple volumes.
Is this correct:
· A drive created by SnapDrive can only be used by one computer (baring the read-only case you spoke of)?
· SnapMirror for SQL requires SnapDrive?
2009-04-27 03:04 PM
Best Practice would be to create a separate NetApp Flex-Vol for each area for SnapManager for SQL usage. This would be a minimum of SystemDB, AppDB, AppLogs, SnapInfo.
From a Windows Volume point of view, this can be created within a volume mount point (with some limitations). So basically you can layout the data in a very similar look and feel as you might do if you had a local RAID setup and you were creating several partitions on a disk.
Technically there are ways of reducing the Flex-Vols created on the NetApp side of things by using QTree's, but you might need to read up on this first to work out the best way to accomplish it.
Best bet is to go back to the IT guys and say that SnapManager for SQL is the best way to get online application backups and give the best flexibility. It will require several different volumes (or mount points) for the SQL data. SnapManager for SQL does rely on SnapDrive also.
Back to the original question. If you are using a MS Cluster, then SnapDrive can present a drive to multiple computers. If you are not, then SnapDrive can present disks only to 1 host at a time.
SnapDrive (and SnapManager for SQL) can be used to easily clone and present the databases to multiple hosts, however they will be point in time copies of the data, and not identical copies of the live changing data.
Hope this helps and isn't too confusing?
2009-04-27 03:34 PM
· Do you mean to say that I should create a SystemDB, AppDB, AppLogs, SnapInfo volume for each SQL server (if 3 SQL servers this would be 12 volumes), or would each SQL server use these same 4 volumes?
· Where would I find a current Best practices guide for ‘SnapManger for SQL’
· Where do I find good documentation on SnapManager for SQL – I have only found marketing material. Where do I get the training material (how to setup, configure, best practices, etc)?
2009-04-27 03:42 PM
Yes, you would create these 4 volumes for each SQL instance when using SnapManager for SQL. If you are not using SMfSQL, then you don't need this granuality. So in total, 12 volumes for 3 SQL servers.
I'm terrible at finding best practice guides on the NOW site! I'll see what I can dig out for you though...
As with most things, it does depend on your particular deployment, but hopefully some of the above is useful to you.