Category Archives: community

dbachecks – SQL Server compliance testing with simple configuration management

if you’ve not heard yet, the people behind the dbatools PowerShell module (including me) have a new toolset for you, dbachecks. dbachecks uses Pester to let you validate your SQL Server estate in a simple way and generate meaningful graphical reports. The official launch of the module is at SQL Bits 2018

Out of the box dbachecks uses test values that we’ve found to be the most appropriate from our years of experience with SQL Server. But these may not be the best values for your particular organisation. For example, we expect to see a Full backup less than 24 hours old for each database, in your case you might only take a Full backup once a week and use differential backups during the week. So we needed a flexible and simple system to let you change the values. dbatools friend Friedrich Weinmann (b | t) has written a great PowerShell module framework called PsFramework which we’ve integrated into dbachecks to handle the configuration of the tests

Out of the box dbachecks will load it’s default config values from the file .\internal\configurations\configuration.ps1 (#! link to GH). To see the current values use Get-DbcConfig:
Get-DbcConfig ouput

I’ve just picked the top few rows for the screenshot, as of writing (15/02/2018) there are 98 config options available. We’ve tried to make the configuration option names obvious. There are main sections of configuration values group:

  • app – Configuration for the module
  • domain – Configuration for authentication
  • mail – Configuration for sending reports
  • policy – The values for the tests
  • skip – Controls which tests should be excluded

And each has a useful desription as well to make it easy to find. In the screenshot you’ll notice that I’ve configured the mail.* options to suit my test environment. So now I can use Send-DbcMailMessage to email my test results without having to specify all the parameters. To set a configuration parameter you use the Set-DbcConfig command:

Set-DbcConfig -Name mail.subject -Value "DbcChecks report"

Set-DbcConfig example

Or perhaps as mentioned at the start of this post, you don’t want a Full backup in the last day, but in the last 7 days. You can easily configure that:

Set-DbcConfig -Name policy.backupfullmaxdays -Value 7

And now your tests will be checking you’re never more than 7 days from a full backup. By default we’re checking that you’ve taken a Differential backup in the last 25 hours, so you’re now good to go!

And don’t worry, you don’t need to reset these every time you run the tests. The PsFramework module persists these non default sessions in the Windows registry at:

Computer\HKEY_CURRENT_USER\Software\Microsoft\WindowsPowerShell\PSFramework\Config\Default

So on my system we can see:
DbaChecks registry storage

Now what happens if you want to distribute these config changes to multiple servers, or share them with colleagues to make sure you’re all singing the same tune. We’d strongly recommend you don’t modify the .\internal\configurations\configuration.ps1 file directly, as that will be replaced whenever you update the module. To make your life easier (which is the entire point of the module) we’ve included Export and import functionality. To export the configuration to an easy to parse JSON file you simply run:

Export-DbcConfig -Path c:\path\of\yourchoice\filename.json

If you omit the path value then by default we will export the results to $script:localapp\config.json. Now you’ve got a simple JSON file it’s easy to source control (you are using source control aren’t you?) to keep track of changes and make sure they were implemented as expected. If you want to apply that configuration to another install, then it’s simply a case of running the import command:

Import-DbcConfig -Path c:\path\of\yourchoice\filename.json

and you’re done. We support UNC, so again it’s simple to have a central repository to apply the same configuration. And it works well with just fragments of configuration as well, so if all you wanted to control were the email settings you can create a JSON file like this:

[
    {
        "Name":  "mail.failurethreshhold",
        "Value":  0,
        "Description":  "Number of errors that must be present to generate an email report"
    },
    {
        "Name":  "mail.from",
        "Value":  "null@stuart-moore.com",
        "Description":  "Email address the email reports should come from"
    },
    {
        "Name":  "mail.smtpserver",
        "Value":  "smtp.stuart-moore.com",
        "Description":  "Store the name of the smtp server to send email reports"
    },
    {
        "Name":  "mail.subject",
        "Value":  "DbcChecks report from stuart",
        "Description":  "Subject line of the email report"
    },
    {
        "Name":  "mail.to",
        "Value":  "SqlReports@stuart-moore.com",
        "Description":  "Email address to send the report to"
    }
]

Then you can import just this snippet to set the configuration for those options. Makes it easy to seperate out enterprise level configuration changes from the actual SQL test options. The same technique can be used to ‘force’ the correct backup testing parameters in all cases, while letting other tests be customised as needed.

T-SQL Tuesday #97 – What I’ll be working to improve in 2018

T-SQL TuesdayThis month Mala has asked us to post about our Learning Goals for 2018. Now, there’s nothing like nailing your colours to the mast so you can come back in a year and see if you actually did something (though let’s not talk about my 2017 exercise plans!), so here goes:

Tech Learning Goals

Networking

I read TCP Illustrated years ago, so apart from IPv6 I’m pretty solid on the fundamentals. but the increasing number of ways these can be manipulated and managed has ballooned. And no matter where your data and applications are going to live in the future it’s still going to be essential to know how they can be connected. So I’m definitely pencilling in some time with our Network Infrastructure team to get an overview of what’s out there, and what they see as the future. I know I can bingle that sort of stuff, but it’s even better to get some professional advice

Then I’ll be hitting Pluralsight and other video and looking forward to building some very different labs to the ones I’m used to. For once the servers and the data won’t be the focus.

The aim is to sit in a meeting with our network team and keep up with everything! Though I might draw the line at memorising the Cisco model numbers like the seem to have

More Automation

I’ve increasingly automated a lot of my repetitive work tasks. But I need to automate a lot of the routine work that’s come in a filled up my ‘spare’ time. Now I’m as guilty as the next IT Pro about sticking to the easy fix for automation and sticking to technologies I know. Well, for 2018 I’m going to start branching out and make the automation to force me to learn some new technologies. So rather than sticking to PowerShell I’ll be looking at Python and Go in more depth, looking at more serverless Cloud technologies for data transformation and endpoints rather than spinning up more VMs or containers and trying to let other Cloud services take the strain rather than me.

If I get this right and make progress, then the other things on this list will come to pass. Freeing up time to work on the good stuff will be the biggest pay off.

New Personal skill

Improve my Writing

I’m not a natural writer by any stretch of the imagination, my natural inclination is numbers, equations and strange pencil diagrams that only I can decode. This tends to limit me when writing documentation, and blog posts take a disproportionate amount of time to write.

So I’m going to head to the Nottingham library to get some books on writing. And to make sure I put it into practice, I’m going to be keeping a journal from January 1st (don’t worry, it won’t be online!). Hopefully forcing myself to write everyday without an audience will help me with writing for an audience. So I’ve 2 weeks to find a nice pen and notebook to try and inspire my inner Samuel Pepys!

New non tech skill

Get better at marketing myself

In the classic British manner I’m happy to hid my lamp under a bushel. So for next year I’m going to try to put myself out there. I’m hoping this will be help along with some from improving my writing as above. I’m going to make more use of asking other tech friends to review session submissions with useful feedback. Asking for feedback from organisers on my submissions, including when I succeed so I can build on good things as well as fixing the bad.

And hoping that the increased blog posting from my better writing skills means I’ll have more content to share here, and from the new tech skills more interesting things to write about.

The metrics for this one will be the number of events I present at in 2018, the number of visitors to my blog.

Conclusions

To me that looks like a good solid set of skills to work on. They complement each other, so an improvement in one should help with another one. They can also be worked on at different rates, which stops things getting stressful if something has to be put aside due to other pressures.

Nottingham and East Midlands PowerShell User Group

PowerShellInterested in any form of PowerShell usage and based around the East Midlands and Nottingham? Then this could be the group for you. We’re looking to cover anything that uses (or can be used with) PowerShell. So topics that are fair game include:

  • AD Management
  • Scripting
  • Source Control
  • DevOps
  • Azure/AWS/Cloud Provider of your choice
  • Exchange management
  • SQL Server
  • Pester testing
  • Continuous Integration
  • .Net Internals
  • Generally anything that would interest someone using PowerShell or give their career a boost!

Nothing is set in stone yet as we want to get some feedback from potential members. There’s a date booked for the 8th Febuary (Kick Off meeting), but what happens is up to you.

Would you prefer a traditional usergroup with booked speakers given presentations in a formal setting, or something more informal like a roundtable/whiteboard sessions? Or perhaps half and half?

We’d love to know what you’d like to see or learn. So please either drop a comment below or sign up for the Kick Off Meeting and give us feedback on Meetup.

New year, New speaking dates

Lining up a few SQL Server Usergroups speaking sessions for the year already:

All sessions will be:

Indexing Nightmare – Cut Through the Clutter

Inherited a database with 30 indexes on every table? Has the vendor for your 3rd party app recommended more indexes over the years than you can remember? Got indexes that were added to fix a data load problem 4 years ago, but not sure if they’re still being used? Indexes are key to SQL Server performance, but like everything too much of a good thing is a bad thing. In this sessions we’ll look at how you can analyse your current indexes with the aim of consolidating them into useful ones, even removing some completely and how to improve the ones you’ve got left

Except for Southampton, where it’ll be:

Get on the Bus

As time goes by, more systems are crossing hosting boundaries (On Premises, Azure, multi-cloud provider, ISVs). We need a simple reliable mechanism to transfer data between these systems easily, quickly and reliably. Microsoft have taken their Message Bus technology and moved it to the cloud as Service Bus. This session will introduce you to this service and give examples of how internal databases can safely process data from cloud hosted applications without having to be exposed to the InterTubes. Examples are predominantly .Net C#, but aren’t complex!

Dates

Nottingham SQL Server Usergroup – 12th January 2017
(Also presenting will be Steph Middleton, talking about Building a Robust SSIS Solution)
(More details and registration here

Midlands/Birmingham SQL Server Usergroup – 19th January 2017
More Details and Registration here

SQL Surrey Server Usergroup (Guilford) – 20th February 2017
Link and details to be confirmed

Southampton SQL Server Usergroup – 1st March 2017
More details and registration here

Hope to see some of you there. And if there’s any other usergroups out there that are looking for speakers then let me know, have presentations on SQL, Powershell and general IT process to offer.

So you want to present at a SQL Server Event

So you’re thinking about stepping up to speak at a SQL Server (or any other technical event), or are having your arm gently twisted by an organiser to do so. How bad is it going to be?

tl;dr version:
– Just do it, it’s easy, and it’s great!

Long Version:
Not very. Let’s break down the most comment arguments:

1) – I’m not using the later version
Doesn’t matter. Most people out there won’t be. As of writing this, there are very few people running SQL Server 2016, but there are a lot of people still on SQL Server 2012 (and older!). So don’t think you have to be talking about the latest greatest feature

2) – I’m not using the coolest technology
Yes, each SQL Server release has a must-use technology which people preach about. But that’s not always what people want to hear about. Replication is as old as dust, but it’s still something people want to learn about or know how to fix, a good replication talk aways gets listeners. I talk a lot about backups, and not the new features either, and those talks go down well. What about indexing and performance, well those are perennial favourites, and everyone does them differently so maybe you’ve got something to add there

3) – I’m not doing anything exciting
Neither are most people out there! The lie in the marketing papers is that everyone should be doing a billion transactions a second and have a multi terabyte Web Scale database!
Truth is, 90% of your audience aren’t doing that either. Most of us have the same issues, too many databases and not enough time to look after them all. Those are topics that will grab people

4) – I’m not going in depth enough.
I admit it, I love a good Bob Ward (w|t) or Bradley Balls (w|t) 500 level session on deep SQL Server internals, but then that’s me!
For most people a good level 200 session on a topic is a great introduction to that topic, pushing into 300 for someone who wants to move on to the next level. So don’t worry if you’re not breaking out the debugger or tracing into dll calls

4) – I’m not an MVP or other high end consultancy title
Neither are most of us doing the speaking. Don’t let that hold you back. You think they got those titles before they started speaking? It’s putting yourself out there that get’s you noticed.

5) – I don’t have enough content
You’ll be surprised how easy it is to fill up 50 minutes with content. And that’s without questions, once they come into the picture you’ll find yourself accelerating to get everything in. Demos always take longer than you plan as well, seriously, never underestimate how long demo can take in front of an audience!

6) – I’ve not done it before
We all start somewhere (Birmingham SQL User group for me many years ago), local user groups are good as you’ll have friendly faces around. If you want to dip a toe in the water then keep an eye out for events offering a shorter quicker intro, for example lightning talks of 10-12 minutes for you to have a go with, or there’s webinars, so you can present from the security of home.

7) – Don’t be afraid of questions, or you answers (An addition suggested by Rob Sewell (w|t))
Yes, people will ask questions. But don’t be scared of them. I’ve yet to see someone throw in a question explicitly to be nasty to a presenter. Most of the questions will be because someone’s not quite followed what you’re saying so repeat yourself and see how that goes. If you get a question you really can’t answer, you can’t answer in a reasonable amount of time, or is going to lose the rest of your audience you can always arrange to take it afterwards or give them your contact details and discuss it offline.

So there’s nothing insurmountable there. All group leaders and organisers want to see new speakers, so don’t be afraid to ask for help. We’ll happily let you know of any topic requests we’ve had from our members, or give you feedback on your topic. They’re also happy to go through your presentation with you before the big day to make sure it’s going.

Post up below if there’s anything else you’re worrying about. And if there isn’t, go and start writing that presentation

SQL Relay Nottingham 2016

SQL Relay logp

After a great time last year, we’re bring SQL Relay back to Nottingham on October 6th 2016.

We will have 3 tracks covering SQL Server, BI, and Analytics. There’ll be 3 tracks of 1hr sessions, plus a workshop track with half day sessions.

We’ll be covering a wide range of topics such as SQL Server performance, management, and development, Azure/Cloud technology, R, big data, cubes, reporting and dashboarding. These topics will be covered at a variety of levels so there’s something to suit everyone whether an accidental DBA, a hotshot BI pro, or a jack of all trades.

All of this for the princely sum of 0p! We’ll even provide lunch. You won’t get a better SQL Server training off in Nottingham in 2016

Speaker submissions are open if you want to speak. Closing date is 19th August

The timetable for the day should be out shortly after that.

Register to attend or speak here – Nottingham SQLRelay Registration

Nottingham SQL Server User group begins

After many successful years running in Leicester we’re proud to announce that the PASS East Midlands chapter is moving to Nottingham for 2016.

Gavin had been running the group since it’s birth, but has now handed the reigns over to myself and Tobiasz. As we’re both based in Nottingham we have decided to move the SQL User group to Nottingham. With the response we had for Nottingham’s first SQL Relay event we wanted to build on that, and this also makes it more accessible with Nottingham’s connections to the rest of the East Midlands and Lincolnshire area.

We’re going be based at Capital One’s Trent House on Station Street, easy to get to from Derby, Leicester, Loughborough, Lincoln, Sheffield and all parts of Nottingham

We have 4 SQL Server User group dates lined up for next year:

As with all PASS SQL User groups there is no charge for attending, yep, that’s free training! We’ll even provide some food and drinks while you learn!

Myself and Tobiasz are speaking at the first event, and we’re currently lining up some great home and international speakers for the other sessions.

But we’d love to hear from you if there’s any particular topics you’d like covering, I’m happy to go out and find speakers covering areas of special interest to our members. Also let us know at what level you’d like sessions presented, if you want an introduction to a particular topic that’s great, as is wanting a level 500 brain warper from the likes of Bob Ward!

All details for future meetings will be posted via our Meetup page – PASS East Midlands User Group.

Please register with the meetup group to keep up to date. You’ll also need to register for the event to attend. This is because we need to provide Capital One with a list of names to be signed in at security, so if you haven’t registered then you won’t be allowed in I’m afraid.

Look forward to seeing some of you there. Please post any comments here or at PASS East Midlands User Group

SQL Relay bringing free SQL Server Training to Nottingham

sqlrelaylogo70Having spoken at the last couple of SQL Relays, I’m very happy to announce that in 2015 we’re visiting Nottingham for the first time.

On the 7th October SQL Relay will be at the Nottingham Albert Hall conference center.  Speaker submissions are now coming in (and if you fancy speaking, please submit a session), and we hope to release the full lineup early in August. Based on previous years there will a wide range of high quality presenters.

At Nottingham we will be having 4 tracks on the day:

  • SQL Server
  • Business Intelligence
  • Business Analytics
  • Workshops provided by our sponsors

Across these we’ll be covering topics such as SQL Server performance, management and development, Azure/Cloud technology, R, Big Data, Cubes, Reporting , Dashboarding and much more.

If you’re a SQL Server DBA, then this could be a great opportunity to pick up some Business Intelligence crossover training. Or for a Business Intelligence developer, find our how to use Big Data solutions (on Azure, without the hardware costs) to analyse your data even further.

All of this great SQL Server content is available for the princely sum of Nothing. That’s right, its free, and we’ll even provide you with lunch.

We’re open for registrations, so sign up today and look forward to some quality free SQL training in Nottingham

If you’ve any questions, then please ask them.

Doing something a bit different at SQL Saturday Exeter 2014: PowerShell for the curious SQL Server DBA

sqlsat269_webHaving had such a great time at last years SQL Saturday in Exeter, I was very happy to see the SQL Southwest team announce another one for 2014 on Saturday 22nd March (Register here).

So, I’ve been offered the chance to present 2 consecutive sessions covering 1 topic, 100 minutes to fill with something new. I’ve decided that based on feedback and questions from my “Using PowerShell for Automating Backups and Restores with PowerShell” presentation I’ll be filling the 100 minutes with:

PowerShell for the curious SQL Server DBA

Continue reading

Tagged , ,

Blog Copy Editing, a SQL Server Community approach

copy-write-crop

I’m sure I’m not alone amongst SQL Server bloggers in starting to write a new post in a fit of great enthusiasm, but eventually getting bogged down in a mire due to dithering about whether:

  • The point on my post is clear enough?
  • Is the post structured properly?
  • Is my writing clear and understandable?
  • I am using too many colloquialisms?
  • Do my code examples make sense?
  • Are any attempts at humour actually funny?
  • And a whole host of other things I’ve probably not picked up on, because someone hasn’t told me….

Having helped various people over the year proofread documents (My wife’s MA dissertation in English Literature being a highlight) I realise how important it can be to have another pair of eyes look over your work.

That pair of eyes are even better when they can offer usable criticism. So when I proofread my wife’s dissertation I could spot obvious spulling mistkes, but the chances of me spotting a problem when discussing the actions of the  Ranting Poets of the early 80’s was nigh on impossible. And vice versa, whilst an expert on Poetry and English Literature, Charlotte isn’t likely to pick up on my making a mistake whilst discussing PowerShell backups.

So, assuming that there are other SQL Server bloggers out there in the same boat I was considering setting up a mutual help group? We’d offer to help proofread and copy edit each others future posts, offering advice, criticism (Constructive!) and a potential soundboard.

If you are interested, drop a comment below. Depending on the uptake we might need to think of a clever way of managing this, but hey, we’re nothing if not inventive!