pointers, solutions and scripts for the SQL DBA
not intended to replace msdn, common sense or oxford commas

Monday, July 31, 2017

Twilight TimeZone: Handling Time in Your App Architecture at SQLSaturday Baton Rouge

Thanks to everyone who joined my colleague Steve Schaneville and me for a presentation on date/time data architecture in modern applications. Steve and I are the principal consultants for appdev and SQL Server respectively at Sparkhound, and figured that a joint presentation on this topic would be valuable.

It was! We got a lot of great questions and positive feedback from our first audience at SQLSat Baton Rouge 2017, as well as a ton of notes to add to our presentation the next time, which will likely be at Houston Tech Fest 2017 in September.

We reviewed the SQL and .NET architecture for date/time storage an informative and thought-provoking talk about handling timezones in your application architecture. Dealing with Time Zones is disconcerting, rarely straightforward, and often complicated! In the end, our architecture recommendations lead to two likely best paths - either using datetimeoffset (and optionally also storing the Time Zone information in a separate field) or storing the date and time in separate fields. (But preferably datetimeoffset.) There are few advantages and major disadvantages to storing only UTC data or all-in-one time zone data.

You can download the slidedeck and sample code here.

UPDATE: Related to Steve and I's presentation on date time architecture, check out our colleague Vance's blog post on serialization: http://www.sparkhound.com/learn/blog/date-demo-blog

Our presentation at SQLSatBR 2017

Sunday, July 30, 2017

Change RegisterAllProvidersIP setting on an existing Availability Group

Was working with a client who had an existing Availability Group, but did not have the RegisterAllProvidersIP setting enabled on their cluster. So, recent changes to their connection strings to take advantage of MultiSubnetFailover to minimize failover re-connection delay wasn't helping. Multisubnet failover needs to see both Listener IP's to help, so RegisterAllProviderIP's is required to be ON.

Here's why.

With RegisterAllProvidersIP disabled on the client access point (the cluster network), only one IP address is made available for the listener. When enabled, all site IPs for the listener are simultaneously listed. Connection strings using MultiSubnetFailover will try out all IPs simultaneously, providing for the fastest possible transition after an availability group failover.

Caveat remains that any connection strings that aren't or can't use MultiSubnetFailover will have problems. So RegisterAllProvidersIP should be enabled only when MultiSubnetFailover can be used in all application connection strings. More information here. You really do need to understand the applications and their connection strings here, this has little to do with the SQL Server and more to do with apps and DNS aliases.

In the case of the client above, enabling RegisterAllProvidersIP on an existing, in-use cluster is possible, but does require a brief outage.

Here is the script to use via PowerShell.

Comments guide along the way.

nslookup AAGListenerName #With RegisterAllProvidersIP = 0, this will only return one IP, the IP of the current primary.

Get-ClusterResource  -Cluster "FC_Name" #Get Cluster Network name

Get-ClusterResource "ClusterNetworkName"  -Cluster "FC_Name" | set-clusterparameter RegisterAllProvidersIP 1  -Cluster "FC_Name"

#You'll get a warning, all changes will take effect until ClusterNetworkName is taken offline and then online again.

Stop-clusterresource "ClusterNetworkName"  -Cluster "FC_Name" #Take Offline

Start-clusterresource "ClusterNetworkName"  -Cluster "FC_Name" #Right Back Online

Start-clusterresource "AAGName"  -Cluster "FC_Name" #This step is important. The AAG is offline, must bring the AAG Back online

#Wait a moment, then verify

Get-ClusterResource  -Cluster "FC_Name" #Verify

nslookup AAGListenerName #This should now return Two IPs, for both the primary and secondary. Now, MultiSubnetFailover can help.

UPDATE: Thanks to a commenter, updated the 0 to 1 in the set-clusterparameter line.

Wednesday, July 19, 2017

SQLSaturday Baton Rouge at LSU on July 29

The big finale to our SQL Summer and the last of the three Gulf South 2017 SQLSat events, our 9th annual SQLSaturday features an 11-track lineup of speakers from around the country, assembled by our scheduling extraordinaire Kenny Neal.

The schedule includes a whole track of PowerBI experts, a panel on Careers in IT, an entire track on Big Data/Analytics, tracks for .NET, mobile, and web developers an entire track for Business Intelligence, and so much more. Oh, and of course top-notch SQL DBA and SQL Development tracks from big names in the SQL industry!

Who attends SQLSaturdays, you say? I'm glad you asked...

This event is entirely free for attendees, including a jambalaya lunch, and tons of raffle prizes to giveaway at the end of the day. Everything is paid for by our amazing sponsors.

Please pre-register if you plan on attending! Please also consider signing up to be a volunteer as well, and our expert volunteer coordinator Adrian Aucoin will put you to work, probably just by being a proctor and attending the same sessions you were going be in anyway. And we still have of sponsor tables available in the atrium to be a part of this local community-driven event.

Have questions about sponsoring? Check out our informational sponsorship PDF explaining all the benefits, costs, the day-of experience, and the process of getting involved in one of the largest and broadest SQLSaturday events in the world. Have questions? Please reach out to us via email at sqlpassbr@gmail.com. The sponsor registration and payment deadline is July 15.

Thumbs up if I'll see you there!

Tuesday, July 04, 2017

Celebrate Your Independence with Free Tools

Been working on this blog post for a while, thought I'd share today an inventory of tools I use regularly that are free to the DBA.

A nearly ubiquitous free Microsoft-provided download to provide for easy zooming, live zooming, drawing on your screen, and more. It is something that makes a big difference when communicating to an audience. Won't make you a better presenter, but will make your content clearer and easier to communicate. Be sure to practice with it before you go live. :)

An open source alternative to other screen recording, screen capturing, even making quick .gifs and uploading them to social media, image sharing applications and more.

An open source alternative to BeyondCompare that has many of the same features, including file and folder structure comparison. I commonly use this to compare large result sets to make sure that my query tuning hasn't drifted the outputs, or to compare two queries to find differences. It's a quick and easy application when this type of text or file comparison is needed. I've been using it for years for these purposes.

Visual Studio Code
A Microsoft-provided open source rich text editor that has a lot of the features you see in expect in Visual Studio, but in a lightweight, quick-loading text editor that compares favorable to other paid alternatives. It's replaced other alternatives for me for lightweight code editing.

Plan Explorer
SentryOne (formerly SQLSentry) released a user-friendly, feature rich, useful alternative to the SSMS execution plan viewing options that is particularly useful when analyzing massive, complex query execution plans and breaking down costs. The guidance provided is obviously solid technically and can deliver valuable insights. It even has integration with SSMS. A premium paid version has collector service and database.
Also: You should of course also check out new features that have been added to SSMS recently, including Live Query Stats and the Query Store. SSMS itself is a little obvious for this list.

Remote Desktop Connection Manager (RDCMan)
Not to be confused with Remote Desktop Manager or any similarly-named browser extensions, RDCMan is a free Microsoft-provided tool that makes it easy to organize remote desktop connections, securely store credentials for groups of RDC's, for example in a domain. This is a dream tool for folks who have many RDC's with many credentials in many different domains.
Sidebar: Do you get an annoying popup when you open your RDCMan application, "There were problems decrypting some credentials"? That's the built-in encryption that keeps your stored credentials secure. You'll get that (and lose the stored passwords) when you migrate from one machine to another. You'll have to open the .rdg file and remove the password hashes. There are instructions in the RDCMan help to migrate a personal certificate along with the .rdg file for proper migration, it's the only way to have remembered passwords transfer to another device. (I've been meaning to blog about this over time, but didn't feel it was worthy of its own post.)

Okay, maybe this isn't free, but it comes with your Office365 account or office license for Word and Excel, so unless you're in a no-Microsoft environment, you have access to OneNote for free.(Teachers and students globally can also get all of Office365 for free.) Had I known about OneNote back in college I'd have been using it then. OneNote is a fantastic information-gathering tool. It can do quite a lot more than a simple note-taking platform. We share password-protected OneNote notebooks on SharePoint for secure team-based collaboration and note taking. And the search function can find word matches in your notes and handwriting, and even in your screenshots, photos, and audio recordings, something you can't do with stacks of legal-size notepad paper. I've presented using OneNote as a whiteboard before as well, and it performs admirably with your drawing pad or tablet touchscreen.
Caveat: By default, the Ctrl+1 zoom feature of ZoomIT (above) and the Ctrl+1 checkbox feature of OneNote make for exciting note taking. The good news ZoomIT is easy to close and re-open.
The ubiquitous rich persistent chat app has set a very high bar for other collaboration tools, including the me-too Microsoft Teams. It's free but you have to pay for history and some other features. It's got a million different integrations, is very popular in the tech world for internal corporate team communication, but also in the social activism communities and technical user community, especially in the SQL Server community. The richness of the medium allows for better responses when you're crowd-sourcing SQL help than the twitter hashtag #SQLHelp, which is still popular. Speaking of which...

There is no larger or more popular platform for short-format communication this day and age, used by everyone from teenagers, heads of state, even teenaged heads of state. Hashtags are topics, so posting a question accompanied by #SQLHelp will allow others anywhere in the world to see your post if they follow that topic. Twitter has most impact during live events, especially physical events like conferences, but also sporting events and breaking news. The next time you're at a SQLSaturday event, check out the hashtag for the event, for example, #SQLSatBR at SQLSaturday Baton Rouge. If you're at PASS Summit in the fall, try #SQLSummit. Also, reading about their architecture for outrageous growth scale is interesting.
Quick unsolicited advice: If you're on Twitter, you should try to keep your professional and personal lives separate. You know you shouldn't launch political tirades on LinkedIn or in front of your coworkers or at the Thanksgiving dinner table. You should use your professional platforms to interact with technical peers and colleagues and coworkers, and to represent your employer (and future employers). Use your personal, potentially anonymized account(s) to hurl insults at sports rivals and to argue over whether the dress was blue or gold. As a consultant who tried to keep public work and politics separated, the benefits to this strategy are clear. If you're a government employee, this is probably required of you too.
Microsoft Assessment and Planning Toolkit
Does a yeoman's job of collecting IT infrastructure inventory in your environment, including SQL Servers, via a variety of connection methods and all without an agent. Generates a bunch of reports that CIO's and managers will eat up. Has delivered good value with minimal investment in time or expertise.

Yep, there is a capable free version for organizations, assuming those organizations have an Azure tenant/Office 365. It is also free for individuals who are not in those organizations.

Aardvark is a really neat social tool that allows you to source answers come from other users who have signed up for knowledge in general categories. It's really going to change the world.
Just kidding of course, but it was cool in a make-the-world-small kind of way for a while there before Google bought then killed it in 2011.