Jack Vamvas investigates an error when trying to connect to SQL Server 2016 on Windows Server 2016:
Question: I’ve upgraded an application with a built – in Database API . When attempting to establish a SQL Server database connection this error appears – Connection failed – SQL Server Error 772 – TCPIP Socket
Upon investigation the application was using the native drivers attempting to connect to a SQL Server 2016 \ Windows 2016
As part of the testing I downloaded the ODBC 13.1 SQL Server drivers – independent of the application and tested a DSN connection to the same SQL Server – and it connected OK. I then created a DSN with native drivers and the error reappeared.
What is going on ? How can I fix this issue?
Read on for the solution and keep those drivers up to date.
Monica Rathbun tries out resumable online index creation in SQL Server 2019:
SQL Server 2019 brings a very exciting new feature that, is long overdue. Resumable online index create is one of my favorite new things. This paired with the Resumable Index Rebuilds introduced with SQL Server 2017 really gives database administrators much more control over index processes.
Have you ever started to build a new index on very large table only to have users call and complain their process is hung, not completing, or system is slow? That’s when you realize you’re the cause because you tried to sneak in a new index. I have many times, because creating a new index can impact performance and can be a problematic process for users when you have no or little downtime windows available. When you kill the create process it rolls back requiring you to start from the beginning the next time. With resumable online index creation you now have the ability to pause and restart the build at the point it was paused. You can see where this can be very handy.
Click through for a demo and discussion on what options are available.
Marek Masko shows us how to read through the transaction log to understand a data change operation:
Another solution that can be used to track changes executed against your database is to read Transaction Log file or Transaction Log Backups. Transaction log file (and backups) consists of every transaction executed against your database. The only requirement for this is to have a database in the Full recovery model. In the Simple recovery model, every committed transaction can be very quickly overwritten by another one.
Also, this is something that you get for free without the need to enable and configure any additional SQL Server functionality. Of course, besides the database backups, but you already do database backups, right?
To test this approach, you have to make some preparations. You need to set the database’s recovery model to Full. As a first step, check database properties to ensure that it is appropriately configured. As a second step, you need to create a full database backup. From this point, the database is in Full recovery model, and every transaction is fully logged. Thanks to this you are able to read logged transactions from Transaction Log file. The same applies to read from Transaction Log backup. To do this, you need to create such a backup after you execute database schema changes.
I think I’ve only done this once or twice, but it’s a good technique to know about.
Grant Fritchey creates a plan guide and then forces the plan in Query Store:
If I look at the plan that is stored in Query Store, I’ll see the identical plan up above, including the PlanGuideDB and PlanGuideName properties.
So, let’s force the plan using the values returned from the query above:
Now, when we run the query, we’ll see both the plan guide in use and that the plan is forced (see this earlier blog post explaining this behavior). This is all expected behavior.
Check it out to see how SQL Server behaves.
Jeff Mlakar shares a few tips on hosting SQL Server via VMware virtual machine:
Why is Over-Allocating a VM Bad?
Why is is so important to allocate just the right amount of resources for your guest VM running SQL Server? We all know the problem with an undersized system; however, what about an oversized system? What problem could that be? Here are a few common issues to consider:
Over-allocating CPU causes poor resource utilization across all the guest VMs
Over-allocating memory unnecessarily increases memory contention and overhead on other guest VMs
Having more vCPUs assigned to the VM can have an impact on licensing.
There are some good points in the post, so check it out.
Kenneth Fisher walks us through the risk of increased Shadow IT with migration to the cloud:
Shadow IT has been, well, maybe not the bane of the IT department, but certainly a pain in the neck. On the off chance you’ve never heard of shadow IT do any of these sound familiar?
A user asks you to restore a corrupt database on a SQL Server you’ve never heard of and isn’t in your inventory. (And 50/50 odds there’s never been a backup taken.)
You do a licensing true-up and dozens of new SQL Servers suddenly show up.
You hear from a user: “We have this mission critical Access database that suddenly isn’t working. I know you don’t support access but you’re the database person so we need you to fix it.”
It’s an interesting short essay and worth thinking about if you’re in the cloud or moving that way.
Jason Brimhall walks us through installation of the SQL Server Profiler on Azure Data Studio:
Azure Data Studio (ADS) is getting all sorts of love and attention these days. So much so that they have finally gotten around to adding Extended Events (XE) to the tool – sort of. Now we have the power to run traces on SQL Server via ADS.
The presence of XE in ADS comes via an extension and comes with a few other caveats. I will explore the extension for XE available in ADS in this article and discuss some of the caveats. As you read the article, it might be helpful to go ahead and download ADS if you do not already have it.
Jason points out the name of SQL Server Profiler and I’d like to add my own bit of irritation here. “Don’t use Profiler, except the one good Profiler but not the Profiler you think you’re using unless you know not to use Profiler and use Profiler instead.” Yeah, that’s pretty clear.
Chrissy LeMaire has a few more breaking changes for us:
It’s been a busy couple days! Here’s a list of our breaking changes
Invoke-Sqlcmd2 has been removed and replaced with a warning to use Invoke-DbaQuery
The NetworkShare parameter has been renamed to SharedPath
UseLastBackups has been renamed to UseLastBackup
There are also several new commands as well, so check them out.
Frank Gill shows how to migrate a database from on-premises to an Azure SQL Managed Instance:
If you have run through my last Managed Instance blog post, you have a Managed Instance at your disposal. The PowerShell script for creating the network requirements also contains steps to create an Azure VM in a different subnet in the same VNet. Unless you have a site-to-site VPN or Express Route between your on-prem environment and Azure, you will use this VM to connect to your Managed Instance.
Install Management Studio on the Azure VM. To connect to your Managed Instance, you will need the host name for your Managed Instance. You can find the Managed Instance host name on the resource page for your Managed Instance in the Portal.
I think this migration story is a bit easier for DBAs than the old Azure SQL Database strategy of building dacpacs.
Greg Low shows how to have every Management Studio tab open in SQLCMD mode:
Note the :CONNECT command is used to connect to another server.
Because everything else works pretty much the same, and you get a whole lot of additional options, you might choose to open all your new queries in SQLCMD mode. That’s easy to do.
SQLCMD mode is one of those things where I thought I’d use it a lot, but aside from deploying database projects, I don’t. Granted, this could be a failure of imagination on my part.
Kevin Feasel
2018-11-29
Administration, Security