SQL Server

SQL Battleships

When Battleships Met SQL Server, like Bailys meeting Coffee, but better.

Do you have an equal love of the classic two player board game Battleship and also SQL Server database development? If the answer is yes (which it obviously should be) then this is the blog post for you!

The next question that I’m more than happy to answer for you is; how can I combine these 2x loves?… Well my friend, please continue reading to find out.

SQL BattleShips

So, let’s create ourselves a SQL Server database to play a game of Battleships using tables as our grid and ship layout, then let’s use stored procedures as our weapons in which to fire on your opponent (executing statements against their tables). The other unique advantage of playing SQL BattleShips is that both players don’t need to be in the same room verbally querying each other’s grid references and board. In other words, why use English to query this data from a person when you can use SQL to query a database table.

Getting Setup

If you haven’t already done so, please download the zip file from the Downloads page in the database backups section labelled BattleShips.zip. It contains a SQL Server 2014 database backup file of the database called BattleShips. Please restore the database to a suitable SQL instance (and please check the assumption section below). I say a suitable SQL instance because apparently my previous employer didn’t think we needed it on every production server in the organisation!

A Few Assumptions

  • You or the person creating the new game, which doesn’t have to be one of the players has sysadmin access to the SQL Server instance where the Battleships database has been restored

Why? The new game procedure alters various database objects and setups up SQL logins for each player.

  • The SQL instance has database mail setup and is working.

Why? For ease of automation and sending each player details of what to do next. Including login details.

  • The SQL instance is setup for mixed mode authentication.

Why? Each player will have a SQL authenticated login created on the SQL instance in order to be able to connect to the BattleShips database and play with their respective objects. The database users then have a set of permissions defined which prevents each player for seeing each other’s tables (ship board).

  • You and your opponent are aware of how to edit/query database table data, plus views and execute stored procedures with required parameters in SQL Server Management Studio (SSMS).

Why? Because this is SQL BattleShips and it can’t be played using English queries.

Creating A New Game

To create a new game, execute the following procedure adding an email address for each player as required.

1
2
3
4
5
6
USE [BattleShips]
GO
 
EXEC [dbo].[NewGame]
	@Player1EmailAddress = N'bobby.tables@somewhere.com',
	@Player2EmailAddress = N'chuck.norris@somewhere.com'

Using database mail players will then receive details instructions on what to do and where to connect to using a set of SQL credentials provided. For information the body of this email is sorted in the [dbo].[EmailText].

To isolate players from each other as part of the game SQL authenticated user accounts are created for each player which only grants the necessary permissions to the respective player’s databases objects. This is handled using database schema’s and execute only permissions to certain procedures. Players will not have access to any other database on the SQL instance.

If you are a sysadmin you can of course very easily cheat, but this is not in the spirit of the game and it is expected that each player only uses their SQL account provided in the email sent out.

How To Play

Following the instructions provided by email, once you’ve connected to the BattleShips database in SSMS, edit your ‘board’ table. [playerX].[board]. Add all your available ships and close the table editor once complete.

BattleShipsBoardSetupWhen you are happy with the strategic planning (layout) execute the following stored procedure. This will validate your board layout and provide feedback if a ship is the incorrect length or has been missed. If everything is correct your status will be set to ready.

1
EXEC [playerX].[ImReadyToPlay]

Once both players have completed this operation can battle commence! Query the table players if you are unsure of the other player’s status.

1
SELECT * FROM [dbo].[Players]

They may also be ready to play or still setting up their board. If you’re playing in the same room feel free to speak to each other, it’s not against the rules, just not in the sprite of SQL BattleShips.

When both players are ready start taking shots at each other’s ship grids in turn, with player 1 going first. This can be done by executing the following procedure. You’ll of course want to update the gird reference parameter value once you’ve eliminated A01.

1
2
EXEC [player1].[TakeShot]
	@GridReference = 'A01'

Following execution feedback will be returned about the success of your shot and the states of both your board and a grid of which coordinates you have already tried. If you use the same grid reference twice you’ll be warned so your shot isn’t wasted.
BattleShipsShots
After a winner has been established by an opponent sinking all of their rival’s ships (or updating all of their ships with an X for hit). The players table will be BattleShipsPlayersupdated with the winner’s status and the loser will no longer be able to take any more shot.

Happy hunting my friends and please give me your feedback on any epic battles, and maybe the coding!

Many thanks for reading.


SQL Server Management Studio Code Snippets

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.

SnippetsNewProc

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.SnippetsIntellisenseList

  • 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

SnippetToolsMenuTo 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.

SnippetManager

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.

SnippetHelloWorld

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.


Using Hyper-V and PowerShell to Create the Perfect Developer Workstation

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.

WindowsFeaturesHyperV

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!

HyperVCreateSwitchFrom 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.

The Guests

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.

The Goal

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.

HyperVVMList

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?!

HyperVRAMWeight

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.

Final Words

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.

RDCMAddMachine

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.

1
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.


Paul’s Frog Blog

Paul is a Microsoft Data Platform MVP with 10+ years’ experience working with the complete on premises SQL Server stack in a variety of roles and industries. Now as the Business Intelligence Consultant at Purple Frog Systems has turned his keyboard to big data solutions in the Microsoft cloud. Specialising in Azure Data Lake Analytics, Azure Data Factory, Azure Stream Analytics, Event Hubs and IoT. Paul is also a STEM Ambassador for the networking education in schools’ programme, PASS chapter leader for the Microsoft Data Platform Group – Birmingham, SQL Bits, SQL Relay, SQL Saturday speaker and helper. Currently the Stack Overflow top user for Azure Data Factory. As well as very active member of the technical community.
Thanks for visiting.
@mrpaulandrew