New Database Options

I saw recently that Azure SQL Database is getting a few more Database Scoped Options for that platform. These are intended to give more control over the way in which the engine behaves, without requiring each database on a server to function the same way. I expect to get to the on-premises product at some point, where they’ll be even more useful as we often might want different behavior for different contexts on an instance.

While there are advantages to managing all databases in an instance in the same way, I do think that more and more we consolidate databases at times and it’s better to have additional control when needed at the database level. This week, I wonder if there are things that you wish you would have been able to specify for each individual database.

What options would you want to see added at the database level? 

I think that many of the options we’ve been given in current versions, as well as the newer ones appearing in SQL Server 2019 are a good start. I don’t know which instance level settings I might want here, but I certainly would like to see newer capabilities at the database level. It would be nice to see the capabilities for jobs and alerts to be set at the database level. Even if this were a part of the Agent subsystem, having the ability to keep these jobs within a database and have the agent read them would be useful.

Moving more capabilities to the database level gives us more flexibility in separating the workloads for different applications. With the movement of the platform code, and many customers, to Azure SQL Database where the system requires less dependence on an instance, it makes sense to start including more options at the database level. I would guess that at some point most of the settings that we need for manage a system will be included and set at the database level.

Steve Jones

The Voice of the DBA Podcast

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

Posted in Editorial | Tagged , | Leave a comment

Fun with Savepoints–#SQLNewBlogger

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

I haven’t spent a lot of time with savepoints, but I did find a question recently and thought I’d take a moment to dig into how they work. They are interesting, and they can be useful for you in certain situations.

Warning: Anything involving transactions can be tricky, so be sure you test, test, test and check out how things work with a wide variety of situations, including some you might not expect.

Here’s a basic setup. I’ll create a table to log some actions.

, LogMessage VARCHAR(200)

Now that I have a table, let’s do something in a transaction. I’ll start a transaction, make two inserts, but set a savepoint between them


INSERT dbo.TransLogger (LogMessage) VALUES ('First insert inside transaction')




SELECT top 10
  FROM dbo.TransLogger AS tl

If I look at the results, I see this:

2018-11-12 16_46_08-SQLQuery8.sql - Plato_SQL2017.sandbox2 (PLATO_Steve (57))_ - Microsoft SQL Serve

That makes sense. I inserted this row (I’ve been testing, so that’s why it’s 11), and marked a savepoint with the SAVE TRANSACTION Firstsave line. Then I rollback a transaction to this savepoint, which does nothing. Finally I commit. I see my one row.

Let’s add something. I’ll add a second item, and decide to roll it back.

DECLARE @rollback INT = 1


  INSERT dbo.TransLogger (LogMessage) VALUES ('First insert inside transaction')

  INSERT dbo.TransLogger (LogMessage) VALUES ('Second insert inside transaction')
   IF @rollback = 1


SELECT top 10
  tl.ID, tl.LogMessage
  FROM dbo.TransLogger AS tl

Note I’ve added a variable so I can decide to rollback or not. I’d often have some condition or error handling that might cause a rollback, so this simulates that. Note that work before the savepoint is committed, but work after is removed with the ROLLBACK TRANSACTION Firstsave.

My results are a single row. Note, I cleared the table between runs.

2018-11-12 16_49_42-SQLQuery8.sql - Plato_SQL2017.sandbox2 (PLATO_Steve (57))_ - Microsoft SQL Serve

Savepoints give me a place to commit work if I need it before doing more. This potentially allows me to capture some changes and not others if I don’t want to fail my entire transaction.

Personally, if I’m doing this, I would likely just have two transactions if I can have one commit without the other.


A few minutes of experimenting gave me a quick post. I need to do more, and certainly test more, but this is a basic idea of what savepoints are. You can write something similar.

Posted in Blog | Tagged , , | Leave a comment

The Linux CoC

This is a busy time of year for me, with lots of conferences and other events taking place. It’s busy most years, but this past October was especially busy in my life. I’ve been in New York, London, and Hong Kong during the month, which is quite a spread of time zones. It’s been a mix of work and pleasure, and I agreed to all these trips, so I can’t complain. In any case, it’s both an exciting set of trips and a daunting set, and I don’t know if I’d want to do that again.

In my travels, I noticed some press about a new Linux Kernel Code of Conduct. This is a change from the original Code of Conflict that Linus Torvalds published. I’m not sure he adhered to his own words from the reports I’ve seen over the years about his comments to developers. In any case, he signed off, though not everyone likes the new code of conduct. I don’t know enough about the issues, but I do realize that not everyone behaves well towards others, especially in this business.

In any case, I go to lots of conferences. I meet lots of people, and see lots of different situations play out between attendees, organizers, venues, and speakers. For the most part people are fairly well behaved and treat each other respectfully. That’s not always the case, which is why many conferences and organizations have adopted some Code of Conduct that should apply to those that attend events, are members, etc. I do think this is a good idea, as it gives us a common framework where we can evaluate behavior as well as debate future changes.

Last week PASS has their annual Summit, with their own Anti-Harassment policy. While I haven’t observed any actions that would violate the policy, I have had friends report they have experienced these types of behavior. I’ve had friends ask for an escort over concerns of potential behavior. It’s sad that this happens in the world, but it’s a reality. I’m glad that organizations are trying to move in a direction that protects those that feel harassed or threatened.

It’s likely that there will be overreactions, reports of misunderstandings, and similar issues. Certainly some people want the freedom to behave as they see fit, where they don’t believe they are doing anything wrong. I understand that, but ultimately I also believe that it would be better to have a few people investigated or thrown out of a conference for no reason than have others suffer because they aren’t believed. I’m here for any of you that are struggling with abuse, and I hope others are as well.

I hope that we learn to live by the famous quote from Bill and Ted’s Excellent Adventure: be excellent to each other. If some of you can’t do that, then at least learn to live by Wheaton’s Law.

Steve Jones

The Voice of the DBA Podcast

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

Posted in Editorial | Tagged , | Leave a comment


Volleyball season is approaching. Practice started last week for the team that I’m coaching this year, and I’m excited. I look forward to teaching and competing with a new group of athletes each year. I’ll also look forward to a more regular schedule and a bit less traveling for a few months.

In preparation for this season, I’ve been doing some learning, some reading and watching, trying to improve my abilities, something I’ve done for a few years. One of the books I completed recently was one on John Wooden and called Wooden: A Coaches Life. This was a look at his life, as player and coach, with some of the descriptions and principles that embodied his work as a college basketball coach.

There were interesting stories and topics in the book, but one of the core items emphasized in the book was Coach Wooden’s emphasis on the fundamentals of the game. He stressed this with his players, asking them to work on the basics and perfect them more than on any complex plays or situations. I tend to focus on the basics when I coach as well, hoping to train players to be good at their jobs, trusting them to react to new situations.

This feels like advice that is applicable to a data professional as well, especially in the era of new features and functions that continually expand on the capabilities of the Microsoft data platform. While graph structures and containers and Azure Data Factory and Big Data Clusters are amazing new technologies, there is still a need to have good, solid fundamental skills for a SQL Server system. We still expect anyone working in those areas knows how to backup a database, how to write good T-SQL, how to set security for objects, and more.

If you want to specialize, that’s great. Perhaps you love BI or HA or some other aspect of working with the SQL Server data platform. Just keep in mind that the fundamentals are important, no matter what your job. You ought to be very competent at handling any of those tasks that we would teach a junior DBA in their first year on the job. Once you know those, you can move on to more specific items. If you don’t know those, be sure you include those as part of your learning along with more niche topics.

Steve Jones

The Voice of the DBA Podcast

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

Posted in Editorial | Tagged | Leave a comment

T-SQL Tuesday #108

tsqltuesdayIt’s that time of month, and this is a good topic as it relates to career learning. I’m a big fan of improving your career, so I like this topic. The invitation is from Mala, one of the people I look forward to seeing each year at various events.

Non SQL Server Tech

At heart, I’m something of a data person, though I dabble in various other technologies at times. This year, I made it a point to work on learning two new technologies, one of which was outside of SQL Server. Python was what I chose and I ended up spending some time on various Python courses for about 5 months. Then life and work got in the way.

I still want to spend a bit more time on Python, but I also recognize that I need a new challenge, so I’m going to pick something else for 2019. For me, this will be CosmosDB.

I think CosmosDB is a neat technology and has some really good things inside of it, but I really don’t know enough about it. I’ve had minor exposure to NoSQL structures, but not really enough to know how well I’d use them for a project.

The Plan

For 2019, or at least for the first quarter(ish), I want to port a database from SQL Server to CosmosDB and play with the differences. I have a few sample ones, but I’ve been compiling a database of some SQL Saturday data and want to use that as a test. I’ll work on moving the data to the different CosmosDB structures, likely a document structure and a graph structure, and gain some experience as to how these work.

I hope to build a simple REST website that accesses these databases, which should also let me compare the differences for data access and note where one structure might work better than the other.

I’ll set a reminder for the end of each month in 2019 (Jan-Apr) to evaluate where I am.

Posted in Blog | Tagged , | Leave a comment

Random Pix from the 2018 PASS Summit

A few memories from me. First, a beautiful late arrival view.


My first session feels a little lonely


It started to fill a bit later


A few selfies, Angela












and my view during a break after Thursday’s sessions.


Where’s my room?


It’s a wrap


After Friday, a nice walk out the convention center and outside



Until next year.

Posted in Blog | Tagged , | Leave a comment

Internal Controls

I was browsing the Internet and stumbled on a small part of a larger story that struck me. Many of you may have heard of the story of Jamal Khashoggi, the journalist for the Washington Post that was killed. I hadn’t spent much time reading about the story, and I don’t really want to discuss that topic here. The politics of the situation are not relevent here.

There’s a part of the NY Times background story that caught my eye when a quote was posted on Twitter. This is part of that quote: ” The intelligence officials told the Twitter executives that Mr. Alzabarah had grown closer to Saudi intelligence operatives, who eventually persuaded him to peer into several user accounts”. Essentially, an employee at Twitter was accused of accessing, and potentially disclosing, sensitive data about customers. This is what I want to discuss.

In my career, there are quite a few times that I’ve had to access data to solve some problem, debug an application, or produce a report. In many cases, I’ve had to maintain some confidentiality of the data, not even discussing specifics with other employees that were not supposed to view that information. To me, that’s just part of being a professional. We handle all sorts of data, some of which we should never use outside of solving an issue or producing a report.

As I thought about what was alleged here, I wonder how many social media companies have controls or auditing to determine who has accessed information. Would they be able to actually produce a report that validates some assertion that data was, or was not, accessed. I doubt many companies have these kinds of controls. Unless some Excel file or other export was on a file share, would there be evidence?

Then I thought does anyone really do a good job of producing audit records for information access? I know some government and law enforcement systems do this (and some legal software), actually tying queries and results to some individual and even a piece of work. That’s not the nature of information for most of us, though perhaps it ought to be.

Auditing data, especially for information access, could be a huge amount of data. Even keeping a record of all user access for a week in most SQL Server databases might be more data than many of us have in our database. I do think we ought to have the option, and I’d hope that we get more detailed, more capable, and more configurable methods of auditing SQL Server activity in the future (Hint, give us SQL Audit data in a csv).

Steve Jones

The Voice of the DBA Podcast

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

Posted in Editorial | Tagged , | Leave a comment

November Data Platform Releases

This past week was the PASS Summit in Seattle. It’s an annual conference that attracts thousands of SQL Server and data platform professionals. I am lucky in that I usually get to attend, and this year was no different, although it was a short summit for me. There are some live blogs of the keynotes from Kendra for Day 1, 2, and 3 if you want to catch up.

Microsoft was there with a large presence, and as always, they delivered a keynote and had plenty of demos that showcase new changes and enhancements for the data platform. In this case, center stage is really SQL Server 2019, though there were plenty of other items shown as well. I was surprised to see the Azure Managed Instance get so many mentions. I suspect this is an easy for for many companies to transition away from an expensive local data center, or to find ways to use less staff and continue to run a SQL Server outside of their existing infrastructure. I don’t know if this is a good fit for most customers, but Microsoft certainly wants you to try it. The Business Critical edition (with business critical pricing) will release as generally available on December 1.

There were a few releases as well, which you might want to play around with in your lab. First, Azure Data Studio (ADS) got it’s November release. I’m still not certain I love the tool, but Microsoft is working hard to improve it and add features. There aren’t a lot of changes this month, but there are a few more extensions and a number of bug fixes. The paradigm for ADS is just a little off for me, and I’m not quite sure why. I find VS Code to work well for C#, Python, and PowerShell, so why is ADS off for me? Not sure, but let me know in the discussion if you like the tool.

We also have our second release of SQL Server 2019 with CTP 2.1 being announced. It’s supposed to be available Friday for download, at least as a container, but we’ll see. There aren’t a lot of changes, but there are some. What’s more impressive is Microsoft being able to release a second version a month after the first one. They hope to get to a monthly cadence, which I think is amazing for a large product like SQL Server, especially as Windows struggles with their cadence.

There is one amazing new feature, which I think will really improve SQL Server performance for many systems: Scalar UDF Inlining. It’s not a panacea, but it should dramatically improve the use of functions in many workloads. There are restrictions, and it’s only SQL 2019, but I look forward to testing a few demos to see how well things perform with this enhancement to the query processor. You should give it a try as well, testing workloads before and enabling compatibility level 150. If you see improvement, maybe there’s a good case to upgrade your instances that might be using lots of functions.

There are more announcements, especially in the BI area. We get some cool SSRS enhancements, and you might want to watch my friend, Patrick LeBlanc, demo the changes in Power BI. I love Power BI and I think this is going to be the de facto reporting tool for most organizations moving forward. Maybe it will even displace Excel for visuals.

There are a lot of moving parts in the Microsoft Data Platform right now, which may feel overwhelming to many of us. That’s fine. We don’t have to learn everything, but we can pick something that looks interesting and spend a few hours playing. You never know what you might get inspired to learn more about.

Steve Jones

Posted in Editorial | Tagged , | Leave a comment

Vote for the PASS Board of Directors

Voting is open for the PASS Board of Directors. It’s a non-event this year, with three open positions and three candidates. That’s disappointing, as I would hope to see new candidates, new blood, and some change in the organization. I’m not complaining, since I didn’t run, but I hope that more people will run in the future.

You might think there’s no reason to vote, but one of the people voting will win a free registration to the 2019 Summit. That alone is worth a few clicks and a moment of your time.

Log into your MyPASS account and you can vote. Good luck in the contest.

Posted in Blog | Tagged , | Leave a comment

Republish: Data Breach Danger

In Seattle today, delivering two talks, so you get a republish of Data Breach Danger.

Posted in Editorial | Tagged , | Leave a comment