Happy Birth Day, Windows

Twenty-five years ago, on Nov. 20, 1985, Microsoft introduced its first version of Windows to the world. Not many people outside the technical press or the tech industry took notice.

What's changed in Windows in the last 25 years? Plenty. In this image gallery, You can get an idea or revise your memories of Windows.

Visual Tour

Drop-Add-Drop-Add .. Column of SQL Server table

This error was encountered by one of our team mates. His task was to simply add three columns to the table and populate data on them. So he has written a rollback script to drop this column. While testing he executed these scripts few times and he noticed that it was failing at the 6th instance. He was able to recover this by doing a Index Rebuild.

I was trying to figure out by writing an sample script for this but took little while.

SET NOCOUNT ON
IF OBJECT_ID('dbo.InitTable') IS NOT NULL
DROP TABLE InitTable
CREATE
TABLE InitTable
(
ID Int identity (1,1) PRIMARY KEY CLUSTERED,
Data1 varchar(1000) NOT NULL,
Data2 varchar(1000) NOT NULL)

INSERT INTO InitTable
(Data1,Data2)
VALUES
( REPLICATE('A' ,1000),
REPLICATE('B' ,1000))

DECLARE @LOOP_COUNT INT
DECLARE @MAX_LOOPS INT

DECLARE @DYNAMIC_SQL VARCHAR(8000)
SET @LOOP_COUNT = 1
SET @MAX_LOOPS = 1500

WHILE @LOOP_COUNT <= @MAX_LOOPS BEGIN SET @DYNAMIC_SQL = 'ALTER TABLE dbo.InitTable ADD ' + 'Data3 [varchar](400) NULL, ' + 'Data4 [varchar](400) NULL, ' + 'Data5 [varchar](400) NULL ' EXECUTE (@DYNAMIC_SQL) SET @DYNAMIC_SQL = 'Update InitTable' + ' SET Data3 = Replicate(''A'',400) ' + ' ,Data4 = Replicate(''B'',400)' + ' ,data5 = Replicate(''C'',400)' EXECUTE (@DYNAMIC_SQL) Alter table InitTable Alter Column Data3 varchar(400) NOT NULL Alter table InitTable Alter Column Data4 varchar(400) NOT NULL Alter table InitTable Alter Column Data5 varchar(400) NOT NULL Alter table InitTable drop column Data3 Alter table InitTable drop column Data4 Alter table InitTable drop column Data5 SET @LOOP_COUNT = @LOOP_COUNT + 1 END

So in the sixth loop this script is failing and also I notice if I commentet out following lines, this script is not failing at 6th instance but at the 11th instance.

Alter table InitTable
Alter Column Data3 varchar(400) NOT NULL

Alter table InitTable
Alter Column Data4 varchar(400) NOT NULL

Alter table InitTable
Alter Column Data5 varchar(400) NOT NULL


Things not are not finished my friends.
For updating you can see I have used 400 charactors, if I increased it to 1000 it is failing at different point. Guess what, if it is 1000 it is not failing at 6th but at 9th. Why why why? know whos, I gave it up.

Denali Arrives

Microsoft is making available for download on November 9 the first test version of its next-generation SQL Server release, code-named “Denali.”

Denali will be focused on providing users with more high-availability, self-service and BI functionality.

Here’s a list of some of what is on tap to be included in Denali:

•SQL Server AlwaysOn
a new high-availability “solution that will deliver “increased application availability, lower TCO (total cost of ownership) and ease of use, according to the Softies
•Project codename “Apollo”
new column-store database technology aiming to provide greater query performance
•Project codename “Juneau”
a single development environment for developing database, business intelligence (BI) and web solutions
•Project codename “Crescent”
a web-based, data visualization and presentation solution, and follow-on to the PowerPivot technology that is part of SQL Server 2008 R2
•SQL Server Data Quality Services (based on technology from Microsoft’s 2008 Zoomix acquisition)

In addition to making the first Denali CTP available, Microsoft also is making available today a first CTP of the SQL Azure Reporting Services and the SQL Azure Dat Sync Service

Microsoft also launched a first beta today of a new service, codenamed “Atlanta.” Atlanta is a configuration monitoring cloud service that aims to help customers to reduce downtime and improve the performance of Microsoft SQL server deployments. The service monitors the configuration of SQL deployments to help database administrators proactively avoid configuration problems and to resolve identified issues.The target ship date for Atlanta is first half of calendar 2011.

more at here

SQL Server Masters Certification Goes Global

Well, I haven't posted any post about certification before this. That is simply because my lazyness to pass certificate. However, this time I thought of publishing this blog post on SQL Server Microsoft Certified Master (MCM).

The SQL Server MCM exam was previously available exclusively as part of a three-week training and certification program offered at the Microsoft campus in Redmond, Wash. Now candidates who wish to earn the certification will be able to choose from multiple testing locations all over the world and take the exam in a matter of hours rather than weeks.

As Field notes, the cost of the exam has also dropped significantly. Previously, the required three-week SQL Server 2008 MCM training session plus four exams necessary to earn the certification cost candidates approximately US$18,500 — in addition to associated travel expenses. Now, candidates can earn the certification by passing just two exams: the four-hour Knowledge Exam, and a six-hour hands-on Lab Exam, which will be available in early 2011.

Read more at here

SQL Server Security Concerns

A survey of SQL Server pros highlights the challenges posed to database security by such factors as insider threats, human error and poor patch deployment.

Only a third of SQL Server professionals polled in a recent survey say that personal identity information, such as Social Security and credit-card numbers, are encrypted in all of their databases. Another 25 percent say they aren't using encryption to protect the data at all.

Among its findings: While 20 percent of respondents say a data breach in their organization is either "inevitable" or "somewhat likely" during the next 12 months, a full two thirds describe such an event as "highly unlikely" or "somewhat unlikely."

Read the full survey report here

Top 10 Secrets of a SQL Server Expert

Here are the top 10 ways a SQL Server DBA can take control of his environment and reduce the overall potential for crises to occur.

10. Take Inventory
9. Standardize Configurations
8. Understand the I/O Subsystem
7. Create a Customized Maintenance Plan
6. Ensure the Security of Your System
5. Get on Good Terms with Your Developers
4. Develop a Comprehensive Disaster Recovery Strategy
3. Take and Test Regular Backups
2. Monitor and Maintain Performance
1. Know Where to Find Information

Read the article here

SQL Server Management Service (SSMS) of 2008 R2 is crashing when editing steps in SQL Server Agent Jobs

SQL Server Management Service (SSMS) of 2008 R2 is crashing when editing steps in SQL Server Agent Jobs
When editing/viewing steps in SQL Server Agent Jobs in SQL Server 2008 R2, SOMETIMES SSMS crashes with following error message.

------------------------------

Creating an instance of the COM component with CLSID {AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2} from the IClassFactory failed due to the following error: c001f011. (Microsoft.SqlServer.ManagedDTS)

------------------------------
ADDITIONAL INFORMATION:

Creating an instance of the COM component with CLSID {AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2} from the IClassFactory failed due to the following error: c001f011. (Microsoft.SqlServer.ManagedDTS)

------------------------------
BUTTONS:

OK
------------------------------


http://connect.microsoft.com/SQLServer/feedback/details/557402/ssms-can-no-longer-create-or-edit-job-steps



Temporary work round would be to close SSMS and start it again. However, this is bug exists with SQL Server 2008 R2 and you need to install CU3 for this. When the service pack for SQL Server 2008 R2 is released you can use that service pack to resolve the issue.

Code Names for SQL Server

You must have heard about codes names used for various products released by Microsoft. Do they have any logic behind naming them? Recently I had the opportunity of watching a PASS SQL Server session conducted by Stephen Forte and at the start of the session he discussed about Microsoft product code names. That was really nice and thought of sharing with others.

Different product teams have different conventions of naming them. Windows CE team naming their products after Whiskies. For example, Talisker, Macallan etc. Visual Studio team is naming their products after Islands or Cities Pacific North West. So what are used for SQL Server? Well, National parks are used for code names are SQL Server.

Shiloh – SQL Server 2000

Shiloh is National Military Park in US. Shiloh National Military Park preserves the American Civil War Shiloh and Corinth battlefields.

Yukon – SQL Server 2005

Though many are saying this is a mountain, there is a National park called Yukon in Alaska.

Katmai – SQL Server 2008

Katmai is again a National park. Katmai National Monument was created in 1918 to preserve the famed Valley of Ten Thousand Smokes, a spectacular forty square mile, 100 to 700 foot deep ash flow deposited by Novarupta Volcano. A National Park & Preserve since 1980, today Katmai is still famous for volcanoes, but also for brown bears, pristine waterways with abundant fish, remote wilderness, and a rugged coastline.

Kilimanjaro – SQL Server 2008 R2

Kilimanjaro, The name itself is a mystery wreathed in clouds. It might mean Mountain of Light, Mountain of Greatness or Mountain of Caravans.

Denali – SQL Server 2011

For the future version of SQL Server again the standard is maintained. Denali is situated in Alaska.

Finally , there is a another cool story about code name Longhorn. That is the code name for Vista. Can you guess what longhorn is? Well it is a BAR!!!. Yes it is a bar situated between Whistler and Blackcomb it is said to be easily the most popular bar in Whistler.one of the most favorable locations in all the drinking world. You can view some of the pictures from here.

Singing Pass in August as seen between Whistler and Blackcomb

Whistler is the code name for Windows 2003 and Blackcomb is code name for Windows 7. Since Vista release between Windows 2003 and Windows 7, Vista was named after bar which is between Whistler and Blackcomb.

SQL Server Agent job syspolicy_purge_history is failing in the cluster environment

When you install or upgrade SQL Server 2008 or R2 you will see a new SQL Server Agent job named syspolicy_purge_history. In fact this is the only job you will see after installing a brand new SQL Server 2008 instance.

More at http://www.sql-server-performance.com/faq/syspolicy_purge_history_failing_p1.aspx

Process & Processor

Performance Monitor a.k.a perfmon is used to monitor different counters for different purposes. However, some counters are bit confusing. Process and Processor two misleading counters so thought of putting this note.

Processor

When adding processor object, you have _Total object and 0, 1 .. which are the processor number. Following the graph for _Total (Red) , 0 (Blue), 1 (Pink) objects for % Processor Time counters. ( Colors given in the graph for relevant counter)

In the above graph, you will see _Total is NOT the total of processer 0 and 1. But it is the average of them.

For example, let us say you have four processors of % Processor time of 20, 30, 50, and 80 and _Total count will be 45.  i.e.  (( 20 + 30 + 50 + 80 ) /4)

Process

While processor is the counter for your processors and Process is counter for each process you are executing right at the moment.

for example,  if you wish to measure % processor for sqlserver, this is the measure you have to select.

in this also, you have the _Total counters. Unlike the Processor counter this counter is sum of all the processes INCLUDING idle processor.

From the above graph, you can see _Total counter value is through out 200.

So if you want to match Process and Process it will be following,

\Processor(0)\% Processor Time  +  \Processor(1)\% Processor Time  + … All the other processors

= \Processor(_Total)\% Processor Time  -  \Process(Idle)\% Processor Time