Jonathon

Jonathon Eve-O'Connor's Blog

Executing SQL Scripts From TeamCity

Recently I was working in an environment where we used TeamCity to automate unit testing (further blogs on this coming later). Essentially the process was that the latest source would be deployed, a set of ETL processes would be run and tests would validate the results to ensure changes to the project over the day had not affected the reliability of the results.

In the interests of ensuring that the tests were the same each time my preferred method was to use a script to remove the databases from the SQL instance on the server (for other reasons I was keen not to change the deployment scripts in order to achieve this).

Whilst in this example I delete databases using the SQL script it should be noted you can use this method to execute any SQL script on a target server.

1. Create a new Build Configuration (or add as a step to an existing one).

2. Create a SQL Script that does whatever it is you want it to do and save it in a text file eg ‘ClearDatabases.sql’.

image

3. Create a folder on the server running team city – this will need to be a folder which the service running the team city build agent has access to otherwise the process will fail.

4. Add a build step – giving a name that indicates what the script will be doing – in this case I have called it ‘Drop Existing Databases’.

image

The script should be configured with a runner type of ‘Command Line’ and run a custom script.
In the ‘Custom script’ section I have the following: sqlcmd -e -b -d master -i "C:\UnitTestingScripts\ClearDatabases.sql" -S "DEVTESTDW\SQL2014"

5. If the SQL server on which the script is to be executed is not the same as the Team City server (very likely) then you will need to ensure that the target server has the correct firewall and SQL configuration in order to accept the connection.

 

Getting Started – Continuous Integration – Team City Installation and Setup

I have been working on a project where we have been using Continuous Integration (CI). Having used this for several months I am very enthusiastic about it. To give a brief explanation, using CI means that when you check in your source the project will be built built and (configuration dependant) deployed to a shared development environment.

Whilst this takes a little getting used to I have found the benefits are significant – errors during the build are immediately spotted and with the automatic deployment to the shared environment errors or code clashes can be identified quickly.

In this blog post I will be detail the setup of Team city in a windows and TFS environment for use in CI. The free edition of Team City will be used which allows access to all features and use of 3 build agents should be sufficient for most BI development environments. In subsequent blog posts I will cover the setup of automated build and deployment for Database and SSIS packages using MSBuild.

Preparation

1. Download Team City (Windows edition) from: http://www.jetbrains.com/teamcity/download/

2. Create a domain account which will be used to run the Team City service. The account does not require any special permissions on the network but should be granted local administration rights on the machine selected as the build server. Later when we use Team city for deploying projects this account will need appropriate permissions on the target servers to deploy the Databases, SSIS Projects etc.

3. Download the latest version of the JDBC driver for SQL Server from Microsoft, available from http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774

4. Select a SQL Server instance to be used to hold the Team City metadata (2005 or higher). Mixed mode authentication should be enabled.

5. Create a database on the server to hold the Team City metadata. I suggest calling it TeamCity and using the Full recovery model. Ensure that the default collation of the database is set to SQL_Latin1_General_CP1_CS_AS this is generally NOT the default configuration.

6. Create a non windows account and give it full permissions over the TeamCity database created in step 5.

Installation

1. Run the Team City setup program as an administrator.

2. Install to the default directories and Select all features.

3. Select the option to install as a service. When prompted use the service account setup in Preparation Step 2 for Team City Agent & Scheduler (not the SYSTEM account).

4. Choose a default port for the Team City Agent. This will be used when you access the administration portal to view build progress and make configuration changes. For the purposes of this tutorial I will use 8083 which will make the address of the when viewed from the local machine http://localhost:8083/ 

5. You should be presented with a screen like the below. If the configuration looks correct click save.

image 

6. Select to run immediately upon completion of the install.

7. Open your web browser and navigate to http://localhost:8083/mnt (change port number if required) You should be presented with the following screen:

image

8. Click Proceed. Following this (if you do not click proceed the directory will not exist) Install the JDBC driver from preparation step 6 and place the drivers (sqljdbc4.jar & sqljdbc.jar) in the C:\ProgramData\JetBrains\TeamCity\lib\jdbc directory (assumes default installation paths).

9. When prompted to setup access to a database chose 'MS SQL Server' as the instance type. Set the connection properties and name of the database to the DB setup in step 5 of the preparation using the SQL account from step 6 of the preparation.

10. Read and accept the license agreement.

11. Create an Administrator account for Team City configuration and ensure that the credentials are stored securely for later use.

image

The next step is to connect TeamCity to your source control, detailed here: http://blogs.adatis.co.uk/blogs/jeoc/archive/2014/09/19/configuring-team-city-to-connect-to-visual-studio.aspx