If you are a SharePoint Administrator it’s possible that sometimes you need to move all SharePoint database (i.e. the configuration database, the content databases, the service application databases, etc.) to a completely different SQL Server box.
Well, it’s not something that you do on a daily basis, but you may need to perform this task in at least a couple of circumstances:
- You are revamping the infrastructure and you have a super-powerful, brand new SQL Server cluster
- You are performing a SQL Server consolidation (reducing the number of servers/instances)
- You need to replicate a production environment back into the staging farm (sometimes the opposite is possible as well)
The technique is definitely feasible and is well documented in a number of places (see, for example, this page on Technet: http://technet.microsoft.com/en-us/library/cc512725.aspx).
In a nutshell, you use SQL aliases as a way of indirection.
SharePoint does not resolve the SQL instance by IP address or servername/port, but through a generic name (the alias).
You can modify the alias so that the connection is redirected to another instance, without affecting the SharePoint configuration (a part from the service interruption, of course).
That said (and coming back to the reason for this post) pay a lot of attention to the Edition of your SQL Server Box, even if you have the very same level of upgrades at the source and the target (for example, you move the databases from a SQL Server 2008 R2 + sp1 box to another SQL Server 2008 R2 + sp1 box).
SharePoint does not require the Enteprise Edition of SQL Server, but it leverages Enterpise features if these features are available!!
So if you are trying to move a database from Enterprise to Standard, you may be lucky or not according to whether any enterprise specific configuration has been applied.
Just to make an example, SQL Server Enterprise supports data compression for tables and indexes.
Some SharePoint databases make use of data compression, if it is available (for example, I verified this on the Links Store db used by the Search Service Application on a SP2013 farm).
You can revert to an uncompressed database (table/index), but I guess you will end up completely out of support (you are modifying a database directly).
So… be careful and always perform all verifications beforehand J