Sorry Part 2 has taken a little while in coming, I spent the last few days with my girlfriend and driving from MI down to FL. Anyways, you probably don't care about that, you just want to know what I did to move the rest of my databases, so here it goes.
After moving all your content databases over, the next thing to do is to move the config database. Microsoft actually provides a pretty straight forward way to do this. Start by taking a backup of your SharePoint config database and restoring it to your new SQL Server. Once it is restored there you can run stsadm –o setconfigdb. The flags that you can set along with this command allow you to set your newly moved Database as the new config database. This is where the fun starts. Many of you have done this and then reported that you still get errors about access the old SQL Server. A co-worker and I decided to dig into this a little more and found that in Central Admin under Operations -> Servers in Farm, it showed our old SQL Server as having the configuration databases, even though it was completely removed.
This is where the dive into the SharePoint Databases began. I opened up the SharePoint Configuration Databases and started digging and quickly discover a couple of tables that I could change a few rows in and solve all my problems. The first of these was dbo.Objects and the second dbo.Dependancies. In my objects table I still had an entry for my old SQL Server and a GUID tied to that Server. I took the GUID from my old SQL Server and did a query to find what it was the parent of and discovered it was the parent of the databases instance (which has a blank name if you are using the default instance). I took the GUID of the SQL instance, did a query and found that in this database, it was still the parent ID for my Config databases!! I had a good backup of my database so I figured what the heck, let's change this. So, I found the GUID for the instance of my new SQL Server, and executed an update query to change the ParentID GUID of my configuration database to the GUID that matched my new SQL Instance. I then ventured back in Central Admin and found to my excitement, that the Configuration Server listed about in my Servers in Farm was now changed to my new SQL Server. So far so good…but then I looked at my event log.
Still getting errors, so I thought maybe it was because my original SQL Server was still in my Server list in Operations -> Servers in Farm. I tried to remove it, but receive an error from SharePoint about dependencies…another table in my SQL Database. I decided to take a look and see in here. I found by executing queries on the database that there were still dependencies on my old SQL Server, particularly for different things relating to Alerts. Again, I had a backup, so I changed everything in my dependencies table by executing an update query that replace any GUID referencing my old SQL instance with a reference to my new SQL instance. I was still getting errors when trying to remove the SQL server and it dawned on me, that all my SharedServices databases were still listed as having a ParentID of my old SQL Server in my objects table. So, I went back to my SQL Server, did a backup and restore of all my SharedServices databases to my new Server, opened up another query windows and this time updated the ParentID of all the SharedServices databases in my objects table to the GUID for my new SQL Server. After an IIS reset, just to make sure, everything was still working!! Every reference in SharedServices was to my new SQL Server, all my databases were offline on my old SQL Server and everything worked!! I checked my error log and was still getting a few random errors. I restarted the server just to clear anything that had gotten cached somehow before I went any further.
That did it! After the reboot ALL my SharePoint errors in the event log were gone and everything seemed to be running! I had successfully moved over all my SharePoint databases, not lost any information and wasn't receiving any errors. Unfortunately, in the few days that have followed, I've found a few minor problems I'm still trying to work out and figure out if they are related to the move or not. The first is that PerformancePoint doesn't recognize SharePoint as being configured when trying to install the WebParts for PPS. The second is that there seems to be weird permissions issue with IIS as any site on my SharePoint server that isn't anonymous access, NTLM or Kerberos prompts for a username and password, but won't accept even an admin password. This is happening with my Forms Authentication site as well as with a generic .NET site running on the server. If anyone has any ideas I would love to hear them and I'll keep you updated on what I find.
I'm sorry this is a longer post, but hopefully you will all find this helpful and worth the longer post.