Thursday, August 30, 2012

Cryptography-SQL Server 2012

During the past few years with increasing attacks on systems and online portals, one of the most talked about areas has been cybersecurity and how to secure systems of all kinds. There have been regulations passed in different industries which call for increased compliance and state of the art security mechanisms.
Security is a vast field, one of the area which I would like to focus is on Cryptography. The area of cryptography deals with the practice of techniques for secure communication when there are presence of adversaries(folks who try to thwart secure communications/exchange of information). In the area of databases cryptography is gaining a lot of importance. SQL Server 2012 has options to add cryptography providers. There are different topics in the area of cryptography within SQL Server, each topic/category provides a level of encryption for the data. The mechanisms available in SQL Server are:

Transact-SQL functions
Asymmetric keys
Symmetric keys
Certificates
Transparent Data Encryption

Each of the above mechanisms offer varying level of encryption. Asymmetric keys offer a higher level of encryption than symmetric keys. In SQL Server, the cryptographic providers can be found in SSMS under the Security option in Object Explorer.


There are different functions available which can be found on Books Online Such as
CREATE SYMMETRIC KEY, for example:

CREATE SYMMETRIC KEY TestKey09 WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE Purchase04;
GO

In the above example an Encryption is done by AES 256 algorithm and and encrypts the key with the certificate Purchase04. Similarly there are other functions to create a asymmetric key and certificates.
The above options provide mechanisms to have more secure databases.


Thursday, August 23, 2012

SQL Server 2008- RAID Concepts...

One of the concepts that is relevant when it comes to Database Storage is how is the data organized on disks. In today's world we have SAN's which are very powerful , I am pretty sure lot of advances have been made in the areas of storage. One of the often looked into concept is the concept of RAID levels, how one should configure database storage and what RAID level to use for the Database Servers. Here is a link  which provided a good overview of different RAID levels. I am more of developer than a Database Administrator. The link below gives us a good introduction to the RAID concepts.

http://www.acnc.com/raidedu/0

Friday, August 17, 2012

SQL Server 2012-Generating Scripts...

One of the tasks that is often done in a database development environment is the scripting of database and using it for created test databases. Typically if one were script out the tables in the database in the SSMS, one would right click on the database and choose generate and publish scripts. In SQL Server 2012 one of the things i noticed was that when you right click on the database and choose tasks, there are some new options added. For the database scripts, right click on Generate scripts, a response window like the following opens up. In the first screen one chooses the objects that need to be scripted, in the second screen it says Scripting options on the side.

Set Scripting Options Window, Click Advance Button to Set Scripting Options.


When you observe initially you see options where the file needs to be saved and scripts need to be published. I was looking for where to set the scripting options, then i clicked on the advanced button,

Scripting Options Window


 the scripting options window popped up. In the scripting options one of the choices is Script for Server version there are a lot more choices. The choices range from SQL Server 2000 to SQL Server 2012, it includes SQL Server 2008 and SQL Server 2008 R2. This is option is very effective tool to create test databases and also there is an option generate scripts for the data as well.

Monday, August 13, 2012

MERGE-SQL Server 2008

In SQL Server 2008 ETL operations one of the common situations that is handled is the requirement for capturing change record information or what is more colloquially called  as Deltas. One of the common requirement is say on first day of a production ETL process records come in into a SQL Server Database they are inserted. Then from second day onwards the process needs to look for new records that have come in and also records which have changed from day one. In case of records that have changed from day one, we need to update the existing record and add the new changed record into the table. This type of scenarios can be handled by the MERGE statement available in 2008. For example there are two tables say:
Contact and Contact_Master. The Contact table gets truncated everyday and records are populated, the contact_master table has all the records which includes new and updated records. The users access the Contact_Master table through views in order to get the information based on a date range. The following command would help us insert new and modified records into the master table.

INSERT INTO contact_master (contactid,contactname,activebitflag,createddate)

SELECT contactid,contactname,activebitflag,createddate FROM
( MERGE contact_master S
USING contact L
ON S.contactid=L.contactid
WHEN Matched
AND S.activebitflag=1
THEN UPDATE SET S.activebitflag=0
WHEN NOT MATCHED BY TARGET
THEN INSERT (contactid,contactname,activebitflag,createddate)
VALUES (L.contactid,L.contactname,1,GETDATE())
OUTPUT $action,1 AS activebitflag,L.contactid,L.contactname,GETDATE() as createddate
) AS ActiveInserts (Action,activebitflag,contactid,contactname,createddate)
WHERE Action='UPDATE';

There are different parts to the SQL statement: The Merge statement has a MATCHED section which handles updates of records matched between contact and contact master. The NOT MATCHED section handles the insert of new records.There is OUTPUT clause in MERGE Statement that can be useful, there is a variable called $action which holds the keywords such as INSERT,UPDATE and DELETE, this can be very useful especially when one wants to know how many records got inserted.updated and deleted as part of the Merge Statement. In the above statement, the results of the merge are captured in a derived table. In the derived table all the records which were flagged as UPDATE as part of initial merge, get inserted as new records into the table, so a record which already exists gets updated and also gets inserted with a different flag. This is one of the strategies used to capture delta information.

Monday, August 6, 2012

SQL Server 2012-Execution Plans

One of the most often used and very valuable tool available for SQL Server Developers is the Show Execution Plan feature available in SSMS. The execution plan would be used every developer in order to understand the execution plans generated by the queries and more importantly is used for performing troubleshooting on queries that are not performing well. In SQL Server 2012 there is a new hint which is available in the Execution Plan. In order to demonstrate the concept I created a simple table called National.
Here is the structure/script of the table created.

CREATE TABLE [dbo].[National]
(
[NationalID]    Varchar(50) NULL,
[EntityID] [int] NULL
)

Once the table was created I inserted some sample values into the table. Once this was complete I was ready ro run some queries and look at the execution plan. The first query I ran was

SELECT [NationalID],[EntityID] FROM [dbo].[National]
WHERE [NationalID]='1'

When I ran the above query there was nothing abnormal with the execution plan, kind of saw what was expected.

I changed the above query to test the NationalID by passing a integer value, so i modified the query and ran it as

SELECT [NationalID],[EntityID] FROM [dbo].[National]
WHERE [NationalID]=1



When I looked at the execution plan there was  one thing I noticed, there was an "!" point next to the SELECT icon with COST 0%. I looked at the "!" and had a message saying Type Conversion in Expression.

meaning there was an implicit conversion performed since NationalID is defined as VARCHAR and this may affect "Cardinality Estimate in query plan cache. This is very interesting as if this were a bigger table with large number of rows such implicit conversions could cause performance issues.




Friday, August 3, 2012

SSISDB Catalog-SSIS 2012

There have been lot of changes made in SSIS 2012, some of which I have blogged earlier. One of the key areas of change has been the deployment section. When a Integration Services 2012 is configured along with the Integration Server, there is a new Catalog which is the key point for getting information about the SSIS packages deployed to the Integration Server. There are several categories of SSIS packages which have been addressed in the SSISDB catalog. This is different from the system tables that are available in MSDB database. The focus of the SSIDB catalog is to enable the administrators to handle the SSIS packages better. Let us focus on the different tools available to focus on categories of troubleshooting.
In case one needs to get information about the performance and execution information of SSIS package, we have the following catalogs that can be used.

catalog.executions
catalog.execution_component_phases
catalog.executable_statistics
dm_execution_performance_counters.

All of the above objects provide vital information about an SSIS package. Currently SSIS is being used in lot of big ETL operations, so getting information about the packages in terms of execution speed is very vital. As always performance of ETL is very critical when it comes to data availability. The other area of troubleshooting category is the ability to Add,Query and remove data taps in a SSIS package Data flow.
Here are the objects that can be used.

catalog.add_data_tap
catalog.add_data_tap_by_guid
catalog.remove_data_tap
catalog.execution_data_taps
catalog.execution_data_statistics

In Summary the addition of the SSISDB datalog to SSIS 2012 provides a lot of productive tools in order to administer SSIS packages.