Here are the answers to questions from the SQL Source Control webinar that was run on August 19, 2010. You can view the recording (WMV) if you wish.
Q: Is there support for Sourcegear Vault? or just for Subversion and TFS?
A: SQL Source Control v1.0 supports Subversion (SVN) and/or Team Foundation Server (TFS). We are considering supporting others based on polls ran on www.sqlservercentral.com and feedback we hear from users. Please vote/comment athttp://redgate.uservoice.com/forums/39019-sql-source-control/category/6301-additional-source-control-support.
Q: Is source control installed on just the DB server or on each desktop that runs Management studio?
A: SQL Source Control is an add-in to SQL Server Management Studio (SSMS). SQL Source Control will need to be installed on each client that runs SSMS in order to access the source control features.
SQL Source Control is not a source control system. It’s an integration between SSMS and existing source control systems (SVN, TFS). Your source control system is a server level item that should be centralized. All team members can use SQL Source Control on their SSMS client to link to the same repository to share changes between them.
Q: What is the impact of SQL Source Control on SSMS? Are there stats on memory consumption? Or is it minimal?
A: This depends on your database size, the number of databases you have linked, and the number of database developers on that server. SQL Source Control polls your database server to detect if changes have been made to the database to show the blue indicators on the Object Explorer. If this is a problem, this polling can be configured via an XML file if you contact support@red-gate.com. It’s best to have a local development database for each developer.
Q: Does SQL SC handle CLR objects, such as .dll files that are deployed onto the db server?
A: Yes. There was an issue in the original release with 64 bit CLR objects, which is fixed in v1.0.6.3 and later, http://www.red-gate.com/MessageBoard/viewtopic.php?t=11654.
Q: What happens if someone alters an object that doesn't have this software?
A: The alteration is the same as ever. If someone does not have SQL Source Control, then they will not be notified and they will not be able to commit these changes to source control.
Q: On our systems some of the stored procedures differ slightly between databases they are installed on. For example they may link to a table via a linked server and the linked server will have a different name on different vendors databases. Currently we store the procedure in version control by storing it as a quote delimited string that is executed We have a function on the database that supplies the linked server name inserts it in the stored procedure definition string as the procedure is installed on the databases. Is it possible to store this type of logic on this source control system?
A: We would have to try this...
Q: Will this work for schema changes?
A: SQL Source Control is tracking schema changes to your database. In source control, a creation script for each object is being stored so you can see how each object changes over time.
Q: We have three environments (DEV, UA and Prod). I'm trying to understand how this would work in moving changes from each environment. (Our developers are dbo on their databases in DEV but not in UA nor in Prod.)
A: SQL Source Control is a development tool. It should be used for your day-to-day db development activities to track changes. The person that deploys changes would use SQL Compare to deploy the change to QA and Prod. Red Gate’s SQL Compare can generate the alter script based on a scripts folder from your source control system and a target database. This ensures no changes are forgotten and they are applied in the correct dependency order.
Q: Is there support for controlling table data (test data sets or lookup table entries)?
A: SQL Source Control puts an empty “Data” folder in the repository. You can use Red Gate’s SQL Data Compare to script out lookup table entries for now and we hope to make this more integrated in a later release, http://redgate.uservoice.com/forums/39019-sql-source-control/suggestions/457874-source-control-data-data-folder-is-blank-. We believe that the application, db schema, and lookup table entries should be versioned together to provide a complete picture of the project at a point in time.
Q: When another user creates a new database and links it, does the data come as well or is it just empty tables?
A: It’s only the schema. Tom could have restored a backup of the original database instead to have some development data to work with. If Tom does create a blank database, he could use Red Gate’s SQL Data Generator to populate realistic test data to develop against.
Q: How can the source control system restrict multiple users modifying the objects? (like an exclusive lock, checkout/checkin etc)?
A: We currently don’t support this, but you can vote/comment on this feature at http://redgate.uservoice.com/forums/39019-sql-source-control/suggestions/802123-tfs-exclusive-checkout-svn-lock.
Q: Is Sql Server Control working with Express Edition of Sql Management Studio?
A: SQL Source Control works with SSMS 2005, SSMS 2008, and SSMS 2008 R2. We currently do not support express. (It seems to work in SQL Server 2008 Express, but this is not supported and therefore not tested.)
Q: The [USE XXX] statement at the top of the script showed Tom's database during his session. So I presume that doesn't get included in the version in source control?
The USE statement is not included in source control. That was included since Tom right clicked on the stored procedure in the Object Explorer and selected SSMS’s modify feature.
Since the USE is not included, what’s in source control can be applied to any target database name.
Q: Will we be able to replay this webinar at a later time?
A: Yes, we will email you a link to this recorded webinar. We will also be doing another webinar in the next few months.
Q: What happens when many developers share the same database?
A: SQL Source Control does not change the database at all, so you can continue to work as you normally would. If many developers share the same database, then you could run into problems such as 1) overwriting each other’s changes and 2) making a complex change, which breaks something and impacts everyone on that database.
If you use SQL Source Control on a shared database, you should be careful to only commit/undo your own changes. We hope to have better support for this in a future release.
Q: When you do a get latest version, do you get a script that can be copied and also deployed somewhere else? If you have a dev database and a live database on a different server?
A: When you do a get latest, you are automatically updating the development database. You shouldn’t link your live production database to source control. Use Red Gate’s SQL Compare to generate a script from the version in source control to your live production database. This way, you have a chance to review the script first and run it on staging to test it out before running it against your live production database.
Q: How do you handle different versions of scripts that are formatted differently but are functionally equivalent?
A: This depends on the object. For stored procedures, this would be considered a diff. For tables, we ignore column order for now, so this would not be considered a diff. We could change this in a future version if we hear from users they would prefer this to be considered a diff.
Q: Can you get latest based on a source control label?
A: You can label a version in your source control system. You can get/checkout this labelled version to a folder and then use Red Gate’s SQL Compare Pro to update a database to this version.
Q: Does it require SQL Compare or is this standalone?
Q: If it uses SQL Compare Pro, do you need to buy that to use SQL Source Control?
A: To link databases to source control, commit/get/undo changes, and view history, only SQL Source Control is required. SQL Compare is needed to deploy database changes to other environments or to restore a previous version.
Q: How do table schema changes work - specifically adding a column? When I 'get latest' will it issue drop/create statements, or will it intelligently issue an ALTER TABLE ADD COLUMN statement. In other words, is it possible to preserve data that is in your local database?
A: SQL Source Control preserves your table data. When you do a get latest, it generates an alter script to apply the changes in source control to your development database in the correct order.
Q: Will we get to see an example working with TFS during this Webinar?
A: Not during this presentation. The only difference is the initial setup when you link a database. We have another recorded webinar that will be available soon.
Q: If I didn't commit the changes to source control, would that be rolled back in sql server tool or not?
A: If you don’t commit the changes, then they will remain as changes and appear on your commit tab when you lob back into SSMS. We don’t change any of the current behaviour of database development. You would have to explicitly undo your changes if you want to get back to a previous state.
Q: Can this be used in sql 2000?
A: We currently support SQL 2005, 2008, and 2008 R2.
Q: Can you sort your columns on the history tab? or filter? so i'm looking at just one person's changes...
A: Not currently. You could use the source control system’s history to do this...
Q: Where is the association of the database to source control stored?
A: This is stored as an XML file for each user. It is on the client machine in the users application data.
Q: Can you setup role like read only?
A: Read-only would depend on rights on the SQL instance (sql permissions) and in the source control system (SVN, TFS)
Q: Does this work in a distributed environment, where we mix offsite and onsite developers?
A: Read-only would depend on rights on the SQL instance (sql permissions) and in the source control system (SVN, TFS)
Q: I have too many sps... Can we filter those sp that got modified?
A: The commit list will only show sps that you have modified.
Q: Can you view history on just one object within SSMS?
A: The history is currently for the entire database. We are currently working on this feature and in v1.1 (expected in Sept), you will be able to see history for an object if you right-click on that particular object.
Q: I think this was answered by the labels question, but is there any support for branching and merging?
A: If you use your source control system to branch, you can link your database to that. To merge the branch, you would also need to use your source control system.
Q: What about the merges?
A: For merging a branch, you would need to use your source control system. For conflicts, you could choose to Keep Mine, Take Theirs (what’s in source control) and then reapply a change, if needed. Or, you can use an external tool for complex merges.
Q: How much does it cost?
A: See www.red-gate.com/products/sql_source_control for pricing information. There are multi-user discounts available. SQL Source Control is also available in the Developer Bundle and SQL Toolbelt, if you are also considering other Red Gate tools.
Q: How would this handle promotion to test and production environments?
A: You should use SQL Compare Pro to promote changes to test and production environments. SQL Compare Pro generates a synchronization script in the correct order and ensures nothing is forgotten. You could set a process to only deploy from source control (instead of the dev db) to force users to commit their changes or they won’t get included in the promotion.
SQL Compare Pro can also be used via a command line. You can automate your database builds and include this in a continuous integration environment so that your automated tests are running against the latest application and database. For more information, see http://www.red-gate.com/help/ContinuousIntegration.html.
Q: I'm a single developer shop, without TSF or SVN. How would I use this tool stand alone?
A: SQL Source Control is not a source control system. It’s an integration between SSMS and source control. You would need a source control system. You could look into SVN, since it’s open source. You could also look into TortoiseSVN, which provides a file based repository, for single developers, http://www.red-gate.com/supportcenter/Content.aspx?p=SQL%20Source%20Control&c=SQL_Source_Control/help/1.0/SSC_Setting_Up_SVN.htm&toc=SQL_Source_Control/help/1.0/toc.htm.
Q: How do you handle changes to the static data set that can be pushed out, if you wanted to add/remove data?
A: SQL Source Control provides an empty “Data” folder in the repository. You can use SQL Data Compare Pro to synch static/reference/lookup data to this folder, http://www.red-gate.com/help/SourceControlData.html. We hope to integrate this into SQL Source Control in a future release.
Q: I think I saw that source control was included for existing users of some of the Red Gate tools (SQL Compare Pro, etc.) -- could you briefly discuss licensing for current Red Gate users?
A: SQLSource Control is included in the SQL Toolbelt and SQL Developer Bundle. If you have one of these with active support and upgrades, then you are entitled to SQL Source Control at no extra cost.
Q: Does the SQL change tracking feature work with sql source control?
A: I do not understand this question...
Q: How does this handle changing the name of, for example, a store procedure?
A: Renames are currently treated as a drop and an add.
Q: What happens if someone who doesn't have this tool changes an object in the database?
A: The same thing as normally would. That person would not be able to get notifications of changes or commit the changes or take advantage of any of the other features (e.g., viewing history to see what has changed).
Q: Is there a way to gather all changes to setup release script easily?
A: Yes, use SQL Compare Pro. This will generate the synchronization script for you in the correct dependency order.
Q: Can we create versions of the databases instead of get latest for deployment into different environments?
A: Yes, use SQL Compare Pro. This will generate the synchronization script for you in the correct dependency order.
Q: Following your link to http://redgate.uservoice.com/forums/39019-sql-source-controlshows voting options, but nothing for source control support voting...
A: Vote comment at http://redgate.uservoice.com/forums/39019-sql-source-control/category/6301-additional-source-control-support.
Q: Can you please show how the structure looks in source control (SVN or TFS) for the database structure used in this demo?
A: Sorry we didn’t see this question during the webinar. The source control system has directories for different object types and then each object has its own .sql file, so you can track how each object changes over time.
Q: What is the performance on large databases (>5000 objects)?
A: The performance depends on the number of objects, but also on the size of the objects (e.g., how many lines they are). The performance should be comparable to running SQL Compare on the database.
Q: So this is more of a developer tool than a DBA tool, because DBA would use Sql Compare?
A: It depends. A DBA could use this to see the history and track who changed what, when, and why. They would then use SQL Compare for deployment.
Q: How about deploying schema changes on a table that contains data? Is it not recommended? Does it handle any special scripting techniques required in this scenario?
A: When you get latest, we generate an alter script, so data in a table is preserved. If you rename a column in a table, this is treated like a drop/add, so you may want to run your own alter script for this update to preserve your data. If you add a new NOT NULL column, and the table already contains data, you will need to provide a default, or add the column manually, then update it, and then set it to be NOT NULL.
Q: Can they better describe the performance affects of the behind-the-scenes polling?
A: SQL Source Control polls your database frequently to detect changes to display the blue indicators in the Object Explorer. Performance is better in dedicated environment versus a shared database. Performance is also better if you are working on a local database.
Q: If SSMS is connecting to a SQL 2000 server, will this product still work?
A: This is not supported and it is not tested. SQL Compare does support SQL 2000, so it may work in some cases...
Q: Just to be clear you would not source control your production db, but a dev instance and then simply move changes to prod via a tool like SQL Compare?
A: Yes. SQL Source Control is for you day-to-day db development activities as you make changes, test them, and then commit them to source control. You would then use SQL Compare to deploy these changes to your Test/QA/Staging/Production environments.
Q: How well does SQL Source Control system handle merging collisions?
A: In a conflict situation, where 2 users are on a dedicated environment and they are both changing the same object. User1 would commit the object and have no problems. When User2 goes to commit the object, then they would see a conflict on their commit tab. User2 can decide to keep mine or take theirs. If they keep mine, then they may need to reapply User1’s changes. If User2 decides to “take theirs,” then they may need to reapply their own changes. For complex changes, an external tool could be used to merge the files.
Q: Is there a white paper on a good overall lifecycle using this tool, maybe with SQL Compare?
A: Here’s a white paper on improving database development using Red Gate tools, http://downloads.red-gate.com/HelpPDF/ImprovingDatabaseDevelopment.pdf. There’s also a video about how you can improve database development using SQL Source Control with other Red Gate Tools at http://vimeo.com/12204922.
Q: Will this source control SSIS packages as well?
A: No. SQL Source Control is for source controlling database schema objects in SSMS. If SSIS packages are developed in BIDS, there should already be integration with source control.
Q: Is it possible to search within SQL Source Control?
A: I don’t quite understand this question... You can sort the objects on the Commit/Get tabs.
Q: Can you load a previous revision in source control?
A: You need to use Red Gate’s SQL Compare Pro to do this.
Q: Can you shelve database changes?
A: No. Please vote/comment on our Suggestion Forum at http://redgate.uservoice.com/forums/39019-sql-source-control, if you are interested in this feature. As of August 23, 2010, there are no suggestions for this yet.
Q: Can this work in an environment where multiple developers work against the database? And then subsequently have each user commit only his/her changes?
A: You can work in an environment where multiple developers work against the same database, but you have to be careful to only commit your own changes. Currently, all changes are shown on the Commit tab. Please vote/comment on this topic if you would like better support for this in a future version,http://redgate.uservoice.com/forums/39019-sql-source-control/suggestions/838691-show-or-filter-by-username-on-commit-list-for-shar.
Q: How does the product work when 2 or more users make changes to the same object locally then do a commit? Are the changes merged?
A: Users have the option to keep mine or take theirs (what’s in source control). They may then need to reapply changes. If the change is very complex, then they can use an external merge tool to merge the 2 versions and then commit this merged version back to source control.
Q: I have too many stored procedures... Can we filter those stored procedures that got modified?
A: I don’t quite understand this question... The only objects that appear on the Commit tab are those that have been modified. If you only want to commit one stored procedure specifically, then you could right-click on that stored procedure and select commit, which will only select this object in the Commit list.
Q: Our dev db is restored from production on a regular basis. How would source control handle this?
A: It would be best to restore your prod db right after you do a synchronization and the backup matches what is in prod. If not, you may need to resolve some conflicts.
Q: Is there any plans to force TFS source control policies and tied TFS source control integration?
A: We hope to support TFS policies. V1.0 currently ignores them. Please vote/comment here if you are interested in this feature, http://redgate.uservoice.com/forums/39019-sql-source-control/suggestions/726952-evaluate-tfs-policies-when-committing-db-changes. We currently support associating or resolving work items when committing. Please see more information at http://www.red-gate.com/MessageBoard/viewtopic.php?t=11454.
Q: Is there any support for automatic merges when there is more than one person working on a particular object?
A: No. We don’t support automatic merges because we think that a database developer should review the scripts and make sure that the logic is still correct.
Q: Can you pull the .sql script file from source control (instead of having SSMS script it) when you tell management studio to modify an object?
A: No. You just continue to work on your db as normal as if the file didn’t exist. SQL Source Control will automatically script it out for you.
Q: Will it include CDC (change data capture)?
A: I don’t understand this question...
Q: Does source control keep track for security/role changes?
A: Yes.
Q: If one person is not connected, are they running wild?
A: I don’t understand this question. It depends on your database development model. On a shared model, you would still see their changes, but they would not be committing them to source control. On a dedicated model, then their changes would never be pushed back to the rest of the team.
One best practice is to deploy from source control instead of from the development database so that users are forced to commit their changes if they want them to be deployed.
Q: How is check-in/check-out handled to ensure that developers are not stepping on each other?
A: We currently don’t support this because we are using the edit-merge-commit model instead of checkout-edit-checkin. If this is a feature you would like, please vote/comment athttp://redgate.uservoice.com/forums/39019-sql-source-control/suggestions/802123-tfs-exclusive-checkout-svn-lock.
Q: VSS support was mentioned. When to expect it?
A: VSS is currently not supported. Please vote/comment at http://redgate.uservoice.com/forums/39019-sql-source-control/category/6301-additional-source-control-support.
Q: Is there any options to specify which objects or object types to source control?
A: Not in v1.0, but we are hoping to provide this capability in a future release, http://redgate.uservoice.com/forums/39019-sql-source-control/suggestions/458708-exclude-objects-from-versioning-svn-ignore-.
Q: Where do we 'vote' on the additional stores?
A: Please vote/comment athttp://redgate.uservoice.com/forums/39019-sql-source-control/category/6301-additional-source-control-support.
Q: So branching can be done manually by changing the link, but is there a way to do merging?
A: You would also need to use your source control system to merge the branches back together.
Q: Can we discuss reporting a bit?
A: Unfortunately, we did not get to this question during the webinar. Viewing the history is like a report of who changed what, when and why. If there’s something else you would like to see, then please vote/comment at http://redgate.uservoice.com/forums/39019-sql-source-control.
Q: Please change a table, and a view, then display the commit page, those should be sortable?
A: Unfortunately, we did not get to this question during the webinar. All changes would show up on the Commit tab. The Commit list at the top is sortable. Also, dependencies are considered. If you try to commit only a view that references a table in the commit list, you will be prompted to include the table as well.
Q: Do you save the objects or scripts of the objects?
A: Each object’s creation script is saved in source control. This is so you can track how each object changes overtime.
Q: Does it have its own process memory or shares with SSMS or SQL Server?
A: SQL Source Control does not have its own process because it is an add-in to SSMS.
Q: Could you further integrate the with the Object Explorer to have the source controlled version pulled instead of what's on the server?
A: To get the version in source control, use SQL Compare Pro. To suggest new features/enhancements, please vote/comment at http://redgate.uservoice.com/forums/39019-sql-source-control.
Q: You said source control linking is by SSMS client, but things like views are stored in the DB. If more than one person is developing on a single SQL Server, on One DB, aren't changes overwritten?
A: SQL Source Control doesn’t change your database development. With or without SQL Source Control, if more than one developer is making changes to the same database, then changes could be overwritten. That’s the advantage of SQL Source Control. It’s so easy to share your changes between developers if they each have their own database linked to a centralized source control repository. Therefore, you can make sure changes aren’t overwritten and if a developer is exploring complex changes that could break things, it is not impacting anyone else on the team.
Q: It’s been challenging trying to convince my boss that developing against production is a bad idea. Is there a step by step scenario on how you would use a staging environment for dev and using this tool to push to prod?
A: Developing against prod is definitely a bad idea. There’s problem tons of info on Google about this.
The best thing is to use SQL Source Control against a development db and then use SQL Compare to deploy these changes to other environments.
Q: Can this work if I already have a VS 2010 Database project setup? Meaning can this work and interact with a VS 2010 Database project?
A: Not currently. SQL Source Control uses SQL Compare’s format to write the .sql files that are stored in source control, which is different than the VS 2010 db format. This is something we are considering. Please vote/comment athttp://redgate.uservoice.com/forums/39019-sql-source-control/suggestions/1010465-work-with-a-visual-studio-2010-database-project.
Q: If your database has foreign keys, will this source control execute statements in the correct order to ensure the dependencies are correct?
A: Yes. That’s why regular source control is hard. SQL Source Control uses SQL Compare behind the scenes to update your working database in the correct order. It will also generate alter scripts during Get Latest so table data is not lost.
Q: Can we tell if others are working on the same object to avoid conflict during commit?
A: Not currently. Depending on team dynamics, you may have other ways to communicate this, bug tracking system, daily standups, etc.
