Voice of the DBA

Tag: DevOps

  • Database Development Made Easy

    I ran across this post on developing database with SSDT. It has a lot of steps, and reading through it, I find this to make some sense, but I’m not sure I think this is easy. I can see why developers find databases to be a pain to work with. There are a lot of steps in this post to just setup and configure a database project. Databases are fundamentally hard to work with, as the model of maintaining state between changes and ensuring data is not lost can be hard. While the concept is similar to keeping track of configuration files, the scale of data in a database is vast and ever changing. Tooling to manage data that might need to be recovered isn’t very practical.

    I ran across a developer that was trying to automate their database development. They used tooling to deploy a table to a production system. The application connected and data was stored in the table. The developer then dropped a column from a table and deployed this change. It worked, but this was a mistake and this person decided to deploy the previous version of the database, with the additional column restored. The deployment worked, but there wasn’t data in the column that had been dropped, and added back. Why not, asked the developer?

    Many people are of two minds here. One, any tooling or automation should preserve data and allow for rollbacks. In the application world, this makes perfect sense, and even the data our application uses (reference files, configuration files, etc.) are restored if we rollback to a previous version.

    For the data people, this makes no sense. The data in a column could be of significant size. We often plan for systems to reach millions (or more) rows of data, and trying to save the state of this data before a change isn’t practical. Even if we were to store changes items for a few deployments, it’s entirely possible that putting the data back wouldn’t make sense as related information in other tables might not match up correctly. Consider the case of a financial system and restoring old money values. Who knows what issues would be created?

    For developers, this highlights one of the things they dislike about databases. They are must manage state transitions across deployments, and rolling back to previous versions isn’t often possible. This is one reason that I have often performed a backup before major deployments, and even today, in an automated DevOps process, I’d want to perform a backup if any significant data were being changed or deleted.

    I’ve spent a lot of time advocating for DevOps and smoother, modern database development practices for the last few years. I don’t want the database to be a hindrance or impediment to change, but I also don’t want to compromise the integrity or safety of data. My pitch has always been that our database automation tools at Redgate don’t perform any magic. They smooth, and hopefully speed up, the process of making database changes that we’ve used for decades. They save you time and effort, just as other tools may do, but they can’t change the rules of relational database changes.

    Everyone developing code inside or connecting to a database needs to understand how transactions and data changes work. There are rules and restrictions the protect our data. These mean we need to sometimes plan and consider the consequences of our actions. This should also mean that despite wanting to move faster and make changes, we can’t treat data placed in columns as malleable in the way a method in C# can be changed back and forth. We need to account for, and protect, the information stored in our systems. There are patterns that can help you evolve your database from one state to the next, but there isn’t any magic that lets you drop and add data storage elements without some preparation for handling the data itself.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 5.1MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Use More Pull Requests

    I noticed a short while back that Books Online is on github. You can fork the code and make corrections to pages and then submit a pull request. This is the model that many OSS projects use, including the amazing DBATools project. That one I really like, and if I was better at PowerShell, I’d contribute. If you’re a PoSh whiz, I’d urge you to contribute.

    A few years ago I saw a lot of complaints about the SQL Saturday site, and over the years I’ve also seen a number of complaints about the Pass Summit sites, and the registration process. At one point there was a debate over whether PASS should build a registration system or continue to pay for the service. To me, this was a perfect place to start a project, maybe with a few volunteers, and then crowdsource enhancements and improvements. Get help from the members of this technical community, after all, this is the way most of us make a living.

    If Microsoft can get help from outside, I’d expect other organizations could as well. Certainly PASS has limited resources, and this is a great way to perhaps get new ideas, innovate, and grow their system. There will be some friction and loss in reviewing changes, but I’d expect that the overall gain would come from the greater number of people contributing to the software.

    We’ve kicked this around for SQLServerCentral and Database Weekly, and I am hoping to start getting help in enhancing my own site from its community at some point. There are certainly challenges and difficulties in integrating code from lots of sources. DevOps makes this easier, with more automation to evaluate and test code before a human needs to review it. However, DevOps has its own challenges, with extending the culture and process to others, and finding those individuals that want to contribute and buy into the philosophy, not to mention maintaining code quality and standards. However, I hope that you consider using pull requests and getting help from others in your own organization or project.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.0MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • MSBuild and Azure SQL Database

    I saw a report of a problem building a database with ReadyRoll in Azure SQL Database. This person wanted to use a local Shadow database in LocalDB and target an Azure SQL Database. I hadn’t build that config, so I decided to give it a try.

    Update: ReadyRoll has become SQL Change Automation,and this no longer applies. Contained users are supported with SQL Change Automation projects, which will solve this issue.

    I ended up with this:

    2017-07-20 10_56_01-builds_azure-CI summary

    which was making me a little crazy. I’ve had most builds work really well. I tried a number of things, but kept getting a few items in the build. There were login errors or network errors, both of which bothered me since I could manually log in with SSMS from the same machine as my build agent.

    I suspected a few things here, one of which was the use of named pipes for the Shadow database and TCP for Azure SQL Database.

    Eventually, I decided to fall back with msbuild, ignoring VSTS, and make sure all my parameters were correct. I started here:

    C:\Windows\Microsoft.NET\Framework\v4.0.30319\msbuild builds_azure.sqlproj /p:TargetServer=”dkranchapps.database.windows.net” /p:TargetDatabase=”SSBuilds” /p:TargetUsername=”dlmdeploy” /p:TargetPassword=”astrongpassword” /p:ShadowServer=”(Localdb)\ShadowSSBuilds7″ /p:GenerateSqlPackage=True /p:SkipDriftAnalysis=True /p:ShadowUsername=”shadowuser” /p:ShadowPassword=”someotherpassword”

    I promptly got a VS build started and then this error:

    C:\Program Files (x86)\MSBuild\ReadyRoll\ReadyRoll.Data.Schema.SSDT.targets(513,5): error : An error occurred while attempting to
    create a patch script: Login failed for user ‘dlmdeploy’. [E:\Documents\Visual Studio 2015\Projects\builds_azure\builds_azure\buil ds_azure.sqlproj]
    Done Building Project “E:\Documents\Visual Studio 2015\Projects\builds_azure\builds_azure\builds_azure.sqlproj” (default targets)
    — FAILED.

    I verified the password in SSMS, verified the firewall and tried again. Same issue. Then I tried this:

    > sqlcmd -S dkranchapps.database.windows.net -U dlmdeploy -P “AStrongPassword”
    Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user ‘dlmdeploy’..

    Hmmm, that’s interesting. Why would this work in SSMS and not SQLCMD? My first thought was some driver setting, maybe ADO v ODBC, but that seems silly.

    Eventually I suspected some mismatch in databases, and found this post. It confirmed what I was about to test. I needed the same login/password in the master db as in the particular db I was using.

    I used SSMS and connected to master to create a user there.

    2017-07-20 11_06_42-SQLQuery5.sql - dkranchapps.database.windows.net.master (sjones (112))_ - Micros

    Once I did this, the build ran fine, both in the command line and in VSTS.

    A couple lessons here. First, in Azure, you connect to databases, but since some processes (like MSBuild) might connect to a default, you need the user in your db and in master. If you have multiple databases (as I do), I’d suggest separate accounts for building in each db.

    Second, work with the command line first. That’s the key. Once you have things working from there, it’s easy to move to a tool and automate your command line instructions.

  • DevOps–Fixing Poorly Named Constraints

    I was building some code the other day and kept getting problems in my deployment for a change. The deployment was having issues, and this came down to this statement.

    ALTER TABLE EventLogger DROP CONSTRAINT [PK__EventLog__5E548648B043C0BC]

    The problem was that this was the constraint on one developer’s workstation, but on another laptop, and in QA/Staging/Production, this constraint didn’t exist.

    When we deploy to other environments, such as QA and Production, we will always see the wrong constraint, as most deployment mechanisms look at the name of the object, not the function. Every upgrade script will typically try to run the above statement and then run an ALTER TABLE ADD CONSTRAINT later to add the PK back.

    If we have the correct name of the constraint in QA, the script will work. However, the name is likely different in each environment, so we need to fix this.

    We can find the name of the PK with this script:

    SELECT 
    A.TABLE_NAME, 
    A.CONSTRAINT_NAME, 
    B.COLUMN_NAME
FROM 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS A, 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
WHERE 
       CONSTRAINT_TYPE = 'PRIMARY KEY' 
    AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND A.TABLE_NAME = ‘EventLogger’

    If we take the results of this, we can use this to produce a drop script. Here’s one way to do this. We’ll store the name of the constraint in a variable and use the EXEC() statement to execute some dynamic SQL. We then can execute the ADD CONSTRAINT with a new name later in the script.

    DECLARE @s VARCHAR(200)
SELECT @s = A.CONSTRAINT_NAME
FROM 
     INFORMATION_SCHEMA.TABLE_CONSTRAINTS A, 
     INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
WHERE 
        CONSTRAINT_TYPE = 'PRIMARY KEY' 
     AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME

AND A.TABLE_NAME = 'EventLogger'

EXEC('alter table EventLogger drop constraint ' + @s)
GO
/*
Other work
*/
ALTER TABLE dbo.EventLogger ADD CONSTRAINT EventLoggerPK PRIMARY KEY (LogId)

    This is the type of DevOps change that I would release a table at a time, slowly cleaning up the constraint names. This will smooth your process and increase the reliability of your deployments.