Voice of the DBA

Category: Blog

  • The 2017 Data Platform Summit

    I’m off in a couple days to India for a week of vacation and then the 2017 Data Platform Summit. This is my first time attending, and I’m excited to go. A new country to visit and present in.

    Print

    In addition to the sessions listed above, I’ve got a pre-con on Aug 15 on Building a Database DevOps process, and there are a few chalk talks as well. All in all, a busy week for me.

    If you’re in the area, consider coming to the event. You can visit the conference site at http://dataplatformgeeks.com/dps2017/

    If you want to see the cast, check out this video: The 2017 Star Cast

    Follow the event on Twitter with the #DPS2017 hashtag as well. See you in Bangalore.

  • Checking Your Database Properties–#SQLNewBlogger

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

    I was reading Grant’s Database Fundamental Series on Database Properties, and it got me thinking. I think this is a good set of knowledge to have, but building on the properties, can you check them programmatically?

    You can, and here’s how.

    There is a function, DatabasePropertyEX(), that provides you a way to check properties.  You can use this with two parameters to check your database. These parameters are:

    database name – The name of the database, where you can use dbname() for the current database.

    Property name – These are a series of items to check a value for.

    As an example, one of the items Grant mentions is the recovery model. I can check that with this code:

    SELECT DATABASEPROPERTYEX(DB_NAME(), ‘Recovery’)

    In the current database, I get this:

    2017-07-27 14_31_24-SQLQuery8.sql - (local)_SQL2016.TestingTSQL (PLATO_Steve (52))_ - Microsoft SQL

    There are many properties I can check, and I can see a nice list here from SQL Prompt, or I can check the BOL page.

    2017-07-27 14_31_59-SQLQuery8.sql - (local)_SQL2016.TestingTSQL (PLATO_Steve (52))_ - Microsoft SQL

    As nice as it can be to pop open SSMS and look at dialogs, learn to check things programmatically. Once you can do that, you can start to let the system check and alerts you to changes.

  • 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.

  • 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.

    2017-06-15 10_09_14-Ubuntu 64-bit SQL Server .210 - VMware Workstation

    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.

    2017-06-15 11_32_50-Virtual Machine Settings

    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.

    2017-06-15 11_42_23-Ubuntu 64-bit SQL Server .210 - VMware Workstation

    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.

    2017-07-09 10_57_51-Ubuntu 64-bit SQL Server .210 - VMware Workstation

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

    2017-07-09 11_04_59-Ubuntu 64-bit SQL Server .210 - VMware Workstation

    Now, back to SQL Server on Linux.