John Liu Blog

Every drop counts

Upgrade SQL SSISDB

When upgrade SSISDB, a few steps need to be considered when moving the SSISDB to a higher version on a new SQL instance. 1; Backup currently database master key BACKUP MASTER KEY TO FILE = 'file location/name' ENCRYPTION BY PASSWORD = 'your password for the backup key file' If you forgot the current password for the master key, you can reset the password via backup the master key (if not already have one) and then restore it with a new password

Posted by John Liu Tuesday, July 19, 2022

SQL Server on Azure VM

Some considerations when configure SQL Server on Azure VM. This also applicable to AWS VM For storage: - Separate volume for data and log file - Enable read caching on data file volume - Disable any caching on log file volume - Use D: drive (local SSD that will be cleared each time with server reboot) for TempDB - Enable instant file initialization - Move trace file and error log directories to data volume To evaluate disk performance when migrate from on-premises to cloud, capture following counters in Performance Monitor:

Posted by John Liu Wednesday, November 17, 2021

SQL database roles

One way SQL server to easily manage access is using database roles. SQL server has several built-in database roles. db_owner role member by default can perform any action within the database. However, except the actual database owner (user name dbo), other members in the db_owner role can be deny access by placing them in other database roles with deny access to objects, such as db_denydatareader. Other roles functions: db_backupoperator: perform database back db_datareader: can read data db_datawriter: can INSERT, UPDATE and DELETE data db_ddladmin: can create/modify any objects but can read/write data db_denydatareader: can’t read data db_denydatawriter: can’t write data db_securityadmin: can grant access to user db_accessadmin: can create user but can’t grant access to objects and data

Posted by John Liu Sunday, November 14, 2021

SQL Table Expression

Itzik Ben-Gan has a great series of articals about table expressions, focus on four types of named table expressions: derived tables, common table expressions (CTEs), views, and inline table-valued functions (inline TVFs). Fundamentals of table expressions, Part 1 Fundamentals of table expressions, Part 2 – Derived tables, logical considerations Fundamentals of table expressions, Part 3 – Derived tables, optimization considerations Fundamentals of table expressions, Part 4 – Derived tables, optimization considerations, continued

Posted by John Liu Thursday, November 11, 2021

Software working with Azure SQL Database

When develop software working with Azure SQL database, some special considerations needs to be taken into account. Connection string With standard SQL Server, if the connection didn’t specify a database name in your connection string, the connection default to master database. Unless specifically denyed access, a valid login with connection permission can connect to master database and be able to see the full list of database names on the connected instance, even if they don’t have permission to access those databases.

Posted by John Liu Tuesday, November 9, 2021