Exam 70-461 passed

I managed to get a last minute booking for the exam yesterday afternoon and have just taken it.  I passed with 925 out of 1000 but as I use this stuff each day I expected myself to do okay.  It’s now onto 70-463 – “Implementing a Data Warehouse with Microsoft SQL Server 2012” which may take some time as I only know the basics of data warehousing.  This is the third and final exam in the MSCA: SQL Server 2012 certification.

The SEQUENCE object

In SQL Server 2012 the SEQUENCE object was added.  This seems to be to get past some of the deficiencies in the IDENTITY property.

It allows you to use the same sequence across multiple columns in multiple tables whereas the IDENTITY property is linked to a particular column within a particular table.

It allows cycling of the sequence and allows you to create the next sequence number before you insert the data into the table.  With the IDENTITY property you need to insert the data first and then get the newly generated identity value.

To create a new SEQUENCE object just use the following format

CREATE SEQUENCE dbo.SimonTest

This creates a new SEQUENCE object named dbo.SimonTest with default properties, including the default BIGINT data type.

The SEQUENCE object has the following properties

  • INCREMENT BY – the value to increment by.  Equivalent to the y value in the IDENTITY(x,y) property.  The default is 1.
  • MINVALUE – the minimum value allowed.  By default this is this smallest value allowed in the data type.
  • MAXVALUE – the maximum value allowed.  By default this is the largest value allowed in the data type.
  • CYCLE or NOCYCLE – says whether to allow cycling or not.  The default is NOCYCLE.
  • START WITH – the value to start the sequence at.  Equivalent to the x value in the IDENTITY(x,y) property.

Cycling is where the SEQUENCE object will reset itself to the minimum value once it reaches the maximum value – I’m not sure when you would actually use this.  It’s worth noting that if you want your sequence to allow cycling and you only want to include positive numbers then you should set the MINVALUE propery to be 1, otherwise if you’re using an INT data type the first value after cycling will be -2147483648 even if you set the START WITH property to 1.  For example

CREATE SEQUENCE dbo.SimonPositiveCycle AS INT
MINVALUE 1
CYCLE;

will create a new SEQUENCE object with an INT data type.  The START VALUE is by default the same as the MINVALUE so this will this 1 and when it recycles it will go back to 1.

Now we want to start using the SEQUENCE and to do this we run

SELECT NEXT VALUE FOR dbo.SimonPositiveCycle

This will return the value 1.  If we run it again it returns 2 and so on.

You can use the SELECT NEXT VALUE FOR function in multiple places including INSERT and UPDATE statements and default constraints.  To use a SEQUENCE object instead of an IDENTITY property you just create a default constraint on the identity column.

All of the SEQUENCE object properties listed above can be updated using the ALTER SEQUENCE statement.

Finally, you can tell the SEQUENCE object how often it should write the current sequence value to disk rather than just storing it in memory.

ALTER SEQUENCE dbo.SimonPositiveCycle
CACHE 100;

means that it will write to memory 100 times before it writes to the disk.  It’s better for performance to write to memory but there is a risk that you lose the current value if there is a shutdown.

Automating DB Unit Tests in Visual Studio 2012 using TFS Build Definitions

A few months ago we upgraded our Database solution for DB Pro in VS 2010 to SSDT 2012.  Just before that I set up some database unit tests but these got forgotten about after the upgrade.

I’ve just been working on getting these back up and running so we can add new tests for the new work we’re going to be doing.  All our DB unit tests are in their own solution rather than being a project within our database solution.  This is because we run our tests as part of a TFS build and the build always complained unless the project was in it’s own solution.  I simply opened this DB unit test solution and it was automatically upgraded.

In 2010 we had a Test List file that grouped the tests into different lists and we just had to tell the build definition which tests from which lists to run.

However, in 2012 the Test Lists are deprecated and we now have to use Test Categories (although they are still supported if you do still want to use them – see below).  There is no Test Category file to replace the Test List file and we need to specify the category or categories in the method declaration for the test.  In the example below I’m assigning the test Scribe_Log_ServiceBrokerETLQueueSend to the categories ETL and ETL_StoredProcedures.

DB Unit Test Categories

Now we just need to configure a new Build Definition in TFS.  In the Process tab choose the Default Template and in the Projects to Build select your DB Unit Test solution.

Now click the ellipses on the Automated Tests row and a new window should open.  Click on Add and the Add/Edit Test Run window will open.  I’m going to use the Visual Studio Test Runner but if you do still want to use your Test List file (.vsmdi) you can use the MSTest Test Metadata File Test Runner.  You can leave the General tab with the default values or updated as you require but on the Criteria tab simply enter TestCategory=ETL as the Test case filter, obviously replacing “ETL” with the name of your Test Category.

You can also use the Test Category if you choose the MSTest.exe Runner and in this case simply enter the name of the Test Category in the Test category criteria text box on the Criteria tab.

Elasticsearch – Installation

We are using Elasticsearch for our search functionality on the new API framework we are developing to replace our legacy applications and I’ve started reading “ElasticSearch Server” by Rafal Kuc and Marek Rogozinski and published by PACKT.

I’ve played about with Elasticsearch and have installed it locally but only know the basics of posting a document and getting it back. I’ll be going over the contents of the book over the next few weeks so will hopefully get a better understanding of how it all works.

Firstly, though here’s how it is installed…

Please note, I’m using a 64-bit version of Windows 7, and apologies in advance if any of the terminology is not spot on (I’m a database developer after all).

C:\elasticsearch-0.90.3\bin\elasticsearch.bat

  • Next you’ll need to download cURL or some equivalent and you can get this from http://curl.haxx.se/.  This is the command you can use to communicate via the Elasticsearch API which os based on an HTTP protocol and REST.
  • Put the curl.exe somewhere on your file system and add the path to the PATH environment variable.
  • You can then run curl commands from a command prompt. To test this you can run the following and you will get a list of all the curl commands you can run

curl.exe --help

  • You can now send something up to Elasticsearch. A basic example is as follows…

curl.exe -XPUT localhost:9200/test/type/1 -d {"test":"text"}

In this example the -X flag tells curl.exe what HTTP method to use – in this case PUT. The endpoint we are doing the “PUT” to is localhost:9200/test/type/1. This is broken down into the following parts:

  1. localhost – this is the host you are posting to
  2. 9200 – this is the default port
  3. test – this first part is the name of the index
  4. type – this second part is the name of the type
  5. 1 – this final part is the id of the document

The -d flag marks the start of the request document. The document in this example is {“test”:”text”} and this should be in JSON format.
In a Windows command prompt you’ll need to escape the double-quote characters with a leading backslash as shown in the following example.
Elasticsearch - Install - curl

Now if you hit enter you should get an “ok” response from Elasticsearch as shown below.

Elasticsearch - Install - curl response

The response from Elasticsearch also returns the index, type and id of the document and this is unique on the Elasticsearch instance. If you send a completely different JSON document up to Elasticsearch with the same index, type and id it will simply overwrite the old document.

Every time you post up to Elasticsearch the version for the document is incremented. In the example above this is the second time I posted up to localhost:9200/test/type/1 so the _version value is 2.

To confirm our document exists we can now call a GET on the same endpoint as in this example.

Elasticsearch - Install - curl get

Now going back to where we ran elasticsearch.bat… This would have opened a new command prompt that looked something like this.

Elasticsearch - Install - 9200

In this example you can confirm that the port 9200 is being used. You’ll need to look for the [http…] bit. You can also see the name of the instance that is currently being used. This is not configured by default and unless you specify a particular value in the startup files Elasticsearch will use a random name, in this case [Boom Boom].

Finally, as mentioned above you have to escape all the double-quote characters if you’re using a Windows Command Prompt so it would be easier to use a better tool. I’ve been using Cygwin but there are probably others tools that people who know more about API’s could recommend.

Foundations of T-SQL

I’ve start preparing for the 70-461 exam and have just started looking at the training kit guide book. The stuff in this exam is the stuff I do on a daily basis so I should be able to get through it quicker than the stuff for the DBA exam.

I assumed I could just skim through the book and pick out the bits I didn’t know that well. I started at the beginning and was quite surprised that I didn’t remember ever reading the stuff they talk about in chapter 1.

This chapter is titled “Foundations of Querying” and it briefly talks about the the origin and evolution of SQL and the T-SQL dialect.

It all stems from the mathematical principals of set theory and predicate logic.
In set theory a set is collection of objects that are considered as a whole and not as individual objects. A set has no duplicates and the set {a, a, b} is considered to be the same as the set {a, b}. There is also no order to a set and {b, a, c} is considered to be the same as {a, b, c}.

In 1969 Edgar F. Codd came up with the relational model. I’ve obviously heard of the relational model but having never read anything on te theory behind I was under the misconception that the relational bit meant that the tables are related to each other by foreign keys.

However a relation is a mathematical term and has a heading and a body. The heading is a set of attributes, each of a particular type, and the body is a set of tuples.

The standard SQL language (of which T-SQL is a dialect) stems from this relational model and tries to represent a relation with a table, attributes with columns and tuples with rows.
The relational model also has predicate logic as a basis. A predicate is an expression that when applied to an object, gives the result true or false.

The main point this chapter seems to be getting across is that if you inderstand the concepts in the relational model then you should understand how to write better T-SQL with the basic premise being that you perform actions on sets of data and not process data iteratively with things like loops and cursors. The bit about sets not containing duplicates and not being ordered tells us that we should have primary keys on our tables and that the engine is not going to care about the ordering of the data.

If you’ve never heard of the relational model or set theory and you write any SQL it’s worth looking into it so understand how best to write your queries.

The guide book recommends SQL and Relational Theory, Second Edition by C. J. Date (O’Reilly Media, 2011) and I’m going to try and get a copy of this.

70-462 passed! (Just)

My exam was scheduled for the end of August but a last minute change of plans yesterday meant I decided to reschedule it for this morning. I was getting in the high 90s in the scores on the practise tests that came with the guide book so was quite confident. However I only just scraped through with 733 – the pass mark being 700.

I’ve now got the guide book for 70-461 which more of the database development side of things and as I do this on a daily basis I should find this easier than the DBA exam.

Don’t disable your Service Broker queues (if you care about the order in which the queues process your messages)

I’m a SQL developer within a team developing a new framework that will be used to replace our multiple legacy systems.  We ETL certain data from certain legacy entites to our new system and use Service Broker in this process.  Our legacy system sends the Id of the Entity in a Service Broker conversation and then doesn’t worry about what happens after that.  This asynchronous processing is why we chose to use Service Broker.

In our legacy system we run certain processes after an entity is saved on our administration website and the final one of these processes is to send the entity to Service Broker.  Our legacy system processes any related entities in a particular order and these all get sent to Service Broker in that same order.  As the ordering is handled by the legacy system we didn’t implement any priorities on our Service Broker queues – it just receives the messages in the order we sent them with the default priority of 5.

However, yesterday we had an issue where a new user was created on our administration website.  Our users all exist within a container and the permissions for that user are set on the container allowing us to give the same permissions to multiple users by adding them to the same containers.  This new user was part of a new container and in this situation the legacy system processes the new container first and sends it to Service Broker and then processes the user and then sends that.  Service Broker receives the container entity first and we ETL over the required data.  Service Broker then receives the user entity and we ETL that.  Because of the database model in the new system the container must exist before the user can be added to it so we always have to ETL over the entities in this order.

Shortly after the new user was created we received a call saying they are unable to access the new system.  I had a look and for some reason the user hadn’t been ETL’d across.  The new container was there but not the user.  I checked the logs and saw that even though we’d sent the container before the user, Service Broker had received the user message before the container message.

I checked the logs to see if this was happening often and found several occurrences on certain days when the receive order seemed to differ from the send order.  The times these happened all seemed to occur roughly when we had a live release.

Before each go live we disable our Service Broker queues to temporarily stop the ETL process.  While the queues are disabled the messages queue up on the transmission queue (sys.transmission_queue) and then when we enable the queues they are sent from the transmission queue to the Service Broker.  However, the transmission queue does not seem to care about the order in which it received the messages so it sends them up to the Service Broker queues in a random order.  This is how we ended up processing the user entity before the container entity.

On MSDN the documentation states that “Service Broker guarantees that a program receives each message in a conversation exactly once in the order in which the message was sent, not the order in which the message entered the queue“.  (See full article here…http://msdn.microsoft.com/en-us/library/ms166049(v=sql.105).aspx).  This statement only seems to hold true if the queues are enabled.

I’ve have since seen on the MSDN site that they say “..it is rarely necessary to stop a queue once the queue is made available” so I don’t think our stopping of the queues is recommended.  (See full article here… http://msdn.microsoft.com/en-us/library/ms166046(v=sql.105).aspx).

We have a stored procedure associated with our target queue and this is the thing that runs the RECEIVE statement. In future we are just going to disable the activation on the queue so that the messages are sent to Service Broker but not received until after our go live is complete.  This will ensure the messages are picked up in the same order they are sent.

I’ve written a bit of SQL to demonstrate the ordering with the queues enabled and disabled.

1) Create a new database and set up the service broker queues.  I’ve created two priorities, 3 and 7 so I can demo how Service Broker uses these.

USE master
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'ServiceBrokerTesting')
BEGIN
 DROP DATABASE ServiceBrokerTesting;
END;
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'ServiceBrokerTesting')
BEGIN
 CREATE DATABASE ServiceBrokerTesting;
 ALTER DATABASE ServiceBrokerTesting SET ENABLE_BROKER;
 ALTER DATABASE ServiceBrokerTesting SET TRUSTWORTHY ON;
 ALTER DATABASE ServiceBrokerTesting SET HONOR_BROKER_PRIORITY ON;
END;
GO
USE ServiceBrokerTesting
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '12345ABC$'
-- Create the service broker objects
IF NOT EXISTS (SELECT * FROM sys.service_message_types WHERE name = 'TestType')
BEGIN
 CREATE MESSAGE TYPE TestType VALIDATION = NONE;
END;
IF NOT EXISTS (SELECT * FROM sys.service_contracts WHERE name = 'TestContract')
BEGIN
 CREATE CONTRACT TestContract (TestType SENT BY ANY);
END;
IF NOT EXISTS (SELECT * FROM sys.service_queues WHERE name = 'TestInitQueue')
BEGIN
 CREATE QUEUE TestInitQueue WITH STATUS=ON, RETENTION=OFF;
END;
IF NOT EXISTS (SELECT * FROM sys.service_queues WHERE name = 'TestTargetQueue')
BEGIN
 CREATE QUEUE TestTargetQueue WITH STATUS=ON, RETENTION=OFF;
END;
IF NOT EXISTS (SELECT * FROM sys.services WHERE name = 'TestLowInit')
BEGIN
 CREATE SERVICE TestLowInit
 ON QUEUE dbo.TestInitQueue (TestContract);
END;
IF NOT EXISTS (SELECT * FROM sys.services WHERE name = 'TestLowTarget')
BEGIN
 CREATE SERVICE TestLowTarget
 ON QUEUE dbo.TestTargetQueue (TestContract);
END;
IF NOT EXISTS (SELECT * FROM sys.services WHERE name = 'TestHighInit')
BEGIN
 CREATE SERVICE TestHighInit
 ON QUEUE dbo.TestInitQueue (TestContract);
END;
IF NOT EXISTS (SELECT * FROM sys.services WHERE name = 'TestHighTarget')
BEGIN
 CREATE SERVICE TestHighTarget
 ON QUEUE dbo.TestTargetQueue (TestContract);
END;
IF NOT EXISTS (SELECT * FROM sys.conversation_priorities WHERE name = 'TestLowPriority')
BEGIN
 CREATE BROKER PRIORITY TestLowPriority
 FOR CONVERSATION
 SET (CONTRACT_NAME = TestContract,
 LOCAL_SERVICE_NAME = TestLowTarget,
 REMOTE_SERVICE_NAME = N'TestLowInit',
 PRIORITY_LEVEL = 3);
END;
IF NOT EXISTS (SELECT * FROM sys.conversation_priorities WHERE name = 'TestHighPriority')
BEGIN
 CREATE BROKER PRIORITY TestHighPriority
 FOR CONVERSATION
 SET (CONTRACT_NAME = TestContract,
 LOCAL_SERVICE_NAME = TestHighTarget,
 REMOTE_SERVICE_NAME = N'TestHighInit',
 PRIORITY_LEVEL = 7);
END;

2) Now we’re going to send 50 messages.  The messages will contain an integer 1 to 50 and we’ll send them in order.  All messages will sent using priority 3 except for every 10th one which will use priority 7.

-- Create conversations
-- Use message priority 3 except for every 10th one use priority 7
DECLARE @dialog_handle UNIQUEIDENTIFIER ;
DECLARE @Counter INT = 1;
WHILE @Counter <= 50
BEGIN
IF @Counter % 10 = 0
 BEGIN
BEGIN DIALOG CONVERSATION @dialog_handle
 FROM SERVICE [TestHighInit]
 TO SERVICE 'TestHighTarget'
 ON CONTRACT [TestContract];
SEND ON CONVERSATION @dialog_handle
 MESSAGE TYPE [TestType] (@Counter);
END
ELSE
 BEGIN
BEGIN DIALOG CONVERSATION @dialog_handle
 FROM SERVICE [TestLowInit]
 TO SERVICE 'TestLowTarget'
 ON CONTRACT [TestContract];
SEND ON CONVERSATION @dialog_handle
 MESSAGE TYPE [TestType] (@Counter);
END;
END CONVERSATION @dialog_handle WITH CLEANUP;
SET @Counter += 1;

END;

3) Now create a table to write the messages as we receive them.

CREATE TABLE dbo.Receiver
(
 Id INT IDENTITY(1,1) NOT NULL
 ,priority INT NOT NULL
 ,queuing_order INT NOT NULL
 ,service_name VARCHAR(20) NOT NULL
 ,message INT NOT NULL
);

4) Run the RECEIVE statement to read off the messages.

DECLARE @priority INT
DECLARE @queuing_order INT
DECLARE @service_name VARCHAR(20)
DECLARE @message INT
WHILE (1=1)
BEGIN
WAITFOR (
 RECEIVE TOP (1)
 @priority = priority,
 @queuing_order = queuing_order,
 @service_name = service_name,
 @message = CAST(message_body AS INT)
 FROM TestTargetQueue 
 ), TIMEOUT 6000;
IF @@ROWCOUNT = 0
 BEGIN
 BREAK;
 END;
INSERT INTO dbo.Receiver (priority, queuing_order, service_name, message)
 VALUES (@priority, @queuing_order, @service_name, @message);
END;

5) Check the contents of the table to prove that the messages were received firstly in priority order and then in the order they were sent.  In this case messages were received in the order 10, 20, 30, 40, 50, 1, 2, 3, etc, as the messages that were a multiple of 10 were sent with a higher priority.

SELECT *
FROM dbo.Receiver;

6) Truncate the table, disable the queues and resend the messages.

TRUNCATE TABLE dbo.Receiver
GO
-- Disable the queues
IF EXISTS (SELECT * FROM sys.service_queues WHERE name = 'TestInitQueue')
BEGIN
 ALTER QUEUE TestInitQueue WITH STATUS=OFF, RETENTION=OFF;
END;
IF EXISTS (SELECT * FROM sys.service_queues WHERE name = 'TestTargetQueue')
BEGIN
 ALTER QUEUE TestTargetQueue WITH STATUS=OFF, RETENTION=OFF;
END;
-- Resend the messages but this time don't close the conversations
DECLARE @dialog_handle UNIQUEIDENTIFIER ;
DECLARE @Counter INT = 1;
WHILE @Counter <= 50
BEGIN
IF @Counter % 10 = 0
 BEGIN
BEGIN DIALOG CONVERSATION @dialog_handle
 FROM SERVICE [TestHighInit]
 TO SERVICE 'TestHighTarget'
 ON CONTRACT [TestContract];
SEND ON CONVERSATION @dialog_handle
 MESSAGE TYPE [TestType] (@Counter);
END
 ELSE
 BEGIN
BEGIN DIALOG CONVERSATION @dialog_handle
 FROM SERVICE [TestLowInit]
 TO SERVICE 'TestLowTarget'
 ON CONTRACT [TestContract];
SEND ON CONVERSATION @dialog_handle
 MESSAGE TYPE [TestType] (@Counter);
END;
SET @Counter += 1;

END;

7) As you can see the messages are all just sitting in sys.transmission_queue.

SELECT *
FROM sys.transmission_queue;

8) Enable the queues.

IF EXISTS (SELECT * FROM sys.service_queues WHERE name = 'TestInitQueue')
BEGIN
 ALTER QUEUE TestInitQueue WITH STATUS=ON, RETENTION=OFF;
END;
IF EXISTS (SELECT * FROM sys.service_queues WHERE name = 'TestTargetQueue')
BEGIN
 ALTER QUEUE TestTargetQueue WITH STATUS=ON, RETENTION=OFF;
END;
GO

9) Run the RECEIVE statement again and log to the table.

DECLARE @priority INT
DECLARE @queuing_order INT
DECLARE @service_name VARCHAR(20)
DECLARE @message INT
WHILE (1=1)
BEGIN

 WAITFOR (
 RECEIVE TOP (1)
 @priority = priority,
 @queuing_order = queuing_order,
 @service_name = service_name,
 @message = CAST(message_body AS INT)
 FROM TestTargetQueue 
 ), TIMEOUT 6000;

 IF @@ROWCOUNT = 0
 BEGIN
 BREAK;
 END;
INSERT INTO dbo.Receiver (priority, queuing_order, service_name, message)
 VALUES (@priority, @queuing_order, @service_name, @message);
END;

10) Check the contents of the table.  As you can see Service Broker has honoured the priority but the messages within each priority are not ordered correctly.

SELECT *
FROM dbo.Receiver;

In summary if you need to ensure that the messages are received in the order they were sent then you should not disable your Service Broker queues.  Either disable the activation on the target queue or disable the thing that does the initial send.