In SQLCMD mode, should CONNECT be an implicit batch separator?

Hi Folks,

I've been working with SQLCMD mode again today and one thing about it always bites me. If I execute a script like:

:CONNECT SERVER1

SELECT @@VERSION;

:CONNECT SERVER2

SELECT @@VERSION;

:CONNECT SERVER3

SELECT @@VERSION;

I'm sure I'm not the only person that would be surprised to see all three SELECT commands executed against SERVER3 and none executed against SERVER1 or SERVER2. If you think that's odd behavior, here's where to vote: https://connect.microsoft.com/SQLServer/feedback/details/611144/sqlcmd-connect-to-a-different-server-should-be-an-implicit-batch-separator#details

 

Should SQL Server tools target wide screen formats instead of portrait formats?

There was a short discussion on the SQL Down Under mailing list this morning about screen resolutions for working with the SQL Server tools. In particular, the issue was about how unusable the tools are on the 1366×768 resolution notebooks that now seem to be the most common. While finding a notebook with an appropriate resolution is obviously the answer at this time, I started thinking that the product itself needs to address this.

SQL Server tools currently target a portrait 4:3 shape for minimum window sizes. Increasingly, portrait 4:3 format monitors are disappearing from sale.

While I loved the standard portrait orientation and prefer to work in it, the world has decided that watching DVDs on computer screens is more important than real work. So sadly, I think the SQL Server tools should now target wide screen formats by default. If you think, as I do, that the SQL Server tools should now be designed to be at least workable on 1366×768, vote here: https://connect.microsoft.com/SQLServer/feedback/details/636373/sql-server-tooling-should-target-wide-screen-form-factors#details

Log Shipping Between SQL Server Versions (perhaps 2005 to 2008)

One of the discussion lists that I participate in, had a brief discussion this morning about whether or not it's possible to perform log shipping between differernt versions of SQL Server. Specifically, can you do log shipping between SQL Server 2005 and SQL Server 2008?

SQL Server does support restoring earlier version databases on later versions of the product. The databases get upgraded along the way when you perform restores of databases. SQL Server also allows you to restore transactions logs from earlier versions of the product but (as Robert Davis points out in the comments below), the upgrade doesn't happen until recovery of the database occurs. And that's why you can't use STANDBY mode in this situation.

So, you can set up log shipping between versions, however things aren't that simple. Log shipping is often used to provide a warm standby. If you use it in this way and you need to fail over to the standby server, you now have now way to swap the log shipping roles, as you can't then log ship back from the 2008 server to the 2005 server.

If you are performing a one way log ship, intentionally, this might be quite acceptable to you. I often see log shipping used when servers are being upgraded from one version of SQL Server to another version, even side-by-side. When it's time for the swap to the new server to happen, the final logs just need to be moved and this takes very little time. There are other reasons as well as to why you might be happy to just have a one-way log shipping operation.

The main point is that you need to consider why you are performing log shipping before you do this. If it's with a view to swap roles from primary to secondary and back, then log shipping between versions isn't for you.

 

New Upgrade Technical Reference for SQL Server 2008 R2

Hi Folks,

A year or two back, I was involved in a project with my colleagues (led by Ron Talmage) to construct an Upgrade Technical Reference for SQL Server 2008. It seemed to be well received.

We've updated it now to SQL Server 2008 R2 and it's just been published. You'll find it on this web site: http://www.microsoft.com/sqlserver/en/us/product-info/why-upgrade.aspx You'll need to click on the Upgrade Guide link towards the middle of the RHS under the "Why Upgrade" whitepaper.

Enjoy!

Regards,

Greg

Help support Itzik's plea for T-SQL Windowing functions

One of my colleagues, Itzik Ben-Gan is known to most in the SQL Community, particularly for his passion around the T-SQL language.

He's recently written an amazing blog post that describes why these functions are needed, along with a plea for helping promote the ideas to the Microsoft product group via the Connect site.

Anyone with an interest in T-SQL should read the post as, by itself, it provides great insights into the language and the possibilities that some subtle changes could bring.

You'll find it here: http://www.sqlmag.com/blogs/puzzled-by-t-sql/tabid/1023/entryid/13085/Window-Functions-OVER-Clause-Help-Make-a-Difference.aspx

Highly recommended reading!

Worldwide Online Techday – More Latin American goodness

I also got an email from one of our Brazilian colleagues today. Diego Norgare was telling me about the upcoming Worldwide Online TechDay that will be running on October 30th. Sessions are running in both Portuguese and also in English. You'll find more about it here:

I was particularly intrigued by the session on liposuction for your database.

24 Hours of PASS for Latin America!

One of the biggest pushes I was trying to make while on the PASS board was to help grow the organization into other areas of the world, and probably a little out of the organization's comfort zone. We in western English-speaking countries often totally underestimate the passion for SQL Server and the number of professionals that work with it around the world. In my time on the board, I was both amazed and privileged to see the types of events already occurring and the vast number of attendees. Often these numbers simply dwarfed the typical numbers of attendees in more familiar locations.

Latin America is a great example of this. Our friends from the regional mentor team at PASS in Latin America have organized a local edition of 24 hours of PASS. This will bring you oodles of free SQL Server goodness in both Spanish and Portuguese. It's great to see PASS continuing to spread its wings. Please help support it.

For more information and registration: http://sqlpass-latam.org/Inicio.aspx

SQL Server 2008 R2 Idea: Data Profiling of any table in SSMS

I love the data profiling task that was added to SSIS in SQL Server 2008. It allows you to get a profile of the data in a table – this includes things like what are potential candidate keys, what length are the data values in each column, what percentage of rows in a table are null in each column, what is the distribution of values in the data, etc. etc.

If you haven't used it, I'd suggest taking a look at it. It's a real hidden gem in the product.

However, I think they missed the #1 place that this sort of functionality would be useful. Imagine if you could just right-click a table in Object Browser in SQL Server Management Studio and say "Profile this table…". That would be so insanely cool.

If you think so too, you know you want to vote for this at: https://connect.microsoft.com/SQLServer/feedback/details/610847/data-profiling-needs-to-be-also-in-ssms-object-explorer

Project Phoenix – underway and active

Every so often, Microsoft sends MVPs a set of MSDN subscriptions that we can give to someone that would benefit from them. I have found it to be very interesting in past years and have to say that the most pleased I ever felt giving these away has been to students who are struggling to get into the workforce for some reason (one with a notable disability comes immediately to mind) or to developers that are just down on their luck for some reason.

So I was really excited when I saw that one of our buddies Arnie Rowland had decided to formalize a program that give the subscriptions to people that can really benefit from them. In Arnie's case he started an effort to give MSDN Subscriptions, training, books, and tools to unemployed developers that would take on a project for a non-profit, school, or church. To date, 27 MVPs have joined the effort and contributed one or more of their MSDN subscription cards. Ten proposals have already been selected to receive awards. And the project continues until the end of the year. Full details here: http://sqlblog.com/blogs/arnie_rowland/archive/2010/07/30/like-a-phoenix-rising-from-the-ashes.aspx 

The project is not just US-based either. Another MVP buddie Rod Colledge is helping to make this happen in the ANZ region. It's likely that several MVPs in our region will be involved. Details of the local project are here:

http://www.rodcolledge.com/rod_colledge/2010/09/project-.html 

Please help to get the publicity happening and to get the word out to more unemployed developers, even invite eligible non-profits, schools, etc. to submit a project that could be matched to an eligible developer.

SQL Server 2008 R2: MDS setup HTTP Error 500.19 – Internal Server Error – Error Code 0x80070021

I've (fortunately) ended up with a new notebook recently and had to reinstall everything. One problem I ran into was with Master Data Services. The config program seemed happy but it shouldn't have been. What threw me was that it seemed to have a dependency check for IIS 7, etc. while configuring MDS. That led me to think I'd already configured IIS appropriately. I hadn't and when I tried to use the site, I got the above error. IIS needed to have the following:

Common HTTP Features
· Static Content
· Default Document
· Directory Browsing
· HTTP Errors

Application Development
· ASP.Net
· .NET Extensibility
· ISAPI Extensions
· ISAPI Filters

Health and Diagnostics
· HTTP Logging
· Request Monitor

Security
· Windows Authentication
· Request Filtering

Performance
· Static Content Compression

Management Tools
· IIS Management Console

Plus NET Framework 3.0 Features
· WCF Activation
· HTTP Activation
· Non-HTTP Activation

Plus Windows PowerShell

Plus Windows Process Activation Service
· Process Model
· .NET Environment
· Configuration APIs
 

I'm sure that'll be covered in a "readme" somewhere (that sadly we often neglect to read each time we install) but I was lulled into a false sense of security by the dependency check in the installer. It would be great to see it upgraded to fully test for dependencies.