SQLAuthority News – Author’s Birthday – 5 SQL Server Best Practices

Today is my birthday. For those who are curious, I have now completed 31 years in my life. I have learned lots of new things during the course of the past few years. Every year I feel that I am getting better as a human being. Since today is my birthday, I have decided NOT to work and NOT to blog. I will do something really fun!

I am going to clean up my personal servers as well and finish a few tests, as well as complete my benchmark projects. My personal data is also stored in one gigantic database. Because it is my personal server, no one pays me and thus I hardly maintain it.

Today I am going to do the following maintenance tasks on it:

  1. Backup Master. I am going to have a backup of the database using script; however, the backup script has not been updated for a long time now. Last time, I noticed that I was not getting the master database’s backup. And no matter what one suggests, I still think it is very important to have a backup of the master database. I am going to alter the script to have a correct backup of the master database.
  2. Online Indexing. I have upgraded my server from SQL Server 2005 Standard to SQL Server 2008 Enterprise. On the other hand, I never changed the index rebuilding script. One of the tables is very huge and when indexes are rebuilt in it, it often gets broken. I plan to change the index rebuild script to include the option of (ONLINE = ON). This will be good to avoid the time outs.
  3. Restarting the Server. I have the server connected to UPS and even though we have lots of very short power outages, my server is not affected, fortunately. I almost forgot when I really restarted it. Although, it is not necessary to restart server at all, this procedure is intended for my windows box, which has many updates installed but I have not rebooted it in order for these updates to take effect. This may not be the best technique, but it works enough for me because my data is not that critical. If you are maintaining a high-availability solution, I suggest that you have fail over solution implemented before restarting the server.
  4. Wait Stats. I recently learned about Wait stats. I am eager to know what Wait Statistics really are, and what they can do for my server. Due to the fact that I have not upgraded the hardware for many years now (although I have upgraded the OS, SQL Server version and huge data growth), I could find out which of my hardware needs an upgrade.
  5. Fresh Air. I will make sure that after all of the above tasks are completed I am going to go out with my family and breathe some fresh air.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQLAuthority News – 2 Sessions at TechInsight 2010 – June 29 – July 1, 2010

Earlier this month, I got the opportunity to visit Malaysia for community sessions on June 29 – July 1, 2010 at Kuala Lumpur, Malaysia, which I would consider as valuable experience. I presented two different sessions at the event. The event was extremely popular in local community, and I had great time meeting people in Malaysia. I must say that the best thing about Kuala Lumpur is the people and their response.

Malaysia Twin Towers
Malaysia Twin Towers

Techinsights is a major technology conference to network with like-minded peers and also up-skill your knowledge on latest technologies. An event that offers opportunity to dabble in hardcore technologies with in-depth and hands-on demonstration by Microsoft MVPs and industry experts local and abroad. This three-day event will challenge what you think you already know. You’ll return to the office with cutting-edge insights and expertise that will make life easier for you (and everyone else) at work. This round, we have a special highlight on new technologies such as SharePoint 2010, Visual Studio 2010, SQL Server 2008 R2, Silverlight 4, Windows 7, Windows Server 2008 R2 and many more. TechInsight is an event created by techies for techies. There is no marketing involved. It is indeed an experience to rediscover the uber-geek within you. Sign up today to secure your seat.

Techinsight - 2 Sessions
Techinsight – 2 Sessions

I presented two sessions there. Both of my sessions were in the TOP 5 sessions of Development track. Additionally, my session on Join got the highest ranking ever in Dev Track.

1) My Join, Your Join and Our Joins – The Story of Joins

Joins are very mysterious; there are many myths and confusions. This session will address all of them and also tell the story of how they act when it is about performance. Does the order of table in Join matter? Does the right or left join any different to each other? Does the Join increase IO? When is an outer join not an outer join and inner join? All these questions are answered and many more stories of Joins are included. Learn the simple tricks to get the maximum out of this tool.

Session Evaluations

Overall session rating 7.5
How valuable was the content presented 7.467741935
How effectively did the presenter communicate the content 7.596774194

2) Spatial Database – The Indexing Story

The world was believed to be flat but no more. Now SQL Server supports the spatial datatypes and many more functions. This session addresses the most vital part of Spatial datatypes and talks about how to improve the performance for the application, which is already blazing fast. We will look at how indexes are behaving with different spatial datatypes and how they can help to improve the performance and also learn the pitfalls to avoid them affecting performance.

Session Evaluations

Overall session rating 7.237288136
How valuable was the content presented 7.322033898
How effectively did the presenter communicate the content 7.457627119

I must express my special thanks to all the organizers of the event – Ervin, Walter, Raymond, and Patrick (in no particular order). They did an excellent job, and all the attendees of the event had great time as well. The food was awesome, and the response was excellent. After one month, when I am writing this review, I am still thinking of the wonderful experience I had from this event. This makes me want to not miss this event any year.

Techinsight - Event Organizers
Techinsight – Event Organizers

This one event is truly TechEd quality event in Malaysia. Kudos to the organizers and Microsoft.

Techinsight - Kuala Lumpur, Malaysia
Techinsight – Kuala Lumpur, Malaysia

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Enumerations in Relational Database – Best Practice

Marko Parkkola

Marko Parkkola

This article has been submitted by Marko Parkkola, Data systems designer at Saarionen Oy, Finland. Marko is excellent developer and always thinking at next level. You can read his earlier comment which created very interesting discussion here: SQL SERVER- IF EXISTS(Select null from table) vs IF EXISTS(Select 1 from table). I must express my special thanks to Marko for sending this best practice for Enumerations in Relational Database. He has really wrote excellent piece here and welcome comments here.

Enumerations in Relational Database

This is a subject which is very basic thing in relational databases but often not very well understood and sometimes badly implemented. There are of course many ways to do this but I concentrate only two cases, one which is “the right way” and one which is definitely wrong way.

The concept

Let’s say we have table Person in our database. Person has properties/fields like Firstname, Lastname, Birthday and so on. Then there’s a field that tells person’s marital status and let’s name it the same way; MaritalStatus.

Now MaritalStatus is an enumeration. In C# I would definitely make it an enumeration with values likes Single, InRelationship, Married, Divorced. Now here comes the problem, SQL doesn’t have enumerations.

The wrong way

This is, in my opinion, absolutely the wrong way to do this. It has one upside though; you’ll see the enumeration’s description instantly when you do simple SELECT query and you don’t have to deal with mysterious values. There’s plenty of downsides too and one would be database fragmentation.

Consider this (I’ve left all indexes and constraints out of the query on purpose).

CREATE TABLE [dbo].[Person]
(
[Firstname] NVARCHAR(100),
[Lastname] NVARCHAR(100),
[Birthday] datetime,
[MaritalStatus] NVARCHAR(10)
)

You have nvarchar(20) field in the table that tells the marital status. Obvious problem with this is that what if you create a new value which doesn’t fit into 20 characters? You’ll have to come and alter the table. There are other problems also but I’ll leave those for the reader to think about.

The correct way

Here’s how I’ve done this in many projects. This model still has one problem but it can be alleviated in the application layer or with CHECK constraints if you like.

First I will create a namespace table which tells the name of the enumeration. I will add one row to it too. I’ll write all the indexes and constraints here too.

CREATE TABLE [CodeNamespace]
(
[Id] INT IDENTITY(1, 1),
[Name] NVARCHAR(100) NOT NULL,
CONSTRAINT [PK_CodeNamespace] PRIMARY KEY ([Id]),
CONSTRAINT [IXQ_CodeNamespace_Name] UNIQUE NONCLUSTERED ([Name])
)
GO

INSERT INTO [CodeNamespace] SELECT 'MaritalStatus'
GO

Then I create a table that holds the actual values and which reference to namespace table in order to group the values under different namespaces. I’ll add couple of rows here too.

CREATE TABLE [CodeValue]
(
[CodeNamespaceId] INT NOT NULL,
[Value] INT NOT NULL,
[Description] NVARCHAR(100) NOT NULL,
[OrderBy] INT,
CONSTRAINT [PK_CodeValue] PRIMARY KEY CLUSTERED ([CodeNamespaceId], [Value]),
CONSTRAINT [FK_CodeValue_CodeNamespace] FOREIGN KEY ([CodeNamespaceId]) REFERENCES [CodeNamespace] ([Id])
)
GO
-- 1 is the 'MaritalStatus' namespace
INSERT INTO [CodeValue] SELECT 1, 1, 'Single', 1
INSERT INTO [CodeValue] SELECT 1, 2, 'In relationship', 2
INSERT INTO [CodeValue] SELECT 1, 3, 'Married', 3
INSERT INTO [CodeValue] SELECT 1, 4, 'Divorced', 4
GO

Now there’s four columns in CodeValue table. CodeNamespaceId tells under which namespace values belongs to. Value tells the enumeration value which is used in Person table (I’ll show how this is done below). Description tells what the value means. You can use this, for example, column in UI’s combo box. OrderBy tells if the values needs to be ordered in some way when displayed in the UI.

And here’s the Person table again now with correct columns. I’ll add one row here to show how enumerations are to be used.

CREATE TABLE [dbo].[Person]
(
[Firstname] NVARCHAR(100),
[Lastname] NVARCHAR(100),
[Birthday] datetime,
[MaritalStatus] INT
)
GO
INSERT INTO [Person] SELECT 'Marko', 'Parkkola', '1977-03-04', 3
GO

Now I said earlier that there is one problem with this. MaritalStatus column doesn’t have any database enforced relationship to the CodeValue table so you can enter any value you like into this field. I’ve solved this problem in the application layer by selecting all the values from the CodeValue table and put them into a combobox / dropdownlist (with Value field as value and Description as text) so the end user can’t enter any illegal values; and of course I’ll check the entered value in data access layer also.

I said in the “The wrong way” section that there is one benefit to it. In fact, you can have the same benefit here by using a simple view, which I schema bound so you can even index it if you like.

CREATE VIEW [dbo].[Person_v]
WITH SCHEMABINDING
AS
SELECT
p.[Firstname], p.[Lastname], p.[BirthDay], c.[Description] MaritalStatus
FROM [dbo].[Person] p
JOIN [dbo].[CodeValue] c ON p.[MaritalStatus] = c.[Value]
JOIN [dbo].[CodeNamespace] n ON n.[Id] = c.[CodeNamespaceId] AND n.[Name] = 'MaritalStatus'
GO
-- Select from View
SELECT *
FROM [dbo].[Person_v]
GO

This is excellent write up byMarko Parkkola. Do you have this kind of design setup at your organization? Let us know your opinion.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Brief Note about StreamInsight – What is StreamInsight

StreamInsight is a new event processing platform introduced in upcoming version SQL Server 2008 R2. Similar to other components such as SSIS, SSAS or Service Broker, it also needs to be installed along with the SQL Server.

Up to SQL Server 2005, Microsoft’s main focus on SQL Server was to build a platform to efficiently store, manage, and retrieve data. However, now, Microsoft enhanced SQL Server to accept, monitor, and respond to complex and high number of events in near zero latency. For this, Microsoft introduced StreamInsight using the following approaches:

  1. Continuous and incremental processing of unending sequences of events.
  2. Lightweight streaming architecture that supports highly parallel execution of continuous queries over high-speed data.
  3. The use of in-memory caches and incremental result computation provide excellent performance with high data throughout and low latency.
  4. Low latency is achieved because the events are processed without costly data load or storage operations in the critical processing path.
  5. All processing is automatically triggered by incoming events. In particular, applications do not have to incur any overhead for event polling.
  6. Static reference or historical data can be accessed and included in the low-latency analysis.

In StreamInsight, the events are processed by a CEP (complex event processing) Server. CEP Server has its own optimizer and standing queries to handle events optimally in real time. In simple terms, we can understand it as given below:

This architecture is designed to process up to 100,000 or even more events per second.

Following are few areas where this new platform would help in monitoring, mining, and analyzing the data in motion and in providing significantly more informed business decisions in real-time:

  • Manufacturing process monitoring and control
  • Clickstream analysis
  • Financial services
  • Power utilities
  • Health care
  • IT monitoring
  • Logistics
  • Telecom

Another important thing about StreamInsight is that the developers do not have to learn any new language to implement it. Its CEP Server (standing queries) can be created in LINQ (Language Integrated Queries), while the event Source application and Target application can be written in Microsoft .Net language. By using LINQ, developers familiar with SQL will be able to quickly write queries in a declarative fashion. Its deployment is also simple with flexible methods such as integrating in an application as a hosted DLL or running as a stand alone server through a wrapper such as an executable or windows service.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – 1200th Post – An Important Milestone

Today is the last day of 2009 and this is my 1200th post! This year had been a wonderful year for me. I was actively involved with the community, and there were a lot of occasions where I could work along with IT professionals to resolve their issues in projects.

Today, as this is my 1200th post and last day of 2009, we will go over few but very important milestones of this year (of course, in my life).

Instead of longer list, I have decided to list only the most important events. Event listed event are in order of its occurrence and not necessarily ordered as their importance.

Top 7 Community and Personal Events

1) SQLAuthority News – Author Visit – Complete Wrapup of Microsoft MVP Summit 2009 Trip
2) SQLAuthority News – Summary of TechEd India 2009 – A Grand Event
3) SQLAuthority News – SQL Server Energy Event with Rushabh Mehta – May 20, 2009
4) SQLAuthority News – Business Intelligence Training Roadshow August September 2009
5) SQLAuthority News – Community TechDays in Ahmedabad – A Successful Event
6) SQLAuthority News – Notes of Excellent Experience at SQL PASS 2009 Summit, Seattle
7) SQLAuthority News – Notes from TechDays 2009 at Infosys, Bangalore

Top 5 Personal Events

1) SQLAuthority News – 1000th Article Milestone – 8 Millions Views – Solid Quality Mentors
2) SQLAuthority News – MVP Award Renewed
3) SQLAuthority News – Baby SQLAuthority is here!
4) SQL Authority News – Advanced T-SQL with Itzik Ben-Gan – Solid Quality Mentors
5) SQLAuthority News – An Year of Personal Events – A Life Outside SQL

Top 7 Blog Articles by Readers’ Choice (In order of Popularity)

1) SQL SERVER – 2008 – Interview Questions and Answers Complete List Download
2) SQL SERVER – Insert Data From One Table to Another Table – INSERT INTO SELECT – SELECT INTO TABLE
3) SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL
4) SQL SERVER – 2005 – Create Script to Copy Database Schema and All The Objects – Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects
5) SQL SERVER – Import CSV File Into SQL Server Using Bulk Insert – Load Comma Delimited File Into SQL Server
6) SQL SERVER – Retrieve Current Date Time in SQL Server CURRENT_TIMESTAMP, GETDATE(), {fn NOW()}
7) SQL SERVER – TRIM() Function – UDF TRIM()

Year 2009 was great year and I wish all of you that next year of 2010 brings more prosperity and happiness in all of yours life.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – Meeting SQL Expert Imran at Hyderabad

I was very fortunate to meet the SQL Server Expert and one of the top participants of this blog Imran Mohammed. Imran has been very active on this blog and have previously contributed with few articles as well. I have been communicating with Imran for a long time; he is always very active and quick to reply. Many times, he has solved various difficult problems of readers which. He always goes an extra mile to resolve such problems – once I happened to see him spend more than 10 hours to solve a problem posed by a reader.

When I met Imran, I was totally surprised!! Seriously, I was expecting a very old person with a huge experience in SQL. He is one of the very young SQL enthusiasts with extremely smart brains. We had wonderful dinner together, wherein we discussed SQL. While talking to Imran, I realized that he is a very matured person with a great attitude towards life, particularly towards technology. He is a patient listener who imbibes information with a very open mind. Our discussion covered different technologies. Please note that even though he is very young due his in-depth knowledge on SQL was at par with that of someone who was in this industry for  very long time. I asked him to share some tips from his real-life experience.

Pinal and Imran

Here are few tips I have gathered from him while we discussed SQL. Some are generic to life, and few are very specific to SQL as well.

  • I do not take a chance, I always take backup.
  • If I am answering a question to anybody, my first goal is to solve the problem and second is to find it right and optimal solution for the first goal.
  • If I come across any problem with answer – first I solve it, then only I look at the given answer Advance feature of T-SQL regarding ranking, query guide, and filtered indexing are really helpful to improve the performance.
  • Before leaving a comment at a blog post, I always read original post and all the comments left before.
  • I do not remember syntax, but I do not forget them if I use them once.
  • I work sometimes more than 16 hours as I enjoy working with SQL.

I must say that I always have a great time meeting experts like Imran Mohammed; all my best wishes for his success!

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Differences in Vulnerability between Oracle and SQL Server

In the IT world, but not among experienced DBAs, there has been a long-standing myth that the Oracle database platform is more stable and more secure than SQL Server from Microsoft. This is due to a variety of reasons; but in my opinion, the main ones are listed below:

A. Microsoft development platforms are generally more error-prone and full of bugs.

This (unfairly) projects the weaknesses of earlier versions of Windows onto its other products such as SQL Server, which is a very stable and secure platform in its own right.

B. Oracle has been around for longer than SQL Server and must therefore be more stable and secure.

Well, this does not count for anything. Being around longer does not mean that you are necessarily wiser. Need more proof? – look at General Motors.

Let us look at the comparisons between Oracle’s DB platform and SQL Server:

Number of reported vulnerabilities for per product

In my opinion, this is the most basic test for stability and security – the number of errors and bugs reported for a product is roughly proportional to its security and stability. Note that this number is usually compiled by independent information-security companies; so, there is no question of “hiding the numbers.”

In this regard, Oracle fares poorly as compared with SQL Server. Oracle Corporation releases an amazingly large number of patches and Critical Patch Updates (CPUs) for its DB platform. To be fair, following are some of the arguments that support Oracle DB (together with answers for those same arguments):

Oracle runs on several platforms, while SQL Server only runs on Windows

Answer: No, the patches and bugs reported are almost all cross-platforms, which implies that they are OS-independent.

Oracle DB also includes several other components, so we are not comparing like with like

Answer: Here, I considered only the database server components. This implies that any problem arising from components such as the Intelligent Agent or the Oracle Application Server has not been included.

Let us compare the Nov 2009 vulnerability reports of the both Oracle11g [1] and SQL Server 2008 [2].

Product Advisories Vulnerabilities
SQL Server 2008 0 0
Oracle11g 7 239

This is not only for the latest DB platforms: Oracle 11g and SQL Server 2008. No, if we take a historical perspective, Microsoft patched 59 vulnerabilities in its SQL Server 7 – 2000 and 2005 databases in the past 6 years, while for the same period Oracle issued 233 patches for software flaws in its Oracle 8, 9 and 10g databases. Moreover, in 2006, Microsoft SQL Server 2000 with Service Pack 4 was ranked as the most secure database in the market together with the PostgreSQL open source project. Oracle10g was placed at the very bottom of the same list.

DBAs are wary and tired of patching the Oracle DB

A survey conducted in January 2008 [3] showed that two-thirds of Oracle DBA’s do not apply security patches. The underlying cause of this is that Oracle Corporation releases a huge number of patches and fixes for various bugs, which itself leads to this secondary problem. There is a lot of fatigue and effort involved in tracking, testing and installing several patch releases every year. In 2009 alone, Oracle released 33 patches for its DB.

However, I am not at all suggesting that Oracle DBAs are lazy or do not take database security seriously. The main reason why many DBAs are very wary of patching Oracle databases is the complexity involved. First, note that patch testing, and also CPU testing is a long and intensive process. Because of the large numbers of bug fixes and CPUs released by Oracle, many application vendors whose products run on an Oracle DB simply do not have the time to test a patch, or as soon as they do so, another one is released. This, in turn, implies that if their clients risk installing unapproved patches, then the vendor can rightfully refuse to support them in case that patch then causes an error in the application.

Slavik Markovich, the Chief Technology Officer of database vendor Sentrigo Inc, said at a conference:  “To apply the CPU, you need to change the binaries of the database. You change the database behavior in some ways that may affect application performance. So applying security patches to a database typically involves testing them against the applications that feed off the database. This is a very long and very hard process to do, especially if you are in enterprises with a large number of databases and applications. Applying these patches means months of labor and sometimes significant downtime, both of which most companies can’t afford.”

Microsoft has a working system of patch testing and rollout, whereas Oracle does not have such a system

Trustworthy Computing is a Microsoft tool that proactively identifies and allows you to install missing patches. When Microsoft launched this initiative, many people did not take it seriously. But now it has proven to be a lifesaver for many busy DBAs and system administrators who simply do not have the time to worry about installing patches. Oracle does NOT have an equivalent tool.

Also, Oracle also does not make life easier for companies who want to keep their databases secure, making it complex to download and install patches. With SQL Server, you can schedule automatic installation of updates and patches. Moreover, if it causes an undesired effect on your application, you can simply uninstall it, leaving the database at it was prior to the update. This is somewhat similar to the System Restore feature of Windows. With Oracle DB, both the installation and removal of patches are complex events that are not easy to do and undo, except for a seasoned DBA.

However, the single most crucial factor in Microsoft’s DB-security-management success is its Security Development Lifecycle (SDL). The use of SDL [4] implies that knowledge obtained after resolving the problems is never lost; instead it is ploughed back into the cycle. Therefore, instead of repeating the same mistakes every time, you can at least ensure that the new code is more secure than the old code, even though it is not completely secure. For instance, the mistakes that were committed and resolved while developing SQL Server 2005 were not repeated during the development of SQL Server 2008. However, there is one issue that bothers developers and DBAs who use Oracle DB: they come across the same mistakes in every version used by them. Eventually, when one problem is resolved, many a time the results are not problem-free and in turn, a new error or problem is created – overall, there is no consistent and reliable problem-solving technique for correcting bugs and fixes. In fact, database consultant Karel Miko estimates that Oracle Corp. is about 5 years behind Microsoft in patch management.

Summary

I hope this article helps to debunk the myth that SQL Server is a less stable and less reliable platform than Oracle DB. As many researchers and security consultancy firms worldwide have pointed out, SQL Server is consistently more secure and much less prone to errors and bugs than Oracle DB.

Sources:

[1] http://secunia.com/advisories/product/18050/?task=statistics_2009

[2] http://secunia.com/advisories/product/21744/?task=statistics_2009

[3]http://www.computerworld.com/s/article/9057226/Update_Two_thirds_of_Oracle_DBAs_don_t_apply_security_patches

[4] http://www.infoworld.com/d/security-central/database-expert-oracle-trails-microsoft-patch-management-166

Reference : Pinal Dave (http://blog.SQLAuthority.com)