We’ve all had to move a database. For databases above a small size, this is a painful process. While there are a number of ways that you can move a database without downtime, they all involve a lot of extra work. What if there were something easier?
What’s the Real Problem?
For most people, moving a database is a one time thing – you’re moving to a new server. That’s pretty much it. I spent 5 minutes staring at my ceiling trying to figure out other reasons that most people might move a database from one SQL Server to another.
For some people, though, databases could move on a regular basis. Think about it like this – you host a software as a service (SaaS) application. You’ve got one database per relative unit of work (customer, forum, cat picture collection). These databases can sit idle for months before, suddenly, there’s tremendous activity.
Normally, you wouldn’t make each SQL Server capable of handling peak load for any section of your SaaS. Instead, you would probably have a few servers ready to handle the worst load and move your busiest database over to those servers.
Typically, this is going to involve a small outage and setting up something like database mirroring or log shipping in order to move the database with minimal down time.
SMB to the Rescue!
Microsoft has supported housing SQL Server databases on a file share for a while now. If you’re not familiar with the idea, I recommend checking out the MSDN article Install SQL Server with SMB Fileshare as a Storage Option.
Moving a database with SMB is straight forward – detach the database from the old server, attach the database to the new server, bring the database online. For most of us, this quick change is much preferred to other methods of moving the database.
SMB File Shares and You
Although SMB file shares are a new technology, they’re worth considering for new SQL Servers. SMB file shares make it easy to move databases between SQL Servers. If you want to learn more about using SMB check out the MSDN article on using SMB and SQL Server.
Brent says: If your storage area network (SAN) is using older connectivity methods like 2Gb or 4Gb FC, and your Ethernet is 10Gb, you might even see a performance boost.
12 Comments. Leave new
Does it make sense to use a contained database at that point? IIRC that would solve the login migration work.
I had to go look up what contained databases provide – you’re the first person to mention contained databases in a really long time. It certainly does look like you could use contained databases, provided that you had designed for them up front.
Is “contained databases” one of those features that sounds cool, but hardly anyone uses (like Multiserver Job Administration)?
I have yet to find contained databases at a customer site. I think the barrier to entry is high enough that you’ll only find it in existing applications that are built to leverage some kind of internal cloud.
I read “SMB TO THE RESCUE” as “Super Mario Brothers to the rescue”…
Good article as usual, Jeremiah!
Aww, thanks!
You are right SMB is fantastic, it gives a very high degree of flexibility and good performance! Please be aware that SMB storage is not supported by Management Studio in a cluster, you can’t attach a DB from the gui, But off course a real DBA doesn’t use Gui 🙂
Interesting – both the SQL Server 2012 and 2014 documentation states that this is a supported configuration (see https://msdn.microsoft.com/en-us/library/hh759341.aspx). The AlwaysOn Failover Clustering page also mentions that clusters are awesome because of “support for a broad array of storage solutions, including WSFC cluster disks (iSCSI, Fiber Channel, and so on) and server message block (SMB) file shares.”
Have you encountered something different?
Ohh yes it is supported for the sql instance, also in a cluster. But you can’t attach a database in a cluster setup, when you use the gui is calls a depricated function that checks for a drive letter in the cluster role, and when you use smb you don’t have a drive letter in the cluster role. You get this error: Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.
Sadly the new function, that ms doesn’t use, is more or less a copy of the depricated function.
This hasn’t been fixed in sql 2014 sp1, still an error!
Btw it is the same with restore of a backup. You can’t browse to your backup file, this can give you a hard time if you have many database files.
Ah, I understand now. We should file a connect item about this!
I have a question, Brent said: “Brent says: If your storage area network (SAN) is using older connectivity methods like 2Gb or 4Gb FC, and your Ethernet is 10Gb, you might even see a performance boost.”
I wonder why? So as far as I understand, the files are stored on a SAN and have to be read and written to and from this SAN through a network. How could the configuration of a SMB share for accessing the same files on the same SAN improve performance?
Thanks in advance.