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.
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.
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:
- South Asia MVP Open Day 2008 – Goa
- South Asia MVP Open Day 2008 – Goa – Day 1
- South Asia MVP Open Day 2008 – Goa – Day 2
- South Asia MVP Open Day 2008 – Goa – Day 3
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
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.
- SQL PASS Summit, Seattle 2009 – Day 1
- SQL PASS Summit, Seattle 2009 – Day 2
- SQL PASS Summit, Seattle 2009 – Day 3
- SQL PASS Summit, Seattle 2009 – Day 4
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”.
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:
- Introduction to FIRST_VALUE and LAST_VALUE
- Introduction to FIRST_VALUE and LAST_VALUE with OVER clause
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
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)