MSSQL: Controlling Resources

To prevent SQL Server taking up all the resource and leaving nothing to other application residing from the VM.

Right, Click Instance - Properties - Memory - Maximum server memory (in MB).

Note: leave the Processor alone unless you understand your system completely. Such as threads of dedication to certain queries.


To prevent applications from taking too many resources. Use Resource Pools.

MSSQL: Enabling and Disabling SQL Server Remote Connection

If a user is having issues connecting to the SQL Server remotely. This may worth a look. By default, it is set to "1" or ON and allowed connecting from a remote client. Another common type of connectivity issue might be connecting to Named instance or non-default port which discussed in another blog. 

To turn it OFF.

 EXEC sp_configure 'remote access', 0 ;   
 GO   
 RECONFIGURE ;   
 GO   

Remote connection troubleshooting reference

MSSQL: Contained Database

The Contained database consisted of all the settings enabling the database to be self-reliance and not depending on the instance or the database engine. Benefits of a contained database are to enable the ease to migrate to another instance or Azure platform easily.

The process of making converts a database to contained database consist of 2 steps.

To view if an instance contains any Contained database.

select name, containment from sys.databases;
select * from sys.dm_db_uncontained_entities;  
select * from sys.sql_modules; 

Step 1: Enable server property Contained Database authentication parameter

sp_configure 'Contained database authentication', 1;  
go  
Reconfigure  

Step 2: set database to contained database

use master  
go  
alter database vcdb_2016 set containment = PARTIAL  

To view Contained database

select name, containment from sys.databases;

If there is a need to migrate any users to the Contained database. It can be achieved with the following TSQL. Please check provided link if many users need to be migrated. Otherwise, each user will need to be migrated manually with "sp_migrate_user_to_contained" procedure.

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-migrate-user-to-contained-transact-sql?view=sql-server-2017
Just as an example.

 sp_migrate_user_to_contained    
 @username = N'Barry',   
 @rename = N'keep_name',   
 @disablelogin = N'do_not_disable_login' ;  

Other means of migration to Azure can be done using SQL Server Management Studio to copy the database over. Detach and attach method works as well.

MSSQL: Renewing Windows 2016 Server Standard Edition

Once the Windows Server 2016 trial is expired, it tends to just shut down. Activating will be impossible as it will no longer providing the option to enter the serial keys. The workaround is to re-arm the trial period

Bring up 'cmd' with Administrator mode.

slmgr -dlv

It will show the Remaining Windows rearm count. The user can rearm the trial license for a total of 6 times and 180 days for each.


Re-arm and restart the Windows.
slmgr -rearm

Now, activation should enable the user to enter the serial key by selecting "Change product key" if the user has a serial key instead of continuing to use trial periods.

MSSQL: Configuring SQL Server Private Mail

SQL Server Database Mail enables the database engine to send mail messages and alerting users of the database. The database mail stored procedures are disabled by default and it requires DBA configure and enable before using. Most of every mail configuration resides in the MSDB database.

Private Profile can be accessed by a specific user of the mail host database only. To configure a private, the user must be a part of the msdb database user.


The steps of Mail Configuration consists of Creating Profile, Add Account to the Profile and enabling the configuration.

The User name drop-down box users should already exist in the MSDB database under Security. This is the part that different than Public Profiles.

The parameters are configurable.

The Database Mail will not provide any indications of any configurations.

There are a couple of ways to verify the mail.

OR using SQL Command.

 exec msdb.dbo.sysmail_help_configure_sp;  
 go  
 exec msdb.dbo.sysmail_help_account_sp;  

Use the following query to verify if the mail is enabled or disabled. If the VALUE column is set to 0 then it is disabled and vice versa.

 SELECT * FROM sys.configurations WHERE NAME = 'Database Mail XPs'  

If the Mail is not enabled, it can be enabled with the sp_configure procedure. 

 exec sp_configure 'show advanced options', 1;  
 go   
 reconfigure;  
 go  
 -- 0 is to disable and disabling mail to send messages out.  
 exec sp_configure 'Database Mail XPs', 1;  
 go  
 Reconfigure;  
 go  

The last piece is to use the Mail.

Enable and Configure for usage.

Restart the SQL Server Agent. That's about it.

MSSQL: Looking into DB lock

Being a DBA. it is inevitable that users will come to your desk to ask why certain reports/queries running slower than usual or if there are anything blocking them.

Simulating a table lock for 10 minutes.

 use VCDB_2016  
 BEGIN TRAN   
 SELECT 1 FROM VPX_VERSION WITH (TABLOCKX)  
 WAITFOR DELAY '00:10:00'   
 ROLLBACK TRAN    
 GO  

Checking lock status.

 SELECT * FROM sys.dm_exec_requests where status not in ('sleeping','background');  

Dive deeper to take a look at the session id. The concerned session ID here is 52. There is Background, Running, Runnable, Sleeping and Suspended, The Sleeping and Background shouldn't be a concern. Suspended and perhaps Runnable should be looked at. 

 exec sp_who2  

Over here, the sp_who2 providing a lot of valuable information. The status is SUSPENDED and no blocker from other transaction (BlkBy Column). 

I usually prefer to take a peek at the SQL text as well. It is always useful to see which object, what is it doing and initiated from who. The sql_handle was gathered from the dm_exec_requests dynamic view. In this example, having a wait for 10 minutes should sound suspicious. The SQL Handle was gathered on the earlier query.

 select * from sys.dm_exec_sql_text(0x0200000049AABC156F2142A5AB98C8719717A8B61590F7CF0000000000000000000000000000000000000000)  

The following example showing what exactly the slow/suspended query is doing and from which object. Knowing what's the problem is half of the problem resolved.

In some cases, Suspended simply just slow and lacking resources and users simply just need to wait it out and let it completes. If the user needs to run a report and simply killing it every time, the needed report will never complete. 

Kill the session if it needs to be. For example, the offending session here is session ID 52.

kill 52

MSSQL: Creating Transparent Data Encryption

What is TDE?

Transparent Data Encryption
To be clear, encryption is not a way to prevent users from accessing an instance. Encryption is a way of obfuscating the database with keys or password.  To control users from accessing the database and the tables is through logins, authentication, and role.
This feature is very similar to Oracle's TDE where the key is stored in "keystore" or Oracle Key Vault while the SQL Server TDE key is stored in Master database associating with a certificate which will be used to encrypt the user database. SQL Server and Oracle offer similar encryption algorithms. Oracle does offer more granular encryption such as Column Encryption and Invisible Column. 

Image taken from https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/media/tde-architecture.gif?view=sql-server-2017

To sum up the SQL Server TDE, it basically can be accomplished in 4 steps.

1. create a master key in the Master database
2. create a certificate in the Master database protected by master Key
3. create encryption key in User database protected by the certificate
4. alter database to enable encryption.

Example

Use Master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ’MyPassword1!’
GO
CREATE CERTIFICATE TDE_Certificate WITH SUBJECT = ’TDE Certificate’;
GO

-- back up the certificate. This is used for recovery.

BACKUP CERTIFICATE TDE_Certificate TO FILE = ’d:\TDE_Certificate.cer’ WITH PRIVATE KEY (FILE = ’D:\TDE_Certificate.key’, ENCRYPTION BY PASSWORD = ’MyPassword1!’);
GO
Use VCDB
GO
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
GO
ALTER DATABASE VCDB SET ENCRYPTION ON;
GO
select * from sys.dm_database_encryption_keys;

This is just an example. I am not in a position to recommend the encryption on VCDB database.

-- Before restoring the database.

create certificate TDE_Certificate from file = 'd:\TDE_Certificate.cer' with private key (FILE = 'D:\TDE_Certificate.key', deryption by password = 'MyPassword1';

MSSQL: Backup (COPY_ONLY) and restoring database under different name.

Back up the database with COPY_ONLY to reduce interference to the production environment.

USE MASTER ;

BACKUP DATABASE VCDB TO DISK = 'C:\VCDB\MyDatabase1.bak' WITH COPY_ONLY ;

--Do not have to create new db first. Can be restore with new db name. The new db name will be created automagically.

USE MASTER ;

RESTORE DATABASE VCDB4 FROM DISK = 'C:\VCDB\MyDatabase1.bak' WITH MOVE 'VCDB' TO 'C:\VCDB\VCDB_3.mdf', MOVE 'VCDB_log' TO 'C:\VCDB\VCDB_3_log.ldf' ,REPLACE, RECOVERY

Left click and right click on Task - Restore database will do too

Select -  Restore Files and Filegroups