Category: Blog

Create a BACPAC–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

One of the things that you might encounter at some point is the need to create a BACPAC. This is essentially a DACPAC with data included. There are times you might find a BACPAC more convenient than a full backup, especially if you work with Azure.

This post shows how you can create a BACPPAC file (with the extension .bacpac) from SSMS.

I have a small database, the PartsUnlimited database, from the Microsoft PartsUnlimited demo application. I want to create a BACPAC from this, however, when I right click and select Tasks, there is no “Create BACPAC” option.

That’s fine, because the “Data Tier Application” is the DAC, and an export includes the data, which creates the BAC part of the process. Let’s choose “Export Data-tier Application”. Once we do that, we get the expected summary wizard screen to start.

I click next and immediately get prompted for a location in which to save the file. The will include all your data, so choose a location that has sufficient disk space. You have the option to also store this in Azure if needed.

Note the filename above is already filled out by default, but you can change this in the edit box. If you click the “Advanced” tab, you’ll get the chance to select only certain tables if that is required. You can see this dialog below.

Once you’ve chosen a location, you click next and get a summary page.

Click finish, and the process starts. This runs very quickly, extracting the schema and then all the data, noting the results of each object.

That’s it. If I look in the location, I’ll see my BACPAC file.

I can copy this to another machine and import it to recreate a database. We’ll do that in another post.

SQLNewBlogger

A short, quick post. I’ve done this before, but I had to do this for a quick process and took 5 extra minutes to take screen shots, spending 5 minutes later writing this up.

March 15, 2017
The Livestream Product Launch

SQL in the City Streamed in 2016 was a success, with lots of feedback that people enjoyed the sessions and format. With the release of SQL Clone, we’ve decided to do it again.

The SQL Clone Product Launch Live Stream is coming in a couple weeks, on Mar 29, live from Cambridge, UK. I’ll be traveling there, along with Grant Fritchey, and we’ll both be helping broadcast all day, repeating sessions across many time zones.

Join us and see if SQL Clone is right for you. Or download an eval and give it a try.

March 15, 2017
T-SQL Tuesday #88 – WTF

It’s T-SQL Tuesday time again, and this is a good one. The host this month is Kennie Pontoppidan, who I had the pleasure of running with one morning in Copenhagen. His theme is The daily (database-related) WTF.

You can read about T-SQL Tuesday at tsqltuesday.com and see all the archived invitations. Join in the fun by watching the site, or the #tsql2sday hashtag on Twitter. If you want to catch up on old invites, feel free.

WTF

I’m not sure if I have a great story, but I certainly have had a few “WTF” items in my career. Some of them caused by me, some by others, and often not a good thing.

The one item that I’ll relate here goes back quite some years in history, but it was one that affected me for many years in my career. I do quite a bit of work with DevOps and software development these days, trying to help people build better software.

Part of that is ensuring your code, including your database code, is in a version control system (VCS). I’ve worked in a variety of environments, using different source code control methods, and I have to relate a story that taught me how important it can be to manage code and track the versions in production.

A long time ago I went to work for a small startup. The company had been in business for 5 or 6 years, and had a number of customers that depended on our service. However, our developers and admins were a mess. I was brought in to try and straighten out the the database and infrastructure side of our codebase.

One of the first things I found was that all our stored procedures in the production server were encrypted. I wasn’t sure why, since we hosted our machines, but that wasn’t a big deal.

Until it was.

One day we had an issue on one of our SQL Server 2000 servers (we had two, supposedly identical). In troubleshooting and putting some sample data in both systems for a fake customer, we got different results. Hmmm, not what I wanted to see.

I checked the VCS (SourceSafe at the time) and checked out the code. I then loaded my test data and … got a third, different result. Now I was concerned as this was a production bug that was delaying work for a customer.

This was early in my tenure at the company, so I asked the other DBA for some information. He had been passed over in favor of me by management and wasn’t a great deal of help. He told me that he likely had the source code for server 1 on his machine.

You might start to guess why this is a WTF post. Rather than get upset, I wanted to see the code, which he produced. His version was one one of the servers, but not the other. Where was that code? He thought it was on the lead developer’s machine, who conveniently, was on vacation. We checked, and it wasn’t in his checked out folder structure.

What about our former developers, who had moved out of state? We still had their machines, which were supposedly going to be shipped to them so they could continue working for us. The checkout folder wasn’t right, but we found 2 other folder structures, copies of the source code tree, with different code. At this point, I just needed to fix the issue, and managed to find a copy of the stored procedure that matched my issue, built a correction, and deployed it to both servers. Unencrypted.

Detective Time

At this point, I paused some work that the DBAs were assigned. We spent a day or so pouring over all developer’s machines, finding 6 or 7 different copies of source code (plus our SourceSafe install). Multiple files were different, and we essentially had no idea what was running in production.

Apparently our developers had decided that when they checked in code, they would leave it on their machines. If they wanted a branch, in essence, they’d copy this folder over, make changes, and sometimes copy things back, but mostly just assume they knew what was good, and leave the updates on disk, uncommitted to the VCS.

I couldn’t come up with a rational reason for this. Back then, we didn’t have any sort of integration like SQL Source Control and we checked code in and out of the VCS manually. I had previously set all my machines to remove the code from my local disk, ensuring I’d go to the VCS to get code when I needed to change something. I suspected this was one way of creating ”job security” by a few developers and DBAs. Hint: it didn’t work with me.

Solving the Issues

This necessitated a complete reboot of our system. Together with a new lead developer (you can guess where the old one went), we paused development for 3-4 days. I found a routine to decrypt SQL Server 2000 stored procedures, and we all spent time decrypting the stored procedures from both instances and committing them back to a new tree in SourceSafe. If we had an discrepancies, then we had at least two developers examine the code, merge the differences and commit code.

After a week of lost work, we had a clean source code tree of what was running in production, and both database servers synchronized. From there, we could start new development. Learning from the issues, I also would only deploy changes from the VCS (manually back then), which ensured the developers had an incentive to commit their changes and not just alter the database objects on the development server.

Of course they still did, and we had numerous deployment issues. If only we’d had a few better tools back then for CI and CD in the database world.

March 14, 2017

Using tSQLt to Find Min/Max Times

I love tSQLt. It’s a good way to write tests that can determine if your code is actually working. Since I’m a fan of unit testing, I think using tests to verify your logic is great. What’s excellent with tSQLt is that I can verify a number of cases at once.

I ran across this post asking for help with a query. Given the sample data and results, I wrote this proc and test. In the test, my “Act” is calling a proc I wrote that executes the first post’s query.

CREATE OR ALTER PROCEDURE RunTimeTests
AS
BEGIN
    SELECT
        Taskid,
        MIN(StartTime),
        MAX(EndTime),
        DATEDIFF(MINUTE, MIN(StartTime), MAX(EndTime))
    FROM TimeTests
    GROUP BY Taskid;
END;
GO
EXEC tsqlt.NewTestClass @ClassName = N'tTimeTests'
GO
CREATE OR ALTER PROCEDURE [tTimeTests].[test calculation min max time from timetests]
AS
BEGIN
    -- assemble
    EXEC tsqlt.FakeTable @TableName = N'TimeTests', @SchemaName = N'dbo'

    INSERT into TimeTests
        VALUES 
        (1, '2017-02-23 09:48:47.413',NULL ),
        (1, '2017-02-23 09:50:47.413', '2017-02-23 10:59:47.413' ),
        (1, '2017-02-23 09:49:47.413',Null ),
        (2, '2017-02-23 10:40:47.413','2017-02-23 11:55:47.413' ),
        (2, '2017-02-23 10:39:47.413', NULL ),
        (2, '2017-02-23 10:11:47.413','2017-02-23 11:30:47.413')

    CREATE TABLE tTimeTests.Expected
    ( taskid INT, Mindtime DATETIME2(3), maxtime DATETIME2(3), Minutes int)

    INSERT tTimeTests.Expected
     VALUES (1, '2017-02-23 09:48:47.413', '2017-02-23 10:59:47.413', 71)
          , (2, '2017-02-23 10:39:47.413', '2017-02-23 11:55:47.413', 76)   

    SELECT *
     INTO tTimeTests.Actual
      FROM tTimeTests.Expected
      WHERE 1 = 0;
    -- act
    INSERT tTimeTests.Actual EXEC RunTimeTests;

    -- assert
    EXEC tsqlt.AssertEqualsTable
     @Expected = N'tTimeTests.Expected', @Actual = N'tTimeTests.Actual', @Message = N'Incorrect times'
    
END

When I run this, it easily verifies the answer that the data is incorrect from the poster.

If I change my expected results:

    INSERT tTimeTests.Expected
     VALUES (1, '2017-02-23 09:48:47.413', '2017-02-23 10:59:47.413', 71)
          , (2, '2017-02-23 10:11:47.413', '2017-02-23 11:55:47.413', 104)

and re-run the test, it succeeds.

Now, does this mean the developer wouldn’t make this mistake? After all, if you think you should be getting those results, you will struggle with the query.

It doesn’t help there. However, it does help if you modify this code later and start to have strange results. This also means that I can add in more rows to the data, even more cases, and determine if the procedure still works. If I’m trying to cover a dozen cases, it’s much easier to re-run a tSQLt test than manually looking through results.

Give tsqlt a try. It’s free, and if you have the SQL Toolbelt, you can get a GUI with SQL Test for executing your tests.

March 13, 2017

Category: Blog

Create a BACPAC–#SQLNewBlogger

SQLNewBlogger

The Livestream Product Launch

T-SQL Tuesday #88 – WTF

WTF

Detective Time

Solving the Issues

Using tSQLt to Find Min/Max Times