3

I will be installing a new SQL Server 2014 database system, which is based on our current SQL Server 2005 database system. The new system will be on a completely new infrastructure when we go live, leaving the current infrastructure available for rollback should we encounter any issues.

The business requires that a rollback option be available for 2 weeks minimum. Therefore in the event of a disaster scenario we would be required to switch back to the SQL Server 2005 database system. So the challenge I have is replicating the data upon going live with the SQL Server 2014 database system.

I am looking for a way to replicate the data (not necessarily the schema) from the SQL Server 2014 system to the SQL Server 2005 system.

I am not really a fan of SQL Server Replication and my preference is not to use this. My understanding is that this is not an option for us anyway due to the versions being too far apart, and therefore not supported.

I want to reach out to the community to find out if any tools are available to achieve this type of replication. It may be possible for us to implement a schema change freeze if this helps in finding the right solution.

Please can you advise what options are available to us, if any? Or should we look to develop a bespoke system internally?

Are you aware of any Microsoft partners that might achieve this for us through a solution already developed?

Thanks in advance for any advice.

Joe

Improve this question
6
  • how big are the databases in question after archiving whatever can be archived?
    – Ali Razeghi - AWS
    Jan 4, 2016 at 19:04
  • The database is 400gb. I am unable to archive any data in the current situation.
    – Joe
    Jan 4, 2016 at 19:05
  • Do we have any archive options such as moving to their own filegroups, scripting out archive data, or is there data that can be restored much slower in case of a rollback? Otherwise if you're willing to spend some good money, Dell is now ready to go public with their 'etl' products for SQL Server that used to be exclusive to Oracle. It will let you merge supersets and subsets of data together in other databases. Your issue is the 'rollback' option. Outside of scripting out the data changes I'm not sure how you would revert. Other option would be to use a scaling program to write to both.
    – Ali Razeghi - AWS
    Jan 4, 2016 at 19:06
  • Yeah this is my feelings too. I was hoping there would be some magic software tools available. Thanks
    – Joe
    Jan 4, 2016 at 19:10
  • How many tables in total and how many have high write rates? Also, how much downtime can your business tolerate if you need to rollback. In highly critical systems that have high uptime requirements, it's worth paying for software to handle this or write your own if the team has the expertise. If the system can tolerate some downtime (e.g. weekend) and the amount of changes in the parallel run period is expected to be low/moderate, a simpler solution might be acceptable, especially if the schema is simple. More details will help here.
    – SQLmojoe
    Jan 4, 2016 at 19:23

3 Answers 3

Reset to default
4

Reading your question and thinking a bit more about a possible solution leads me to recommend transactional replication.

Make sure you do implement a change freeze to your schema.

From BOL :

For transactional replication, a Subscriber to a transactional publication can be any version within two versions of the Publisher version. For example, a SQL Server 2000 Publisher can have SQL Server 2008 Subscribers, and a SQL Server 2008 Publisher can have SQL Server 2000 Subscribers.

Based on above truth,

  • Take backup of your current sql server 2005 database and make sure you have instant file initialization enabled on all servers.
  • Freeze all the data and schema changes <-- This is your downtime.
  • Migrate your database from sql server 2005 to 2014 and do all post restore steps.
  • Repoint your application to the new server instance.
  • Set up T-Rep from sql server 2014 to sql server 2008R2 (this will be an intermediate server or instance and can be on the same new server if you want).You care about this step since, you want to be on supported version. Make sure you initialize replication manually using tsql.
  • Now have another layer of replication from sql server 2008 R2 (intermediate) to your sql server 2005 server. Make sure you initialize replication manually using tsql.

Note: If the servers are in same datacenter, then you can even do a snapshot which will be very fast.

Above exercise will be worth, since you want a supported rollback plan. Since your business wants to keep things around for 2 weeks, you should be good once you test above.

Note: I have not tried (as I have not been in a situation wherein I have to replicate data from sql server 2014 to 2005), but it should work (though not supported) theoretically. Give it a try for small database and you will easily be able to gauge the effort required.

Improve this answer
3
  • 1
    This is a great solution IMO and recommended if your acceptable downtime or data loss is very low and they are willing to invest time to do it the 'hard way' properly.
    – Ali Razeghi - AWS
    Jan 4, 2016 at 21:07
  • @AliRazeghi true, since OP's business wants to have a feature to keep the old database synced with the new version. Its a good exercise to do and once you have it setup right, its worth the effort. An alternate path to get data out quickly would be bcp out and bulk insert in, if the OP is willing to take risk :-)
    – Kin Shah
    Jan 4, 2016 at 21:14
  • Yup, I've had to do both in emergencies. The problem I had with Transactional Replication during an emergency was the dang schema requirements. Several tables didn't have Primary Key's and so fourth, which is just horrible for performance and optimization as well as you know. I really like your idea of doing them both though so you don't fall in that situation.
    – Ali Razeghi - AWS
    Jan 4, 2016 at 21:20
1

What you pick will be determined by how long you can be down and how much data loss you can endure.

If you don't have a lot of INSERT data that comes in (updates are fine), you could look at how much data loss is acceptable and script out the difference between the two. You could use a program like SQL Data Compare to compare backups of both and have it script out all the changes which you would have to execute on the old one and have it 'deploy' them. I haven't tested this against backups on different versions but I don't see why it wouldn't work, we'd have to test it thoroughly first.

Even if you have a lot of INSERT data and it grows fast, SQL Data Compare can work quite well if it's on a stable machine with proper resources. You could even do a data compare sync on a 'ready to go hot stand by' system daily or hourly so your data load is less. If your acceptable data loss is 20 minutes, then sync ever 15 minutes, assuming your live database can handle it.

Bypassing locking: You could always log ship the production copy somewhere else and sync to that so you don't block prod reads/writes when reading the data as that would be an issue. You could snapshot and compare off the snapshot so you wouldn't block writes but taking snapshots so often on prod wasn't really how it was designed.

The Cheap Option

Let's say your acceptable data loss is 20 minutes. You take your transaction log backups every 15 mins (and test them of course), and you take your tested nightly fully backup (if it's not tested, how are we ensuring only 20 minutes data loss). Let's say you have a crash and it goes down.

You will then:

-Restore Full backup of 2014 data.

-Restore Transaction Log of 2014.

-After testing your old SQL 2005 backup, Open up RedGate SQL Compare.

-Compare 2014 data to 2005 data.

-Push 2014 data changes to 2005.

If you require faster recover than that, look at the SQL Data Compare command line tools which are very simple and easy to automate, just note the risks with making ad hoc large reads on prod systems listed above. You'd be best to do this off a snapshot off a mirrored pair or off a transaction log shipped pair in between loads. Otherwise you might actually cause an issue where people can't access data!

Expensive Option:

Other options are to create database application proxies or firewalls that will write the data to two different end points. Dell has a ETL tool that was exclusive to Oracle which does SQL Server for this, and so does a up and coming company, ScaleArc. These require considerable more investment, tuning, and maintenance. They were not created for '1 off' solutions.

Improve this answer
1
  • That's a great idea Ali - I think the cheap option would work for us. I'll go and test this out and see how I get on. Thanks very much.
    – Joe
    Jan 4, 2016 at 19:18
-3

Have you looked at perhaps log shipping or differential backups. Basically if the 2005 just needs to be available and on standby these two options will work.

What you need for differential backups is first a full backup from 2014 then restore to 2005 using no recovery then differential backup restores can take place for the data to remain up to date. You will of course always have to restore last full backup before differentials. Let me know if this helps.

Improve this answer
2
  • 2
    ADTJOB, You can't downgrade SQL Server backups. You can only restore backups to a newer version, you cannot restore from a newer version to a older version. Even SQL 2008 R2 backups won't restore on SQL 2008 R1.
    – Ali Razeghi - AWS
    Jan 4, 2016 at 19:23
  • The is plain wrong and should be deleted. A higher version backup cannot be restored to a lower version of sql server.
    – Kin Shah
    Jan 4, 2016 at 21:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.