Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am in the process of deprecating an old SQL Server server and replace it with a new server.

My old SQL Server is a named instance called: SQL\Development My new SQL Server is a default instance called Sandbox

Is there a way on the server side to redirect clients from SQL\Development to Sandbox?

There are hundreds of clients and I am looking for the easiest way to accomplish this task.

share|improve this question

migrated from stackoverflow.com Sep 24 '12 at 14:27

This question came from our site for professional and enthusiast programmers.

1  
Why not rename the old server to something else, then slip the new server into the same hostname/instancename? If you're changing host/instance names, then you'll have to update all your clients. –  Marc B Sep 24 '12 at 14:22
    
    
There's a new server naming convention which is preventing me from bringing down the old server and rename the new server. That was my plan initially - to build the new server and then join it to the domain immediately after the old server has been removed. I figure that the clients connection would change - connections or ODBC connections. I was just a little curious. Thanks for responding. Michael –  Michael Sep 24 '12 at 14:50
    
SQL Server Alias would not work for this scenario. –  Michael Sep 24 '12 at 14:51

2 Answers 2

up vote 4 down vote accepted

We are doing exactly this at the moment in our company - decommisioning servers and moving the databases to a new infrastructure. We use a DNS alias to point to the SQL instance - the only downside is that the SQL instance must be configured to use static ports. Once this is done you can connect to a SQL instance using the following address: servername,port - this way you do not need to know the instance name. Once you have configured your application with to connect with the DNS alias moving your databases is easy - just create a copy of the database and change the IP Address that the DNS Alias points to.

There are a couple of downsides to this approach; firstly, configuring SQL Server to use static ports requires a restart of the SQL Server instance; secondly, all servers that are to take part in the moving of databases must be configured to use the same port (otherwise you would need to reconfigure the client each time).

We primarily use this for DR to be fair (although it is making the migration to new infrastructure easier) and the approach is pretty robust. When failover occurs an automated job kicks off to redirect all of the DNS Aliases to point to the new server. There will be a slight window of failure within the application while the new addresses are replicated across the domain controllers but this tends to be fairly small.

I hope this helps you.

share|improve this answer
1  
Good response and that is the exact approach moving forward. The ServerName/Port will simplify future migration process as clients does not need to know the name of the SQL Server instance. –  Michael Sep 24 '12 at 16:18

I have used DNS C names extensively for similar purposes in the past. For the uninitiated, a C name works like an alias for a server and is configured via the DNS system for your network. One catch is that you need to shut the old server off when putting the C name into production, so all applications need to be migrated at the same time.

BUT: This doesn't work for named instances. I would suggest that you consider using a named instance in order to make the migration easier.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.