A Fine Slice Of SQL Server
Tom Collins enumerates several reasons for a transaction log backup chain breaking:
The SQL Server transaction log backup chain aka log chain is the series of sequential transaction log backups related to a database. The log backups are related to each other and are represented through LSN . Breaking the transaction log chain will limit the restore point of the backups.
Click through for four such reasons as well as a scenario explaining how it could happen.
Comments closedPaul Randal explains how multi-version concurrency control works in Postgres:
PostgreSQL uses an optimistic isolation system known as Multi-Version Concurrency Control (MVCC). MVCC ensures transactions writing data to the database don’t block concurrent transactions needing to read the data being modified. This works through the magic of row-versioning—PostgreSQL creates versions of rows in the database tables to minimize blocking from concurrent access. As more and more versions are generated, a garbage control mechanism called VACUUM must be used to ensure the tables are properly maintained. In this article, I’ll explain how all this works via a series of examples.
This is quite similar to Read Committed Snapshot Isolation in SQL Server but with a couple of twists, including the need to vacuum tuples.
Comments closedErik Darling has some recommendations:
This is a short post to warn you about the memory recommendation tab in the SQL Server installer.
Let’s say you’re doing the smart thing and giving your Standard Edition install 192 GB of RAM:
Read on to understand why a max memory size of 128 GB isn’t necessarily the right answer for Standard Edition but also how you might set it that way by Next-Next-Nexting.
Comments closedGarry Bargsley performs a migration:
Good evening. Today’s episode is coming to you from my home office, where I feel motivated to write a blog in the comfort of my home.
Today we will discuss migrating SSRS from one instance of SQL Server to another. Several methods are available for you, but I will show you how to backup and restore the Reporting Services components for a full migration.
Read on for the process and pay special attention to Garry’s notes about encryption key handling.
Comments closedDennes Torres saves some cash:
The Auto-Shutdown policy is another important policy to ensure our virtual machines don’t expend more than what we planned for them. If we have a time window to use the virtual machines, the auto-shutdown policy can deactivate them at the right time.
We need to discover the deep internal details about the auto-shutdown configuration before creating the policy. The method we can use is to set this configuration and export the virtual machine as a template. We change the configuration to on and off, export and check the difference.
This can be kind of annoying when you’re working late—though you can delay auto-shutdown pretty easily. If you’re the type of person to forget turning off cloud resources when not in use, this is one way to prevent an unexpectedly large bill.
Comments closedKellyn Pot’vin-Gorman has a tough talk:
If we were to ask any DBA to separate the database in one cloud and the application tier in another without the context of a marketing announcement, they would look at us like we’d grown a third head. I’m incredibly surprised that anyone even considers the OCI Interconnect for this use, let alone the 150 that are currently using it. Oracle applications, like E-business Suite, Peoplesoft, JD Edwards and Hyperion are incredibly network latency sensitive and to recommend separating their tiers in two separate clouds just is alien to me. When we deploy these in Azure, we place all tiers in a proximity placement group to let Azure know that they are connected and this ensures that when a resource comes online after changes are made, redeployments, etc. the resources stay close to each other.
Definitely worth a read.
Comments closedLee Markup explains why you want to pay attention during SQL Server installations:
There’s one sure-fire way to set yourself and your applications up for failure when using SQL Server. I’ve seen all kinds of people do this.
It will cost you performance and create maintenance headaches down the road.
Let me explain what it is and why it causes trouble.
Click through for some of the specific problems you might run into.
Comments closedErik Darling continues to be busy:
I haven’t run into a bug with this thing in just about… Well, ever. This proc has been pretty solid.
As I use it though, especially in front of clients, I find myself wanting some additional data points all in one place. With this release, I’ve added a few small things.
Click through for the list of updates.
Comments closedJason Bouska has a big announcement:
Microsoft Azure SQL Database is a fully managed cloud database (PaaS) that handles many database management tasks without user intervention. Tasks such as patching, upgrading, taking backups, and monitoring can be configured to the specific needs of the workload and are performed in the background. Azure SQL Database runs the latest stable version of SQL Server and patched OS with 99.99% availability. The intelligent automated functions built into the database free up the user to focus on other important tasks.
Today I am introducing the Azure Well-Architected Service Guide for Azure SQL Database. Like other service guides, this guide for Azure SQL Database contains design considerations, checklists, and detailed configuration recommendations that can assist cloud architects in deploying optimal Azure SQL workloads in line with the guiding tenets of the Well-Architected Framework: security, reliability, cost management, performance efficiency, and operational excellence.
I’ve found that the Well-Architected Framework (whose overloaded acronym is still annoying) works best once you’re far enough along that you have a good idea of workload characteristics, meaning it’s not for the pre-planning state. Also, a full review might take hours or days and require several people to complete, not just a DBA.
Comments closedTracy Boggiano continues a series on starting a new job as a DBA:
I mentioned in my New Database Job – The 90 Day Plan blog how I have a trick for storing index usage stats up until close to the next reboot of the SQL Server. You really can do this for any DMV related query that you gets reset at the reboot of a SQL Server instance. With this I am able to have data of the most the current index usage stats up to the midnight before a reboot of my SQL instances to analyze.
This is a good one to store, as is the output of sp_WhoIsActive.
Comments closed