SQL SERVER 2012 TO 2017 data mirror

Shri 21

Hi Expert,

How will data mirror SQL Server Application from one server to another . How to check which server machine depended on this server and how will move it from sql server 2012 to sql server 2017 with all dependency, jobs, agents etc together

os windows server 2008 to win server 2016

Shree

Erland Sommarskog 112.7K Reputation points MVP

2020-11-14T10:08:20.107+00:00

Sorry, but your question is not very clear. Can you explain your scenario in more detail so that we can help you?
Dirk Hondong 871 Reputation points

2020-11-14T10:59:01.39+00:00

Hi there,
I go with Erland.
The question is not quite clear.
Do you want to migrate from SQL 2012 to 2017? If that's the case: there's an awesome community tool out there: dbatools
All powershell based that will help you to move from one SQL Server to another including jobs and so on.

Regards
Dirk

Accepted answer

Jeffrey Williams 1,896 Reputation points

2020-11-14T17:18:03.73+00:00
You will not be able to setup 'mirroring' in your current environment. See this document: https://learn.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/upgrade-sql-server-failover-cluster-instance-2008-2012?view=sql-server-ver15

To be able to setup mirroring (availability group) you need to have a cluster and none of the scenarios allow for moving from 2008. This document shows how to perform a rolling upgrade: https://learn.microsoft.com/en-us/windows-server/failover-clustering/cluster-operating-system-rolling-upgrade

This also shows what can/cannot be done: https://sqlha.com/2019/08/15/ags-and-wsfc-os-rolling-upgrades-what-works-and-what-doesnt/

Basically, this leaves you with a backup/restore option as the best and safest route to upgrade. You can reduce the amount of downtime using the following strategy:

Backup database on old system

Restore database to new system with norecovery (can be done several hours before cutover)

Differential backup on old system about 1 hour before cutover

Restore differential backup to new system with norecovery

Restore transaction log backups from old system to new system from differential backup forward

Perform tail-log backup on old system (will take database offline on old system - if not, manually take database offline)

Restore final transaction log to new system

Restore database with recovery

The downtime will be minimal - but you still need to have that downtime. All systems/applications connecting to the old system will need to be updated to connect to the new system. You could shut down the old system and update DNS so the old name now points to the new IP address - or you can setup client aliases - or make changes to the hosts file on each device...

Performing a backup/restore will not lose any data...unless you allow the applications to continue filing data into the old system. This is why you need to take the database offline...it will prevent any additional data from being filed into that database during the migration.

Even if you somehow get mirroring working...the applications connections must be updated to use the new system or the listener, which will require a downtime for the application to insure all connections have been moved.
Please sign in to rate this answer.

1 person found this answer helpful.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

9 additional answers

Shri 21 Reputation points

2020-11-14T15:09:26.77+00:00

Hi Expert,

Yes it is all about migration sql server 2012 -2017. can you give more clarity about this dbatool .is it comes with sql server 2017 and will it cover, jobs,linked server,mail profile, proxies etc

Shree
Please sign in to rate this answer.
Dirk Hondong 871 Reputation points

2020-11-14T15:29:24.647+00:00

Www.dbatools.io
Everything well documented
One of the best community tools
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Shri 21 Reputation points

2020-11-14T15:45:43.847+00:00

Hi Expert,

it would be used for finance company . is it freeware ? paid or come with SQL Server?

I do not think i can use as it is without Microsoft confirms it.

Please tell me about microsoft inbuilt tool. like data mirror - will it work with sql server 2012 to sql server 2017 or any other suggestion
Please sign in to rate this answer.
Dirk Hondong 871 Reputation points

2020-11-14T15:50:16.837+00:00

It's a community tool
Free for all to use
Recommended and recognized by Microsoft
Go to the website and read

Regards
Dirk

Jeffrey Williams 1,896 Reputation points

2020-11-14T16:27:47.997+00:00

There is no simple tool to migrate to a new Edition of SQL Server. There are many ways this can be accomplished - but all of them will require effort and experience. In your case - if I understand correctly - you want to migrate from Windows Server 2008 R2/SQL Server 2012 to a new system running Windows Server 2016/SQL Server 2017.

To do this - you need to create the new server, install SQL Server 2017 - patched to the latest CU. Once you have that built out, you then need to backup/restore the databases from the old system to the new system, copy agent jobs, build maintenance plans, copy logins, build linked servers (if they exist), add any other objects that are needed (extended events, CLR, etc...), enable service broker (if needed), set trustworthy (if needed), etc...

Jeffrey Williams 1,896 Reputation points

2020-11-14T16:28:12.96+00:00

Once you have a working system on 2017 - you can then test and validate the new system before migrating to verify everything is working as expected. Any application issues then need to be addressed, any query issues need to be identified and corrected, compatibility mode of the databases need to be verified (can you run in latest compatibility mode with no issues, or do you need a lower compatibility mode?).

One problem you will run into...if you create the logins on the new system they will not match up with the users in the restored database(s). This is where a tool like dbatools.io come in as that tool can copy the login from the old system and create it with the same SID and password - so when the database is restored the user ties to the login without any additional steps. This can be done without that tool...but you have to write the script to do that yourself.

Jeffrey Williams 1,896 Reputation points

2020-11-14T16:30:49.877+00:00

All of this has to be completed prior to determining how you are going to cutover to the system. You need to look at the requirements for the cutover - how much downtime will you have, do you need to minimize that downtime as much as possible?

Your question about mirroring - I don't think it will work. You cannot setup a cluster for migration across that many versions of the OS - I believe. So that leaves you with backup/restore options. And that also requires application changes to the connection strings to repoint to the new system.

Shri 21 Reputation points

2020-11-14T16:47:59.057+00:00

Hi Expert,

In back up and restore will loose the operational data and can not stop the transactional database as company will loose the money. Mirroring will create database with no full recovery mode and the transactional will happen to Mirror database first and then record will insert to main database and when the main database is stopped then mirror will act as primary database. it this way only small error will come on the page and once it is refreshed then it will work as before

how it is looks any loopfuls or drawbacks in this?

and dbatools like tools are good but since it is not available on Microsoft portal ..unable to use. and company will not allow to download as it is against the policy.. it if is just code and not pointing anywhere and we can copy and paste the code and use it but no exe files
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Shri 21 Reputation points

2020-11-14T16:14:28.76+00:00

Hi,
i can not see any Microsoft recognition on the portal. I afraid can not download.

Expecting other solutions

Shree
Please sign in to rate this answer.
Dirk Hondong 871 Reputation points

2020-11-14T16:22:00.137+00:00

Then not provided by me
Dbatools is the easiest way nowadays
Even MS people recommend this tool
If you're afraid of using it then I think you have to pay for consulting
And in the end the consultant probably will use dbatools.
Get my point?

There is no need to be afraid
Just read all the information provided on the website

That's all I can tell you

I'm working on a reinsurance company and use the tool set on a daily basis.

Regards
Dirk

Jeffrey Williams 1,896 Reputation points

2020-11-14T16:56:56.967+00:00

To repeat - there is no simple tool...you have to script out the logins, agent jobs, linked servers, etc...

Shri 21 Reputation points

2020-11-14T17:05:25.703+00:00

Hi Expert,

My question is can we go ahead with data mirror for this ? we are okay. if it is not simple? will it work between sql server 2012/os 2008 and sql server 17/2016

based on below

In back up and restore will loose the operational data and can not stop the transactional database as company will loose the money. Mirroring will create database with no full recovery mode and the transaction will happen to Mirror database first and then record will insert to main database and when the main database is stopped then mirror will act as primary database. it this way only small error will come on the page and once it is refreshed then it will work as before

how it is looks? any loopfuls or drawbacks in this? it is my approach..is there any way to we need to consider or it is okay proceed

Shree

Dirk Hondong 871 Reputation points

2020-11-14T17:48:45.407+00:00

That's the thing
dbatools does everything for you, script out all logins, jobs, linked servers etc pp
So, yes, it is a simple tool which will do that for you

In that project are many SQL Server and Powershell MVPs involved.

But still: you need to know what you're doing.

Shri 21 Reputation points

2020-11-14T18:09:10.643+00:00

Hi,
does it requires downtime? if it is exe file then i am afraid.. can not dowload ? is it available on gthub?

Shree

Dirk Hondong 871 Reputation points

2020-11-15T10:58:15.183+00:00

you can get it directly from the Microsoft powershell gallery when running install-module dbatools

Again, you need to start reading documentations that are provided here: www.dbatools.io

Also, if you want to migrate something, then you need to prepare a target system like @Jeffrey Williams already pointed out

And yes, at least when you switch your applications from old server to new there will be a little downtime.

Also yes, it is on GitHub
https://github.com/sqlcollaborative/dbatools
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Shri 21 Reputation points

2020-11-14T17:54:20.373+00:00

Hi Expert,

Can you help me in explaining this High availability group in sql databse and how to find it and how it will affect to mirroring in database. Any query that we can find high availability group

To be able to setup mirroring (availability group) you need to have a cluster and none of the scenarios allow for moving from 2008. This document shows how to perform a rolling upgrade: https://learn.microsoft.com/en-us/windows-server/failover-clustering/cluster-operating-system-rolling-upgrade
Please sign in to rate this answer.
Dirk Hondong 871 Reputation points

2020-11-15T11:03:20.37+00:00

As you already figured out: this document does not help you with your upgrade problem moving away from Windows Server 2008

Also: what is your actual setup? You you have an Windows Cluster 2008 right now or not?
If you have a single Box right now without any clustering then it won't help you at all.

Then you have to follow either the way Jeffrey already explained to you
Or you take a look at this article by Glenn Berry from sqlskills

Upgrading SQL Server–Migrating with Database Mirroring
https://www.sqlskills.com/blogs/glenn/upgrading-sql-server-day-13/

Shri 21 Reputation points

2020-11-15T11:47:52.14+00:00

Hi Expert,

My actual set up is windows Server 2008 and sql server 2012 to migrate this to win server 2016 and sql server 2017. How to check if cluster is available in current box? Plus 2 more server on different machine and 2 data mirror server

Shree

Dirk Hondong 871 Reputation points

2020-11-15T12:01:34.463+00:00

So you don't even know how your current environment is setup and if your actual installation is clustered or not?
Or if your actual installation is mirrored?

O, let's start easy:
if you connect with SQL Server Management Studio to your DB server and open the databases folder
Is there any DB with additional infromation in brackets, e.g. (Principal,Synchronized) or similiar?
If none of the DBs have such information then there's no mirroring.

Regarding cluster:
check on your DB server if cluster service is running either through services.msc or maybe cluadmin.msc is availabe. Maybe juat check your processes if Clussvc.exe is running or not.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

SQL SERVER 2012 TO 2017 data mirror

9 additional answers

Your answer