A Snippet on Snippets

I’ve never used Snippets before, mostly because I have always had a personal script repository stored in the cloud along with Intellisense or a SQL Formatting add-on. Lately though, I’ve found it a bit easier to store commonly run scripts within Management Studio using Solution Explorer. I mentioned this in a post that Aaron Bertrand reviewed for me, and he suggested Snippets as an alternative. Snippets are something I’ve been meaning to look into for a while. These are only available in SQL Server 2012 and later, but I’ve had access to that at work for about two years, so I’m long overdue to explore this feature.

If you right-click in Management Studio, you have the option to Insert Snippet, which then provides you with a unique option list of sub folders with snippet files.

InsertSnippet

InsertSnippet2

Choose a snippet, and you will get a default script similar to this:

CreateSampleProcedure

Now, the really neat thing about snippets lies in the highlighted sections in the image above. If you update the parameter names in the declaration, they will automatically update in the select statement as well. That could save some time and naming issues while creating a new procedure. The best way to learn exactly how to recreate this feature is by reading the existing snippet files for examples.

I assumed adding a new snippet would be a breeze. I assumed I’d be able to set everything up with a bit of click-guessing, and I’d be snippet shortcutting my way to scripting simplicity in no time. I assumed wrong. Don’t jump into snippets blindly.

I did.

If you click on Tools, you can select Code Snippets Manager. You will then see all the existing folders with their corresponding snippets. I want to create new snippets though, so I’ll try Add, navigate to the folder where I store my scripts, and Select Folder.

CodeSnippetManager

Add

This is the Snippet Manager after I have successfully Added my…empty…folder

Alright, it imported the Folder into the viewer, but it’s empty. I know there are SQL scripts in that folder – what’s wrong? Let’s try Import. Navigate to the folder again…and it’s still empty! What’s the big idea!?

ImportSql

After calming down, I looked carefully at the above screen.  If you notice in the bottom right corner, the only available extension is .snippet. I tried to select other types, but that drop down menu is a lie. There are no options. I know that drop down menu is default behavior for Windows, but all it did was lull me into a false sense of freedom for being able to choose other file types.

At this point, it’s obvious that I should not be trying to add snippets on a whim. I highly suggest you do a bit of reading on snippets or at least find the existing snippets and open a file or two to see how they are formatted. You can find the exact location by using the Code Snippets Manager. Click on one of the default folders, and it will display the file path in the Location header. The screenshot of the Manager above has the file path displayed. It will save a lot of time and confusion if you choose to read a bit before just trying to make it work. Learn from my mistakes.

Still, I tried to make it work. I made a new snippet file with only a SQL script inside. By doing this, I was finally able to add a file, but immediately received the following error.

InvalidFormat

Only then did I give up and read the documentation. I like to poke around at a new feature for a bit before reading all about it, as it helps me understand everything while later researching because I’ve at least seen the options before. This was one of those times where it was a much worse idea than normal to work that way.

Turns out, once I created the .snippet file, I still needed to add XML notation to it so that SSMS could read the file. Any folder I Add will auto populate with new snippets saved to it. That’s pretty handy. I’m no XML wiz, but the below code can be copied and pasted into each snippet, with only minimal, obvious changes.

<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets  xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
	<CodeSnippet Format="1.0.0">
		<Header>
			<Title>Snippet Title</Title>
            <Shortcut></Shortcut>
			<Description>Code Snippet for X construct.</Description>
			<Author>Author's Name</Author>
			<SnippetTypes>
            <SnippetType>Expansion
			</SnippetTypes>
		</Header>
		<Snippet>
			<Declarations>
                <Literal>
                <ID>Condition</ID>
                <ToolTip>Condition to evaluate</ToolTip>
                <Default>Condition</Default>
                </Literal>
			</Declarations>
			<Code Language="SQL">
			<![CDATA[
--SQL Script Here
$condition$ --This will have a Tool Tip as defined above.
$selected$
--Code following will appear after selected text.

]]>
			</Code>
		</Snippet>
	</CodeSnippet>
</CodeSnippets>

You should edit the Description tag for each snippet and then just copy and paste the SQL inside the code header. The Author tag will be useful to identify snippets, but once you have a default script saved, that should not require upkeep. The ability to make shortcuts is awesome, but I doubt I’ll remember the keyboard combination and use it enough to warrant a shortcut.

The SnippetType should be Expansion (for a regular snippet) or SurroundsWith. You should look at the existing SurroundsWith snippets before crafting your own if you opt for that choice.  There are variables, like $selected$

, that you should include in a SurroundsWith snippet.  That variable will allow you to select a script, then add the SurroundsWith snippet to place the inserted code around the selected text. Omitting the variable will cause the SurroundsWith snippet to just replace the selected script – not very helpful. To select a SurroundsWith snippet, you will need to right-click in the Query Pane and choose Surround With, which is directly underneath the Insert Snippet command. You won’t have to drill through as many sub menus if you choose SurroundsWith, because there are fewer snippets there.

The Declarations tag allows you to identify objects in the string that will have a tooltip. The ID tag should match a variable name used in the Code tag, with the ToolTip tag being the text you want displayed when hovering over the text. For example, the Create Table snippet has an ID tag of $TableName$, and after inserting the snippet, the ToolTip identifies its purpose.

SnippetHover

Finally armed with a valid snippet, I went back to Code Snippets Manager. Now I can see my sample script along with the tagged values.

SnippetTitle

Adding a snippet for BEGIN TRAN would be a great way to test out the different SnippetTypes. Create the snippet as an Expansion first with no special variables. Next edit the file and change the SnippetType to SurroundsWith and add the $selected$ variable between your BEGIN TRAN and ROLLBACK/COMMIT lines. Highlight a SQL  statement and insert the snippet through the SurroundsWith option. Notice that this time, the snippet indeed surrounded the statement. You can include a variable in an Expansion snippet, but it’s best to categorize correctly, so that you can find a snippet next time with minimal effort.

Summary Snippets definitely have some merits, but it takes a bit of work and understanding to create them. Make sure to source control or at least back them up once created. For anyone with a SQL formatting add-on, snippets probably aren’t overly exciting (but neither was Intellisense). Anyone using the native environment though, it’s your only way to create code shortcuts.

The interface is infinitely more complicated than it should be, but so are many other aspects of Management Studio. You should be able to click Add in the Code Snippets Manager, paste in a script, select Expansion or SurroundsWith as the Snippet Type, and then save it to the menu level of your choice. Alternatively, you should be able to Import saved SQL files to be snippets without any special formatting. Then add in the special variables as desired.

I originally thought snippets could be a replacement for how I stored most of my scripts, but instead, it’s more appropriate as shortcuts for only very popular code. Except for a few constantly used scripts, I doubt the easy access will outweigh the setup costs to use snippets for me. I know it’s not overly difficult to copy and paste the XML into a new file, but snippets are all about being lazy in the first place!

Renaming Policies, Conditions, and Categories

I have a habit of creating a new policy with only a vague idea of what I want to accomplish with it, and by the time I’m finished, the policy may have taken on a new tone. Other times, I make a test policy that turns out to be a great idea that I want to keep around. The problem with this? I start by naming the policy something terrible like TestPolicyDoThisThing. After making a few grouped expressions and half a dozen lines or more in the condition, I really don’t want to recreate it by using the GUI or script. I just want to rename the policy to something better, like RealPolicyDescriptiveName.

Renaming Policies is not easy or intuitive.

Right click on the policy, Rename…or not.

Rename Policy Missing

Where is Rename?

OK, click on the policy, then slowly click again to edit the name…

Why is there no interface for renaming a policy? I can click on a table to rename it, but not a policy? Explain to me how it is less dangerous to rename a table accidentally (by misclicking and typing something without paying attention) than to rename a policy. It isn’t. In fact, it’s much worse to randomly rename your tables. I hear that might break some applications.

So I poked around a bit. I really had no desire to recreate policies from scratch, and it absolutely should not be necessary. Luckily, there is a tsql command to rename a policy, and it’s fairly simple to use. SP_SysPolicy_Rename_Policy can rename a policy, just supply the @name parameter and the @new_name parameter.

Execution Failed

Wait! I know this exists!

It will help if you specify the correct database to use. Try executing the procedure with the fully qualified name, msdb.dbo.sp_syspolicy_rename_policy, and it will work successfully.

Execution Successful with FQN

Fully Qualified Names are best practice for a reason

Renaming Conditions works exactly the same way as policies when it comes to renaming them, Rename Condition is oddly absent, but a similarly named command will solve the problem. You can execute msdb.dbo.sp_syspolicy_rename_condition using the same parameters as the policy rename.

Missing Rename Condition

Renaming Categories is easy, strangely enough. There is a user interface for policy Categories. Click on Policy Management, then Manage Categories. This window will allow you to add new, and edit existing, categories. This is a great feature, and helps you keep track of Condition names. Ironically, it is also the feature I would use the least out of the renaming options.

Manage Categories Exists

So how did this sneak into the interface?

Edit Categories with Manage

Renaming and adding categories is as simple as typing in the Name field.

Summary As much as I like Policy Based Management, I wish that Microsoft would have finished building the feature – at least it feels like PBM is unfinished every time I work with it. So many facets are apparently missing, PowerShell execution is convoluted at best, and interface options are just strangely absent. Something as simple as renaming policies and conditions should not be available only through a hidden stored procedure. Policy Based Management is a great feature of SQL Server, and it’s one of the few standard edition features they have added lately. Why couldn’t Microsoft put a bit more effort into it and make it as powerful and easy to use as its potential implies?

Book Review: SQL Server 2012 High-Performance T-SQL Using Windows Functions

I’ve been extremely lazy about reading technical books for the past few months. Primarily I’ve been reading SQL blogs and haunting the SQLServerCentral forums instead. While still informative, I have a ton of SQL Ebooks that demand to be read. Part of my New Year’s Resolution was to start reading both technical books and novels again…so I’m on my fourth novel! However, I just finished my first technical book of the year. I read the first chapter of a few books before settling on something “easy”.

SQL Server 2012 High-Performance T-SQL Using Windows Functions by Itzik Ben-Gan is actually a very short for a SQL book, coming in at only 210 pages. With such a lengthy title, it’s shocking that the book is so short and concise. It is edited well, having a tiny errata containing only one technical mistake on a sample script and two minor typos. This is awesome considering many books I’ve read are plagued with gigantic errata. There is also a companion website where you can download source code and the entirety of Chapter 1.

TSQLFunctionsChapter 1 – SQL Windowing
Pages: 32

The introductory chapter provides a great overview of what the book has in store, making it a perfect sample download. It explains the options available for window functions and which functions are new in SQL 2012 (LAG, LEAD, etc.). The chapter provides some quick examples to whet your appetite with tantalizing hints of more details to come. The chapter is kept light enough that you want to keep reading while still providing useful information, all the while promising great things to come.

Highlights:

  • Programming options explained – Set Based, Window Functions, and Iterative
  • Preview of Chapter 5 in the form of sample scripts and situations
  • Elements of window functions – Partitioning, Ordering, and Framing

Chapter 2 – A Detailed Look at Windows Functions
Pages: 48

This chapter goes into explains partitioning and framing. If you are even slightly fuzzy on the concept, this is a very important section. I was surprised at just how much detail the author went into to explain the technicalities.  On the other hand, it’s a deep dive that could overwhelm someone trying to read the book with very little prior knowledge of window functions. In that case, I suggest you skim the chapter and come back to it after you better understand what window functions can do to save your queries.

The thing that bothers me is how the author melds theoretical SQL window functions in with the TSQL supported functions. I appreciate that he mentions ANSI standards, but if it’s not directly supported in TSQL, I don’t really want to learn about it in detail while reading about TSQL functions. Now I’m afraid that I will try using NTH_VALUE and then get horribly disappointed when I remember it’s not supported. Separating the functions that are not supported by SQL Server into a different section or chapter might have been the better route here.

Highlights:

  • Aggregate Functions – Distinct and Nested
  • Ranking Functions – Row_Number, Ntile, Rank, Dense_Rank
  • Distribution Functions – Percent_Rank, Cume_Dist
  • Offset Functions – Lag, Lead, First_Value, Last_Value

Chapter 3 – Ordered Set Functions
Pages: 20

The explanations in this chapter get very “mathy”. If equations scare you, you might need to read quickly, or just look at the general solutions and move on. Basically he provides script alternatives and the math necessary to get those alternatives to work. Very cool; a bit complex.

Highlights:

  • Hypothetical Set Functions – Ranking and Distributions
  • String Concatenation – Using Coalesce, Stuff, and XML path

Chapter 4 – Optimization of Windows Functions
Pages: 32

If you don’t understand query execution plans, a lot of this chapter will go over your head. It even starts with such a warning. There are numerous examples of execution plans and each example explains why it is slow or fast based on index scans, seeks, parallelism, etc. Sample data is provided for testing.

Columnstore indexes are mentioned, but for window functions to perform best, normally these should not be used due to sorting issues. It’s not like many people are using them yet anyways.

The detailed information on framing was my favorite part of this chapter. I’ve used ROWS UNBOUNDED PRECEDING to calculate running totals in SQL 2012 with great success, but I had never studied the particular differences between ROWS and RANGE (hint, stick with ROWS by default) or considered the many applications of the BETWEEN operator for framing, especially for pulling specific rows quickly using LEAD or LAG.

Highlights:

  • Indexing Guidelines for Window Functions
  • Improving Parallelism using Apply
  • Optimizing Functions using Framing (added in SQL 2012)
  • Distribution Functions – Rank and Inverse optimization

Chapter 5 – T-SQL Solutions Using Windows Functions
Pages: 78

Some people will skip directly to this chapter, and really, once you have read the book, this is most likely the section you will flip to when you need a trick query.  The format of this chapter is: Introduce an issue; provide a set based, iterative, and windows function solution; then compare the time and effort to do each.

It’s a great chapter to glance over once, and then keep mental notes of what it contains for when you need  to do some odd math, like calculate data islands or gaps.

Highlights:

  • Number Table
  • Date Sequences
  • Paging
  • Removing Duplicates
  • Pivoting
  • Top N
  • Mode
  • Running Totals
  • Intervals
  • Gaps
  • Islands
  • Median
  • Conditional Aggregate
  • Sorting Hierarchies

Summary

Overall, this was a great read that I recommend to anyone who writes TSQL. Although I’ve worked with window functions for a few years and have experience using the new SQL 2012 functions, the exhaustive detail that the author went into is important for tuning, especially considering the relatively short reading investment necessary. The last chapter will continue to be a great resource for rare query requests, especially since it  provides non-window function alternatives – in case a query has to run against an older version of SQL Server.

Windows Server 2012 Training Summary

Last week I attended a four-day training session titled Windows Server 2012 R2 Capabilities, Administration and Support. The class consisted of nine modules with nine labs. While this class was primarily directed at Windows Administrators to provide a general overview of the operating system, it did provide a few great snippets of information, but also a few terrible ones. I’ll try to keep this as brief and yet informative as possible…I’m sorry about the wall of text.

Day 1

The first day was an overview of new and exciting features in Server 2012 followed by an introduction to PowerShell administration.

This is the most important day of class for someone new to Server 2012. The first half of the day introduced the new layout of Server 2012. It’s the same design as Windows 8, so it is very daunting to the average person who hates 8. The instructor spent awhile explaining how to avoid the start screen and how to find programs and pin them to the desktop or taskbar.

A lot of time was spent looking at the new Server Manager. I want to use Server 2012 exclusively just for that dashboard. It’s that awesome. Besides looking cool, most important configurations are right there; no need to hunt all over the system.

ServerManager

Remote administration is great in Sever 2012. Now you can create Server Groups and add servers to those groups using Server Manager. You don’t have to log into every box or use complex PowerShell statements. It’s a bit like having a Central Management Server in SQL Server. I don’t know all the limitations yet, but I plan to explore soon.

Interesting note: If you promote a server to a Domain Controller using the GUI, everything works normally. If you promote via PowerShell, Server Manager will forever have a task assigned asking you to promote to a DC. Supposedly this is fixed for Windows 10, but it is a low priority fix never going to happen in Server 2012.

The PowerShell module was little more than an introduction on how to open PowerShell and use Get-Help. Throughout the rest of the class, scripts were provided to complete tasks as an alternative to the GUI, but learning PowerShell takes more than one or two rushed hours. I pitied anyone there who had never touched PowerShell; I’m sure all it did was reinforce their idea that PowerShell is too complicated to learn.

My one piece of advice is that you triple check that you are on the correct machine before you run a PowerShell script! To my own embarrassment, I admit that I had to troubleshoot and repair configurations more than once because I wasn’t paying attention to which Virtual Machine I was playing on.

Day 2

The next two days were mostly a blur of features that I will probably never touch again, except perhaps in lab scenarios. I’ll briefly mention a few things.

One module consisted of Hyper V setup and administration. All the virtual machines used Hyper V, and the class started feeling like a marketing ploy to convert your existing environment to Hyper V. The feature looks nice; I just wish my home processor supported SLAT so I could run it. I’ll stick with Virtual Box till I upgrade hardware.

Also mentioned today were ISCSI Storage, Data Deduplication – this sounds useful for reducing storage sizes, Work Folders, Dynamic Access Control, and Offload Data Transfer. The instructor skipped a section on DAC Management.

Labs consisted of joining to a domain, setting up a SCSI disk, preparing virtual machines for SAN storage using Storage Roles, creating an ISCSI target, and connecting to the ISCSI target. There was mention that two of the virtual machines will be clustered later in the week. Cool!

If you are interested in working with Server Core, but are afraid to make the jump, you can install the GUI, configure the server, and then uninstall the GUI. Optionally you can leave Server Manager available or go straight to the minimal core setup and just do your administration remotely. Sounds pretty cool, and I plan to play with this feature in home labs too.

Day 3

Active Directory & Networking were the topics for the third day of class. Another day of topics that were mostly over my head and that I would not have access to in my regular role.

A few notes:

  • No support for Domain controllers in a live environment for virtualization.
  • Prior to 2012, restarting or restoring a DC could cause failure when the RID pool would issue RIDs below the pool amount from the rest of the DCs. Server 2012 now validates the pool before issuing RIDs.
  • Recycle Bin must be enabled for recoverable objects in AD
  • New or upgraded features: DHCP Failover, Policy based admin, PowerShell DHCP module, DHCP integration with DNS
  • IPAM: new feature that doesn’t work that well. It works till it breaks, then you are doomed. Manages IP addresses, domain names, and device identities (instead of using something like Excel)
  • DCPromo is deprecated, but still available through PowerShell. Evidently the Microsoft instructor still prefers using deprecated features. Curious.
  • In 2012, you can restore an AD user using “Restore To” to eliminate the issue of having to restore the entire structure of an old user, this will let you put them anywhere without issue.

Now for the crazy scenario of Day 3…

While setting up new accounts using Active Directory Administrative Center, the instructor was apparently showing user passwords in clear text. Someone pointed this out, and the instructor began a VERY long discussion on why this is not a security issue. He argued that administrators should be able to see any account’s password if they created it. After pressing that this is a major security hole, he spoke with an AD Admin he knew. Finally he realized that the field in question was actually the user account field, he was incorrectly typing the password there. Crisis averted…but the fallout remains.

WHY would a Microsoft employee defend the idea that clear text passwords are not a security flaw? This still is very concerning to me. In this hypothetical situation, I don’t care if you audit logins; if the admins steal user passwords, that they can plainly see, it would be tracked, sure, but we know people share passwords with other accounts even if it’s a horrible security practice. Why would you make it so easy for an admin to possibly gain access to someone’s email, utility logins, or bank accounts? I could write a whole, very long blog about this, so I will move on.

Rushing to finish early on Friday, we covered three modules on Thursday, the last one being Failover Clustering. I had been waiting all week for this one! Too bad it was marred by marketing lies.

First, the cool aspects. We got to build a failover cluster using virtual machines. However, it went perfectly, so obviously this was not a real world scenario! Nothing much to report about the cluster creation, but if you have made few or no clusters, this part of the class is fun.

A few “new” features:

New in Server 2012! One node clusters! Yet in 2005…

New in Server 2012! Split Brain scenarios gone! Quorums have been around for a while.

Day 4

The last day was a blur of 80 slides smashed into about an hour, and then a lab that was demoed to us with the hopes that we would work along with the instructor. Lots of rush to get us out the door on the last day. Not much I can say about the day, because I did not have a lot of time to write notes or process the information.

Remote Desktop Services was the topic of the day. This can be installed via Server Manager, and then administered from the same spot. Weird caveat here is that everything grayed out is installed, while anything highlighted in green has not been deployed. The appearance is a bit misleading.

Summary

Major issues throughout the class were out-of-date lab documentation, duplicate labs, typos, and generally bad directions. For instance, the written instructions directed us to use the laptop has a host machine, but in reality we should have used one of the virtual machines. Another time, an earlier lab invalidated the directions for a later lab. We had to undo our earlier work (once the instructor realized the error) so that the later lab could be completed. The three huge hard copy manuals were even further out of date than the electronic copies. They were nothing more than a massive waste of paper.

The first day of the class is great for someone who is scared to work with Server 2012. It should dispel the fears of a new layout and excite you for all the new features at your fingertips. Beyond that, the benefit of the class is limited to those who would be dealing with every feature on a day-to-day basis. While I appreciate knowing a bit more of server administration, I feel that reading a few articles and then working in a 2012 virtual environment for a few hours would have been just as effective, if not more so.

Windows Server 2012 Training

This week is exciting because instead of an average work week, I will be attending some free Windows Server 2012 training provided by my employer. I love free training; I wouldn’t care if it was in fletching or how to make home-made soap (actually those sound neat). Something actually related to my career though? That’s awesome training! I’ve never used Server 2012 outside of a lab environment, and even then I’ve only touched it a few times in Virtual Machines since no one seems fast to move to it around here. I’m looking forward to checking it out seriously and uncovering all its mysteries.

I think that I have a slight advantage over some of the people who will be in the training, because I actually have Windows 8 on all my home computers. Obviously a lot of people have been slow to adopt the unfamiliar operating system and Server 2012 follows the same style as Windows 8. I’m one of the many with hopes for Windows 10 improvements (obviously so amazing it required a whole number skipped) but I also have realistic expectations of mediocrity for that.

Although, on second thought about my advantage, I also modified Windows 8 to avoid the Metro/Start screen and always boot to desktop. I then installed Classic Shell to get the start menu back. Maybe I’m not so savvy with the new OS after all. I did try to use Windows 8 without any modifications for a few months, hoping that 8.1 would have enough corrections to keep me happy. I caved after too many minutes spent in frustration trying to track down various programs and administrative settings. The second time I had to resort to the Run command to adjust a setting, I was done.

I know Server 2012 improved clustering considerably, and that’s what I’m hoping to demo the most while in the training class. I’ve been using Availability Clusters with Server 2008 and I know that’s probably causing issues that could be fixed with an upgrade. I can only hope that this ~30 hour class will cover clustering in detail.

On the other hand, I’m most concerned that the title of the training is “Capabilities, Administration and Support.” Learning how to troubleshoot the OS is great, but I don’t want to be stuck learning how to provide support for it to others. Since I don’t really know what I’m getting into today, I’m more than a little afraid too much time will be spent on that.

I plan to take extensive notes on the training so that I can report the highlights and disappointments, which I will then report next week.