SQLServerCentral Webinar #11 Q&A - A Sandbox Development Process

Q: Does SQL Virtual Restore work with SQL 2005?A: Yes, it works on SQL Server 2000 (SP 3 or later), 2005, 2008 and 2008 R2

Q: Will these Red Gate tools work with SQL Server Express?A: This isn't officially supported/tested, but I think it might work.

Q: In your experience, what is the best way to co-ordinate DB development work between DBA and application developer?A: Sit near each other and communicate. Use a common process for changes to the system.

Q: What about promoting changes to QA/Production. Specifically, if a SQL object is changed by two different developers, on two different commits?A: If two developers make a change, the first developer to commit to source control will 'win'. The next developer will experience a 'conflict', and will have to decide whether to 'keep mine' or 'take theirs'. Or they could manually merge the objects.

Q: How does change control tie into version control? Separate software or integrated process? What have you seen that works well in small shop (10-20 developers)?A: We certainly advocate generating your SQL change script from a source control label, so I guess this is an integrated process. Some people like to have change control superceed version control, with your VCS changes (script, deployment, etc) rolled into a process that delivers the change. Alternatively, you take any changes, drop them into a branch, and then include them in VCS, deploying from there.

Q: Can you mount a .BAK which is located over a network? \\Server2\c$\MyDB.BAK?A: Yes

Q: Is there a way to DIFF or see the change history of tables/procs from withing SSMS/SQLSource or do we use svn and diff scripts on the file system?A: Yes, this is possible. Right click on the database or at object level in the Object Explorer and select the History option.

Q: Virtual Restores are ReadOnly right?A: No - the database you mount from your backup with SQL Virtual Restore is a live, transactionally consistent and fully functional database. Any changes are written into the .vldf and .vmdf files. For write intensive applications, you might consider SQL Storage Compress, which compresses the data files themselves.

Q: So there is no "exclusive checkout" with the shared model?A: No exclusive checkout as we can't stop users who have permissions from changing a database. We might add this as an option using DDL triggers in the future, but most users we've spoken to don't like the idea of this.

Q: What are the primary differences (from a process / source control perspective) the shared db mode vs sandbox mode? A: If each developer has their own sandbox, the isolation means any breaking changes don't affect other developers until they choose to 'get latest'. If you're using the shared model, each change affects everyone the instant it's made.

Q: Is there a plan/path for branching databases from the root/default instance which is the trunk to a retro instance, relinking to a retro branch without having to manually unlink, backup, restore, link?A: It's not currently possible to branch from within SSMS. You need to use your existing source control client tools. However, this is something we hope to add in a later release. We also understand that the unlink/link process is a little heavy, so this will also something we will review going forward.

Q: Is there a way to write "post get latest" scripts that are automatically run on get latest?A: At the moment, 'get latest' uses the SQL Compare engine so it will naively generate a script. However, SQL Source Control 3 will allow you to commit custom scripts alongside your more complex schema changes and data migrations, which will be not only re-used by 'get latest' but also by SQL Compare on deployment.

Q: I imagine that to keep virtual restores smaller they must not build indexes, compress data or something. What are the basic tradeoffs? Is there a URL you can refer me to?A. The virtual restore is smaller because the restore only needs space for the backup plus the .vldf and .vmdf files, whereas a physical restore requires space for both the backup and the live database. The .vldf and .vmdf are used to store any changes written into a virtually restored database; so typically they're very small (often just a few MB) after the restore, as normally they contain just the transactions that are rolled forward during the recovery phase of the restore.

Of course, if you restore from a compressed backup, you will make even larger space savings. The database mounted with SQL Virtual restore behaves exactly like a fully functional database.

Q: Can we revert a data from table back to a version we want?A: Yes. This involves getting a version of your data scripts to a folder and using SQL Data Compare Pro to push this to your development database. The workflow will be improved in the next release.

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands
of articles and SQL scripts, a library of free eBooks, a weekly database news roundup,
a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals
that makes it such a success.