All Things SQL Server

Thanks to those that attended my talk this afternoon on SQL Server for the Web Developer at the SQL Live! conference in Orlando.  Below are a few links from the talk and I've attached a zip w/ the demo scripts.

• SQL Database Publishing Wizard: http://www.codeplex.com/sqlhost
• Astoria: http://astoria.mslivelabs.com/OnlineService.aspx 

demo scripts in attachment...

Hey folks.  The Manageability team in SQL Server has just released a community technology preview of the SQL Server 2005 Best Practices Analyzer.  This is a major reworking of the original Best Practices Analyzer for SQL 2000 and provides a host of new features, including support for other SQL services such as Analysis Services.

 You can find it here: http://www.microsoft.com/downloads/details.aspx?FamilyId=DA0531E4-E94C-4991-82FA-F0E3FBD05E63&displaylang=en

 As Paul Mestemaker (the project's Program Manager) said in his internal announcement mail for the CTP: "Be Pro Active. Run BPA" :)

New in this release:

-          Integration into Visual Studio 2005 and Visual Web Developer Express 2005.  You can now right-click on a database in the VS Server Explorer and select “Publish to provider”!

-          Full support for SQL 2000 as a source database

-          Coverage of all object types

-          Support for integrating sqlpubwiz.exe into hoster automation.  This change was based on feedback from hosters on how they want to provide database download services.

Looking forward, we plan to officially RTW in 8 languages in mid January.

Also, last night Scott Guthrie posted a great tutorial on using the Database Publishing Wizard to deploy a database to a hoster that hasn't yet adopted the Database Publishing Services.

Dan

Today, we’re excited to announce the release of the 2nd CTP of the SQL Server Hosting Toolkit!

This CTP contains both a dramatically enhanced Database Publishing Wizard as well as the new Database Publishing Services.

Major enhancements to the Database Publishing Wizard include support for almost all database-level objects and data types, a wizard-based GUI, and integration with the Database Publishing Services. 

The Database Publishing Services enable hosters to provide a seamless deployment story for their customers who’ve downloaded the Database Publishing Wizard.  The services are designed to be easy to deploy and require minimal resources.  With this CTP, hosters can do test deployments in preparation for our formal release next month.

If you're a hosting customer, download the Database Publishing Wizard, give it a shot and tell us about your experience. 

If you're a hoster, download the whole toolkit and shoot us a mail.  We'd love to talk to you about your experience hosting SQL Server and how we can shape the toolkit to suit your needs.

Dan

So you got a hosting account and you're ready to deploy your site/app to the web!  (If you haven't yet, there are lots of great hosters out there offering Windows/ASP.NET/SQL hosting solutions)

Pushing the ASPX pages has always been pretty straight-forward, but moving the database (along with its data) from a dev box up to a hoster has been tricky (read: very hard) until recently.  With the release of the first CTP of the Database Publishing Wizard, it's now an easy task.

Ibra Bordsen (a developer on the SQL Hosting Team) recently posted a great tutorial on how to use the Database Publishing Wizard to deploy a DB to a hoster.  Check it out and let us know if you find it helpful.

I've recently come across a number of folks in different contexts who were trying to figure out how to acheive the equivalent of MySQL's "LIMIT" clause in SQL Server.  The basic scenario is that you want to return a subset of the results in a query from row number X to row number Y.

The good news is that SQL Server 2005 makes this really easy.  We introduced a new set of ranking functions into the T-SQL language that let you accomplish the basic LIMIT semantics and much more if you feel like getting fancy.

Quick example:

SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY name) as row FROM sys.databases ) a WHERE row > 5 and row <= 10

The query above returns rows 6 through 10 from sys.databases as ordered by the "name" column.  ROW_NUMBER() is the key function we're using here.  It's one of a set of ranking functions introduced in 2005.  Note that it's always accompanied by an OVER clause that specifies the ordering that the row_number should be based on. 

For details on the ROW_NUMBER() function and its use, see: http://msdn2.microsoft.com/en-us/library/ms186734.aspx

For info on other ranking functions in SQL Server 2005, see: http://msdn2.microsoft.com/en-us/library/ms189798.aspx

The SQL Server group at Microsoft has just launched the SQL Server Hosting Toolkit with the release of the Database Publishing Wizard Community Technology Preview 1.

The objective of the SQL Server Hosting Toolkit is to enable a great experience around hosted SQL Server.  The Database Publishing Wizard works toward this mission by making it easy to upload a database from a development box up to a shared hoster.  In its first incarnation, the Database Publishing Wizard is a command line tool that generates a T-SQL script designed to be executed in the script execution windows provided by most hosters in their database management consoles.  In the coming months we'll be adding a GUI and building seamless integration between the tool and an upload service we'll provide to hosters for deployment.

Details on the Database Publishing Wizard as well as the download can be found at http://www.codeplex.com/Wiki/View.aspx?ProjectName=sqlhost&title=Database%20Publishing%20Wizard.

We plan to be releasing advances in the Toolkit frequently so check out this first CTP and give us your feedback!

The folks at Teratrax posted a good set of instructions for troubleshooting any issues you might be having connecting to an instance of SQL Server 2005 Express:

http://www.teratrax.com/articles/connecting_sql_server_express.html

I've attached the scripts used in this Teched session...

Hi folks.  I'm giving a TechEd talk here in Boston titled "DAT421: Peeking Inside SQL Server 2005 Engine" tomorrow morning at 8:30 am in room 102AB.  We'll be looking at how you can better understand what's happening and what's happened on your SQL Server by using the Dynamic Management Views, the Default Trace and other technologies in SQL Server 2005.

If you're here in Boston for TechEd, are planning on attending, and have questions you think are pertinent to the topic, send me a mail or post a comment with your question(s).  Depending on the question, I may be able to incorporate it into the session for you...

We're launching the first CTP of SQL Server Everywhere today!

SQL Server Everywhere is a new, lightweght database you can load within the process of your application.  We first announced this new SKU a few months ago, and it's an exciting addition to the SQL Server platform, providing a great solution for apps that use and keep data, but don't need a "server" per se.

If you're a data application developer, this is a release you should familiarize yourself with.

Download:  http://www.microsoft.com/downloads/details.aspx?FamilyID=85e0c3ce-3fa1-453a-8ce9-af6ca20946c3&displaylang=en

Details: SQL Server Everywhere CTP Books Online

Steve Lasker's post is also worth a look: http://blogs.msdn.com/stevelasker/archive/2006/06/12/SqlEvCTP.aspx

Thought I'd post a useful link for finding hosters that provide SQL Server 2005 services:

http://www.asp.net/hosters/Default.aspx?PromotionID=23

Welcome to my new blog focusing on SQL Server!  This blog should serve as a source of news and information about SQL Server from the perspective of an insider in the product team.

A bit about me:  I'm a Senior Program Manager in the SQL Server Strategy, Infrastructure and Architecture team.  I've been in SQL Server for five years now, coming up initially through the Engine Programmability team, and then the broader Engine team.  I've helped design and drive delivery of SQL/CLR, Dynamic Management Views, Visual Studio Integration, and T-SQL features such as exception handling and DDL Triggers.