Voice of the DBA

Tag: DevOps

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:

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.

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.

August 2, 2017
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.
July 26, 2017
DevOps Basics–Git log

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. This is also a part of a basic series on git and how to use it.

In a few previous posts I’ve looked at getting going with git, and in this post we continue by looking at how we can get some information about the actions we’ve taken.

If we want to see what has happened in our repo, lots of clients will show a list of changes, but from the command line we use a simple “git log”. When I do this, I see the reverse chronological view of commits.

There are a lot of options for the log command, but there are a few I use often.

Limit Entries

I often use a –n, where n is a number, to limit what’s returned. For example, I’ll use –3 to show the last 3 commits.

I also like the –p option, which will show differences. As you can see here, I added the UserRoles.SQL file, putting in new lines.

At times, I like the –decorate option, which lets me know which branch was affected. This is helpful if I’m moving around on branches and I get confused. That does happen.

There are lots of search options, and I use them at times, but rarely, so I’m usually searching for the documentation to know the dates or patterns. I do look at the –committer= syntax with my name. That lets me find my changes among others.

I also like to keep things small, so using the –pretty=oneline option is handy.

Now I can easily see what I’ve done lately.

There are lots of ways to look at history, and certainly a client makes things easier, but I’d say that you should learn the command line, just in case there’s some issue and your client doesn’t display it properly.

Last thing, when you run git log and end up with a colon prompt, you’re in the less utility (I think, been a long time since Unix). To get out just type:

q

July 19, 2017
DevOps Basics–Staging and Committing Changes

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. This is also a part of a basic series on git and how to use it.

In the course of normal work, you’ll change your code files. Git requires that you specify those changes that you want to commit, and those that you don’t. This means I can make changes to a few files, but not commit all those changes.

For example, let’s say that I add a couple files to my repo. I’ll add the Tables/Log.sql and Views/LogView.sql to my repo. This will give me a status in git that shows these are new files. In this case, I see the folders as they are new as well.

If I add these files as being tracked, I’ll use “git add Tables” to add that folder and file. I get a new status.

Here my Log.sql file is being tracked (and the folder) as changes that are staged to be committed. If I commit now, I’ll get just that file added, but not Views\LogView.sql.

Staged and Changed

There is one strange thing I’ve run into, at least, strange to me. If I stage my Views folder, I’ll get this:

Now I’ll change the LogView.sql file, removing the SELECT * and adding columns. When I check the status, I now see the file in both the staged and unstaged areas.

This is allowed, but if I commit, I’ll get the original version of LogView.sql as it existed when I ran the git add command. See below that after my commit, I still see the modified file.

If I add and commit that file, I can use git log to see the actual changes. See the line in red with the – is the original line, while the green line with + is the change.

Git add and git commit are the main ways you’ll commit files. If you have issues with a GUI client, then this is a good way for you to debug and clean up your repo. Knowing the command line is always the best way to truly understand what is happening.

June 29, 2017