Creating new SQL instance as part of server OS upgrade

Question

I have been forced to abandoned my in-place upgrade for a server running windows 2008 r2 to windows 2012 r2 because it just won't upgrade for me. Thus, I am standing up a new VM server with Windows 2012 R2 and have installed SQL 2008 R2 (same version as the current production instance). My thinking is then I can just do a backup or copy of the production databases, import them on the new server, change the new server name/ip to the same as the old one, and everything should go smoothly. My only problem is that I have zero knowledge or experience with SQL so it's going to be a tough slog.

My first question; having installed the new SQL instance and launching Server Management Studio, I notice that it only gives me an option for connecting to a Database Engine of name (local). The original one I need to replace shows it's own name as a server name (for instance, "PROD-MSQL-VM"). It will also let me log in with my domain administration account; the new one with (local) does not, it only lets me log in with a newly created "sa" account. I'm wondering if when I did my original SQL installation I named something incorrectly that it's not accessible from outside itself?

Answer 1

Hi @Sudz28 ,

My thinking is then I can just do a backup or copy of the production databases...

This is a feasible method. But to use the way of backup/restore to migrate the database, usually you need to manually migrate logins, jobs, etc.
You can also use the Data Migration Assistant(DMA) tool to migrate your databases. DMA can migrate multiple databases at once.
https://learn.microsoft.com/en-us/sql/dma/dma-overview?view=sql-server-ver15

The first thing to be clear is that whether the SQL Server instance installed on the new server is the default instance or the named instance should be consistent with the original server. And if it is a named instance installed, you need to keep the instance name consistent.

If the computer name of the new server has not been changed when the SQL Server instance is installed on the new server, then if you change the computer name later, you also need to modify the server name in SQL Server.
https://learn.microsoft.com/en-us/sql/database-engine/install-windows/rename-a-computer-that-hosts-a-stand-alone-instance-of-sql-server?view=sql-server-ver15

In addition, it is best to use a test database for migration testing and connection testing before migrating the production databases.

>My first question;...

When using SSMS to connect to SQL Server, you can specify the server name, authentication, etc. in the "Connect to Server" dialog box.
https://learn.microsoft.com/en-us/sql/ssms/quickstarts/ssms-connect-query-sql-server?view=sql-server-ver15

Answer 2

Thanks for the reply! Maybe I should describe as best I can what our current setup is in case it makes an difference. As best I can tell, we have a single VM server running Windows 2008 R2 and hosting SQL 2008 R2 (v. 10.50.6560).

Checking SSMS, this SQL instance/server is hosting ~26 databases, most of them SharePoint related but a few others that are not. When I look under "Security -> Logins", I see 17 different entries. Several appear to be AD accounts for individuals but I'm assuming all the ones without DOMAIN\username, just a username, are specific accounts only to this SQL instance? I'm not even sure how to figure out which accounts are used by which databases, so my plan was to just re-create all of them and hopefully I can figure out the various passwords for the non-individual-user accounts. A couple of Login accounts look to have downward-facing red arrows next to them, does that mean they're not in use or disabled or something?

My new server VM is Server 2012 R2, on which I have installed the same version of SQL 2008 R2 to keep as many things consistent as I can for the initial move. I'm not sure what you mean when you say "...the SQL Server instance installed on the new server is the default instance or the named instance...". The existing one is named "PROD-MSQL-VM" with an IP of 192.168.15.20 (example number), whereas the new one only appears to be called "(local)" and I'm not sure where I can change this. My plan was to stop the production server, move/migrate/copy all the databases over to the new server, rename and re-ip the new server to match the old prod server, and then bring it back online.

I will check out the links you included to see what I can derive from them!

Answer 3

Hi @Sudz28 ,

>I'm not sure what you mean when you say "...the SQL Server instance installed on the new server is the default instance or the named instance...".

An instance of the Database Engine is a copy of the sqlservr.exe executable that runs as an operating system service. Each instance manages several system databases and one or more user databases. Each computer can run multiple instances of the Database Engine. Applications connect to the instance in order to perform work in a database managed by the instance.

Only one default instance is allowed to be installed on a computer, whose name is MSSQLSERVER. A named instance is one where you specify an instance name when installing the instance.

You can open SQL Server Configuration Manager or Windows Services to view the SQL Server instance installed on the computer.

Answer 4

Hi,

>what is the difference when I connect either to (local) or to PROD2-MSQL-VM

If the ''PROD2-MSQL-VM" is the computer name of the local machine, then local(localhost) is no different from it. These can be used to connect to the local default instance of SQL Server.

If "PROD2-MSQL-VM" is the name of another remote computer, it is possible to connect to the remote default instance of SQL Server from the local SSMS.

Have a look on this doc:
https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/logging-in-to-sql-server?view=sql-server-ver15

>but I don't understand why I see different Login accounts when I log in to one versus the other, etc. I'm sure it comes down to account permissions, but this stuff sure isn't intuitive.

Logins have different server permissions, which determines what they can access. Imagine that ordinary users can view all important information such as logins like an admin account, which is not allowed.

Answer 5

Sorry to dig up an old thread but having completed the migration to the new SQL server back in late May, I finally got around to deleting the original server VM when no issues were encountered after 60 days. NOW, however, it's been realized that there are some Jobs (under SQL Server Agent in the SSMS) and that they've all been failing since shortly before the cutover. Looks deeper into the logs, I'm seeing "Could not load package "Maintenance Plans\Daily Backups and Cleanup because of error 0xC001000A. The specified package could not be loaded from the SQL Server database".

Sure enough, when I look under Management -> Maintenance Plans in SSMS, I see absolutely nothing. My assumption now is that these backup jobs are referring back to something that used to be in the Maintenance Plans folder of the old server but that I didn't know about/never copied over. And now the old server is gone. Is there any way to recreate whatever was in the old Maintenance Plans folders from the last server, or am I SOL at this point and will need to figure out what 'used' to be in there and create all new backup jobs going forward?