Sunday, March 10, 2013

Screencast: Working with Snippets in SQL Server 2012

Welcome to another screencast of the "SQL Server 2012 Screencast Series"!

In this short screencast we talk about code snippets in SQL Server 2012 Management Studio.

Code Snippets are being used for many years now by developers in Integrated Development Environments (IDEs) like Microsoft Visual Studio. Their benefits vary. Some of them are:
  • Code Reuse
  • Easier and Faster Programming
  • A method of defining coding standards
SQL Server 2012 provides the functionality of Code Snippets similarly as offered in Visual Studio thus making writing code in SSMS 2012 an even richer experience.

In the screencast, you can see how you can easily create snippets using my freeware tool "Snippets Generator for SQL Server 2012" and how to import it and use it in SSMS 2012.

You can watch the screencast below or at the "SQL Server and .NET TV":

Read more on this article...

Sunday, February 24, 2013

Encrypting SQL Server Databases

In corporate environments there is always the requirement/policy of data protection. Data is the most valuable asset in every organization, after its human resources of course, as it is with data where the organization’s business processes run and produce results.

SQL Server is one of the leading data platforms worldwide and as such, from SQL Server 2008 (Enterprise Edition) and later, all the necessary mechanisms for ensuring data protection are available within the Database Engine and can easily be used.

SQL Server 2008 introduced Transparent Data Encryption (TDE); a mechanism allowing the DBA to easily encrypt databases without affecting their operation as it allows full transparency to the database users and applications. Based on TDE, the DBA encrypts the database using a master key and a certificate. The user accesses the database transparently as the encryption is automatically maintained on the Database Engine-level and in the case of a potential data theft, i.e. someone illegally copies the database files (data and log files), when he tries to access the database it will not be allowed because the database is encrypted. You can find a simple example on how you can use Transparent Data Encryption in one of my older posts here.

As mentioned above, Transparent Data Encryption is available in SQL Server 2008 or later. However, if you would like to achieve database encryption in earlier versions of SQL Server, you can do it by using a special feature of Windows on the file system-level, that is Encrypting File System (EFS) which was introduced in version 3.0 of NTFS and provides file system-level encryption. EFS is available from Windows 2000 onwards. The way EFS works is transparent, yet very powerful. A high-level description of its operation is the following.

For encrypting files:
1. With a symmetric file encryption key (FEK) generated for this purpose, EFS encrypts the file.
2. Then it encrypts the FEK using the active Windows user's public key.

For decrypting files:
1. EFS decrypts the encrypted FEK using the Windows user's private key.
2. Then it decrypts the encrypted file using the decrypted FEK.

So, in the case you would like to encrypt a database in SQL Server 2005 or earlier using EFS you could achieve it as follows:

1. Log in to Windows using the user account that is used by SQL Server Database Engine.
2. Log in to SQL Server Management Studio (SSMS).
3. Detach the database from the SQL Server Instance.
4. Encrypt the database files (data and log files) on the file system-level ([File Properties] - [Advanced] - [Encrypt contents to secure data]).
5. Verify the encryption of the database files using the Windows command "cipher.exe" in the folder that contains the database files. It will return the value "E" for the encrypted files.
6. [CRITICAL] Export the Windows user's personal certificate with the private key ([Start] - [Run] – “certmgr.msc”) and store it on a safe location (i.e. removable storage).
7. Attach the database back to the SQL Server Instance.
8. If any problems, try to restart the SQL Server Instance.

Be extremely cautious when running the above procedure because in the case you change the user account that runs the SQL Server Instance and the new user is not authorized to access the database files, then you will lose access to your data. Always backup the user’s certificate/private key which can be used to decrypt the encrypted files. Always be careful when using data encryption mechanisms.

Also, if you encrypt database files and attempt to start the SQL Server service as any user other than the user that encrypted the database data/log files in the first place, the instance will not start and/or your database will be set to suspect.

A last thing you should have in mind when using EFS for encrypting SQL Server 2005 or earlier databases, is that EFS will affect the performance of SQL Server as there is an overhead whenever decrypting the underlying database files. There are many parameters that can be taken into consideration when it comes to the SQL Server performance degradation such as: the supporting storage system and its RAID levels, the OS configuration and more.   

Related Microsoft Articles:

Read more on this article...

Wednesday, January 30, 2013

T-SQL Tip: Retrieving Security-Related Info for SQL Server Logins

-- Retrieves Security-Related Information
-- for all the SQL Server Logins
-- SQL Server versions  supported: SQL Server 2005 or later
[name] as LoginName,
LOGINPROPERTY ([name] , 'DefaultDatabase') as DefaultDatabase,
LOGINPROPERTY ([name] , 'DaysUntilExpiration') as DaysUntilExpiration,
(CASE ISNULL(LOGINPROPERTY ([name] , 'IsExpired'),0) WHEN 0 THEN 'False' ELSE 'True' END) as IsExpired,
(CASE ISNULL(LOGINPROPERTY ([name] , 'IsLocked'),0) WHEN 0 THEN 'False' ELSE 'True' END) as IsLocked,
LOGINPROPERTY ([name] , 'PasswordLastSetTime') as PasswordLastSetTime,
LOGINPROPERTY ([name] , 'PasswordHashAlgorithm') as PasswordHashAlgorithm
FROM master..syslogins

For more info, check out the following links:
Read more on this article...

Friday, January 18, 2013

Snippets Generator v2.0 is Out!

I'm very happy to announce the general availability of version 2.0 of Snippets Generator, one of my open-source projects in CodePlex.

Snippets Generator allows the user to easily create T-SQL snippets for SQL Server 2012! You can create a snippet from scratch or by using one of the snippet templates provided by the tool.

Snippets Generator started as one of my blog posts back in January 2011 and moved onto CodePlex in December 2011. The first stable version was released on August 2012 and today, I'm releasing Snippets Generator v2.0! Of course, as always, the tool is open-source and free to everyone as it is under the Microsoft Public License (Ms-PL).

This new version (2.0) includes the following improvements/enhancements:
- Improved Graphical User Interface: More user-friendly environment.
- Snippet Templates: A library of T-SQL snippet templates that can be used as a starting point for creating your own snippets.

Snippets Generator v2.0 - Main Screen

Snippets Generator v2.0 - Snippet Templates Library

Read more on this article...

Monday, December 31, 2012

Selected Tweets of Year 2012!

Hello friends,

Another year has come to an end and as being faithful to the "tradition" of posting in the last day of every year my selected tweets for the year that ends, here I am, posting the "Selected Tweets of Year 2012!".

It was another great year of interaction with you, the SQL Server Community. We saw together and discussed exciting new releases such as SQL Server 2012 and other related technologies, we discussed common issues and ways to overcome it, as well as interesting approaches to utilizing existing technologies for achieving great things.

It is a privilege to communicate with you, via all possible channels like blogs, forums, offline and online events, conferences and of course, Twitter! Here are the tweets!

Blog Posts/Articles and Announcements
[Blog] Listing Directory Contents using T-SQL -  #MVPBuzz #in #fb #sqlserver

[Blog] How to retrieve size information for all the tables in a #SQLServer #Database -  #MVPBuzz #in #fb

[Blog] Retrieving Log Space Information within a #SQLServer Instance -  #in #fb #MVPBuzz

[Blog] Retrieving Log Space Information within a #SQLServer Instance - The Stored Procedure! -  #in #fb #MVPBuzz

[Blog] Could not load file or assembly 'Microsoft.SqlServer.Smo, Version=, ... -  #in #fb #MVPBuzz

[Blog] SQL Server 2012: Sequence Objects -  #MVPBuzz #in #fb #SQLServer

Want to know more on Contained Databases on #SQLServer 2012? Read my article on @MVPAward blog!  #MVPBuzz #fb

[Blog] Migrating to a Contained Database in SQL Server 2012 -  #MVPBuzz #fb #Microsoft #SQLServer2012 #Denali

[Blog] New Post Series: T-SQL Tips -  #MVPBuzz #SQLServer #fb

[Blog] T-SQL Tip: Retrieving Database File Sizes -  #MVPBuzz #SQLServer #fb

[Blog] T-SQL Tip: Retrieving SQL Server Instance-Related Information -  #MVPBuzz #SQLServer #fb

[Blog] Getting the Disk Usage Statistics for all Tables in a Database - #MVPBuzz #SQLServer #fb

[Blog] T-SQL Tip: How to Create a Simple Linked Server Between SQL Server Instances - #MVPBuzz #fb

Proudly Presenting: SQL Server 2012 - The Database Engine [TRAILER]:  - Enjoy!!! #MVPBuzz #sqlserver #fb

[Blog] SQL Server 2012 - The Database Engine [TRAILER] -  #MVPBuzz #sqlserver

[Webcast] SQL Server 2012: The Database Engine -  #MVPBuzz #fb #sqlserver #Denali

[The SQL Server and .NET TV] Webcast on SQL Server 2012: The Database Engine -  #fb #MVPBuzz

[Blog] There was an unexpected failure during the setup wizard - #SQLServer #MVPBuzz #fb #Troubleshooting

Blogged: Argument data type ntext is invalid for argument ... -  #SQLServer #MVPBuzz #fb

Blogged: Handling Disk Space Issues During Heavy Index Rebuild Operations -  #SQLServer #MVPBuzz #fb

[Blog] Internal Query Processor Error: The query processor could not produce a query plan -  #SQLServer #MVPBuzz #fb

[Blog] T-SQL Tip: Getting the File Locations for all DBs in a #SQLServer Instance -  #MVPBuzz #fb

[Blog] T-SQL Tip: Getting all the Records from all Tables in all User Databases -  #MVPBuzz #SQLServer #Tips #fb

[Blog/Screencast] Migrating to a Contained Database in #SQLServer 2012 -  #MVPBuzz #fb

[The SQL Server and .NET TV] Migrating to a Contained Database - #SQLServer #MVPBuzz #fb

April 1st 2012: #SQLServer 2012 generally available!!!

I'm a #SQLServer #MVP for the fourth year in a row! A HUGE thanks to you, the Community for supporting my efforts! #MVPBuzz #in #fb

[Blog] #Microsoft #SQLServer #MVP for the fourth year in a row! -  #MVPbuzz #in #fb - thank you all!

My blog has reached 200.000 visits! First step in celebrating it? Webcast on #SQLServer 2012 coming up this month! #in #fb #MVPBuzz

The first stable version of Snippets Generator for #SQLServer 2012 now available! - Get it on #CodePlex!  #MVPBuzz #fb

[Blog] Snippets Generator v1.0 - Stable Version Now Available! -  #MVPBuzz #fb #Community @SQLServer

My YouTube Channel (The SQL Server and .NET Blog TV) got some lifting! :) Check it out! - … #MVPBuzz #SQLServer

My blog now has more than 250.000 visits! Let the celebrations begin!  :) #MVPBuzz #SQLServer #fb

[Blog] Celebrating 250.000 Visits! -  - Thank you #SQLServer #Community! You Rock!!! #fb #MVPBuzz

Microsoft announces project name "Hekaton"! In-memory processing for OLTP! Wow! Live demo #now:  #MVPBuzz #SQLServer

#SQLServer 2012: Learn more now from The SQL Server and .NET TV!  #Webcast #MVPBuzz #fb

Hello friends! Working on something massive! Yes, it is #SQLServer-related! ;) More news soon! #MVPBuzz #fb

[Personal Website Updated] Webcasts and Screencasts: | Talks and Publications:  #SQLServer #MVPBuzz

[The SQL Server and .NET TV] Subscribe and stay up to date with the latest Webcasts on #sqlserver!  #MVPBuzz #fb

[Celebrating 250.000 Visits!] Q&A Session on SQL Server -  #MVPBuzz #sqlserver #fb

Perfect documentation on #SQLServer #Database Engine Locking:

My warmest wishes for a happy new year! A year full of health, love and creativity! One thing is for sure: together, we can make 2013 even more awesome!

Read more on this article...

Thursday, December 20, 2012

Screencast: Migrating to a Contained Database in SQL Server 2012

Contained Databases is an exciting new feature shipped with SQL Server 2012.

Among other, it introduces a more robust security model where you can access databases without using SQL Server Logins. You just need to use the Contained User and you can have full access to the database.

This allows you to easily migrate Contained Databases to other instances of SQL Server 2012 without having to worry about any SQL Server logins, orphaned users, etc., for the simple reason that SQL Server Logins are not necessary when using Contained Databases!

I have just prepared a screencast with live demos showing how you can migrate an existing database to a Contained Database and thus achieve portability.

I'm making the screencast available here as well as on "The SQL Server and .NET TV":

Also, below you can access the slides.

Wishing you Happy Holidays and a Happy New Year!!!
Read more on this article...

Tuesday, December 18, 2012

T-SQL Tip: Getting all the Records from all Tables in all User Databases

Yeah, I know, who would ever want to do that (!) but the purpose of this post is not to illustrate how you can actually get all the records from all tables in all user databases (even though it shows how to do it) but rather to provide an example of how we can dynamically generate T-SQL statements that can be used for undertaking various multi-database operations.

-- Dynamically builds  T-SQL statements for retrieving all the records
-- from all the tables in all user databases
-- SQL Server versions  supported: SQL Server 2000 or later
exec sp_MSforeachdb
@command1="IF DB_ID('?') > 4 print 'use ?;'",

  1. In Query Window in SSMS, right click - Results To: Results to Text
  2. Run the above query and you will get "SELECT *" statements for each column of each table in each user database (Database ID > 4). By executing the produced code you get all the data records.
*Note: I could directly execute the produced code but when you run multi-database operations, it is wiser to first review the SQL statements, hey, you never know! :)
For more info, check out the following links:
Read more on this article...

Monday, December 17, 2012

T-SQL Tip: Getting the File Locations for all DBs in a SQL Server Instance

-- Dynamically builds  T-SQL statements for retrieving the file 
-- locations for all the databases in the SQL Server Instance
-- SQL Server versions  supported: SQL Server 2005 or later
SELECT 'use '+ [name]+'; select '''+[name]+''' as DBName,cast ([name] as varchar(45)) as LogicalFileName,cast (filename as varchar(100)) as FileName from sysfiles;' as SQLStatement FROM master.sys.databases

Details: Just execute the statements generated from the above query and you will receive the file locations for all the databases in the SQL Server instance.

For more info, check out the following links:
Read more on this article...

Saturday, December 15, 2012

Internal Query Processor Error: The query processor could not produce a query plan

OK folks, it did happen a few days ago to get this error message:

Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.

I was trying to execute the following query:

FROM dbo.tbl1
tbl2ID=(SELECT id FROM dbo.tbl2 WHERE Code='Code1')
tbl3ID=(SELECT id FROM dbo.tbl3 WHERE Code='Code2')

As you can see, in the above query I'm using two subqueries for getting some keys for use in my main query. In some cases you might get the aforementioned error message. However, this is not always the case as it depends on many factors and not only on the query itself.

Anyway, in order to resolve the issue I just had to re-write the query. The re-written query is the following:

SELECT  t1.* 
FROM dbo.tbl1 t1, dbo.tbl2 t2, dbo.tbl3 t3
AND t1.tbl3ID=t3.ID
AND t2.Code='Code1'
AND t3.code='Code2' 

As you can see, I just removed the subqueries and replaced them with additional joins on my main query. Now my query works like a charm!

*** Before changing your query or doing anything else, first, make sure that your instance of SQL Server has the latest service pack installed.
Read more on this article...