Monday, 18 September 2017

Master Data Services Model Cloning

Yes! My company uses Master Data Services or MDS. Yes! It's a pain. And yes! I needed to learn how to use and administer it. For anyone that hasn't had this well meant tool forced upon on them, it feels ... (if I'm being kind) ... a little unfinished.

Based on my experience MDS administration requires an awkward mix of command line tools, the MDS web interface, and even some sql scripts and stored procs executed directly on the database. There are a few strange quirks, like the fact that individual users or groups can be set as administrators, but there is a single super-user. This super-user can only be an individual and not an AD group (although I'll be happy to be proved wrong on this!). Users in the System Administration role can deploy models, but still need to be given permissions to view and edit those models by the super-user (this has changed in SQL 2016 - multiple super-users are supported as well as AD groups are now supported as super-users).

Friday, 4 August 2017

Check for SQL Server Connectivity

My company is revamping its domain structure and tightening up network security. I am one of the guinea pigs and therefore have been put into the new domain and OU groups. Obviously, we are experiencing some teething problems. As a result I can't access things, namely SQL Server instances that I used to be able to connect to from my work station.

I needed to provide the network team a list of the instances I could no longer access and I certainly wasn't going to try to connect to each one manually. We have a very large estate and it would have taken me ages.

Friday, 28 July 2017

Cleanup SQL Server Logins

Cleaning up after someone leaves. We all do it right? Our companies all have water tight policies and procedures to handle that, or maybe they have policies but not procedures. Or maybe they just kind of don't.

Well, if your company is anything like most of the places I've worked, it's somewhere in the middle. And from a DBA perspective, especially on non-production environments, your list of logins and users becomes a bit long and messy after a while.

Friday, 5 May 2017

Compress your database tables

SQL Server has offered data compression since SQL 2008. It is a very good feature but, as with almost everything SQL Server, it needs to be deployed judiciously. Data compression has been written about and reviewed by many SQL experts who are much more qualified and knowledgeable than me, so I won't go into it too much.

First, a quick summary of what the feature does as well as its benefits and effects:
As the feature's name suggests, when it is enabled on a table and/or its index(es), SQL server compresses (or shrinks) the data before it is written to the database. There are two types: ROW and PAGE.