Adding the volume on Amazon EC2 was very straightforward. It was just a matter creating a new volume, attaching it to the server, enabling and then formatting the drive. This is covered quite nicely in Attaching an EBS Volume to EC2.
Now with the drive ready, I needed to move the DB files over. The instructions for Moving User Databases are pretty straightforward. However, I ran into some hiccups - I had open connections to the database (from when I was troubleshooting the database size :-) ) and the command to take the database offline simply waits until those connections are closed and you're none the wiser. Also, the new files / locations need to have the right permissions otherwise you can't bring the database back online. So, here are my updated steps.
- Close all connections to your database. You can check for open connections by executing an sp_who2
- Take the database offline by running the following:
ALTER DATABASE [Sync DB] SET OFFLINE
- Move the files to the new location
- Update the new location in the system catalog as follows (make sure you use your own location!)
ALTER DATABASE [Sync DB] MODIFY FILE ( NAME = 'Sync DB', FILENAME = 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Sync DB.mdf' );
ALTER DATABASE [Sync DB] MODIFY FILE ( NAME = 'Sync DB_log', FILENAME = 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Sync DB_log.LDF' );
- Make sure that the database user has the correct permissions to the database files. I just copied the original settings which is giving the SQLServerMSSQLUser group full control access to the DATA folder (and the new database files).
- Bring the database back online by running the following:
ALTER DATABASE [Sync DB] SET ONLINE
- Verify the file change by running the following:
SELECT name, physical_name FROM sys.master_files WHERE sys.master_files.database_id = DB_ID(N'Sync DB')