SqlClient 5.0

The .NET data provider SqlClient for SQL Server has been released in version 5.0 for general availability. This is a new major version so you are advised to check the changes, especially the breaking changes.

At first there are added support for TDS 8.0, but that will not affect most developers or their products. Anyway there is a breaking change with this on the Encrypt property. A quick solution in many cases is to add the keyword TrustServerCertificate with the value true to the connection string. This will look like this:
…Integrated Security=SSPI;TrustServerCertificate=true;…
Using TrustServerCertificate is also described in the Microsoft documentation „Connection String Syntax“ in the section „Using TrustServerCertificate“.

But the added support for SPN on server and failover will be interesting to many working on critical systems using SQL Server AlwaysOn Availability Groups and Kerberos authentication.

I think that the most significant change is the breaking change on added dependency on the Microsoft.SqlServer.Server package. You should really take a detailed look on this if you are working with SqlClient based systems. Even if you are not a developer or architect but "only" Database Administrator or DevOps Engineer. The migration from the namespace Microsoft.Data.SqlClient.Server in the .NET framework to the namespace Microsoft.SqlServer.Server in the SqlClient package will require some sort of refactoring code.

See more in the blog post "Released: General Availability of Microsoft.Data.SqlClient 5.0" and the Release Notes on GitHub.

History

2022-08-10 : Post created.
2024-01-20 : Update with TrustServerCertificate.

Execute on a subset of databases

Sometimes it is necessary to execute a command or a statement on a subset of the databases in a instance. A quick solution is to generate the commands in a SELECT on sys.database and then execute the output.

But when this has been done a few times for a recurring task the solution becomes boring and tiresome. And then a more automated solution is required.

Automating the entire statement build and execxution over a cursor and using sp_executesql looks like a nice T-SQL solution.

DECLARE @DatabaseID AS INT;
DECLARE @DatabaseName AS NVARCHAR(50);
 
DECLARE @DatabaseCursor AS CURSOR;
SET @DatabaseCursor = CURSOR LOCAL FAST_FORWARD FOR
  SELECT name, database_id
  FROM sys.databases
  WHERE databases.name LIKE 'wso2am!_%' ESCAPE ('!');
OPEN @DatabaseCursor;

FETCH NEXT FROM @DatabaseCursor INTO @DatabaseName, @DatabaseID;
WHILE @@FETCH_STATUS = 0
BEGIN
  DECLARE @BackupSql NVARCHAR(4000) = N'BACKUP DATABASE [' + @DatabaseName + N'] TO DISK =''R:\BACKUP\' + @DatabaseName + '.bak'' WITH COPY_ONLY;';
  DECLARE @Msg NVARCHAR(2047) = CONVERT(nchar(27), SYSUTCDATETIME()) + N'Z  Backup [' + @DatabaseName + N']...'
  RAISERROR (@Msg, 0,0) WITH NOWAIT;
  DECLARE @BeginTime DATETIME2 = SYSUTCDATETIME();
  EXECUTE master.dbo.sp_executesql @stmt = @BackupSql;
  DECLARE @EndTime DATETIME2 = SYSUTCDATETIME();
  SET @Msg = N'  Duration = ' + CAST(DATEDIFF(s , @BeginTime, @EndTime) AS NVARCHAR(42)) + N's';
  RAISERROR (@Msg ,0,0) WITH NOWAIT;

  FETCH NEXT FROM @DatabaseCursor INTO @DatabaseName, @DatabaseID;
END
 
CLOSE @DatabaseCursor;
DEALLOCATE @DatabaseCursor;

The example above is part of cloning a subset of databases. I hope that you can use this in other situations.

RAISERROR with UNIQUEIDENTIFIER

The T-SQL type Uniqueidentifier is not a formatting possibility for at RAISERROR message. But converting the value to a string is a quick fix. This example is based on the Uniqueidentifier output from the procedure sp_add_jobschedule.

DECLARE @_schedule_uid UNIQUEIDENTIFIER = N'7f6a5df0-a68f-4b07-992f-b7595958061f';
DECLARE @_schedule_code NVARCHAR(50) = CONVERT(NVARCHAR(50), @_schedule_uid);
RAISERROR ( N'Schedule UID = %s', 0,0, @_schedule_code ) WITH NOWAIT;

The output is then

Schedule UID = 7F6A5DF0-A68F-4B07-992F-B7595958061F

A small detail is that with the conversion the value is also converted to upper-case.

Descriptions

Databases and objects in databases on SQL Server can be described with the Extended Property MS_Description. This description can be read by and used with Visual Studio, which gives a handy access to detailed documentation.

Unfortunately does instances and server-level objects like logins not have Extended Properties. So these elements can not have a description direct attached. There are some alternatives you can consider for these elements such like custom registry keys, file in root directory. But please don't create a custom table in the master database!

Description Lifecycle

A description can be created with the stored procedure sp_addextendedproperty. The procedure has eight parameters to control the name, value and level in the object hierarchy of the extended property. The value of the description has the type sql_variant, and the size can not be more than 7,500 bytes.

Later the description can be changed with the stored procedure sp_updateextendedprocedure.

Sometimes a tool or a clone can require the description to be removed. This can be done with the stored procedure sp_dropextendedproperty.

Extended Properties can be viewed through the catalog view sys.extended_properties or the function sys.fn_listextendedproperty.

Example on table description

EXECUTE sys.sp_updateextendedproperty
  @name=N'MS_Description', @value=N'List of SQL Server collations',
  @level0type=N'SCHEMA',@level0name=N'sqladmin',
  @level1type=N'TABLE',@level1name=N'collation';

SQL Server Management Studio

In SQL Server Management Studio (SSMS) the description on a object can be set as described above. But there are two more ways to add a description to a object. They are short described below, but the order they are mentioned in does not imply any ranking of the possibilities.
The first way to add a description is in the SSMS table design tool. Right-click on a object in the Object Explorer and click Design. Show the properties of the object with another right-click or press Alt+Enter. Then you can edit the Description property.

The second way to add a description is in the Database Diagrams of a given database. In here you can access the properties of a objects and edit the Description property.

Visual Studio

In SQL Server Management Studio (SSMS) extended properties are in the object properties. Here each extended property like MS_Description can be created, viewed, changed and deleted.

A Visual Studio data project can be created on a existing database or with a new database. Open the design of a table and the description in MS_Description is in the properties window, usually down to the right in Visual Studio.

In the example above the table "collation" has the description "SQL Server collations", that is placed in the extended property MS_Description of the table.

Actually the value of the description can also be handled (create/change) in a Visual Studio data project.

Data Classifications

When Microsoft introduced data classification with SQL Server 2012 this information was stored in extended properties. But with SQL Server 2016 this information is in (hidden) system tables.

Discussion

I can only recommend that you put the extended property MS_Description on each database and every object in the databases. At first it wil be a disturbance and somewhat annoying. But when you databases get in production, have lived for a few years and new developers og DBAs are to keep the database alive, then you (hopefully) will experience the benefits. That be in SSMS, Visual Studio or a 3rd part tool.

As a start you could add MS_Description to your static code test. At least on database, data objects and functional object. Later you can extend the test to cover more technical parts of the database like filegroups or users.

History

2023-01-01 : SSMS section added.
2022-05-31 : Entry created.

Restore AdventureWorks

There are several SQL Server sample databases from Microsoft, and one of the easiest to start and still somewhat up-to-date is AdventureWorks. There are various versions and editions of the database, and they are described in the Microsoft document "AdventureWorks sample databases".

This is an example on how to restore AdventuresWorks on a SQL Server Database Engine instance. The scripts and technical details are in the Github repository SQLAdmin (AdventureWorks).

Before you start restoring you have to make the backup file available to the instance. This can be done by copying the file to the default backup location of the Database Engine instance.

Then you can start the restore and some minor configurations to make the database available as below.

The configurations are not necessary to make the database available to you, but they are nice to make it general available and functional on SQL Server. Configuring Query Store on the database is optional before SQL Server 2022, but could be handy on a non-production instance.

History

2024-10-28 : Post made general and with scripts in Github.
2022-05-05 : Post created on SQL Server 2019