Category: RedGate

Jan12201612 January 201612 January 20162Commentsby Gregor Suttie

Continuous Deployments for SQL Server – Part 4

Posted in Continuous Deployment, Pro Tips, Productivity, RedGate, Tools

Ok so we have seen in parts 1, 2, and 3 how to go about adding your database to source control, as well as comparing the schema and the data held within our SQL Server databases.

Its time to go about releasing changes made from one database to another (again think of you deploying changes from UAT to Production). There are several ways to go about releasing the changes, here are two of them:-

  • Script the schema and data changes as 2 separate scripts, which you can easily combine yourself.
  • Use Redgate SQL Packager and even create an .exe to run which will allow us to update the database.

Ok so let me demo how to go about using option two using Redgate SQL Packager which comes with the Redgate SQL Toolbelt.

Start up SQL Packager and you’ll see this start-up screen:-

packager1

I want to package an upgrade to a database so I have selected that option already, the next screen shows this:-
packager2

Above I have chosen my Database server and the database I want to use as the source and the target database (one we want to update).

Below shows the database objects I wish to package and apply on the target database.

packager3

Below shows the tables whose data I want to package and apply to the new database.

packager4

Below shows me the script that has been generated for me, first tab is the schema script, second tab is the data script and the third tab is for any warnings.

packager5

And the last screen gives us a choice to either package the change as a .exe, package it as a c# project, launch the script in SQL Query Analyser or Save the script for further inspection.

packager6

Choose option 1, run the exe and your database updates are complete, that’s all there is to it, any issues found the changes will be rolled back as they are transactional, leaving your database in tact.

Once complete, just run SQL Compare and SQL Data Compare and you can verify all is good – and viola, you’ve just updated production with schema and database changes, and you’ve been given a few different ways to do using RedGate SQL Toolbelt.


Jan0420164 January 201612 January 20161Commentby Gregor Suttie

Continuous Deployments for SQL Server – Part 3

Posted in Best Practices, Continuous Deployment, Pro Tips, RedGate, Tools

In part 2, I covered how to compare the schemas of two databases, in part 3 I’ll to cover how to compare the data held within these two databases, for that I am going to use SQL Data Compare.

Again we will use the same two databases, imagine your comparing UAT to Production and wish to compare the data within these two databases:-

sqldatacompare1

sqldatacompare2

(If the links are too small click on the image and then when the new window appears click on Original size at 1896 × 316)

The above image shows us that the t_depot database table has 4 rows which are in the Stock_DB_Deisgn table which aren’t within the STOCK_DB_DESIGN_ORIGINAL database. Again same as with SQL Compare, if we now click on Synchronization wizard we will see the following screen:-

sqldatacompare3

And again as with SQL Compare, we get 2 options:-

  • Create a deployment script – use this option if you want to script the changes and review before running in the changes.
  • Synchronize Using SQL Data Compare – use this option if you want the tool to make the changes for you, you also get the option after its ran to automatically compare the 2 database data after the changes, to verify.

And that is it, easy, simple and straightforward, no manual steps involving creating scripts, no real chance for it to go wrong, plus once again you can source control the script if you really wanted to or share it with colleagues.

In part 4 I’ll cover how to deploy changes to your database whether its schema changes, data changes or both.


Jan0420164 January 201612 January 20162Commentsby Gregor Suttie

Continuous Deployments for SQL Server – Part 2

Posted in Best Practices, Continuous Deployment, Pro Tips, Productivity, RedGate, Tools

If you’re using SQL server at work and manually deploying changes to your database using manually crafted scripts then its time to stop, there is a better way to do this by automating it, remove the chance of human error, this will also ensure that before you deploy to production that your changes will work, guaranteed.

In this blog post, I’ll discuss comparing two SQL Server databases which you can use SQL compare to funnily enough compare them and see the differences between the two, very quickly and reliably, imagine you wanted to compare your local Development database with say your Staging database for example, or compare UAT to Production.

In this blog post I’ll go over the steps I went through and how to use Sql Compare to work out what’s changed between the two databases, schema wise, then in the second part I will show you how to use SQL Data Compare. to compare the data within both databases and in part 3 I will either create a script to update the database or run an exe which the tools will create for us to make both databases the exact same, both in schema and in data, so let’s get started.

I’ve made dome schema changes to STOCK_DB_DESIGN and I wish to see whats changed, so lets see how to compare the 2 databases using SQL Compare (I’m using version 11).
sqlcompare4

In the screen shot above I’ve chosen my databases to compare and the tool will now run and show me what the schema differences between the two. the screen shot below shows the results after the tool has been ran against both databases.

sqlcompare5

On the left pane we see the changed database, on the right the database without these changes, I clicked on the first row to show the changes in the t_depot table and in the split at the bottom it shows the differences per object – very quick and easy to see what’s changed.

Update time
In order to update the older database with the new changes, we simply click on Deployment Wizard at the top and we get the following screen with options:-

sqlcompare6

Here we get 2 options:-

  • Create a deployment script – use this option if you want to script the changes and review before running in the changes.
  • Deploy Using SQL Compare – use this option if you want the tool to make the changes for you, you also get the option after its ran to automatically compare the 2 database after the changes to verify.

And that is it, easy, simple and straightforward, no manual steps involving creating scripts, no real chance for it to go wrong, plus you can source control the script if you really wanted to or share it with colleagues.

In part 3 I’ll cover comparing the 2 databases when it comes to data, for this we will use SQL Data Compare.


Jan0420164 January 201612 January 20161Commentby Gregor Suttie

Continuous Deployments for SQL Server – Part 1

Posted in Best Practices, Continuous Deployment, Pro Tips, RedGate, Tools

Okay so your using a SQL Server database at work but you haven’t yet put the database into Source Control, here are some reasons as to why you want to do this:-

Source control your database
Trust me your going to want to do this if you haven’t already, it’s a good practice, it may save your skin some day, yes you can take backups which again is a good practice, here is an excellent article on why you want to do this:- Why put your database into source control?.

Okay now that we wish to put our database into source control – we still use Subversion at work so I downloaded Subversion and installed it locally as well as TortoiseSVN.
I have 2 databases called STOCK_DB_DESIGN and STOCK_DB_DESIGN_ORIGINAL within SQL Sever, the former had a few schema changes, imagine this is Development(STOCK_DB_DESIGN) and Staging(STOCK_DB_DESIGN_ORIGINAL).

I created a local repository within Subversion and downloaded RedGate’s Database Lifecycle Management Products.

This tool comes with SQL Compare and SQL Data compare as well as a host of other awesome SQL Server tools which you can find out about at the above link.

For this post I’ll be using Redgate’s SQL Source Control which adds a tab to SQL Server Management Studio and looks something like this:-

sqlsource1

So lets go ahead and add a database to our source control using this tool, Select the option above, and then Next

sqlsource2

I selected Subversion because at work we still use it, but you can use Git also and others, click Next

sqlsource3

I then give it a repository URL, I already created a repository within Subversion for my database and added the URL like above, click Next and that’s it, our database is now linked to source control. Now to get the database scripts into Subversion we need to right click on the Database in question within SQL Server Management studio like so and select Commit Changes to source control:-

sqlsource5

This will then add all of the database objects to SQL Source Control, once that’ completed, go into windows explorer and I use TortoiseSVN I can right click within my local SVN repository folder and then select Checkout or get latest version and this will result in the following folders being populated:-

sqlsource4

And when I go into say the tables folder I’ll see the following:-

sqlsource6

And that’s all there is to source controlling your SQL Server database. If I make change to an object say a table I’ll see whats changed locally in SQL Management Studio and I’ll know I still need to commit the change as seen below:-

sqlsource7

In Part 2 I’ll show you how to compare schemas on both databases to work out what changes are required, and how to script them using the tool.


Jan0120161 January 20161 January 20160Commentby Gregor Suttie

My todo list for work in 2016

Posted in Continuous Deployment, PowerShell, Pro Tips, Productivity, RedGate, Testing

Its 2016 and I like to make a list of things I wanna look into and put into place at work so here is a list of things I am aiming to do which also includes at my job in 2016:-

  • A blog post each week, last year I only managed 11 blog posts and that’s poor, so more blog posts will come in 2016.
  • Lightning Talks, we have started doing these at work and this year I plan to do a few of them if given the chance, I’m doing one on the 6th of January on as Developers Top 10 Best Practices which I will share about once I have done my talk.
  • Test 3rd party end points, have a dashboard page which tells us what is up and what is down, going to try to use the Chrome add-on called Postman and use collections within Postman to do this.
  • Database Deployments, currently we manually script everything and then get the dba to run the scripts in manually, we need to script the database and put it into Source control each release as a starting point, I’m looking forward to this as it well help aid with our deployments and speed them up. Hoping to get the RedGate Sql Toolbelt into the company so we can use this to help us achieve this.
  • Red/Green deployments, we currently deploy at weekends and this can and should change so that we aren’t spending time at weekends doing releases which take quite a lot of time, we can automate them more and this year I plan to fix that.
  • More Testing, we are closing in on unit testing our PowerShell scripts, this will be another nice addition to the number of different areas which we are currently testing which is great.
  • Code Reviews, need to figure out a way that keeps everyone from being bored, brings benefit to the team and keeps us developers on our toes going forward.
  • Continuous Improvement, test more, more in-depth code reviews, automate more, release finished work, tackle the back log each sprint.

That’s it for now, I will add to this lost throughout the year as we go, i will keep an eye on this post and how we get on and blog about each one individually, hopefully I’ll get the chance to work on some if not all of these this year.

Feel free to follow me on twitter at @gsuttie


Jul27201227 July 201228 July 20120Commentby Gregor Suttie

What I learned last week – July 23-27th

Posted in jQuery, RavenDB, RedGate, TeamCity

This is the first in a series I am going to do on what I learned last week – it will cover what I’ve been up to and what I have read and learnt throughout the week – ok let’s get started.

Found a really nice sample on how to notify users when an action on your site has taken place – say you save a record or delete a record, perhaps you need to show a validation message, maybe even a banner at the top that has to stay there,

Take a look at the oh so simple to use JQuery plugin called Notify.

Awesome Stuff

  • Visual Studio 2012 is really nice to use! – you should take a look now if you havent already.
  • Shout out to the guys at RavenDb highly recommend it, also note its FREE until you need to release to production, so if you’re doing demos, needing a quick win on a project – take it for a spin.
  • Resharper 7 is out – if you’re not already using this then do yourself a favour, why walk when you can run, go try it now Resharper 7.
  • Redgate have brought out a plugin for setting up a database build in TeamCity Redgate TeamCity Plugin

Please leave a comment after the beep.


Sep13201113 September 201130 September 20111Commentby Gregor Suttie

How to Source Control your SQL Database

Posted in Pro Tips, RedGate, Tools

In this blog post I am going to show you how to add a SQL Server database to Subversion using Redgate SQL Source Control, the tool claims you can add your database to a source control provider such as Subversion in about 5 minutes – lets see whats involved.

I have Subversion running on my own windows 7 pc at home, I use it for testing out tools and integration with CI and so on, in future blog posts I will cover more on Continuous Integration.

I’m going to start by adding an existing database into Subversion using the SQL Source Control tool with screenshots so you can follow. I currently have Subversion installed and running on c:\svn\trunk\

Below is a screenshot of my local SQL Server

screenshot1

screenshot1

SportsStore is a local database used within one of the MVC books I have, lets add this database to Subversion using the tool now.

Create a New Folder which needs to be empty within your local repository folder, in my case my subversion folder is c:\svn\trunk\ – this is where your database scripts and data will be where your scripts in source control will be locally.

screenshot3

screenshot3

Highlight the database on the left and then select the blue text on the right which says “Link database to source control…”

The following window will pop-up, here enter the url to your repository, since mine is local I just need to add c:\svn\trunk\SportStoreDB (this is the folder your db scripts will go into), click go.

screenshot2

screenshot2

You should see the following :-

screenshot4

screenshot4

At this point the tool has added all the scripts including the data for all your tables to Subversion – now we just need to get this from Subversion into our local folder – to do this right-click on the folder and choose SVN Checkout and then click ok as below

screenshot5

screenshot5

This now leaves us with the entire SportStore database having been scripted and placed within your subversion repository and you now have a local copy – perfect and easily accomplished.

Below are the screenshots of the output from the tool within your Subversion folder.

screenshot6

And a view of the stored procedures folder with created scripts for you.

screenshot7

For more on this great tool check out Redgate SQL Source Control.


Dec17201017 December 201013 April 20110Commentby Gregor Suttie

Redgate SQL Prompt

Posted in RedGate

Redgate SQL Prompt which is one of their tools I use on a daily basis – it provides intellisense for writing SQL queries as well as a lot more – it has some very nifty options for splitting tables, smart rename which you can use to rename a table column for example and it will do this for all objects which reference the column changed.

Format SQL does just what it says on the tin – it formats your SQL based on formatting options that you as the user define.

Find Invalid objects looks through your database and will show you any objects which have became invalid – say a column is renamed by a user but they forget to changed the stored procedures that reference the old column name – this tool is your new best friend.

These are just some of the options available in SQL Prompt – the number one thing going for this tool is productivity – you can write SQL queries much faster.

For a 14 day free trial:- http://www.red-gate.com/products/sql-development/sql-prompt/