September 14, 2015 at 11:34 pm
Comments posted to this topic are about the item Version Control - Just Start Doing It?
September 15, 2015 at 12:55 am
I'm keen to get our team using source control. However we have a couple of things that are making it hard to understand the best way of approaching it. Would appreciate anyone's thoughts.
- we only have licenses for Dev and Prod servers. No developer instances.
- our IT dept will not sign off on Redgate Source Control (which handles the above situation perfectly)
I am looking for something simple for devs to use which basically means we can audit and roll back any changes. Not currently looking for automated builds.
September 15, 2015 at 1:07 am
nick.latocha (9/15/2015)
I'm keen to get our team using source control. However we have a couple of things that are making it hard to understand the best way of approaching it. Would appreciate anyone's thoughts.- we only have licenses for Dev and Prod servers. No developer instances.
- our IT dept will not sign off on Redgate Source Control (which handles the above situation perfectly)
I am looking for something simple for devs to use which basically means we can audit and roll back any changes. Not currently looking for automated builds.
Hi Nick,
It is great that you want to use source control and it doesn't have to have any cost associated with it.
If you do not have any budget for Redgate Source Control then you can use SSDT - you do not have to use the automated builds but it is there if you want to later on.
If you do not already have visual studio you can either use the express version or community if you are eligible (< 1mill usd turnover I think but check the requirements).
For developer instances if you do not have a developer license (50 USD per dev) then you can use SQL Express or localdb, these do not include enterprise features but are typically enough for SQL developers - I have used both in the past and they are just fine.
What I would suggest is that you get a install of SSDT, create a new SSDT project and import your database - have a look at my blog seried on building a CI process for Adventureworks (https://the.agilesql.club/taxonomy/term/34) in particular part 2 "AdventureWorksCI Step 2 From MDF to Dot Sql Files".
If you really don't want to use SSDT then you will just have to build into your developer workflow the idea of scripting out all the changes and manually putting them in .sql files which gets boring pretty quickly 🙂 - however even this is much better than no version control at all.
Hope it helps and feel free to ping me with any questions or for any help!
ed
September 15, 2015 at 1:33 am
Thanks Ed - some interesting reading there. Didn't realise SSDT was so useful! Also the SQL Developer Licences are cheap enough for us to move towards a developer instance model.
Cheers
Nick
September 15, 2015 at 1:39 am
nick.latocha (9/15/2015)
Thanks Ed - some interesting reading there. Didn't realise SSDT was so useful! Also the SQL Developer Licences are cheap enough for us to move towards a developer instance model.Cheers
Nick
Great, good luck!
September 15, 2015 at 2:19 am
The part I always struggle with when trying to implement local developer instances of databases, and have everything integrated via version control/CI is the data. Versioning database structure and keeping everything in sync can be handled using the tools you mentioned, but when it comes to having decent data to use during development to help the process, that's where things start to get tricky.
Obviously it's impractical and a data security risk to maintain a local copy of all data from the live DB, while automatically generated test data can be hard to interpret.
Anyone got any recommendations/tips from experience about how you've dealt with this?
September 15, 2015 at 2:25 am
I've seem two approaches and the second is always easier 🙂
The first is that you have some data generation scripts that can generate test data - these take quite a while to set up and maintenance is a bit of a pain but you get exactly the data you want.
The second is to use the redgate data generator tool, it isn't too expensive and you can save your projects and share them so it becomes really easy to generate test data - however much data you require.
ed
September 15, 2015 at 2:29 am
Thanks Ed.
I've been intrigued by the Redgate test data generation (whether I can get budget for it is another thing :-P) but I'm curious as to how configurable it is? Does it just generate nonsense data, or are you able to configure it so that the data generated is relatively sensible?
September 15, 2015 at 2:34 am
GShenanigan (9/15/2015)
Thanks Ed.I've been intrigued by the Redgate test data generation (whether I can get budget for it is another thing :-P) but I'm curious as to how configurable it is? Does it just generate nonsense data, or are you able to configure it so that the data generated is relatively sensible?
It is really configurable and it is quite sensible, for instance if you have a column first_name it will put in actual first names.
For each column you can specify the type and restrictions on the data, i.e. a random int between 40 and 99 or a sequential int between x and x - or you can write your own code to generate test data.
I would really suggest downloading a trial, for one project I worked on the business case was basically, I can spend 3 days writing test data creation scripts which will be a bit rubbish or we can buy this tool and I can generate good test data in a day.
ed
September 15, 2015 at 3:16 am
Great, thanks Ed, will give it a shot.
September 15, 2015 at 3:44 am
We have struggled with this for a few years now.
We tested out RedGate Source Control with SVN (Subversion) on a few projects and found that we had a few issues with it. We spent more time fixing problems with conflicts and the way in which RedGate works that we soon abandoned it and adopted our own approach (marginally based on what RedGate actually does)
The way redgate tracks changes is through extended properties against each object we do the same now (where every objects has a version number and release date in an extended property attached to it) we use SSDT with VS (as our devs use that anyway) and we store the DB build as part of the software repository so it is branched and merged as part of the same base.
You need to take not that DAC or BAC dont support extended properties, so if you are deploying to Azure it can be a bit problematic how ever fresh deploys are reasonably straight forward from SSDT itself.
SQL Server seem to have started down the route of using DAC to deploy and upgrade databases, however thus far have not included data. You can create and deploy a BAC file with the data, however the features to upgrade aren't as mature as just DAC.
The biggest problem we find is DATA itself, especially when deploying to a live system where customer data also muddies the outcome a little.
In some cases where tables have had to be restructured etc etc it can be a rather messy conversion script.
September 15, 2015 at 3:49 am
JohnJack (9/15/2015)
We have struggled with this for a few years now.We tested out RedGate Source Control with SVN (Subversion) on a few projects and found that we had a few issues with it. We spent more time fixing problems with conflicts and the way in which RedGate works that we soon abandoned it and adopted our own approach (marginally based on what RedGate actually does)
The way redgate tracks changes is through extended properties against each object we do the same now (where every objects has a version number and release date in an extended property attached to it) we use SSDT with VS (as our devs use that anyway) and we store the DB build as part of the software repository so it is branched and merged as part of the same base.
You need to take not that DAC or BAC dont support extended properties, so if you are deploying to Azure it can be a bit problematic how ever fresh deploys are reasonably straight forward from SSDT itself.
SQL Server seem to have started down the route of using DAC to deploy and upgrade databases, however thus far have not included data. You can create and deploy a BAC file with the data, however the features to upgrade aren't as mature as just DAC.
The biggest problem we find is DATA itself, especially when deploying to a live system where customer data also muddies the outcome a little.
In some cases where tables have had to be restructured etc etc it can be a rather messy conversion script.
Hi John - when you talk about data do you mean reference / lookup data (i.e. data required for foreign keys to ensure data integrity) or actual business data?
ed
September 15, 2015 at 4:21 am
Allot of it is config data for our systems.
It's very rare you deploy a completely blank database
We use post deploy scripts in SSDT
September 15, 2015 at 6:36 am
Good reading. I'm all for use of source control.
September 16, 2015 at 4:13 pm
Hi,
That was a great article. I have been doing many home-grown-solutions at various placed I had worked for almost 10 years to get SQL Server objects into source control.
Just Recently I achieved a fool-proof dependable solution.
All this starts with VS 2013 and its new Database Solution and Git.
In broad steps:
1. Integrate VS 2013 to GIT/Stash
2. Create a Database solution and a project within it for each of your databases.
3. Step 2 syncs the database with VS pprojects.
4. Now you can diff the database and VS project and do updates either way.
5. Then you can sync it with Git
6. Now you can create a script using the Publish option in VS which can be used to merge it with a target database. For this I used Flyway which does version control of scripts. But you can do it from within VS itself.
It is a live system now.
I wish I could write an article about this.
But the most important thing is we can do this only with VS 2013 which has introducted the Database project.
thanks
Jambu
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply