SQL SERVER – Weekly Series – Memory Lane – #003 – Database Encryption

SQL SERVER - Weekly Series - Memory Lane - #003 - Database Encryption memorylane Here is the list of selected articles of SQLAuthority.com across all these years.  In this blog post, we will talk about database encryption and many other features. Instead of just listing all the articles I have selected a few of my most favorite articles and have listed them here with additional notes below it. Let me know which one of the following is your favorite article from memory lane.


SQL SERVER - Weekly Series - Memory Lane - #003 - Database Encryption memorylane-800x647

This was the first year of my blogging and lots of new things I was learning as I go. I was indeed an infant in blogging a few years ago. However, as time passed by I have learned a lot. This year was a year of experiments and new learning.

2007 – Database Encryption

Working as a full-time DBA I often encountered various errors and I started to learn how to avoid those errors and document the same.

ERROR Msg 5174 Each file size must be greater than or equal to 512 KB
Whenever I see this error I wonder why someone is trying to attempt a database that is extremely small. Anyway, it does not matter what I think I keep on seeing this error often in industries. Anyway, the solution of the error is equally interesting – just created a larger database.

Dilbert Humor
This was the very first encounter with database humor and I started to love it. It does not matter how many times we read this cartoon it does not get old.

SQL SERVER - Weekly Series - Memory Lane - #003 - Database Encryption dilbert5

Generate Script with Data from Database – Database Publishing Wizard
Generating schema script with data is one of the most frequently performed tasks among SQL Server Data Professionals. There are many ways to do the same. In the above article, I demonstrated how we can use the Database Publishing Wizard to accomplish the same. It was new to me at that time but I have not seen much of the adoption of the same still in the industry.

Here is one of my videos where I demonstrate how we can generate data with the schema.


Delete Backup History – Cleanup Backup History
Deleting backup history is important too but should be done carefully. If this is not carried out at regular interval there is a good chance that MSDB will be filled up with all the old history. Every organization is different. Some would like to keep the history for 30 days and some for a year but there should be some limit. One should regularly archive the database backup history.

South Asia MVP Open Days 2008
This was my very first year at Microsoft MVP. I had Indeed big blast at the event and the fun was incredible. After this event, I have attended many different MVP events but the fun and learning this particular event presented was amazing and just like me many others are not able to forget the same.

Here are other links related to the event:


Enable or Disable Constraint 
This is a very simple script but I personally keep on forgetting it so I had blogged it. Till today, I keep on referencing this again and again as sometimes a very little thing is hard to remember.

Policy Based Management – Create, Evaluate and Fix Policies
This article will cover the most spectacular feature of SQL 2008 – Policy-based management and how the configuration of SQL Server with policy-based management architecture can make a powerful difference. Policy-based management is loaded with several advantages. It can help you implement various policies for the reliable configuration of the system. It also provides additional administrative assistance to DBAs and helps them effortlessly manage various tasks of SQL Server across the enterprise.

SQLPASS 2009 – My Very First SQPASS Experience
Just Brilliant! I never had an experience such a thing in my life. SQL SQL and SQL – all-around SQL!

I am listing my own reasons here in order of importance to me.

  • Networking with SQL fellows and experts
  • Putting a face to the name or avatar
  • Learning and improving my SQL skills
  • Understanding the structure of the largest SQL Server Professional Association
  • Attending my favorite training sessions

SQL SERVER - Weekly Series - Memory Lane - #003 - Database Encryption SQLPASS407

Since last time I have never missed a single time this event. This event is my favorite event and something keeps me going. Here are additional post related SQLPASS 2009.

2010 – Database Encryption

Get All the Information of Database using sys.databases
Even though we believe that we know everything about our database, we do not know a lot of things about our database. This little script enables us to know so many details about databases that we may not be familiar with. Run this on your server today and see how much you know your database.

Reducing CXPACKET Wait Stats for High Transactional Database
While engaging in a performance tuning consultation for a client, a situation occurred where they were facing a lot of CXPACKET Waits Stats. The client asked me if I could help them reduce this huge number of wait stats. I usually receive this kind of request from another client as well, but the important thing to understand is whether this question has any merits or benefits, or not. I discuss the same in this article – a bit long but insightful for sure.

Error related to Database in Use
There are so many database management operations in SQL Server that requires exclusive access to the database and it is not always possible to get it. When any database is online in SQL Server it either applications or system thread often access them. This means the database can’t have exclusive access and the operations which required this access throws an error. There is a very easy method to overcome this minor issue – a single line script can give you exclusive access to the database.

Difference between DATETIME and DATETIME2
Developers have found the root reason of the problem when dealing with Date Functions – when data time values are converted (implicit or explicit) between different data types, which would lose some precision, so the result cannot match each other as expected. In this blog post, I go over very interesting details and the difference between DATETIME and DATETIME2

History of SQL Server Database Encryption
I recently met Michael Coles and Rodney Landrum the author of one of the kind book Expert SQL Server 2008 Encryption at SQLPASS in Seattle. During the conversation, we ended up how Microsoft is evolving encryption technology. The same discussion leads to talking about the history of encryption tools in SQL Server. Michale pointed me to page 18 of his book of encryption. He explicitly gave me permission to re-produce relevant parts of history from his book. Database Encryption is a very interesting topic and you can search about it online by keywords – “Database Encryption”.


Functions FIRST_VALUE and LAST_VALUE with OVER clause and ORDER BY

Sometimes an interesting feature and smart audience make a total difference in places. For last two days, I have been writing on SQL Server 2012 feature FIRST_VALUE and LAST_VALUE. I created a puzzle that was very interesting and got many people to attempt to resolve it. It was based on the following two articles:

I even provided a hint about how one can solve this problem. The best part was many people solved the problem without using hints! Try your luck! 

A Real Story of Book Getting ‘Out of Stock’ to A 25% Discount Story Available
SQL SERVER - Weekly Series - Memory Lane - #003 - Database Encryption outofstock
This is a great problem and everybody would love to have it. We had it and we loved it. Our book got out of stock in 48 hours of releasing and stocks were empty. We faced many issues and learned many valuable lessons. Some we were able to avoid in the future and some we are still facing it as those problems have no solutions. However, since that day – our books never went out of stock. This inspiring learning story for us and I am confident that you will love to read it as well.

Introduction to LEAD and LAG – Analytic Functions Introduced in SQL Server 2012
SQL Server 2012 introduces a new analytical function LEAD() and LAG(). This function accesses data from a subsequent row (for lead) and previous row (for lag) in the same result set without the use of a self-join. It will be very difficult to explain this in words so I will attempt a small example to explain this function. I had a fantastic time writing this blog post and I am very confident when you read it, you will like the same.

I hope you enjoyed this ride to memory lane about Database Encryption.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Memory Lane
Previous Post
SQL SERVER – Retrieving Random Rows from Table Using NEWID()
Next Post
SQLAuthority News – Microsoft SQL Server 2012 Service Pack 1 Released (SP1)

Related Posts

Leave a Reply