In this book I have tried to cover every single piece of information that might requires for installing and configuring SQL Server HA option like Clustering, Replication, Log Shipping and Database Mirroring...
Recently Microsoft has released MS SQL Server 2008 R2 Management Pack, it can monitor the Microsoft SQL Server components such as database engine, databases and SQL Server agents. MS SQL Server 2008 R2 Management Pack has few enhancements in this release including:
Simplified and streamlined customer configuration and deployment
Lower cost of operation due to improved health model and reduced false alarms
Meets low-privilege constraints needed for financial and medical industries
New rules, monitors, and knowledge improvments
Easy deployment
Discover and monitor SQL Server 2005, SQL Server 2008 and SQL Server 2008R2
You can download the SQL Server Management Pack here - Link
Hemantgiri S. Goswami | www.sql-server-citation.com
There are tons of useful resources available for SQL Server but the link I am going to share with all of you today is really the greatest amongst - it's a SQL Server Developers Training Kit. Yes, MS has made the updated version available for we all and the best part is - it's free.
The key benefits are:
How to build applications with unique and new features available in SQL Server 2008 and SQL Server 2008 R2
It contains #55 videos, # 32 presentations, #39 demos and #24 hands-on-labs
Content for SQL Server beginner developers
Doesn't it seems a bundle full of great resource on SQL Server 2008? Here is the complete list of what it includes if you still wants to check before you download it from here.
Recencly Microsoft has released the Best Practices Analyzer (BPA) for SQL Server 2008 R2. BPA is a tool which helps Administrators to audit there local and remote SQL Server instances sagainst system events, logins, permissions and metadata for recommendation if it doesn't follow MS best practices for SQL Server.
But, you have to have pre-requsite installed on your system before you install BPA for SQL Server 2008 R2, they are:
In this quarter Microsoft Press has gift a free eBook on "Introduction to SQL Server 2008 R2", a book written by Microsoft SQL Server MVP Ross Mistry and Stacia Misner.
This book has 10 Chapters and 216 pages which covers
Enhancements in SQL Server 2008 R2
Multi Server Administration
Data Tier Application
HA and Virtualization Enhancements
Server Consolidation and Monitoring
Data Warehousing
Master Data Services
Event Processing with StreamInsigh
SSRS Enhancements
Self-Service Analysis using PowerPivot
Isn't this list looking great? I just have downloaded this book in PDF format, alternatively you can download this book in XPS format as well.
A month ago I and my friend were discussing IT budget, and it becomes very interesting when we started discussing virtualization. Let me brief you the case: with the growing business usage of IT infrastructure increases and that increases the usage of energy and space. Apart from this, there is another side; are those resources getting utilized at optimum level? Well, at some cases yes but in most of the cases it doesn't - source.
One would think if this really requires? I would say, yes, it is required. Let's take an example here; company Ax Inc is managing its 200 servers at its datacenter at Bangalore. Let's say a single server which utilized at 60% consumes 350 watts, now let us calculate -:
Unit
Rate
Amount
Up to 100
2.90
290
101 to 200
3.60
360
201 to 300
4.20
420
301 to 400
4.45
222.5
Above 401
4.70
-- NA --
Bill generated for single server
1292.5
(Figure 1)
Further, calculating this amount for 200 servers would make 258500/- INR per month (US$ 5609.45 per month, as of 23-Feb-2010 according to CurrenciesDirect). If we also consider the bill for cooling systems and miscellaneous expenses it would be much more. In addition, add licensing costs, resources which manage and maintain this IT Infrastructure will add some extra burden.
Is there a solution for this? Yes, there is a solution for this – Virtualization. Virtualization is great technology which helps us to utilize our IT infrastructure at the max and lower down the costs. You must be wondering how this can be possible, and if it is; how to implement this solution!!! As I said above, our discussion was turned very interesting – the solutions are to implement "Virtualization". Virtualization – a methodology of dividing the resources of a computer into multiple execution environments, by applying one or more concepts or technologies such as hardware and software partitioning , time-sharing, partial or complete machine simulation, emulation, quality of services, and many other.
There are bunch of benefits that are attached to with implementing Virtualization, and related product i.e. lowering power consumption, ease of deployment, maintenance and updates, and consolidation. Let me brief you about each.
Lowering power consumption: As I shown you in the above calculation in figure 1, 200 servers would generate whooping electricity bill - 31,02,000/- (2585000*12) annually. This is just an example and that to without considering cooling system.
Now, if you have hardware with Hyper-V you will be able to have your multiple servers on it. You can even host with Linux. And, naturally with lower numbers of servers your electricity bill goes down significantly.
Ease of Deployment: With the use of Microsoft Virtual Server Manager, System Center Virtual Machine Manager you will be able to optimized your physical and virtual resources.
Centralized virtual machine deployment and management
Intelligent placement of virtual machines
Integrated performance and resource optimization
Fast physical to virtual and virtual to virtual conversion
Leverage and extend existing storage infrastructure
Scriptable using PowerShell
Live migration
Hot add/remove of VHDs, pass-through disks
Server consolidation
Maintenance and Updates: The real beauty is that it makes administrator's life easy. With the use of System Center Configuration Manager and Operation Manager you will be able to easily do our maintenance and updates easily.
Comprehensive application and service level monitoring
Update management
Software distribution
Operating system deployment
Ease of Backup
Conclusion: Virtualization helps a lot to save energy by consuming less power, save resources, and make IT administrator's life easy by server consolidation, deployment of patches and updates and even migration. As I have said in my previous post about embedding security to your product or piece of code, you may have the same tight security mechanism implemented with Virtualization- refer the document. Microsoft's own websites like MSDN and TechNet which has 1~3 million hits a day runs 100% on Hyper – V.
Cumulatively all this effort saves a lots of money by reducing IT costs, this makes real sense to me because it makes my life easy and as IT cost is down my client is happy with the solution.
Security!! A word – that everybody want, whatever it may for - a life, money or a job. In an IT world this term is often used, and asked by the client, the customer or the end user because it is closely attached to the information they are providing or storing or exchanging with each other. It can create a chaos if an ounce of the information is leaked or spread publicly; refer the case about classified US Military info available, reported in July-2007 by Network World.
So, what we really have to care about that every piece of work that we do should be secure - by default, and this applies to every technology. That means whatever we work on, we should follow and implement the security policies to build the robust and secure product. Recently, I was researching about security and I found a link about Microsoft Security Development Lifecycle (SDL). It is since 2004 that Microsoft has made SDL mandatory to follow for its entire product range. I would advise you to follow the same when you make a product for a business environment, communication software such as messenger or dealing with the sensitive information.
Since 2004, most of the product from Microsoft has built-in features to tighten security. This applies to SQL server as well; you can see that SQL Server 2005 has great security features i.e. you can enforce password policy on your database server to ensure that the passwords that your users are crating are strong. Apart from this now it is not necessary to assign system administrator rights to run profile, the only permission user requires is ALTER TRACE to run profiler. You may refer my article on how to secure SQL server for more pointers.
Microsoft publish security bulletin every month which will give advice you if you required installing any patch on your system. And to make this procedure easy for Windows operating systems and Office product Microsoft is offering Security Compliance Management Toolkit Series. Also to keep your system free from Viruses or malware Microsoft Assessment and Planning (MAP) toolkit is useful.
So, all in all we all should be more cautious when we are dealing with sensitive data and should improve by following SDL.
Last to Last week we discussed how Null Values can cause a trouble in Common Mistakes in SQL Server – Part 4. This I will discuss about Auto Growth feature of SQL Server, and how it can impacts us.
What is Auto Growth?
Auto Growth is a feature that allowed database files (primary, secondary and log) to expand when database file becomes full - without manual intervention.
Auto Growth feature is really useful when we do not want to increase the size of database files manually. There are two ways you can set auto growth – using SQL Server Management Studio (SSMS here after) and T-SQL. Auto Growth can be configured – In Percent and In Megabytes.
How Auto Growth can impact us?
Now that we know what Auto Growth feature in SQL Server is; I’ll describe how this feature can impact us badly if we do not configured this option carefully . Below is the table that will give you an idea what would happen if database file reaches to its threshold value and it needs to expand!!
See the calculation below when Auto Growth option is configured with in Percentage:
Configured to grow "In Percentage"
File Size (before growth)
Growth - 10%
File Size (after growth)
1st time
5000
500
5500
2nd time
5500
550
6050
3rd time
6050
605
6655
4th time
6655
665.5
7320.5
5th time
7320.5
732.05
8052.55
6th time
8052.55
805.255
8857.805
7th time
8857.805
885.7805
9743.5855
8th time
9743.5855
974.35855
10717.94405
9th time
10717.94405
1071.794405
11789.73846
10th time
11789.73846
1178.973846
12968.7123
You will notice that the database growth is exponential when it is configured with Percentage i.e. 10%. This is because this is calculated in cumulative manner; the value I have used here is 20 times lower than what we used to work on production systems generally. If we set Auto Growth in Percentage it will occupy our HD space unnecessarily and creates fragmentation. Moreover when database files are expanding you will notice high volume in CPU spikes and I/O cycle.
What is the best practice for Auto Growth option?
Best practice is to configure Auto Growth option in static value. See the sample calculation below:
File Size (before growth) MB
Growth - 500MB
File Size (after growth) MB
1st Time
5000
500
5500
2nd Time
5500
500
6000
3rd Time
6000
500
6500
4th Time
6500
500
7000
5th Time
7000
500
7500
6th Time
7500
500
8000
7th Time
8000
500
8500
8th Time
8500
500
9000
9th Time
9000
500
9500
10th Time
9500
500
10000
Database grows in a controlled manner when Auto Growth is configured with the static/fix value in comparison to Percentage i.e. 10%. The value I have used here is 20 times lower than what we used to work on production systems generally.
It is true that now a day’s storage costs are lower down for desktops or home systems but it is really very costly when it comes to servers and data centers and this scenario will just add overhead to costing for storage solutions. So, the best bet is to configure Auto Growth with static value.
I would advise to configure Auto Growth option to static value only.
Last week we discussed advantages of recompilation in Common Mistakes in SQL Server – Part 3. This week I will discuss the Null Value and how it can cause an issue!!
What is NULL?
Null is the value which indicates the value is unknown. Null is different than zero or an empty string, as a zero or empty string indicates that the value is known (refer http://en.wikipedia.org/wiki/Null_%28SQL%29 for further reading.)
Why should we avoid NULL values?
In one of my previous projects I was asked to perform some tests and do analysis on a specific database which had a tremendous growth rate, and it seemed that either the estimation related to growth was wrong, or the application inserts data more often than is expected. The database growing more than 300% in just 2 years wasn’t a good sign - something was really wrong. After I finished the analysis I found that there was a single table which had significantly contributed to the database size - that table itself was 450+ gigs in size; it had 217 columns and more than 50 million records, most of the columns allowing null values, which is where the problem was.
There is a misconception that if we have the NULL values in a table it doesn't occupy storage space. The fact is, a NULL value occupies space – 2 bytes. If we do not consider this point at the estimation stage we will have trouble with the growth of the database. Sometimes, however, application design demands some columns to be there for future use, and in this case, there will be NULL values.
Let me give you an example – create a table “tblEmployee” having thirteen columns of fixed length data type. I will add 150000 records and check the size of table. Remember, we are not creating a primary key/clustered index - thus this will be the Heap.
1. Specify the number of rows that will be present in the table
Num_Rows = 150000
2. Specify the number of fixed length and variable length columns:
6. Calculate the number of rows per pages (8096 free bytes per page)
Rows_per_page = 8096 / (122+2) = 40.27 = 40
7. Calculate the number of pages required to store all the rows
Num_Pages = Num_rows / Rows_per_page
Num_Pages = 150000 / 40 = 3750
8. Calculate the amount of space required to store the data in the heap (8192 total bytes per page)
Heap Size (bytes) = 8192 * 3750 = 30720000 Bytes
The size of the table tblEmployee is 30776KB which means storing 150,000 records would occupy 30 MB. Now, lets calculate this for 50 million records - you will get 9990 MB ((50000000/150000)*30). Remember we have calculated this for 13 columns only; if your table has more columns the size could be bigger than this. Download the demo script here
The above example shows how your estimation will be wrong if you do not consider NULL values while estimating database size. To estimate your table size correctly, you must consider this point, and you should try to narrow down the possibility of having NULL values in your tables.
Last week in Common Mistakes in SQL Server - Part2 we discussed the advantages and disadvantages of using Identity columns. In this article I would like to shed some light on the advantages of recompile , and in what cases you should attempt recompilation!
Generally, we create views and stored procedures (“proc” here after) to avoid the re-write T-SQL which is called often by an application or on ad-hoc basis. And, there is strong reasoning to do so: both - views and procs provides am edge over executing T-SQL as they can reuse cached plan stored in the system table syscacheobjects ( you can query it using DMV sys.syscacheobjects).
We are aware that once we have created a view and/or proc and call it for the first time it gets compiled and executed(and the execution plan is stored in the syscacheobjects system table). Then, every time we call this view or proc, it uses the same cached execution plan. By doing this, SQL Server Engine is reducing the overhead of recompiling the same set of T-SQL.
What exactly is an Execution Plan?
There are number of connections that can be made to the database server, from one or more different locations, and each of them are querying the database server to return some data. The specific data structure is called an execution plan (i.e. select * from Product where ProductSubCategoryId = 4).
What the SQL Server engine does here is - if the T-SQL came from a view or proc, it searches for the cached execution plan for the same T-SQL statement. The SQL Server Engine will reuse the plan if it is found ; otherwise, it will compile and execute the statement. For example we have to query Product table where SubCategoryId is 4 and 11 and this query is being called frequently( 500 times a day). If you query the syscacheobjects system table, or query DMV sys.syscacheobjects, you will notice that the Usecount value is not incremented.
Now, if you were to, create a view and execute it 5 times, and then query DMV sys.syscachedobjects again, you will notice that the Usecount value has been incremented. This is because the SQL Server engine has used the cached execution plan.
Why should we recompile the proc or view?
There are certain events which invalidate the cached execution plan, and it is advisable to recompile* a proc or view if one of these events occur:
·Schema changes / alter table
·Alter index or drop index
·Calling sp_recompile or using the ‘with recompile’ or option (recompile)when executing the T-SQL
·Large insertion or deletion of data in the tables
·If you call DML and DDL in single set of T-SQL
·Change the value of SET option
*Note: Recompilation is an expensive operation, but it will help to avoid use of old/invalid plans.
Let us query the Product table in the AdventureWorks database; note that it uses the clustered Index Scan (PK_Product_ProductId)
Now we will create a non-clustered index on column ProductSubCategoryId in Product table.
Now we will create a procedure that uses the same select statements that we have used earlier (CMPart3_Proc1)
As we have the non-clustered index on the column ProductSubCategoryId, the relational database engine should use the non-clustered index. Let’s check if it does it by executing the query
Let's create a proc here, and execute it. If you notice, the database engine still uses the same execution plan even though we have a newly created index on the ProductSubCategoryId.
We will free the procedure cache now and recompile the proc so that relational engine can create a new execution plan and use a newly created non-clustered index
You will note that as soon as we execute DBCC Freeproccache & sp_recompile ‘usp_ProdSubCatId’ Relational engine has created a new plan and is now using the correct index (in our case idx_ProdSubCatId).
That is why it is advisable to recompile proc or view as I've described above. You may download the demo script here.
