SQL Server Default Backup Location

SQL Server makes it easy to set a server's default data and log locations. In SQL Server Management Studio, simply right click on the top level server node in the Object Explorer and select Properties. Then click on the Database Settings page selector on the left side of the screen. At the bottom of this screen there are text boxes for entering the default Data and Log locations.

Unfortunately it's not as easy to set the default backup location. To do this you need to modify the server's registry. Open the Registry Editor by typing regedit either on a command line or in the Search programs or files box at the bottom of the Windows Start Menu. Once you're in the Registry Editor navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\YourInstanceName\MSSQLServer. Now enter the directory you'd like to be your default. Save and close the Registry Editor.

The next time you attempt to backup or restore a database you will be presented with your new default backup location. No more navigating to a different location over and over again.

The Future of Visual Studio Database Projects

It is with great sadness that I relay the decision by Microsoft to do away with the Visual Studio Database Project type that I love so much. This project type has given database developers the tools needed to implement high quality Database Development Life Cycle processes. Some highlights can be found here.

However, all is not lost. VSDB will be replaced by what was until yesterday referred to as project Juneau and is now officially named SQL Server Data Tools. It will also be hosted in the Visual Studio environment.

SSDT has many similarities with VSDB but is a complete rewrite from what I understand. Below are some of the features previously unavailable in VSDB.

1. Code navigation (Go to Definition, Find All References)
2. Enhanced code analysis and design time validation
3. Table designer similar to SSMS
4. Connected (live database) and disconnected (declarative) development
5. LocalDB - SQLExpress without the need to explicitly install any SQL Server instances
6. Entity Framework integration for a much more tightly integrated development experience between the data and application layers
7. Support for CLR objects
8. Support for SQL Server 2012 and SQL Azure databases (both support 2005, 2008, and 2008 R2)
9. Integration of SSIS, SSRS, and SSAS projects into the next edition of Visual Studio (currently Visual Studio 2011)

In my opinion, of the biggest shortcomings (as of SSDT CTP3) of SSDT is that there is no longer support for database unit tests. Automated unit tests have a huge impact of quality and I believe are key to robust DDLC.

While I am sad to see VSDB go, I am confident that Microsoft will create an even better tool in SSDT. I look forward to exploring it and sharing my findings in future posts.

SSIS Configuration File (.dtsconfig) Behavior (relative path issue)

Let me start by saying that I prefer SQL Server configurations over all other configuration types in SSIS for the majority of configurable values in my SSIS projects because they are more secure, easier to decipher, and fit well into database deployment methods and tools that I use.

Unfortunately, not all configurations can be of the SQL Server configuration type as we need an initial configuration that stores the connection string for the database that the SQL Server configurations live in.

This is where it becomes necessary to use an XML configuration file type of SSIS configuration. One of the problems with using an XML config file is that SSIS does not understand relative paths. This means that all environments must store the config file in the exact same directory structure, otherwise the file will not be found (except in some circumstances which I'll explain later in this post).

To get around this problem we can use something called an Indirect XML configuration file configuration type to store this single configuration containing the connection string for the SSIS configuration database. See the first configuration in the screen shot below.

The beauty of this configuration type is that it allows you to use an XML config file in such a way that its location is not environment specific. It does this by getting the location of the XML config file from a Windows Environment Variable. Unfortunately this must be created in environments where SSIS packages are being developed (where BIDS is installed) and in runtime environments where the config file does not live in the same directory as the SSIS packages being executed. While this does require added effort, it only has to be done once per environment. Below is a screen shot of the creation of a sample Environment Variable.

If you store your XML configuration file (.dtsconfig) in the same directory as the rest of your SSIS packages (.dtsx) then it is not necessary to create this environment variable on machines where your packages will be executed by the SSIS runtime and/or dtexec/dtexecui. SSIS will attempt to find the Environment Variable and when it cannot, it will then look in the root path of the SSIS packages and use the config file found there.

However, if your design requires that you store your XML config file in a location other than your package location you must create the Environment Variable in all locations.

At the risk of repeating myself, BIDS will not be able to find your config file unless you hard-code it's absolute path in a regular XML configuration file configuration (non-Indirect XML configuration) or create an Indirect XML configuration using an Environment Variable. This is true even if the config file lives in the same directory as your packages. This works fine when there is only one developer and that developer uses only machine to develop. Once you have multiple development team members with potentially different directory structures, you can avoid many headaches by making your XML config file location environment agnostic. Don't fear the Environment Variable!