Automation with Red Gate tools - 1.0
Worked example: setting up continuous integration with TFS2012 - Automation with Red Gate tools
This worked example demonstrates how to set up Red Gate's SQL Automation Toolkit with Team Foundation Server 2012.
The end result is the WidgetShop example database configured for continuous integration. When changes to the WidgetShop database are checked in to SQL Source Control, a build is triggered in TFS and the current version of WidgetShop is deployed to the continuous integration database, WidgetShopCI. tSQLt tests also then run on a copy of the database.
Make sure you have the following:
- Access to Team Foundation Server 2012 (TFS)
- Access to a SQL Server 2012 instance
- SQL Server Management Studio (SSMS)
- Red Gate SQL Source Control on the same machine as SSMS
- Visual Studio 2012
- The Red Gate continuous integration build scripts. You can download them from this web page.
- The WidgetShop demo files. You can download these in a zip here.
Creating the team project
- Make sure Visual Studio is connected to TFS. Click Connect to Team Foundation Server:
- Enter your server details.
- Open the File menu, go to the New menu and click Team Project.... The Specify Team Project Settings page is displayed:
- Name the project WidgetShop and click Next. The Select a Process Template page is displayed:
- Select a process template, and click Next. The Team Site Settings page is displayed:
- Select Do not configure a SharePoint site at this time and click Next. The Specify Source Control Settings page is displayed:
- Make sure Create an empty source control folder is selected and click Next. The Confirm Team Project Settings page is displayed:
- Click Finish to confirm the settings. The Team Project Created page is displayed:
- Click Close.
The Team Project is now set up.
Setting up source control for the project
- In Visual Sudio, go to Team Explorer and click Source Contor Explorer:
- In the View menu, go to the Other Windows menu, and click Source Control Explorer:
- Click Not mapped to create a workspace mapping. The Map window appears:
- Select a local folder to map the $/WidgetShop root to, and click Map. The following window appears:
- Click Yes to synchronize the files.
- Open the local folder you mapped the source control location to and copy the WidgetShop demo folder to it:
- Copy the Red Gate continuous integration build scripts files into the same folder:
- Using Source Control Explorer in Visual Studio, add all the files you've just copied to source control. To do this, right click select Add Items to Folder.
- Select all the folders and files, then click Next.
- Click the Excluded items tab, select all the items in there, and click Include items(s). Then click Finish.
- Right click again and select Check In Pending Changes.
- Click Check In in Team Explorer.
You now have all the files you need in source control.
Setting up the build
- In Team Explorer, go to Builds.
- Click New Build Definition. The following page is displayed:
- Name the build WidgetDevelopment. Set the Queue processing to Disabled for now. We will change this to Enabled when everything has been set up.
- Click Trigger, the following page is displayed.
- Select Continuous Integration.
- Click Workspace, and click in the Source Control Folder field to browse for a folder. The following window is displayed:
- Set the Source Control Folder to WidgetShop and click OK.
- Click Build Defaults. The following page is displayed:
- Select Copy build output to the following drop folder. Enter the location of the fileshare that you want to use as your drop folder.
- Click Process. The following page is displayed:
- Under Projects to Build, browse for sqlCI.proj in source control and add it.
- Under Agent Settings, select a suitable build agent. In the example below, we have used the Name Filter to select one.
- Save the build definition with Ctrl+S.
You have now set up the build definition.
Setting up the databases
- Open SQL Server Management Studio (SSMS).
- In the File menu, go to Open and click File....
- Browse for the WidgetDevelopment.sql file from the demo folder and click Open.
- Press F5 or click Execute to create the WidgetDevelopment database.
- In Object Explorer in SSMS, go to the WidgetDevelopment Database. Right click it and click Link Database to Source Control.
- On the Link to Source Control page that appears, select Team Foundation Server (TFS). The following dialog is displayed:
- Enter the URL of your TFS server under Server URL. Set the Database folder and Migration scripts folder paths as shown above. These refer to locations in the WidgetDeveolpment folder that you added to source control.
- Click Link.
- Right Click the WidgetDevelopment database in SSMS and click Commit Changes to Source Control.
- The following page appears:
- Click Commit. This commits the database to source control.
- Click New Query.
- In the new query window, execute the following SQL to create the continuous integration database:
CREATE DATABASE WidgetShopCI
ALTER DATABASE WidgetShopCI SET TRUSTWORTHY ON
- Next, run the following which is required for tSQLt tests:
The databases are now configured.
Configuring the targets file
- In Visual Studio, go to Source Control Explorer and open $\WidgetShop\sqlCI.targets
- Fill in the following details:
Database folder path: WidgetShop\Database\Scriptsfolder
Database server name: your database server and instance for example, sql.example.com\sql2008
Target database name: WidgetShopCI
Serial key: your Automation License serial key, or leave it blank for a trial.
To use a Windows account to access the SQL server, set Windows authentication to true. It will use the Windows account that the build agent runs under.
If you want to use a SQL account to access the SQL server, set Windows authentication to false and fill in the SQL user name and SQL password options.
Set tSQLt tests to true.
- Save the sqlCI.targets file.
- Right click sqlCI.targets file in Source Control Explorer and check it in.
- Edit the Build Definition and set it to Enabled. (Similar to step 2 of Setting up the Build, above.)
You have now configured a build for continuous integration.
To see it in action, make a change to the schema of WidgetDevelopment in SSMS, for example, add a new table. Commit the changes in SQL Source Control.
A build will start automatically:
When it's complete, open the log file and view the results.
You should now have successfully configured continuous integration.
Was this article helpful?
Thanks for your feedback!
all SQL products
- Compatibility of Red Gate tools in 64-bit environments
- Application has encountered an error and needs to close
- Error message after installing SQL Toolbelt - The description for Event ID ( 1 ) in Source ( nview_info ) cannot be found.
- Changing the temporary directory used by the installer
- Toolbelt Installer "hanging" while "scanning volumes"
- Login failing with "trusted SQL Server connection" error when using RunAs
- Some Red Gate products identified as containing a trojan by Anti-Virus software
- Activation may fail with Unknown Error -1
- Product uses web help although a CHM file is available locally
- Argument exception resulting from missing environment variable
- Check for updates may fail when used through proxies
- 'Unidentified Publisher' error when repairing or uninstalling
- Licensing activates product as standard edition
- Moving Red Gate software products to another machine
- Red Gate tools log locations
- The application UI opening slowly when there is no internet access
all SQL products
- Red Gate product acknowledgements
- Activating your products
- Activating your products
- Red Gate bundle history
- Check for updates
- Troubleshooting Check for Updates errors
- Current versions
- Deactivating your products
- Installing Red Gate products from the .msi file
- Requesting additional activations
- Serial numbers for bundles
- Reactivating using a different serial number
- Extending your trial
- Finding your serial numbers
- Moving a serial number from one computer to another
- No response received for manual activation
- Licensing and activation resources
- Licensing and activation resources
- Troubleshooting licensing and activation errors
- Licensing and activation FAQs
- Red Gate tools log file locations
- Download old versions of products
- Download product prerequisites & utilities
- Support & upgrades
- Upgrading your software
- Upgrading FAQs