Background My colleague and I were recently looking for ways to improve our production Availabiity Group nodes. These are beefy machines with a large amount of memory, and things like buffer cache scans can be lengthy. In his research, my teammate came across discussions on best practices for the Target Recovery Time setting of databases.... Continue Reading →
Getting Started As A SQL Server Professional
I'll never forget when I first started working with SQL Server. After the initial excitement of diving into something new I launched SSMS and then just sat there looking at a blank query window, with no idea what to do next. I see a lot of questions online regarding how to get started as a... Continue Reading →
SQL Server “Failed to open loopback connection”
TLDR: If you have the SQL Browser Service disabled then you must also have Shared Memory enabled, otherwise SQL Server can't communicate with itself. While setting up a sandbox Always On Availability Group cluster recently I ran into some unexpected behavior. The first sign of problems was that SQL Agent would not start. The service... Continue Reading →
Installing SQL Server Using Desired State Configuration
(Update: I've since discovered that SqlServerDsc has replaced xSQLServer.) One of my growing passions is using PowerShell Desired State Configuration (DSC) to automate all the things. I started out with simple configurations for testing but wanted to dive into more complex\useful situations for my day-to-day DBA life. To be honest, I was intimidated by the... Continue Reading →
Dynamics CRM Install\Import “The SQL Server {sqlserver_name} is unavailable”
My apologies for the significant gap between my last post on our CRM 2016 Upgrade Adventure and this one. My time has been consumed with preparing for go-live, but I've been keeping track of the roadblocks and caveats we encounter as I go so that I can post about them at later dates. One of... Continue Reading →
Dynamics CRM Import Fails on “Upgrade Indexes”
As I mentioned in the last post, I'm taking you through our adventure in upgrading the existing on-premise Dynamics CRM 2011 environment to 2016 (and eventually 2016 cloud). Previously I discussed the first show-stopper error we received, "Must declare the scalar variable “@table”." Following that resolution the import continued past the stage “Metadata xml upgrade: pass... Continue Reading →
Dynamics CRM Import Error “Must declare the scalar variable “@table”
This post is the first in a new series I'm going to call "CRM 2016 Upgrade Adventure". Summary: my organization has taken on the ambitious challenge of not only upgrading our existing Dynamics CRM 2011 environment to the 2016 version but of moving it to the cloud service as well. Aside from getting the vanilla components... Continue Reading →
Using Powershell to Refresh a Test SQL Server Instance from Production
A project I've been wanting to work on for a long time is how to automate restores of our Production databases to the Test instance. There are a number of challenges associated with this. First, the restore has to be able to find which backup it needs to use. Secondly, many of our databases do... Continue Reading →
Using PowerShell to Perform Availability Group Failovers
In the past we've explored how to use PowerShell to automate failovers on SQL Failover Clusters for Windows Updates and other scheduled maintenance. But what if you're using Availability Groups instead of the traditional cluster? Fear not, there is still a PowerShell option for that. Usually I would have our night team use SSMS to fail... Continue Reading →
Fixing Orphaned SQL Users via PowerShell
In SQL Server, a login is an instance level object that is used for authentication. It is mapped to a database user, which controls permissions at the database level. These objects (login and user) are tied to one another via a SID. If the login is deleted and then recreated, or if you restore your... Continue Reading →