0

I'm working on a legacy SqlServer database with no version control. I've tried importing it into a VS 2017 database project, but it takes more than an hour to load ("your project will be ready after 1200000 operations are completed"), and usually crashes out in less time than it took to load.

Does anyone have any suggestions for a version control system I can try that will cope with real-life databases?

Richard Petheram
  • 805
  • 12
  • 16
  • Are the answers to https://stackoverflow.com/questions/173/versioning-sql-server-database any help to you? – Ed Harper May 23 '19 at 12:40
  • 1
    Software recommendations are off topic for Stack Overflow. If you can make this on topic though it would help tagging the version of SQL Server. As you said it's legacy, I assume you're using an unsupported version of SQL server. – Thom A May 23 '19 at 12:41
  • Take a copy of your database and let this be a baseline. Script your changes to the database and store these scripts in source control. Spawn off a UAT from your live and apply the scripts here before you push them to live. I cannot fathom why you would be making changes to a "legacy" system. – betelgeuce May 23 '19 at 12:46
  • @Ed Harper, yes that is very much the answer I was looking for and failed to find, thank you. – Richard Petheram May 23 '19 at 20:10
  • @betelgeuce, the reason there's no version control is that the main application comes from a third party, and they release things fairly randomly, getting change scripts is a non-starter. Senior developers on the project haven't, historically, used version control. I use the term 'legacy' because the original structure is over 20 years old, and still going strong as a market leader in its field.The changes are for added functionality around the core app – Richard Petheram May 23 '19 at 20:16
  • @Larnu, I'm not so much asking for software recommendations as the methods people use. The version of Sql Server is an issue in that there are several databases within the server instance running at compatibility modes from 2012 to 2017 and all points between. The problem is more about how to deal with SqlServer when the entire schema is out of control. – Richard Petheram May 23 '19 at 20:22
  • Possible duplicate of [Versioning SQL Server database](https://stackoverflow.com/questions/173/versioning-sql-server-database) – Ed Harper May 24 '19 at 09:22

2 Answers2

0

Baseline your Database and call this Version 0.1.0

As you need to make changes to it, like add columns, data etc. Script this and add this to the source control of your choice. Call this file something like: 

Version-0.1.1.sql

As you make more and more changes the amounts of files will be added to. 

Version-0.1.2.sql
Version-0.1.3.sql
Version-0.1.4.sql

Of course you will test these before you deploy to live. As you are working with what is a legacy system I would probably shy away from investment in expensive tools for what is a legacy system in the first place.

To bring a database to a particular version you would run the scripts in the order. Obviously in each script you have failover etc that handles anything that may go wrong within the scripts.

It is a manual process but the best points are it's cheap, easily to understand, does not require much expense and it's a methodical system to manage change.

Note: Obviously deploy scripts to a UAT version before directly on Live.

betelgeuce
  • 837
  • 5
  • 18
0

I have had a lot of success using flyway with both sql server and postgres. It allows you to create numbered versions as betelgeuce described in his answer, but also offers additional protection of ensuring your earlier versions haven't been changed before deploying any new changes

Tom Halson
  • 380
  • 3
  • 12