?
Solved

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

Posted on 2014-03-30
7
Medium Priority
?
4,193 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 800 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 400 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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 800 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 800 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 800 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

752 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