Solved

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

Posted on 2014-03-30
7
4,069 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 8

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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
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 8

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

691 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