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