DBA - How To Disable TDE on a Database and Restore To Another Server

Scenario:

Once you enable the TDE ( Transparent Data Encryption ) on a Database, Even you Set it Off and take the backup you will not be able to restore to another server without creating Certificate. In our Scenario we need to provide database without TDE to one of our vendors for testing. We don't want to provide them the Certificate and Key.

Solution:

Here are the steps to disable TDE on SQL server database and then take backup and restore to another server without creating Certificate.

Step 1: 

Get the Certificate Name with respect to Database

USE MASTER
GO
SELECT CR.name                 AS CertificateName,
       DB_NAME(EK.database_id) AS DBName
FROM   sys.dm_database_encryption_keys Ek
       INNER JOIN sys.certificates CR
               ON EK.encryptor_thumbprint = CR.thumbprint

Step 2: 

Set the encryption off on Database by using below script.

USE MASTER;
ALTER DATABASE [YourDbName]
SET ENCRYPTION OFF;
GO

Step 3:

Drop Database Encryption Key by using below script 

USE [YourDbName]
DROP DATABASE ENCRYPTION KEY

Step 4: 

Drop Certificate on Database by using below script. Get the certificate name by using script in Step 1.

USE MASTER
DROP CERTIFICATE YourCertificateNameThatYouWantToDrop

Step 5: 

Take the backup of Database and then Restore to destination where you want to. It would let you restore without any error.

SSIS - How to Change Value of Variable During Debugging in SSIS Package

Scenario:

We are doing unit test of our SSIS Package or debugging. We are storing row count in RowCnt variable in our First Data Flow task. Depending upon the value of RowCnt variable ,we have set up Precedence Constraints. If the RowCnt is greater than or equal to 10 we want to Execute one Data Flow Task and if RowCnt is less than 10 then we want to Execute Second Data Flow task. It is very common scenario that we even don't have exact number or records in source to test both parts of SSIS Package. This is just one example, there could be so many other situations where you want to have the control  to change the value of variable/s so you can test different parts of SSIS Package while doing Unit Testing or Debugging.

Solution:

We will be changing the value of variable in Variable Watch Windows. Let's walk through above example step by step.

Step 1:

I have created RowCnt variable as shown below

Fig 1: Create RowCnt Variable in SSIS Package

Step 2: 
Bring Data Flow Task to Control Flow Pane and then create connection to Database or create any source connection as per your source. I have created Database connection and extracting data from SQL Table.
Inside the Data Flow Task, I have connected OLE DB Source to Row Count Transformation to save count in RowCnt variable.

Fig 2: Use Row Count Transformation in Data Flow Task to save Record Count

Step 3: 

Bring two Data Flow Tasks and connect to first Data Flow Task as shown in below Figure. Double click on green connection between them and write expressions.If you need help with using Precedence Constraints, please check This Post.

Fig 3: Configure Precedence Constraint in SSIS Package between Tasks

Step 4:

Now the fun part start, we want to enable Break Point on Data Flow Task so we can see the value of variable and change to test different scenarios. Right Click on Data Flow Task and then Select BreakPoint. It will open a window. Check the box as shown below

Fig 4: Enable BreakPoint in SSIS Package

I have chosen Break when the container receives the OnPostExecute event. By choosing that I want to make sure all the records are read in RowCnt variable inside Data Flow Task.

Step 5:

Execute the SSIS Package and see the value of RowCnt variable in Watch window. If you are not sure how to get to Watch window. You can check This post that shows you step by step how to configure it.

Fig 5: Variable Watch window in SSIS Package

As we can see that the value of RowCnt=9 and according to our expressions on Precedence Constraint, Data Flow Task 2 should execute. Let's hit play button and see if that works.

Fig 6: Use Precedence Constraint to Control Execute Flow in SSIS Package

Step 6:

To test the second part where our Data Flow Task 1 should execute. Either we have to have more or equal to 10 records in table or we should be able to change the value of variable while executing our SSIS Package. We are going for second option as it is easy and quick and we don't have to worry about making any changes on Source side.

Let's run the package one more time and go to Variable Watch window.

Fig 7: SSIS Variable Watch Window to Change Variable value while Execution

Double click on value that is 9 and change to 11 so we can test Data Flow Task 1. Once you will change is the 9 will change the color to red but in front of User::RowCnt the value will be changed from {9} to {11}

Fig 8: Change the value of variable in SSIS Package during debugging

Now click on Play button (Start Debugging) or Fit F5. As the value of value of RowCnt=11 now the Data Flow Task 1 should execute.

Fig 9: Modify value of variable during troubleshooting SSIS Package

As we can see that the RowCnt value did change and we were able to test both parts of SSIS Package without making any changes in source data. Hope this will help!

TSQL -What Is The Difference Between INNER JOIN AND LEFT JOIN

INNER JOIN: 

Inner Join returns you all matching records from two tables. You can further join the result set to another table or result set. As long as the column values match for Joining columns , you will get the records back.

If there are duplicate records for Joining column/s and they match,then you will get duplicate records in your result set.

LEFT JOIN:

Left Join will return you all the records from Left Table and matching records from Right table. If there are not matching records for Right Table, You will get Null values in those columns. Left Join will also return you duplicate records if your tables have duplicate records in columns on which you are joining and they are matching.

Let's create an example to see them in action;)

I have created two Temp tables #Person and #Country. #Person table CountryId from #Country Table. So we can join on CountryId.

CREATE TABLE #Person
  (
     PersonID  INT IDENTITY(1, 1),
     FName     VARCHAR(100),
     LName     VARCHAR(100),
     CountryID INT
  )GO

CREATE TABLE #Country
  (
     CountryID   INT IDENTITY(1, 1),
     CountryName VARCHAR(50)
  )

GO

INSERT INTO #Person

VALUES     ('Raza',
            'Ali',
            1)GO

INSERT INTO #Person

VALUES     ('Christy',
            'Jason',
            2)GO

INSERT INTO #Person

VALUES     ('John',
            'Rivers',
            3)GO

INSERT INTO #Person

VALUES     ('Sukhjeet',
            'Singh',
            NULL)GO

INSERT INTO #Country

VALUES     ('USA'),
            ('France'),
            ('Italy')

SELECT * FROM #Country

SELECT * FROM #Person

Fig 1: Sample Temp tables 

Now if we want to find all the Persons which has the country or in other words , We want to find all the matching records. To do so we will be using INNER JOIN

SELECT P.FName,
       P.LName,
       C.CountryName

FROM   #Person P
       INNER JOIN #Country C
               ON P.CountryID = C.CountryID

Fig 2: Inner join output

As we can see that Sukhjeet Sing is not returned by our above query as there is no matching record available in #Country Table for Sukhjeet Singh. The Countryid for Sukhjeet Singh is Null and we don't have Null record in #Country Table. Even if we would have Null in Country Table, it would not return us Sukhjeet Singh as Null is not equal to Null. In such situations we can always use ISNULL function to replace with some number on both sides so we can join them.

Now if we want to get all the records from Left Table, in our case from  #Person table. No matter if it has matching records or not in #Country Table. We can use LEFT JOIN.

SELECT P.FName,
       P.LName,
       C.CountryName

FROM   #Person P
       LEFT JOIN #Country C
              ON P.CountryID = C.CountryID

Fig 3: Left Join output

As we can see that all the records from left tables are returned and if there was matching record in Right table that value is returned. If there were not matching records then NULL is returned as value.

TSQL - What Is Logical Query Processing Order In SQL Server

The way you write TSQL query in SSMS ( SQL Server Management Studio) or any other Editor that is going to be executed on SQL Server does not run in the order it is written in. When we write query we start with Select, Update or Delete and then use conditions etc. SQL Server take this query and evaluates order of the query in different order in which it is written. The way SQL Server evaluates the order of execution is called Logical Query Processing Order.

Let's say we have a simple query

SELECT Name, SUM(Sale) AS TotalSale

FROM Dbo.Sale

WHERE Name LIKE 'A%'

GROUP BY Name

HAVING SUM(Sale)>100

ORDER BY Name DESC

The order of our above query is

1	Select
2	From
3	Where
4	Group by
5	Having
6	Order By

Fig 1: Written Query Order

But when SQL Server is going to evaluate this query, It will not start with Select statement. Below is the Logical Query Processing Order for above query

1	From  (Table or Tables)
2	Where (Search Criteria)
3	Group By ( Group by Column/s)
4	Having ( Search or Filter Criteria)
5	Select ( Select list of columns)
6	Order By ( Order by column/s)

Fig 2: Logical Query Processing Order

As we can see that the order in which query is written is not the order in which it will be evaluated/executed by SQL Server. Fig 1 , the start point of query is Select but in Logical query processing order ( Fig 2) is From clause. 

TSQL - How To Send Email From SQL Server In HTML Format

We can send email from SQL Server in HTML format by using dbo.sp_send_dbmail stored procedure. To send email in HTML format, we have to build the body of email with HTML Tags. The below code can be used to send basis HTML formatted email. If you want to loop through some results of table and send in HTML formatted email. You can use This script for looping ( TSQL Cursor) and build the body by using below script.

--Get the Profile Name that you want to use 
--select * from msdb..sysmail_profile

DECLARE @Recipients VARCHAR(200)
SET @Recipients='aamirwarraich2001@gmail.com'
DECLARE @Subject VARCHAR(100)
SET @Subject='Test Email Subject'
DECLARE @Body NVARCHAR(2000)

DECLARE @ProfileName VARCHAR(100)
SET @ProfileName='EmailProfileName'

SET @Body=N'
<html>
 <head>
  <meta http-equiv=Content-Type content="text/html; charset=windows-1252">
  <style>p{font-size:10.0pt;font-family:Calibri,"Arial","serif";}</style>
 </head>
 <body>
 <p>Hello All,</p>
 <p>Sql Age is the blog spot to learn and share Information, Scenarios, Real Time Examples about SQL Server,<br />
  Transact-SQL (TSQL), SQL Server Database Administration (SQL DBA), Business Intelligence (BI),<br />
  SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS) and Data Warehouse (DWH) Concepts.</p>
 <p>Thank you,<br />
  Development Team<br />
 <a href="http://sqlage.blogspot.com/"><b>sqlage.blogspot.com</b></a><br />
 </p>
 </body>
</html>'
EXEC msdb.dbo.sp_send_dbmail
  @profile_name =@ProfileName,
  @recipients = @Recipients,
  @subject = @Subject,
  @body = @Body,
  @body_format = 'HTML'

Here is the output of above script. If you want to change the fond size , format type that can be changed in above code as per requirement.

Fig 1: HTML Formatted Email send by SQL Server dbo.Sp_Send_dbMail Stored Procedure

TSQL - What Type of Commands Are Available In TSQL?

There are three types of Commands available in TSQL

DCL (Data Control Language) :

These commands are used to control the permission on objects such as tables, views, stored procedures and functions etc.

For example:

Grant and Deny are the commands which can be used to grant permission on objects or deny.

DML (Data Manipulation Language):

DML commands are used to select data, update, delete and insert.

For Example:

Select * from dbo.Table

Update Table

Delete from Table

Insert into dbo.Table

DDL (Data Definition Language):

DDL commands are used to create, drop or alter database objects.

For example:

Create table, drop table, alter table, and create SP etc. Truncate is also considered DDL command.

DBA - How To Change Recovery Mode To Simple For All Databases on SQL Server

Scenario: 

This can be scenario on Development machine where you have given permissions to developers to work on different databases and you really don't need to take the transaction log backups for these databases. By setting the Recovery mode to Simple for all the databases on Development machine can save you some space (Storage) as you don't have Backup jobs set up for Transaction log backups.

Solution:

The below Code can be used to Set the Recover Mode to Simple for all the databases for which it is not set to Simple recovery mode already. You can change below script if you want to change Recovery mode to Full as well by simply changing <>'FULL' and SET RECOVERY FULL.

USE MASTER
GO
DECLARE @DBName VARCHAR(200)

DECLARE DB_Cursor CURSOR FOR
  SELECT name
  FROM   sys.databases
  WHERE  name NOT IN ( 'master', 'tempdb', 'model', 'msdb' )
OPEN DB_Cursor
FETCH NEXT FROM DB_Cursor INTO @DBName
WHILE @@FETCH_STATUS = 0
  BEGIN
      IF ( (SELECT recovery_model_desc
            FROM   sys.databases
            WHERE  name = @DBName) <> 'SIMPLE' )
        BEGIN
            DECLARE @SQL NVARCHAR(500)

            SET @SQL='ALTER DATABASE [' + @DBName
                     + '] SET RECOVERY SIMPLE'

            PRINT @SQL

            EXEC ( @SQL)
        END

      FETCH NEXT FROM DB_Cursor INTO @DBName
  END

CLOSE DB_Cursor
DEALLOCATE DB_Cursor

TSQL- How To Loop Through Users From a SQL Server Table And Send Email

Scenario:

We have a table which contains information related to the users, their emails, email subject and email body. We need to write process that will read all the records for which Email is not sent and send them email by using the information available in table. After sending email we will be setting the EmailStatusFlg=1 that means the email is send for this record.

Solution:

To loop through the table records one by one , we will be using TSQL Cursor. By using Cursor we will loop through one record at a time and email and then take next record and so on.

For this example, I have created sample table with some sample data. Initially EmailStatusFlg=0. Once the email will be send , the EmailStatusFlg will be set to 1.

USE TestDB
GO

CREATE TABLE dbo.EmailNotification
  (
     EmailID        INT IDENTITY(1, 1),
     EmailAddress   VARCHAR(100),
     EmailSubject   VARCHAR(200),
     Body           NVARCHAR(MAX),
     EmailStatusFlg BIT
  )

GO

INSERT INTO dbo.EmailNotification

VALUES     ( 'aamirwarraich2001@gmail.com',
             'TestEmail Subject',
             ' This is test email to users',
             0)

GO

INSERT INTO dbo.EmailNotification

VALUES      ( 'aamirwarraich2001@gmail.com',
              'TestEmail Subject 1',
              ' This is test email to users 1',
              0)

Let's write our TSQL Cursor to loop through Email addresses and send email to users one by one.

-->GET the MailProfile by using Below Query
--select * from msdb..sysmail_profile

--> Cursor to Send Email to Recipients one by one

DECLARE @Mail_Profile_Name VARCHAR(100)

SET @Mail_Profile_Name='MailProfileName'

DECLARE @MessageBody NVARCHAR(1000)

DECLARE @RecipientsList NVARCHAR(500)

DECLARE @MailSubject NVARCHAR(500)

DECLARE @EmailID INT
    
DECLARE Email_cursor CURSOR FOR
SELECT Emailid,EmailAddress,EmailSubject,Body 

FROM dbo.EmailNotification WHERE EmailStatusFlg=0
OPEN Email_cursor 
FETCH NEXT FROM Email_cursor  INTO @EmailID,@RecipientsList,@MailSubject,@MessageBody

WHILE @@FETCH_STATUS = 0
  BEGIN
 
  EXEC msdb.dbo.sp_send_dbmail
    @profile_name = @Mail_Profile_Name,
    @recipients = @RecipientsList,
    @body = @MessageBody,
    @subject = @MailSubject;
    
    UPDATE dbo.EmailNotification
    SET EmailStatusFlg=1
    WHERE EmailID=@EmailID
FETCH NEXT FROM Email_cursor  INTO @EmailID,@RecipientsList,@MailSubject,@MessageBody
  END
CLOSE Email_cursor

DEALLOCATE Email_cursor

You can create Stored Procedure or use this script as it. The script can be run manually or you can create SQL Server Agent Job to run on schedule.

TSQL - How To Find Version of SQL Server

SQL Server provide built in functions those are helpful to provide us different information related to SQL server such as Version, SQL Server Name etc.

We can use @@Version to get the version information of SQL Server. I have executed below Query on SQL server 2008 and SQL Server 2012.

SELECT @@VERSION

Fig 1: Output of @@version on SQL Server 2008 R2

Fig 2: Output of @@Version on SQL Server 2012

TSQL - How to Get Row Count Of All The Tables In SQL Server Database

Sometime we want to get the Row Count quickly for all the tables in our Database for analysis. There are multiple ways to do that. We can use TSQL Cursor to loop through all the tables and use count(*) to get the row count.

I am using Catalog views to get this information by using below query. This is quick way to find the row count for all the tables in a database. The Cursor with Count(*) can be slow as it has to count rows for each of the table.

USE YourDBName

GO

SELECT OBJECT_NAME(id) AS TableName,
       rowcnt          AS [RowCount]

FROM   sysindexes s
       INNER JOIN sys.tables t
               ON s.id = t.OBJECT_ID

WHERE  s.indid IN ( 0, 1, 255 )
       AND is_ms_shipped = 0

I ran above query on TestDB and got below information.

Fig 1: Get Row Count for all the Tables in SQL Server Database

Check out our other posts on Data Analysis / Data Validation / Data Cleansing

• How to Search in all Columns for all tables in a database for Date Value in SQL Server
• How to Find Percentage of Null Values in every Column of all the Tables in SQL Server Database 
• How to Find Percentage of Empty or Blank Values in every Column of all the Tables in SQL Server Database
• How to search for a String in all Columns in all tables in SQL Server Database
• How to find and Replace Text in all the columns in all the tables in SQL server Database
• How to find values with Leading or Trailing Spaces in SQL Server Table 
• How to Trim Leading and Trailing spaces for all the values in all the Columns in all the Tables in a SQL Server Database
• How to Find Duplicate Records in All the Tables in SQL Server Database
• How to Find & Delete Duplicate Records on All Tables in SQL Server Database
• How to Generate Select all Columns with or Without Top X Rows From All Tables Query Dynamically in SQL Server
• Concatenate SQL Server Columns for each Table Query Dynamically in SQL Server 
• How to List all Missing Identity Values for all Tables in SQL Server Database
• How to Find Max and Min Values for all the columns for all the tables in SQL Server Database

TSQL - What is the difference between ISNULL () AND NULLIF() Function In TSQL

ISNULL( ) function replaces the Null value with placed value. The use of ISNULL ( ) function is very common in different situations such as changing the Null value to some value in Joins, in Select statement etc.

NULLIF ( ) function returns us Null if two arguments passed to functions are equal. NULLIF( ) function works like Case statement. If both arguments are not same then it will return us first argument value.

Let's create small example to see these functions in action

SELECT *INTO   #tmp

FROM   (SELECT N'1'       AS [Id],
               N'Aamir'   AS [FName],
               N'Shahzad' AS [LName]
        UNION ALL
        SELECT N'2'      AS [Id],
               N'Robert' AS [FName],
               N'Ladson' AS [LName]
        UNION ALL
        SELECT N'3'      AS [Id],
               N'John'   AS [FName],
               N'Rivers' AS [LName]
        UNION ALL
        SELECT N'4'    AS [Id],
               N'Mike' AS [FName],
               NULL    AS [LName]
        UNION ALL
        SELECT N'4'    AS [Id],
               N'Tina' AS [FName],
               N'Tina' AS [LName]) t;
SELECT [Id],
       [FName],
       [LName]FROM   #tmp

See the records of #Temp Table by using Select statement.

Fig 1: #Temp table Records

Now we want to replace Null in LName column with "Unknown" if any value is Null. We also want to create new output column in our Select statement with name "ColValueNotEqual". If FName and LName are different then we want to get FName value and If FName and LName are same then we want to get Null for this column. Let's use ISNULL ( ) and NULLIF ( ) functions to get required results.

SELECT [Id],
       [FName],
       ISNULL([LName], 'Unknown') AS LName,
       NULLIF(FName, LName)       AS ColValueNotEqual

FROM   #tmp 

Fig 2: ISNULL() and NULLIF() in TSQL

In Fig 2, We can see that LName Null value is replaced by "Unknown" as we have used ISNULL() function. ColValueNotEqual is returning us FName in case when FName and LName are not equal and returning us Null when FName and LName column's values are equal as we have used NULLIF() function.

TSQL - What is the Difference between LEN() AND DataLength() Functions

LEN( ) function returns us the number of characters or length of text. This function can be used in different scenarios, It is very common we want to know the Max number of characters available in our one of the columns.

For that we can use

SELECT MAX(LEN(colname)) FROM dbo.TABLE

DataLength( ) function returns us the space(memory) taken by value we provide to this function in Bytes.

Let's see couple of examples

Fig 1: Len vs DataLength Functions in TSQL

As we can see that 'Aamir' has five characters and Len( ) functions returned us 5. DataLength( ) Function also returned us 5 ( 5 Bytes) as 'Aamir' is considered varchar data type and each character takes one byte in varchar data type.

Let's change the data type of 'Aamir' to NVARCHAR by adding N'Aamir'. Now our DataLength( ) function should return us 10 ( 10 Bytes). As two bytes are required to save NVARCHAR character.

Fig 2: Difference between DataLength() and Len() Function in TSQL

SSIS - How To Delete Old Folders In SSIS Package

Scenario: 

One of the post I wrote "How to delete old files" that you can check Here. In this post we will be deleting the old folders. We will be able to provide the Retention Period in Days. The package will delete all the old Folders older than Retention period in days.

Solution:

Step 1:

Let's create two variables 

ParentFolderFolder: 

Provide the parent folder path in which other folders exist those we need to drop.

RetentionPeriodInDays: 

Provide the number of days. The package will delete all folders older than days provided in this variable.

In my demo , I want to delete all the folders which are older than 7 days.

Fig 1: Create Variables in SSIS Package for Delete Old Folders

Step 2: 

Check the folders in your parent folder, I have 3 folders in my Test Folder which are older than 7 days as of August 8,2014.

Fig 2: Parent Folder with Folders to be Deleted

Step 3:

Bring Script Task to Control Flow Pane. Open Script Task and add both variables as shown

Fig 3: Adding Variables to Script Task

Step 4:

Click on Edit Script and write below script

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Runtime;

using System.Windows.Forms;

using System.IO;
namespace ST_a1d5cdbbb4b044319ade671733f495e8.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        
        public void Main()
        {
     string ParentFolder;
     int RetentionPeriod;
     RetentionPeriod = Convert.ToInt32(Dts.Variables["User::RetentionPeriodInDays"].Value);
     ParentFolder=Dts.Variables["User::ParentFolderPath"].Value.ToString();
     var folders = new DirectoryInfo(ParentFolder).GetDirectories();
     foreach (var Folder in folders)
            {
                if (Folder.CreationTime < DateTime.Today.AddDays(-RetentionPeriod))
                {
                   //MessageBox.Show(Folder.Name);
                    Folder.Delete(true);
                }
            }
            // TODO: Add your code here
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

Step 5:

Execute the SSIS Package and check the folder if older folders are deleted.

Fig 4: Delete Old Folder with Files in SSIS Package

As we can see that all the older folders are deleted with files and only one folder is left which was created today( 08/08/2018)