New in 2012 – Videos

Back in 2008, I’ve created a series of videos on SQL Server 2008 administration that were made available on BlogCast Repository website. It’s been quite a while since I’ve had my hands on a video recording software. And since I regularly write articles for MSSQLTips.com, I’ve decided to also translate those articles into video tutorials. Now, you might be thinking, “Why videos when you already have articles?” Most of you know that I’m an educator/teacher/communicator. I try to make learning as simple and engaging as I possibly can, incorporating stuff like graphics and proper use of the color palette. And while there are a ton of written technical articles out there, the use of a visual mode of communication such as video helps reinforce the learning process. I, for one, watch a lot of online videos and recorded webcasts when trying to learn something new for the first time. Aside from the fact that my eyes tend to hurt when I do a lot of reading online and offline, watching videos helps decrease my eyes’ exposure in front of the screen (I can easily turn off my eyes and just listen to the recording.)

That being said, I look forward to creating those video tutorials this 2012.

SQL Server 2012 High Availability Webcast Resources Now Available

There have been requests from attendees of last week’s webcast to make the resources available for download. If you’ve attended or viewed (thru MSSQLTips.com’s recording) my webcast on SQL Server 2012 High Availability with AlwaysOn, you can download the resources – slide deck, Windows-based client application together with the Visual Studio 2010 project files, batch files to simulate workload and T-SQL scripts – here (you need to change the file extension from PDF to ZIP and extract the contents). I would, however, request that you’d fill in your information here for my reference and feedback. If you would be using these resources for purposes other than personal – presentations, blog posts, articles, etc., please provide proper attribution to the original source.

I definitely would love to hear from you as this helps me improve my work.

From *-SqlHadrService to *-SqlAlwaysOn

If you’ve been playing with SQL Server “Denali” AlwaysOn, you probably have seen the new PowerShell cmdlets for managing the new AlwaysOn feature. However, as SQL Server 2012 RC0 has been made available for download, you need to watch out for these “breaking changes” for AlwaysOn (I call them breaking changes because if you are using PowerShell scripts to automate AlwaysOn configuration, your scripts would no longer work.)

With SQL Server “Denali” CTP3, you may have used cmdlets like *-SqlHadrService to enable the AlwaysOn features across all instances that you join in your availability group.

Starting with SQL Server 2012 RC0, these have now been converted to *-SqlAlwaysOn.

So be sure to update those PowerShell scripts before you run them for testing to make sure you don’t waste your time trying to figure out what went wrong. And if you haven’t downloaded SQL Server 2012 RC0 yet, make sure you do so and get your hands dirty trying out the latest bits.

And since we’re already on the topic of SQL Server 2012, you also might want to catch my webcast on AlwaysOn Availability Groups on 30-Nov-2011. You can register for the webcast thru this URL.

Building a Scale-Out SQL Server 2008 R2 Reporting Services Farm Whitepaper Now Available

Back in 2009, I wrote a 4-part series article on installing SQL Server 2008 Failover Cluster on Windows Server 2008 and, for the past two years, has been on the Top Ten Tips. I think one of the reason is because of the fact that we humans learn easily with the use of visuals (the articles are packed with screenshots and explanations). And since the internet has provided us with the opportunity to explore different types of media – graphics, videos, animations, etc. – our demand for these different types of media has increased with our need for continuous learning.

Last year, I was searching for official documentation on building a scale-out SQL Server 2008 R2 Reporting Services farm from both the Microsoft TechNet and MSDN sites. However, the amount of documentation and the way they were written were too much for me. I guess that’s because they were written for a more highly technical and advanced audience. In more cases than not, I’ve been frustrated when I can’t even understand the jargon used in the documentation. And I’m guessing I’m not alone. More and more IT professionals want to try out new stuff but are taken aback by the complexity of the documentation available out there. And, so, with the same motivation behind the SQL Server 2008 Failover Cluster series of articles, I wrote a document on building a scale-out SQL Server 2008 R2 Reporting Services farm. I thought, since I’ve been doing it or SharePoint, why not do it for Reporting Services. This was the basis of the five-part series article made available on MSSQLTips.com.

You can download the whitepaper from here. I would, however, request that you’d fill in your information here for my reference and feedback. Print out as many copies as you like, share it with your friends who you think will benefit from it or send them the PDF version. If you would be using this document for purposes other than personal – presentations, blog posts, articles, etc., please provide proper attribution to the original source.

I definitely would love to hear from you as this helps me improve my work.

Watch Out for SQL Server 2012 AlwaysOn Webcast

My friends at MSSQLTips.com asked me to do a webcast on SQL Server 2012 AlwaysOn Availability Groups. You’ve probably noticed that I have not been writing anything about SQL Server 2012 (formerly “Denali”) from both the articles that I’ve been writing and the previous blog posts as compared to when SQL Server 2008 was being released. That’s just my preference specifically because of all the stuff that I can’t talk about back then. Well, now that it is officially out in the public with the new name, I guess I no longer have to worry about mentioning anything that isn’t publicly available.

Being a high availability/disaster recovery (HA/DR) guy, AlwaysOn is one of the features that I like about SQL Server 2012. This provides organizations with more options to consider when implementing an HA/DR solution.  Two things that are being introduced here are AlwaysOn Availability Groups and AlwaysOn Failover Clustering.

On the 30th of November 2011 (3PM EDT), join me and the guys from Fusion-IO and MSSQLTips.com as we explore this new feature in SQL Server 2012 called AlwaysOn Availability Groups. To register for this webcast, simply click on this link. If you have questions about AlwaysOn Availability Groups  even before the webcast, you can post them here so we can discuss it further. I will try my very best to make sure that your question be mentioned during the webcast

Watch Out for Microsoft TechDays Canada 2011

If you happen to be in Canada, you probably have heard about Microsoft TechDays – the premier technical conference for IT professionals and developers. This year, three cities – Vancouver, Montreal and Toronto – will be hosting this 2-day event. Two years ago, I did a presentation “on SQL Server on Hyper-V” as well as “free/cheap tools for IT professionals.  This year, I will be delivering a presentation on Upgrading to SQL Server R2 and SQL Server “Denali” (now officially announced as SQL Server 2012) in Toronto on the 26th of October 2011. If you’re in town, let’s catch up for a cup of coffee as I will be at the Data Platform booth the whole day (except during my presentation) answering questions and providing guidance on how you can maximize your investments on the SQL Server platform. I’ll see you there.

24 Hours of PASS Fall 2011 Session Recordings Now Available

I’ve you’ve missed watching the webcasts, the recordings are now available online. You do have to log in using your PASS website credentials so if you haven’t registered yet, this is a good time to do so.

Cannot show requested dialog “There is no row at position 0″ and Making your Microsoft Connect Items Matter

Tags

bug, Microsoft Connect, SQL Server Management Studio

I have not opened a Microsoft Connect item before so I don’t know how the bug reporting and resolution process works at Microsoft for the SQL Server product. I just happened to receive a phone call from one of my customers asking why they are getting the error message below on one of their SQL Server instances.

Cannot show requested dialog “There is no row at position 0″

He is using SQL Server 2008 R2 Management Studio to connect to several SQL Server instances. One particular scenario was that he was getting this error message when retrieving the database properties of one of the SQL Server instances using the Properties dialog box in Management Studio but not on the rest of the instances. Google did return several results on resolving this issue. In case you hit this particular issue, follow the steps outlined below.

1. Query the spt_values table in the master database. If you don’t get any results, it means that the table has been truncated. How it got truncated is an exercise for you to figure out
2. Look for the u_tables.sql file usually found in the <drive>:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL00\MSSQL\Install folder
3. Run the u_tables.sql file to re-populate the spt_values table in the master database

After running the SQL script, verify that you can now open up the Properties dialog box from within SQL Server Management Studio.

Now, my curiosity didn’t stop there. I found Microsoft Connect Bug ID 258528 which is marked as Closed and Not Reproducible. I asked the guys from the SQL Server Product Team about why the Connect item was closed and, according to them, it was a terribly written bug. Initially, I had the feeling that they were just trying to avoid me altogether but knowing how dedicated and committed they are to addressing issues with SQL Server and delivering a great database platform, I asked even further. It gave me an understanding of how bug fixes are addressed. Whenever a Microsoft Connect Item is opened, the teams reviewing the item need to identify a handful of information, including

• how to reproduce the problem
• the root cause of the issue
• the impact on users
• the frequency with which the problem occurs
• the cost and risk involved in fixing it
• any available workarounds

This simply means that the more detailed information provided regarding the issue, the easier it is for Microsoft to triage, fix  & then verify the issue is actually fixed. It’s like telling your mechanic that your car won’t start when you didn’t even tell him about your broken car key or that the fuel tank is empty, etc. So, if you’re considering opening a Microsoft Connect item for a Microsoft product – whether it’s SQL Server, Microsoft Exchange, SharePoint, etc. – I encourage you to provide as much detailed information as you possibly can to assist Microsoft Product teams in addressing the issue. Effective communication is key to proper problem resolution.

Clone Your SQL Server Databases Using SMO and PowerShell

Back in 2009, I wrote an article about how to generate SQL scripts using Windows PowerShell and Server Management Objects (SMO) using the Script() method. One thing that you need to consider when using the Script() method is making sure that you have taken into account all of the database objects as well as their related objects. Take, for example, the Table object (or Table class according to SMO). A Table object consists of a collection of columns. Each column consists of several properties that can be considered database objects such as primary and foreign keys, constraints, etc. This means that for every column, you need to iterate thru all the possible constraints. Same thing with triggers and indexes. And we’re just looking at one database object here with multiple dependencies – the Table.

This is something that I needed to deal with when one of my customers wanted me to fix a process that included running a backup and restoring it on another server as well as truncating the tables.  They simply needed to clone their database structure for testing purposes. I highlighted the fact that the backup/restore/table truncate process is utilizing a lot of unnecessary resources – network, disk and CPU – when all they needed was the database structure. That’s where I recommended using database creation scripts. It’s lightweight and does not utilize unnecessary resources. While I’m a big fan of process automation, I emphasize the need for proper definition of the process as well as making the process as simple as I possibly can. This PowerShell script is just one of the steps included in that process.

$sourcePath="C:\temp" #Variable to contain location of SQL scripts

$s=new-object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST"

#Assign object to the database selected
$dbs=$s.Databases

#Script database creation - adding GO statement
$dbs["$database"].Script() +"`r`nGO`r`nUSE [$database]`r`nGO`r`n" | Out-File $sourcePath\$database.sql -append

#Generate script for all tables, excluding system tables
foreach ($tablesin$dbs["$database"].Tables | where {$_.IsSystemObject -eq$false})
{
$tables.Script() +"`r`nGO`r`n" | Out-File$sourcePath\$database.sql -append

#Generate script for all indexes in the specified table
foreach($indexin$tables.Indexes)
{
$index.Script() +"`r`nGO`r`n" | Out-File$sourcePath\$database.sql -append
}

#Generate script for all triggers in the specified table
foreach($triggerin$tables.Triggers)
{
$trigger.Script() +"`r`nGO`r`n" | Out-File$sourcePath\$database.sql -append
}

#Generate Default Constraints on columns that have them
#Iterate thru all the columns in the table
foreach($columnin$tables.Columns)
{
#Iterate thru all the default constraints in the columns
foreach($defaultin$column.DefaultConstraint)
{
#If there are default constraints, script out the ADD CONSTRAINT command
if ($default-ne$null)
{
$default.Script() +"`r`nGO`r`n" | Out-File$sourcePath\$database.sql -append
}
}
}

}

You can literally dig deeper on the different properties of the Table class to generate the corresponding script for the objects that you wish to create. Happy scripting!

24 Hours of PASS: Fall 2011 Edition

Today, I just got confirmation regarding the sessions available for the 24 Hours of PASS: Fall 2011 Edition (also called the Summit Preview edition). Indulge in free online SQL Server training on September 7 and 8, 2011. Sessions will be made available via Microsoft LiveMeeting. As this is an online event, act fast and register as soon as you can because seats are limited. Check out the 24 Hours of PASS website for more details. I will be delivering a session on disaster recovery on the 8th at 10 AM (Eastern). Watch out for more informaation on Twitter using the #24HOP hashtag