SQL Server Setup using Configuration File

This month blog post talks about another overlooked feature of SQL Server. SQL Server Setup can generate a configuration file based upon your inputs during you run the installation GUI. After that you can reuse the generated configuration file to deploy SQL Servers throughout the enterprise with the same configuration. It helps you to standardize installations throughout the enterprise. Here comes a demo showing how to use configuration file to install a stand-alone instance.

Execute sql server setup.exe > Installation > New SQL Server stand-alone installation.

Click Skip and Next buttons until reaches Installation Type > Perform a new installation.

In this demo, I'm using SQL Server 2019 Developer edition, but no matter which edition you are going to install (except express), you can still generate and use configuration file by following the same procedures stated here.

Enter the product key if you have, accept the license terms. Then in Feature Selection page, choose the features you needed. In this demo, I only chose Database Engine Services and SQL Server Replication.
Change the directories to install as you wish, for example, in D:\ drive.

Choose Default Instance or enter the Named Instance.

For performance boost, I enabled Grant Perform Volume Maintenance Task privilege which essentially enabled Database Instant File Initialization (IFI). Then I customized the server collation to SQL_Latin1_General_CP1_CI_AS.

In Database Engine Configuration page, I added current user as sysadmin, tempdb 2 data files, MAXDOP 2, and accepting recommended max server memory.

In the Ready to Install page, copy the Configuration File Path, then Click the Cancel button. 

You can open the generated .ini config file in notepad, to see the configuration values you just entered.
For example:
FEATURES=SQLENGINE,REPLICATION
INSTANCENAME="INSTANCE1"
INSTANCEDIR="D:\Program Files\Microsoft SQL Server"
SQLMAXDOP="2"
SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"
SQLSVCINSTANTFILEINIT="True"
SQLSYSADMINACCOUNTS="DESKTOP-LJND0A5\Monkey"
SQLTEMPDBFILECOUNT="2"
USESQLRECOMMENDEDMEMORYLIMITS="True"

Now close the installer program. Open a DOS command prompt, go to the sql server installer media directory, execute the sql server SETUP.EXE with config file specified, e.g.
SETUP.EXE /ConfigurationFile="C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\Log\20230404_020323\ConfigurationFile.ini"

You still need to traverse through the installer GUI, accept the license terms, but this time you can see the setup already made the selections that you defined in the last time generating the config file.

Setup configuration file is not as powerful as powershell scripts such as dbatools.io, but it's a quick and easy way for MSSQL DBA to standardize installation, especially DBA likes me that not so familiar with scripting.

Configure SSRS (also Power BI) Scale-Out deployment

Reporting Services (and also Power BI) Report Server supports a scale-out deployment model that allows you to run multiple report server instances that share a single report server database (ReportServer DB). Scale-out deployments are used to increase scalability of report servers to handle more concurrent users and larger report execution loads.

Scale-out deployments consist of:

• Two or more report server instances sharing a single report server database.
• Optionally, a network load-balanced (NLB) cluster to spread interactive user load across the report server instances.

Reporting Services does not participate in Microsoft Cluster Services clusters. However, you can create the report server database on a Database Engine instance that is part of a FCI or AlwaysOn Availability Group (AAG).

In this blog post, I would like to show you how to configure a scale-out deployment model of Power BI Report Server. The ReportServer DB will be put in a AAG, and the two report servers connect the ReportServer DB by using the AAG Listener name.

Procedures:

Run the Power BI Server installer on the Report Server #1.

Configure the Report Server #1.

Use the Report Server #1 to create the new ReportServer (and ReportServerTemp) databases. The SQL Server name is the AAG Listener name.

Still in Report Server #1, configure the Web Service URL and Web Portal URL. Just apply the default settings is OK.

 

Now go to Report Server #2, repeat the steps above to install Power Bi Report Server. BUT change the Report Server database to "Choose an existing report server database".

After finished configuring the Report Server #2, go back to Report Server #1 SSRS Configuration Manager. In the "Scale-Out Deployment" tab, you should see two entries: the 1st entry is the Report Server #1 which is already joined, the 2nd entry is the Report Server #2 which is Waiting to Join. Click the Report Server #2, then click the "Add Server" button. The Report Server #2 will becomes joined.

Now no matter you connect to which report server instance, when you deploy reports, data sources, and make permission changes, it will also be applied to all report server instance, as they are referring to the same ReportServer DB.

Extended Stored Procedure xp_logininfo

SQL Server provides a system extended stored procedure xp_logininfo, which is very useful to check domain users and groups granted access to the SQL Server instance. In this blog post I'm going to show how we can use this extended stored procedure to get information about the permission paths for users and groups, and to get information about the members of domain group.

If account_name cannot be identified as a valid Windows user or group, an error message is returned.

If account_name is a valid Windows user or group that is not associated with a SQL Server login, an empty result set is returned.

If account_name is specified, xp_logininfo reports the highest privilege level of the specified Windows user or group. If a Windows user has access as both a system administrator and as a domain user, it will be reported as a system administrator. If the user is a member of multiple Windows groups of equal privilege level, only the group that was first granted access to SQL Server is reported.

If account_name and all are specified, all permission paths for the Windows user or group are returned. If account_name is a member of multiple groups, all of which have been granted access to SQL Server, multiple rows are returned. The admin privilege rows are returned before the user privilege rows, and within a privilege level rows are returned in the order in which the corresponding SQL Server logins were created.

If account_name and members are specified, a list of the next-level members of the group is returned.

By using this stored procedure, database administrator can check account information from domain without bothering domain administrator.

Service Account does Not have the required user right "Log on as a service"

Last week I encountered a problem in one of my client's production SQL Server. The SQL Server service cannot be started after changing the service account from NT Service\MSSQLSERVER to a domain user account. Below screenshot shows the error raised in SQL Server Configuration Manager:

It guided me to the Windows event log. In the System event log, we got below error:
The problem cause is the service account doesn't have the required user right "log on as a service". According to SQL Server BOL there are at least four permissions required by the SQL Server service account to run. Let's double check that is really missing in the Local Security Policy setting:
If your servers are in an active directory domain, mostly these permissions should be applied by group policy, so you better consult the domain administrator. But there is also a workaround, you can use the service.msc to change the service account, and the service.msc will help you to grant the "log on as a service" right to the new service account, as shown by below screenshot:
HAPPY NEW YEAR! :D

Removing Transparent Data Encryption (TDE)

This month blog post is about removing Transparent data encryption (TDE) from a SQL Server database. It's not as simple as you thought. If you do it carelessly, you will be doomed. You will find that the database which was previously encrypted will not mount. You are faced with the error "Cannot find server certificate with thumbprint 'XXXX'.". You then try to restore the database from a backup and get the error "The file "DatabaseFile" failed to initialize correctly. Examine the error logs for more details." This has happened because there are still encrypted values in the log file. If you reach this point and you still have a copy of the certificate used to encrypt the database, restore that certificate to the Master Database and the database that was previously encrypted should mount. If you no longer have the certificate then you can try attaching just the mdf file which should rebuild the log file. If you only have access to a backup file and not the certificate, then you really are stuck.

Let's go to the sample script about how to remove TDE correctly:

/* HOW TO REMOVE TDE

* https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver16#remove-tde

* https://learn.microsoft.com/en-us/sql/t-sql/statements/drop-database-encryption-key-transact-sql?view=sql-server-ver16#examples

*/

-- CHECK

USE master

GO

SELECT DB_NAME(database_id), * FROM sys.dm_database_encryption_keys;

GO

--DECRYPT

ALTER DATABASE TestDB SET ENCRYPTION OFF

GO

-- CHECK encryption_state 3 --> 1

SELECT DB_NAME(database_id), * FROM sys.dm_database_encryption_keys;

GO

-- WAIT

USE TestDB

GO

DROP DATABASE ENCRYPTION KEY

GO

-- Clear Log

USE master

GO

ALTER DATABASE TestDB SET RECOVERY SIMPLE;

GO

USE [TestDB]

GO

DBCC SHRINKFILE (N'TestDB_log' , 0, TRUNCATEONLY)

GO

-- Reset Log

USE master

GO

ALTER DATABASE TestDB SET RECOVERY FULL;

GO

-- Make Compressed Backup

BACKUP DATABASE TestDB TO  DISK = N'D:\Backup\TestDB.bak' WITH NOFORMAT, NOINIT,  NAME = N'TEstDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10

GO

Error Creating AlwaysOn Availability Group

This month blog post, I would like to share an error case on creating sql server availability group.

I had a pair of sql servers, formed a WSFC, and enabled alwayson availability group feature in SSCM. But later our system administrator said he need to destroy and create again the WSFC. After the WSFC re-created, I tried to create availability group, then I get the following errors:

"Failed to obtain cluster information. Either the specified instance of SQL Server is not running on a Windows Server Failover Cluster (WSFC) node, or the user lacks sysadmin permissions on the SQL Server instance to obtain the cluster information."

"The specified instance of SQL Server is hosted by a system that is not a Windows Server Failover Cluster (WSFC) node."

Here are the troubleshooting steps:

1. Double check availability group feature enabled in all sql replicas.

2. Check your current login got sufficient right to create availability group in all sql replicas, for simplicity, sql server sysadmin server role.

3. Check cluster permission by right-click windows cluster in fail-over cluster manager -> Properties -> Cluster Permissions

By default, the Local Administrator group has Full Control on the WSFC. So for simplicity, the sql sysadmin login you are using should be added to the OS local administrator group too.

4. Run the following sql queries on each sql replica:
SELECT * FROM sys.dm_hadr_cluster_members;
SELECT * FROM sys.dm_hadr_cluster;
It should return some rows like below:

But if no any rows returned, then that sql replica needed to disable and re-enable availability group feature in SSCM.

Solution:
Disabled alwayson availability group feature on the “bad” node using SSCM, then restarted the sql service. Then enable availability group feature again followed by another sql service restart. After finishing this, you will able to get information about the cluster and nodes via the query which I mentioned earlier.

Repeatable Read causing Deadlock

Last month, one of my client consult me for an incident in its production sql server. They found a stored procedure that being called by multiple concurrent application threads raising deadlock errors frequently, and more seriously the deadlock errors were accumulated. Which means the sql server Lock Monitor (deadlock detection) resolve the deadlocks (by choosing one of the thread, which is the least expensive to roll back, as a deadlock victim, and kill it) not fast enough as the new threads spawned by the client application. (Another ref.: https://learn.microsoft.com/en-us/answers/questions/988682/sql-server-deadlock-detection-only-kills-one-victi.html). This case was very hard to troubleshoot, because the it is a single stored procedure, meaning the threads are accessing tables/objects in exactly the same logical order. Below shows the deadlock graph that being captured:

It seems a very typical deadlock graph, not enough clue provided. But when I opened the deadlock.xdl file in text, I saw isolationlevel="repeatable read (3)":

w

So I asked my client why the application need to set repeatable read to call that stored procedure. And you guess what they said? They told me that application was developed by outsource software vendor, and it's end-of-support now. In order to convince my client that repeatable read is the problem cause, I showed them a simple demo provided in Microsoft Tech Community: