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

sql server upgrade

I have a database server that is about 4 years old.  we have a bunch of users that point their applications to this server.

the name of the server is    Alpha

We want to upgrade the sql server version from 2012 to 2016.   I think i would like to create a new server and do a clean install of sql server instead of doing an in-place upgrade.

So the new server would be bravo......  (if we do not do an in-place upgrade)
isn't it true that if i get the permissions right on bravo and restore all the databases on bravo.  that i could thereotically change the name of Alpha to lets say Alpha1 and then rename Bravo to Alpha that the users would not need any configuration changes.

Can someone please verify or send me to a document that outlines the best way to do this.
0
jamesmetcalf74
Asked:
jamesmetcalf74
3 Solutions
 
Scott PletcherSenior DBACommented:
Typically you'd want to just have the name "Alpha" route to the Bravo server using DNS or other routing.  Afaik, although I am not a network person at all, the DNS name and the server name don't actually need to match.  The specified name just needs to get you to the correct IP address.
0
 
pcelbaCommented:
Look at SQL Server installation Wizard: https://docs.microsoft.com/en-us/sql/database-engine/install-windows/upgrade-sql-server-using-the-installation-wizard-setup
or at upgrade instructions directly: https://docs.microsoft.com/en-us/sql/database-engine/install-windows/upgrade-sql-server
or view from professionals here: http://www.itprotoday.com/microsoft-sql-server/upgrading-sql-server-2016-part-one

The question is why would you need to upgrade? Do you need to implement some new SQL 2016 features? Remember license costs are higher for SQL 2016, the stability and reliability is at the same level...

If you provide connection strings used by your apps (without sensitive info, of course) then we may tell what has to be changed. To rename SQL Server instance is not feasible as it is derived from SQL Server Service name. And as Scott mentioned, SQL Server has IP address which may be different if you have new hardware or VM.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
As Scott said, you can create a DNS entry called Alpha that points to Bravo. This way who's pointing to Alpha will be rerouted to Bravo without knowing. Only problem with this solution is that people change jobs and roles and some forget things, so in the future if nobody who created this DNS entry is in team or company and they need to perform a new server migration, things can run really bad.

Another solution is to create a new server called Alpha but not connecting it to the network. After the migration done, then shutdown old Alpha and connect new Alpha to the network.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Scott PletcherSenior DBACommented:
Over time you should migrate to a logical name rather than a physical name anyway.  For example, "ProdApp1" rather than "Bravo" or any other actual server name.  Keep the physical names completely separate from the logical/app names.  That allows you to, for example, move an app from one server to another and never worry about changing the apps/connections themselves.
0
 
Daniel_PLDB Expert/ArchitectCommented:
Hi,

I assume we are discussing changing hostnames for default instances of SQL Server installed. After successfully changing hostname in Windows advanced host properties and AD you issue:
--verify you have old name
select * from sys.servers where server_id=0;
--remove old name
sp_dropserver 'old_server_name';
GO
--ad new name
sp_addserver 'new_server_name', 'local';
GO
--restart SQL Server instance
--verify you have new name
select * from sys.servers where server_id=0;

Open in new window


Regards,
Daniel
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
The way I do my migrations is encompassed in multiple posts above.

Create a new server and install SQL Server
Log ship all databases with basic backup/restore (not with built in log shipping)
Do a tail of the log backup on the source and restore the log to the shipped copy ready for cutover.
Then I rename the Windows Server
Reboot
Then do the sp_dropserver, sp_addserver
Restart SQL Server or the server
You should be good to go.

I have done this multiple times and it beats an in-place upgrade.
0
 
jamesmetcalf74Author Commented:
Perfectomundo
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now