Caveman's Blog

My commitment to learning.

Archive for the ‘Sudheer Reddy Battula’ Category

SQL Server: Database deployment best practices

with one comment


Here are some of the SQL Server database deployment tips that have been very useful to me.

1. Store all your database object scripts as individual files under source control. This provides an excellent way to keep the database code organized and will also be very useful when auditing code changes. Another advantage would be that these scripts can be used to creating an instance of an application database with minimal amount of data.

2. Maintain a senior resource as a single point of contact to handle all SQL changes in non-DEV environments. This resource could also be responsible for code reviews and helping with code optimization. This provides an excellent way for enforce organizational best practices and it will also make it simple to keep track of changes in a given database environment.

3. Compare the database which is getting deployed with the last database that got updated in the software factory line. Comparing the schema and/or data will give you a contemporary view of the database state, thus helping you with a diligent way of preparing your deployment scripts. You can use tools like Redgate SQL-Compare, dbcomparer, etc for this purpose.

4. Make sure that the SQL scripts always successfully pass through the development, QA and stage environments before reaching the production/live environment. On a side note data in the production should be replicated (after scrubbing is necessary) to all the non-production environments as frequently as possible to achieve best level of code compatibility.

5. Always take a backup of the database, turn off replication agents and all SQL jobs, before deploying to the production/staging environment. This might add some overhead, but making this a habit will help with a error free, faster and a easy to restore deployment.

6. This may seem trivial by it is good practice to end every SQL Script with “GO”. SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad-hoc session or script if this is the first GO [1].

7. Maintain the deployment scripts with a naming convention. This will help in organizing the scripts for executing them in predetermined sequence and will give you a good idea about what a script is intended to do. I usually name my files as follows: naming the scripts with number in the following format give established the order of execution and also give you an opportunity to add new script(s) in between any two scripts.

Convention: Script_[Number]_[Number]_[ObjectName]_[Operation].sql

  • Script_100_001_table_employee_add.sql
  • Script_200_001_spGetAllEmployees_update.sql
  • Script_200_002_viewEmployeeDetails_update.sql

8. It is good practice to sandwich your SQL script in between a Transaction and Rollback/Commit block. One exception to this rule (if you will) would be when you have a script that performs a huge number of data manipulation operations. This could some times cause the transaction log to grow really big and cause the server to run out of space.

9. Create data validation scripts for the deployment changes. This way you do not have to wait for the application testing to respond to any errors.

10. I have used script consolidation for some of the deployments I was involved with. File consolidation helps in saving time when you have to execute a ton of script files. Here is the code for the SQL Merge tool.

11. Prepare a deployment document for establishing and organizing the deployment process with the following information; this will be useful with maintaining compliance with the Sarbanes-Oxley Act [2].

  • Deployment Schedule
  • Activity ownership
  • Pre-deployment activities
  • Deployment activities
  • Post-deployment activities
  • Rollback plan
  • Contact list

References:
1. MSDN Online
2. Sarbanes-Oxley Act

SQL Server: Index defragmentation

leave a comment »


DBCC INDEXDEFRAG: Index defragmentation is the process that reduces the amount of index fragmentation.This process does not hold any table locks long term while defragmenting an index, hence does not block running queries or updates. This is unlike the index building process or the re-indexing process when a table lock is enforced. The underlying table cannot be modified, truncated, or dropped while an online index operation is in process.To make sure that the index operation can be rolled back, the transaction log cannot be truncated until the index operation has been completed; however, the log can be backed up during the index operation. It is not suggested to use on very fragmented indexes.  Here is an example of MSDN as to what happens when an index is defragmented:

Figure: Index defragmentation in action [1].

DBCC DBREINDEX: Faster than dropping and re-creating, but during rebuilding a clustered-index, an exclusive table lock is put on the table, preventing any table access by users. And during rebuilding a non-clustered index a shared table lock is put on the table, preventing all but SELECT operations to be performed on it.

REBUILD INDEX: Best performance, but places an exclusive table lock on the table, preventing any table access by users and shared table lock on the table, preventing all but SELECT operations to be performed on it.

Note: According to Microsoft best practices, index defragmentation is most effective when an index has at least 8 pages. DBCC INDEXDEFRAG is one of the deprecated command. The equivalent contemporary command is ALTER INDEX REORGANIZE

Here is an award winning solution for the SQL Server Indexes and Statistics maintenance. You can download IndexOptimize procedure and use it as a comprehensive solution for this purpose. The SQL Server Maintenance Solution website seems to me like a must have for all DBA’s. Following is the syntax for rebuilding or reorganizing indexes with fragmentation on all user databases


EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',
 @FragmentationLow = NULL,
 @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationLevel1 = 5,
 @FragmentationLevel2 = 30

References:
1. Microsoft SQL Server 2000 Index Defragmentation Best Practices
2. SQL Server Mainenance Solution

PMP Certification – Lessons Learned

leave a comment »


In this post I am writing about my lessons learned from the PMP certification process. I have passively started my preparation 8 months prior to taking the exam by paying a lot of attention to the project management processes implemented at my work place. This helped me later in relating the theory to the actual implementation. Then came the expensive boot camp, which was useful for me because I am more like the classroom kinda guy. But you can easily find quite a few inexpensive and quality PMP Ed services on the web. Here is the concise version of my Lessons learned:

Study Plan and Material

Preparation: 4 months; Studying with a friend helped a lot (killed the boredom). We scheduled the exam on the same day and got certified.

Boot camp: Edwel; Instructor: Richard Perrin; He is an awesome dude. A staff member from Edwel helped me a lot with the application submission process

Books: Rita Mulcahy, Edwel Notes, PMBOK; In this order, reading the PMBOK guide at the end helped in grasping the dry material in that book.

Exams: Lehmann (62%), Simplilearn (70%), Edwel (75 – 80%), PMZilla 25 tough (68%), Exam central (82%), Rita (80% avg), PM Study (4 exams – 80 %) . Taking the practice exams at the public library was my strategy to getting used to the Prometric environment. It took me about 6 hours every time in writing the exam, scoring and then reviewing the exam.

Cheat sheet: ITTOs, Formulas, HR theories.

Additional material: Rajesh Nair notes (the best concise material I was able to find – Thank You sir!!!), ITTO acronyms.

Web resources : PMZilla, Deep Fried Brain, PM Prepcast, Youtube (Sir Ganttalot, Praizion, CorneliusFichtner)

Exam Experience

Thoroughly understanding and then memorizing the ITTO’s helped me to recreate the process flow during the exam. I was able to write down the ITTO acronyms in 15 minutes before the actual exam which gave me a sense of confidence, however I did not have to refer much to my notes during the exam. The exam questions seemed to be easy but were tricky. I had to concentrate very hard at carefully reading and understanding the lengthy questions. PM Study exams were the closest to the real exam (I wish they were a little more tougher). I finished answering all questions in 3:15 hrs, used the remaining 45 mins to review 50 questions. After a few nervous moments I was happy when I saw that I am a PMP.  I have thoroughly enjoyed this experience and learned a lot about how PMI suggests that project be management and implemented.

Best wishes.

Written by cavemansblog

January 14, 2012 at 9:52 pm

VS2010 + ASP.Net: Access to path is denied

leave a comment »


Problem: Access to path was denied when a dotnet app was writing an excel file to a folder of the website.

Solution: Grant access to the “Network Service” account to the folder that has permissions issue.

Written by cavemansblog

September 23, 2011 at 12:42 am

Drinking game – Fizz Buzz

leave a comment »


“Fizz Buzz” also called as “Bizz Buzz” is a drinking game. Players generally sit in a circle. The player designated to go first says the number “1”, and each player thenceforth counts one number in turn. However, any number divisible by three is replaced by the word bizz and any divisible by five by the word buzz. Numbers divisible by both become bizz buzz. A player who hesitates or makes a mistake is either eliminated or must pay a forfeit, such as taking a drink. [1]

Fizz Buzz is also used as a fun coding exercise like this: print all numbers from 1 – 100, while:

1. replacing the numbers divisible by 3 with the word “Fizz”.
2. replacing the number divisible by 5 with the word “Buzz”.
3. replacing the number that is divisible by 3 and 5 with the word “FizzBuzz”.

Following are four C# implementations of this problem:

            for (int i = 1; i <= 100; i++)
            {
                Console.WriteLine((i % 3 == 0 && i % 5 == 0) ? "FizzBuzz" :
                    ((i % 3 == 0) ? "Fizz" : (i % 5 == 0) ? "Buzz" : i.ToString()));
            }
            string s = "";
            for (int i = 1; i <= 100; i++)
            {
                if (i % 3 == 0)
                    s = "Fizz";
                if (i % 5 == 0)
                    s += "Buzz";

                if (i % 3 != 0 && i % 5 != 0)
                    Console.WriteLine(i);
                else
                    Console.WriteLine(s);
            for (int i = 1; i <= 100; i++)
            {
                if (i % 15 == 0)
                    Console.WriteLine("FizzBuzz");
                else if (i % 3 == 0)
                    Console.WriteLine("Fizz");
                else if (i % 5 == 0)
                    Console.WriteLine("Buzz");
                else
                    Console.WriteLine(i);
            }
            for (int i = 1; i <= 100; i++)
            {
                if (i % 3 == 0 && i % 5 == 0)
                    Console.WriteLine("FizzBuzz");
                else if (i % 3 == 0)
                    Console.WriteLine("Fizz");
                else if (i % 5 == 0)
                    Console.WriteLine("Buzz");
                else
                    Console.WriteLine(i);
            }

References:
1. Bizz Buzz – Wikipedia