Voice of the DBA

Author: way0utwest

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
SSoL: Adding disk space with a repartition

I don’t think most of us need to know Linux, but if you end up managing a system, it’s good to have a little idea of how to get around. This is a short series of posts as I remember the skills I used to have back in university.

While working with some SQL Server 2017 tests, I ran out of disk space. I tend to size my VMs around 40GB, and that works for some things, but I’ll run out of space.

I needed to expand the VMWare disk. That doesn’t mean Linux sees the space directly, and I had to figure out how to make the partition bigger. I could have added another disk, but I wanted to work through this process. I learned I needed to have an inactive partition, so I download gparted on a live cd and booted to that.

Next I started working through a few of the tutorials on Linux partitions. This one on AskUbuntu was helpful, as I ended up having to move my swap partition to the end of the disk.

GParted isn’t completely intuitive for a Windows guy, but I muddled through it.

Once I had the operations I wanted, I applied them, and partitions moved.

I rebooted, and checking the main partition properties, I had space.

Now, back to SQL Server on Linux.

August 1, 2017
The Second Best Job

I think I have one of the best jobs in the world. I get to run SQLServerCentral, focusing on the Microsoft Data Platform, writing articles, experimenting with technology, speaking at events, and more, all with a bit of autonomy. I’ve been doing this job for well over a decade, and I’ve built routines and rhythms that have allowed me to balance work, family, and life. I really enjoy my job with Redgate.

Redgate also runs Simple Talk, a companion site that seeks to educate and inform the community, just like SQLServerCentral. Simple Talk runs somewhat longer pieces and has fewer newsletters, but it’s a part of Redgate’s effort to give back to the community and help our current and future customers get better at their jobs. And now, we’re hiring a new editor for Simple Talk, which means the second best job in the company has an opening.

This isn’t necessarily the easiest job, and it does take a certain kind of person. I’ve told many people for years that almost anyone versed in SQL Server could do my job for a few days, but few could do it for a month. Or maybe, few would want to do it for a month. Producing new content on a regular basis, handling some of the tedious duties of an editor, these can be difficult tasks for many people over time. Even managing yourself (we need an independent self-starter) can be a challenge.

We have a great team in marketing, and Redgate is a fantastic company. The highest praise I can give them is that they have been the kind of company I’d want to build if I were to start a new venture. There are great benefits, you can work from home and work with autonomy. Plus, you get to work with me.

We need good technical skills, strong communication skills, and someone that wants to help grow the site even further. If you think this might be your kind of job, think about applying. Who knows, maybe the second best job could be yours.

Steve Jones

The Voice of the DBA Podcast

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

August 1, 2017
Auto DevOps

As much as I like DevOps, this scares me. There are a lot of employees (20%) at BMW that are software engineers. They want to increase that to a larger percentage, which I assume means that we’ll have more and more code in our automobiles, with more systems tightly integrated. We’re even gathering more and more data in modern vehicles. Maybe there are good database job opportunities for some of you with automakers.

If there’s one thing we’ve learned in a few decades of writing software, it’s that large monolithic systems are a mess. They contain vulnerabilities, and more importantly, they are very difficult to patch and change. All of the DevOps success stories I’ve seen from large systems have either been a complete rewrite, or a long term refactoring that removes dependencies and tight couplings between modules.

In cars, we’ve seen all sorts of hacking issues. Hackers can attack a vehicle remotely and a faulty (or hacked) update might leave you in trouble. Granted these things aren’t that likely, yet, though I think this piece understates the potential problems. I think that because once a hack is discovered, how sure are we that a) it will be reported to vendors (hackers might just exploit it), or b) that a fix will certainly be developed that works well and doesn’t cause any issues (remember 10s of millions of lines of code current year cars), and c) consumers will apply the patch. That last item worries me, especially if cars become more connected and share data about operation or as we move to autonomous (semi- or total) vehicles.

Personally I’m not against code in vehicles. I’m not even against some connected systems. What I am against is a monolithic, tightly coupled system. I don’t want engine control or drive by wire sharing a network or code with a CD player or navigation system. I don’t want one computer controlling vehicular functions, entertainment, and climate control. I also want to be sure that there is some protection for all this data, to be sure it doesn’t overwhelm any system. I’ll also admit I like idea of upgrading or replacing parts from different vendors, some of whom might do a much better job of building systems.

We’ve learned a lot about software and complex systems. While I don’t want all manufacturers to use one system or have tight regulations, I would like to see some mandates that separate critical systems from each other. Both in code, and physically. We don’t need to specify code standards, or encryption, or any details, but let’s ensure that a base bar of building software is being used in any of the mass produced vehicles, planes, trains, automobiles, or anything else of substantial size that is operated by the general public.

Steve Jones

The Voice of the DBA Podcast

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

July 31, 2017