Data Compare crashes Visual Studio 2010

Although I'd previously noticed the Data Compare and Schema Compare options on the Data menu in Visual Studio, until today I didn't have a good reason to see what they can do.

Note I'm running VS Ultimate; if you can't see it, I'm not certain in which editions of the product this menu makes an appearance. SQL Server Management Studio 2008 doesn't seem to have a comparable tool that I'm aware of—which seems odd—but please comment if you know something I don't.

So here's the situation: two copies of the same database, one that's been moved to a different server and both of which have data changes. Your mission: find and fix the differences.

Open VS, Data –> Data Compare –> New Data Comparison…

CRASH!

Luckily I was able to work around this problem (feature?) by re-opening VS, Data –> Transact-SQL Editor –> New Query Connection, connecting to any ol' database (or optionally just cancelling out of the connection window) and then launching the compare wizard again.

I'm not sure what this does since you specify a database connection in the compare wizard anyway and cancelling the connection window or closing the new query window after opening the Transact-SQL Editor connection makes the problem go away. I've logged a bug with Microsoft connect.

Update 6 Sept 2010: MSFTConnect just got in touch to tell me this is likely related to the Solution Navigator extension in the Productivity Power Tools. Disabling apparently fixes the problem and the problem should be fixed this month.

If you found this post helpful, please support my advertisers:

Upgrade SQL Server Edition or Change License Key

Apparently upgrading from a developer version of SQL Server is possible. These screenshots are from an MSDN installation of SQL Server 2008 and entering a non-MSDN key will presumably legitimise the bits for production use (I don’t have a retail or volume key available so this is a guess at this stage).

To get here, re-run the installation wizard and select Edition Upgrade from the Maintenance screen:

Then enter your new product key or use a free edition instead:

You can also do this at the command line:

Setup.exe /q /ACTION=editionupgrade /INSTANCENAME=<MSSQLSERVER or instancename> /PID=<PID key for new edition>" /IACCEPTSQLSERVERLICENSETERMS

Selecting a delimited list of rows with other values in SQL

There are a number of really detailed posts out there describing an easy way to concatenate the values from a specific column into a delimited list without resorting to a cursor. Using coalesce and a temporary variable, the activity is child’s play:

Assuming you have a table named MyTable with a column named FirstName:

DECLARE @List varchar(MAX)

SELECT 
        @List = COALESCE(@List + ', ', '') + CAST (FirstName as varchar (200))
FROM
        MyTable

select @List

the above will return you something like “Ben, James, Ed”.

Nice and easy, right? But how do you use this in a non-trivial situation where you need to include additional columns in the select list? You can’t do this in the above statement (as far as I’m aware); in my case today, I wanted to a) avoid declaring the temporary variable and b) join the results to another table by Id.

for xml path ('') will help in the first case:

select cast(FirstName as varchar (200)) + ', ' from MyTable
for xml path ('')

but you’ll still get stuck trying to join the results.

Thankfully Arnie Rowland has a really concise SQL wiki post describing how accomplish this feat in SQL Server 2000 and 2005/2008.

Assuming this table:

CREATE TABLE TeamInfo
   (  MemberID    int IDENTITY,
      TeamID      int, 
      FirstName   varchar(50)
   )

Arnie’s query looks like this:

SELECT
   t1.TeamID,
   MemberList = substring((SELECT ', ' + FirstName
                           FROM TeamInfo t2
                           WHERE t1.TeamID = t2.TeamID
                           ORDER BY 
                              TeamID,
                              FirstName
                           FOR XML PATH('')
                          ), 3, 1000 )FROM TeamInfo t1
GROUP BY TeamID

And returns this result:

TeamID     MemberList
1	Bob, Jim, Mary
2	Ellen, Ralph, Sue
3	Bill, Linda

Note the substring function drops the leading delimiter and limits the result to just under 1000 characters. Also TeamInfo doesn’t have to be the same table, it can be any table with same IDs used in the t2 query.

Seeking an Experienced DBA with Strong T-SQL Skills

We’re looking for an experienced database administrator to start initially on a short term (three month) contract at Tourism WA’s Perth office. If you match this description, you do quality work, and you’re not a cocky git, please get in touch asap: info@mediawhole.com.

Required Personal Skills

• The ability to work independently with initiative and self-drive
• Excellent interpersonal skills
• Excellent verbal and written communication skills
• A sense of humour

Required Technical Skills

• Practical experience writing Transact SQL scripts and applying relational database concepts
• Practical experience installing, maintaining and migrating SQL Server 2000, 2005, and 2008 on 32 and 64-bit platforms
• Practical experience configuring and maintaining SQL Server 2008 clusters
• Knowledge of general performance testing and environment optimisation approaches
• Practical experience performance tuning existing code (stored procedures, user defined functions, views, other queries)
• Practical experience tuning indexes and configuring maintenance plans
• Practical experience implementing a database server health monitoring and status alerting system
• Practical experience with high availability techniques for SQL Server (mirroring, log shipping, etc.)
• Knowledge of deploying C# CLR assemblies within SQL Server

Desirable Technical Skills

• Experience maintaining product-specific database environments (specifically SharePoint/MOSS 2007)
• A working understanding of network concepts as they relate to database administration (firewalls, TCP/IP, performance)
• A working understanding of SAN and RAID technologies
• Experience tuning storage subsystems
• Experience installing and maintaining SQL Server Reporting Services (SSRS)
• Experience creating and maintaining SSRS reports
• Basic understanding of source control concepts

SharePoint and SQL Server with Greg Low at PSPUG

Sezai’s been doing a fantastic job of attracting some high-end speakers to the Perth SharePoint User Group and we had the immense pleasure of attending Greg Low‘s presentation on SQL Server optimisation for SharePoint admins this month.

Greg is a god among men in my mind and one of those can’t-miss speakers; apart from being a SQL Server MVP and Microsoft Regional Director, he really knows his stuff and delivers a nuanced presentation. I’d seen Greg in Adelaide on previous occasions and since he’d made his way all the way to Perth just for us, there’s no way I’d miss him.

There were some key take-aways from Greg’s talk I’m paraphrasing here for future reference… note I’m not a DBA and don’t necessary understand all of this—in other words, don’t apply this advice blindly without additional research!

• The autogrow settings should be configured appropriately on both content databases and the tempdb—the SQL Server defaults are inappropriate. In general, this means turning off autogrow and managing database file size manually. Alternatively, autogrow can be left enabled for contingency but should be reconfigured to grow using an appropriate value (in MB, by ~100MB).
• tempdb is rebuilt to the configured autogrow value every time the sqlserver process restarts; the start value should be large enough to avoid file system fragmentation.
• Never, ever auto shrink a SharePoint database as it will only have to regrow again and may increase fragmentation.
• Use DBCC CHECKDB but beware fixing problems may incur data loss.
• Whack your disk subsystem using benchmarking utilities like SQLIO and Iometer. Reasonable latency falls between 5-15ms, for example.
• Instant file initialisation allows SQL to write to files that have not been zeroed out by the OS, thereby avoiding the performance hit incurred by that activity. The MSSQLSERVER service account must be granted the SE_MANAGE_VOLUME_NAME right by virtue of being added to the Perform Volume Maintenance Tasks security policy.
• Multiple HBAs can lead to write ordering and disk subsystem issues; SQL Server 2005 introduced page checksums to help with this issue but the feature is turned off by default; it should be enabled.
• Virtualising database servers faced a lot of bad press in the past but those days are behind us. Hyper-V R2 and ESX are the way forward.
• Don’t even think about SQL Server 2000.
• The gap between SQL Standard and Enterprise is getting wider with SQL Server 2008.
• As most SQL Server instances are disk and memory-bound, consider enabling table/row/page compression in 2008 to reduce IO and memory usage at the expense of CPU. You mileage may vary as this will obviously depend on the content to be stored as photos and Office documents are already compressed. Backup compression is also possible.
• Configure databases with one file per CPU core.
• Different databases have different IO profiles; tempdb should be hosted on a dedicated spindle (see http://technet.microsoft.com/en-us/library/cc262731.aspx for more info about locating the various SharePoint databases).
• Ensure statistics are configured correctly.
• Index fill factor should typically be ~70% for a typical SharePoint environment.
• The default Windows Server 2003 sector size is too small; set it to 64k when formatting drives.
• Clustering SQL won’t boost performance like mirroring will. Clustering happens at the server level whereas mirroring must be configured for every database.
• If configuring a SQL alias, use TCP/IP.
• Create a separate SQL Server instance if an existing database server’s collation doesn’t match SharePoint’s specific requirements. Changing an incorrect collation is a lot of work.

Presenting at the next SharePoint User Group Presentation

Just a reminder I’ll be delivering part two of my two-part presentation on how we do web content management on MOSS 2007 this Tuesday at 12:30pm. We had a great turnout for the first presentation so it will be interesting to see who comes back!! Honestly, it’s amazing how many of you SharePoint/MOSS guys I’ve met through the first presentation and I’m really looking forward to bumping into a few more of you—please come up and say hello!

The blurb on the user group site is a straight copy of the part one blurb so here’s what I’ll be talking about tomorrow:

westernaustralia.com was one of the first public-facing MOSS 2007-based internet sites launched in Australia and is billed as the Western Australia Tourism Commission’s flagship web site. Two years on and thirty MCMS 2002-based tourism sites are now being migrated to the MOSS 2007 platform. In the second segment of this two part presentation, Michael Hanes, the Development Coordinator/Tech Lead at Tourism WA, talks about the backend MOSS environments. In this presentation Michael presents the existing and replacement hardware environments, virtualisation, environment structure, farm configuration, security, site collection structure and variations, performance, tooling, content delivery (Akamai), and content deployment.

Jeremy’s aiming to record the presentation again so, all being well, the part two webcast will be available after the event in case you’re unable to come along.

Here’s part one in case you missed it. See you down there for part two!!

[Update: a vodcast of the presentation is now available here with a PDF of the slides and notes here.]

[Update: the original PowerPoint deck is now available.]

SQL Server Results to CSV

I'm sure all you DBA types out there have an elegant, simple mechanism for saving results to a .csv file but I'm not a DBA and get frustrated trying to find the best way to do this every time I'm faced with a "reporting" task. I normally run my query as text, save the results as a .csv and then File->Open in Excel before trying to tell Excel how the data is delimited. This works well in some cases but it's long winded and the bugly .csv wizard thing always seems to split on commas even when I tell it not to (that's .csv for you, I guess!).

Well, no more! I found a gem of a little tool today for doing exactly what I need and it's simple and doesn't get in the way. The UI even works the way I'd write a UI for this kind of tool!

http://codecorner.tigernews.co.uk/codecorner/SQL2000selecttocsvutility.html

There's no installer so run the .exe and from there in it's just like a normal Visual Studio database connection picker thing: specify the server name, the database name, supply your credentials. Once that's done, paste in a query, and specify the output file and you're done! It even converts bit fields to true/false strings!

---

Custom-Built Microsoft Office SharePoint Server 2007 Branded Sites and Webpart Development - info@mediawole.com

Search 2 Launched on westernaustralia.com

It's been a busy week at Tourism! In addition to pushing out new web sites, my team has also been busy launching the next iteration of search on www.westernaustralia.com. Although wa.com is a MOSS-based web site, it uses a custom search system built in-house to meet an exacting set of requirements and keep half the planet happy. 

Search 2, as it's known internally, started out as a retrofit of the tours search sub system but quickly evolved into a full rewrite to make search faster, more accurate, and more configurable while introducing new features like spelling suggestions. 

Tourism WA has a paid membership model where tourism operators pay an annual fee to join the Tourism WA Network in return for a listing on westernaustralia.com. As a result, everyone wants to see their product listing on top of the pile when it comes to search results. We also have a lot of custom content on the web site: if you search for "Perth", the MOSS content page related to Perth is listed first; if you search for "hotel", you're probably after accommodation products but the display of the hotel listings in WA are randomised to ensure fairness. Trust me, these are some simple examples--what we end up with is a complex business logic that dictates how search results are weighted and ranked. 

The search implementation on wa.com has a long and sordid history. We started out using the in-built MOSS search and the BDC, crawling MOSS page content and tourism product stored in the Australian Tourism Data Warehouse. The BDC is a great tool for unifying disparate data sources but MOSS search simply didn't give us the configurability required to deliver on the business requirements. 

When panic set in, a Google search box was evaluated. While it did search, as you might expect, it only searched the Google way; it's still sitting in the server room, unused. As everyone fretted over the many problems we were facing, Rick, the developer working on search, went "underground" and built the RDC--or Rick's Data Catalog. The RDC would eventually replace the BDC and MOSS search in our little world. wa.com was launched with this solution, which is actually build on SQL Server Full Text indexing.

The RDC got us across the line after a never ending round of user acceptance testing but fell down in the tours category. It also abused some of the more horrific technologies from some of our older sites like XSLT and inline C#. It was also just a messy proof of concept at the code level and had some major design flaws. An example of the latter was a crawler that couldn't be scheduled and cleared the main index table before initiating the crawl; the problem with this approach was no search results would be returned while the crawler was run--apart from the manual administrator intervention required to actually kick it off and verify the results.

Search 2 is one of those engineering marvels you come across so rarely in the software world. The underlying design is fairly straightforward for what it does and it uses the chosen technology set appropriately--judiciously and as required. In other words, it's not over-engineered and gets on with the job in a logical, unobtrusive way. The grunty data work is relegated to the database where it belongs and the main UI is composed of configurable web parts. 

That's not to say Search 2 didn't take a while to build. Nico, one of most experienced team members when it comes to MOSS and search, has been hard at work on this project since April. UAT progressed over a month and deployment took three days! 

We're already building on top of the Search 2 fundamentals Nico has in place. An online booking engine has passed the proof of concept stages and a new itinerary planner should be out in the new year. Search 2 will also form the basis for a search re-release on the partner sites, which adds another few dimensions to the mix. Working with the Search 2 code base to integrate these components has almost been pleasurable. Either way, who can complain when all of the project stakeholders are happy?!  

Nico's off for a well-deserved Christmas break and I'm quietly confident we won't have any major bedding in problems during the four weeks he's away (unless the database server dies but that's another story). Meanwhile, Search 2 should be humming happily along--have a look for yourself!

---

Custom-Built Microsoft Office SharePoint Server 2007 Branded Sites and Webpart Development - info@mediawole.com

Great SQL Server Database Performance Article

Zach Nichter has written a very approachable article for TechNet Magazine on the subject of SQL Server performance measurement. It's brief and very much to the point, covering performance counters, profiling, and dynamic management views. It's by no means MOSS-specific but nonetheless applicable and the author obviously has a wealth of experience on this topic.

"Optimizing SQL Server CPU Performance"

http://technet.microsoft.com/en-us/magazine/cc137784.aspx

---

Custom-Built Microsoft Office SharePoint Server 2007 Branded Sites and Webpart Development - info@mediawole.com

Free eBook: Introducing Microsoft SQL Server 2008 (MS Press)

Microsoft Press is offering the Introducing Micorosoft SQL Server 2008 ebook (by Peter DeBetta, Greg Low, and Mark Whitehorn) as a free download.