A few end of 2010 SQL BI thoughts

This blog tell us what happned and what will happen to SQL Server in 2010 and in 2011.

good one.

Top 9 New Features of SQL Server "Denali"

Denali still in her early age, but lots of talkings are around about her features.

This article tells you top 9 features of Denail

SQL Scripts Manager,Powerful, reliable, automated scripting by SQL Server experts, for the community

SQL Scripts Manager is a free tool that brings together must–have scripts from expert DBAs, SQL Server MVPs, and Red Gate developers to enable you to automate common troubleshooting, diagnostic, and maintenance tasks.

Get more info about the tool from here

Microsoft SQL Server 2005 Service Pack 4 RTM

Service Pack 4 (SP4) for Microsoft SQL Server 2005 is now available for download. SQL Server 2005 service packs are cumulative, and this service pack upgrades all service levels of SQL Server 2005 to SP4 .

SQL Server 2005 SP4 includes SQL Server 2005 SP3 cumulative update 1 to 11, customer requested fixes, along with instances of the SQL Server 2005 SP4 database Engine support for DAC operations.

For a list of the bugs that are fixed in SQL Server 2005 Service Pack 4 (SP4), see KB article 2463332. No new features or major changes were added to SP4.

Companies Have More SQL Server Databases than Oracle Databases

According to Embarcadero Technologies’ Database Survey Report, 83 percent of respondents are running SQL Server in their database environment. Oracle came in second with 76.6 percent of respondents saying they have Oracle databases inhouse.

The Database Survey Report revealed many other interesting facts, so I recommend taking a look at the full report. Are you and your company in line with what the survey reported or does your environment buck the trend

SQL Server 2008 R2 System Views Map

The Microsoft SQL Server 2008 R2 System Views Map shows the key system views included in Microsoft SQL Server 2008 and SQL Server 2008 R2, and the relationships between them. The map is similar to the Microsoft SQL Server 2008 version and includes updates which are also applicable for the Microsoft SQL Server 2008.

Download from here

Monitor User Connections in SQL Server

This article explores the causes and remedies for applications failing due to too many open connections to SQL Server.

Read the full article at http://www.sql-server-performance.com/articles/dev/monitor_user_connections_sql_server_p1.aspx

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

Is There a Shortage of SQL Server Experts?

Do you think we have enough SQL Server experts? Article from Brain indicate there are shortage in SQL Server experts.

“There are certainly many world-class SQL Server experts, and there have been for quite some time. I suppose it’s more of a matter if there are enough available to satisfy demand. One observation I’ll make is that many of “famous” SQL Server experts I know are consultants who presumably aren’t interested in working full-time for a single company in a DBA capacity. Note that I didn’t say the best SQL Server people are consultants; I used the word “famous.” I’ve long suspected that for every PASS pre-con speaker there are dozens of people who are just as talented on a technical level and don’t desire to be famous or simply haven’t had the break that propels them to attention on the community stage. So, I wonder—am I right about that? If I’m right, then I suspect that the lack of expert and very senior SQL Server technologists is largely perception rather than reality.”

Getting Job Category for the SQL Server Agent Jobs

Getting Job Category

There are Job categories associated with SQL Server Agent Jobs. As you know you can get the job information by querying sysjobs system table.

SELECT J.name as jobName,C.name Category FROM sysjobs J

INNER JOIN syscategories C ON J.category_id = C.category_id

WHERE C.category_class = 1

Now you can see this is not huge query. But the problem is Microsoft documentation, If you go the sysjobs documentation as shown in the following image, id does not say from which table you should get the category from. Since job category table does not have job prefix it is bit difficult to find this out.

But the documentation not that bad since you have http://msdn.microsoft.com/en-us/library/ms181367.aspx page which will tell you all the related tables for the Agent Jobs. However, it would have been much better if this information is mention at the sysjobs documentation itself.

Dilbert & SQL Function

I am not a fan of Dilbert. But this seems to be something related to SQL.  

SQL Agent job getting suspended

When you run some SQL Server agent jobs they will be suspended and you will not be permitted to re-run them.

http://www.sql-server-performance.com/faq/sql_agent_job_suspended_p1.aspx

Know your Data with Data Profiling

Data quality is become a major issue in database. In SSIS, there is a new control task called Data Profiler. See my article in sql-server-performance.com

Enabling Resource Governor

This is UI bug which was missed by MS QA team.

Steps to produce

1. Create a classifier function

CREATE FUNCTION dbo.RG_Classifier_2() RETURNS sysname

WITH SCHEMABINDING

AS

BEGIN

DECLARE @grp_name sysname

IF (DB_NAME() = 'Sales')

SET @grp_name = 'GroupSales'

IF (DB_NAME() = 'Reports')

SET @grp_name = 'GroupReports'

IF (SUSER_NAME() LIKE 'DataW')

SET @grp_name = 'GroupDWH'

RETURN @grp_name

END;

GO

-- Register the classifier function with Resource Governor

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.RG_Classifier_2);

GO

-- Start Resource Governor

ALTER RESOURCE GOVERNOR RECONFIGURE;

GO

2. Create another classifier function

CREATE FUNCTION dbo.RG_Classifier_3() RETURNS sysname

WITH SCHEMABINDING

AS

BEGIN

DECLARE @grp_name sysname

IF (DB_NAME() = 'Sales')

SET @grp_name = 'GroupSales'

IF (DB_NAME() = 'Reports')

SET @grp_name = 'GroupReports'

IF (SUSER_NAME() LIKE 'DataW')

SET @grp_name = 'GroupDWH'

RETURN @grp_name

END;

GO

-- Register the classifier function with Resource Governor

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.RG_Classifier_3);

GO

-- Start Resource Governor

ALTER RESOURCE GOVERNOR RECONFIGURE;

GO

3. Go to properties of Resource Governor.

All classifier functions in the drop down while last one is selected with Enable Resource Governor option is selected.

4. Select some other classifier function name and above option is disabled.

5. Select the previous classifier function again where enable resource governor should be enable where as it is disable.

If you cancel the screen and come back to the same dialog box it will be enable as it should be.

35370 Days Remaining…

Well, this is nothing to do with databases, though i got this issue when found that there is not enough disk space to install SQL Server and I went on to compress window folder.

SQL Server 2008 R2 Installation Issue

I was not able to install SQL Server 2008 R2 for a while. I tried this in various environments like Virtual PC (VPC), VMWare  with Windows 2008 with and without service packs, but was failing.

While installing  it gave me a error saying there is some issue with this package and it continued to installed.  At the end of installation, it is saying all the services except analysis service failed to installed. That is, it has failed to installed Database services, Replication and Reporting services and it will successfully installed all the client components.

All the while, I was running the ISO file (en_sql_server_2008_r2_developer_x86_x64_ia64_dvd_522665.iso)which I downloaded from MSDN.

At last I extracted the ISO file into a folder and executed the setup.exe from there. Guess what, it worked.

CUs Released

Microsoft has released SQL Server 2008 SP1 CU8 (Build 2775). There are 18 fixes in this cumulative update, and most of them are for Reporting Services and Analysis Services.

Microsoft released SQL Server 2008 R2 RTM CU1. It is Build 1702, and it contains 76 fixes.

Using SQL Server Default Trace

Default Trace is a useful tool for gathering data and information on your SQL Server environment and can be very useful in troubleshooting your environment.

Read the article at Using SQL Server Default Trace

History of SQL Server

As techies, we won’t much consider the history, for us what matters is the what we have today and what we will get tomorrow. But history teacher will tell, if you don’t know your history, you will not a success man for today and future.

What ever the point it is, let us look into the history of this great database SQL Server.

SQL Server 1.0

This version was release in 1989 and was released my jointly Microsoft, Sybase and Ashton-Tate. This edition was same as Sybase 3.0

SQL Server 4.2.1

SQL Server 4.2.1 for Windows NT was released in 1 993. Microsoft began making changes to the code. Microsoft SQL Server 4.21 for Windows NT was released at the same time as Windows NT 3.1.

SQL Server 6.0 & 6.5

SQL Server 6.0 was released in 1 995. In 1 996, the 6.5 upgrade was released.

It included the first version of Enterprise Manager and SQL Server Agent.

SQL Server 7.0

SQL Server 7.0 was released in 1998, and was a full rewrite of the Database Engine by Microsoft. You can say this was the first Microsoft SQL Server. SQL Server 7 also included English Query, OLAP Services, replication, Database Design and Query tools and Full-Text Search. Data Transformation Services (DTS) is also introduced. This was a major enhancement really. Especially features like DTS, OLAP were really new tools to the database users.

SQL Server 2000

SQL Server 2000 looks like SQL Server 7.0 and Microsoft entered into database market in big time. This SQL Server version was featured with clustering, much better performance, and real OLAP. It added user-defined functions, indexed views, Distributed Partition Views, and improved replication.

SQL Server 2000 64-bit version for Intel Itanium was released in 2003, along with the first version of Reporting Services and Data Mining tools. DTS became more powerful than SQL Server 7. Northwind joined Pubs as the sample database.

SQL Server 2005

After five long years Microsoft released another SQL Server. This was another rewrite of the Database Engine2005 and added tons of new features and technologies, including Service Broker, Notification Services, CLR, XQuery and XML data types, and SQLOS. T-SQL gained try-catch and the system tables were replaced with Dynamic Management Views (DMVs). Management Studio replaced Enterprise Manager and Query Analyzer. DTS is replaced by Integration Services. English Query was removed, and stored procedure debugging was moved from the DBA interface to Visual Studio.

Encryption was included in this version and Copy-only backups.

SSIS came out with blistering features to compete with the market existing ETL tools.

AdventureWorks and AdventureWorksDW replaced Northwind and Pubs as the sample databases. SQL Server 2005 supported.

With SQL Server 2005 you had all the features in a one version unlike earlier case where you need SSRS, SSAS in separate installations.

Since it took long five years, many users got used to SQL Server 2000 and had difficulty of moving them from there.

SQL Server 2008

Some are saying this is part 2 of SQL Server 2005. You can’t disagree with them. This version was added with Policy-Based Management, data compression, Resource Governor, and new beyond relational data types. Notification Services goes out.

T-SQL finally heard the voices of developers and gets date and time data types and table-valued parameters, the debugger returns, and Management Studio gets IntelliSense.

CDC, Change tracking was included.

Future of SQL Server

SQL Data Services is Microsoft’s database in the cloud called SQL Azure.

Kilimanjaro, estimated availability in mid-201 0 extends SQL Server’s BI suite with tighter integration with Office 1 4.

SQL Server 2011 continues the strategic direction of SQL Server 2008.

Summary

Version	Year	Release Name	Code Name
1.0	1989	SQL Server 1.0
1.1	1991	SQL Server 1.1
4.21	1993	SQL Server 4.21	SQLNT
6.0	1995	SQL Server 6.0	SQL95
6.5	1996	SQL Server 6.5	Hydra
7.0	1998	SQL Server 7.0	Sphinx
	1999	SQL Server 7.0 OLAP Tools	Plato
8.0	2000	SQL Server 2000	Shiloh
8.0	2003	SQL Server 2000 64 Bit version	Liberty
9.0	2005	SQL Server 2005	Yuko
10.0	2008	SQL Server 2008	Katmai
10.5	2010	SQL Server 2008 R2	Kilimanjaro
11.0	2011	SQL Server 2011 (SQL 11)	Denali

C2 Level Auditing

Auditing has become a must in today market environment due to many reasons.

Read the article at http://beyondrelational.com/blogs/dinesh_asanka/archive/2010/05/08/c2-level-auditing-with-sql-server.aspx

A New Law Could Change the Way You Build Database Applications

Massachusetts recently passed a sweeping new data security law that will have a profound impact on the way the United States, and perhaps the rest of the world, manages and develops data-centric applications. Oddly, most people in the business don’t seem to know about it.
Google “Massachusetts data security law, 201 CMR 17.00” and you’ll find plenty of facts about the new law. I also encourage you to read InformationWeek’s "States' Rights Come to Security Forefront: Massachusetts' new data protection law reaches beyond its borders. Are you ready?" It’s one of the best summaries I’ve seen. But even it falls short of helping you understand the profound impact of this law.
Here are the basics of the new law. If you have personally identifiable information (PII) about a Massachusetts resident, such as a first and last name, then you have to encrypt that data on the wire and as it’s persisted. Sending PII over HTTP instead of HTTPS? That’s a big no no. Storing the name of a customer in SQL Server without the data being encrypted?  No way, Jose. You’ll get a fine of $5,000 per breach or lost record. If you have a database that contains 1,000 names of Massachusetts residents and lose it without the data being encrypted that’s $5,000,000. Yikes.

more at http://www.sqlmag.com/article/sql-server/A-New-Law-that-Will-Change-the-Way-You-Build-Database-Applications.aspx

Why SQL Server 2008 R2 Matters to Small Business

If you're like many small business owners, the news that Microsoft is weeks away from the release of a new version of SQL Server 2008 could seem utterly irrelevant to your interests. But it isn't. Microsoft has added important new features in this version that will help you delve more deeply into your business data, extract the most useful nuggets of information, and present them in a compelling way.

Best of all: If you're savvy enough to build a pivot table in Excel, you'll be able to use these features without the help of professional IT types.

http://www.pcworld.com/businesscenter/article/194808/why_sql_server_2008_r2_matters_to_small_business.html

How to Read Excel Cells from SQL Server Integration Services

We can get data in all sorts of formats in the real world. In this new article from me, learn how to use scripting to import data from individual Excel cells.

Read the article at http://www.sqlservercentral.com/links/66237/151372

SQL Server 2008 R2 has RTMed today

You can get more information, resources and download a free trial here: http://www.sqlserverlaunch.com. MSDN and TechNet subscribers can expect to download it soon.
Some more information here: Data Platform Insider blog

How to change server name when replication is enabled

Changing a server name is not difficult but Microsoft does not recommen to change the server name when the replication is configured. These are the steps you need to follow.

http://www.sql-server-performance.com/faq/change_server_name_replication_enabled_p1.aspx

Could not find stored procedure 'dbo.sp_MSins_dboTest'

This is a frequent error you get in transactional replication. See how to resolve this at http://www.sql-server-performance.com/faq/could_not_find_dbssp_msins_dbotest_p1.aspx

Queries which include DMFs return a syntax error

When you execute queries which include DMFs the queries return a syntax error. See the full FAQ at http://www.sql-server-performance.com/faq/dmf_syntax_error_p1.aspx

SET NOCOUNT ON

What is this setting. Read the new blog post at beyond relational

BeyondRelational.Com

I am invited to write blog posts targeting noive DBAs / database developers at www.beyondrelational.com . Since I can’t duplicate those post here I will include the shortcut here to the beyondrelational.com .Those will be tagged as BeyondRelational.com

Microsoft SQL Server 2008: What's New with R2?

Are you ready for Microsoft SQL Server 2008 R2? This white paper highlights some of the major features included in SQL Server 2008 R2 due to be released in the first half of 2010.

Microsoft SQL Server 2008- What's New with R2-

Windows Authentication Users Domain Will not Change when Server Name Changed

in practice, you need to change the server name due to many reasons. There are few challenges you need to face as Database administrator. This is one of them.

After changing the server name, you need to run following stored procedures.

sp_dropserver SERVER2

GO

sp_addserver SERVER3, local

GO

SERVER2 being the old server name and SERVER3 is the new server name.

After this if you can login to the server with SERVER3\Administrator if you have configured SERVER2\Administrator user before. However, if you closely look at there is no user called SERVER3\Administrator instead user is SERVER2\Administrator.

When server name is changed, I believe these windows authentication users also changed. Now you need to run following script to change user names accordingly.

/****** Object: Login [SERVER2\Administrator] Script Date: 04/06/2010 14:51:51 ******/

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'SERVER2\Administrator')

DROP LOGIN [SERVER2\Administrator]

GO

/****** Object: Login [SERVER3\Administrator] Script Date: 04/06/2010 14:51:51 ******/

CREATE LOGIN [SERVER3\Administrator] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

GO

Changing Data Type for Derived Columns?

If you want to add a column to the existing data flow in SSIS, in normal circumstances you are using Derived Column Transformation to accomplish this task.

If you drag and drop the Derived Column Data Flow Transformation and following configuration dialog box will appear.

Unlike in SQL Server 2005, you don’t have an option of selecting the data type you need. It will select default data type for expression you type.

For example, for text data type it will be Unicode string [DT_WSTR], for positive integer’s four-byte signed integer [DT_I4], for float numeric [DT_NUMERIC].

There is a workaround for this. Go to the advanced Editor; navigate to the option shown in following image.

You can select any data type from the available combo box here and if you go back again to the previous configuration, you will see the newly selected data type there as well.

However, I feel this is a bug to be fixed.

Are You Using Template Explorer

This is an hidden feature for many DBAs and developers. See what you can do with Template Explorer in my latest blog at http://beyondrelational.com/blogs/dinesh_asanka/archive/2010/04/01/are-you-using-template-explorer.aspx

Light Weight Monitoring using Extended Events

This is my latest article on Extended Events in SQL Server 2008. This is the latest monitoring mechanism in SQL Server 2008.

Re-indexing/ Reorganizing a Table after Enabling CDC

In SQL Server 2008 there is a new feature named CDC where you can capture data changes. Sorry this blog entry is not going to discuss about how to work with CDC instead this is an issue which I faced after enabling CDC.

I enabled CDC for a table called CDC with following syntax.

EXEC sys.sp_cdc_enable_table

@source_schema = N'dbo',

@source_name   = N'data',

@role_name     = N'dbo',

@supports_net_changes = 1

GO

Then I performed a re-organize for that table.

USE [COB]

GO

ALTER INDEX [PK_Data] ON [dbo].[Data] REORGANIZE WITH ( LOB_COMPACTION = ON )

GO

Then it returned the following error.

Msg 22983, Level 16, State 1, Procedure sp_cdc_ddl_event_internal, Line 77

The unique index 'PK_Data' on source table '[dbo].[Data]' is used by Change Data Capture. To alter or drop the index, you must first disable Change Data Capture on the table.

Msg 3609, Level 16, State 2, Line 1

The transaction ended in the trigger. The batch has been aborted.

Same error will appear if I do a re-indexing.

This bug is fixed with Cumulative update package 6 for SQL Server 2008 Service Pack 1

Depreciated Features in SQL Server 2008

With every versions of SQL Server, there are new features added. With new features, there are discounted and depreciated features.

Most of the time we are not sure whether our applications are using discounted or depreciated version. Discounted versions anyway will fail, but depreciated features will work.

To identify depreciated features, now there is a new perfmon counter called, SQLServer:Depreciated Features.

This was my output, simply after adding these counters.

Above image shows SQL Server instance has 4 databases with compatibility level 90 and 2 databases with compatibility level 80. (Scale is x10). In SQL Server 2008, compatibility level 70 is discontinued while 80 and 90 compatibility levels are depreciated.

Following query has two depreciated features.

CREATE Table #

(

ID INT,

NAME TEXT,

LANGUAGE NTEXT

)

CREATE Table ##

(

ID INT,

NAME TEXT,

LANGUAGE NTEXT

)

DROP TABLE #

DROP TABLE ##

This will generate few more counters as you can see below.

This count is an aggregated counter and it will be reset to zero when the service is restated.

Functionality Change in REPLACE Function in SQL Server 2008

What is the output for the following query?

DECLARE @V CHAR(6)

SET @V = 'ABC'

SELECT REPLACE(@V,' ','L')

Guess what you have two answers. In SQL Server 2005, it will be ABC while in SQL Server 2008 it is ABCLLL.

What does this mean? They have fixed the bug in REPLACE function in SQL Server 2008. In SQL Server 2005, for char data type, before replacing it trims the variable which is incorrect. In SQL Server 2008 now it is not trimming your data before the replacing function.

If you are in the process of upgrading to SQL Server 2008 this is an point to consider.

Strange Error after DDL Trigger

I created a DDL trigger on my server (SQL Server 2008 with SP1) to pick the database name which is listed below.

CREATE TRIGGER [ddlsvrtrg_create_database_backup_paths] ON ALL SERVER

FOR CREATE_DATABASE

AS

SET NOCOUNT ON

DECLARE @xmlEventData XML,

@database_name VARCHAR(50),@path varchar(500),@fullPath varchar(500),@diffpath varchar(500)

SET @xmlEventData = eventdata()

SELECT @database_name = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(150)')

.

.

.

After creating this trigger, I tried to create a database from the UI and you will get the following error.

TITLE: Microsoft SQL Server Management Studio

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

Create failed for Database 'DB_DDL'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+((Katmai_PCU_Main).090329-1045+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476

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

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

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

SELECT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. (Microsoft SQL Server, Error: 1934)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=1934&LinkId=20476

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

BUTTONS:

OK

However, if I create a database from a script it will work. Also, I generated the script from the create database UI. That is still working.

Also, this issue does not exist in the SQL Server 2005 with SP3.

DDL Trigger on Database Create

I created a DDL trigger on SQL Server 2008 with SP1. Trigger is firing well for Create Database statement. However, it does not fire for the instance where you restore a database to a new database. Since, there is no DDL trigger event for Database Restore there are no other alternative.

To capture the database restore event, there was a nice suggestion by a forum member in SQL Server Central to create a DML trigger on [msdb].[dbo].[restorehistory]

SSMS Customize Shortcuts

Do you know that you can customize short cuts in SQL Server Management Studio. This is a small article on www.beyondrelational.com. Read this article SSMS Customize Shortcuts

Not another Comparison Tools for SQL Server

There are several tools for SQL Server schema comparisons. This is a new tool called dbForge.

See the latest review on this http://www.sql-server-performance.com/software/review/dbforge_review_2010_p1.aspx

SQL Server Access From Linux - What Do You Think?

Microsoft is getting serious about offering SQL Server access from Linux...and your input is being requested. Actually, the request is that you provide your thoughts about Microsoft offering ODBC support for non-Windows platforms (which would provide SQL Server access from Linux) by completing this survey:

https://www.surveymonkey.com/s/SQL_Server_multi-platform_ODBC_survey

The survey is open until March 10th and takes about 10 minutes to complete. The survey questions will give you an idea of how broadly Microsoft is thinking about ODBC support for non-Windows platforms.

Please take a few minutes to complete the survey...your feedback is much appreciated and will help set the direction for an important initiative within Microsoft.

SQL Server 2008 SSIS BUG

1. Drag and Drop the foreach Loop Container to the Control Flow task
2. Double click
3. Navigate to Collection tab

By default, it shows Enumerator as foreach File Enumerator. But second dialog box shown is not the parameters you should get for foreach File Enumerator.

If you want to get them, this is the workaround,

1. Select any other Enumerator
2. Select foreach File Enumerator again. Those parameters are back.

SQL Server Destination remote server error

It is recommended to use SQL Server Destination over OLEDB Destination. However, there can be occasions where you have to use OLEDB Destination.

http://www.sql-server-performance.com/faq/sql_server_destination_remote_server_p1.aspx

Add Node to A SQL Server failover Cluster failed with invalid SKU error

This is a bug which is supposed to be fixed with SQL Server 2008 SP1, but this bug still exists and there is a workaround too.

http://www.sql-server-performance.com/faq/sku_error_add_node_failover_cluster_p1.aspx

Copy Only Backups for Adhoc Backups

Do you know that there is a new backup type called Copy Only backups in SQL Server. Copy only backups are independent of the sequence of normal SQL Server backups and are useful for ah-hoc scenarios where a backup/restore is necessary.

Read my latest article at Copy Only Backups for Adhoc Backups in www.sql-server-performance.com

Will Check Constraints Improve Database Performance?

Most of developers are in the view that check constraints are for data integrity.

Check this http://www.sql-server-performance.com/faq/check_contraints_performance_p1.aspx

Is there a difference between fill factor 0 and 100

When creating indexes in SQL Server, if you do not specify an index fill factor, the fill factor will be 0 (effectively the same as 100%). You can specify an index's fill factor percentage in a number of different ways

Read more at http://www.sql-server-performance.com/faq/fill_factor_0_100_p1.aspx

Cannot Execute SSIS packages with Excel in 64 bit version of SQL Server

When you try to execute SSIS package which has a Excel Destination/Source in SQL Server 64 bit version, you will get following error.

[Connection manager "Excel Connection Manager"] Error: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.

As error suggest Excel Connection Manager does not support 64 bit version. Depending on the environment you can execute this SSIS packages.

SQL Server Agent Job

In SQL Server 2008, there should be a checkbox on the Execution options tab Job Step page to run the package in 32-bit mode as shown in following image.

In SQL Server 2005, you have to change the Job Step from an Integration Services Step to an Operating System step, and specify a DTEXEC command line instead. The DTEXEC command line needs to specify the DTEXEC.EXE from the Program Files (x86) folders, NOT the DTEXEC.EXE from the Program Files folders which is the 64 bit version.

DTEXEC

If you're executing the package using, as said before, you need to specify the 32-bit version of DTEXEC by explicitly running the DTEXEC.EXE from the Program Files (x86) SQL Server folder.

Visual Studio

You can also set the 32 bit runtime environment using Project Properties -> Debugging -> Run64BitRuntime = False, as shown in the following image.

Will Truncate Command be carried forward to Secondary Server in Log Shipping?

Truncate command will not delete data row by row hence deleted data will not be logged in Transaction Log. In Log shipping, transaction log backups will be transferred to the secondary server. So the natural question is whether truncate command will be carried forward to Secondary Server.

Let us examine, what is happening to transaction log during the truncate command. Following is the part of transaction log you will see when you issue a truncate statement.

 

Current LSN	Operation	Context
0000001c:00000014:0005	LOP_MODIFY_ROW	LCX_PFS
0000001c:00000014:0006	LOP_MODIFY_ROW	LCX_PFS
0000001c:00000014:0007	LOP_FORMAT_PAGE	LCX_IAM
0000001c:00000014:0008	LOP_HOBT_DELTA	LCX_NULL
0000001c:00000014:0009	LOP_MODIFY_ROW	LCX_IAM

PFS – Page Free Space

IAM – Index Allocation Maps

The actual process for Truncate Table is to de-allocate the pages assigned to a table, this de-allocating part will be captured in log file. So in the, transaction log backup, this statement will be captured and will be transferred to the secondary server. This means that Truncate command will be carried forward to the Secondary Server.

Similarly, in mirroring, transaction log backup restoring, transactional replication truncate statement will be transferred to the intend targets.

PN: table is de-allocated by unhooking the IAM chains and then de-allocating the individual pages and extents using a background task – a process called deferred-drop, to avoid running out of locks during the de-allocation process.

Brad's Sure DBA Checklist

Sometimes, all a DBA needs, to help with day-to-day work, is a checklist of best-practices and dos and don’ts. It provides a handy reminder. Brad has come up with a new update to his famous checklist

http://www.simple-talk.com/sql/database-administration/brads-sure-dba-checklist/?utm_source=simpletalk&utm_medium=email&utm_content=BradChecklist-20100125&utm_campaign=SQL

Version Information on SQL Server

If you want to find out version information, you can use @@Version function. However, in SQL Server 2005, you won’t get the service pack level of the SQL Server.

If you run SELECT @@VERSION in SQL Server 2005, you will get following output.

Microsoft SQL Server 2005 - 9.00.4207.00 (X64) Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

PN: here Service Pack 2 means service pack of the operating system, not the service pack of the SQL Server. To find out the service pack level in SQL Server 2005, you need to run following T-SQL.

SELECT SERVERPROPERTY('ProductLevel') ServicePack

In SQL Server 2008 this is what you get for SELECT @@VERSION.

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Copyright (c) 1988-2008 Microsoft Corporation Developer Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2)

In this you can see that you will get the Service pack level from the @@VERSION.

Apart from above commands, following stored procedures also give you the server information.

EXEC master..xp_msver

EXEC sp_server_info

BUG: Naming convention issue with SQL Server Configuration Manager

In the first image, SQL Server Configuration Manager shortcut, you have SQL with all caps. In the next image, which is the SQL Server Configuration Manager tool, in the title it shows as Sql with title case.

BUG: SQL Server 2008 Installation

In the SQL Server 2008 installation, there is service accounts configuration. In this SQL Server Analysis Service and SQL Server Reporting Services has a case error as shown in the following image.

 

An Error Occurred during decryption when creating a linked server.

When linked server is created, following error is occurring.

An error occurred during decryption.

PN: Also, when configuring distribution for replication following error occurred.

An error occurred during decryption.

There is no remote user 'distributor_admin' mapped to local user '(null)' from the remote server 'repl_distributor'.

Changed database context to 'master'. (Microsoft SQL Server, Error: 15466)

This also due to unable to create linked server named repl_distributor.

The Service Master Key is the root of the SQL Server encryption hierarchy. It is generated automatically the first time it is needed to encrypt another key. By default, the Service Master Key is encrypted using the Windows data protection API and using the local machine key. The Service Master Key can only be opened by the Windows service account under which it was created or by a principal with access to both the service account name and its password.

Solution is to regenerate the service master key with following command.

ALTER SERVICE MASTER KEY REGENERATE

In case above statement generates an error, you have to use with FORCE command. http://support.microsoft.com/kb/914261

ALTER SERVICE MASTER KEY FORCE REGENERATE

How to Generate Data Scripts for Your Tables

Generating Data into scripts is a common problem faced by database developers. Many times you need to use third party tools. With SQL Server 2008, there is an inbuilt feature to generate Data scripts. Follow the steps given below.

1. Right-Click the Database and Select Generate Scripts… from the Task context menu.
2. In the Script option dialog select True fro Script Data option. By default this is true. If you have logged into SQL Server 2005 server, still you can enable this option.

 

 

3. After this option next options are normal options that you will get.

Following is the sample script that was generated by using above option.

USE [AdventureWorks]

GO

/****** Object:  Table [HumanResources].[Department]    Script Date: 01/02/2010 21:32:38 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [HumanResources].[Department](

      [DepartmentID] [smallint] IDENTITY(1,1) NOT NULL,

      [Name] [dbo].[Name] NOT NULL,

      [GroupName] [dbo].[Name] NOT NULL,

      [ModifiedDate] [datetime] NOT NULL,

 CONSTRAINT [PK_Department_DepartmentID] PRIMARY KEY CLUSTERED

(

      [DepartmentID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET IDENTITY_INSERT [HumanResources].[Department] ON

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (1, N'Engineering', N'Research and Development', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (2, N'Tool Design', N'Research and Development', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (3, N'Sales', N'Sales and Marketing', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (4, N'Marketing', N'Sales and Marketing', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (5, N'Purchasing', N'Inventory Management', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (6, N'Research and Development', N'Research and Development', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (7, N'Production', N'Manufacturing', CAST(0x00008C6900000000 AS DateTime))

SET IDENTITY_INSERT [HumanResources].[Department] OFF

/****** Object:  Default [DF_Department_ModifiedDate]    Script Date: 01/02/2010 21:32:38 ******/

ALTER TABLE [HumanResources].[Department] ADD  CONSTRAINT [DF_Department_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]

GO