Wayne Sheffield shows how to read and modify registry entries using SQL Server:
xp_instance_regread
In this example, I used xp_regread to read the direct registry path. If you remember from earlier, there are SQL Server instance-aware versions of each registry procedure. A comparable statement using the instance-aware procedure would be:
This statement returns the exact same information. Let’s look at the difference between these – in the first query, the registry path is the exact registry path needed, and it includes “\Microsoft SQL Server\MSSQL12.SQL2014\”. In the latter query, this string is replaced with “\MSSQLSERVER\”. Since the latter function is instance aware, it replaces the “MSSQLSERVER” with the exact registry path necessary for this instance of SQL Server. Pretty neat, isn’t it? This allows you to have a script that will run properly regardless of the instance that it is being run on. The rest of the examples in this post will utilize the instance-aware procedures to make it easier for you to follow along and run these yourself.
Sometimes you just have to change something in the registry from SQL Server. Hopefully that “sometimes” is rare.
Arun Sirpal advises you to enjoy the change in control when moving to Azure:
The key for me has been to “embrace the change”. I have come from a traditional DBA background, backups, consistency checks, server level configuration at the heart of things. Yes, I no longer care about SQL backups and things of that nature when operating within Azure but I have different tasks and to think about.
Sceptical? Please read on.
Do read on.
In a perfect world, your SQL Server would be so secure that you would not need antivirus software, you would have behind layers of firewalls, nobody would ever connect with remote desktop to install anything, and it would always have all of the latest security patches… But that is not the real world.
Given that your SQL Server often times contains extremely valuable information, and that the damage that could be done by virus software, malware, and ransomware could be so great then it is strongly recommended that you run antivirus software on your SQL Server. There are some files that you will want to exclude from the virus check.
I’m not a big fan of running anti-virus software on database instances, but if you have to run it for whatever reason, be sure to check out Steve’s advice.
Kellyn Pot’Vin-Gorman gives a bit of DevOps 101 to database administrators:
Monitoring changes a bit with DevOps. It’s less about a simple tier and moves to the entire infrastructure. A need to monitor application, host, database and availability between each is essential. As these different tiers rarely come from one vendor and many may even be proprietary, there are requirements to monitor using multiple tools, scripts and interfaces.
Two of the main products for monitoring, recognized in the DevOps community are New Relic and AppDynamics. Monitoring can be as simple as a suite of scripts that report the health and status of processes and orchestration, notifying if there is any failure. This choice normally has a scaling limit and at some point, a more robust solution is required or gaps are felt in the monitoring process or failure at certain tiers. More enterprise solutions, such as New Relic and AppDynamics and enhanced by logging suites like Splunk and Sumo Logic.
Read the whole thing.
You can configure Spot instances in Cloudera Director’s instance templates. These instance templates contain a flag indicating whether Spot instances should be used, as well as a field specifying the bid price for those instances.
Each instance group in the cluster template includes a field that indicates the minimum number of instances required in that group for the cluster to be considered successful. Cloudera Director will continue with bootstrapping or growing a cluster if the minimum count for each instance group is satisfied. Spot instances should not be used for instance groups that are required for the normal operation of the cluster, such as HDFS DataNodes. Instance groups configured to use Spot instances should set their minimum number to zero with the expectation that the instances may not be provisioned due to the Spot bid price being lower than the Spot price.
If you’re able to take advantage of spot instances, you can end up saving a pretty good amount of money.
Paul Codding tells us what’s coming in the next version of Ambari:
Ambari Log Search (Tech Preview) has been one of our most popular features, and in this release has seen UI, and backend refreshes based on customer feedback. Log Search is planned for GA with the next major Ambari release, Ambari 3.0 in which the UI will be simplified, and the backend will have more robust log retention and scaling capabilities.
There are some interesting changes, so read the whole thing.
You are probably reading this post because you have experienced the pain yourself and I dont want to waste anymore of your time and get right to the steps involved in breaking up a huge database or a datafile
Check the space situation on your host
Get the space usage by files for the big database/datafile in question
Decide on number of files to add/location
Add multiple secondary datafiles
Distribute data from big datafile into the new datafiles using EMPTYFILE option
Shrink the big datafile and set a maximum size
Change the default filegroup
Read on for Jana’s step-by-step approach.
Nate Johnson has a script he uses to automate installation of SQL Server on a new server:
We can then use this file in the
ConfigurationFileargument ofsetup.exefrom the SQL Server install media. To put a little more color on that: the.inifile is really just a collection of command-line arguments tosetup.exe; you could also list them all out in-line, but that would be tedious and silly. Here’s a couple major selling points of creating your own config file:
Slipstream updates (SP’s, CU’s), instead of having it go out to MSFT update servers (or *aghast* sticking with the original RTM bits, you heathen you!)
Specify drive letters / default file locations: sure, this may be considered old-hat if you’re running super slick storage, but I still find it makes management a bit easier if I know where my MDFs, LDFs, TempDB, & backups will always be.
Take advantage of 2016’s better TempDB setup options (# files, size & growth)
Read the whole thing.
Jeff Mlakar walks through some advanced MSDTC troubleshooting:
Sometimes when a VM is cloned from a template (VMware or Hyper-V) the CID can be duplicated between the two machines. Evidence of this can be seen in the Event Viewer (Event ID 4101). This means the two MSDTC services will not be able to communicate with each other.
A possible error message is:
The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction
The MSDTC feature of the Windows operating system requires unique CID values to ensure that MSDTC functionality between computers works correctly. Disk duplicate images of Windows installations must have unique CID values or MSDTC functionality may be impaired. This can occur when using virtual hard disks to deploy an operating system to a virtual machine.
This burned me in the past. Jeff has several scenarios that he walks us through, so if you’re using the Distributed Transaction Coordinator, definitely check this out.
Steve Jones troubleshoots an issue with Azure SQL Database:
I’ve had most builds work really well. I tried a number of things, but kept getting a few items in the build. There were login errors or network errors, both of which bothered me since I could manually log in with SSMS from the same machine as my build agent.
I suspected a few things here, one of which was the use of named pipes for the Shadow database and TCP for Azure SQL Database.
Eventually, I decided to fall back with msbuild, ignoring VSTS, and make sure all my parameters were correct.
Read on for the rest of the story.
Kevin Feasel
2017-08-17
Administration
No Comment