A Fine Slice Of SQL Server
Chad Callihan simulates integer overflow:
If we try to add one more record, we’ll get an error:
Msg 8115, Level 16, State 1, Line 14
Arithmetic overflow error converting IDENTITY to data type int.
Read on for two solutions to the problem.
Comments closedArun Sirpal takes us through a fairly new feature in Azure SQL Database:
There is a new (ish) interface to looking and configuring backups for your Azure SQL Database. This can be found within the settings section of the SQL Server.
As you can see, by default we have 7 days retention to allow for PITR – Point In Time Recovery, anything longer you will need to setup long term retention.
Click through to see how to set this up.
Comments closedJulia Evans gives us an introduction to Firecracker:
Firecracker says this about performance in their specification:
It takes <= 125 ms to go from receiving the Firecracker InstanceStart API call to the start of the Linux guest user-space /sbin/init process.
So far I’ve been using Firecracker to start relatively large VMs – Ubuntu VMs running systemd as an init system – and it takes maybe 2-3 seconds for them to boot. I haven’t been measuring that closely because honestly 5 seconds is fast enough and I don’t mind too much about an extra 200ms either way.
That’s pretty fast. Click through for more info on installation and configuration.
Comments closedChad Callihan ran into an error creating a new database:
A tool that restores a model type database and does a bit of configuration work was failing. I took a look at the stores procedures and started to go step by step. It didn’t take long before getting this error message when attempting to restore/create a database:
Msg 5184, Level 16, State 2, Line 3
Cannot use file ‘D:\sql_log\CC_Test_name_4.ldf’ for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it.
Click through for the solution.
Comments closedJamie Wick takes us through a lengthy process:
SQL Server has been supported on several Linux distributions for a couple of years now. For some people, the primary stumbling block to implementing SQL Server on Linux is the need to retain Active Directory (ie Windows-based) authentication for their database users and applications. Below we’ll go over how to join a Linux server (Ubuntu release 20.04) with SQL Server 2019 to an Active Directory domain, and then configure SQL Server to allow Windows-based logins.
There are quite a few steps here and I appreciate Jamie providing us an image-filled, step-by-step process.
Comments closedAndy Leonard has a new product announcement:
I’m excited to announce SSIS Framework Manager CE (Community Edition) is available for download at DILM Suite! SSIS Framework Manager CE is designed to support SSIS Framework Community Edition, providing a GUI to facilitate SSIS Framework Application creation, configuration, and management.
Three views are supported in this initial edition: Catalog, Application, and Package. The Catalog view incorporates the same Catalog treeview used in SSIS Catalog Browser (also free) and SSIS Catalog Compare (not free):
Click through to see what’s included.
Comments closedGarry Bargsley explains the utility of the model database:
Below I will outline the model database, which has a unique purpose for SQL Server.
The model database is an empty shell of a database that has the sole purpose of providing a database template for any new User Database created in SQL Server. With that being said, you can make configuration changes to the model database and then expect those baseline settings to be applied to all new user databases.
Read on to see some of the things you can set. I was a bit disappointed in some of the things which model doesn’t apply across to other databases, but it does carry over quite a bit.
Comments closedMichael J Swart has a tip for those who have RCSI turned on and are using app locks:
The procedure
sp_getapplockis a system stored procedure that can be helpful when developing SQL for concurrency. It takes a lock on an imaginary resource and it can be used to avoid race conditions.But I don’t use
sp_getapplocka lot. I almost always depend on SQL Server’s normal locking of resources (like tables, indexes, rows etc…). But I might consider it for complicated situations (like managing sort order in a hierarchy using a table with many different indexes).
Click through to see how it normally works and how you should switch things up if you’re using Read Committed Snapshot Isolation.
Comments closedErik Darling explains a difference:
One thing I’ve seen people run into is that these two technologies have very different relationships with Partitioning.
Now, I know this isn’t going to be the most common scenario, but often when you find people doing rocket surgeon stuff like tracking data changes, there are lots of other features creeping around.
Click through to see which one works better.
Comments closedDeepthi Goguri starts a series on memory grant internals:
SQL Server have different types of components (Plan Cache, Buffer pool and other memory components) in the memory area and requires memory for many things. We also require memory to process our queries. Each operator in the execution plan requires memory to process the rows from one operator to the other operator. Some of the iterators need to be buffered as the rows come into them before sending them to the next iterator in the execution plan. These iterators needs more memory. These are called memory consuming iterators. The grants that we given to these memory consuming iterators are called Query memory grants.
Click through for more.
Comments closed