VS Live 2017 Anaheim Slides and Code

The slides and code from my talks at VS Live 2017  Anaheim are now available on Github. Thanks to everyone that attended:

A Tour of SQL Server Security Features – https://github.com/way0utwest/EndtoEndEncryptedSQL2016

Unit Testing T-SQL Code – https://github.com/way0utwest/UnitTestingTSQL/tree/vs2017Anaheim

Posted in Blog | Tagged | Leave a comment

SQL Data Generator–Getting a value based on another column

This is a series on SQL Data Generator, covering some interesting scenarios I’ve run into. If you’ve never tried it, SQL Data Generator is a part of the SQL Toolbelt. Give it a try today with an evaluation today.

One of the things that people often want to do is generate data, but limit the generation to some data in another column. Here’s a good example. Let’s suppose I have some data that represents a balance in an account. That’s in a column we’ll call Balance. In another column, I have a status that is either OK or Overdrawn, depending on whether the Balance column is positive or negative.

If I perform a random generation on these columns, I’ll get some strange data. Sometimes the data below matches up, sometimes it doesn’t. I have positive numbers as Overdrawn and negatives as OK

2017-10-05 10_51_08-SQL Data Generator - New project _

Let’s fix that.

In Data Generator, I have a variety of choices for the generators. Let’s look at what I can do for the Status column.

2017-10-05 10_52_27-SQL Data Generator - New project _

Certainly there are RegEx and Python scripts, but there’s a nice “Cross Column” section with some examples. In this case, let’s look at the Age in Years generator. The definition is:

2017-10-05 10_53_11-SQL Data Generator - New project _

In this case, it’s a simple .NET date function and some math. I can do that. Most importantly, I can see the “Insert Column Name”, which lets me pick another column in my table.

Python

The language of choice in SQL Data Generator is Python, specifically Iron Python. Outside of C# Datatime values, Python is needed. If you examine any of the other cross column items, you’ll see we need a main() function that returns something.

In this case, it’s a simple expression. I’ll use an If statement to check if the Balance is >= 0. Here’s a Python construct.

def main(config):
     if Balance >= 0:
         return “OK”
     else:
         return “Overdrawn”

Whitespace matters, as does indentation. If I put this in like so:

2017-10-05 10_57_57-SQL Data Generator - New project _

I’ll get this. Notice that the status is correct.

2017-10-05 10_58_05-SQL Data Generator - New project _

In a real project, you may have more complicated logic, or more likely, status values. One way to handle those is to use a Python function and return the appropriate values for your system.

You can build some complex and interesting data generation projects with SQL Data Generator. Give it a try today.

Posted in Blog | Tagged , , | Leave a comment

Unit Testing T-SQL Code

Abstract

Unit testing has become an integrated, expected part of most software development teams. Many database developers have yet to implement unit testing as a regular habit. This session will look at two-unit testing frameworks and show how to implement tests for common types of non-trivial T-SQL queries. You’ll examine the tSQLt framework as well as the Microsoft Unit Testing framework for SQL Server.

You will learn:

  • How to structure and build unit tests for database code with tSQLt
  • How to structure and build unit tests for database code with database projects
  • Understand the challenges of test data and how to solve them

Level: 200

Demos

These are the testing demos for this talk:

  • Loading and reloading test data
  • Checking standards
  • Checking joins, specifically outer join refactoring
  • Checking function calculations
  • Checking boundary conditions
  • Using the 0-1-Some pattern
  • Checking NULLs

PPTX and code on github:

Data Platform Summit – https://github.com/way0utwest/UnitTestingTSQL/tree/master

VS Live 2017 – Anaheim – https://github.com/way0utwest/UnitTestingTSQL/tree/vs2017Anaheim

Posted in Presentations | Tagged , | Leave a comment

A Tour of SQL Server Security Features

Abstract

Protecting data from unauthorized access becomes more important all the time. SQL Server includes a number of features that make data protection and security easier for developers and DBAs with a framework for protecting data. Come learn how Always Encrypted, TDE, Row Level Security, Dynamic Data Masking, and column level encryption can protect your systems.

You will learn:

  • About the different encryption and security features in SQL Server
  • Understand the code changes required for encryption mechanisms
  • Gain a basic understanding of RLS and DDM, which do not require code changes to help protect data

Level: 100

Demos

This talk includes the following demos

  • Always Encrypted setup and data access
  • Row Level Security setup and use
  • Dynamic Data Masking for users
  • Column Level Encryption implementation
  • TDE setup and verification
Posted in Presentations | Tagged , , , , | Leave a comment

Rebooting Ubuntu Linux from the Command Line–#SQLNewBlogger

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

Since SQL Server 2017 is now out, and Linux is an option, I thought I’d write a bit more about little Linux things that admins might want to know.

After installing an update, I neede to reboot my Ubuntu install. Apparently there was a dialog, but it was hidden. Unlike on Windows, I didn’t see an easy way to click a button and restart. However, since I had a terminal shell open, I was sure there was an easy way to do this.

I could have run a quick search, but I fell back to the old standby:

man shutdown

This showed that there is a shutdown, as on Windows, with the same –r parameter. This needs sudo to run, and by default it reboots after a minute. The command I ran was:

shutdown -r

Learned something, which I should have known. I almost typed shutdown, but I decided to double check.

Note: I realized later that there is a small menu in the upper right. I had the VM in a window and didn’t notice the icon. Clicking the gear gives a menu.

2017-10-02 10_38_28-Ubuntu 64-bit SQL Server .210 - VMware Workstation

This lets you then choose shutdown or restart.

2017-10-02 10_38_36-Ubuntu 64-bit SQL Server .210 - VMware Workstation

Posted in Blog | Tagged , , | Leave a comment

Software Patents

This editorial was originally published on Jan 30, 2014. It is being re-published as Steve is out of town.

I highly dislike the current patent system in the US. I think awarding patents for an idea like “1-click purchasing” is a poor practice that discourages innovation. There are numerous problems with our patent system, not the least of which is that it can stifle new ideas and prevent small companies from building on similar ideas that are, well, what I’d all common sense. I don’t quite share Richard Stallman’s view of software patents, but I do think we have fundamental flaws in the way we handle this intellectual property.

I do, however, think that software has value, and the work we do as software engineers deserves compensation, as noted in this post. We should have some patents, and we should reward those that create truly innovative, and not common sense, ideas in the digital world. How we define those, and how we measure innovation isn’t clear to me, but I do think that non-trivial ideas deserve protection.

Our legal systems around the world are slow to catch up to the digital world, and may never do so. As quickly as we can debate patents, copyright, trademarks, and more, the digital world grows, expands, and changes to stretch and challenge our thinking of how the world works. It almost seems as though digital ideas and software defy analogies and explanations that we can relate to the real world.

I don’t know how I’d change patents, but I don’t want them to go away completely. I do, however, know what I’d do with copyright. I’d change it back to the original system we had, granting 14 years exclusivity to creators, with a single option to renew for 14 years and that’s it. The world certainly changes too fast to wait 95 years to use someone else’s work.

Posted in Editorial | Tagged | Leave a comment

Quick SQL Prompt Updates in a Pattern

I work for Redgate and write about products. I’ve got a series of SQL Prompt posts here on little things I like. SQL Prompt might be my favorite tool.  SQL Prompt will be yours as well if you give it a try.

We had a customer post a question today on how they can built an update statement with a pattern. Specifically, they said that the code often looks like:

UPDATE dbo.Contacts
   SET
   c.Salutation = @Salutation
, c.FirstName  = @FirstName
, c.MiddleName = @MiddleName
, c.LastName   = @LastName
, c.Suffix       = @Suffix
WHERE ContactID = @contactid

The table columns are the same name as a variable. That’s a good pattern, and I’d think SQL Prompt could handle that.

It doesn’t.

The column picker doesn’t work with Updates (logged w/ product team), and I can’t duplicate selected text over (also logged for discussion). However, I do have a workaround.

As I thought about it, I realized there are some features of Prompt that help here, and some of SSMS that will work.

I made a quick video of the process, but I’ll describe it below:

The Process

The first thing is to get a column list. ssf<tab> does for me. I’ll get the select statement for a table and then expand the list of columns with a tab when on the *.

Now, I’ll copy the columns. I tend to copy all since it’s usually easier to remove than pick and choose specific ones. I’ll wrap these in an update, which could be a snippet. If it’s not, that’s fine.

From here, I use the power of Shift+ALT. If you’ve never done this, it’s amazing. I use this to select the columns and copy them. Then I’ll CTRL+ALT  to add the = and paste in the columns. I can then use CTRL+ALT once again to remove the alias and replace with a @.

And, of course, I can reformat to make it look nice with SQL Prompt. Give SQL Prompt a try today and see how it can improve coding and feel free to share your tips here.

Posted in Blog | Tagged , , , | Leave a comment

The Cloud is Better In At Least One Way

Many people don’t see the cloud platform as necessarily better run than what they do in their local data center. That might be true, and certainly the caliber of people, their experience, responsiveness, and more can make a big difference to the way in which any environment operates. One would like to think that AWS, Azure, Google Compute, etc. would all hire the best people and pay them more to operate a top notch system. I’m sure they have some of those people, but they also may have some average people that might not be better than your administrators.

Corruption is something I’ve experienced a few times in my career, and it was incredibly scary each time. I’ve been on multi-day support calls, trying to recover data, exported out pages to reconstruct values, and had to have very difficult conversations with management and clients to let them know data is gone. I think the worst experiences for me were breaking the news to workers that would have to try and re-enter data. Many of them had lots of extra work to look forward to without extra pay.

The Microsoft Azure team takes corruption seriously, and there’s a good description in this post of how they protect against and deal with corruption in Azure SQL Database. It’s quite comprehensive, and it’s a set of things that I wouldn’t expect most companies to implement. Even some of the better SQL Server people I know don’t necessarily get called in during off hours when a corruption alert fires. Plenty of people working with SQL Server might not have been through an Immersion event and understand how to even deal with corruption outside of calling Microsoft.

The cloud is a vast array of service and settings, many of which we can manage ourselves. In those cases, certainly the cloud might not do a better job than we would. No one on a cloud vendor’s staff is going to manage your VM or help you ensure your database design is solid. However, in checking for corruption, ensuring disk level backups, watching for DDOS and other threat vectors, the cloud vendors certainly do a better job than most of us. And for corruption, Azure SQL Database seems better watched than most databases I’ve seen in my career.

Steve Jones

The Voice of the DBA Podcast

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

Posted in Editorial | Tagged , | Leave a comment

Using Backup-DbaDatabase for a Quick Backup

I really like the dbatools project. This is a series of PowerShell cmdlets that are built by the community and incredibly useful for migrations between SQL Servers, but also for various administrative actions. I have a short series on these items.

One of the core tasks of a DBA is backing up a database. In fact, I’d argue that it’s the most important thing for a DBA to know. Second would be restores.

I’ve used the BACKUP DATABASE command so often from T-SQL that it’s a quick way for me to just get a backup of a database. I almost always have SSMS running, so I can easily just run the backup.

For regular backups there are some great tools out there, SQL Backup Pro from Redgate, Minion Backup, Ola Hallengren’s scripts, and more. However, there still might be a time I want to make a few backups, perhaps copy them over, and that’s where I think dbatools and Backup-DbaDatabase might help.

This is a nice, easy cmdlet to use. You can probably guess how to use it. Give it an instance, a database (or few), include a path, maybe create folders for each database, and let it go.

That’s about it.

If I run this interactively, I’d get the progress bar:

2017-10-02 17_04_29-{15%} cmd - powershell (Admin)

When the command is done, I see the results from each database. I get the file, folder, full path, and the script used.

2017-10-02 17_04_36-cmd - powershell (Admin)

I could include a different path if I wanted, and certainly I can chain this along with other PoSh commands.

I don’t know that I’d use this often, but it is handy in places, and certainly if I want to script the movement of some files, perhaps for something like HA/DR testing or setup, or even to refresh other environments.

Posted in Blog | Tagged , , , | Leave a comment

A Double Dose of Trouble

It’s Friday the 13th, during the month of October. Halloween, a strange holiday associated with horrible events, and Friday the 13th, a bad luck day, are happening this month. Both are associated with horror movies, implying that this isn’t necessarily a good month for the world. It’s a double dose of bad luck, which is what Equifax would have you believe. I wrote about this yesterday, where the company would have you believe a single employee makes a mistake when a scanner fails, and hackers were ready to attack.

I’ve, in general, had a lucky career. I’ve worked hard, and things have gone my way. That doesn’t mean I haven’t made mistakes or systems haven’t gone down, but overall things have gone well. As I think about Friday the 13th of October, I’m reminded about Murphy’s Law, where things that can go wrong, may just do so. I’ve tended to expect that some things will go wrong and prepare accordingly. In fact, in keeping with a corrollary that I’ve sometimes heard to Murphy’s law that if one thing goes wrong, I’d expect that something else will fail.

On this double downer of a day, I’m wondering if you’ve experienced that. Have you had something bad happen at work, only to find that overshadowed by something worse? Hopefully you didn’t make a security mistake and then get hacked, but perhaps something else has broken?

In my career, I’ve had this happen, though not directly to me. I consulted with a small company to make a little extra money and because I liked their business. I was rooting for them to succeed and wrote some database code to help speed up their application. One day they call me to say that a drive array failed with the database mdf on it. I wasn’t worried, and while in another state, I was ready to walk them through a restore process on the new array. Unfortunately, this was a default install, and the default backup path was on the old array. I hadn’t set this up, nor was I aware of their backup process.

The lead developer was proud of himself. He’d made a backup the day before, using a file share on a network drive. He had the .bak file. I thought this would be a quick and easy call until he read the error message that not all media sets were provided. That’s when I realized that the developer, while making the backup in SSMS, hadn’t cleared the previous backup file from the dialog. He’d inadvertently created a striped backup.

Eventually they managed to locate a backup that was many days old. I’m not sure how far back, but they angered a number of clients, who I’m sure all started looking for a new vendor. Eventually the company failed, and I moved on. There wasn’t much to be done in that situation, but it was a certainly double dose of things going wrong at the same time.

I hope few of you have had double the bad luck in your career, but if you can share a story, we’re happy to commiserate with you.

Steve Jones

The Voice of the DBA Podcast

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

Posted in Editorial | Tagged | Leave a comment