reconnected my instances again?

Question

Hello I have Server A (Windows server 2008 R2, SQL Server 2008 R2) I need to migrate my database to Server B (Windows Server 2016 with SQL Server 2016)

In my Server A. I have many API, developer projects, connectios, etc...

What is the best advise to do?

Z) reconnected all my conecctions, APIs, developer projects, with the new instance and credentials?

Y) configure Server B, with the same instance, HOSTNAME, and credentials(user=sa, passwd=***)?

This last option(Y), even if I rename the same credentials, hostname and instance my Server B. I need to reconnected all?

Answer 1

As they say, it depends.

Many people would do Y. That is, install the new server as the old server and cut over. This will require some downtime, though, since you are likely to run into problems, if you have two machines on the network with the same name. So you would have to shut down the old server before you copy. Or, well, you could rename the server once you are satisfied. This requires one extra step in SQL Server:

EXEC sp_dropserver OLDNAME
EXEC sp_addserver NEWNAME, 'local'
-- Restart SQL Server after this.

However, there is one thing in your post, which makes me think that you should go for Z. Your post suggests that applications are connecting with sa. That is very bad, and that is something you would need to fix if this is the case. The server upgrade could serve as a kick in the rear parts to do that action. And I should hasten to add that connection should not be by any other sysadmin account. Applications should connect only with permissions to run stored procedures and possibly read/write data in tables.

Answer 2

I would do a multi-phase update.

• Create a DNS logical CName for the database server
• Add new service accounts to old database
• Point the CName the old server
• Update the connection settings to use the CName and service accounts
• Deploy connectisettings
• Build and test new server
• On go live, change the CName to the new server and shutdown the old server
• Computers that connect may need to do a DNS cache flush

This make the next upgrade easier. After all, SQL 2016 is on extended support at this time. won't be long before you need to update again.