SQL ALIAS: Replacing SQL Server in Sharepoint FARM

Hey Techies,

I have come across situations many times where organisations decide to switch thier Sharepoint databases to another SQL Instance, Replace SQL server for different reasons including upgrading to new version.

I tried to Use SQL Alias for such requirement and it worked just fine for me.
My Test configuration:

1.Mac1 - DC+DNS+SQL
2.Mac2 - MOSS WFE and Query,
3.Mac3 - MOSS App and Index Box.

Here I wanted to replace Mac1 with a New Machine Mac1B.

Plan:- As Mac1 had Imporant roles, I decided to proceed one by one starting with AD then DNS and finally SQL . Please Move directly to Step L below incase you are not intrested in AD and DNS transfer.

A. Installed another machine Mac1B,connected it to the Network and joined to the Domain.
B. Configured Mac1B as ADC and Global Catalog Server (GC).
C. Installed DNS on the Mac1B and configured Active DIrectory Integrated Zone on it for the Domain. Configured Mac1B to use itself as Primary DNS Server.
D. Added Mac1B as Secondary DNS server in the Scope Option in DHCP Server (Lying on some other machine)
E. Added GC to Mac1B and removed it from MAC1. Transferred all Master Roles from Mac1 to Mac1B.
F. Removed any references from DNS and AD sites and services for the DC Mac1. Removed Mac1 as Name Server from DNS Zone Settings (_msdcs and Domain)
G. Demoted Mac1 as member Server in Domain.
H. Configured Mac1B as Primary DNS Server.
I. Shutdown /Stop Sharepoint Services on MAc2 and MAc3.
J. Installed SQL Server on Mac1B with same settings as Mac1, Took backup of all the databases from Mac1 (including ConfigDB,Content, SSP, admin content, search etc) and restored them on Mac1B.
K. Uninstalled SQL, DNS from Mac1 and disjoined it from domain. Shutdown the box.

L. Now the Issue:- Sharepoint is configured to use Mac1 as the Database server which now is Mac1B. I have tried renaming the server earlier, which worked well but still not a great Idea.

M. On the sharepoint servers, configure SQL CLient to use ALias as Mac1 for MAc1B.
Steps:
On the Sharepoint server, open command prompt and run cliconf(this is builtin client in windows).
CLick on GENERAL TAB and enable TCP/IP by selecting it under disabled Protocols box and click ENABLE button.
CLick on the ALIAS TAB , CLick on ADD:
Server Alias: Mac1
Network Libraries: TCP/IP
Server Name: Mac1B
Port: depnding on your SQL Installation (default-1433)
CLick Apply.

TEST the connection:

Create a blank text file and change the extension from .txt to .UDL
Double click to Open and click on Provider Tab,
Select "Microsoft OLEDB Provider for SQL Server" and click Next
On Connection tab, Type SQL ALIAS configured above (Mac1 in this case)in the "use data source name" and click refresh.
Use Authentication type
click on "test connection" button and the connection should Succeed. Also, you should see the databases on Mac1B when you expand the dropdown "Select the database on the server".
N. Start Sharepoint services on the sharepoint machines and it shouild start working just fine.

In this case, it worked as Mac1 was removed completely from the netwrk but in situations where companies have thier other applications using database from original DB server, this will not work as the original server is live, so USING A SPECIFIC SQL ALIAS just for SHAREPOINT from the very installation will help which may be used to point to any other server as required. This may help to TEST your DR Implementation as well.

Using SQL Alias for sharepoint is a Good Practice but I have seen some issues with third party Backup Products having problems while trying to backup sharepoint databases, which they should work upon.

Yeah, it was bit tedious last night :)

Thanks!