In another post I explored a way to run WMI queries from tsql (sort of). Let’s do it again. The script below enables xp_cmdshell, runs WMIC.exe (capturing the output to a temp table), shreds the XML, returns a result set of columns and rows, and disables xp_cmdshell. If you’re adventurous and daring, run the script in a test environment.
With Powershell, as Dave mentioned, this really isn’t the best way to do the job, but there are times when you aren’t able to use Powershell.
A fix has been published for SQL Server 2012 (SP2 and SP3), and one is coming for SQL Server 2014 (RTM and SP1) – though in this most recent update, SQL Server 2014 was removed from the list of affected versions (not sure it was accurate to remove those). I will update this space once the next 2014 CUs are released, but for now you should plan to rebuild tables after dropping columns as a matter of course and, more importantly, as part of the same maintenance operation.
This is a good reason to stay up to date on CUs.
Once the data has been encrypted, we can move forward with creating a new view that will be used to “head fake” the application. The view is named the same as the original table therefore the change is seamless to the application. The application doesn’t know if it’s calling a table or a view so that’s why this works.
You should never store passwords in plaintext. You should almost never store passwords in a reversable format (i.e., encrypted). The primary case in which I can see storing passwords encrypted rather hashed is if you have automated systems (or non-automated technicians) which need passwords to authenticate somewhere. Even then, there’s a lot of value in using OAuth tokens. But if you can’t get around any of this, John’s solution does remove the really bad decision of leaving passwords in plaintext.
Much of the time there is a systems team and a DBA team, and when the DBAs need to build out a new set of SQL Servers, they request X number of virtual servers from the systems team. The servers are handed over and the DBA team works its magic, and then we have our Failover Cluster Instance or Availability Group High Availability solution. But, is it really Highly Available?
The reason I ask is twofold:
- Which physical hosts are your Virtual Machines are located on?
- Which data stores are your virtual disks are located in?
If the answer to either of these questions results in the same answer for any of your Virtual Machines in an Availability Group, or Failover Cluster Instance for that matter. Then you potentially have a massive flaw in your implementation that can affect availability.
The moral of the story is to communicate with the network administrators and SAN folks.
If you’ve been paying attention, you’ll have noticed that I’ve done the rownumbering in reverse order, and added a dummy (RowNum 0) field at the top of the list – this is to make sure that, if the most recent record is a RESOURCE_MEMPHYSICAL_LOW record, that we can get results that include that value.
This all looks OK in theory. But we’re still getting stupidly high values for the SecondsPressure field, and wait – what’s this? Multiple ring buffer records with the same ID?
More importantly, he shows us how bad the situation is: is this something that happened for a couple of seconds, or is it persistent? This is a great walkthrough.
The DAC, what is it? It is the Dedicated Administrator Console. Basically it is a way to connect to your SQL Server when all the other connections are tied up. But it takes a little bit of pre-planning so that you can use it when things go bad with your SQL Server. Let’s enable it so you can test using it and know that it is there in the future.
This is your “get out of jail free” card when the instance is completely unresponsive. A small amount of memory is dedicated to the DAC so that even if anything else is locked up, you have a chance to fix the problem short of a reboot.
For this example, let’s assume that we have a physical server with 512 Gigs of RAM and two physical NUMA nodes (and two CPU sockets). We have a VM running in that machine which has a low CPU requirement, but a large working set. Because of this we have 4 cores and 360 Gigs of RAM presented to the VM.
The answer is not trivial, making this an interesting question.
The database in question wasn’t stuck in recovery, it would slip in and out of the status throughout the day. Normally, I would only ever expect to see a database in recovery during a restore or after a service restart. Once recovery is complete, I would not expect to see the database slip into ‘in recovery’ again. I especially wouldn’t expect a database to keep slipping in and out of recovery.
The answer is a true head-slapper. Whose head, I’ll leave up to you…
That diagram provides you about 260 last minutes of Server CPU usage and measured in percents.
As you can see my SQL Server is mostly doing nothing and only during that blog-post writing in the last half and hour or so it is heavily running test scripts to over-utilize my CPU, but it still barely goes more than 60% of CPU (Blue line).
The Red line represents all other processes besides of SQL Server and you can tell if anything else from outside is impacting your performance.
Combined with Glenn Berry’s diagnostic queries, you could generate some quick analytics. I’d still use R for anything more than slightly complicated, but this is great for those environments in which you don’t have good alternative tooling.
We are currently working on testing and publishing SQL Server Container Images that could speed up the process of getting started with SQL Server in Windows Containers significantly. Stay tuned for an update!
Windows getting into the Docker world is interesting.