A brief note on version...

With Microsoft's move away from the Service Pack model, SQL Server has seen more frequent updates. This is generally a good thing, but it does pose a problem for those of us who write about it. Information gets out of date. New functionality comes in, old functionality is deprecated, documentation for older versions disappears, and the great cycle of technology continues. Many of us (like me!) write these blogs in our free time, to educate the community and ourselves, and it can be difficult to go back and maintain older posts to ensure they're up-to-date.

I have both Windows and Linux versions of SQL Server (at the time of this writing, my lab VMs are SQL Server 2017 CU14 on Windows 10 and Ubuntu Server 18.04 LTS; I have spun up a container or twelve in my time as well, and of course there's Azure SQL Database, and...), and I keep them up-to-date as new cumulative updates are released. I'll do my best to post version information as I write, but I probably won't go back and update old posts unless there's a really big change. Even then, I'll probably write a new post instead.

If you need historical version information, go directly to Microsoft SQL Server Versions List, do not pass GO, do not collect $200. Maybe toss 'em a couple bucks. You can also now find documentation for previous versions of SQL Server on the SQL Server previous versions documentation page.

Tracking Data Changes - Introduction

Unless you're managing a static archive database, the data in your databases will change. Odds are, at some point you're going to need to know about these changes for one reason or another. Perhaps you're using a data warehouse and need to retain history, or you need to keep track of slowly-changing dimensions. Perhaps you have an application that needs the latest data, and you want an easy way to feed it without querying the entire table for timestamps or update flags. Perhaps you have audit requirements and need to keep detailed change history.

Whatever the reasons, SQL Server has a few options to satisfy various use cases. Your options range from the 'roll up your sleeves and do it yourself' triggers, to Change Tracking, Change Data Capture, and Temporal Tables.  In this series, I'll show you these options and how to use them. We'll begin with Change Tracking, then move on to Change Data Capture, Temporal Tables, and conclude with triggers.

If you're using a platform other than SQL Server, please refer back to your RDBMS's documentation (or - let's be honest here - StackOverflow followed by your search engine of choice) for information about tracking data changes. Many platforms have similar functionality to handle this need, but if nothing else triggers are almost always a viable option, if a bit more manual work than we might like.

In SQL Server, some of the functionality depends on your version and edition. The following table provides a quick summary:

Tool	Debut	Edition
Change Tracking	SQL Server 2008	All
Change Data Capture	SQL Server 2008	Standard as of SQL Server 2016 SP1, Enterprise prior
Temporal Tables	SQL Server 2016	All
Triggers	Back in the dim mists	All

Here is a (very) quick overview of the various options:

Change Tracking:

• Uses an internal versioning mechanism to track changes
• Just tells you that a change happened (it can tell you WHICH column was changed) - no history
• Snapshot isolation, so be mindful of TempDB
• Reasonably tolerant of schema changes
• Requires a primary key
• Automatically cleans up after itself

Change Data Capture:

• Uses a log reader agent (hello sp_replcmds!) to capture change data
• Can give you full change history
• Does not play nicely with schema changes 
• Allows two capture instances per table
• Highly configurable - capture only columns you want
• Automatically cleans up after itself

Temporal Tables:

• Also requires a primary key
• Also gives you the full change history
• Uses period columns in the source table to feed the history table
• Easy to query
• In SQL Server 2017, introduced the capability to clean up after itself

Triggers:

• Offer you amazing flexibility in designing your tracking
• Let you capture metadata and stuff the other solutions may not allow
• Require you to manage everything from soup to nuts

There is a lot more about each of these methods, of course, and in the course of this series I'll walk you through each in some detail. Please check back soon for an introduction to Change Tracking!

A quick 'save me from myself' thing in SSMS

Have you ever felt that 'down-elevator' feeling in the pit of your stomach when you realize you just executed a script against the wrong instance? I have.

It's of course possible to add name checks and all to only allow execution on a given instance, I'm going to go out on a limb here and guess that most people don't do that in most cases.

Happily, there's a quick and easy setting you can take advantage of to give yourself a visual indication that hey, maybe you're not where you thought you were: the 'Use custom color:' setting in the Connection Properties of the Connect to Server dialog box.

To get there, first click Options:

Check the box for 'Use custom color:', and hit 'Select...':

Pick your color, click 'OK' (I tend to use red for production, orange for QA/test, green for development, but go with whatever works best for you), and then click 'Connect':

From here on out, the status bar down at the bottom of the query window will be whatever color you've picked for that instance:

While various third-party tools offer similar functionality (often with a lot more flexibility and configurability), this is built-in to SSMS and has been around for quite a while. It's a great quick gut-check!

Updates and housekeeping

After... well, forever... on an old theme, I've updated. Some older posts may look weird, but most should be good. Please let me know if you run into any problems.

The code I can't live without

Today's T-SQL Tuesday topic is thought-provoking: What code couldn't you live without? (OK, OK, technically it's just titled 'Code You Would Hate to Live Without', but work with me here.)

One of the hallmarks of the dedicated DBA is that we write our own tools. I've encouraged juniors to write their own code, even if they can find things online that solve their problems (inexperienced folks blindly using code from online is a whole can of worms that I may open later). We gain so much by doing the work to learn about the systems we're interacting with, and the harder work of figuring out how to write the code, be it T-SQL, Powershell, or what have you, to really dig in there.

As a brief aside, in my experience if you don't have the curiosity and drive to begin writing your own tools, you're not going to get very far as a DBA. I'm not sure I'd WANT you to be in charge of my servers. Now, not for a second would I expect highly-polished code from juniors right out of the gate. What matters more is evidence of that curiosity and drive. What matters is that we keep learning, keep practicing, and keep improving.

On the flip side, there are times we find something out there that Just Works. Part of the wisdom we develop as we progress is knowing when to press on with writing our own, and when someone else's code does the job perfectly.

I'm finished digressing - on to the code I'd hate to live without!

Under the 'code from others' umbrella, code I'd hate to live without includes:

• Ola Hallengren's maintenance solution. Oh my god you guys, if you need a robust backup and maintenance tool it's well worth your time to consider Ola's. I've used Ola's solution with great success in both small and large environments.
• Glenn Berry's diagnostic information queries. These are priceless. They cover almost anything you need to gain insight into your server's health and status.
• Adam Machanic's sp_whoisactive. What's going on, in detail, RIGHT NOW!
• sp_who2 filtered, from SQL Matters. Simply putting sp_who2's output into a table is brilliant, and one of those things that I wished I'd thought of myself when I came across this. 

Of the things I've written myself, I would hate to live without these things (please note, these are all hosted in Dropbox; if you want copies and can't get to Dropbox please let me know!):

• Replication scripts!
• A quick script to get publication information. 
• This returns publishing database, publication name, the source article name, subscriber instance, and the immediate sync setting.
• A trio of scripts I wrote to get replication agent history - distribution, snapshot, and log reader. 
• These scripts return agent names, agent IDs, status info, and the most recent 100 history records. Adjust the value as necessary.
• A script to identify the jobs associated with various agents.
• A script to get stored procedure execution information, and the query plan if you want.
• This query gives a lot of info - when the plan was cached, how many times the plan's been looked up, how many things reference the plan, when the proc was last executed, how many times it's been executed, all sorts of worker, reads, and writes info including averages.
• The usual caveats on the cache info apply - restarts, cache flushes, index rebuilds, plan recreations, etc. are all things to keep in mind
• Some templates for file manipulation
• Moving database files
• Restoring a database

I've got a lot more, but these are the most important to me.

On the importance of testing your backups

You're backing up your databases. The jobs run when they're supposed to, you've chosen the right types of backups, got all your settings dialed in, they take the expected amount of time, the files are there on disk. Everything's good!

Well, maybe.

Have you restored your backups? You should. I'd even go so far as to say that without test restores, your careful backup strategy was a waste of time and energy.

"But the backups complete without errors," you say. And that's certainly good, but it's only part of it. Things can go wrong in backups. Disk hiccups after the backup is generated can cause problems. Bugs in the database engine can cause unexpected problems as well. Different database products will have different issues and quirks, but the key thing to remember is that they ALL have them. Your preferred RDBMS is no different. It's up to you as the DBA to learn what they are, and to learn how to work with and through them.

This is one that I've personally run into: "RESTORE DATABASE is terminating abnormally" error message when you perform a full backup of a database in SQL Server 2008 R2

We added a new data file to a database, and due to a quirk in our naming convention the truncation caused a problem: we wound up with two identical logical filenames in the backup file list, and we couldn't restore the backup. We were fortunate that we didn't need to do any restores for that database before we found the problem, and Microsoft did release the fix in the linked article, but we would've been in serious trouble if we hadn't caught the issue and suffered data loss.

You also need to test your restores in a realistic fashion. How much data can you recover? How long will it take? This is when you will need to consider the business's needs.

There are two objectives around recovery that you need to consider: the recovery point objective (RPO) and recovery time objective (RTO). In brief, they respectively address how much data the business is comfortable with losing, and how long the business is comfortable with a database being offline in case of failures. People too often assume that both RPO and RTO will be close to zero. It's certainly possible to get very close to instantaneous recovery with no data loss, but it can get very expensive very quickly as you're then getting into high availability (HA) territory. The old saw, 'fast, cheap, and good: pick two,' definitely holds true here!

If you don't know how long your restore will take, you won't know if you can meet the RTO. If you don't know how much data is available in your backups, you won't know if you can meet RPO. If your backups are bad, you're guaranteed not going to meet either the RPO or RTO unless neither matters for the instance (but how many instances do you manage that fall under that 'it would be nice to recover it, but we can live without it' heading? I know I don't have many in production). If you haven't talked with your management about their expectations around RPO and RTO, please do so.

While RPO and RTO are certainly important considerations, they can be ongoing topics of conversation - your first order of business is to ensure that your backups first CAN be restored, and second that they give you the results you expect. As with anything, practice makes perfect. Openness and honesty will go a long way if you identify gaps. These can be hard things to bring up, I know - I've had to have a few unpleasant conversations on this topic over the course of my career. But how much worse will the conversations be if something fails and you can't recover?

Test your backups, folks! You'll be glad you did!

SQL Saturday 700 in Colorado Springs is coming up!

If you work with SQL Server in any capacity, I can't recommend attending a SQL Saturday enough. A day of no-cost training and networking is a fantastic opportunity. SQL Saturdays offer a wide variety of content - there are commonly tracks on database administration, business intelligence, development, professional development, management, and many others. 

So many big names in the SQL Server community got started speaking for local user groups, SQL Saturdays, and the like, and many of them continue doing so. It's a fantastic opportunity to learn and spend time with deeply knowledgeable people, pretty much all of whom are also super-nice!

It's also a great opportunity to get to know the sponsors. Odds are you'll have the opportunity to talk directly with knowledgeable folks from storage vendors, tool vendors, recruiters, and more.

One of the other best things about SQL Saturday is that there's an open call for speakers for each event, and many of them work hard to include new speakers and local folks. If you want to share your knowledge in a larger venue, it's a great opportunity to get started. I've submitted a session myself this year. You don't even have to stick to your local area. While you're on your own for your expenses, it can be extremely rewarding to travel, meet new people, and get yourself out there in front of a larger audience.

If you live along the Front Range in Colorado (or anywhere in CO, really), northern New Mexico, etc., I hope you'll consider attending SQL Saturday #700 in Colorado Springs on 24 March.  

There are also ample volunteer opportunities. It can take a surprising amount of work to make the events run smoothly, and there's always something the organizers can use help with. Even though I'm no longer helping to plan and run the event, I've still ticked the volunteer opt-in on the registration form.

Dan Gurney, 1931 - 2018

I wrote a brief memorial for Dan Gurney over at The Pit Crew Online. The man had a LIFE, let me tell you.

A quick note about multi-hop replication in SQL Server

Many people use SQL Server replication in a multi-hop setup. Publish from A to B, republish from B to C, and so on. This is a well-known use case, even if it makes those of us who have to maintain it a bit crabby at times - throw in some other stuff like Change Data Capture that uses the Log Reader Agent and it's a recipe for fun (but that's another post for another day).

One thing I've seen a number of people run into trouble with is making changes to publications in this sort of environment. It's simple enough when you're adding new things - you start at the start and move down the chain. Where I've seen folks have the most trouble is with removing articles and publications - for that, you have to start at the last hop and work your way back to the source.

Managing this kind of setup can become tedious, so I urge you to get comfortable with the various stored procedures, views, and tables that are present in SQL Server. Microsoft's documentation on the topic is thorough and helpful, and spending some time in the lab poking around will amply repay you. Document your environment, learn what replicates where, and when it comes time to make those changes you'll be in much better shape.

If you don't have a lab, don't fret! Simply sign up for a Visual Studio Dev Essentials account at VisualStudio.com, and you can download SQL Server Developer Edition at no cost to you.

Helllllloooooo 2018!

Gosh, about 4 years. I figure it's time to start bloggin' again!

I'm still working with SQL Server, so I'm going to mostly blog about that. I'm still interested in things Japanese, so I'm probably going to blog about that a bit too. I've also developed an interest in motorsport in the interval, but for that you'll want to check out The Pit Crew - that's where I write about that!

A tough power situation in the Tohoku, and its roots in the Meiji period

While TEPCO and its Fukushima crapfest disaster idled power plant still occupy much of what little attention the US press pays to the ongoing electricity crisis in Japan, it's worth noting that Northern Japan is having capacity issues on top of the earthquake and tsunami aftermath. My friends in Southern and Western Japan are in comparatively good shape, electricity-wise, but it's difficult to get surplus power to the East and North due to an interesting quirk in Japan's electrical grid.

Power companies in Western Japan produce power at 60 Hz, while the generating firms in the East produce power at 50 Hz. This discrepancy has its roots in the modernization rush in the Meiji period. The link above gives a great capsule summary - the generating firms in Tokyo and Osaka imported equipment from different countries, and the nation grew to have two frequencies as firms in the East followed Tokyo's lead, and those in the West followed Osaka's.

While the frequency discrepancy doesn't cause problems for most devices that use mains power, the nature of power generation and transmission equipment is such that you can't simply send power from West to East, or the other way. There are a few conversion stations (three, to be precise), but there's only so much capacity per station. The combined 1 GW capacity falls far below the nearly 10 GW shortfall in the East. There is also some limited HVDC transmission capacity between the grids, though it isn't enough to transmit much more power.

The rush to modernize, and the uncoordinated growth stemming from long-held regional rivalries, leaves its mark on Japan even today.

がんばれ日本! がんばれ東北！

It was very sweet that the Independent in the UK had this phrase on their front page. It's been a very busy and exciting (in the negative sense) few days in Japan. Thankfully, everyone I know is accounted for and safe as far as I know, but many were not so fortunate. I've changed the title of my blog to a more somber phrase for a bit - it's a haiku by Issa, written after the death of his young daughter.

I will have more to say on the situation soon, but right now I'm still in processing mode. My one word of advice to observers is to look past the Fukushima reactor complex problems and focus on the un-sexy but far more important human toll in the Tohoku region.

I will say that it's interesting (and really, rather understandable) that much of the world is paying more attention to the reactor problems than many of the other issues. This is perhaps a common human thing - the quakes are (for now) largely over (this is leaving aside the aftershocks), and the tsunami is done (again, for now), so we pay attention to the immediate drama at Fukushima. I don't think the instant gratification culture is to blame here, rather I think it's just in our nature to be attracted to the more dramatic issues. The cleanup and search efforts are just not as exciting, though I'm sure we'll see a few stories of people beating the odds and surviving in amazing circumstances, but soon enough even this will be forgotten by most people not directly involved.

Give money if you can, give your time if you can, give your thoughts and encouragement if that's all you can give.

Kan he get Japan's budget in shape?

I first apologize for the horrible pun in the title.

Japan is showing some signs of growth again, even though it's small. The budget is still a problem, though, and one thing PM Kan is considering the LDP's suggestion to increase in the consumption tax, from 5% to 10%, over the next few years. He's also talking about a balanced budget by 2020.

Compare this to Mr. Osborne's bumping the UK's VAT to 20%, up from 17.5%.

Apart from the oddity of the major political parties suggesting a tax INCREASE just before an election (which really does underscore the depth of the problem), it's worth noting that Mr. Kan has proposed this as his cabinet's popularity is slipping.

Mr. Kan is in a very tough spot, but I hope that he sticks to his guns and lays the groundwork for future responsible fiscal policies. Even if he suffers politically, history will be far kinder if he can get Japan on the right track.

Perhaps the timing of Mr. Kan's accession to the Prime Ministership will work in his favor, though. Elections are looming and the DPJ has lost a lot of public goodwill. The churn in Prime Ministers in Japan (including Mr. Kan, there have been five since Mr. Koizumi left office in 2006, though all but Mr. Hatoyama have lasted right around a full year; Mr. Koizumi, by contrast, was in office for over five years) indicates a general failure of leadership and concomitant lack of public confidence in both the LDP and DPJ (even though the PM is selected by the ruling party, public sentiment is very important). Mr. Kan may not have much of a future in office no matter what.

Thanks to this, he may well feel free to bite the bullet and make the hard choices that must be made. In the immortal words of Kris Kristofferson and Fred Foster, freedom's just another word for nothing left to lose (Janis Joplin's version of the song was far and away the best, by the way). Mr. Kan may well be supremely free to act.

There is of course the concern that raising taxes could cause growth to stall. This is a legitimate worry, and a reason to implement a tax increase gradually. It is heartening to note that Mr. Jimi is taking a sensible departure from his predecessor's preference for yet more spending to stimulate growth. It is also heartening that he is taking the position that postal funds should not be directed toward risky overseas ventures. At this point, Japan can ill-afford a crisis generated by unwise use of Japan Post savings funds.

I encourage Prime Minister Kan to press forward with the hard choices and keep Japan on the road to sensible fiscal policy.

(All linked articles were accessed on 22 Jun 2010. They are subject to change without notice, and the links may eventually break. Paragraph on taxation vs. growth concerns added after the article was published. Due to time zone differences, some articles may have a publication date of 23 Jun 2010; they were accessed on 22 Jun 2010 in the Mountain time zone, GMT -6 due to DST.)

Japan's next steps

With PM Kan taking the reins on a coherent note, I'm cautiously optimistic about Japan's immediate future. That said, I think Japan must address a few issues urgently in order to keep the ball rolling. Yes, some of these are certainly optimistic, possibly bordering on wishful thinking, but needful nonetheless.

• First, address the textbook issue and concomitant historical revisionism. In terms of public perceptions of Japan, this is perhaps the single most significant sticking point in Japan's relationships with its neighbors. Japan must own up to its wartime acts and come to terms with its Imperial past. Germany has managed to survive after doing so. Taking this step will help cool anti-Japanese sentiment throughout East and Southeast Asia.
  As an example, recent polls indicate that the Chinese and Japanese public both see addressing historical issues as the top priority (see question 16 here, for instance). Given the increasing economic interdependence in Asia, the dividends of such a program will be substantial. As the public goes, so often go the leaders.
  
  Interestingly, this could have some interesting repercussions in China regarding the Chinese Communist Party's legitimacy in the public eye. Communism is no longer a viable ideology, and so the CCP has had to rely on other means to maintain its legitimacy. Anti-Japanese sentiment is a convenient tool, and if the Chinese public begins to warm toward Japan, the CCP will face an interesting time domestically. It will at least harmonize their domestic take toward Japan and their diplomatic take - China is on quite the charm offensive with Japan right now from a diplomatic perspective, particularly Premier Wen's jogging excursion through Yoyogi Park. While there could be some unintended consequences (aren't there always?), I believe that overall the two nations could begin to build a more meaningful bilateral relationship once the public opinion gap is bridged.
  
  I realize that this is not likely to happen, given the significant resources that the ultranationalist players possess. That said, it is an effort that must be made. If the DPJ wishes to meaningfully distinguish itself from the LDP, they are the party to get the ball rolling. Unless the Ultranationalists and Rightists and other fringe Uyoku groups are brought to heel, they will continue to be a problem. Textbook reform will be a key tool in reducing rightist influence. The kids need to know the truth. The truth sometimes really hurts, but Japan will continue to suffer prestige problems until this is resolved.
• Next, as Kan mentions, get the economy sorted out. This is crucial in so many ways, and one more blog post can't do it justice.
  
• Either get the birth rate sorted out or enact meaningful immigration reform. Japan is rightly proud of its unique culture, produced under unprecedented historical circumstances (periodic contact with continental Asia, punctuated by long periods of isolation, during which the Japanese had time to truly internalize and transform what they learned from the mainland, producing culture that, while obviously derived from the culture on the mainland, is vibrant and distinctive and very much a Japanese sort of thing). That said, Japan can't meaningfully continue without some solution to the impending demographic crisis (see this paper - eek! There is an alternate methodology proposed here, so it'll be interesting if it goes anywhere, and if so what results are obtained. I will be keeping an eye on projections either way.). Japan has a choice to either get its domestic birth rate up, allow immigration in a more meaningful way, or face relegation to a 2nd- or 3rd-tier power.
  
  The downside of the aforementioned history is that xenophobia and associated racism are endemic in Japan, though they're generally quite polite about it. This will need to stop even if the birth rate rises. As the world grows ever more interdependent and interrelated, foreigners will continue to seek opportunity in Japan, and jobs teaching English aren't going to cut it. The government needs to quit scapegoating foreign residents for domestic problems, and needs to institute reasonable measures to allow legitimate immigration. The government also needs to take steps to ensure foreign resident workers are treated as well as their Japanese counterparts. These problems are of course not unique to Japan, but the Japanese approach to the issue isn't terribly productive here.
  
  Also, and I'm just saying, granting Japanese citizenship to the descendants of Korean and Chinese immigrants whose families have been in Japan for generations would be a nice gesture here.
  
• Evaluate Japan's security relationship with the United States and its closer neighbors. I will come back to this in a follow-up post, but suffice to say that the Futenma issue is symptomatic of some strains in the US-Japan security relationship. The relationship is strong overall, of course, and very beneficial to Japan, but it will not last forever.

Everyone has their own list of things Japan must do, of course. Not being in Japan (and not being Japanese, of course) colors my perceptions. Any of my Japanese readers/readers in Japan (a man can dream!) have input? I look forward to any comments.