In this article I will be discussing the moving of database files within a SQL Server instance. We'll also work through a typical move scenario, looking at the scripts we should use and the meta data available to help us.

Let's first begin with what we do know.

Under SQL Server 2000, altering database paths was all but limited to the TempDB database. You had to detach the user database and then re-attach it, or you could use backup, then restore. Hmm, messy to say the least. 

Under normal operation, once SQL Server has a handle on a database you will not be able to manipulate the files at the OS level. Any attempt to copy the files, etc., will result in the dialog box below.

 

 

To address the first point, thankfully in SQL Server 2005 onwards, this is no longer necessary, and in fact SP_ATTACH_DB has been deprecated and will be removed in a future version of SQL Server. You should now use:

CREATE DATABASE ... FOR ATTACH

To address the second point, in order to release the handle the database engine has on the user database files we merely need to Offline the database. We do not need to stop the SQL Server services.

Let's just re-cap that; we do not need to stop the SQL Server services.

You may issue the following command to Offline the database;

ALTER DATABASE [yourDB] SET OFFLINE

If you have active connections and wish to roll them back and take the database offline you may do so using;

ALTER DATABASE [yourDB] SET OFFLINE WITH ROLLBACK IMMEDIATE

Once the database is Offline you may move and\or rename your database files. Just remember that if you delete or rename the files, the database will not come back online again. When attempting to Online the database you will usually receive an error along the lines of:

 

 

You must first amend the system catalogs to provide the new paths\filenames, this is done using the ALTER DATABASE command passing in the MODIFY FILE parameters as shown in the following query construct:

ALTER DATABASE [yourDB]
  MODIFY FILE ( name=logicalfilename, 
                filename=N'c:\folder1\folder2\adbfile.mdf'
              )

Important Note: When using the T-SQL command above, SQL Server will accept whatever you type and issue in the ALTER DATABASE statement so be careful and check your typing!

For example this would be valid

ALTER DATABASE [yourDB] MODIFY FILE(name=logicalfilename, 

filename=N'c:\MSSQL\DATA\gobbeldygook.dat')

If the path\filename does not exist when the database tries to start you will receive an error!!

 

Moving the Files

 

---

With the above in mind, let's look at how we would achieve the goal of moving a databases disk files to new locations.

Our Scenario

The server drives are filling up quickly and you have been asked by the manager to move the disk files to a new set of drives provided by the Windows administrator. The engineer has created your new file paths for you and retained all NTFS ACLs required for the SQL Server services. The drives\paths supplied are as follows (I am using my C drive but this could easily be G or F or some other drive letter);

C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBA\MSSQL\Data

Before making any changes to the OS files and their locations first check the metadata available to you in the following system catalog: master.sys.master_files.  The important metadata to collect consists of the Logical Filenames and the Physical Names. You may obtain this information using the following query;

SELECT database_id,
       name, 
       physical_name 
 FROM sys.master_files 
 WHERE database_id = DB_ID('SampleServiceCompany2009')

For my database I have the following:

 

 

 

I need to amend these paths from the "C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data" directory to match the new location provided to me. The first task is to construct a set of T-SQL move commands. The ALTER DATABASE ... MODIFY FILE command really only needs 2 parameters for the file move: the files logical name and the physical_name. Both of these are retrieved in the query shown above. The move commands are extremely simple and as shown earlier take the following form;

 

 

Issuing these commands now will update the system catalogs, however, the new paths will not take effect until the database has been restarted (or taken Offline then brought back Online). The following message informs us of this:

 

 

I'll now take the database Offline using the command highlighted below.

 

 

With the database Offline, I may now move the files(and even rename them if I really wanted to).

A word of caution here. It is advisable to copy and paste the files to the new locations. Only when the database comes online successfully would I then remove the old files.

Once you have copied the files to the new locations you would then bring the database Online, this is done using:

ALTER DATABASE [yourDB] SET ONLINE

Shown below are the typical screenshots you will see if the database fails to start. From the information dialog below click the message column and you will see details of the issue.

 

 

The error details show below provide an indication to the issue, the files probably do not exist (in fact that's generally exactly what an OS Error 2 means).

 

 

If the database starts successfully you will see the following

 

 

Help, My Database Won't Start

In every scenario I have encountered whereby a database file move has failed, the issue has been down to a mistyped path and\or filename, resulting in the DBA then getting into a vicious loop trying to correct themselves. Should your database fail to start, don't panic. Perform the following tasks;

• Check the script you used to modify the database file locations, have you got the paths and filenames correct?
• Have your admin check the permissions to the new path for the SQL Server Database Engine service account.
• Query the catalog master.sys.master_files , do the paths\filenames here match what you're expecting?
• If you are unable to complete the move successfully, revert back to the original file paths\names. As you left the files in the source directory simply issue the appropriate ALTER DATABASE ... MODIFY FILE statements and bring the database back online.

Querying master.sys.master_files to obtain the current database file paths

 

 

This is a very easy task to complete providing you pay full attention to the task in hand. If you encounter an error, stop and review what you currently have in place both from the system catalog metadata and the physical OS locations\filenames.

As always, enjoy and if you're still stuck post back and I'll help all I can

 

Anybody who has worked with High availability for SQL server over the years, will be fully aware of Microsoft Cluster Services (Windows 2003) and Windows Server Failover Clusters (Windows 2008). With a clustered environment we are able to fully mitigate failures by utilising 

• multiple network cards for network redundancy
• new Majority node set quorum models (available in Windows server 2003 SP1 onwards) to remove the disk dependency and increase support for multi site clusters.
• multiple computer nodes to negate core node hardware failure (i.e. motherboard, etc)

 

The only real weak link in the chain is the shared storage. There are many ways to achieve redundancy here, but it usually comes at significant cost and it is often difficult to setup and maintain. Of course, as previously mentioined, a fail over cluster only mitigates the server hardware, it does not provide a single or even multiple secondary databases. We have database mirroring in SQL Server versions prior to SQL Server 2012, but these only provide scope for a single nonreadable (with the exception of snapshots), secondary database. You can combine other SQL server availability technologies with fail over clusters, but think of the increased complexity. 

Well, AlwaysOn is the new high availability feature from Microsoft in SQL Server 2012. AlwaysOn takes the old database mirroring concept and creates a whole new level of HA, this encompasses the following;

• No shared storage, each server\instance has localised storage and removes the storage Single Point Of Failure.
• AlwaysOn listener service to accept centralised requests to HA database groups.
• Multiple availability databases instead of the traditional Principal\Mirror scenario.
• Better failover functionality leveraging Microsoft Windows Server Failover Clustering.
• The ability to suspend data movements at the primary level or individual secondary level.
• Support for multiple IP subnets.
• Offload backup and maintenance operations to secondary databases.

 

By offloading backup operations to read only replicas, you can reduce the I\O requirements on your production systems. Multiple secondaries can also provide DR and reporting replicas.

AlwaysOn uses the concept of Availability Groups and these may contain one or more databases that you wish to enable for HA. There are a set of strict pre requesites that must be satisfied before a database may participate in an Availability Group, these are fully detailed at the following link.

http://msdn.microsoft.com/en-us/library/ff878487

 

AlwaysOn still uses the familiar SQL server endpoints for instance to instance communication, you may also create a highly available listener service which you will use to accept incoming connections to the availability group. The listener consists of a unique IP address and a unique virtual network name, this is by far one of the most significant changes in making the groups databases highly available. By providing a centralised access point into the availability group, clients are removed from the issues that are generated during failover of a database. Availability group replicas may also be configured for read-only routing, this allows online readable secondaries to handle read only requests removing concurrency issues from the primary replicas.

 

With AlwaysOn you still also have the traditional synchronous and asynchronous modes that were used in database mirroring. Asynchronous replicas support manual failover only while Synchronous replicas support automatic or manual failover.

 

When attempting to setup AO groups there are a range of options for troubleshooting any errors. You have the AlwaysOn dashboard and also the SQL Server and Windows logs. The wizard driven deployment offers the easiest deployment route, whereas manual deployment requires a lot of manual interraction. Despite this, AO groups are still extremely easy to deploy and configure and offer a level of HA that was previously not available without resorting to complicated levels of feature integration.

 

Creating Your Own AlwaysOn Group 

The availability group may be created and configured via the wizard or manually. Manual interraction is not as streamlined as the wizard, for this reason you may want to use the wizard as your main deployment method. When using the wizard you have the option of scripting the process much in the same way as you can when performing other actions within SQL Server.

 

To setup and configure a virtualised SQL Server 2012 Always On complete system you will need the following

• A physical host machine with your favourite Hypervisor installed (I use VMWare Server 2.0.2).
• Windows 2008 R2 Enterprise
• SQL Server 2012 Enterprise

Optional: You may use Windows 2008 R2 Standard edition for your Domain Controller instead of Enterprise edition.

 

The first tasks are to create the required virtual machines, these are 

1 x Windows 2008 R2 domain controller (Standard or Enterprise)

2 x Windows 2008 R2 cluster nodes

 

If you require any help in creating these, please see my previous guide starting at the following link for help with VMWare server and creating the VMs, etc.

http://www.sqlservercentral.com/articles/virtual/72682/

 

For my VMs I have used the following network configurations 

Node	Network	IP Address
Windows 2008 R2 DC	Public	192.168.0.40
Windows 2008 R2 SQLHANode1	Public	192.168.0.42
	Private	192.168.93.42
Windows 2008 R2 SQLHANode2	Public	192.168.0.43
	Private	192.168.93.43

 

 

 

 

 

 

 

All of the VMs will use local virtual disks, no shared storage, this will replicate the scenario required for the AlwaysOn high availability. Once you have all the VMs created, networked and updated you may now install your stand alone SQL Server instances to each of the 2 nodes. SQLHANODE1 has a named instance "INST1" and SQLHANODE2 has a named instance "INST2". Once the instances have been installed and are operational, proceed to the instructions below.

Create the Windows Server Cluster.

Before going any further it is necessary to install the Failover Cluster feature. Add the fail over cluster feature by opening the Server Manager console and select "Features" and then "Add features" as shown below. Step through the wizard to complete the installation, do this for each of the 2 nodes and close server manager once you have completed.

  

 

Once the feature has been installed to both nodes the cluster installation is performed as follows;

Open the failover cluster manager console and click "Create a cluster". 

 

Click "Next" at the first screen and then at the following screen below, select the servers that will participate in the new cluster and click "Next"

 

Select the option to run the tests and return to the create cluster process, then click "Next"

 

Step through the wizard until you see the screen below, select "Run all tests (recommended)" and click "Next".

The wizard will find all local drives and mark them as non shared disks.

 

You should now be asked to confirm and start the validation, Click "Next" 

 

Once the tests have completed successfully, click "Finish" as shown below. Review the validation report if necessary. 

 

You will then be taken to the "Create Cluster" wizard, supply the virtual networkname and IP and click "Next"

 

Confirm the cluster entry point details and click "Next" to continue at the confirmation screen. 

 

When the wizard completes click "Finish" to create the cluster. 

 

You have now created the base Windows Server Failover Cluster. 

 

Any attempt to access the AlwaysOn features in Management Studio with first completing the configuration will result in the error below. 

 

Open the SQL Server Configuration manager tool and under "SQL Server Services", right click the service you wish to configure for AlwaysOn. You will see the dialog below, check the "Enable AlwaysOn Availability Groups" checkbox and click "OK". You will need to restart the service. 

Deploy A New Availability Group Using the Wizard

Now we have AlwaysOn enabled we can start to configure a new group, righ click "Availability Groups" and select to create a new group. The new availability group wizard starts as shown below, supply your group name and click "Next".

 

 

Select the database(s) to participate in the AlwaysOn group and click "Next". 

 

 

Specify the Availability Replicas and move to the "Endpoints" tab. Check the endpoint configuration and move to the "Backup Preferences" tab. 

 

 

Specify the backup preferences and move to the listener tab. 

 

 

Provide details of the Listener IP, TCP port and network name, then click "Next". 

 

Select your synchronisation preference and click "Next" 

 

Check the validation screen and click "Next". 

 

Review the summary and click "Finish". 

 

 Once the wizard finishes, click close.

 

New Availability Group deployed, configured and synchronised. 

 

 

Manually Failover The Availability Group 

To manually failover the new group, right click the group and select "Failover".

 

Click "Next" at the failover wizard welcome screen. 

 

Select the new Primary replica and click "Next". 

 

Connect to the new Primary replica. 

 

Connected so click "Next". 

 

Review the summary screen and click "Finish". 

 

 

When the wizard finishes click close.

 

 

 Manually Deploy A New Availability Group

If this is the first time you are creating an AlwaysOn availability group on your instance and you are performing the action manually you need to perform the following first.

CREATE ENDPOINT MyEndpoint
    STATE = STARTED
    AS TCP ( LISTENER_PORT = 5022 )
    FOR DATA_MIRRORING (AUTHENTICATION = WINDOWS NEGOTIATE,
       ENCRYPTION = SUPPORTED, ROLE=PARTNER);
GO

GRANT CONNECT ON ENDPOINT::MyEndpoint TO [domain\account]
GO

Failure to create the endpoints first will stop the specified replicas from joining to the availability group.

To manually deploy an Availability Group without using the wizard, right click "Availability Groups" and select "New availability group" then use the following steps. 

Provide a group name and also add in any databases you wish to include. Alongside your Primary replica (the instance you are adding the group on), enter the details of one or more secondary replicas (SQL Server instances you wish to act as secondaries), including the mirroring endpoint URL. Once you have populated these details switch to the "Backup Preferences" page.

 

 Select your backup preferences and any replica backup priorities and click "OK". 

  

Expand the new AO group and right click availability group listeners and select "Add Listener". 

  

Right click the partner node (identified by the red stop icon) and select "Join to availability group". 

  

Connect to the instance 

  

Click "OK" to join the group 

  

The secondary replica has been joined to the AO group. 

  

Open the secondary instance and move to the availability group details. Right click the secondary database (with yellow warning triangle icon) and select "Join to availability group". 

  

Click "OK" to continue, 

 

If successful, the secondary database should change to the familiar Microsoft green facing right arrow icon. 

 

Checking Failover Cluster manager we now see the service and its resources created to support the AlwaysOn Availability group. Upon failover of the primary, the cluster group\application moves to the new primary instances node.

 

 

AlwaysOn availability groups offer a range of possibilities not only to provide redundancy but for offloading backup and reporting I\O too. As always, plan carefully for your requirements and implement the correct solution first time. Building out to test systems not only verifies your configuration but helps you to prepare for your Live deployment.

Picture the scenario, you've moved the enterprises mission critical database from one SQL Server instance to another. You bring the database online, all looks good. Alas, however, you have not created the server logins to provide the necessary database access. The boss wants to know why the application server cannot connect to the database, she's wanting heads to roll unless the application is back up and available in the next 2 minutes!!

 

Luckily, up to and including SQL Server 2012 you have a system stored procedure which handles the mapping of new and existing logins to database users, this procedure is deprecated in future versions of SQL Server but still useful in older versions, How does it work?

Firstly, the stored procedure sp_change_users_login is only used for resolving SQL Server orphaned logins, it takes a number of parameters which differ based upon the action to be performed. The syntax is as follows;

sp_change_users_login [ @Action = ] 'action'
[ , [ @UserNamePattern = ] 'user' ]
[ , [ @LoginName = ] 'login' ]
[ , [ @Password = ] 'password' ]

Valid actions are;

Auto_Fix - this will take the username and password parameters only. If the procedure finds an existing login with the same name the password will be ignored and the user account mapped. If the login does not exist it will be created with the specified parameters.

Report - this action takes no parameters, execute the procedure under your chosen database context and it will report any orphaned users within that database. The accounts are determined as orphaned if the unique SIDs do not match between the server level login and the database user, the name is not used here. You can check this by comparing the catalogs sys.server_principals in the Master database for the server login and sys.database_principals inside each user database for the database user. The report returns the ophaned user name and SID.

Update_One - this action is used to specifically map a database user back to an existing server level login, the login must exist.

  

Back to our scenario, you've contacted the application administrator and he's given you the account password from the web.config file, how do you proceed? We have the password and we know the login name which is the same as our database user, we issue the following query,

exec sp_change_users_login 'Auto_Fix', 'Jon', NULL, 'P@ssw0rd1'

 

The login is supplied as a NULL parameter as this will be created from the user name parameter, If the new login already existed the password parameter would have been ignored. Now, the more astute amongst you would have noticed that the server login and the database user don't have to have the same name, although possibly they should do for clarity. The default for the procedure using Auto_Fix will create a login of the same name, to map the user to a login of a different name, create the login  manually using CREATE LOGIN and then map the acccount. For example, to map the database user Jon to the new server login Bob use the following query,

 exec sp_change_users_login 'Update_One', 'Jon', 'Bob'

 

As an alternative to our scenario, the junior DBA in their panic, has manually created a new server login and is having trouble mapping the login to the database as the user already exists. Fortunately you catch him right before he drops the database user and avoid losing all granted permissions. You do this using the following query

exec sp_change_users_login 'Update_One', 'App_User', 'App_User'

 

Great, the application is back online, the boss declared you a hero and your peers are holding you high on a pedastool. And to think, you very nearly panicked and started dropping users from the database. Next time, you'll script the accounts from the source server and ensure they exist on the target server, hopefully!