Press "Enter" to skip to content

Category: Administration

Scheduling Tasks in SQL Server Express Edition

Steve 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 closed

Best Practices for SQL Server on Physical Machines

Anthony 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 closed

Building a DBA Database

Andy 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 closed

Performing a Restore to SQL Managed Instance

Arun Sirpal shows us how to perform a backup and restoration from an on-premises SQL Server to Azure SQL Managed Instance:

So 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

Scaling Out vs Scaling Up

Jordan Braiuka compares two models for scaling:

We often get questions from customers about the best way to add capacity to their cluster. Is it better to add nodes, or simply to increase the capacity in their nodes? Unfortunately, the truth is there is no best way—like all complex issues in distributed systems, there are benefits and drawbacks to each scaling approach. 

While each of our highly distributed systems (Apache CassandraApache Kafka, etc.) have slightly different implementations of scaling, the concepts remain consistent across most distributed systems. 

Click through for a comparison between the two approaches. As the article indicates, both are meaningful strategies and your choice might come down to a combination of the technology stack and the problem at hand.

Comments closed

Testing sp_ineachdb

Aaron Bertrand takes us to the Island of Misfit Databases:

The only database that requires extra handling is the one that contains a tab, because SQL Server doesn’t know how to generate file names when that character is present. I am sure there are a bunch of other less common but equally exotic characters that may cause the same problem.

This is how I actually tested sp_ineachdb, to make sure it was ready for just about any bad idea anyone used to name a database, and could handle various possible database states (for a lot more background on this procedure, and why it is better than the undocumented, unsupported, and buggy sp_msforeachdb, see this and this). Here you can see that the procedure works against all these poorly-named databases, and skips databases that are inaccessible (rather than raise an exception).

Click through to see the list of databases Aaron uses. Technically, I think Aaron’s blog post also counts as a Halloween post because some of those databases are spooky.

Comments closed

Compatibility Mode and Upgrades

Tom Collins explains how database compatibility mode may be a way to reduce the risk of a SQL Server version upgrade:

Microsoft’s recommended SQL Server upgrade  workflow is to upgrade to the latest SQL Server but keep the source DB compatibility level, assess the workload impact via establishing a baseline and based on testing move the compatibility level  to the latest. 

Upon creation of a new user database – the create  database sets the compatibility level at the default compatibility level of the SQL Server. Keep in mind – if the model database level is set lower than the create database will set the compatibility level based on the model db

You can also change the database compatibility level at any time

Read on for more information about compatibility mode and some inbuilt guard rails around upgrades. Those guard rails aren’t perfect by any means, but over the past couple of editions, we’ve seen a fair amount of movement toward this ideal of compatibility mode being a guarantee of behavior between versions.

Comments closed

Measuring File Latency in SQL Server

Anthony Nocentino has a script and some tips for us:

This post is a reference post for retrieving IO statistics for data and log files in SQL Server. We’ll look at where we can find IO statistics in SQL Server, query it to produce meaningful metrics, and discuss some key points when interpreting this data.

Click through for the script, and then a bulleted list of things to keep in mind as you’re reviewing the data.

Comments closed

SQL Server Express Memory Limitations

Steve Stedman notes that the memory limitations on SQL Server Express Edition are not quite as stringent as you may first believe:

Looking at the memory limits and other limits on the SQL Server versions over time, we have seen things increase, but one limit that is still very low is the memory limit for SQL Express. Specifically the maximum memory for buffer pool per instance of SQL Server Database Engine for SQL 2019. The limit there is 1410 MB.

At first glance you may think that this limit is the total amount of memory that SQL Server will use, but let me show you a couple of screen shots for Database Health Monitor showing the memory utilization on two different SQL 2019 Express servers.

Read on to see what, exactly, the memory limitation is. Also, there are separate limits for things like In-Memory OLTP table sizes.

Comments closed