Hello all, we are looking into a T-SQL source control management tool, akin to RedGate's Source Control application. We are in the process of acquiring company-wide licenses for DevArt's dbForge and would like to find out whether there is something similar in DevArt's product offering. I've noticed that there was a Snapshot versioning version in Schema Compare, but I was looking for a more object-specific tool, where I could specifically check out a Stored Procedure or TVP to my working server, make changes and then check it back in to SVN/TFS. I wouldn't want to version the entire schema snapshot every time for that--seems somewhat wasteful.
Are there any plans at DevArt to develop something similar or does something like this already exist? It'd be ideal if it could plug in to SSMS for a single point of management.
Is there a third-party tool you folks could recommend?
T-SQL Source Control Question
Thank you for heads-up on this. Am I to assume that you will post a sticky notification to the forum once the roadmap is firmed up? Part of this question lies with the fact that we are in the process of purchasing DevArt licenses and need to take this into account. If you need more details, feel free to PM me. Thank you in advance.
Let me share my experience, analysis I have made thus far on this subject, and I hope it'll be somewhat useful for your purposes.
I see that your current implementation of database schema versioning as implemented in the SQL Schema Compare product underlines the concept of "snapshot" versioning. So, you take a snapshot of the schema and version that as a monolithic entity. That's a good approach and is useful in many scenarios where a database developer or developers want to achieve versioning of the entire database regardless of what changes were made. So, if you and I were to work on a project together, we can change one object or a thousand objects as a part of a given commit--a single snapshot will cover it and then it can be brought into source control system and considered a single commit.
My issue however, is a bit different. We have development simultaneously happening in C#/VB.NET and T-SQL. Different developers are making changes to source code in those two distinct environments, but the resulting codebase needs to be in sync. So, if one developer changes 10 stored procedures and commits them all in the same snapshot, but only 3 of stored procedures will need to go to production for this release, in order to match a set of VB.NET web pages for instance, then I'm stuck with rolling out 7 extra SPs that are absolutely useless and can even break something. Sure, I can use the Schema Compare tool and select only what I need, but now the source control commit is not atomic--some of it is used for the build and some of it is not. That makes it hard to keep track of things, especially if I need to keep my VB.NET work in sync with my T-SQL work on a build level.
Instead, I'd like to view all T-SQL objects as individual items and be able to check them in and out as I see fit. In fact, why should a table or stored procedure be different from a C# source file? Just like I check out C# files to a local working directory, I should be able to check out T-SQL objects to a local "working server"--it could be an Express edition in case of MS SQL, for instance. Other DBMSes are similar. I'd like to be able to right click on something in SSMS or a standalone tool, and check-out an object, make my changes, and then check them in, together with an appropriate commit message.
Then, developers are free to work on whatever objects they see fit--during the build cycle the proper objects and the proper versions will be selected, especially if there is integration between build tool, change control tool and source control tool (such as Trac/SVN, for instance).
In my example above, only the 3 SPs will be picked up by the build process. This allows for a much more fine-grained control over the change control and build process, IMO.
Ideally, anything in the SQL server should be version controlled--inclusing Service Broker routes, queues, etc, etc.
I hope that while the above is a lengthy post, it would be useful for you.
Please let me know if you'd like some more information. I, on the other hand, would love to hear DevArt's position and your plans going forward. I think it'll benefit all your customers, not just me.
Thank you for your consideration.
I see that your current implementation of database schema versioning as implemented in the SQL Schema Compare product underlines the concept of "snapshot" versioning. So, you take a snapshot of the schema and version that as a monolithic entity. That's a good approach and is useful in many scenarios where a database developer or developers want to achieve versioning of the entire database regardless of what changes were made. So, if you and I were to work on a project together, we can change one object or a thousand objects as a part of a given commit--a single snapshot will cover it and then it can be brought into source control system and considered a single commit.
My issue however, is a bit different. We have development simultaneously happening in C#/VB.NET and T-SQL. Different developers are making changes to source code in those two distinct environments, but the resulting codebase needs to be in sync. So, if one developer changes 10 stored procedures and commits them all in the same snapshot, but only 3 of stored procedures will need to go to production for this release, in order to match a set of VB.NET web pages for instance, then I'm stuck with rolling out 7 extra SPs that are absolutely useless and can even break something. Sure, I can use the Schema Compare tool and select only what I need, but now the source control commit is not atomic--some of it is used for the build and some of it is not. That makes it hard to keep track of things, especially if I need to keep my VB.NET work in sync with my T-SQL work on a build level.
Instead, I'd like to view all T-SQL objects as individual items and be able to check them in and out as I see fit. In fact, why should a table or stored procedure be different from a C# source file? Just like I check out C# files to a local working directory, I should be able to check out T-SQL objects to a local "working server"--it could be an Express edition in case of MS SQL, for instance. Other DBMSes are similar. I'd like to be able to right click on something in SSMS or a standalone tool, and check-out an object, make my changes, and then check them in, together with an appropriate commit message.
Then, developers are free to work on whatever objects they see fit--during the build cycle the proper objects and the proper versions will be selected, especially if there is integration between build tool, change control tool and source control tool (such as Trac/SVN, for instance).
In my example above, only the 3 SPs will be picked up by the build process. This allows for a much more fine-grained control over the change control and build process, IMO.
Ideally, anything in the SQL server should be version controlled--inclusing Service Broker routes, queues, etc, etc.
I hope that while the above is a lengthy post, it would be useful for you.
Please let me know if you'd like some more information. I, on the other hand, would love to hear DevArt's position and your plans going forward. I think it'll benefit all your customers, not just me.
Thank you for your consideration.
Thank you for sharing the experience.
There is one more thing matters in database versioning.
How do you upgrade the existing database when the new version of the program is released?
I mean, do you manually write upgrade scripts and store them in version control system?
Or, maybe, you use tools to update the production database?
IMO, this is important for implementing the complex solution for the database versioning.
There is one more thing matters in database versioning.
How do you upgrade the existing database when the new version of the program is released?
I mean, do you manually write upgrade scripts and store them in version control system?
Or, maybe, you use tools to update the production database?
IMO, this is important for implementing the complex solution for the database versioning.
Normally, I'd use tools, like the SQL Schema Compare or similar. This is a topic of ongoing discussion on our end also, but here's how I see the process:
1) Developers work with Source Control only--they check-in/check-out stuff as they see fit to their "working server".
2) Deploying a new version from a development to staging database environment should use SQL Schema Compare tool or similar. The tool would sync from "working server" to test or staging server. This helps with any conflict resolution. This is where the change scripts would be generated by the SQL Schema Compare, but only so that changes can be rolled back.
At this point, Source Control is not involved at all.
3) From staging to production is similar to 2). In many companies, 3) is performed by a Production Team, which means that change script from 2) could just be saved as a file and Production Team simply applies it to production or rolls back the change if errors occur. They shouldn't occur of staging was the same as production before dev changes were applied to it.
So, the short answer is: Sql Schema Compare is the way to go here, IMO.
Am I answering your question fully?
1) Developers work with Source Control only--they check-in/check-out stuff as they see fit to their "working server".
2) Deploying a new version from a development to staging database environment should use SQL Schema Compare tool or similar. The tool would sync from "working server" to test or staging server. This helps with any conflict resolution. This is where the change scripts would be generated by the SQL Schema Compare, but only so that changes can be rolled back.
At this point, Source Control is not involved at all.
3) From staging to production is similar to 2). In many companies, 3) is performed by a Production Team, which means that change script from 2) could just be saved as a file and Production Team simply applies it to production or rolls back the change if errors occur. They shouldn't occur of staging was the same as production before dev changes were applied to it.
So, the short answer is: Sql Schema Compare is the way to go here, IMO.
Am I answering your question fully?