• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4476
  • Last Modified:

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

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
Senior IT System Engineer
Asked:
Senior IT System Engineer
  • 2
  • 2
  • 2
  • +1
5 Solutions
 
Anthony PerkinsCommented:
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
 
edtechdbaCommented:
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
 
Senior IT System EngineerIT ProfessionalAuthor Commented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
David ToddSenior DBACommented:
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
 
Senior IT System EngineerIT ProfessionalAuthor Commented:
David, that sounds simple :-) hopefully that the underlying database still can be working successfully.
0
 
Anthony PerkinsCommented:
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
 
David ToddSenior DBACommented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now