SQLAuthority News – Featured on Channel 9

This blog was featured on Channel 9 MSDN over here : TWC9: Scott Hanselman, Jon Galloway, Bing, parallel unit tests, more. I was very proud that this blog was discussed for more than 5 mins (from min 18 to min 23) on my favorite online show. Scott Hanselman, Jon Galloway along with Dan Fernandez make this show very live and very very entertaining.

The article which was featured in the show is SQL SERVER – Comma Separated Values (CSV) from Table Column.

Here are few screenshot from the show.

channel9 1 SQLAuthority News   Featured on Channel 9

channel9 2 SQLAuthority News   Featured on Channel 9

channel9 3 SQLAuthority News   Featured on Channel 9

channel9 4 SQLAuthority News   Featured on Channel 9

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

SQLAuthority News – A Daily Doze of Technology – Alvin Ashcraft’s Morning Dew

A common question that I receive is regarding how I keep myself updated with latest information about technology and what is going on at present. I read lots of blogs and books. I am usually traveling 4 days in my any regular work week. I read physical books at the time. I prefer to read the books in hard copy and not on the computer screen. If you ever spot me reading books, quite often you can see me with a fiction book rather than a SQL Book.

Ok… So the question is what do I read to keep myself updated with latest knowledge? Well, the answer is very simple; I read the daily post by Alvin Ashcraft. Everyday, he makes a list of the best of the best posts of different technologies and lists them category-wise in a single post. You might have came across many technology bloggers like him earlier but just like any other regular blogs, many have either stopped blogging and some stop updating their blogs at all. Well Alvin has been blogging for long time and has been consistently updating on different technologies. The quality of the links Alvin has been including is just getting better day by day.

I must admit that I have been addicted to his blog from long time. Every evening (Indian time), I wait for his blog to be updated; this way, I can read what has happened in the previous day. Alvin is from Philadelphia – also a husband, dad, atheist and geek – and has about 14 years of experience with software development.

Read Alvin Ashcraft’s Morning Dew. No matter when you are reading this post, I am sure that visiting his blog will bring up the latest news updates from technology.

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

SQLAuthority News – Subscribe to Blog – Search a Blog

Quite often I get request if I send blog post in newsletter or through email. Here are few important links.

You can for sure get email of my post, however, I strongly suggest to visit blog as if there are any updates in my post they are reflected on blog.

Subscribe to blog post through email

Subscribe SQLAuthority Feed

Search SQLAuthority – This is very powerful search. Give it a try.

Follow me on Twitter

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

SQL SERVER – 2005 2008 – Backup, Integrity Check and Index Optimization By Ola Hallengren

Script of Backup, Integrity Check and Index Optimization are the most important scripts for any developer. SQL Expert and true SQL enthusiast Ola Hallengren is known for his excellent scripts.

Please try it out and let me know what you think. The documentation is available on http://ola.hallengren.com/Documentation.html and the script can be downloaded from http://ola.hallengren.com.

Here is brief documentation sent by Ola himself for his script in his own words.

Backup Maintenance

I think that most of you have experienced the error messages “BACKUP LOG cannot be performed because there is no current database backup.” and “Cannot perform a differential backup for database “”, because a current database backup does not exist.”.

This usually happens when you have created a new database or when you have changed recovery model of a database from Simple to Full.

The consequence is that the database is not getting backed up, until a full backup (or a differential backup for the BACKUP LOG error message if that can be done) has been performed. It will also create some noise in your monitoring system.

The solution is to check if a differential or transaction log backup can be performed before doing the backup. This can be done by checking sys.master_files.differential_base_lsn and sys.database_recovery_status.last_log_backup_lsn.

The backup solution that I have developed has a parameter called @ChangeBackupType.

EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory = 'C:\Backup', @BackupType = 'LOG', @ChangeBackupType = 'Y'

If it’s a new database in Full recovery model, the backup type for that database and job run will be changed to full. If it’s a database that was newly changed to Full recovery model, then the backup type for that database and job run will be changed to differential. The next time the job runs a transaction log backup will be performed for that database.

New databases start getting backed up quickly and no more “BACKUP LOG cannot be performed because there is no current database backup.” and “Cannot perform a differential backup for database “”, because a current database backup does not exist.”.

Index Optimization

The design idea is categorize all indexes based on their fragmentation level (High, Medium or Low) and whether there are columns with LOB (Large Object) data types. For each category you can define an action. The possible actions are to rebuild indexes online or offline, reorganize indexes, update statistics, reorganize indexes and update statistics or to do nothing.

Here’s an example.

EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationHigh_LOB = 'INDEX_REBUILD_OFFLINE',
@FragmentationHigh_NonLOB = 'INDEX_REBUILD_ONLINE',
@FragmentationMedium_LOB = 'INDEX_REORGANIZE',
@FragmentationMedium_NonLOB = 'INDEX_REORGANIZE',
@FragmentationLow_LOB = 'NOTHING',
@FragmentationLow_NonLOB = 'NOTHING',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@PageCountLevel = 1000

Indexes with a fragmentation above 30% are to be rebuilt, online if possible (no LOB columns), otherwise offline (LOB columns). Indexes with a fragmentation between 5% and 30% are to be reorganized. Indexes with a fragmentation below 5% or a size below 1000 pages are not to be touched.

If you are using partitioning IndexOptimize has a parameter, @PartitionLevel to do index rebuilds and reorganizations on the partition level. If you prefer to do sort operations in tempdb you can do that with the parameter @SortInTempdb and if you would like to set a fillfactor you can do that with the parameter @FillFactor.

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

Please try it out and let me know what you think. The documentation is available on http://ola.hallengren.com/Documentation.html and the script can be downloaded from http://ola.hallengren.com.

SQL SERVER – SQL Server Desktop Screen Background

Buck Woody (MSFT) has published a blog post about SQL Server Desktop Screen Background. I really like the SQL Server Desktop background and I have replaced that background on my work laptop. I came across this particular post because I am regular reader of his blog. Few of the other interesting posts written by him are following.

Who do I talk to in Microsoft about…

Test your Restores, or, a Cautionary Tale

If due to any reason, the original image is moved, you can download the image by clicking on following image as well.

sql2k8BG S SQL SERVER   SQL Server Desktop Screen Background

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

SQLAuthority News – Big Thinkers – Robert Cain

I am exceedingly impressed and inspired by an on-going series of Big Thinkers by Robert Cain – A SQL Server MVP and a genial, whole-souled person.

Robert Cain and Pinal Dave

Robert Cain and Pinal Dave

On meeting Robert Cain earlier this year at SQL Server MVP Summit in Seattle I asked him a question – Where do you get so many innovative ideas to write on blog and create presentations? He replied, “I do not try to get ideas, my experience inspires me.”

Well, it is true that Robert has more than 10 years of experience as one of the TOP experts in SQL Server. Unlike most of the SQL Server MVPs his knowledge is not limited to SQL Server; he is well versed in ASP.NET and Business Intelligence (BI) as well. What is really inspiring and great about him is that he has maintained his position as a top technology expert and has used his position to help out others through his comments/answers in various forums.

Before I answer the question what is big thinkers and why Robert is a big thinker, let me tell you when did I last come across his blog. I was looking forward to learn about BI and I searched for the same in the search engine. I found the following article of Robert. I right away downloaded it and read it through and through. Instead of reading my experience about this easy-to-follow and crisp article, I would suggest you all to experience it yourself. Download the PDF from Introduction To Data Warehousing and Business Intelligence.

Robert takes personal interest when it comes to advising and playing the role of a mentor to the members of community. He has another very useful presentation available online Careers in SQL Server. Even though you are an experienced user I would still recommend you to go through this PDF to see where you stand in your career and what should be your next step.

As I mentioned earlier, Robert is one of the few experts who can fluently talk on both SQL as well as .NET. He has written a marvelous series on FILESTREAM type and storage of varbinary, which is a complete guide on this subject. I strongly suggest that you give it a look, and I promise that it is an easy read.

I may or may not write another part of this series of Big Thinkers but I salute to a real big thinker Robert Cain!

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

SQLAuthority News – Blog Makeover – New Banner – New Color

Just a month back I had  previously changed my personal homepage and had requested for feedback from my readers here SQLAuthority News – Authors Website Redesigned – http://www.pinaldave.com – Feedback Requested. To my astonishment, I received a huge number of emails. But I received only one comment. This time, I would like to request my readers to leave your comments on my blog instead of emailing it to me. This will allow everyone to know about others feedbacks and the actions I take towards incorporating the feedbacks on my blog.

Let us go over the very first and quite perceptible change.

The banner has been given a brand new look. Two major renovations have been done. First, I have replaced the previous dull image of mine with a new sharper and brighter image. Also, you all can see more of me now. I have placed a larger photo of mine; the earlier one was quite small. Second major changes that you all must have noticed is the color scheme. From the two-year old luscious green I’ve moved on to the cool blue!

In the past two years, I had changed my banner only once.  However, this time I decided to completely change my banner as well as color. It now appears spick and span. Yet another noteworthy change I have made is in the banner background. Beside my photo, there are two different images; there is a cloud like image on the left side of my image and a simple grid on the right side. Any guess what these two signify? Well, the reason behind this change is to suggest that SQL Server is moving toward cloud grid like computing world.

I have been receiving countless requests from my long list of readers to change my color scheme. I was flooded with suggestions – Facebook Blue, Twitter Blue, Google Blue, MSN Blue, MVP Blue, LinkedIn Blue, to name a few- making the task of choosing extremely difficult. I finally selected a color which is closer to Facebook blue but a little darker. Of all the medications, choosing the color was the most crucial as there was no clear definition as to which shade of blue I should settle on.

Clean up HTML and Sidebar
I have made a number of other new changes such as removing unnecessary icons, images as well some of the flairs. I think now it is uncluttered and usable. Let me have your suggestions on enhancing the side bar.

Search @ SQLAuthority.com now has the same look and feel as the main site. In addition, the search has also improved and now there will be no more delay in getting result. If you like the search do spread the words.

sa2009 SQLAuthority News   Blog Makeover   New Banner   New Color
Previous Site

New Site

New Site

Again, please send me your comments about the new design.

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

SQLAuthority News – Authors Website Redesigned – http://www.pinaldave.com – Feedback Requested

I’m pleased to inform you all that I’ve recently launched my personal website. It’s been a long time since I’ve been writing on my blog http://blog.sqlauthority.com, but I’ve been keeping my personal notes at my homepage http://www.pinaldave.com.

I’ve completely rehauled the website to give it the much-needed makeover, right from redesigning the layout to writing fresh content. But, I would be extremely happy to have your feedback so that I can enhance my website further.

I’ve always been a people’s person who believes in sharing his knowledge. Also, I want to see myself growing as an individual and as a professional. This is the reason why I pay a lot of attention to feedback. I hope my readers would provide me with some valuable feedback to make my website even better, which would in turn help me reach out to more people to accomplish my objective of serving my community.

I’ve enumerated below the significant improvements that have been made on my website.

pdcom SQLAuthority News   Authors Website Redesigned   http://www.pinaldave.com   Feedback RequestedClick on Image to see Latest Webpage

1) Clear Design

Previously, I used to receive a lot of feedback regarding the layout, which was a little confusing. Besides, the page was flooded with links that directed the visitors away from my website, which was certainly a serious concern. In contrast, the new layout contains few but relevant links that would actually benefit my website.. I have even changed the background from light brown to white for more clarity and better readability. Best of all, all the pages will now feature the latest three articles from SQLAuthority.com on left navigation panel.

2) Content
SQLAuthority.com is my key writing platform and I write on it almost every day. Till now, I have written over 900 articles.. Like any normal guy I too have my favorites and frequently used links. Some of my articles are script based and some are theory based. I have categorized them as per my preference and also based on the feedback I have received. The links are listed here.

3) Photos
I have often received comments that my photos are pretty old and I must update it. Probably, my readers got tired looking at my same old pose! So, I have updated the photos page of my website with my latest photographs. In future I will make sure to keep the photos page updated. Visit my photos page.

4) Resume
In my resume, I have added several important information related to my career. I have simplified my resume, and now it only includes relevant details on my current objective. Currently, I am working as an independent consultant and specialist in SQL Server Query Optimization and Database Architecture. You can hire me or my entire crew. I have a team of over 65 talented and qualified developers who can complete your project irrespective of size, complexity or programming language.

5) Social Media
Social Media and Social Networking are hard to separate when we are a part of society. You can follow me on Twitter or join my professional network at LinkedIn.

I am very eager to know your opinion about my new website. Please do send me your feedback.

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

SQL SERVER – Automated Index Defragmentation Script

Index Defragmentation is one of the key processes to significantly improve performance of any database. Index fragments occur when any transaction takes place in database table.  Fragmentation typically happens owing to insert, update and delete transactions. Having said that, fragmented data can produce unnecessary reads thereby reducing performance of heavy fragmented tables.

I have often been asked to share my personal Index Defragmentation Script. Well, I use Automated Index Defragmentation Script created by my friend – a SQL Expert – Michelle Ufford (a.k.a SQLFool). Michelle is a SQL Server Developer, DBA, a humble blogger, and an absolute geek! She is also the President of Eastern Iowa PASS Chapter, known as the I380 Corridor Professional Association of SQL Server. Currently, she is working with large, high volume, high performance SQL Server databases at GoDaddy.com. Her nickname might be SQLFool but honestly speaking, she is one of the best Gurus of SQL.  You can read her  blog here.

Michelle has written an excellent article about Automated Index Defragmentation Script. I recommend this wonderful write-up to all those database developers who are searching for a good solution to improve database performance. Read her article Automated Index Defragmentation Script to equip yourself with better understanding on how to improve database performance.

Here, I would to mention an interesting question taken from the above-mentioned article, which will clearly show how powerful this Automated Index Defragmentation Script is.

How long will this Automated Index Defragmentation Script  take to run?
Well, it depends. I don’t necessarily recommend running it without specifying a database; at least, not unmonitored. You *can* do that, but it could take a while. For example, to run sys.dm_db_index_physical_stats for all databases and tables, totaling 2TB, took me 4.5 hours; that doesn’t even count the actual defrags.

You can download the script from here.

Please feel free to contact Michelle or me if you have any questions and doubts regarding this script.

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

SQLAuthority News – Top 10 Strategic Technologies for 2009

Gartner, Inc. analysts highlighted the top 10 technologies and trends that will be strategic for most organizations. Factors that denote significant impact include a high potential for disruption to IT or the business, the need for a major dollar investment, or the risk of being late to adopt.

The top 10 strategic technologies for 2009 include:

Virtualization. Much of the current buzz is focused on server virtualization, but virtualization in storage and client devices is also moving rapidly.

Cloud Computing. Cloud computing is a style of computing that characterizes a model in which providers deliver a variety of IT-enabled capabilities to consumers

ServersBeyond Blades. Servers are evolving beyond the blade server stage that exists today. This evolution will simplify the provisioning of capacity to meet growing needs.

Web-Oriented Architectures. The Internet is arguably the best example of an agile, interoperable and scalable service-oriented environment in existence.

EnterpriseMashups. Enterprises are now investigating taking mashups from cool Web hobby to enterprise-class systems to augment their models for delivering and managing applications.

Specialized Systems. Appliances have been used to accomplish IT purposes, but only with a few classes of function have appliances prevailed.

Social Software and Social Networking. Social software includes a broad range of technologies, such as social networking, social collaboration, social media and social validation.

Unified Communications. During the next five years, the number of different communications vendors with which a typical organization works with will be reduced by at least 50 percent.

Business Intelligence. Business Intelligence (BI) have a direct positive impact on a company’s business performance, dramatically improving its ability to accomplish its mission by making smarter decisions at every level of the business from corporate strategy to operational processes.

Green IT. Shifting to more efficient products and approaches can allow for more equipment to fit within an energy footprint, or to fit into a previously filled center.

Reference : Pinal Dave (http://blog.sqlauthority.com), Gartner Inc.(Published after receiving permission)