Sharepoint 2010 database move from SQL 2005 to SQL 2008, Alias question

Question

I want move my Sharepoint 2010 foundation from SQL 2005 to SQL 2008. Now, from what I read the best practice is to use a SQL alias on the new server pointing to the old server after I attach all the sharepoint DB's. What if I have other DB's on the 2005 instance that will need to remain active. Will this cause major conflicts in my environment? I would have to guess so.

The 2005 instance has a mix match of DB's that will need to remain on that server for different reasons. Or should I just look to start over with my Sharepoint environment?

Thanks in advance.

Answer 1

You actually setup the alias's on the SharePoint servers, not the SQL servers. Run

c:\Windows\System32\cliconfg.exe

on each of your SharePoint servers and then create a new alias with the "Server alias" being the same sql name and instance of sql 2005 and the servername being the name and instance of your sql 2008.

After doing that, make sure all of the DBs are on the SQL 2008 instance and restart all of your SharePoint servers. Assuming everything is setup correctly, SharePoint will now be going to your SQL 2008 box for DBs instead of the SQL 2005 one. All of the DBs on SQL 2005 are unaffected by this change and any other applications still using the SQL 2005 DBs (as long as those apps don't live on the SharePoint boxes) should connect just fine to the SQL 2005 DBs.

The best practice I follow is when installing SharePoint, I got ahead and create sql aliases for my SQL instances I plan on using. The alias names I usually use are SPSQL01, SPSQL02, etc. This way if my sql names change or I need to upgrade SQL, all I need to do is change my aliases on the SharePoint servers to point to the new server.

I hope this makes sense.

Answer 2

What Steve said is correct. I used this method to move from a 32 bit instance of SQL 2005 to a 64 bit edition of 2008 R2 in preperation for the upgrade from MOSS 2007 to Sharepoint 2010. You set up the alias on the Sharepoint server, not the SQL server. It works great. Just keep in mind that it any request made to the old database server will be routed through the alias so make sure you move all of your databases to the new database server.