We’ll be looking at what is required to ensure that the data stored SQL Server is secure, and that your organisation can trust that data in it’s mission.
This is more than just a technical workshop. We’ll be spendin time looking into how you can’t secure data without the organisation buying in to the process. To generate a working security policy you’re going to need approval from the top, if your CEO isn’t willing to enforce security then you’re fighting a losing battle. So you need to know how to present an argument at that level for the appropriate level of security and the resources to implement it.
So topics we’ll be covering will includer
Seperation of Duties
Policies and Responsibilities
Cost of Security
What is out of your hands
Don’t worry, there’ll be plenty of techical content as well. We’ll be looking into
Cloud vs On Premise
Setting up the operating, system if you have one
Setting up SQL Server
Development best practices
and much more
The sessions is aimed at all levels of SQL DBA, Developer or anyone who has to ensure the security of data. No previous experience is expected. Any technical examples will be provided so you can work with them on your own time, or take them back to show your colleagues
Until 31st December the price for 2 full training days and 3 days of conference sessions is £999, moving up to £1199, and then £1499 from the 15 Febuary, so get in quick for a barging
If you’ve any questions then please drop me a comment, reply below, or get in touch via Twitter.
Sometimes you have to give people a little more access to an Azure environment than you might like, and then there’s the chance of someone accidentally deleting a resource.
A resource deletion may not sound like too much of a big thing if you’re deploying Infrastructure as code, hey we’ll just terraform apply again and it’ll pop backup.
In theory that’s a great idea, just with one big problem. The new resource isn’t the old resource!
For an example, an Azure SQL Database server is a unique resource. If you delete one you lose any backups you’ve taken as they’re hosted on the server. Spinning up a new one isn’t going to get them back! A phone call to MS Support may if you’re quick and lucky
To avoid this you want to be user Azure Resource Locks. Think of these as the Azure version of child proof locks on your kitchen drawers. Yes, they may occaisonally mean you’ve got an extra step to get a knife out, but the little on can’t get their hands on it.
Auzre Resource Locks
First thing about Azure Resource Locks is that they apply to everyone and every role. Even if you’ve the Owner role on a Resource Group via RBAC, if there’s an Azure Resource Lock on that Resource Group you’re going to be blocked until you’ve removed the lock
This is great because it prevents those “oh ****, that was the wrong subscription” moments
Locks apply downwards from the resource they’re applied to. So if you apply one on a Resource group then it’s lock applies to every resource within that resource group. Apply it to an Azure SQL Database server, and it will apply to all of the Databases on that server.
Azure Resource Lock Types
Resource locks come in 2 flavours
CanNotDelete does what it says on the tin. Once this lock is applied the resource (and it’s children) can not be deleted, even if you use -force
ReadOnly implements CanNotDelete and also prevents any modification of the locked resource and it’s children
Setting Azure Resource Locks
You can set Azure Resource Locks via the Azure Portal, Azure CLI, Azure Powershell or ARM Templates. Below are how you can set the same CanNotDelete lock on the Lock Resource Group using each of the 4 options:
So now we’ve seen how to create a resource lock, what are going to see if we try to delete the resourcegroup, just to prove it works and also so we know what to look out for when we bump into one we didn’t expect to see
As you can see the Resource Locks will stop you deleting the resource, which is nice. The errors messages are also nice and informative, so you know the resource is locked and at which scope the lock is placed. Which makes it easier to find the lock to remove it. Talking of removing locks:
Removing Azure Resource Locks
You can remove locks with any of the methods you can use to create them, so you’re free to mix and match how you do things.
az lock delete --name LockGroupNotDelete --resource-group Lock
Pleased to announce that we’ve now added Azure storage support to all the dbatools commands that copy databases around. This is part of our march towards full support for migrating on premise SQL Instances to Azure SQL Managed Instances
These changes also make it much much simpler to copy or migrate databases across SQL Server instances in different Active Directory domains.
The features in this post are available in dbatool’s main production branch from version 0.9.807 onwards
You’ll need an Azure Storage account set up, previous blog posts on doing that can be found at:
All the other parameters you’d use with Copy-DbaDatabase other than ReuseSourceFolderStructure are available. That one’s not used as Managed Instances don’t allow user to specify paths
You may still get errors when copying certain databases. At the moment we’re not checking all the internal features of a database to make sure they are compatible with Azure SQL Managed Instances.
Start-DbaMigration with Azure Managed Instances
With Copy-DbaDatabase now supporting Managed Instances we can also start making Start-DbaMigration supporting them as well.
This is a preview of where we’re going as this particular command has more moving pieces that we need to make sure are properly compliant with the differences between on premise instances and Managed Instances as linked to above.
The sections working so far are:
Copy-DbaDatabase, as explained above
Copy-DbaLogin, this will copy SQL Server logins across but not Windows logins as they are not supported in MI yet
Bonus news for cross domain transfers
A common query on the #dbatools channel on SQL Community is how to copy a database or migrate an instance between 2 SQL Instances in different Active Directory domains with no trust relationship. The SQL Server connection is fairly simple as we can use SQL Server Logins, but the shared storage is trickier.
Permissions have to be very loose, and deciding how to sort the networking out can take a while
The good news is that using Azure Storage get’s around all of those issues. Create yourself an account, create the credentials on the source and destination instances and you’re good to go.
It’s all secure. The backups are encrypted on Azure Storage, and are only accessible to those people you allow (even Microsoft can’t see them unless you give them your keys). All the file transfer is done under 2048 bit SSL encryption. Which makes it safer than having a non domain joined sever with a share opened up to everyone/everyone
This is iteration one in a process that we hope will make migrating to a Managed Instance as easy as we’ve made migrating across SQL instances in your own Data Centres.
Please feedback any errors or issues you come across, and we’d love to hear you feedback and ideas for improvements..
Feel free to leave them in the comment below, or drop them in to the #dbatools channel on SQL Community, or raise an issue on Github
If you’re in a panic there’s a TL;DR version at the bottom of the page
We’ve all done it. Working for ages tracking down that elusive bug in a project. Diligently committing away on our local repo as we make small changes. We’ve found the convoluted 50 lines of tortured logic, replaced it with 5 simple easy to read lines of code and all the test have passed. So we push it backup to github and wander off to grab some a snack as a reward
Halfway to the snacks you suddenly have a nagging doubt in the back of your mind that you don’t remember starting a new branch before starting on the bug hunt.
Snack forgotten you hustle back to your desk, mistype your password twice as you try to login, and there it is. You’ve pushed directly into the main branch!
Fear and embarassment kick in. Hell, how am I going to fix this! And how much of an idiot am I going to look!
Been there, done that, got enough T-Shirts I really don’t need to buy clothes.
First off, don’t panic. You aren’t the first, and you won’t be the last. We’ll walk through some simple steps to recover from this moden faux pas (I don’t think Debrett’s cover this situation yet, but if they’d like to I’m happy to syndicate)
First off, jump on Slack/Teams/Email or whatever you use to communicate across the project to let everyone know what you’ve done. It’s much less embarrassing to do it now that when someone’s forked off of your unchecked code, or checks something in and merges with it
Now you need to get your commits out of main branch. As these have been committed there’s 2 git options to look at, revert or reset. As this is a public SNAFU then revert is the correct way to do this
A git revert creates a commit that undoes the commits you’re reverting so everything is logged in the history.
git reset will remove the evidence it ever happened from the commit history, which makes it hard to pinpoint errors if you don’t get it perfectly right
This doesn’t mean a git revert isn’t without it’s issues or potential pitfalls. But, because you alerted everyone in step 1 (you did fess up didn’t you?) you’ve got some breathing space, so take it slowly and don’t screw it up again
First you need to work out how far back you need to revert to. Your options are to revert a set number of commits or to revert to a specific commit
To revert a x commits, you’d use this syntax
git revert HEAD-x
So to revert 2 commits you’d use:
git revert HEAD-2
But how would you know how many commits to revert? That leads into the next bit, working out which commit to revert to. There are 2 options here, github or git log
Getting Commit ID from git log
git log is the more complex way of doing this, but is much more flexible. And if you’re like me and spend most of your git time on the command line then it saves finding the mouse and a different window. We’ll look at 2 command line options that should be enough to sort out anything but the worst messages
Running git log on it’s own produces a lot of verbose output:
That’ll keep paging through as much history in that branch as has been kept. To exit just hit q
For some brevity we use the –oneline switch to just return the basic facts:
This is much easier to read, but still returns everything in the history. So let’s reduce that with the -x parameter, where x is an integer saying how many lines you want returning:
Hopefully if you’ve caught the issue quickly and warned everyone else of the issue you won’t have too many commits you need to scan. For the revert you need enough of the Commit ID to be unique, which –oneline gives you
Getting Commit ID from Github
As you’ve pushed the commits to github, github offers a nice way to see the commit history. Below is a snapshot of a repository I’ve just had to revert because I wasn’t paying attention.
To get the commit history, click on the highlighted number of commits.
This pops up a history list of all the commits with their commit hash:
There’s a handy clipboard button next to each commit to grab the Commit ID for the revert
Reverting the Commits
Now we need to revert the unwanted commits. There are 2 options. You can revert a specific number of commits from the current HEAD of the branch. For example, to revert the last 2 commits in the branch you’d use:
git revert HEAD-2
or you can revert to a specific commit id
git revert 93cd242
Personally, I always go with the commit hash. When you’re in a bit of a panic the first thing that goes for me is counting, so having an exact value to revert to saves a lot of double checking
And you can revert commit by commit if you wanted. In the example I’ve been using here I wanted to make sure exactly what was being taken out of files as I went along. So if you look a the git history from after I’d fixed things you can see 2 reverts at the top of the history
So now you’re reverted your local repository, the next thing you need to do it to push the changes back up to the github repository. I know you know how to do this as that’s what caused this issue in the first place!
Finally, let everyone know that they can use the main branch again and make a not to check in future
Let everyone know the branch is not right to stop any pulling/pushing/branching from it
Then get the commit history:
git log --oneline -20
Then revert to the CommitID before the wrong commits:
git revert <CommitID>
Finally push everything back to the origin:
Let everyone know the main branch is safe to use again.
It’s the start of a new year, so time to look at the things I’ve got coming up:
Just started booking things in for 2019, but already got a couple of things booked in that I’m really looking forward to:
I’ve been going to SqlBits for years, and this year I’ve been lucky enough to be picked to speak. I’ll be presenting my session ‘Slack for the DBA’, about why and how you can use ChatOps techniques in your day to day database support to speed things up.
Not sure which day I’ll be speaking on, but I’ll be there for Friday and Saturday if you want to say hi. Hopefully there for the traditional 5k SQL Run on the Friday morning as well
SQL Saturday Iceland
As I’ll be passing over on the way to the next event I thought it’d be rude not to stop off for this as well. Plus an excuse to finally visit Iceland
MVP Global Summit
The yearly gathering of Microsoft MVPs. Really looking forward to this. Meeting other MVPs, getting to talk to the MS product teams, and getting some in depth information.
Just starting to look at other events, and going to be booking in some Usergroup speaking slots as well. So if you’re looking for a Data Platform or PowerShell speaker for you user group then please get in touch
All the dates link to the individual meetup events where you can find more details on that evenings happenings. We’re picking up some quality speakers and topics for 2019. We welcome anyone who works with the MS Data Platform from beginner to expert, and we’re keen to hear from all speakers from first timers to those who’ve been around the international block 😉
Nottingham PowerShell Usergoup
And the Nottingham PowerShell Usergroup is back for 6 meetings in 2019 as well. This will be our second year hosting these event, and we’ve gradually built up a strong core audience, but we’d love to meet more PowerShell people. As normal we’ll be covering a wide range of topics that will appeal to all sorts of PowerShell users. The dates for 2019 are below, each being a link to the meetup for that date:
Again it’d be great to hear from you if you’d like to speak. Any PowerShell topic is more than welcome, and I’m more than happy to help you work on any ideas you have and get you started with your speaking career
This was my second trip to Hannover for PsConf.EU, a 4 day conference covering every corner of PowerShell.
Let’s start with the most important part of a conference, the content. Just a quick scan of the Agenda gave a hint of the quality lined up. With sessions from the PowerShell Dev team, and deep dives from community maestros there was plenty of highly technical content, but mixed up with some easier intros to new topics.
For me some of the highlights were getting a first view of some of the new features coming out from the DSC team for the LCM previews straight from the horse’s mouth (well, <a href=’https://social.msdn.microsoft.com/profile/Michael+Greene>Michael Greene the team leader), more details here. And the look at Pester internals from Jakub Jares (b | t) was good as well, now feel I know a lot more about the underpinning, and the reasons behind some of the ways you have to use it make much more sense.
No bad sessions from my point of view. A few issues with demos. Just from my point as a speaker, if you’re going to run a fragile multi machine demo, record a good version ahead of time. While it’s always good to do a demo live, with those setups once it’s gone wrong its’s very hard to recover.
The days run long, 08:30 – 20:00 on the wednesday with the 3 hour workshops at the end. For me this a bonus. When I’m at a conference I like to concentrate on the learning. Some conferences I’ve been to it’s felt as those you spend almost as much time at lunch or coffee breaks as you do in the sessions. So while there were long days it felt worth it.
The venue is well lit, the AV is great, even from the back of the room the screens were clear and the PA carrried the speaker right back. The rooms were closer together this year, and easier to navigate between. And despite the temperature being a step up from the UK the rooms were kept a pleasant but not overly cold temperature. Plenty of food and drink, the lunch menus weren’t holding anything back.
I’m lucky enough to know quite a few PowerShell community members already, but met plenty more at PSConf.EU. Everyone’s there for the same reason so it was always easy to spark up a conversation with someone, whether sat next to them waiting for a session or in the queue for lunch. Was good to see the number of PS UserGroups that were teased out over a lunchtime breakout session, hopefully it’ll be up on the web shortly.
Tuesday night was party night this year. Back to the Hannover Zoo for an evening of catching up and making new friends. The boat is pushed out with the venue, the demonstration from the Zoo (this year Polar Bear feeding), free food and beer. Don’t think anyone went hungry or thirsty that night.
From the UK this is a pretty easy conference to get to. From Nottingham I took the train down to Heathrow, a 1 hour hop over with British Airways and a 20 minute train journey saw me in the centre of Hannover. Taxis are about ¢12 from the station to the Conference hotel, but as I’d spent a lot of time sitting down I decided to take the 25 minute walk across.
This year I stayed in the conference hotel the Congress Hotel Am StadtPark next door to the main conference venue. Good hotel, room was a decent size and had all the usual features. The bar was reasonably priced and usually full of PowerShell geeks if you wanted a chat over beers. Restaurant was good, but not cheap. The only downside was flaky WiFi, but that improved on Saturday once all the phones, tablets and laptops from the conference had left for some reason…..
Another great year from Tobias and his team. They’ve announced the dates for next year, 4th-7th June 2019 and they’ve been written into the calendar with ink as I’ll definitely be heading back.
This year the SQL Relay committee has decided to make a couple of changes to our session selection procedure.
Our aim this year is to be as inclusive and transparent on our process as we can manage. To this end, this post is going to lay out our aims and how we plan to achieve them.
We want everyone to feel they can submit without worrying that their submission will be overlooked due to Gender, Disability, Availability to do the whole tour or any other reason you may be concerned about.
To do this we are moving to a blind selection process. I (Stuart) will be the only member of the committee who can see the list of submissions. At the end of the Call for Sessions I will release only the session abstracts to the rest of the committee members, who will then choose the sessions based only on the abstracts.
Then, should we have openings due to people’s availability we will go through again to fill in any holes in the agenda.
If you require any special requirements then please add them to your submission. They won’t be used during the selection process, but will allow us and our venues to ensure we cover them
The Call for Papers is available here – SQL Relay 2018 CfP
If you are worried about any other reasons you may feel that your submission may be overlooked and want to talk them through, then please contact me (comments, Contact Form or on Twitter (@napalmgram). Anything you ask will be treated in confidence.
If you would like some help with or an independent review your abstract then please get in touch with Speaking Mentors where some of the best SQL Session submitters can help you out.
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!
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
I’ve long been a proponent of automatically restoring as many of your SQL Server backups as you can. There are many benefits of doing this, amongst them:
Checking your backups are working, I’ve seen people with backups they can’t restore
Knowing how long it will take to recover, your boss isn’t going to be happy if they’re still waiting 3 hours after you said the business would be back up and running
Having logs of having restore them, Auditors love this sort of thing
Peace of mind knowing that your restores are going to work when the brown stuff hits the reciprocating draught machine.
I’ve been using a collection of scripts and functions based on my previous blogs and presentations on the subjects. But they’ve never really been in a state that I felt comfortable sharing with the world.