Moving the User Profile Service Application Synchronization Database

So, the problems with my Sync DB as I mentioned in a previous post finally caught up to me. Running low on disk space, we decided to add another block of disk space to the server and move the Sync DB over there.

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.

1. Close all connections to your database. You can check for open connections by executing an sp_who2
2. Take the database offline by running the following:
   ALTER DATABASE [Sync DB] SET OFFLINE
3. Move the files to the new location
4. 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' );
5. 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). 
6. Bring the database back online by running the following:
   ALTER DATABASE [Sync DB] SET ONLINE
7. 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')

I still haven't gotten to the root cause of the database file being so large. However, I just needed to buy some time for now.