SQL database Version

[vikassethi]

Can we also maintain versions for SQL Server database that is being used in our project? What I would like to do is look what tables/SP/Views were changed from one version to another.

[drmccue]

At SourceGear, we store our SQL Server database definition, stored procedures, and other T-SQL scripts in Fortress. We write all of this information in text files, and manage them under source code control as we would any other text file(s).

Best regards,
Dan McCue
SourceGear

[vikassethi]

I see. So what you mean to say is that like I can checkout/checkin forms in my Visual Studio, there is no way I can do the same with my SQL Server database where I checkout or checkin a table or a Stored procedure?

[drmccue]

Currently, there is no mechanism in Fortress to support treating SQL Server objects (tables, stored procedures, etc.) in the same manner as Visual Studio objects. To say it another way -- Fortress is not integrated into SQL Server Management Studio the way it is integrated into Visual Studio.

Best regards,
Dan McCue
SourceGear

[vikassethi]

I am not sure whether this is the right forum to ask, but are you aware of any product that offers this functionality.

[drmccue]

As far as SourceGear employees answering your question goes -- you're right, this isn't the forum to get an answer to that question. Other forum readers may be able to answer you. Personally, I am not aware of any other systems that offer your desired functionality. The method that I described in the first answer is working quite well for us, and allows us to handle our entire database definition as one "object" (file), all of our stored procedures, triggers, and user-defined functions as one "object" (file), and our upgrade procedure as yet another "object" (file). I suppose it is a matter of perspective on which of these approaches is better -- our method allows us to see database-level changes (foreign keys, for example) that occur at the same time as table-level changes (add table, add/drop column, change definition of column, etc.). I see the benefit of the integration you've asked about, but it is not currently part of our feature set.

We'll be glad to answer any other questions you might have about Fortress functionality, and we hope you'll continue to consider Fortress for your source code and ALM needs.

Best regards,
Dan McCue
SourceGear

[vikassethi]

For source code and ALM needs, I can't think of anything but Fortress. I am a very loyal user of your system and highly recommend it at other forums. However, since last few weeks my requirement for SQL source control is increasing and that's why the hunt has started for that solution as well. However, I appreciate your help on this. Thanks.

[drmccue]

Thank you for your recommendations and praise for the product. I have added your "vote" to the feature request for integration with SQL Server Management Studio.

[AjarnMark]

Vikassethi, in our environment, we also use an approach similar to what Dan has described, in that we have scripts for each of our Functions, Views, Stored Procedures, Jobs, and so on. Then we also have a set of specialty one-time scripts that can only be executed once in any instance of our database, such as CREATE TABLE or ALTER TABLE statements. These are written to protect against being accidentally executed more than once, which would throw an error if it was allowed. This collection of specialty scripts can be closely monitored to keep tabs on what is changing within those critical objects.

These scripts can be grouped together into a SSMS Project and Solution, and then you can check-out/in directly from SSMS. We don't currently use that capability due to other concerns, but we ran some tests with it and it appeared to handle the source system integration okay.

Of course, the catch with using scripts for everything is that you have to learn the T-SQL syntax rather than just rely on the UI capabilities in SSMS. However, SSMS does have the option in most situations to Generate a Scriptfor you that contains the commands that make the changes you just did instead of Saving (executing the commands) immediately. Then you can save that script file into the proper working folder and put it in your source control.

Because these script files are all plain text, we are able to use all of the DIFF features within Fortress. Also, these scripts then become part of our deployment package as we move from Dev to Test to UAT to PROD. With everything scripted, we know that the identical changes are made in each environment.

Hope that helps.

[vikassethi]

Thanks for your detailed reply. For the moment, the company has decided to use Red-gate products for knowing the database versions and difference between them. For future, we would actually like to look for some product that exposes SQL objects in an easy manner like .net objects because the company would like to spend as less time as possible for any SCM related to SQL Server. However, I do appreciate your reply.

[dist0rti0n]

Currently, there is no mechanism in Fortress to support treating SQL Server objects (tables, stored procedures, etc.) in the same manner as Visual Studio objects. To say it another way -- Fortress is not integrated into SQL Server Management Studio the way it is integrated into Visual Studio.

Best regards,
Dan McCue
SourceGear

The classic client let you manage SSMS solutions connected to Fortress and you can always create a database project type in Visual Studio.

The real trick to managing SQL code is your mindset - you have to start thinking of the database as both an application and the data not a single unit. You can easily apply the same methodologies to managing DB code with a little discipline. The first thing to do is create a baseline of all your objects and check it in - then you'll need to be disciplined to work from those files and deploy to SQL Server - break the old habits of logging in, right-clicking to edit a file and the immediately running it on the server. That treats SQL Server as both the application, the data, AND source control. You want Fortress to be your system of record - not the DB.

Once you get that process in place everything else will fall in line.

[Itamar]

Hi All,
There's a third party tool you can use for SQL Version Control (Data and Schema),called Randolph

[dvdtknsn]

Due to popular demand, Red Gate will be introducing Vault support for SQL Source Control shortly so please sign up to the EA program to be notified as to when the build will be available.

http://www.surveymk.com/s/SqlSourceControl_EapSignup

Kind regards and apologies for the wait!

David Atkinson
Product Manager
Red Gate Software