Voice of the DBA

Category: Blog

  • Maturing Your Database Development Process–Version Control

    At Redgate Software, we have a progression of the stages of a database development pipeline. These are the various ways in which you can better engineer your database development to ensure smoother releases to production, with less issues. There are five stages:

    • Manual (S0)
    • Source Control (S1)
    • Continuous Integration (S2)
    • Release Management (S3)
    • Monitoring (S4)

    As I travel around, speaking on these topics, I find many people working in development stops that are really at the S0 level.

    For databases, that is. For their .NET or Java or PHP software, quite a few are at S2, and maybe working towards S3 in many projects.

    That’s a disconnect, and it’s one that we’d like to see changed at Redgate. Certainly we can help and we’d like you to use our products, but more, we want to see better development all around.

    We’re Trying to Help New York City

    In a few weeks, on August 27, 2015, I’ll be in New York to help teach our Database Source Control workshop. Ike Ellis (Crafting Bytes) is teaching the class, and I’ll be there to support him and run the labs. This is a course that Grant Fritchey, myself, and a few others at Redgate Software have built.

    This is the chance to learn how your organization can implement version control for your database, just as most of your developers probably already have for the other software you write.

    We’ll cover setting up Source Control, deploying changes from versions, branching, merging, and more. This is a great hands-on introduction to stabilizing your database development. We’ll provide a VM with labs that you will actually complete.

    We’ve put this first step to building a DLM pipeline on sale for $100. If you’re close to NYC, consider taking a day off and joining us at the Microsoft office in Manhattan for a little database education.

    If you can’t make this workshop, look through our schedule and join us somewhere at a future time.

  • Loading XML Data–CONVERT Option 2

    I was playing around with some XML lately, and had to load a file that looked like this::

    2015-07-22 14_40_59-mathis.xml - Notepad

    I ran a simple query, one that used the OPENROWSET and a CONVERT to load the data. 

    WITH XmlFile (Contents) AS (
SELECT CONVERT (XML, BulkColumn) 
FROM OPENROWSET (BULK 'C:\mathis.xml', SINGLE_BLOB) AS XmlData
)
SELECT *
FROM   XmlFile
GO

    However, that didn’t work. I received this message in SSMS.

    Msg 6359, Level 16, State 1, Line 1

    Parsing XML with internal subset DTDs not allowed. Use CONVERT with style option 2 to enable limited internal subset DTD support.

    Hmmm. That seems to make sense. Let’s add an option to CONVERT of 2. I’ve assumed the last parameter is the one mentioned, as with date conversions, and added that. I can hover with SQL Prompt and see that.

    2015-07-22 14_43_13-SQLQuery1.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (71))_ - Microsoft SQL Server

    Let’s change the code:

    WITH XmlFile (Contents) AS (
SELECT CONVERT (XML, BulkColumn,2) 
FROM OPENROWSET (BULK 'C:\mathis.xml', SINGLE_BLOB) AS XmlData
)
SELECT *
FROM   XmlFile
GO

    That works.

    Why?

    If you go to the BOL page for CAST and CONVERT, you will find an XML styles section. There are possible values of 0 (default), 1, 2, and 3. In this case, the 2 enables an internal DTD processing, which basically uses a default document of sorts for parsing the XML. No external DTD is needed and this is treated as a standalone document.

    I am not an XML expert, but I’m guessing here that I’ve included a document that doesn’t conform to some specification and the additional style parameter allows SQL Server to ignore some of what’s there.

    If anyone knows more, I would like to better understand how this works.

  • Connecting, Saving, Discarding, Branching, and Merging in the Big Apple

    We’ve got another DLM workshop on Database Source Control taking place in New York City on August 27. It’s in Manhattan, at the Microsoft office all day, with lots of hands on labs to help you learn a number of skills:

    • connecting your database to a VCS with SQL Source Control.
    • discarding changes and recovering an older version of your code from a VCS.
    • branching your database code
    • merging changes from branches together.
    • using migration scripts for custom code changes and preserving these in your VCS.
    • and more

    Register today, and spend a day with Ike Ellis and myself  learning about database version control. The class is only $100 for a limited time, so make plans to join us.

    You’ll need to bring a laptop, but we’ll provide a VM ready for you to practice the skills on, and we’ll provide the food and refreshments for the day.

    Using a Version Control System is essential for application developers, and it should be for database developers as well. Learn how you can start to ensure your database code is protected and versioned, just like all your other code.

  • Start SQL Server in Single User Mode

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

    I saw someone recently ask how to reset the sa password, and myself (along with a few others) suggested starting SQL Server in single user mode as an administrator. The poster had problems and at one point I suggested using the command line, which I had used in the past. However that didn’t’ work,  and when I tried it myself, it gave me errors.

    The errors were Operating System errors, which indicated that the errorlog location wasn’t accessible by me. So I decided to elevate my privileges.

    2015-07-06 13_48_41-Start menu

    Once I did that, I was able to get the service to start:

    2015-07-06 13_52_27-Administrator_ Command Prompt - sqlservr -m -s sql2012

    I’m not sure what was hard here, and this is how I’ve always managed to start and stop SQL Server in single user mode from the command line. I then see the output of what is sent to the error log at the console.

    However I also searched around and found a few other ways that are easy to accomplish.

    Net Start

    I haven’t often used net start for commands, but I have a few times. In this case, I looked at BOL and found I could do this:

    2015-07-06 13_43_20-Administrator_ Command Prompt

    This is essentially what clicking "start" in the services applet or Configuration Manager does. However I can add in my "m" parameter with a slash (/) instead of a dash.

    To stop the service, I use NET STOP.

    2015-07-06 13_43_44-Administrator_ Command Prompt

    Configuration Manager

    If you run Configuration Manager, you can also add parameters. First, right click the service and get the properties. Then you want the "Startup Parameters" tab. In there, you can add a parameter in the top box, as I’ve done here.

    2015-07-06 13_44_19-SQL Server (SQL2012) Properties

    Clicking "Add" will put it in the list.

    2015-07-06 13_44_26-SQL Server (SQL2012) Properties

    When you stop the service, the next startup will have this parameter take effect.

    2015-07-06 13_44_06-Sql Server Configuration Manager

    Beware that when you start things up, the first connection that successfully logs into SQL Server will be the only one allowed. Any applications looking to connect, monitoring programs (SQL Monitor or DLM Dashboard) or other clients can take your connection, so be careful and quick.

    SQLNewBlogger

    This was really a quick writeup. I stopped my service and played for 10 minutes, ran a few searches, and took some screenshots. I would have been faster, but I wanted to document this.

    This is great practice for a skill you need rarely, but when you do, you’ll be stressed. Be sure you can start and stop SQL Server a few ways, and add parameters like trace flags and the -m for emergencies.

    References