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.

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.

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... ;-)

What happens when the audit trail meets a data migration?

This is a post inspired by the T-SQL Tuesday blog party series; this month (#45) the topic is hosted by SQLMickey. The topic this month is Auditing, so I thought I'd write some thoughts on how to deal with auditing when doing a system migration.

Most systems have an audit trail of sorts, for legal or compliance reasons,  or possibly just to make the system administrator's life easier. The idea is that this forms a permanent record of who did what, when.

What happens to this when it's time to change the system or migrate the data out to a new system?  How so you retain the audit trail, both of the old system and prove the methods used to transfer the data haven't changed it?

There are many types of audit trail, including paper based ones, but during IT projects focus is usually concentrated on the electronic ones built into systems. Usually, you cannot import the trail from an old system into your new system, as that would imply an editable audit trail. This means that you have to use another method in order to maintain traceability and the link between the old system including the data in it and the new.

If the old system is being maintained then it may be sufficient to simply keep the old system logs where they are, and keep accurate records as proof of how this was transferred to the new system.
During the planning stages of an ETL migration it is likely that documentation will have been developed and this can be retained as proof of how the data was to be migrated.

This may be in the form of data maps, or instructions, but needs to be at the detailed level necessary to provide the required traceability.

By migrating data between systems you break the continuity of the records, so it is important that the records you keep are sufficient, and I find that it's best to involve the person from the business who has to defend items to an external auditor at an early stage, as they need to be comfortable that sufficient records are kept. This may involve data that will change as part of the migration, particularly when changing between systems that have a different way of organising data internally - but in my experience this is not important if proper records are kept such that the data can be related - in either direction - in the future.

If an old system is being decommissioned, it is important to decide whether the audit trail needs to be kept, and if so for how long, as this may be different to how long the data itself needs to be kept. You may also have decisions to make as to the form in which the data is kept - often this is linked to the cost of storage, for example if storing it in the previous system means that a licence would need to be maintained to access the information, but the sanctity of the audit trail must also be considered if the data is to be exported from the system.

A final point I would make is that however you retain this continuity between the data before and after migration, it is sensible to get someone else to review your work - both in terms of requirements and implementation, and document it so that even your decisions about the audit trail are audited.

T-SQL Tuesday #42 - The Long and Winding Road

Have you heard of T-SQL Tuesday? I hadn't until recently, but it's a series of monthly blogs started by Adam Machanic ( blog | twitter )  some years ago (well, 42 months ago, one presumes) for all to participate in. The idea is that each month someone hosts it, that person sets a topic which we all post to and link our post to twitter using the hash tag #tsql2sday.

This month, the topic is set ("hosted") by SQL MVP Wendy Pastrick ( blog | twitter ). She's entitled it "The Long and Winding Road" and challenged us all to post about how you got to where you are, and what you plan to do next, noting what technologies are key to your interests or successes. Here's my story - and first T-SQL Tuesday post...

I've been using databases (predominantly of the Microsoft variety) for more years than I care to remember now, having started with data entry before rapidly moving on to programming Access (v95? or possibly as early as v.2.0) databases as part of my first full time job (not what I thought I'd be doing, but a good use of what was until then just a hobby). I soon moved onto using SQL Server as well, in about the year 2000. I've dabbled with other technologies, spent time programming, dealt with business administration, but generally it was data management that I enjoyed, and that remains my area of focus. The technology has changed somewhat, but the core concepts remain remarkably unchanged. I've changed how I work, have worked with large companies and small, and every challenge is different.

Fundamentally I think that the ability to speak to all levels of the business is required as a data professional - you need to be able to connect with the CEO who wants a key metrics view of the business' information, the data entry clerk who has to record the information, the developer who has to build the system, the IT department who have to manage it and the CFO who wants it all done for no money. Of course none of them want to hear that the solution isn't absolutely perfect (ideally with respect to what is needed in the future), or needs maintenance - but that's all part of the fun!

If I had to say what technology had changed my working life more than any other, it's probably the Internet. Not the protocols, or changes to how I interact with data, but the ability to interact with my peers on demand, and help learn by engaging with other professionals - and amateurs - as they look at their own data challenges. It's introduced me to many more fellow-professionals than I thought existed, the talked about #sqlfamily.

I recently posted about how the Internet can help you find your next role, and also how to connect with the community, and I would also point to Q&A sites like ask.sqlservercentral.com and stack overflow. They're a great source of knowledge and expertise if you need inspiration on a problem, and I have found that by digging into other peoples' questions that interest me I've learned a lot myself. This helps steer my career, by way of introducing me to new technologies I may otherwise not have looked at.

What's next for me? I'm not honestly sure. I've just (in the last week) had my first child, and so a lot is changing right now on a personal level - so it seems a good time to re-appraise my career priorities. Whatever is next, I'm sure it won't be something I could predict now, except that it will be something exciting...