Emanuele Meazzo has updated the SQL Server Diagnostic Notebook:
Good news everyone!
As always, I’ve updated the SQL Server Diagnostic Notebook to include the latest updates to the source scripts
Click through for the link.
Comments closedCategory: Administration
Jamie Wick walks us through SQL Server database snapshots:
A SQL Server database snapshot is a read-only view of what the data pages in the source database looked like, at the time that the snapshot was created. Typically, snapshots are used to provide a point-in-time view of the database (for reporting or auditing purposes) or to allow for quick reversions during database upgrades/modifications. Since the snapshot only contains information on which values have changed, and what they were originally, it’s usually faster to revert the snapshot than having to restore the entire database from backup.
Click through for info on how they work as well as how they perform. I have used database snapshots to great effect in the past when testing changes in development environments back before the days of containers.
Comments closedPam Mooney shows how you can discover and document limits for a SQL Server instance:
Having taken steps to map your database applications to the databases and address your security and backups, you need to turn your attention to your server’s limits.
What do I mean by limits? Certainly, this is an allusion to how you will monitor your server drive capacity, but I also mean how you will track resource limits, such as latency, CPU, memory, and wait stats. Understanding all of these terms, what normal values are for your server, and what to do to help if the values are not normal, will help to keep your servers as healthy as possible.
These measures are big, in-depth topics in and of themselves. This will only serve to get you started. Links to more in-depth resources are included with each topic, and you will doubtless find others as you progress through your career.
Click through for the process.
Comments closedLee Markup walks us through an issue:
In my own local SQL Server I ran across a problem starting the SQL instance. I went to SQL Server configuration Manager and manually started the SQL Server instance. The UI showed the instance had started. I opened SSMS and tried to connect. And I waited, waited and waited some more until it didn’t connect and threw an error.
Read on to see how Lee was able to find and correct the issue without actually being able to start up SQL Server.
Comments closedAndreas Wolter shows how we can implement a log of object changes with DDL triggers:
Over the years working on customer systems, I personally found it to be invaluable and as best practice equipped any database that I designed with such a small trigger and DDL-log-table, just in case. It has helped many times to quickly solve issues with deployments scripts, non-scripted changes to the systems, problems with Source Control and simply getting answers quickly.
The concept is almost trivial and because DDL changes are usually not in performance-critical code-paths, the theoretical overhead on the DDL statement-runtimes is not relevant. (Unless frequent schema-changes are part of a performance-sensitive workload – in which case I would then question if using DDL is a good idea at all in such a place. Note that temporary tables are not caught by DDL Triggers.)
Click through for more information, as well as a sample script.
Comments closedJess Pomfret takes us through running dbachecks on an Azure SQL Database:
Last week I gave a presentation at Data South West on dbachecks and dbatools. One of the questions I got was whether you could run dbachecks against Azure SQL Databases, to which I had no idea. I always try to be prepared for potential questions that might come up, but I had only been thinking about on-premises environments and hadn’t even considered the cloud. The benefit is this gives me a great topic for a blog post.
Click through for the answer.
Comments closedThis is one of them little options that I see which quite often gets little consideration or gets set to a user database without consideration of what the consequences may be if that DB becomes unavailable. There are going to be situations where setting a default other than master is essential and there are going to be situations where leaving as master suits best and this comes down to the individual requirements of each login, Recently I had to fix an issue with user connectivity for a single login, the user was getting failed connections when trying to connect to the SQL server when trying to access one of their legacy databases , everything appeared fine – User account was enabled the password hadn’t been changed and was therefore correct, the database they were trying to access was up and accessible but the SQL error log highlighted the real issue.
Click through for more details.
Comments closedJulie Koesmarno shows off the Kusto Query Language magic in Azure Data Studio notebooks:
To do this, you’ll need to ensure that you have Kqlmagic installed. See Install and set up Kqlmagic in a notebook. Then in a notebook, you can load Kqlmagic with
%reload_ext Kqlmagicin a code cell.The next step is then in a new code cell, you can start connecting to a Log Analytics workspace. There are three ways to do so (roughly – as I’m also learning in this space too):
1. Using Azure Active Directory Device Login authentication.
2. Using Az CLI login
3. Using Client Secret
Read on for one example using Azure AD authentication.
Comments closedJulie Behrens, via Kevin Hill, covers the concept of virtual log files:
It is especially evident there is an issue with VLFs when SQL Server takes a long time to recover from a restart. Other symptoms may be slowness with autogrowth, log shipping, replication, and general transactional slowness. Anything that touches the log file, in other words.
Read on to understand how to figure out if you have a problem with virtual log file counts and a resolution.
Comments closedAndrew Pruski contrasts techniques:
What is the difference between using a load balanced service and an ingress to access applications in Kubernetes?
Basically, they achieve the same thing. Being able to access an application that’s running in Kubernetes from outside of the cluster, but there are differences!
Read on to learn about the key differences between these two.
Comments closed