Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2014-03-30
7
Medium Priority
?
4,341 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

610 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