Better Coding

When the census was last taken in the US, it was still mostly a paper affair. My wife and I sat down with the form, filled it out, and mailed it back to the US government. While much of the data is publicly available, the process of gathering the data was primarily an analog process. That was in 2010, and governments are trying to do things differently. I suspect our 2020 census in the US will be mostly a digital effort, though I hope the companies setting up the applications learn from other countries’ issues.

Australia had their census this year, 2016, and they planned on having most citizens complete the form online, with a requirement to opt-in to get the paper version. However, things didn’t go smoothly. There was a DDOS attack on the night the census was set to collect data. There were also concerns from many people over data privacy. All in all, not a great showing for the Australian Bureau of Statistics and their IT contractors. Things got worse in the weekend after the census was scheduled when a few university students built a website designed to handle a larger load than the government over a weekend for substantially less money.

To be fair, the university students’ site wouldn’t necessarily meet all the requirements for privacy and data security. It also doesn’t seem to have the validation and verification that a website needs. Not to mention a database setup to keep the data for later analysis. Those things would certainly need to be added and tested, but this does show that sometimes there are better ways to develop software than the way that has been done in the past. Large government projects get bogged down, perhaps worse than many corporate projects, but I’m not sure. Perhaps we just need people that code better, and certainly, people that manage developers better.

Certainly the cost of projects for the things like the census might not go down much. After all, I’m sure a fair amount of the cost charged by IBM goes back to the salespeople and engineers responsible for landing the contract. However, I also think that far too often there is too much overhead in large projects starts to obscure the most important part of any software system: it needs to work. I would prefer that even long term, large scale projects be developed in an agile fashion, and take on beta testers throughout (public or private). Let actual users play with the site in advance, report bugs, simulate a load, even watch the site develop in real time.

And when you’re ready to deploy, from experience, I can tell you it’s not too hard to delete all that transactional data before you go live.

Steve Jones

The Voice of the DBA Podcast

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

Posted in Editorial | Leave a comment

SQL Saturday–Viking Edition

I’m off to the Viking Ship Museum for SQL Saturday #532. Actually, the Viking Museum will (hopefully) be a side trip on Friday before the actual event. The actual training will be at the Noroff campus in Oslo.

This is my first trip to Norway, and while it will be quick, I’m looking forward to wandering another new country for a bit. I’m honored to be on the speaker list at this event, which is the third Oslo SQL Saturday (#414, #317).

As with most of the SQL Saturdays, there’s quite a selection of sessions to choose from. I’ll be talking about your career, as well as Continuous Integration for databases. However there are Query Store talks, Power BI, HA, PoSh, Azure, R, and more.

I know many of you don’t live in Oslo, but it’s close to much of Europe, and if you’re looking for a quick getaway to improve your career, come on over on Sept 3.  If you want more training, there are 4 pre-cons to choose from, all taught by some highly skilled SQL Server pros that are worth the effort to see.

Once again, I’m excited for another SQL Saturday, and hope to see some of you there.

Posted in Blog | Tagged , , | Leave a comment

Output into a Table–#SQLNewBlogger

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

I often see people struggling to use triggers for auditing, or having issues with building them to handle multi row updates. However, there’s another choice: the OUTPUT clause.

Not many people use this clause, but it’s a great way to access the virtual inserted and deleted tables in your code.

Suppose I have a simple insert statement like this one:

2016-08-22 11_11_01-SQLQuery7.sql - (local)_SQL2014.Sandbox (PLATO_Steve (57))_ - Microsoft SQL Serv

I want to ensure that I get the data inserted into an audit table. Certainly I could have code that does the insert into two places, like this:

2016-08-22 11_13_40-SQLQuery7.sql - (local)_SQL2014.Sandbox (PLATO_Steve (57))_ - Microsoft SQL Serv

But it can be hard to get developers to use procs like this, they might forget or need to build dynamic SQL. There are the other issues of maintenance where I might update the first INSERT, but forget the second.

OUTPUT allows me to add a clause in my DML statement. I put this before the FROM clause, or in this case, before the Values clause. Then I include values from inserted or deleted along with any scalar values. That looks like this:

2016-08-22 11_16_09-SQLQuery7.sql - (local)_SQL2014.Sandbox (PLATO_Steve (57))_ - Microsoft SQL Serv

However, this returns the data to the console. What I’d really like is to put this into a table. For that reason, I then add an INTO clause, with my table name. Now when I execute this, I get the data from the inserted table added to my EmpAudit table.

2016-08-22 11_18_32-SQLQuery7.sql - (local)_SQL2014.Sandbox (PLATO_Steve (57))_ - Microsoft SQL Serv

A basic, quick look at the OUTPUT clause.


I ran into an issue with OUTPUT and realized that I hadn’t ever covered this basic concept for myself. As I learned a few things, I decided to write about OUTPUT. This took me about 10 minutes to cover the basics, and was part of a 30 minute session writing a few more pieces on OUTPUT.

Posted in Blog | Tagged , , | Leave a comment

The Future for Database Administrators

I’ve been working in technology for over 25 years. As I look forward in time, I suspect I have less time left in a data professional career than I’ve already experienced. That’s fine, and while I don’t plan on retiring anytime soon, I know I likely will retire before another 25 years pass.

While it might not be as much of an issue for me, I do think it’s important to think about where your career may go. I ran across this post on the future of IT Pros and thought, “this sounds familiar.” A friend I knew used to manage a 20,000+ Exchange system for a large company. In 2000, he was sure that the advances in email technology from Microsoft would mean that he wouldn’t have a job in five years. Within two he had moved on to another industry altogether, trying to establish a new career before he was made obsolete.

Certainly if he had planned to still manage a 20,000 mailbox Exchange system the same way today, he’d be out of a job. However, if he evolved to either learn new skills along with Exchange, or added O365 administration, he might still be employed, and earning a very good salary in technology. Likely more than he makes now.

I think about this regularly as I see the Microsoft stack world changing and the emergence of new database technologies like NoSQL, data lakes, and Azure SQL Databases/Azure Data Warehouses. Those technologies certainly might mean that less DBAs are needed to actually install, back up, manage, and care for on-premise database instances. However, I still think there are plenty of opportunities for the Database Administrator. High Availability and Disaster Recovery technologies are more in demand than ever, and there is a complexity in Availability Groups that ensures there are still good jobs. Increasing security and auditing requirements require humans, usually with knowledge about querying and understanding the data more than setting up the technology.

The bar may be raising in terms of skills needed from database IT pros, but I still see lots of opportunity. Continue to work on your career, learn more, adapt, work hard, and you’ll have a job for a long time. Try to ride out the next few years on the same skills you’ve used for the last decade, and it’s entirely possible you’ll find yourself replaced by either a piece of software or another human that’s willing to learn a few new tricks.

Steve Jones

The Voice of the DBA Podcast

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

Posted in Editorial | Tagged | Leave a comment

Everki Versa Long Term Review

A couple years I was looking for a new laptop bag. I settled on the Everki Versa, which I’ve been using on my travels for 2015/2016. It’s been quite a few, which you can see on my speaking CV.

I decided to write a long term review of the bag, since a friend was recently looking at the eBags Pro laptop bag. I have to admit, that one looks good, but I wanted to stop and evaluate what I thought of mine.

Warning: lots of pix, and a little long.

Tl;dr: I like the bag, and love the sunglasses case.

The Front

Here’s my bag. It’s got a souvenir mini-bat next to it for scale. I got that from SQL Saturday 403 in Louisville.

Photo Aug 23, 12 28 26 PM

When you look at it, there’s a small front pocket (on top here) that doesn’t have a closure. However, it has a magnetic catch, so it’s useful for putting stuff in and it won’t fall out. Here’s what I put in it.

Photo Aug 23, 12 28 54 PM

I keep a small Redgate notebook and pen, and will stuff boarding passes in there. I also drop my phone in here when I’m going through airport security. The magnet is strong enough to keep stuff in, but not annoying when I need it. A view of my stuff back in.

Photo Aug 23, 12 29 09 PM

Behind this is a small zipper pocket. I tend to keep pens, a small screwdriver, and a sharpie in here. My passport also lives in here.

Photo Aug 23, 12 29 33 PM

My wallet often goes in here when I travel, since it’s big. There are a few random other items, business cards, misc small stuff goes here. I don’t use this a lot, but I know I’ve got pens and the passport here. Here’s the pocket from the side.

Photo Aug 23, 12 29 20 PM

Next, there’s a small zipper pocket on top. If you look at the first picture, it’s the zipper at the top of the picture. I drop quick things here, like my keys when I travel and my car ticket.

Photo Aug 23, 12 31 28 PM (1)

This is a small pocket, so I also can drop headphones in, but I keep this stuff in there. Extra lip balm, change, headphone earpieces, gum, and a small flashdrive. Easy to get to.

Photo Aug 23, 12 32 09 PM

Side Pockets

There are two side pockets. One is supposed to be for a water bottle, but it’s small. If I had one annoyance, it’s that there isn’t a decent water bottle pocket.

Photo Aug 23, 12 30 17 PM

This is a 24oz bottle, small compared to the ones I usually carry and refill, but none of those will fit here. There is a loop to keep this from falling out. There’s also a small mesh pocket in here where I have a travel LED flashlight.

That is the left side (when I’m carrying). The right side is more important to me and has a few things inside.

Photo Aug 23, 12 30 35 PM

This is my tech power pocket. In here I have these things:

Photo Aug 23, 12 30 57 PM

Two batteries, two cords, and US wall and car adapters. This lets me get to these things quickly on the go.


The main pocket for me is the center one. Here it is open, and you can see there’s a few things. There is a little divider, but I often find myself pushing it down when I shove stuff in. Not super useful, but if has a few pockets where I can organize.

Photo Aug 23, 12 35 55 PM

I love the orange. It really makes things easier to find. I’m not sure I’d get a bag without the orange inside (unless is were hot pink or neon yellow).

What’s inside?

Photo Aug 23, 12 36 42 PM

Here’s what’s inside. At the bottom of the picture, I have two pencil bags that my kids no longer use. These are handy for keeping things. One is a presenter bag with video adapters, presenter remote, an SSD for VMs, and wireless HDMI. The other has misc stuff, like microUSB power, a couple batteries, watch charger, misc cords, and a few other things I’ve picked up. I carry a 3ft HDMI cable, my power adapter, a spare set of earbuds, a 3 outlet power plug (with USB), a worldwide power adapter, batteries, a mouse, and a deck of playing cards (thanks to Ike Ellis).

In the mesh zipper pocket above, I have 3 zip loc bags. One has spare US currency, one has spare Europe currency, and one has a few meds (aspirin, allergy stuff, Nyquil). This is where things go in and out all the time.

There also is a laptop pocket in the back. This is against my back, but is nicely padded. You can see my leather tablet case and a scarf in here.

Photo Aug 23, 12 39 00 PM

What’s nice is this opens wide. I’ve only done this a couple times for airport security since many of them still want my laptop out. So, I just pull it.

Photo Aug 23, 12 37 54 PM

My laptop is about Surface Pro sized, and the leather case holds a mini keyboard inside the pocket. It can hold the Z Canvas keyboard on the tablet.

Photo Aug 23, 12 38 31 PM

There’s a separate pocket to the inside of this pocket. My scarf, hat, and gloves live here, since I’ve forgotten those items on trips and it’s a pain to go buy new ones. Here’s the tablet/laptop.

Photo Aug 23, 12 38 18 PM

I used to carry a 13” laptop in here fine.

The Back

There are two straps on this one, but more importantly, a pass through for a roller bag handle.

Photo Aug 23, 12 39 16 PM

This is required for me. I’m often just dealing with carry on luggage and want my bags secured together. Slipping a handle through here works well. However, I can get the straps in. They quickly unclip.

Photo Aug 23, 12 39 22 PM

I’ve only stuffed them through a few times. It’s not overly helpful, but it has been nice on some small planes to secure them when the bag is under a seat.

Photo Aug 23, 12 40 01 PM

The Hard Case

The other day I was heading to San Antonio and forgot my prescription glasses case. I couldn’t find it. So, I grabbed my glasses and dumped them in here:

Photo Aug 23, 12 33 35 PM

This is my favorite feature. This is a small zipper pocket on top of the backpack that has a hard formed shell. You can see it from the inside pocket here. It’s the ribbed case, and it’s hard, like a hard case shell. No changing shape here. Perfect for glasses.

Photo Aug 23, 12 34 18 PM

That’s what I put in there. Here’s the contents:

Photo Aug 23, 12 35 12 PM

I carry driving sunglasses and a super cheap pair to run in. My spare earbugs go in, and usually crackers or a protein bar in case I need it. I’ve added my other glasses at times, and I don’t worry about them getting broken, or more likely, bent. Having that hard case is excellent.

The Traveling Bag

How has this worked while I’ve had it? It’s been one of the best bags I’ve had. It’s not too heavy (though not light), and carries lots of stuff. It’s easy to fit in overheads, and I haven’t run into a plane that it doesn’t get overhead or under the seats, even small, 30-50 person jets.

I can’t really see any wear and tear, though from my list I see I’ve had about 50 trips with it. Nothing has broken. Zippers work great. The top handles are solid (there are two), and I’m pleased with the performance.

I also say that I’ve gotten better at ensuring that I pack consistently. Power is always in the right pocket, water in the left. Keys in the small top, glasses above that.

This was the most expensive laptop bag I’ve owner, but it’s lasted much longer than a few others I’ve had and seems very durable. I’m pleased.

Posted in Blog | Tagged , | Leave a comment

Security Leaks from Websites

One of the main issues with connecting databases to the Internet is that if a hacker finds a way to get access to the database with credentials, perhaps using a well known account (*cough* sa *cough*) and a weak password, they can get a lots of data. However, even if your database is not on a DMZ or addressable from the Internet, chances are some application that accesses your database is connected to the Internet. Websites are a perfect example of this.

That means our data security is controlled by the security of the web application, and the security coding practices of our developers. That’s a scary in many cases, primarily because we, as an industry, don’t do a good job of actually sharing secure coding practices and habits widely. As much as people talk about and publish information about writing secure code, this doesn’t get spread around to many developers, who have years and years of bad habits. Even here at SQLServerCentral, we don’t have nearly enough information on SQL Injection, which is something I need to work on.

However, even if you aren’t exposing your database server and aren’t vulnerable to SQL Injection, is your data secure? I read a rather sad article from Troy Hunt that looks at how personal data can be leaked. Far too many applications don’t follow secure information handling practices and unintentionally allow information to leak. Perhaps the most common item is verifying whether an account or email address actually exists or not in a login or password reset box. There are other examples, including not properly securing an entire page with SSL, allowing a simple thing like an order number to return user details, or even allowing someone to reset a password by guessing the answer to a security question.

These seemingly benign issues often expose real vulnerabilities. I’m sure many managers and even developers would say that a hacker wouldn’t sit at a web page and try lots of email addresses. That’s probably true. However, does your web application have limits for how often someone can submit code? Extremely powerful scripting tools like Perl, Python, and PowerShell mean that a user could make submissions to your website in an automated fashion, retrieving the code and parsing it to find details.

Most attacks on a system aren’t of the Hollywood movie variety. A hacker doesn’t find some hole and miraculously gain access to data in a few minutes or hours. Instead, a malicious user (outside or inside your organization), might make a series of attacks across days or weeks. They might try millions of emails or order numbers, gathering details and planning their future attacks that will actually reveal sensitive information. All those tools that make your job easier as a developer, or allow complex data analysis, can work just as well against your database as they do with your data.

Steve Jones

The Voice of the DBA Podcast

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

Posted in Editorial | Tagged | 1 Comment

Is Rollback Feasible for Database Changes?

One of the things I used to show people in SQL Compare was the ability to generate a quick rollback script by switching the source and target. We can generate a deployment script going from Dev to Production, switch the source and target, and then generate the rollback script, from Production to Dev. This was the automation of a manual process I used to go through, examining changes made in deployment scripts and producing the reversing changes for various schema items.

Really the only object that causes us problems is the table. We can easily grab previous versions of views, functions, stored procedures and other code objects, applying them on top of our deployment without worrying about maintaining state. We can go back and forth with different versions of code. Certainly our applications might have issues, but the database itself works fine and deployment is quick.

However, that’s not the case for tables. If I add a column to a table in a deployment, how do I remove it? Certainly I can drop the column, but what happens if there’s been a data transformation, or users have entered data? I might need to preserve the data that’s been added, which is a process I might not want to script. What if I split a column or refactor data to a new table? The movement of data back to it’s original spot, while preserving any changed data, is not a trivial task. Add up enough changes in a deployment and it wouldn’t be worth running any rollback scripts. A database restore might be more efficient.

Recently I was discussing these issues with a developer, who remarked that they never built rollback scripts. It wasn’t a good use of time. If there were issues, developers or DBAs would need to make decisions about how to handle the issues. These days, as we’ve become better at architecting database applications, we might even decide to leave the actual table alone and use view or stored procedure change to effect a virtual rollback for the application, erasing the changed schema with code rather than actually returning the table schemas to their previous versions.

As I see our organizations depending on databases to a larger and larger degree, requiring no more downtime than actually needed, I think that rollback scripts for tables might be a thing of the past. Certainly rolling back object code is still possible, with an understanding of the impact on both the database and application when old code is used. That’s definitely a decision that needs to be made on a deployment by deployment basis.

My view is that whenever possible, both the application and database should try to be forward and backward compatible by one version to allow changes to be made to one or the other without breaking the system. That’s not always easy, but in many cases it is with a little forethought, practice, and awareness of the architecture techniques that allow this, it can be relatively easy. In some cases, especially with smaller databases or discrete, limited changes, rollback scripts might make sense. However, I think for most of us this is a software development pattern that’s not worth considering most of the time.

Steve Jones

The Voice of the DBA Podcast

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

Posted in Editorial | Tagged , | Leave a comment

PoSh Everywhere

This week we had an announcement that PowerShell has been released as an open source project and is available on some Linux platforms as well as OSX. I was actually talking with someone else that had been informed prior to the MS announcement, and they told me to go to I first I thought I was getting some inside information. However this is a public site, obviously updated and released in time for the announcement, so no NDAs were harmed this week.
I have an Ubuntu VM I use at times, and I decided to take a few minutes to try out PoSh on Ubuntu 16.04. Installation was easy, as are most things on Linux. Not quite as simple as Chocolatey, but close. This is certainly one of the ways that Linux is a bit easier to use than Windows. Or maybe we’re just more used to the command line in Linux. In any case, it’s a very simple process, which I documented in a blog. For those of you that use PoSh, this will be easy, and for those of you used to Linux and shell scripting, you’ll feel right at home.
 I wondered how much stuff would work, but my first few tests, grabbing some generic, non SQL Server, scripts worked fine. The .Net CORE project has already moved some of the namespaces and functionality over, and this means  that a lot of the management of your infrastructure systems is there. I don’t see the SQL Server libraries ported over (if I’m wrong, someone let me know), but there certainly are some other items, including Azure modules. I thought the demos in this Channel 9 webinar, while simple, were pretty impressive.
Do most of us care about this? No, not really. If you’re a person that runs Windows on laptops and desktops, this doesn’t really matter to you. I run Linux for fun, but not for daily work and my use of these tools will likely be limited to testing. However, if I were to move to an Android or Linux machine in the future, it’s nice to know that I could run the same packages across various platforms without changing any code. That’s useful for collaboration with people that might want to work together on different platforms.
This is where this makes the world easier. If you have friends running Macs, or using Linux, then you can share code. Or they can write PoSh scripts and share them with you. While I love virtualization, sometimes it’s a pain, and this is why I keep an installation of SQL Server on my host. It’s simpler and easier to work with in some situations. This is also why I look forward to a SQL Server developer edition on Linux (and hopefully OSX). It’s just a smoother world when the OS becomes a matter of choice, rather than a requirement to run some code.
Posted in Editorial | Tagged , | Leave a comment

PoSh on Ubuntu

I heard about the PowerShell announcement recently where the platform is porting over to Linux and OSX. I think this is part of the SQL Server on Linux project, where I assume we’ll start to see the functionality that we have on Windows appear on Linux platforms. OSX is a surprise, but since more and more people run Macs, this makes sense. Hopefully we’ll see SQL Server Developer on OSX at some point.

I’ve got an Ubuntu VM, so I decided to see how well this works, so I picked the Ubuntu 16.04 package and found the instructions I ran on my VM. You can see how this worked in the image below.

2016-08-18 12_37_14-Ubuntu 64-bit SQL Server - VMware Workstation

Now that I’ve got the package downloaded, I can start PowerShell pretty simply:

2016-08-18 12_38_51-Ubuntu 64-bit SQL Server - VMware Workstation

What can I do? Well, let me grab a short PoSh script I have. This is from the Advent of Code, and it’s a .ps1 for me. Here it is interactively.

2016-08-18 12_49_53-Ubuntu 64-bit SQL Server - VMware Workstation

If I make a .PS1, then I can paste in the original code:

2016-08-18 12_51_09-Ubuntu 64-bit SQL Server - VMware Workstation

and if I execute this:

2016-08-19 11_07_01-Ubuntu 64-bit SQL Server - VMware Workstation

That’s pretty cool.

I haven’t explorer the extent of the PoSh support, and I don’t think there is SQL Server support yet for the SMO namespaces, but I’m sure it’s coming.

If you’re a Linux person, or an OSX person, I’d encourage you to play a bit and see what you think of the port.

Posted in Blog | Tagged , , | 1 Comment

What’s Your Smoke Test?

Many DBAs and operational staff regularly stress over software deployments to production systems. Even when the administrator has built and tested the deployment scripts, there is still a nagging fear that something will be missed, incurring downtime for systems. I think automation, building an ALM And DLM process, as well practicing deployments in other environments is the way to alleviate concerns, but that’s a discussion for another day.

However, I had a question this week that is related to the deployment process. I’m curious, do you smoke test your production deployments?

Perhaps the first question is do you know what a smoke test is? If you don’t, maybe you want to read a bit about smoke tests. If you know about smoke tests, do you have anything more than testing if a server or service is running?

If you don’t, that’s fine. In fact, throughout most of my career our smoke tests consisted of checking that we could connect to a server or database. In fact, in most cases, we did this manually. We’d deploy some change or patch, then see if we could connect to the server. Sometimes a person would connect with an application, but not much more than logging in. Since many apps were fairly simple client-server applications, this was usually sufficient, but there were times we had problems with not all changes being deployed. A few times we didn’t realize this for days.

If you deploy changes, there should be some small things that you check as an method of validating that your systems are working correctly. There may be initial checks that machines are actually running. If those checks fail, there’s no point in going further. However, if the basic connections work, perhaps you should have a few critical functions that can be called and ensure that basic functionality of the application is there. Or that your new changes actually are deployed. If you have complex, interconnected systems, maybe you want a few smoke tests that you can automate and quickly ensure everything is running as expected and communicating.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.0MB) podcast or subscribe to the feed at iTunes and Mevio

Posted in Editorial | Tagged , , | Leave a comment