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

Wednesday, December 20, 2017

Our SQL Server 2017 Administration Inside Out fun author survey

I was honored to be on the author team of the new SQL Server 2017 Administration Inside Out book by Microsoft Press (out in Feb 2018). While the hard work was done and 700+ pages of final edits were still being passed around, the team of four authors (and our beloved tech editor, Louis) joined me in a fun interview-style blog post.

The questions were answered in late November-early December, as we were finishing up the last edits and getting the book ready, finalizing standards, and keeping up with the astounding/frustrating amount of changes happening to the product and surrounding tools! It's not just the Azure environment that is rapidly improving and maturing...

You can pre-order the book today at the Microsoft Press store or Amazon or your favorite bookseller.

The writing team (in cover order):

Thanks in advance for reading our fun little diversion from our final edits!

1.What music if any did you listen to while writing? 

William Assaf: A lot of Black Keys, Clutch, Disparition, the Dunkirk soundtrack, Thank You Scientist, and more stuff in and around those.

Randolph West: EDM.

Sven Aelterman: “I’d do anything for love (but I won’t do that).” For some unknown reason, my wife kept humming that tune in the background.

Mindy Curnutt: If I was writing from home I had Alexa spin me up some random Coffee House music, she's pretty good at it.

Louis Davidson: Nothing particular, usually older, upbeat music like Led Zep, The Who, Rat Pack, etc. on my Sonos and I can rattle the house. Sometimes though, you gotta turn it off and pay deep attention. (And sometimes you crank up Will Smith and get jiggy with it when you are just ready to be done, but there is more work left.)

2.Where did you do most of your writing, and what was your usual setup? 

William Assaf: At home, with a three-monitor display. Usually had Word open on the left, OneNote and a web browser on the middle, and an RDP session to an Azure VM running the latest SQL Server instance right.

Randolph West: Under the bed, in a veil of tears. Sometimes on the couch while watching Supernatural on TV.

Sven Aelterman: I did most of my writing in Microsoft Word, between page 1 and 70.

Mindy Curnutt: Most of my writing was done on the 3rd floor of the Richardson, TX library (the quiet floor). I tried Starbucks, but it was way too noisy, and at home there are way to many distractions. Once at the library, the only distraction I could find was to walk around looking at what books they had, and that got old after awhile. You know you can bring coffee in the library with you?

Louis Davidson: I have a home office, where I do most of my work. It is great because it it the same computer I work on daily, with 1 21:9 and one 16:9 monitor connected to a Surface Pro 4 to work on, but sometimes it can be a drag sitting where you work all day as well. 

3.Did you learn anything about your personal writing skills or habits while working on the book? 

William Assaf: During the writing of the book, I became incapable of correctly typing certain wrods like authetnication and premisis, due to overuse.

Randolph West: MacBook Pro for life! I learned that I don’t like Microsoft Word.

Sven Aelterman: About the same as what I learned while writing the answers to these questions: give me a deadline, and I’ll find a way to push it back.

Mindy Curnutt: Yeah, I think I'm better at just focusing on one chapter at a time. Writing a book is a BIG commitment.

Louis Davidson: I was tech editor, so I didn’t directly write any of the text, just comments about it. Having written several books though, it is very similar. My worst habit that is most trouble is that I can’t stop when I have a project. So I get a chapter from a writer, and I say I need a week, but then I am up until 3 trying to finish!  

4.What's your favorite new feature of SQL Server 2017? 

William Assaf: The WSFC-less Availability Groups. So many uses, so much easier. Combined with automatic seeding, AG's are getting pretty close to point-and-click.

Randolph West: Linux support. Adaptive query processing. Linux support.

Sven Aelterman: Without a doubt, the fact that Reporting Services is a separate download. Everyone loves an additional installer.

Mindy Curnutt:  Resumable online index rebuilds!! How many times could I have used this. I wonder if Ola's updated his scripts yet to take this into consideration?

Louis Davidson: I am into database coding/designing mostly, so graph tables are highest for sure. They are pretty rudimentary now, but the future is very bright in what they are adding in a future version.

5.Now that the book is (almost) done, what now for your spare time? 

William Assaf: There's still edits, but... I have to catch up on a lot of family time, wife time, and me time (mostly video games, books, scotch, and a gym membership). Some training and online videos related should be coming soon.

Randolph West: Now I can go back to the stuff I abandoned in June, like spending time with my husband, as well as acting, directing, feeding my dog...

Sven Aelterman: I can’t help but look forward to reading all the glowing reviews on Amazon.com. And writing errata. And cursing at Microsoft for changing something else in Management Studio 17.4325.1 or Azure SQL Database that makes the book look like we wrote it in the last century.

Mindy Curnutt: Spare time? Hahahahaaaa (cough, cough). I'm choking. Hold on a minute....



No spare time. Never was spare time. What exactly does that feel like anyway?

Louis Davidson: Well, I am standing in a queue at Disney World writing these interview answers, though that can’t last forever, since magic comes at a price, and that price is cash. But after the holidays it is back to writing blogs, articles, and perhaps my own new book.

6. Thinking back, what parts of your career up to now specifically came in handy when writing this book? 

William Assaf: The recession pushed me into consulting in 2007, and now I spend most my time with health checks and performance tuning. Then, I spend time writing up recommendations documents, investigation results, knowledge transfer, etc. Consulting has prepared me for book writing, it seems.

Randolph West: Copy editing. And the time I worked at a student radio station, when I would pull a 36-hour shift. As for content, dropping tables in production was hugely helpful in knowing what not to do.

Sven Aelterman: Several academic courses come to mind, including Procrastination 101 and Delegating 666 (an advanced graduate level course!). Professionally speaking, more than likely the experience of two months of troubleshooting a SQL Server 2000 failover cluster only to find the SCSI interconnect cable was faulty.

Mindy Curnutt: The painful experience of College Finals.

Louis Davidson: Being a Microsoft MVP, really, and attending a lot of sessions there and at PASS Summit and SQL Saturdays. I mostly code, but I always am listening for stuff to tell coworkers and blog readers about. I try out most stuff myself already, especially when it is inside the box (meaning features where you need only one server to try it out.)

7. Aside from SQL Server Inside Out 2017 (pre-ordered), obviously, what is a cool gift idea for a SQL DBA this holiday season? 

(Sorry if this question is a little late for shipping deadlines...)

William Assaf: I'm a big fan of DonorsChoose.org, a site that helps you crowd-fund projects to improve local classrooms. My kid and I pick out classrooms every year to help. Make a donation to a local classroom with a cool project in their honor, or buy them a gift card. Also, I can personally recommend Randolph’s idea (https://twitter.com/william_a_dba/status/935349132583202817).

Randolph West: A wine cooler, containing a bottle for each day of December. I call it an Advent Cooler. https://www.amazon.com/3-Door-Back-Bar-Cooler-Refrigerator/dp/B01N3AG07I/ 

Sven Aelterman: For DBAs who think the cloud is a fad: http://jobs.tacobell.com/

For DBAs who think the cloud is a real: https://www.amazon.com/s/ref=nb_sb_noss?url=search-alias%3Daps&field-keywords=umbrella

For significant others of DBAs (can’t leave them out!): https://www.amazon.com/s/ref=nb_sb_noss_2?url=search-alias%3Daps&field-keywords=kleenex&rh=i%3Aaps%2Ck%3Akleenex

Mindy Curnutt:  If you have Grandparents (or parents) that aren't tech savvy, there are these really cool photo frames that you can control through the Cloud. I'm planning on getting one for my Grandma.

Louis Davidson: Hmm. Lego always, but something good (DBAs make too much money for something simple. Maybe the Saturn rocket? https://shop.lego.com/en-US/LEGO-NASA-Apollo-Saturn-V-21309. Or Lego Millennium Falcon. https://shop.lego.com/en-US/Millennium-Falcon-75192. Personally, I am low on space for Legos, but I definitely want Lego BB8 https://shop.lego.com/en-US/BB-8-75187. 

Tuesday, December 19, 2017

Start/Stop Group of Azure RM VM's

For testlab purposes, I often have a group of Azure VMs to manually spin up/spin down at once. I've had this script in use for a while and decided I'd comment it up and share.

The SQL Server Availability Groups template in the Azure VM Gallery, for example, creates five VMs in a resource group. Who wants to use the Azure portal in browser to click, wait, x5? And if you try to start/stop too many VMs at a time, they will error out.

PowerShell provides! Since the Start-AzureRMVM and Stop-AzureRMVM cmdlets wait for each VM to finish changing state, it'll take 2-3 minutes per VM, but it's launched in one manual step.
Note, there's also built-in Azure functionality to shut down VMs on a schedule, no coding required. In this case, the use case for this script is to start up my five VMs for a test Availability Group manually, and bring them down when I'm done with my lab. I may also set up the auto-shutdown as insurance that I won't accidentally leave the VMs running.
The below script is quite verbose and well-commented, including the Setup block to get the required module and log you into Azure. You could certainly shorten and simplify the script, but my goal here is readability, and repeatability. (If you have a suggestion on how this can be improved, I'm all for it.)

Specify a resource group name in $ResourceGroupName, and a list of  VM names in $VMs. Important note here: when you have a cluster of VMs, you may want to have them start up and shut down in a specific order. The below example shuts down the domain controller VMs last, and starts them up first.
#Launch VS Code as Administrator

Invoke-Command -script {Install-WindowsFeature -Name "Failover-Clustering" } `
-ComputerName SQLDEV11, SQLDEV12, SQLDEV14, SQLDEV15
Invoke-Command -script {Install-WindowsFeature -Name "RSAT-Clustering-Mgmt" } `
-ComputerName SQLDEV11, SQLDEV12, SQLDEV14, SQLDEV15
Invoke-Command -script {Install-WindowsFeature -Name "RSAT-Clustering-PowerShell" } `
-ComputerName SQLDEV11, SQLDEV12, SQLDEV14, SQLDEV15
#Install-Module SQLSERVER -Force -AllowCLobber 
#Import-Module SQLSERVER

#Must run on the primary node
#TODO: configure initial variable values.

Write-Output "Begin $(Get-Date)"
#Setup: TODO Configure these
$PrimaryReplicaName = "SQLSERVER-0"
$PrimaryReplicaInstanceName = "SQL2K17" #Named instance or DEFAULT for the default instance
$SecondaryReplicaName1 = "SQLSERVER-1"
$SecondaryReplicaInstanceName1 = "SQL2K17" #Named instance or DEFAULT for the default instance
$AvailabilityGroupName = "WWI2017-AG"

#Inventory and test
Get-ChildItem "SQLSERVER:\Sql\$($PrimaryReplicaName)\$($PrimaryReplicaInstanceName)\AvailabilityGroups\$($AvailabilityGroupName)\AvailabilityReplicas\" | Test-SqlAvailabilityReplica | Format-Table

   $AGPrimaryObjPath = "SQLSERVER:\Sql\$($PrimaryReplicaName)\$($PrimaryReplicaInstanceName)\AvailabilityGroups\$($AvailabilityGroupName)\AvailabilityReplicas\$($PrimaryReplicaName+$(IF($PrimaryReplicaInstanceName -ne "DEFAULT"){$("%5C")+$PrimaryReplicaInstanceName} ))"
   $AGPrimaryObj = Get-Item $AGPrimaryObjPath 
   $AGSecondaryObjPath = "SQLSERVER:\Sql\$($PrimaryReplicaName)\$($PrimaryReplicaInstanceName)\AvailabilityGroups\$($AvailabilityGroupName)\AvailabilityReplicas\$($SecondaryReplicaName1+$(IF($SecondaryReplicaInstanceName1 -ne "DEFAULT"){$("%5C")+$SecondaryReplicaInstanceName1} ))"
   $AGSecondaryObj = Get-Item $AGSecondaryObjPath

#Set replicas to synchronous before planned failover
    Set-SqlAvailabilityReplica `
    -Path $AGPrimaryObjPath `
    -AvailabilityMode SynchronousCommit `
    -FailoverMode "Manual" `
    -ErrorAction Stop
    Set-SqlAvailabilityReplica `
    -Path $AGSecondaryObjPath `
    -AvailabilityMode SynchronousCommit `
    -FailoverMode "Manual" `
    -ErrorAction Stop

#Check for when replicas are synchronized.
Do {
$CurrentSync = ($AGSecondaryObj | Select RollupSynchronizationState | Format-Wide | Out-String).Trim()
IF ($CurrentSync -ne "Synchronized") { 
        Write-Output "Waiting for Synchronized state before failover, still $($CurrentSync)"
        Start-Sleep -s 2 
} Until ($CurrentSync -eq 'Synchronized')

#Perform failover
Write-Output "Beginning Failover $(Get-Date)"
Switch-SqlAvailabilityGroup `
    -Path "SQLSERVER:\Sql\$($SecondaryReplicaName1)\$($SecondaryReplicaInstanceName1)\AvailabilityGroups\$($AvailabilityGroupName)\" `
     -ErrorAction Stop `
    #Only include the next line if it is a forced failover
    #-AllowDataLoss -Force
Write-Output "Failover Complete $(Get-Date)"
Start-Sleep -s 10 #Allow failover to resolve

#Return secondary replica to Asynchronous sync
#Note that the values here of Primary and Secondary1 are flipped, because the variables predate the failover.
Invoke-Command -script { `
param($SecondaryReplicaName1, $SecondaryReplicaInstanceName1, $AvailabilityGroupName, $PrimaryReplicaName, $PrimaryReplicaInstanceName)

 Set-SqlAvailabilityReplica `
-Path "SQLSERVER:\Sql\$(($SecondaryReplicaName1))\$(($SecondaryReplicaInstanceName1))\AvailabilityGroups\$(($AvailabilityGroupName))\AvailabilityReplicas\$(($SecondaryReplicaName1)+$(IF(($SecondaryReplicaInstanceName1) -ne "DEFAULT"){$("%5C")+(($SecondaryReplicaInstanceName1))} ))"  `
-AvailabilityMode asynchronousCommit `
-ErrorAction Stop
    Set-SqlAvailabilityReplica `
-Path "SQLSERVER:\Sql\$(($SecondaryReplicaName1))\$(($SecondaryReplicaInstanceName1))\AvailabilityGroups\$(($AvailabilityGroupName))\AvailabilityReplicas\$(($PrimaryReplicaName)+$(IF(($PrimaryReplicaInstanceName) -ne "DEFAULT"){$("%5C")+(($PrimaryReplicaInstanceName))} ))"  `
-AvailabilityMode asynchronousCommit `
-ErrorAction Stop

Get-ChildItem "SQLSERVER:\Sql\$($SecondaryReplicaName1)\$($SecondaryReplicaInstanceName1)\AvailabilityGroups\$($AvailabilityGroupName)\AvailabilityReplicas\" | Test-SqlAvailabilityReplica | Format-Table
} -ComputerName $SecondaryReplicaName1  -Args $SecondaryReplicaName1, $SecondaryReplicaInstanceName1, $AvailabilityGroupName, $PrimaryReplicaName, $PrimaryReplicaInstanceName

Write-Output "End $(Get-Date)"

This is version of the script updated for Resource Manager VMs. For classic VMs, I have a similar script in a blog post here.

Monday, December 04, 2017

Attracting Non-Technical Speakers to Your SQLSat Events

Fellow DBA and community leader Peter Shore of the Columbus SQL PASS UG and SQLSat Columbus OH asked me via Twitter, "What is the best way for us to have a discussion about how you handle SQLSatBR? I am curious about the none data platform tracks, how you attract other disciplines and executives etc." My answer was the draft of a blog post... and a few email and Twitter exchanges later, here we are. Thanks for the blog inspiration, Peter!

This blog post is for SQLSaturday organizers, session selectors, and schedule-masters. 


Professional development and IT leadership talks at past SQLSat Baton Rouge

At SQLSaturday Baton Rouge we try to provide non-technical content for two major audiences: career-minded IT professionals, and also IT leadership

The former is common at many SQLSats, but the latter we find is invaluable to our success as a small town SQLSaturday event in Baton Rouge. In a city of less than 500,000, we had have a 400-600+ person SQLSat event for the last 7+ years because of a broad session lineup.

First off, advertise the tracks you want to fill in your initial Call for Speakers. Emphasize in your Call for Speakers announcement emails that you have a desired track list, and it includes nontechnical tracks. Advertise in your event when announcing it to local user groups, schools, and companies: "Bring your boss! We have a track for IT Manager and CIOs". And yep, these tracks are fairly well attended. Not always full rooms, but we get a critical mass of people for good audiences and conversations. 

Firstly, jobseekers/career switchers/students is an obvious audience, they're probably already in attendance at your SQLSaturday event. It's the IT Management/CIO-level content that we try to add too, because that attracts net new attendees and even more importantly the decision-making crowd, which increases your event's attractiveness and ROI for the sponsors

So how do you get speakers for these two audiences/tracks?

Well, marketing budgets aren't exactly a thing. We remain mostly attended by word-of-mouth, we believe. We have a slick one-pager (feel free to copy), and we've put it in various break rooms, coffee houses, bulletin boards, etc. Reach out to local colleges and universities of course. But to get speakers during your Call for Speakers campaign, you need to reach other motivated professionals like yourself. Start with social media platforms of course, Slack, Twitter, LinkedIn, are good starts. But as a community event organizer and/or SQL Server user group leader, try to become aware or at least reach out to network with organizers of user groups for .NET, SharePoint, VMWare, game developers, Women in Technology, Agile development, IT Pro, etc. 

To fill non-technical tracks for career-minded professionals at your SQLSaturday events, the formula is takes some legwork, but is probably familiar to you:
  1. You can encourage your speakers to submit non-tech topics, most professionals do already have tips for being interviewed, career growth tips, lifehacks type of presentations. In general, you should always advertise a list of tracks/topics to fill during your Call for Speakers.
  2. We encourage these types of professional talks at our annual networking night, and provided some sample topics here.
  3. Reach out to leadership development and young leaders groups. Reach out to local versions of "Shark Tank", young entrepreneurs and similar groups, make sure they know there's a local conference with a track for their topics.
  4. For topics geared towards resumes, interviews, and job hunting, reach out to local HR departments or the Society for Human Resources Management (SHRM).
  5. Reach out to Toastmasters International. They're great for this sort of thing, and can likely provide either a sample Toastmasters meeting or communications-focused sessions.
I bet there are presences from both SHRM and TM (or similar) in most towns in the USA. Both of these groups have conferences of their own where they give talks on interviewing/being interviewed, career development, professional communications, etc.  (Keep in mind some speakers in these organizations may be used to getting paid for speaking - be professional and polite about telling them that your conference is a free conference.) 

To fill tracks for the IT Management/CIO track, here are some ideas for recruiting sessions and speakers. At SQLSatBR, we've had IT Management/CIO/Executive tracks at the last three events. Take a look to see what kind of speakers and sessions we picked in the past, and feel free to reach out to those speakers for your own events.
Presentations for those who perform interviews can be especially useful for the IT Management/CIO-level crowd, especially if you can find someone (like my wife and most HR professionals) who know the law around what you can/cannot ask in an interview, not just to be EOE compliant, but to avoid discrimination. IT leadership will definitely want (need?) to attend that. Some IT professionals may also give presentations on technical interview tips.

Other good ideas for tracks for the IT Management/CIO crowd are "strategic" (aka salesy) presentations by Microsoft or other vendors, without being too technical (or too salesy). Reach out to local IT consulting firms or offices. They probably already run their own "CIO luncheons" or "executive roundtable" meetings. The head of my employer, an IT consulting firm, pulled in all the CIOs he knew (many of which were customers) and honored them with appearance on a panel about "cloud strategy" or "modern IT strategy" or "hybrid datacenters." CIOs eat that stuff up.

Inviting local IT Managers/CIOs to be on a panel can be a nice honor, especially if you advertise it as such. Don't be afraid to organize your own panel, perhaps facilitate it yourself with questions about IT strategy, future trends, past successes/mistakes, etc. A good panel discussion may be 50/50 panelists/audience Q&A. It's not difficult at all, in fact we do a similar panel-style presentation around Jobs in IT

Finally, if you're looking for sessions to place in a CIO track, Business Intelligence talks work well, especially presentations for "executive dashboards". PowerBI sessions, as long as they are not too technical, would be very popular for a IT Management track. Anything on data warehousing, data lakes, "big data", or presentation-layer stuff (like SSRS mobile reports) would be appropriate. 

So fill non-technical tracks for the IT leadership/decision-maker crowd:
  1. Reach out to SHRM or other organizations for HR professionals. 
  2. Reach out to IT consulting firms, especially ones that do Business Intelligence consulting. 
    1. Reach out to anyone in your town who has ever presented a "CIO Executive Summit" or similar.
  3. Reach out during your call for speakers to local chapters of the AITP, ACM, itSMF, VMWare, IT Pro, and other technology chapters. Again, something I've encouraged for a long time - involve other community user groups in your SQLSaturday event, including but not limited to the .NET group.
  4. Reach out to anyone in your town who does "leadership consulting" or "executive coaching", they'd probably jump at the opportunity to present and work in a subtle pitch for their own services. Be sure they are familiar with the format and expectations of your event.
  5. Consider organizing your own Panel discussion of local IT leadership or thought leaders.
  6. Consider some Business Intelligence sessions for a CIO track, especially sessions involving executive dashboards, or BI sessions that are more strategic or design-oriented in nature.
Let me know if you have any questions, and best of luck organizing your next SQLSaturday event!

Careers in IT Panel discussion at SQLSatBR 2016