Solved

How to create SQL Server DB alias to match the old server name ?

Posted on 2014-03-30
7
3,772 Views
Last Modified: 2014-04-15
Hi All,

How to create a database alias in SQL Server 2008 so that I can do the database migration from old SQL 2005 box into another SQL Server 2008 and not have to make some changes in the application server itself ?

Old server:
SQL Server 2005 SP4 Standard 32 bit
DB name: SQLDB05
port 1433

New server:
SQL Server 2008 R2 SP1 Enterprise 64 bit cluster
DB Name: SQLCluster01
port 1433

how to make sure the cutover of the database using detach and re-attaching is seamless or transparent to the application server (SharePoint, web application and some other windows application) ?

DNS CName / alias record will be created to point the old box server name into the SQL 2008 box.
0
Comment
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 200 total points
ID: 39964977
Unfortunately, you cannot use Synonyms for databases, so you may not have much option, but to change your code.  That is if you are unable to change the database name.
0
 
LVL 9

Assisted Solution

by:edtechdba
edtechdba earned 100 total points
ID: 39965033
It sounds like the following article may be of interest to you:

Database alias in Microsoft SQL Server

Excerpt from this article: "Make use of Synonyms. Although synonyms cannot be created for databases directly, we can still use it. The idea is that we create a synonym for every object in the database and then stored procedures will refer to those synonyms instead of fully qualified object names."

And here's another option below:

Is it possible to create an alias or synonym for a database?

Excerpt from this article: "You could create a new database of the original name and fill that with synonyms pointing to all the objects in the renamed database though."
0
 
LVL 7

Author Comment

by:Senior IT System Engineer
ID: 39965631
ok, is there any way to configure it in SQL Server ?
I'm sure that if I create the alias at the DNS level, all traffic should be working, but I'm not sure what to do on the new SQL Server 2008 R2 SP1 database to make it work.

So that I do not need to mock around with the SharePoint site database configuration. SharePoint should still use the existing oldDB name to connect, but the DNS will redirect the connection to the new server.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 200 total points
ID: 39965911
Hi,

I've seen this done using DNS entries. That is, a different name - maybe something application specific - is created in the DNS system, and points to the old server. Clients use that to resolve the name to the ip. When the new server comes on-line, simply change the DNS entry and shutdown the old SQL. Anyone having connection issues needs to do a ipconfig /flushdns

HTH
  David
0
 
LVL 7

Author Comment

by:Senior IT System Engineer
ID: 39965947
David, that sounds simple :-) hopefully that the underlying database still can be working successfully.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 200 total points
ID: 39968157
I can understand that you can use DNS for a new server name, but I cannot see how it is going to help you if you are unable to rename the database.
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 200 total points
ID: 39970396
Hi Anthony,

I agree that it wont help with the change of database name. But absent that requirement, it may be an option.

If not documented though, it can be a pain for the dba's as they don't often check out the DNS system.

Regards
  David
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question