SQL Server Info (Inventory)

--- SQL Server Info (Inventory)

SELECT 

    SERVERPROPERTY('ServerName') AS ServerName, 

    SERVERPROPERTY('MachineName') AS MachineName,

    CASE

        WHEN  SERVERPROPERTY('InstanceName') IS NULL THEN @@SERVICENAME

        ELSE SERVERPROPERTY('InstanceName')

    END AS InstanceName,

 

    SUBSTRING ( (SELECT @@VERSION),1, CHARINDEX('-',(SELECT @@VERSION))-1 ) as ProductName,

    SERVERPROPERTY('ProductVersion') AS ProductVersion, 

    SERVERPROPERTY('ProductLevel') AS ProductLevel,

    SERVERPROPERTY('ProductMajorVersion') AS ProductMajorVersion,

    SERVERPROPERTY('ProductMinorVersion') AS ProductMinorVersion,

    SERVERPROPERTY('ProductBuild') AS ProductBuild,

    SERVERPROPERTY('Edition') AS Edition,

    CASE SERVERPROPERTY('EngineEdition')

        WHEN 1 THEN 'PERSONAL'

        WHEN 2 THEN 'STANDARD'

        WHEN 3 THEN 'ENTERPRISE'

        WHEN 4 THEN 'EXPRESS'

        WHEN 5 THEN 'SQL DATABASE'

        WHEN 6 THEN 'SQL DATAWAREHOUSE'

    END AS EngineEdition, 

    CASE SERVERPROPERTY('IsHadrEnabled')

        WHEN 0 THEN 'The Always On Availability Groups feature is disabled'

        WHEN 1 THEN 'The Always On Availability Groups feature is enabled'

        ELSE 'Not applicable'

    END AS HadrEnabled,

    CASE SERVERPROPERTY('HadrManagerStatus')

        WHEN 0 THEN 'Not started, pending communication'

        WHEN 1 THEN 'Started and running'

        WHEN 2 THEN 'Not started and failed'

        ELSE 'Not applicable'

    END AS HadrManagerStatus,

    CASE SERVERPROPERTY('IsSingleUser') WHEN 0 THEN 'No' ELSE 'Yes' END AS InSingleUserMode,

    CASE SERVERPROPERTY('IsClustered')

        WHEN 1 THEN 'Clustered'

        WHEN 0 THEN 'Not Clustered'

        ELSE 'Not applicable'

    END AS IsClustered,

       local_tcp_port

       FROM   sys.dm_exec_connections

       WHERE  session_id = @@SPID 

 

Let’s talk about In memory OLTP

In-memory OLTP frequently referred to by its code name "Hekaton", was introduced in SQL Server 2014 but it got more popular from SQL Server 2016. This feature was being developed by Microsoft to stay in the game for high speed OLTP transaction speed and recent hardware changes in terms of the cost and throughput.

Many people utilize this feature for maximum performance where they want ultimate speed in  response from SQL server.  

The evaluating and migration of the normal disk based table and workload is made very simple by SQL server, the standard report “Transaction performance analysis overview”can scan through table and stored procedures based on usage. 

****

The data stored in the memory is separate from the buffer pool as like buffer pool paging out the disk when there is memory pressure but this is not with memory optimized tables.

Memory optimized tables use different structures to store data not like data pages. All the data gets loaded to memory at the time of SQL server startup.There is a separate checkpoint file created to just boost the database startup process. 

 Supported Data types:

• bit 

• All integer types: tinyint, smallint, int, bigint 

• All money types: money, smallmoney  

• All floating types: float, real  

• date/time types: datetime, smalldatetime, datetime2, date, time  numeric and decimal types  String types: char(n), varchar(n), nchar(n), nvarchar(n), sysname, varchar(MAX),   Varchar(MAX) 

•  Binary types: binary(n), varbinary(n), varbinary(MAX)  Uniqueidentifie

Steps to implement 

ALTER DATABASE AdventureWorks2016CTP3 

SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON; 

 

ALTER DATABASE AdventureWorks2016CTP3 

ADD FILEGROUP AdventureWorks2016CTP3_mod CONTAINS MEMORY_OPTIMIZED_DATA;

  

ALTER DATABASE AdventureWorks2016CTP3 

ADD FILE (name='AdventureWorks2016CTP3_mod1', 

filename='E:\data\AdventureWorks2016CTP3') 

TO FILEGROUP AdventureWorks2016CTP3_mod 

  

USE AdventureWorks2016CTP3

CREATE TABLE dbo.orderdetails

( OrderID INTEGER NOT NULL IDENTITY PRIMARY KEY NONCLUSTERED, ItemNumber   INTEGER    NOT NULL,

  OrderDate DATETIME   NOT NULL )  WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA); 

Performance benchmark for disk-based table and memory-optimized table.

SQLQueryStress is a simple, lightweight performance testing tool, designed to load test individual queries using this tool we tried generating load on the SQL server 

We did random inserts from 100 connections with 500 iterations on both Disk based table and memory optimized table.

As performance benchmark memory based table load was very fast , it was just 1.15 minutes to complete where on disk based table , the same operation took around 09:30 Minutes.

 

Disk based load

[RML] C:\Program Files\Microsoft Corporation\RMLUtils>     ostress.exe -n100 -r500 -S. -E -dAdventureWorks2016 -q -i"C:\Temp\Disk-based Inserts.sql"

Memory optimized table load

[RML] C:\Program Files\Microsoft Corporation\RMLUtils>    ostress.exe -n100 -r500 -S. -E -dAdventureWorks2016 -q -i"C:\temp\Memory-optimized Inserts.sql"

For new application development and design this feature is great has very sound on performance perspective but on existing tables there is some over head and challenges on data type and referential integrity if those can be handled its great feature in the SQL server 

 

--By Santosh Sethi 

Full Database Backup Restore

Working as DBA or developer, restoring a database is the request which we get regularly. Restoring a database is easy task if our back file is healthy.

Restore a Full Database Backup

Below is script which we can use for restore a database from full backup...

-------------------------------------------------------------------- 

RESTORE DATABASE DB_Name FROM DISK = 'Location of DB backup file path' 

--------------------------------------------------------------------

But the problem with the script is, we do not know where the DB files are moving after the restore.

To over come the issue, below are steps 

1. We need to find out the list of database files which the backup file is containing.

2. The logical name of each DB files which are in side the backup file.

To know the above information, we use  RESTORE FILELISTONLY command.

Below is the example.

  

--------------------------------------------------------------------

RESTORE FILELISTONLY from DISK= N'Location of DB backup file path'

--------------------------------------------------------------------

With the help of the commend, we can identify the list of DB files which are inside the backup file.

In the example, we can see there are 3 files in the D1.BAK D1, D1_1 and D1_log.

At the time of restoration, we can use MOVE options in RESTORE command to move the files at desire location. I'm taking D1.BAK file to explain the restore with move option.

--------------------------------------------------------------------

RESTORE DATABASE D1_RESTORE FROM DISK = N'C:\D1.BAK'

WITH

MOVE 'D1' TO 'C:\D1_DATABSE_1.MDF' ,

MOVE 'D1_1' TO 'E:\D1_DATABSE_1.NDF',

MOVE 'D1_log' TO 'E:\D1_LOG_FILE.LDF',

RECOVERY 

--------------------------------------------------------------------

TDE in SQL Server

Transparent Database Encryption (TDE) – It was developed with SQL Server 2008, and it is also available in Oracle database management systems. It is an encryption method that protects the core data in the database.

The encryption method protects the data in the database by encrypting the underlying files of the database, and not the data itself. This prevents the data from being hacked and copied to another server, in order to open the files, you have to have the original encryption certificate and a master key. The actual encryption of the database is done at the page level.

Because TDE protects/encrypts the structure of the database, it is considered an at rest encryption method.

Procedure to implement TDE (Transparent database Encryption)

STEP -1: Login to any instance and create a database.

create database TDE_Test

 

use TDE_Test

STEP -2: Create a table and insert some data,

 create table emp

(

name nvarchar(200)

)

 

insert into emp

select 'john'

 

insert into emp

select 'peter'

 

insert into emp

select 'Rose'

 

select *from emp

STEP -3: Create a Master Key, using the Master Database.

use master

create master key

encryption by password = 'LocalSecure@123'

STEP -4: Now, use the following query to create a certificate with the subject "MY DB Certificate"

   create certificate MyCert1 with Subject = 'MY DB Certificate'

STEP -5: Create a database encryption key with algorithm AES_128

 

use TDE_Test

 

create database encryption key

with algorithm = AES_128

encryption by server certificate Mycert1

STEP -6: Now Enable a Encryption Key using below query,

 

alter database TDE_Test set encryption ON

 

STEP -7: Take a full database backup using below query,

backup database TDE_Test to disk = N'C:\Admin\TDE_2_Full.bak'

 

STEP -8: Now, backup a certificate using the master database and store it in a secure location.

USE master;

 

BACKUP CERTIFICATE MyCert1

    TO FILE = 'C:\Admin\MyCert.bak'

    WITH PRIVATE KEY (

               FILE = 'C:\Admin\MyCert.pvk',

               ENCRYPTION BY PASSWORD = 'Secure@123');

--SQL1 destination server

/*

USE [master]

 

RESTORE DATABASE [TDE_Test] FROM DISK = N'C:\Admin\TDE_Test_Full.bak'

 

WITH

 

Move 'TDE_Test' to N'R:\Program Files\MicrosoftSQLServer\MSSQL15.MSSQLSERVER\MSSQ\Backup\TDE_Test.mdf',

 

move 'TDE_Test_log' to N'L:\Program Files\MicrosoftSQLServer\MSSQL15.MSSQLSERVER\MSSQL\Data\TDE_Test_log.ldf'

,

FILE = 1,  NOUNLOAD,  STATS = 5

 

*/

/*

  An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

        -------------------------------------

  

·      Additional Information 

(Note- If you directly try to restore a database from a disk so you must ensure that at the time of restore, you get the below error) 

So first create a master key and then create a certificate and private key using previous certificate or 

Follow the below steps 

*/

STEP -9: Move the backup file and certificate to another location after taking a backup

from where you want to restore a database

After that open the secondary Server in SSMS.

Create a master key using master and the same encryption password.

use master

 create master key

encryption by password = 'SQL1Secure@123'

STEP -10: Create a certificate from a backup file using the appropriate private key and decryption password:

CREATE CERTIFICATE MyCert1

FROM FILE = 'C:\Admin\MyCert1.bak'   

WITH PRIVATE KEY (FILE = 'C:\Admin\MyCert1.pvk',

DECRYPTION BY PASSWORD = 'Secure@123')

STEP -11: Now Restore filelistonly for data and log file using below query,

RESTORE filelistonly From Disk = N'C:\Admin\TDE_Test_Full.bak' 

STEP -12: Now Restore a database using move command,

USE [master]

 

RESTORE DATABASE [TDE_Test] FROM DISK = N'C:\Admin\TDE_Test_Full.bak'

WITH

 

move 'TDE_Test' to N'R:\Program Files\Microsoft SQLServer\MSSQL15.MSSQLSERVER\MSSQL\Backup\TDE_Test.mdf',

 

move 'TDE_Test_log' to N'L:\Program Files\Microsoft SQLServer\MSSQL15.MSSQLSERVER\MSSQL\Data\TDE_Test_log.ldf'

,

FILE = 1,  NOUNLOAD,  STATS = 5

STEP -12: Now Restore a database using move command,

USE [master]

 

RESTORE DATABASE [TDE_Test] FROM DISK = N'C:\Admin\TDE_Test_Full.bak'

WITH

 

move 'TDE_Test' to N'R:\Program Files\Microsoft SQLServer\MSSQL15.MSSQLSERVER\MSSQL\Backup\TDE_Test.mdf',

 

move 'TDE_Test_log' to N'L:\Program Files\Microsoft SQLServer\MSSQL15.MSSQLSERVER\MSSQL\Data\TDE_Test_log.ldf'

,

FILE = 1,  NOUNLOAD,  STATS = 5

Ø Advantages of TDE

·      Fairly simple to implement.

·      No changes to the application tier required.

·      Is invisible to the user

·      Works with high availability features, such as mirroring, always on and Log shipping

·      Works with older versions of SQL server, back to 2008

Ø Disadvantages of TDE

·      Only encrypts data at rest, so data in motion or held within and application is not encrypted.

·      All data in the database is encrypted – not just the sensitive data.

·      Requires the more expensive Enterprise Edition (or Developer or Data center Edition) of SQL Server

·      The amount of compression achieved with compressed backups will be significantly reduced.

·      There is a small performance impact.

·      File Stream data is not encrypted

·      Some DBA tasks require extra complexity, for instance restoring a backup onto another server.

·      As TempDB is encrypted, there is potentially an impact on non-encrypted databases on the same server.

·      The master database, which contains various metadata, user data and server level information is not encrypted.

--Check which Databases are encrypted 
SELECT db_name(database_id), encryption_state,   percent_complete, key_algorithm, key_length
FROM sys.dm_database_encryption_keys
WHERE db_name(database_id) not in('tempdb');

Please follow steps below to drop Certificate and Master Key:

Step 1:  Turn off encryption on your user database

ALTER DATABASE DB_Name SET ENCRYPTION OFF

Step 2: Drop the Database Encryption Key of your user database

USE DB_Name

DROP DATABASE ENCRYPTION KEY                                           

Step 3: Drop the certificate and master key on master database

USE master

DROP CERTIFICATE TDECert_Name
DROP MASTER KEY