Voice of the DBA

Category: Blog

  • Quick #SQLPrompt Tips – Expanding Wildcards

    I tend to try and get code on the screen quickly and then start to remove things. I’m a visual person, and it’s helpful for me to see some tables, joins, filters, and columns as I’m structuring a query.

    One of the ways I work quickly is with SQL Prompt is that I’ll write a query, using the SELECT * to hold the place where columns will appear. Since I’m not always sure what columns exist in a table, using the asterisk allows me to complete a valid query.

    2016-08-26 08_46_03-30113.sql - (local)_SQL2014.AdventureWorks2008 (PLATO_Steve (73))_ - Microsoft S

    However, I don’t want to leave the asterisk there. Let’s put the cursor behind it. As you can see here, a tip pops up.

    2016-08-26 08_53_16-30113.sql - (local)_SQL2014.AdventureWorks2008 (PLATO_Steve (73))_ - Microsoft S

    When we hit Tab (or your completion hotkey), the entire column list expands. All columns, from all tables, qualified if necessary, according to my SQL Prompt settings.

    2016-08-26 08_53_25-30113.sql - (local)_SQL2014.AdventureWorks2008 (PLATO_Steve (73))_ - Microsoft S

    Now I have a well written query, or if I don’t need all columns, I can easily remove those that I no longer want to retrieve.

    This is a quick tip, one that doesn’t do a lot, but has the potential to make developers really think about all the data being returned in large queries with a SELECT *.

    Give this a try the next time you find yourself writing a SELECT * query and then remove the columns that you really don’t need. You might also check out a similar piece I wrote for the Redgate blog.

    If you aren’t a SQL Prompt user, then think about downloading an evaluation and becoming a more efficient T-SQL developer.

    You can see a complete list of SQL Prompt tips at Redgate.

  • T-SQL Tuesday #82–Azure SQL Database

    tsqltuesdayIt’s T-SQL Tuesday time and this month we have Jeffrey Verheul giving us the topic of the cloud, or Azure SQL Database. It’s a good topic, given how many people are talking about working in the cloud. There are also the tremendous resources that Microsoft is investing in Azure, so cloud database posts are a good thought for this month.

    Whether you are writing today (Sept 13) or at some point in the future, it’s fun and informative to participate in the T-SQL Tuesday blog party. Learn something and write about it.

    Azure SQL Database

    I have done a little work with Azure SQL Database, but not a log. I have a few ideas, but finding time and focusing has been a challenge. When I saw this topic I had all sorts of ideas, but since I was traveling and busy, I ended up arriving at the deadline without any work done.

    However, there was one thing I’ve been wanting to try in Azure, so I decided to make this the post. I wanted to create an Azure SQL Database using Powershell. This is a topic that I think lends itself to better orchestration and development, so here goes.

    The first step was a quick Google search, which led me to this post on how to Manage Azure SQL Database with Powershell. That’s what I really wanted to do, so I read a bit. The first step was to download the latest Azure Powershell. I did that and ran an install. This required a reboot, something I rarely like, but I swallowed my annoyance and restarted the desktop.

    Once that was done, I ran a couple cmdlets to get connected and check what resources I had. First, I had a login prompt.

    2016-09-13 14_10_50-Windows PowerShell credential request

    Then I got results. As you can see, I had a few, and the list actually scrolls down quite far.

    2016-09-13 14_23_23-powershell

    I then tested another cmdlet, Get-AzureRMResourceProvider, to get a list of regions.

    2016-09-13 14_25_22-powershell

    That worked, so let’s create a database. I used the New-AzureRMSqlDatabase cmdlet, which worked well. I provided the resource group name and servername I already had with a new database.

    2016-09-13 14_14_44-powershell

    That completed, and things looked good. I connected to the Portal, and sure enough, I had a new database.

    2016-09-13 14_15_23-dkranchapps - Microsoft Azure

    Of course, an S1 will eat up all my credits, so I changed that right away.

    2016-09-13 14_15_57-TSQLTuesday - Microsoft Azure

    That was pretty easy. I can see how I might now string together the creation of a database with some other work, perhaps adding data, certainly using the Redgate DLM Automation or ReadyRoll scripts to create objects, and then I can run things and tear them down.

    Speaking of which.

    2016-09-13 14_32_24-powershell

  • Bringing Database Devops to DevConnections

    Another alliteration post. Maybe I’ll make all my titles tongue twisters to test your talents?

    This is my new favorite session. I’m slated to deliver this at a few different conferences this fall, and hopefully you’ll come see it in Las Vegas at DevConnections on Oct 10-13.

    The talk is called Bringing DevOps to the Database, and this presentation is designed to show how many of the software development processes that we take for granted with .NET, Java, and other front end applications can be applied to the database. I’ll cover how version control, Continuous Integration (CI), and Release Management (RM) can be applied to database changes.

    This has been a focus of mine as an evangelist for Redgate Software, and I’ve found a number of ways that you can adopt common, best practice software engineering in the database development process. I’ll show how Redgate makes this easy, but I cover the process in a tool independent way. You’ll learn what needs to happen. How you get that working is up to you, but if help is needed, Redgate can cover that part.

    If you’re looking for some training, some inspiration, and are a full-stack developer, or multi-technology IT worker, think about coming out to DevConnections. There are a slew of technologies being covered (VS, ASP.NET, C#7, Azure, PowerShell, AWS, SQL Server, Sharepoint, Exchange, Hyper-V and more).

    Register today, use 500SPKR to save $500, and I’ll see you in Las Vegas this October.

  • Bringing DevOps to the Database

    Abstract:

    DevOps is a hot topic in today’s software development world. However most of the knowledge and experience with DevOps is based around application software and ignores the database. We will examine how the concepts and principles of DevOps can be applied to database development by looking at both automated comparison analysis as well as migration script management. Automated building, testing, and deployment of database changes will be shown.

    Level: 200 – Not to technical, but you should have some idea of how software development works to get something out of this talk.

    Goals:

    1. Conceptually understand how DevOps principles can easily be applied to the database in either state based or migrations based changes.
    2. Learn how databases can be automatically built and tested in a CI/CD environment
    3. Understand how database releases can be automated, but are different than those deployment/releases made for other software.

    Demos:

    This presentation has a few demos where I show how I stored database code in a VCS, perform an automated build, and use a scripted release process to move code from one database to the next. The demos are pre-built, so I just show how they work. I can do this with:

    • git, VSTS Build and VSTS Release
    • git or SVN, Team City, and Octopus Deploy

    Downloads:

    There are a few versions, as this talk has evolved.

    GroupBy – Bringing DevOps to the Database Groupby.pptx

    Live!360/VSLive – VSLive-Orlando 2016.pptx

    SQL Saturday Cambridge – Bringing DevOps to the Database.pptx