Using PowerShell to drive ETL

You may be aware of T-SQL Tuesday; it's a concept conceived by Adam Machanic ( b | t ) some years ago, to encourage a number of posts on a different specific topic on a monthly basis. This month's topic, as hosted by Rob Sewell ( b | t ), is Let's All Get POSH.

For some time I've used SSIS to move data from one place to another, as part of a wider ETL strategy. Recently however, I've faced some challenges to this model, and have been using PowerShell as an alternative.

So firstly, here's some of the challenges I've faced:

Support for the multiple data formats I need to connect to

SSIS is a Microsoft product, and therefore focused on manipulating data within the Microsoft environment. It does attempt to interact with other systems, for example by use of ODBC to allow connection to read data,  but this is not always as successful as might be hoped.

Flat files, and Excel files etc. are great, as long as the file format remains the same. Increasingly however, the data files we are loading are changing in format, and incorporate factors such as multiple line fields which SSIS requires a lot of work to utilise properly.

Programmability and support

SSIS uses VB.Net or C# programming to provide its interactivity,  and this does provide a level of flexibility.  That said,  it can be tricky to get the right level of function whilst not making the solution so fragile that it is an active barrier to change and presents a problem for support teams.

Testing

I like to know that what I'm deploying works as I expect it to. When making changes, I'd like to know I've not created any problems whilst I've been doing it. This means testing, and ideally in an automated way, so it's repeatable whenever a change to the system is required. However, I've not found a good way to unit test within SSIS, and this means that there is a testing overhead, or a larger risk to accept whenever there is a change to deploy.

A possible replacement

Well, the modular approach of PowerShell can give an ability to reuse smaller aspects of the system to allow you to create re-usable building blocks, and assemble them in any order, in a similar way to the tools you might have used in SSIS to interact with different DBMS systems.

PowerShell also has a unit testing framework (Pester) built in, which allows us to check each building block works as expected. 

PowerShell modules are developed by the community and benefit from the collective experiences of all those contributors. This also means that we have the things that those contributors wanted - and for ETL, that often means an element of automatic discovery of the structure of data sources, whilst allowing a manual specification where that is helpful.

PowerShell is increasingly used in the Windows arena, lending to increased knowledge of the language for support purposes.

A flexible way forwards

So - how can I use this to get a solution to tackle the above limitations, and generate a flexible solution?

Well, we have built a number of functions to read from, and write to, the specific DBMSs that we interact with most - Microsoft SQL Server, MariaDB, Excel and flat files.  These allow a simple read from, and write to, of each of the DBMSs. To introduce flexibility, we pull in the query to run as a parameter to each function.

These functions can then be called, in whatever order is required, to read a query from a configuration file, run it against a given DBMS to read data, and store it in a PowerShell object. Any necessary manipulation can be done at this point, before the file is then written into the target DBMS. The process can then read a second query from a configuration file and use it to perform any required transformation - for example to write into a destination file. This allows us to take either an ETL or ELT approach (transforming either after extract, or after load.

This use of functions have allowed us to test each one works in isolation, and now we have a set of proven functions, we can then re-use these building blocks, to repeatedly move data, whether the data is in the same format, or needs manipulation between the systems.

As an unintended benefit, the modular approach allowed a distribution of the development work among colleagues, to a defined way of working, providing a useful focus to colleagues learning to use PowerShell.

From a support point of view, the use of a standardised language understood by other teams such as DevOps, as well as operation support teams, means that we have more confidence that issues can be resolved promptly should any issues occur.

Keeping track of databases

In most production settings, you end up with a number of databases. Some conform to a naming scheme - perhaps a name of a well used application, perhaps according to customer, perhaps according to some internal identifier. However, in most cases, the current scheme has not always been in place, and there's a collection of unknown databases (or ones that simply don't fit the pattern), which don't cause much trouble, but simply sit there, waiting to trip up the person who has rarely if ever interacted with that database, but is charged with supporting it.

As regulations such as the EU's GDPR come into focus, we are increasingly called to account for all the data we hold, particularly if it is personally identifiable. What this means, is we need to be able to identify what's in these 'miscellaneous' databases, and to what they relate. Frankly, it's a good practice anyway, so you can better manage the data that you hold. It's also helpful for maintenance work to understand the effect if you take a database instance offline; even if your organisation is a 24x7 operation, you will probably have databases that don't need to be online all the time - for example the payroll database (as the team who look at this only work in the day). You may have varying definitions of 'day', particularly if you cross timezones.

The question then is, how to identify what these systems relate to. You may have a documentation source for each database (I often find ours to be regrettably out of date), but even if this is in date, it is designed to be looked at for a specific system, so this still means trawling lots of documentation to find out what we want to know in this case - and the documentation doesn't automatically update as databases move.

The solution I've started to use is extended properties on the database. These are a place to put an sql variant value (which can hold text or numbers for example) properties of an entity, and which forms part of that object and can be scripted out with it - so they can be source controlled. They move with the database, so as it is backed up / moved, the properties stay with the object to which they relate.

So why doesn't everyone do this? Well, firstly, it's a schema change. This is a blocker in some organisations, or for some supplier-supported systems. I've found that asking, and explaining why, goes a long way here. Secondly, there's the question of how to use them.

Extended properties can be viewed in the SQL Server Management Studio GUI, however this is clunky at best, and I tend to use scripts to manipulate them, to ensure consistency.

Extended properties can be added, edited, or deleted using system stored procedures. To read them, a function (fn_listextendedproperty) is called, or a system view is available. The key problem here is how to read from all the databases you have in an estate, both within an instance and across multiple instances.
I use policy based management, with a check that looks for the specific extended property I use, to gather this information, but you could equally well query this from Powershell, as a property on the database object as outlined by Buck Woody.

Using properties like this mean you can also tie to other data you might have - like who is responsible for which systems in the wider business - to give a list of people to notify about potential issues to database instances, which can be updated without updating lots of documents; this means it's more likely to be up to date, when you need it most.

This list of databases, purposes and potentially business owners may also be helpful to your information assurance & governance teams, as they look to ensure they have a good understanding of what databases you hold, and why.

Always check the basics - backups

Are your databases being backed up? Can you restore from them? Are you sure?

SQL Server has a built in way to check when the database was last backed up. You can access this data in a couple of ways:

Via T-SQL  - query the sys.databases table. You can identify the last time the database had a full, differential or transaction log backup

You can also use a Policy Based Management policy to do this - I use this to at least ensure that each database has a full or differential backup from within the last day, and as a prompt to act if it doesn't.

You can of course use a number of means to back up, but the only way of testing the backup properly is to try restoring it. By running a restore you check the backup, and of course the backup chain in the case of transaction log backups.

It's only by testing the restore that you can be sure that the backup is any use - and that your data is safe. So the follow on process is to ensure that you run consistency checks (DBCC CHECKDB in SQL Server) on the restored version.

Now we know:

• The database was backed up
• It can be restored to when the backup was taken
• The restored database is not corrupt
• By implication, the original database wasn't corrupt when the backup was taken

This means it is a backup that you, and your business can rely on.

The next step is to work with your backup software to automate this process, so you can be sure that the backup meets this criteria each time it runs.

Strategies for managing an enterprise - Policy Based Management (T-SQL Tuesday)

You may be aware of T-SQL Tuesday; it's a concept conceived by Adam Machanic ( b | t ) some years ago, to encourage a number of posts on a different specific topic on a monthly basis. This month's topic, as hosted by Jen,McCown, is Strategies for managing an enterprise.

A Strategy is a plan of action designed to achieve a long term goal or aim, so I thought I'd write about how I'd planned and achieved a solution to the problem of assuring myself and the business that our SQL servers met the policies and best practices we aspired to achieve.

I had recently been faced with the need to gain confidence in the state of a set of SQL Servers whose management hitherto has at best been a part time activity, and not owned by any one person or group. This had meant that, understandably, consistency and best practice have suffered, to the extent that a base level of audit and assurance is required. There's a few ways to achieve this, and I needed to come up with a strategy that would hit several goals.

Easy to implement

Technically advanced technologies are great, but sometimes you just need basic checks and the ability to build on the basis they provide. In our case, we needed to use the existing skills base to achieve our goal.

Automated

I didn't have the luxury of assuming there will be a full time DBA in the organisation in the future, and in any case we are always pushed to do more with less resource, so it is likely that any solution that involves manual steps will not be carried out regularly. We therefore needed an automated solution.

Visible

For any audit tool to be effective, particularly a tactical one, we needed to be able to provide a level of reporting, to allow the business to perceive the health of the assets, and have confidence that issues are addressed.

Comprehensive

There's no point in having a solution that doesn't cover all the bases. We needed one that will allow us to expand on the 'out of the box' functions to do custom metrics.

Cost effective

There are a lot of valuable solutions out there that really do provide great monitoring for large server estates, allowing real-time automated resolution of issues, and confirmation of policy adherence. Our needs were more modest however, and it should be noted that the goal was to achieve the monitoring without spending out on licences; this is something we can aspire to once the base level of audit is in place.

So once these goals are defined, I looked at some options. Third party solutions like Nagios, which is a great monitoring tool I've used in the past, were not an option due to existing skills bases. I needed to use something that would use existing developer or administrative skillsets. I explored Policy Based Management (PBM) in SQL server, and decided this was a good avenue because it allowed simple best practices to be easily monitored, in some cases bad practices prevented, and could be easily rolled out to the enterprise via a central management server. 

Using PBM, you can write policies that prevent some behaviours, but due partly to existing codebases, and in some cases third party products that we can't modify, as well as a relatively relaxed regulatory framework, I've chosen to alert rather than prevent. I can write conditions that only target certain server classes, and have taken to using extended properties on databases to denote whether certain conditions apply (and on the master database to denote server environment characteristics, such as Production/UAT/Dev). This gives the flexibility to write one policy for a condition and have it apply wherever an applicable system is hosted in the enterprise.

This setup was still missing one part however - the visible bit. By default, when you have a non-compliant server, all you get is an icon in SQL Server Management Studio, and an error code fired which means you can alert via an event. This isn't even very visible if you know what you're looking for - and doesn't tell you what's wrong.

The solution was to use the Enterprise Policy Management Framework, a combination of Powershell and SSRS to query an estate of servers and present the results in a report, which could be displayed centrally (think intranet, or screen in the management office), or scheduled and circulated like other SSRS reports, and is simple to visually check on a regular basis - a screen full of red is bad!

Did it work? Certainly, and it allowed identification of issues such as backups being missed off the schedules, security permissions which were against policy, orphaned users and the like. This allowed the strategy to achieve the aim of giving confidence in the level of servers across the enterprise.

There are some areas for further thought, not least in terms of the security permissions required to run reports, which are necessarily high - however any system which monitors areas such as sensitive configuration will require a comparatively high level of access. I'm also looking at modifying some of the reports to better fit our needs, but this is a minor change given the data is now in place.

I found that adopting a strategy of implementing a monitoring solution to check the basics are in place has given me the confidence that we have a firmer base to build on. The strategies and goals they aim to achieve will evolve, as they all should, as time is freed up and the needs of the business dictate.  I've not given up on implementing a more integrated monitoring solution in the longer term, but this is a great way of assuring the business that the service we provide is fit for purpose, and can be relied upon.

Solving data problems cost effectively

I believe that most bad data can be categorised as due to one of:

• Human data entry issues
• Bad processing of data we hold
• Corruption of data in storage

The last of these is a longer topic, and whilst there are mitigation strategies they are mostly a cost / spending exercise to achieve the required level of risk reduction. Entry issues have a few mitigation strategies - double entry can be used (principally used historically in finance audit systems) to prevent typos, but in an increasingly real-time data collection this is less practical. However what can be used are methods such as field validation rules, and repeating back to the user related data, to check the details. An example might be to look up the postcode a user entered and present a selection of valid addresses (assuming you have a source of these) for that post code. Thus, if an incorrect postcode has been entered the mistake should be obvious immediately, allowing it to be checked. Another method would be to indicate the location on a map. The method of checking will depend on the way that we have of communicating with the person from whom the data is collected. Part of the goal here is not only to ensure that our data is not erroneous, but that we do actually have the correct data. If you think of a scenario where we are collecting data from people in Plymouth, who will all have a post code starting PL... then if a users enters a post code starting PO.. (Portsmouth) then this is clearly incorrect - however it doesn't tell us what the correct post code actually is. If looking at questions of a more view based type, it may be possible to ask a similar question in two ways and cross check the answers.

Once we have the correct data then we need to ensure the correct processing of it, and it is here that the issues can get costly. The cost of getting data is small compared to the cost of getting it *again* if that data is lost - not only is there a monetary cost but also a cost to reputation. So this implies that we need to ensure our processing of data is valid. The best defence we have against the human quality of fallibility is testing, and more generally quality control. I would suggest that any software (most data processing is now done in software, but the principles can still be applied to manual processes) that is not meeting a basic quality standard should not be judged fit for use. Such standards, whilst defined to meet your project / organisational needs, should consider:

• Have we identified the requirement this fulfils?
• Does the documentation explain how we fulfil it?
• Does the testing check all aspects of the requirement?
• Does the testing check for entry of unusual data?
• Does the unit of code work well on it's own?
• How about if we put it within the overall system?
• Is it maintainable (i.e. according to policies, design standards, etc.)?
• Has someone else code-reviewed it, and the documentation and test, to check for errors?

The last point is important because a test failure would simply imply that the results didn't match what the test expected - it is up to the requirements documentation (or business representative) to arbitrate as to which is incorrect, the test or the code. I believe that all of these are valid and should form your definition of when code is "done" however you develop your solution - a traditional waterfall approach, an Agile methodology, or a more ad-hoc process. By catching these issues before a product is released, and actively processing real data, we remove the costs associated with loss of data, or correcting for accuracy problems which result from our processing.

Whilst we have above covered some basics this is clearly a much broader topic; it amazes me that I still see organisations who do not have robust processes to ensure their data processing does not only meet the legal obligations such as the Data Protection Act (such as Principal 4, accuracy), but also avoid compiling information inaccurately and therefore leave themselves open to making decisions on a flawed set of data, potentially invalidating the decision, and risking significant financial and reputation damage.

Information Vs Data Vs Security

A subject that has been close to my mind recently, is as to when data becomes information, and how the security of that asset is managed - and by whom.

We have probably all seen the triangle:

By Longlivetheux (Own work) [CC BY-SA 4.0 (http://creativecommons.org/licenses/by-sa/4.0)], via Wikimedia Commons

Which as you will gather, denotes that Information is comprised of (useful sets of) Data. There's also the concept, at least in UK law, of data, which comprises of Information:

Data means information which –(a) is being processed by means of equipment operating automatically in response to instructions given for that purpose,
(b) is recorded with the intention that it should be processed by means of such equipment,
(c) is recorded as part of a relevant filing system or with the intention that it should form part of a relevant filing system,
(d) does not fall within paragraph (a), (b) or (c) but forms part of an accessible record as defined by section 68, or
(e) is recorded information held by a public authority and does not fall within any of paragraphs (a) to (d).

https://ico.org.uk/for-organisations/guide-to-data-protection/key-definitions/

So it is clear that these terms are not defined exclusively, and that the terminology alone may present a problem.

The business often has a nominated Information Asset Manager, who looks after the types of information an organisation processes, and is responsible (perhaps to a Senior Information Risk Officer, or SIRO). The organisational policy may well give this person (delegated from the SIRO) the last word on policy - however this doesn't mean this person has the technical background to ensure that policy is implemented - or even practical.

There may also be someone in the IT infrastructure whose remit is security - either a dedicated IT Security Officer, or perhaps someone who has this responsibility as part of their work. Again, this is quite a wide ranging remit, and this person may feel it is better left to subject matter experts in specific systems, and lay guidance as to the principles to be observed.

This leaves the DBA with two potential places to go for security instructions, yet neither may understand the detailed technical processes necessary to actually undertake the remit, as their remit is either business focused or too wide for the focus on the specific technology of databases. That's where the DBA brings his or her expertise to bear, and as such brings value to the business.

Often the DBA role is also to mediate where the tension exists between data security and information use. This is because they are the guardian of the security on the data - and so the first place that gets approached when technical permission is denied. This brings to light the difference between a technical prevention measure, and a business policy allowing, forbidding, or laying restrictions on a course of action.

Whilst clearly data security will almost always win the balance between security and flexibility, the role of the data professional is increasingly to suggest a way to merge the data once permission is secured and in accordance with best practice. This is a difficult balancing act, which may not be fully understood by information users.

So how can we alleviate the issues this generates? Firstly - publicity. Explain what is necessary, and have a ready to roll out example of why you wouldn't like your data to be misused. Perhaps an example might be of sensitive personal data being unexpectedly available to the world for misuse, or the potential for processing of data in a way it wasn't gathered for - both breaches of the DPA, but still things that get requested disturbingly often.

A second avenue is to engage with the other data professionals in a proactive way, and ensure that clear paths are laid down to get issues circulated for discussion - and engage with the business to resolve them. Generally, there's a good way to do things, and a bad way - for example, would anonymised data do for a task? Could the risk be mitigated somehow? Perhaps we can't do something with the data we have, but changing how it is captured to ensure the correct permissions are granted gives us the ability to do this in the future. If the policies don't exist (or haven't been reviewed recently) then these can be bolstered to ensure they are fit for purpose - and in line with recent changes to regulation.

With these approaches, we lay down a safe way of working, that also keeps us within legal boundaries and hence providing value to the business with both of these points.

Justifying Unit Tests for Databases

It’s a shame that unit testing of databases is, relatively speaking, the poor cousin of application unit tests. After all, the same benefits of surety of code and ease of refactoring can be achieved by this simple means, and ultimately costs saved by ensuring that the (inevitable) bugs in an application and database are caught earlier in the product lifecycle.

Whilst Unit testing is a key point in Agile software development, there are too many projects out there where the existing codebase is perceived to be too large to allow unit tests to be built up for a product. This isn’t necessarily the only way to tackle an existing codebase- I’d recommend starting with bug fixes, and building up from there.

I can foresee that an idea that is common in microservice provision, to allow a development team to “adopt” or retain responsibility for a software product for the lifecycle of that product – from initial concept to final retirement, with all the rollout, upgrade and support in the middle – will be what finally convinces database developers that it’s not worth risking buggy code – or the chance of breaking something else when the inevitable support call does come. This merging of the traditional development and operations functions into one service-oriented team makes it in ones’ own interest to minimise total effort on issues – and a few other best practices like good documentation and minimising “just in case” code along the way.

As a strong proponent of data integrity, I see unit testing as part of the armoury of tools necessary to develop and maintain a system to capture information in a robust format, and ensure that the data retains it’s integrity through its’ lifetime.

If you want to find out more about how to justify unit testing for databases, I’ve recently published a course on this very subject through Pluralsight. Whilst Pluralsight isn't free to access, there is a free trial available, so set some time aside and watch it to help you build a business case for adopting this best practice.

What's it like to attend a community event like SQL Relay for the first time?

Following the recent SQL Relay event in Bristol, I had the opportunity to talk to a first time attendee, Claire, who has recently found herself in charge of the SQL Servers in her workplace. I asked her if she would mind sharing her thoughts on the event, so that others can get an insight into what it's like to attend for the first time.

What in the day did you most enjoy, and why?

Quite glad I got to see Alex Whittles' session [Using PowerPivot and DAX to Predict and Win Fantasy F1] because it was interesting to see how the power of Excel can be used, and seeing it applied in the way he did made it easier to understand.

So the real world example helped?

Yes, and quite a fun example too, and I do think it's something we could use as well.

Anything that didn't work in the event?
I don't know if it's because I arrived late to the start of the session [Claire had been speaking to a sponsor at the time, and had run into the start of the following session], but I found the BIML session quite hard to understand, as I felt I'd missed the introduction and found it quite hard to follow where he was going, but I can see it would be useful if you're trying to create multiple packages and don't want to run through and do the same thing again.

Had you come across BIML before?
No, although I had found something similar before, so I could recognise the concept, and it was nice to see you can build things in that way rather than just amending them.

Did you realise that was an intermediate session?
No, no I hadn't picked that up! I felt it was good, but I did feel a little behind.

There's often one session like that - particularly if it's not something you can use at work, and get familiar with, but it's good to introduce yourself to new concepts.

The NoSQL Session, Richard Munn's session - I was a bit confused by it I suppose,  because everything we do is entrenched in SQL, seeing outside that was hard; and then seeing the three different pieces of software he was demonstrating, I was trying to see how that could be of relevance to what we do, and if it would be better, but I found I couldn't clearly see.

So it's something you need a longer session to appreciate?
Yes, I think so, but it's great to see how the concepts work, and  it's good to see what else is out there.

Yes, it's just a taster, isn't it..

Yes. And his presentation style was funny, which helped - he did make me laugh!

And the last session, the "fun" one [Dave Morrison's SQL Fun Time Hour], I wasn't really sure what to expect, because it didn't really describe what it was about before I went, but it was really interesting, really good, and I was hanging on his every word. There was so much in there that was really useful.

So that's the one you'd like to see more of?

Yes, it was all real life, little bits of every day stuff you do all day and don't necessarily appreciate what issues it throws up in the background. I did really appreciate that one.

So would you go to more community events in the future?

Yes, oh yes - well, if I'm allowed to!

Some of the events are at weekends, or evenings - would you make it in your own time?

That's a different type of "Allowed To"!

It sounds like a positive experience then?

Yeah, oh yeah, I'd definitely try to go again.

It was just like a series of lectures, wasn't it, you just choose what you think might be most applicable and choose, and I like that, and it was friendly as well! I had thought it might be a bit more unfriendly, but it was fine.

Did that worry you - that it might be aimed at people who had been doing the job longer?

Yes, I know that some of the people I spoke to, the moment they spoke back to me I could only really smile as I didn't understand what they were saying, but I did also have conversations with people who I felt were in a similar position to me, which is good to know! And I was already expecting there would be less females there, so that wasn't a surprise.

I don't know if I feel strongly about gender mix, but didn't wan't to feel people were talking over my head just because I was female.

Did you find that?

No, I didn't get that at all.

I'm glad to hear that.

Yes, a good day, but now I just feel I could sleep for a year - It's a lot to take in, and reflect upon. I just hope I've got enough notes to convince my employer to send me to another one!

Thanks for your time, Claire, and I hope to see you at future events.

Check constraints not what you thought they were?

I was recently adding a check constraint to a table in SQL Server, and noted some behaviour which surprised me, so I thought I'd share it with you. I've tried this on SQL Server 2012 and 2008 R2.

I added two fields, which either both needed to be null or both filled in. (Now, it is possible this requirement points to a normalisation requirement - but that's not the point at hand). So the check constraint is simply to ensure that either:

• Field A is null AND Field B is null

OR

• Field A is not null AND Field B is not null 

So I used the following check constraint:


USE tempdb

CREATE TABLE AllOrNothing
    (
      id INT IDENTITY(1, 1) ,
      FieldA VARCHAR(100) ,
      FieldB VARCHAR(100)
    )

ALTER TABLE AllOrNothing ADD CONSTRAINT UK_AllOrNothing 
    CHECK (
             (FieldA IS NULL AND FieldB IS NULL) 
           OR 
             (FieldA IS NOT NULL AND FieldB IS NOT NULL)
           )


Now, what surprised me was when I queried sys.constraints on this:

and again if I modify the constraint in SQL Server Management Studio:

The brackets have gone! Now, this isn't going to affect the functionality due to the precedence order of the logical operators, but will still have a SQL Developer looking at it and wondering if the intended functionality is what's been put in place. (Of course, the best way to be sure is to unit test your code).

I thought I'd blog about this, as whilst it doesn't change the function of what's been coded, it does change the form of it, which is unusual and was to me unexpected.

What do you use to version control your databases?

Many developers version control (AKA source control) the databases upon which they work, to give them the same benefits as we receive when using source control for applications, as well as the benefit that we have the two systems version controlled together. But a recent tweet from Red Gate got me thinking about those that don't use source control for databases.

24% of developers don't use database source control - make sure you aren't one of them. See the crew at #techdaysNL to learn more
— Red Gate (@redgate) April 17, 2014

I asked Red Gate about the source of their figures, and they helpfully supplied the results of the polls on SQLServerCentral.com upon which they had based this. The figures make interesting reading, and I wanted to draw out a couple of trends which I found interesting. Here's a plot of the results

"Which Version Control System do you currently use to store your database scripts?"
Results from SQL Server Central.com

24% of developers not using database source control actually seemed a little low to me - my experience is that a great number of companies don't use source control for databases, but this may be due to a number of things, not least that as a consultant I tend to visit companies that need assistance! Looking at the figures however, I can see that it has varied between 31% and 21% of people don't use source control in each poll, and doesn't seem to be dropping dramatically. Now, this may be a different set of people each time, or it may be due to the self-selection nature of such polls, but it is clearly a not-inconsiderable amount of people.

I've also noted with relief that the number of users of VSS has dropped dramatically since support for it was discontinued - if you still use VSS please consider migrating to a supported system. TFS is growing in use, probably due to the inclusion of licenses with MSDN subscriptions.

The use of Git and Mercurial is also surprisingly low - perhaps the distributed nature of these systems do not lend themselves as naturally to database source control, but I have used Git with database scripts with no real issues in the past.

I found these results interesting, and if you have not yet adopted source control for your databases, you may be interested to read my series of articles entitled Stairway to Database Source Control, which are currently being published on SQL Server Central.com.

Why learn from Pluralsight?

A little while ago, I recorded my first course with Pluralsight, an online training provider.

When discussing this with others, some have expressed reticence to try it, as access to the courses is sold on a subscription basis. In this post I want to tell you why I author courses and blog posts for Pluralsight, and share with you some ways that you can experience this training before paying, so you can ensure you like this way of learning before committing.

I came across Pluralsight about a year ago, as a library of developer focussed learning. Since then, they've expanded and acquired other course libraries, covering the whole spectrum of IT learning, including Open Source and Cloud Computing, as well as commercial software. I still find them my go-to provider for the training I need (you should regard continuing learning in your field as a best practice). The on-line, modular model allows me to learn in the time I have - most courses have modules in 20-40 minute lengths, so they can be watched in a lunch break - and over several days you can cover a whole course. I find their approach to training to be conducive to learning, and the subscription model encourages you to learn and improve as much as you can, rather than each subject or area costing more money.

So how can you try Pluralsight training, and see my course as well as the other great courses in the library, if you can't get budget approval for a subscription immediately?

Well, firstly, you could sign up to a 10 day (200 minute) trial. This is free of charge, giving you time to cancel if you don't like it before it converts to a monthly subscription. But there are other ways to get this sort of training for free that you may want to explore.

Some courses are not charged for, and can therefore be accessed whether you have a subscription or not. For details of which, check these blog posts. There are some free courses for children. But you may find that you already have a subscription which can help you get access to the full Pluralsight training library - such as Microsoft's BizSpark, Dreamspark+ and WebsiteSpark, which each give you 90 days' access.

Of course Pluralsight courses are not the only method of continuing your learning, and I recommend that you attend community events such as SQL Saturdays for in person learning, as well as your local SQL Server User Group.

UPDATE: If you are a member of any of the following industry programmes, you can get a years' free Pluralsight training by filling in this form : Microsoft MVP, EMC Elect, Google Developers Expert, VMware vExpert, Citrix CTP, Salesforce MVP, ASPInsiders, Friends of Red Gate, JavaOne Rock Star, Cisco Champions.

Keeping tSQLt tests separate in SSDT

Users of SQL Test and the tSQLt framework upon which it is based have noted in the past that because test objects are stored in the database, they can be difficult to differentiate from the objects under test.  This has been a barrier to some users adopting tSQLt for unit testing, as it prevented separate management of tests and raises the potential danger of tests being accidentally deployed within a production environment.

Recently I have been using tSQLt within SSDT (following this method by Ken Ross), and it occurs to me that this method of controlling unit tests allows us to keep the code under test in a different project to our tests, and therefore overcome the difficulty of keeping our tests as database objects. When we have our tests in a separate project within the same solution, we can choose to deploy either the tests with our code under test or simply the code itself without the tests.  By configuring the test project to ensure that it requires the code under test to also be deployed at the same time and into the same database, we can set up publish definitions for the tests to our development machine and also a publish definition for just the code under test. It's the latter which we would use to deploy outside of our development workstation, for example to UAT. It also means that if you deploy by DACPAC the tests have never been in that package, so it is ready to deploy to each environment without your needing to take any additional steps.

Of course, a Continuous Integration engine has access to both projects within the solution from source control so it can either include unit tests or not, depending on your desired build action.

By having both the code under test and the tests themselves within the same solution, we can use the same source control process allowing both the code under test and the tests themselves to be in one place, which prevents drift between the test and the code under test.

When developing databases in SSDT, using this method gives me the best of both worlds; tests which to live with the database under development  including within source control, and yet do not need to be removed from the database as part of or after the deployment process.

Book Review - Tribal SQL

How many of you have written a book? Probably not many. Yet how people working with SQL Server have thought that they knew better, or that someone else would really appreciate how you do something? Probably a lot more.

Trying to encourage new authors to share a small, manageable amount of knowledge on a subject, like the lightening talk of the book world, is one of the aims of the book I'm looking at in this post - Tribal SQL.

It came about from a wish of Jen and Sean McCown (The Midnight DBAs) to let people read the insight and enthusiasm which is present in the less often heard corners of the SQL Community. I think they've done a good job in encouraging new voices.

The subjects covered are as varied as the topics - and all are in an informal, insightful manor, which encourages this both as an introduction to areas into which you might not have previously strayed, and also as a reference material / revision of best practice for those who have.

I'd strongly recommend this book to Accidental DBAs, who may find themselves suddenly confronted with a lot of new requirements and challenges, not the least of which is a distinct lack of training budget! (I'd also encourage attendance at SQL Community events, like SQL Saturday Exeter which is being held in March in the South West of England, and is free to attend).

This book doesn't restrict itself to the technical subjects (although it certainly does cover them, for example in Storage Internals and Data Compression) but also covers softer skills, such as project management skills, and how to get a stellar performance review. There's more basic introductory topics too, such as Stephanie Locke's chapter on reports, and how to gather requirements, implement a good relationship with others in the organisation, and produce them a great report.

I found the breadth really refreshing - the authors clearly want you to learn, and the fact that they've donated their royalties to charity really underlines their wish to help those around them.

If you aren't convinced, Red Gate have currently got a sample chapter on their website (UPDATE: as Melanie pointed out in the comments below, there are three sample chapters from the book on the Simple Talk website - Agile Database Development, Guerrilla Project Management for DBAs and SQL Server Storage Internals 101), and you can buy the book from Amazon - there's even an e-book available! The code samples are all available from the Tribal SQL website.


Disclosure: I was sent a review copy of this book free of charge by Red Gate Publishing, but otherwise received no remuneration or reward for this review.

tSQLt adds Function Mocking to the unit testing armoury

The latest release of tSQLt (version V1.0.5137.39257) was published last week, and you can download this version in the usual place. If you're using SQL Test with tSQLt, you can upgrade SQL Test to the latest tSQLt release by following these instructions.

(Note, if you've not come across tSQLt before, it's a database unit testing framework written in T-SQL, and there's a great article explaining why you might want to use it here. Red Gate have written SQL Test to help integrate the power of tSQLt with the SQL Server Management Studio environment. This article assumes you're familiar with tSQLt, but if not I'd strongly encourage you to look at it for unit testing your databases.)

I am pleased to see that this release adds function mocking, as well as a simpler way to rename classes than the previous work around, and checking for a specific error number to tSQLt.ExpectException.

Let's look at that function mocking in a bit more detail. Consider a function which should add two numbers together. We would want to unit test it, and we can do that in the normal way, but a test on a stored procedure which needs to isolate from that function needs to call our new tSQLt method tSQLt.FakeFunction.


CREATE FUNCTION dbo.AddTogether (@a INT, @b INT)
RETURNS INT
AS
BEGIN
   RETURN @a+@a
END;
GO

CREATE PROC Maths AS
  SELECT dbo.AddTogether(1,2) AS SumOfNumbers
GO

Now, we can see there is a bug in the function above (and if we run the Stored procedure, we will get a value of 2 returned), but let's ignore that function and proceed to test our stored procedure. Remember, because we are isolating from our dependencies, we don't expect out stored procedure to fail its unit tests.

The way that FakeFunction works is that you need to supply it with a stub function to use in place of your function to be isolated. I suggest you put them in the test class. So let's create a simple function that returns a static value - 3.

EXEC tSQLt.NewTestClass @ClassName = N'MathsTests' -- nvarchar(max)
GO

CREATE FUNCTION MathsTests.Fake (@a INT, @b INT) 
RETURNS INT
AS 
BEGIN
   RETURN 3
END
GO

Now we are ready to create our test:

CREATE PROC MathsTests.[test I get a value of 3 returned when I add 1 and 2] 
AS
--Assemble
EXEC tSQLt.FakeFunction @FunctionName = N'dbo.Addtogether', -- nvarchar(max)
            @FakeFunctionName = N'MathsTests.Fake' -- nvarchar(max)

CREATE TABLE MathsTests.Expected (SumOfNumbers INT)
CREATE TABLE MathsTests.Actual (SumOfNumbers INT)
INSERT MathsTests.Expected (SumOfNumbers)
VALUES (3)

--Act
INSERT MathsTests.Actual
EXEC tSQLt.ResultSetFilter 1,'exec dbo.Maths'
--Assert
EXEC tSQLt.AssertEqualsTable @Actual = 'MathsTests.Actual', 
                                               @Expected = 'MathsTests.Expected'
GO

If we run this test, we get a successful test, because the stored procedure under test returns a row with a value of 3, the expectation, as our code under test (the stored procedure) is isolated from the function which has the bug in it. Of course, you would want to ensure that any module from which you isolate is properly tested, so that you catch issues such as this, but this somewhat contrived example allows you to see how the true cause of the failure can then be found more easily.

The ability to fake functions in this way is a great addition to the unit test writer's armoury.

Learn how to unit test your databases from the comfort of your desk!

Following on from my previous posts on tSQLt, I've recorded a training course for Pluralsight which goes through how to use tSQLt and SQL Test to unit test your databases, to give you confidence in your database code.

I've used SQL 2012 Developer Edition for the course, but tSQLt will work with any version of SQL from 2005 SP2 upwards, and you don't need to purchase SQL Test to follow along.

Pluralsight offer a 10 day free trial of their service, so you can try before you buy.

My three hour course takes you through why you would want to unit test, and introduces the tools we will use. I then show you how to write your first unit test, before going into more detail on how to isolate dependencies, and the variety of things to test for. We look at how to unit test in your day to day workflow, before looking at some more advanced topics, like using unit tests to enforce development standards.

The course assumes you are familiar with T-SQL code and stored procedures, but have not used tSQLt before, so it should be of interest to those new to the concept of database unit testing as well as those who have dabbled with tSQLt or SQL Test.

To see the course, or the detailed list of content, click here.

T-SQL Tuesday #49: Wait for It …

This is a post inspired by the T-SQL Tuesday blog party series; this month (#49) the topic is hosted by Robert Davis (@SQLSoldier). Robert challenged us to "give us your most interesting post involving waits". Which reminds me...

I used to live in a little hamlet in the north of Devon. The closest post office was about 5 miles away, and this was so far in the sticks that the post office and village shop were second only to the pub for being the centre of the conversationalist's universe. As this was both in one, you can imagine how much information flowed through that little building!

However, this wasn't all great news if you were in a hurry. Occasionally, I'd pop across to post some item that needed weighing, or proof of purchase, and discover that in order to buy my stamp and post the letter, I'd have to wait for the conversation in front of me to finish. There was only one serving position, so It wasn't unknown for a queue with two people in it to take upwards of 45 mins. There was a similar queue if you wanted to buy stationary or similar from the shop desk (it seems to be a British tradition that the post office queue is separate from the queue for the rest of the shop - even if the same person then serves you).

So, I hear you ask, where is this going? What relevance does this have? Well, the post office / corner shop metaphor is a reasonable one for some of the waits in SQL Server (and I believe a reasonable attempt at a somewhat humourous twist on the topic at hand). 

We have the queue - the "to do" list, of people waiting to be served - queries or processes which are waiting to be worked on. We have the thing they are waiting for - time at the counter - this could represent resources such as RAM, with the worker who serves us as the CPU. We have the question of whether there is more than one person serving at a time (Parallelism). Perhaps we have a post office and a corner shop (2 instances) in the same building (server).

What about the shop owner (DBA)? He has to plan for whether he has enough capacity (resources) for the people coming to the shop (workload). He needs a big enough doorway (IO) to cope with the people coming and going, with their parcels (data). He doesn't want to pay for (license) more staff than he has need for, but neither does he want people to dread coming to his shop (Server) to do their business (get, put or update the required data) because it takes too long - otherwise he may lose his contract to run the post office (job)! 

In the day to day process of managing the flow of people through a post office, our shop owner needs to ensure he has information about why people aren't happy, so that he can solve it. Our DBA has these tools however, and one of these is the wait stats view sys.dm_os_wait_stats, which gives the aggregated view of waits that the system has encountered - the equivalent of an anonymous survey of all customers using the shop during the day asking what they waited for, which gives us the tools to see where we might be stretched for resources. As with the survey, sometimes the results are a little misleading, and so we need to dig further to cure the problem, but it gives a good place to start looking.

A lot of the problems in this simple metaphor are familiar to us all, but I find that relating the technical details to a real world environment can help to clearly explain why things aren't working, and perhaps help us to solve a constraint on our work.

Lastly, yes I know that this was strictly about waits involving post, rather than a post involving waits... ;-)

Using Insert Exec with multiple result sets

Sometimes, there is a need to get result sets from a stored procedure into a table. One deceptively simple method is with a Insert..Exec statement.

This works fine for a simple example such as:

USE tempdb
GO

CREATE PROC dbo.tempproc AS
    SELECT  'a' AS FirstChar ,
            'b' AS SecondChar ,
            1 AS FirstInt

GO

DECLARE @table TABLE
    (
      FirstChar CHAR(1) ,
      SecondChar CHAR(2) ,
      FirstInt INT
    )
INSERT  @table
        EXEC dbo.tempproc
SELECT  * FROM    @table


This returns our table, as we might expect.

However, the situation gets a little more complex if we return from our stored procedure two datasets, which have the same metadata:

ALTER PROC dbo.tempproc AS
    SELECT  'a' AS FirstChar ,
            'b' AS SecondChar ,
            1 AS FirstInt

    SELECT  'c' AS FirstChar ,
            'd' AS SecondChar ,
            2 AS FirstInt
GO

Now, when we run our code to capture the dataset we get:

This is perhaps a little surprising - and gives us the scenario where the results from our stored proc are indistinguishable from what we would get if we had:

ALTER PROC dbo.tempproc AS
    SELECT  'a' AS FirstChar ,
            'b' AS SecondChar ,
            1 AS FirstInt
           UNION ALL
    SELECT  'c' AS FirstChar ,
            'd' AS SecondChar ,
            2 AS FirstInt
GO

However, if we accessed this same data via a different method (e.g. from CLR or an application looking at distinct result sets) these would be distinguishable. This is in my opinion counter intuitive behaviour which can give us a misleading result.

Suppose you were capturing a result which would be either in the first result set or in the second (the other to be returned empty). It follows that you couldn't use Insert..Exec to capture this as you would be unable to determine which set had caused it. In other words, the results returned using Insert Exec with the following two queries are indistinguishable:

ALTER PROC dbo.tempproc AS
    SELECT TOP 0 'a' AS FirstChar ,
            'b' AS SecondChar ,
            1 AS FirstInt
          
    SELECT  'a' AS FirstChar ,
            'b' AS SecondChar ,
            1 AS FirstInt
GO

ALTER PROC dbo.tempproc AS
    SELECT  'a' AS FirstChar ,
            'b' AS SecondChar ,
            1 AS FirstInt
          
    SELECT  TOP 0 'a' AS FirstChar ,
            'b' AS SecondChar ,
            1 AS FirstInt
GO

Try running the queries individually and you will see what I mean!

This is something to be aware of, both when coding and testing stored procedures, especially if Insert..Exec is used to capture information.

Testing for Exceptions with tSQLt

I've recently been looking at two relatively new pieces of functionality in tSQLt, the unit testing framework that underpins Red Gate software's SQL Test.

I've written and presented about SQL Test and tSQLt before, including how to upgrade SQL Test to the latest version of tSQLt, but in case you've not been aware of these tools previously, tSQLt is a unit testing framework which allows SQL Server developments to be properly unit tested, within the database, and SQL Test is a user interface which makes it easy to see and run tests.

One of my gripes in the past has been the limited functionality for testing exceptions. Most applications will raise an error in some place or another, hopefully well captured and handled, but not always. Often the database needs to raise an error to feed back to the application, and this should be tested in the same way as any other requirement.
The problem which I have come across in the past is in how to prevent the inbuilt transaction handling capabilities of SQL server from rolling back the test transaction if you hit an error (tSQLt runs all tests in a transaction). This can cause the test to error, which of course is contrary to the desired behaviour. Previously I've had to use a catch-try block to set a variable which I then evaluated to check whether an exception had been raised.

That's where this new functionality comes in - we have two new functions, ExpectException and ExpectNoException which encapsulate the following command, watching for an exception, and pass or fail the test appropriately. ExpectException can even look out for a specific message, and fail if you get a different message.

So, a demonstration. Consider the following stored procedure:

CREATE PROC dbo.MyProc @Myvar INT
AS
IF @Myvar != 1
BEGIN
  RAISERROR ('Variable is not one!',16,1)
END
GO

This will create a procedure that will error if you do not supply a valid input - a number 1. Clearly this is a fabricated example, but it illustrates the point.

This can be tested with the following test:

EXEC tSQLt.NewTestClass @ClassName = N'ExceptionTesting' -- Test class creation
GO


CREATE PROC ExceptionTesting.[test wait for exception old method]
AS
DECLARE @error INT = 0 /* flag to indicate that an error was raised */
BEGIN TRY
  EXEC dbo.MyProc @Myvar = 2 /* the code under test */
END TRY
BEGIN CATCH
  SET @error = 1 /* Set variable as an error was raised */
END CATCH

IF @error != 1  /* Raise an error if the variable has not been set */
  EXEC tSQLt.Fail 'Exception was not raised'
GO

Which will fail the test if an exception is not raised. It is even possible to test for a specific message using ERROR_MESSAGE to determine this in the catch block.

The new functionality however makes this much more user friendly as a test, which of course makes this more maintainable.

So how would this test now look?

CREATE PROC ExceptionTesting.[test wait for exception new method]
AS

EXEC tSQLt.ExpectException @ExpectedMessage  = 'Variable is not one!'
  EXEC dbo.MyProc 2

GO

As you can see, this is much simpler, and easy to see what is being accomplished. It is also possible to check for specific messages (as has been done here), severities and states, as well as using pattern matching (for example for date or time based messages) as part of this same statement.

It is worth noting that this is defined in tSQLt as an expectation rather than an assertion, which may catch some out in terms of naming, but is sensible to denote that the expectation is before the code under test is run, whereas assertions happen afterwards.