Monthly Archives: May 2016
Code Snippets are not a new thing. Many languages have them and many plugins for SQL Server Management Studio (SSMS) include them. However I believe the out of the box offering in SSMS is just fine and this post explores how I use the feature and how you could too.
Code Snippet Types
Firstly it is important to understand that within SSMS you have two different types of code snippet. These are:
- Expansion – offering the insertion of a code block into whatever location you put the cursor within a query window.
- SurroundsWith – as the name suggests this will encapsulate code you have highlighted within a query window adding top and tail code blocks from the snippet.
In each case the snippets will only add code to your query window, nothing will be overwritten.
What You Already Have
In SSMS you already have a wealth of code snippets built into the SQL Server client tool and what you may not realise is that when you, for example, right click in the object explorer panel for a new stored procedure, the resulting query window code is taken from a code snippet stored with the SSMS program files.
So that’s just one way to access code snippets within the context of whatever new view, function or procedure you create. The other way is via the SSMS keyboard shortcuts, which as a result of my extreme dislike for the defaults they are now more memorable to me than Ctrl + C and Ctrl + V. If I was ever to actually change them in SSMS options I’d probably be completely lost. Anyway, there are different shortcut combinations of each snippet type.
- Expansion: Ctrl + K, Ctrl + X
- SurroundsWith: Ctrl + K, Ctrl + S
In a new query window when pressed they will give you an IntelliSense style selection list which you can browse through with the arrow keys. Plus Enter and Backspace for forwards and backwards navigation.
The next thing to note about the standard set of SSMS code snippets is that they are stored at the following location (depending on your version of SQL Server/Visual Studio) should you ever wish to copy or modify them. Which if you continue reading you definitely will.
C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLEditor\Snippets\1033
Finally if you care to search your C drive for files with a .snippet extension you will probably be surprised to learn that in the Microsoft world they are used all over the place. Especially if you have full Visual Studio installed. There implementations might vary between coding environments and languages but as a standard they are well established.
Creating Your Own SQL Snippets
Now that we know all about code snippets its time we think about creating our own. This is something I have done and encouraged when working with various SQL Server development teams over the years and every time the practice has been happily embraced. At the end of the day who can really remember the exact syntax for every language encountered on a daily basis… English is ‘ard enough 🙂
Right then, like a lot of Microsoft files underneath the pretty icon and file extension its just XML, not JSON yet, certainly in SQL Server land, RDL, DTSX etc. Well code snippets are no exception and if you have Notepad++ installed when you open the .snippet files syntax highlighting works a treat. So if you care to create a new Notepad file either with ++ or without (your choice) and paste in the following XML. Then save the file to a suitable location with the .snippet extension you’ll have created your first custom SQL code snippet. This is doing nothing more than printing the string Hello World.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
<?xml version="1.0" encoding="utf-8" ?> <CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet"> <CodeSnippet Format="1.0.0"> <Header> <Title>Hello World</Title> <Shortcut></Shortcut> <Description>My first Hello World SQL Server code snippet.</Description> <Author>Paul Andrew</Author> <SnippetTypes> <SnippetType>Expansion</SnippetType> </SnippetTypes> </Header> <Snippet> <Declarations></Declarations> <Code Language="SQL"><![CDATA[PRINT 'Hello World']]> </Code> </Snippet> </CodeSnippet> </CodeSnippets>
Within the above XML you can fairly easily make out the important values which you’ll want to change.
- Title – this is what will appear in your IntelliSense style list within SSMS.
- Description – this will also appear in SSMS next to the highlighted snippet title within the IntelliSense menu as a callout.
- Author – being me in the case of the example above.
- SnippetType – this is important to enter depending on the type of snippet you want as described above (Expansion or SurroundsWith).
Lastly you’ll want to edit the actual SQL script that has been included in the snippet. This is done within the CDATA block of the Snippet parent node and wrapped with square brackets, XML syntax highlighting will help here if available.
Snippet Use and Sharing
To add your custom snippets to SSMS go to Tools > Code Snippets Manager. Then in the dialogue that appears use Add and select the location where your snippet(s) are saved and choose Select Folder.
This snippet location does not need to be a local folder. It could also be a UNC path that everyone in the development team have configured giving them access to custom created shared code directly in SSMS. No need for DropBox shares or separate SQL files in mapped network drives.
I would also recommend defining this snippet location with a set of Visual Studio team settings. But maybe that’s for another blog post.
Here’s the Code Snippets Manager screen where you Add and Select Folder just in case its unclear.
Finally if you’ve done everything right or I’ve told you correctly, when you hit that “well known” keyboard shortcut Ctrl + K, Ctrl +X you’ll see the My Snippets folder (if you called it that) in the IntelliSense menu. Upon selection the callout description will appear and if you go all the way the SQL script will be added to your query window. I did both in the following screen shot just for demonstration purposes.
In a later blog post I’ll include my database backup and restore code snippet files which I use every day. Why just have a SQL file when the snippet file can give you so much more and in a more accessible way?
Many thanks for reading.
So the second challenge I faced after unboxing and plugging in my new workstation at Purple Frog Systems was software. Having customers using all versions of SQL Server from 2005 onwards I didn’t want to tie myself to a particular version locally and I also didn’t want the hassle of running lots of different SQL Server services all on the same host operating system. Especially if I wanted to use Windows 10 as my host, which as we know would have compatibility issues with SQL Server 2005 and our old friend BIDS.
Enter Microsoft Hyper-V
In Windows 8 onwards Hyper-V is available out of the box as a feature which can simply be switched on from Control Panel > Programs and Features > Turn Windows Features On or Off. Apologies in advance if the home editions don’t support this.
Note: be sure to select the Hyper-V Module for Windows PowerShell.
Now with my Windows 10 host now running Hyper-V management services I set about creating a bunch of virtual machines to serve up every version of SQL Server required.
Whenever starting the Hyper-V manager always do so as an administrator. The user interface throws some odd errors without the elevated permissions which could lead you down a rabbit hole of Google searches. Trust me!
Creating a Virtual Switch
For those of you new to Hyper-V creating a virtual switch for your guess virtual machines is one of the most important things to get right and sort out first. It’s very simple to do, but without it your network admins and DHCP police might start questioning what on earth your physical network connection is doing. Again, trust me on this!
From your Hyper-V Manager toolbar go to Action > Virtual Switch Manager. In the dialogue window that appears in the right hand panel choose the switch type of External and click Create Virtual Switch. Give the switch a name and select the External Network: that the new switch will basically impersonate. This will be a sort list if you only have 1x physical network connection.
With an external virtual switch in place any guess machine setup to use it as a network resource will appear on your network in the same way your physical host does. It’s own MAC address, IP address, hostname etc. Also be mindful that Hyper-V will take over how your network connections appear in your host operating system and a CMD ipconfig /all will start to look a little more complex, but this is nothing to worry about and perfectly normal.
Creating the Guest Virtual Machines
To offer some repeatability when creating my guest virtual machines I broke out the PowerShell, hence including the module at install time. With a few parameterised cmdlets I was able to setup several empty virtual machines ready for my guest operating systems. Example below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
#Create hyperv machine New-VM ` -Name $VMName ` -Path $VMLocation ` -MemoryStartupBytes $RAM ` -NewVHDPath $VHDOSFile ` -NewVHDSizeBytes $VHDSizes ` -SwitchName $NetworkSwitch ` -Generation $Generation #Set Number of CPUs Set-VMProcessor ` -VMName $VMName ` -Count $CPUs #Create data disk New-VHD ` –Path $VHDDataFile -Dynamic ` –SizeBytes $VHDSizes `
Download the full script I used here.
Note: I’m not a PowerShell expert, just a big fan of it’s uses.
You’ll see in the full script that I give my virtual machines 2x hard drive files each on different controllers (IDE and SCSI), which are in turn are created on different host hard drives, mainly because my host has 3x physical disks. My original intention was to have all operating systems on mechanical disks and all data on solid state disks. So for guess SQL Server installs the virtual machine data disk would be used to house databases attached to the instance. In practice this didn’t really pay off but then this is just another advantage of using Hyper-V, that the underlying resources can be moved around as required.
If you have time I would recommend having you ISO’s on a separate physical disk to the guess hard drive files. This greatly speeds up installation. You could even run the guess OS hard drive files on your solid state drive just for installation, then move them to mechanical disks afterwards.
Assuming you now have a bunch of virtual machine shells in place I’d recommend the following operating systems vs versions of SQL Server. Mainly to avoid any compatibility issues.
- SQL Server 2005 – Windows Server 2003
- SQL Server 2008 – Windows Server 2008
- SQL Server 2012 – Windows Server 2012
- SQL Server 2014 – Windows Server 2012
- SQL Server 2016 – Windows Server 2016 (assuming the OS is released in time)
I’m not working to talk about installing Windows and SQL Server on each guest. I’m assuming your familiar with that Next, Next, Next process (if you want the defaults!). However I would say that for SQL Server 2012 onwards I did this twice for Analysis services to give me both Tabular and Multi Dimensional services in my guess virtual machine.
If you’ve worked through this post and setup your host in the same way I have you’ll now be able to enjoy the advantages and flexibility of running all versions of SQL Server at the same time with all SQL services in guess virtual machines on your developer worksation.
My virtual machines are also setup with dynamic RAM:
- Initial value: 2048 MB
- Min value: 512 MB
- Max value: 8192 MB
The Memory Weight slider is great if you want to keep all your guess virtual machines running at the same time like I do. If doing some development work on SQL Server 2005 I simply increase that guests RAM priority which dynamically adjusts the less used virtual machines so the host doesn’t get overwhelmed. Plus you’ll only be developing on one version of SQL Server at once right?!
The last thing you’ll probably want to do is install some SQL Server client tools on your Windows 10 host. I went for SQL Server 2012, plus Visual Studio 2012. Then SQL Server 2014, plus Visual Studio 2015. Installed in that order.
Please be aware that the above was only possible with a Microsoft MSDN subscription which provided licencing for the guest operating systems and older versions of SQL Server.
During this setup I also had the advantage of being a domain admin, which meant creating things like service accounts for all my virtual machines was easy, adding my virtual machines to the domain and accessing SQL Server services from my host using AD authentication made easy. If you not a DA SQL authentication and local workgroups is ok, but has its limits for SSAS.
You may want to try using Remote Desktop Connection Manager to access your guess operating systems. In the latest version a VM ID can be including giving console style access without needing to connect to each guess from the Hyper-V manager.
VM IDs can be found with the following bit of PowerShell.
Get-VMMemory -VMName "PF*" | Select VMName, ID | Sort-Object VMName
Very last thing, I mentioned my host machine had 3x physical hard drives. The third of which is a huge 4 TB block. To keep my guest operating systems fairly customer independent you’ll have seen I only gave the standard virtual hard drives in PowerShell 100GB of space each. What I then do is give a guest an additional virtual disk much larger in size which resides on the host big data volume. Work is done here. Then post project completion this customer specific virtual data disk can just be dropped/archives/moved away and the guest machine ready for the next work item. It’s of course a management overhead, but helps keep things clean.
Many thanks for reading.
Once I was blind, but now I see!
In the early part of 2015 (I think) a work colleague and friend suggested that on Thursday evening I attend the SQL Server Midlands User Group. The event was completely new to me, I’d never before ventured outside my professional comfort zone, or even had any appreciation that there was such a gathering about SQL Server locally. Anyway, I registered and got some more details. The event was currently ran by some guy called Alex Whittles.
Thursday evening came around and I made the journey from Stafford to Birmingham and the venue for the user group, the Midlands Art Centre (MAC). Little did I know that this event was to become the catalyst for my career and involvement in the SQL Server community. Needless to say the event was a massive eye opener to how close the SQL Server user community was, not just socially, but in terms of knowledge sharing and skills transfer. Family is the best way to describe it, a massive SQL Family.
Time passed and I proceeded to attend the next few Midlands User Groups, meeting new people and listening to the rich variety of speakers that gave up there time to attend each session. In short I was hooked. I might compare myself to the robot Johnny 5 from the 1986 film Short Circuit needing “more input”. Well the webinars started to flow and I quickly realised that I needed to shed my current skin and public sector employer and engage more with the SQL Server Community with a view to expanding my horizons.
Next I attended the Azure User Group, SQL Relay, SQL Saturday Exeter and then the big one SQL Bits XV (which was also the technical launch for SQL Server 2016). I was honoured to be an official helper at my first ever SQL Bits which felt incredible to be part of this huge event. Meeting people that are as passionate about what they do as I am was truly inspiring.
During this time my ambitions were rewarded and through the SQL Server Midlands User Group that guy I mentioned above, Alex Whittles, became my boss! I joined Purple Frog Systems as a Business Intelligence Developer in March 2016.
So what next? Well my friend, keep an eye on this blog and lets find out.
Thanks Rob for the pictures.