SharePoint connection to SQL Server

This is a big <facepalm> since it should have been pretty obvious.

I was trying to install a new SharePoint 2013 farm connecting to a brand new SQL Server 2008 R2. In the SharePoint Products Configuration Wizard, it asks for the SQL connection and kept rejecting my credentials. Checking on the SQL server side, I could not see any connection attempts. I used the following to test with Powershell

$CommandText = "SELECT @@VERSION"
$SqlCommand=New-Object System.Data.SqlClient.SqlCommand($CommandText)
$ConnectionString='Data Source=SQL1;Initial Catalog=master;Integrated Security=SSPI'
$SqlCommand.Connection=New-Object System.Data.SqlClient.SqlConnection($ConnectionString)
$SqlAdapter=New-Object System.Data.SqlClient.SqlDataAdapter($SqlCommand)
$DataSet=New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)

I kept getting all sorts of errors. It turns out the Windows Firewall on my SQL server was not allowing port 1433. Argh - wasted a couple of hours tracking this one down.

Managed Service Accounts not supported by SQL Server

I have been setting up a new Active Directory Domain Services running in Windows Server 2008 R2 functional level. According to the documentation What's New in Services Accounts in Windows Server 2008 and Windows 7: "The managed service account is designed to provide crucial applications such as SQL Server and IIS with the isolation of their own domain accounts, while eliminating the need for an administrator to manually administer the service principal name (SPN) and credentials for these accounts." So I create an account to run the SQL services and install SQL server. However, when I try to pick the service account, my SQL user will not show up in the picker at all. Searching around some more, I come across the Managed Service Accounts Frequently Asked Questions (FAQ). It states: "The use of managed service accounts with Microsoft SQL Server is not supported." Argh!

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.

Generating XML file from SQL SELECT statement

I needed to generate an OPML (XML) file from data from a SQL Server 2008 SELECT statement. It's a bit of a hack job, but this is pretty much what I did:

/* Generate part of the OPML file from the database */
/* Make sure max characters is 8192 */
/* Send results to text or to file */
/* Need to manually merge the rows back together */
SELECT
'<?xml version="1.0" encoding="utf-8"?><opml><head />'
+ (SELECT [col1] as text
      ,[col2] as title
      ,[col3] as type
      ,[col4] as xmlUrl
      ,[col5] as htmlUrl
  FROM [mydb1].[dbo].[mytable1] t1
  inner join [mydb1].[dbo].[mytable2] t2 on t1.id = t2.id
        for xml raw ('outline'), root ('body'))
+ '</opml>'

Reference: Constructing XML Using FOR XML.