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:
Continuous and incremental processing of unending sequences of events.
Lightweight streaming architecture that supports highly parallel execution of continuous queries over high-speed data.
The use of in-memory caches and incremental result computation provide excellent performance with high data throughout and low latency.
Low latency is achieved because the events are processed without costly data load or storage operations in the critical processing path.
All processing is automatically triggered by incoming events. In particular, applications do not have to incur any overhead for event polling.
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
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.
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.
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!
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  and SQL Server 2008 .
SQL Server 2008
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  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  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.
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.
This 15 modules, level 300 course provides students with the knowledge and skills to capitalize on their skills and experience as an Oracle DBA to manage a Microsoft SQL Server 2008 system. This workshop provides a quick start for the Oracle DBA to map, compare, and contrast the realm of Oracle database management to SQL Server database management.
Update: Do not forget to checkout last three photos and follow me on twitter (of course!)
I have previously documented my four-day experience of SQL PASS 2009 Summit at Seattle. There were many reasons for SQL enthusiasts to attend the SQL PASS event; I am listing my own reasons here in order of importance to me.
Networking with SQL fellows and experts
Putting face to the name or avatar
Learning and improving my SQL skills
Understanding the structure of the largest SQL Server Professional Association
Attending my favorite training sessions
During these four days, there was so much happening that it is difficult to document everything. I had tried to cover this event briefly on a daily basis in the following blog posts:
I will list a few of the additional details that I have not included in the earlier post. Keynotes were the best part of the event, and I have covered major points of the keynote in above mentioned posts. I had met many SQL Legends during this event and included information regarding them. However, there were few things I still missed and I am listing them here. Seattle is great city, and I am visiting it the second time. This time I got an opportunity to take the Underground city tour and I had really enjoyed.
Washington Convention Center
Undergroud Tour 1
Undergroup Tour 2
It was great to meet SQL Server MVP Glenn Berry at the MVP Summit. He is a very kind and friendly person. You will always find him hanging around with people from different organizations and countries. If you try to eavesdrop his conversation, you will hear terms like ‘dmv’, ‘performance’, and ‘optimized query’ very often. He is very much known for his excellent optimization script known as “SQL Server 2008 Diagnostic Information Queries“. If you have never taken a look at that, I urge you to go there and check this out. I believe that even if he blogs once a year only with this script, his fans will not complain. I have previously published his interview on my blog. Please note that he is the only person ever featured in an interview on this blog.
Glenn Berry and Pinal Dave
It depends! This is one most loved (or hated) phrase in the world of programmers or DBA. It is quite common to start answering question using this phrase. Mostly, this phrase initiates humor but rarely, people take this as offence. Again the intent of speaker is not always conveyed properly, it depends on how the phrase is used (I could not resist to use this phrase as well :P). I had very interesting discussion with Michelle Ufford and Jason Strate when we were waiting for coffee at one afternoon. We discussed both the sides of this phrase. Michelle suggested many good points when and why this phrase is valid. Jason also suggested that this phrase is commonly used a) when the presenter does not have sufficient information to answer or b) when the presenter does not know the answer. We discussed that it would be really interesting to start the conversation answering the most possible answer and then talking about special cases (for which it depends is quite commonly used.) Let us see one example.
Michelle Ufford, Jason Strate, Pinal Dave
Q: Does index improve performance?
A1: It depends on query and data you have.
A2: Usually YES; however, there are cases when it reduces performance, for which the query and data needs to be examined carefully.
There are times when it depends totally make sense. I use it several times; I love the blog of Andy Warren which talks is branded as it depends. This discussion between three of us was just a friendly discussion, and if you think we are talking about you, it is wrong!
During this event, I had great conversation with SQL Legend Buck Woody. This man is full of energy and may be the most loved person in this summit. His session was the funniest session I have ever attended. At the end of his session, I had more than 2 pages of my notebook filled with notes. If I have wrote down how many times, I have laughed loudly, I would have no time to do anything else. Buck had a good hour of discussion with me. We discussed many different topics, ranging from Community to Microsoft and Collage Students to SQL. I am very thankful to Buck for spending his valuable time with me – I am honored.
Buck Woody and Pinal Dave
Joe Celko – this name is very popular in SQL world, and I admit that I was always afraid of him. Every time I see his photo, I just felt nervous. I never thought that I should meet him. Honestly, for the same reason, I did not attend his session. I was absolutely wrong. I met him right after his session was over. He is one of the most friendly people I have met in my life. He is very soft spoken. He gauges the level and understanding of other person and speaks at the same level. He made me laugh and taught many good techniques for how to become good speaker and presenter. When I said I was sorry that I was scared of him, he laughed and said that he knows this. Boy, what a mistake I did!! I should have attended his session. Well, at least I am accepting my mistake here. He is indeed a very friendly soul. He promised me that one time he will come to India.
Brian Moran is the newly elected board member; I would like to call him as ‘the Man with 1000 successful ideas‘. You have to come across him to know what I am talking about. I had the great opportunity to meet him and discuss few things, besides SQL. Brian had many excellent ideas about how to build the community, how to help SQL PASS and few excellent ideas about how to take PASS to the next level. I am glad that he is one of the board members for PASS. PASS is all about passion, and Brian clearly demonstrates the same.
Pinal Dave, Joe Celko, Brian Moran
I met all of them and had a great conversation with them. It was great to meet Ed Hackney; he is a wonderful person and very easily approachable. He talked about SQL community in India and how any community cannot be restrained within geographical boundaries.
Ed Hackney and Pinal Dave
I was waiting to meet Thomas LaRock. He is an excellent SQL Master, and he talked in detail about outsourcing SQL technology and SQL community. I really enjoyed meeting him and spending some time with him during the summit. I plan to learn more from him when I meet him next time.
Thomas LaRock and Pinal Dave
Robert Cain, he is one of the very few SQL masters, who have expertise in .net and Business Intelligence at the same time. I have previously wrote about him in Big Thinkers – Robert Cain. He has given approval that I can write about his BI presentation on this blog. I will write about it soon.
Following image is for sure very interesting! Can you recognize the person? Well, just go here and read about Mr. Denny. And I do have permission from him to publish this photo!
PASS 2010 dates are announced; this event will take place between 8 and 12 November 2010.
Following image is my favorite as it brings the future and past of PASS together. Kevin Kline, a hero of PASS summit, (as he dedicated 10 years of his life to serve PASS board) is in same frame with Rushabh Mehta, who is President Elect for 2010.
Past President Kevin Kline and Future President Rushabh Mehta
Now I am coming to the most interesting part of my experience. I want to introduce to all of you the youngest SQL PASS fan and follower. Her name is Shaivi Dave, and she is big fan of SQL PASS! Shaivi suggests that save your money and register right away for PASS. There is no other conference which is as valuable as SQL PASS.
Nupur Dave and Shaivi Dave
Pinal Dave and Shaivi Dave
She had conveyed her wish to SQL PASS President Elect Rushabh Mehta when she was just 19 days old. May be one of following years, we will see her at summit.
Rushabh Mehta, Shaivi Dave, Pinal Dave
This is note is addendum to my four original posts. I strongly suggest you all to read my earlier posts for details on keynote and other SQL legends.
If you have visited this event and have something more to add to what I have written, please feel free to add it here as a comment. I am looking forward for you all to add more details and information here. Follow me on twitter.
About a week ago, SQL Server Expert, Imran Mohammed, provided a script, which will list all the missing identity values of a table in a database. In this post, I asked my readers if any could write a similar or better script. The results were interesting. While no one provided a new script, my question sparked a very active discussion that is still ongoing.
When providing the script, Imran asked me if I knew of any specific circumstances in which this kind of query could be useful, as he could not think of an instance where it would be necessary to find a missing identity. I was unable to think of a single reason for listing missing identities in a table. I posted Imran’s script on the assumption that someone would come up with an improved script, but as mentioned earlier, nobody did. Instead, we have been able to follow a very interesting discussion on subject of the need, if any, for listing Missing Identity values.
So, the question is this: “Do you know a real-world scenario where a Missing Identity value in any table can create problems?“
I have already received some extremely interesting comments from many experts, and all have posed the above question in one form or another. At this moment, I am still trying to think of an example from my own experience, but have yet to find one. Imran has since come up with one good example. Here is what he and other experts have suggested so far.
Jacob Sebastian – IDENTITY values are not expected to be sequential and there are all chances of having missing identity values, the most common cause is transaction rollbacks.
Simon Worth – The identity column is basically just a random number – even though they come sequentially. A developer making an assumption that the next record inserted will have an identity that is 1 more than the last inserted record. And if this is the case – then there are flaws in the logic of the developer.
Jacob Sebastian – What if the value in my table is 1, 2, 3, 5, 6 etc where “4″ is missing from the sequence. So what is the importance of knowing whether a table has missing identity values or not?
Imran Mohammed – If you use Identity property as your most unique column and Transaction Identifier, then definitely you would want to know why few transaction did not completely, Is there any specific fashion these transaction fails (Can be found out looking at missing values of identity)… Could be helpful to debug.
Today is July 30 and I am very happy; it’s my Birthday, celebration time!!! The most common question I receive on my every birthday is -what are my plans for birthday. Let me share my plans here today. Additionally, if you are interested to know when SQL Server was born read my post SQLAuthority News – Author BirthDay – SQL Server Birthday.
My first plan is that I am going to take a break from blogging on anything technical today and spend more time with my family.
Let me tell you about my second plan. I am very much pleased and encouraged with active participation from many of you on this blog. I have received so far more than 10s of thousands of comments on my blog. These comments are open for you all to read and you can use them in any fashion that can help you out. Without good participation there is no life in any community, whether it is offline or online.
For the same reason, I have introduced Top 7 Commenters section in the side bar of SQLAuthority.com. There are many active users who have been participating for a long time. One of them I just recognized is Imran Mohammed. This SQL Expert has been extremely helpful to the community and has been providing comprehensive answers to hundreds of questions. Hats Off to you Imran! I totally appreciate your knowledge and dedication to SQL community. Without passion and love toward helping community, this is absolutely not possible.
You can subscribe to SQLAuthority.com feed, which will display all the new posts of my blog as they are published.
There is no automated feature for top commenters where I host, so I will have to calculate this manually. I will keep it updated on weekly basis. Every week we will see new names there. First column is number of post, and second column is number name with link to experts website or blog or email.
Every day I receive a couple of emails about starting a forum on SQLAuthority.com. I do plan to start the forum in future. Currently, there are plenty of very good forums on the web. SQLAuthority.com is a blog and below every blog post there is a discussion related to the subject discussed in the blog post.
SQLAuthority.com is all about community and people who are passionate about SQL. Everyone is welcome to participate here. There are many ways to participate.
Discussing blog post
Helping user who have posted their issues on blog
Writing Guest Post
Submitting useful SQL Scripts
Reviewing any Microsoft-related products
Let me know if you have any other useful and interesting suggestions on how I can involve YOU more in my efforts to take SQL community to the next level.
If you are interested in participating in volunteer program do send me email at pinal ‘at’ SQLAuthority.com and we can discuss something out.
Let us take a look at an example where we will generate script database. In our example, we will just take one table for convenience.
Right Click on Database >> Tasks >> Generate Scripts >>
This will pop up Generate SQL Server Scripts Wizards >> Click on Next >> Select Database >> This will bring up a screen that will suggest to Choose Script Option.
On Choose Script Option Screen of Script Wizard under section Table/View Options Look at Script Data row and Turn the Option to True.
The Next Screen will ask about object types. Select all the objects that are required in the generated script. Depending on the previous screen it will show few more screen requesting details about the objects required in script generation.
On the very last screen it will request output options. Select the desired output options of Script to file, Script to Clipboard or Script New Query Window.3
Clicking on Finish button will generate a review screen containing the required objects along with script generating data.
Clicking on Finish button one more time will generate the requested output.
Similarly, if you want to generate data for only one table, you can right click on the table and follow almost a similar wizard. I am sure this neat feature will help everybody who has been requesting for it for a long time.
Watch SQL in Sixty Seconds Episode on same subject.
I have been planning to write a general post on the latest technology for a long time but SQL keeps me so busy that I hardly get time. I know being busy is no excuse as everybody is busy with something. A manager is equally busy managing people as much as a peon busy doing errands. Now, coming back to my topic, I have lots of news to share with you all.
Anyway, number one news is that Bing has been finally released a couple of days back. I am very much excited as something is finally challenging Google – The Search Engine King. Google has been my preferred search engine for many years and my home page too. However, I am quite impressed with Bing which has a clearer and more organized interface. The navigation on the left side does make sense. My opinion is that there is no other option for Google but to improve to retain its popularity. In the search engine race Google has always been a clear winner for many years now, and people have always appreciated its simplicity and most importantly, its accurate results. But now, for the very first time I think Bing is going to challenge Google on two fronts – accuracy of search and simplicity of interface. Only time will decide who will rule, but I would like to give Bing a fair chance to improve itself.
Another major improvement I have done is that I have finally upgraded 3 of my 4 computers to Windows 7. I have never hated Vista for its shortcomings. I have seen people and media pointing negative things about it. In fact I think it has now become a trend to just bad mouth Vista. I thoroughly believe that Vista was a significant improvement over XP. There was a time when XP was everywhere and every software was designed in keeping with its compatibility with it. Then, Vista emerged in the market. XP required less hardware while Vista necessitated upgradation of hardware of most of the computer systems. This was not appreciated by many users as they had to spend money on hardware apart from spending on Vista. Additionally, it took a good amount of time for companies to come out with drivers of Vista. Today, Win 7 is out and getting loudly applauded by the same people who criticized it. Win 7 is definitely better and I love it! It has everything what Vista had and even more. If people hated Vista for some of its drawbacks, in that case Win 7 will not disappoint them and it will exceed their expectations with its additional features. Slowly and steadily, Win 7 is coming out as a winner. It took sometime for Vista to get accepted, which was entirely hardware companies fault as they did not release its driver. But today almost all the companies have Vista compatible drivers which work fine with Win 7, thereby making Win 7 more acceptable.
There is one more reason why Win 7 has become my favorite OS. My wife – Nupur Dave has an old laptop, which is single core (when we purchased it there was no concept of double core) and had XP so far. Since the past one month, my wife kept on complaining that her laptop takes forever to start. As her laptop is 6 years old with 1.6 GHz, Single Core and 1 GB RAM, we decided to purchase a new one. However, I decided to give it a shot and see if I can install Win 7 on this old machine. To my surprise, it took only 25 mins to install the whole operating system and it works better than XP. Now this is something really good about it and it saved my bucks!
What else is keeping me busy? There are so many things to say. I am reading a plethora of books and blogs. I have a collection of over 500 books in my home library and have almost the same number of feeds in my RSS reader. I recently finished a book Pro T-SQL 2008 Programmer’s Guide. It is written by one of the most popular book authors Michael Coles. You can read his interview here. His interview was taken by a dear friend of mine who is a .NET expert Vijaya Kadiyala. He is not only writing about .NET but also writing on SQL. Besides, whenever I have free time I try to solve T-SQL Challenges written by my fellow MVP Jacob Sebastian.
Well, my friend Abhishek Kant suggested me to start using twitter but not to forget my blog. Every time I spend too much time on twitter, I remember his saying: Hats Off! Thanks Abhishek for giving me these golden words. I will keep on blogging.
Thanks John-Wu Zioa for sending this cartoon. I’m flattered!
Now you know what I think of the latest technologies and what I have been doing. I will appreciate your comments for the same and please feel free to share your experience here.