A Fine Slice Of SQL Server
Erik Darling looks at a new DMV in vNext CTP 1.1:
It’s not exactly perfect
For instance, if you just let it loose without filters, you get a severe error. The same thing happens if you try to filter on one of the columns in the function, rather than a column in sys.stats, like this.
Very cool. It’s one step closer to us removing our dependencies on DBCC SHOW_STATISTICS.
Comments closedKendra Little gives thoughts on getting to zero-downtime releases:
I’ve worked in a bunch of environments where we rolled schema changes to production throughout the week, and worked hard to limit downtime. Our biggest challenges were:
The code released wouldn’t have the intended effect, and multiple hotfixes would sometimes need to be written quickly if the issue was urgent.
Problems would happen with replication in production – either replication would be delayed because a lot of modifications were occurring, or a change in an “upstream” system would cause a huge amount of data processing to happen in a “downstream” database
I’m in a ZDT environment. There’s a pretty good amount of overhead work that goes into getting there; there are times in which a 20 minute window would have saved hours of development time, so it’s important to keep that trade-off in mind.
Comments closedRK Kuppala explains how to integrate a Hadoop cluster with Active Directory:
This post explains kerberizing an existing Hadoop cluster using Ambari. Kerberos helps with the
Authenticationpart of enterprise security (whileauthorization,auditinganddata protectionbeing the remaining parts).HDP uses Kerberos, which is an industry standard for authenticate users and resources and providing strong identity for users. Apache Ambari can kerberize an existing cluster by using an existing MIT key distribution center (KDC) or Microsoft’s Active Directory.
This was a lot easier than I expected.
Comments closedDavid Alcock has a script to create SQL Agent alerts for common errors:
These alerts cover a range of errors from potential IO subsystem problems to failed logins, all of which are things a DBA needs to know about, and quickly too.As well as error notifications you can set up alerts to cover performance conditions. The final statement in the script below sets up an alert that triggers when Page Life Expectancy drops below 1000. In all honesty I don’t set up these performance alerts that often but I wanted to show you the kind of thing that is possible and would be handy if you don’t have any third party monitoring.
He follows this up with a post on appropriate response:
But what do I mean by sensible? Typically I see a number of problems with alerting setups; either alerts are inadequate and don’t cover the necessary errors (or there are none at all) but I also see the notifications to alerts not being set up correctly meaning problems go backwards and forwards delaying any fixes.The other problem I see is an over provision of alerts. This usually is because one or more other monitoring systems have been deployed and error notifications have been duplicated as a result. Imagine having an operational tool like System Centre, some SQL monitoring software and native alerting all pinging the same message to the one recipient mailbox. Now on top of that let’s say the alerts have not been configured correctly so information emails are being issued every second. It’s a scary thought but it is easy to see how a critical error might be missed in this scenario.
If you don’t have automatic alerts for high-severity errors, this is an easy way of gaining insight into the problems your server is experiencing.
Comments closedKevin Hill gets into Powershell:
What follows is a step by step of how I expanded on the most basic command to get more and more out of it. I spent a lot of time on MSDN looking up cmdlets, and on Stack Overflow twice with basic questions. Even went to my “DBA” Twitter feed early in the process using #sqlhelp since a lot of my colleagues are veteran PoSH users.
Warning…its very possible I’ve gained a syntax error here and there when copying and formatting this into a post…run at your own peril 😉
One bit I’d recommend is using Out-GridView for occasional display and potentially getting rid of the Excel requirement.
1 CommentAndrew Peterson shows how to start, stop, and restart the SQL Server service on Linux:
Start Service
sudo systemctl start mssql-server
He also shows how to do a status check. This is for distributions which use systemd, which includes the Red Hat distribution set (Fedora, CentOS, Red Hat Enterprise). If you’re on Ubuntu, there’s no support quite yet, but you can use start and stop.
Comments closedKenneth Fisher shows that the Data Migration Assistant developers thought ahead:
I’ve been really excited about the new Data Migration Assistant (DMA) since I first heard about it. One of the things I like best about it is that unlike the old Upgrade Advisor it doesn’t have to be run on the server being upgraded. You can run it against any number of instances from a single workstation. The other day I was working from home and tried running the DMA against a couple of moderate size databases (about 1.25tb total) and I consistently got timeout errors.
Click through for the solution. I’d prefer “have the queries be quick enough not to require this change” be the solution, but I don’t know exactly which queries they’re using, and some DMVs/DMFs can be quite slow.
Comments closedPeter Schott extends a script to clean up the SSIS catalog database:
I really appreciate what MS has done w/ the SSIS Catalog. We have built-in logging at a level that wasn’t possible in prior releases, but that comes at a cost. The default retention is pretty high (365 days) and MS chose to handle cleanup using cascading deletes. This sort of coding makes life easier for the developers, but almost always performs poorly. That’s especially the case when you have 1 parent row with 100’s of thousands or child rows related to it. The problem is compounded because the first time people realize they need to lower the retention limit is about the same time that the database is starting to fill up or has filled up. At that point, it’s too late to lower the retention by a large number because the corresponding delete will still cause issues.
Click through for a script which helps extricate you from sticky situations. The ideal scenario here would be to set your retention period correctly and not have to delete rows directly, but sometimes you’re stuck in a less-than-ideal situation.
Comments closedMax Trinidad shows that Powershell remoting works for connecting Linux to Windows and vice versa:
Connecting Linux to Windows
This is strange but, more likely, you will get an error the first time you try to connect. Just try the second try and it will work.
I need to see what Powershell objects for Linux currently exist; my guess is “not many, if any” but as those start getting fleshed out, I think even the most adamant of grep-sed-awk users will want to pick up at least a little bit of Powershell.
Comments closedErin Stellato walks through capacity planning:
Capacity planning for a new solution is really tricky. You have to come up with estimates about workload based on information you collect from the business. This means you have to ask hard questions about how much data they will expect in the first month, the first six months, and the first year. When a new solution is coming in, this is often the LAST thing the business is thinking about, so very often you’re going to get vague answers. In the case of a new solution, you really have make a best guess effort. Don’t pull your hair out trying to get exact numbers.
If the solution is from a vendor, you must ask the vendor for planning recommendations about both space needed and the resources needed. I admit, they may not have that data, but you don’t get what you don’t ask for. It never hurts to try.
Click through for Erin’s discussion points across hardware and storage requirements over time.
Comments closed