Voice of the DBA

Category: Blog

  • Manual v Automation

    I’ve got my work cut out for me. This week is SQL in the City 2017. One of the sessions I need to present is a Manual DBA database deployment against an automated CI/CD process with the Redgate tools.

    To give our automated DBA a chance, I’ll be playing the role of the manual DBA, which means I’ll need all my typing skills and experience to try and quickly get code moved from one instance to the next.

    I don’t have a lot of confidence I’ll be faster, but I do know I can do the deployment. Or at least, I know I have done plenty of manual deployments in my career that have worked. I know how to script out changes, check them, and be sure they get executed in the right order on the next instance.

    However.

    I’ve also made plenty of mistakes, even with simple changes. I’ve learned that while I know what to do, I don’t always do what I should. Fortunately I know how to fix things quickly. Patching live in production. Always a good idea, right?

    We’ll see.

    We’re broadcasting a virtual SQL in the City this year, on December 14/15. The same content is both days, spread out to cover different time zones on each day. I hope you’ll join us and see what Redgate has in store for 2017.

  • SQLBits 2017 In April

    I’m getting excited, with the announcement that SQL Bits 2017 is scheduled. The conference goes back to Telford, which I enjoyed and takes place April 5-8, 2017. 

    I’m getting my presentations ready for submission, and I really hope I get the chance to speak again. This is a fun event, and one I try not to ever miss.

    Registration opens soon, and the schedule should be out early in 2017, so start planning your trip today. 

  • Interviewed on Channel 9

    Had a quick interview at Microsoft on Channel 9 last month, and it’s now live. Enjoy.

    https://channel9.msdn.com/Blogs/vsppstories/Including-the-database-in-your-DevOps-pipeline/player

  • Easily Move Databases with Copy-SqlDatabase

    One of the things that people have asked to be implemented for many years is an easy way to copy databases. SSIS has the Copy Database Task, but that has been problematic over time. As a result, while easy, it’s cumbersome to take a backup of a database, copy it to a new instance, and restore it. Or use the detach/copy/attach/attach method.

    dbatools gives us a nice, easy Posh command to perform this task: Copy-SqlDatabase. I made a quick test recently to see how this works. Using the –Whatif option, I tried to copy a database from one instance to another on my main computer.

    My first attempt didn’t do well.

    2016-11-22 10_32_58-powershell

    I was thinking there would be a default method, but there isn’t. You must specify using the backup/restore method or the detach/attach method. I decided to try the detach/attach method.

    2016-11-22 10_31_08-powershell

    As you can see, this looks to detach and then reattach the database, so let’s try it. First, check my QA instance. There is no DBAAdmin database.

    2016-11-22 10_31_42-SQLQuery1.sql - localhost_SQL2016.DBAAdmin (PLATO_Steve (67))_ - Microsoft SQL S

    This is a small database with just a few procs and tables in it. When I remove the –Whatif option, it works.

    2016-11-22 10_31_28-powershell

    I saw the green PoSh flash in the middle as the file was copied from one location to the next using the BITS method. When I looked in my SQL2016_qa instance, I saw the database and objects.

    2016-11-22 10_31_54-SQLQuery1.sql - localhost_SQL2016.DBAAdmin (PLATO_Steve (67))_ - Microsoft SQL S

    All in all, this took about two minute for me to move this database. Certainly that is about the time of a backup, but I did this with one command, and it felt much, much easier.

    There was a downside for me. I need to double check this, but I didn’t get the database re-attached at the source, and I had to do that myself. The files were there, just not attached. Perhaps it’s just me, so I’ll retest when I get the chance, but it seems to be a minor bug.

    I also tried the backup/restore option here. I had to provide a fileshare, which I chose to be my local D: drive. Note, this has to be provided or a popup will appear. This also has to be \\server\share, not d:

    2016-11-22 10_51_03-powershell

    This worked, with the new database appearing on my second instance.

    2016-11-22 10_50_47-SQLQuery5.sql - localhost_SQL2016.sandbox (PLATO_Steve (63)) - Microsoft SQL Ser

    The backup was also removed from my drive:

    2016-11-22 10_51_25-EVO850_500 (D_)

    Overall, this is handy, and I’ll likely use this as my default method for moving databases between instances.