Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Switching SQL Server via DNS ?

Posted on 2014-04-24
4
Medium Priority
?
305 Views
Last Modified: 2014-05-30
We have found out that we need to decommission our sql server box. Unfortunately, there is the fear that we may not be able to repoint (change connection strings) on all of our existing applications as some of these may be hardcoded. Would like to stand up a box with the same name,or create an alias.

Would like  to know if anyone has tried to use a dns entry repoint to a new SQL Server. Again, the idea here is we will hopefully not have to touch any code or connection strings. This would most likely be going from Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) to SQL Server 2012.

If anybody could provide a list of steps to do  it would be greatly appreciated.

Thank you in advance,
Jason
0
Comment
Question by:jazzcatone
[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
4 Comments
 
LVL 29

Expert Comment

by:becraig
ID: 40020388
Jason your primary concern here will be having two servers on the network with the same name.

Are you planning to build our server2 with a different name then backup and restore the databases to server2 (users and security principals as well) and then rename server2 to match server1  ?

IF so AD will take care of the DNS repoint once the server name is updated in the AD.

You can also probably indicate the downtime
Rename the old server, build the new server with the current name then proceed to complete.

SQL server Migration checklist:
http://yrushka.com/index.php/database-recovery/sql-server-migration-from-one-server-to-another-detailed-checklist/
0
 

Author Comment

by:jazzcatone
ID: 40020638
Thank you for the checklist. This would most likely be a upgrade in SQL Server version to 2012 as well (in addition to it physically moving) so I'm not sure if we would copy over the system databases like master and msdb. Most likely not.  

   Truth is we haven't really decided on a name yet for the new server. My personal opinion would be to give it the same name as the box we are decommissioning (the old box). Then change dns entries. I am just trying to get a grip on what will be needed.

Jason
0
 
LVL 29

Assisted Solution

by:becraig
becraig earned 1000 total points
ID: 40020657
Then perfect:


Just back up the current DBS (After taking them offline  :) )

Then attach to the new SQL server and take the old one offline delete the computer account in AD and rename the new server to match the old name - ipconfig /registerdns to be sure old entries are gone.

Then do some low level testing to be sure all your principals, views etc match the old DB (Since I am sure some might need to be migrated)


An added step would be DB compatibility mode:
http://stackoverflow.com/questions/10272637/how-to-convert-sql-server-2008-r2-database-to-sql-server-2012

Some additional info:
http://www.codeproject.com/Articles/435703/Migration-from-SQL-server-to
0
 
LVL 1

Accepted Solution

by:
Ravi Kumar Atrey earned 1000 total points
ID: 40087920
Hi,

I assume that you are using your host name entry in your connection string under your application.

You can do one thing, just make another box of SQL server as per your requirements and move your all the dbs there.

Now, just set the same host name of your new SQL box and in DNS records, just update the new IP (your new sql box) under A record of your host name.

e.g.

Your current host name is:: sql.abc.com is pointing to A record:: 10.10.10.1
Now your new sql box's IP is:: 10.10.10.2

So you can go to DNS of sql.abc.com and update A record:: 10.10.10.2

By this way, you don't need to go inside your all applications to change the connection string.

Hope you have got your answer now...

Thanks,
0

Featured Post

Optimum High-Definition Video Viewing and Control

The ATEN VM0404HA 4x4 4K HDMI Matrix Switch supports 4K resolutions of UHD (3840 x 2160) and DCI (4096 x 2160) with refresh rates of 30 Hz (4:4:4) and 60 Hz (4:2:0). It is ideal for applications where the routing of 4K digital signals is required.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
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…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

670 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