A Fine Slice Of SQL Server
Kevin Hill answers ja question about whether full backups break log shipping:
Keep on doing your full backups.
Make sure that any databases you Log Ship are NOT also doing log backups in your SQL Maintenance Plans, Ola Jobs, etc.
Click through for a video walkthrough on the topic.
Comments closedErik Darling has two posts of a similar theme. First up is that you might not need to offload reads:
Duplicating data for reporting, outside of moving it to a data warehouse where there’s some transformations involved, can be an expensive and perilous task.
Your options come down to a native solution like AGs, Replication, or Log Shipping. You can brew something up yourself that relies on native stuff too, like Change Data Capture, Change Tracking, Temporal Tables, or triggers.
Erik’s suggestion here is that appropriate query tuning (and I’ll add proper database design!) does more for you than scaling out.
Then, Erik takes it one step further and recommends against certain features in SQL Server:
Consulting gives you a lot of opportunities to talk to a lot of people and deal with interesting issues.
Recently it occurred to me that a lot of people seem to confer magic button status to a lot of things that always seem to be If-I-Could-Only-Do-This features that would solve all their problems, and similarly a Thing-That-Solved-One-Problem-Once turned into something that got used everywhere.
I do agree with Erik on partitioning (which makes administration easier but usually only helps with read queries on columnstore indexes with huge numbers of rows), In-Memory OLTP (which could have been an incredible feature if it worked as I’d hoped), dirty reads, and over-use of recompilation. For sufficiently busy environments, I disagree with Erik’s take on fill factor, having been convinced by Jeff Moden that there’s a lot of value in well-thought-out fill factor settings, but to make the most of it requires more knowledge of the data than DBAs typically take the time to learn.
Comments closedShivani Sarthi clues us in on Grafana alerts:
Grafana alerts are useful since they tell you about the problems in your system moments after they occur. Which in turn results in quickly resolving the issue. Also reducing the disruption time of the service.
Click through for an overview of functionality, where you can post alerts, and how to configure them.
Comments closedHow to organize workspaces in Power BI is one of those topics that comes up a lot. On one hand, it’s really easy to quickly create a workspace and keep moving. At the same time, it’s also really useful to have a strategy for how you scope your workspaces so they don’t get out of hand over time.
In this post & video we’re going to cover 4 sets of criteria to consider when planning for workspaces in the Power BI Service.
Click through for the video, as well as a post with the details.
Comments closedThomas Williams has built a documentation engine:
Sometimes there’s a need to create database documentation – an old fashioned list of tables, columns, views etc. – as part of delivering a project, or for analysts, project managers and end users.
In these situations I tend to automate. SQL Server is very “meta” in that it contains tables and records that describe, well, other tables and records. Coupled with the
MS_Descriptionextended property, it’s a powerful way to keep up-to-date documentation in close proximity to the code itself.The script below will generate Markdown for database tables, views, stored procedures and functions. Column names and data types are listed for tables. View definitions are output. For stored procedures and functions, only the
MS_Descriptionextended property will be output.
Click through for a big disclaimer and a bigger script.
Comments closedSteve Stedman & co don’t need your SQL Agents:
One of the feature differences between SQL Server Express Edition and the other paid versions is the ability to schedule jobs or tasks using the SQL Server Agent.
Even if you are running SQL Server Express Edition, there is a need to run scheduled jobs.
I’ve used this option and yes, it does work. But it’s definitely a few steps down from using SQL Agent jobs. Still, if that’s not available to you, this is a viable fallback option.
Comments closedAnthony Nocentino has some practices for us, the best practices:
The intent of this post is a quick reference guide based on the recommendation made on Pure Storage Support page in the Microsoft Platform Guide . The target audience for this blog post is for SQL Server DBAs introducing them to the most impactful configurations and settings for running SQL Server on physical machines on Pure Storage.
Click through for a checklist of recommendations.
Comments closedChad Callihan has a few close-to-one-liners:
The most common, short script I could think of may very well be one of the shortest T-SQL scripts that can be executed. When you want to know what version of SQL Server you are connected to, you can run this script:
Read on for that one-liner as well as a few other short scripts.
Comments closedAndy Mallon wants a database all of his own:
I’ve written about my DBA Database quite a few times. Some of the code in that database is written recently, and some of it was originally written a decade ago. Regardless of how old it is, or how good of a DBA I was when I wrote it, these little scripts are life-savers, and they are always at my fingertips.
And Andy has made them available to your fingertips as well. And the rest of your hands, I suppose.
Comments closedSo in the last blog we confirmed that we could move to SQL MI via some analysis, this is now time to actually do a backup and restore via URLs to move data.
Quite simply you need to BACKUP to URL (Azure Storage container) and the setup requirement is that you need to create a SQL credential that holds the SAS token – this is what allows authentication to the container to take place.
Click through for the process.
Comments closed