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.

Fun videos:

https://www.youtube.com/watch?v=-5wpm-gesOY

https://www.youtube.com/watch?v=k4EUTMPuvHo

Our presentation at SQLSatBR 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.

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!