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:

Cursor and Lock

Last month one of my client asked me a question, how SQL Server locks a table and its rows while a cursor is looping over it? So let's do an experiment using the StackOverflow2013 sample database.

Run below query on a window in SSMS. (For simplicity I just show you FAST_FORWARD which was mostly recommended by SQL Server MVPs. You can also try any other types of cursor, but I can tell you that the results are the same):

IF @@TRANCOUNT <> 0 ROLLBACK;
DECLARE @Id int;
DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
    SELECT V.Id FROM Votes V JOIN Users U ON V.UserId = U.Id
OPEN cur
WHILE 1 = 1
BEGIN
    FETCH cur INTO @Id
    IF @@FETCH_STATUS <> 0 BREAK
END
CLOSE cur
DEALLOCATE cur

 Get the SPID from the query, here I got spid 57:

While the first query is running, open a new query window, change the spid value, then execute it:

SELECT      r.start_time [Start Time],session_ID [SPID],
            DB_NAME(database_id) [Database],
            SUBSTRING(t.text,(r.statement_start_offset/2)+1,
            CASE WHEN statement_end_offset=-1 OR statement_end_offset=0
            THEN (DATALENGTH(t.Text)-r.statement_start_offset/2)+1
            ELSE (r.statement_end_offset-r.statement_start_offset)/2+1
            END) [Executing SQL],
            Status,command,wait_type,wait_time,wait_resource,
            last_wait_type
FROM        sys.dm_exec_requests r
OUTER APPLY sys.dm_exec_sql_text(sql_handle) t
WHERE       session_id = 57    -- Change it to the SPID number of the first query
ORDER BY    r.start_time
EXEC sp_lock 57;    -- Change it to the SPID number of the first query
-- Change below values to the dbid and ObjId values that you got from above result
SELECT DB_NAME(12), OBJECT_NAME(149575571, 12), OBJECT_NAME(181575685, 12);

Run the second query multiple times during the first one still executing. You can see different locks hold by DECLARE CURSOR, OPEN CURSOR, and FETCH statements:

Here is the summary of the locks being held by each cursor statement:

DECLARE: just defines the attributes of a cursor and the query used to build the result set, but NOT execute the query, so it always run too fast for you to get its lock, and actually it takes no any locks.
FETCH: retrieves one row from the cursor, it only takes DB S lock.
OPEN: opens a cursor and populates it by executing the cursor query. Table/page/row locks will be obtained by this statement. By default (read committed isolation level), once the OPEN statement finished, locks will be released. So NO table/page/row locks inside each loop for the cursor query.

Final thought: cursors could be used in some applications for serialized operations, but generally they should be avoided because they bring a negative impact on performance, especially when operating on a large sets of data.