jb's Blog

SQL Server 2008 for Developers - Some additional questions 27 Mar 2008 11:12

During the Wellington leg of the Launch Wave, James Hippolite, a regular in the local community gave a nice long list of questions as a follow up to the session.

Rather than answering them then and there James asked if I could blog the responses. I also recieved a number of other questions along similar lines so I thought I would collate all the answers here so everyone could benefit.

GENERAL

1. Does CTP 6 contain the Business Intelligence Development Studio (BIDS)

Yes - CTP6 contains an updated version of BIDS which integrates with Visual Studio 2008 with the same templates available.

SQL SERVER FILESTREAM

1. Can I have more than 1 FILESTREAM group defined in the CREATE DATABASE statement?

Yes - you can specify multiple FILEGROUPS containing FILESTREAM data. The first group you create will be set as the initial default if you dont specify otherwise, and you can then use the FILESTREAM ON clause for CREATE TABLE to indicate which FILEGROUP you want your FILESTREAM data for that table to use.

2. Can we have sub-folders in the FILESTREAM data folder?

No - the internal structure of the FILESTREAM directories is maintained by SQL Server.

3. Why did you create an ID column and a GUID column in the Photo table?

FILESTREAM requires a ROWGUIDCOL column to be declared which it uses to uniquely identify the row. You can see how this is used if you select .PathName() on your FILESTREAM column, the end GUID will be the value of the ROWGUIDCOL column for that row.

The ID column was my normal identity column.

4. Can you use FILESTREAM with replication?

Yes. Databases containing FILESTREAM data can be replicated and log shipped but NOT mirrored (See Books Online to understand the restrictions on the FILESTREAM feature). There is a size limit on Web based replication, but standard replication treats the data as a varbinary(max) and will work even if the target database is not using FILESTREAM.

SPARSE COLUMNS

1. Must SPARSE columns be defined at the end of the column set for the table?

No. You can declare any column as a SPARSE column as long as it is nullable (or if it is of the GEOGRAPHY, GEOMETRY, IMAGE or NTEXT data types).

One other capability around sparse columns which I didnt mention during the talk, was the ability to define a "column set" column for the table. This is a feature which allows you to change the behavior of "select * .." when operating on the table with SPARSE columns. In essense, a column set declares an xml column which will aggregate togethor all of the SPARSE columns in an XML document structure. This can make it a bit easier to work with this type of data since by definition you will be storing "optional" data using SPARSE columns.

BobB has some additional info on column sets in one of his recent blog posts.

TRANSPARENT DATA ENCRYPTION

1. When you enabled encryption for the database, the cursor returned immediately. Did it really go to disk and change all the data on disk?

No - it spawned a background thread which performs the initial encryption work. While this is running maintainence operations on the database are not able to be run, so if you are encrypting a large existing database you will want to schedule this in appropriately. Books Online has more details about this process and what the restrictions are.

2. Does FILESTREAM data get encrypted as well?

No - FILESTREAM data is not supported by Transparent Data Encryption, however you can encrypt the data using EFS. This may seem logical or absurd depending on your POV :)

MISC

1. How were you inserting the Photo data into the database?

I was using a little console application called "PhotoLoader" which I wrote specifically for loading in the test data. It has been provided in the samples I posted earlier. It is a very simple program that loads in the file data from disk and then passes it to the ImportPhoto stored procedure which we created as part of the session.

If you have other questions just email them through :)

comments

SQL Server 2008 for Developers 27 Mar 2008 10:43

After 4 great events in Christchurch, Wellington and Auckland we are finally done and dusted with Launch Wave 08! Thanks to everyone who joined us for the Community Summer Road Trip and for the Launch Wave 08 events over the last couple of months.

As promised, here are the resources from my session "SQL Server 2008 for Developers". It was great to get such positive feedback on the session so I hope the additional resources help furthor in getting you started.

Download: Slides in .pptx format (1.66mb)
Download: Slides in .ppt format (3.90mb)
Download: Code and Demos (915kb)

Remember, the best thing to do next is to download the latest CTP drop for SQL Server from here and get it installed into a test environment!

To run the code and demos, you will need to have SQL Server 2008 CTP 6, Visual Studio 2008, and the Entity Framework Beta 3 installed. I have included the test data and setup scripts I used during the session as well which will allow you to work with the same test data, post a comment or drop me an email if you have any queries or issues.

Some other links you might find useful are:

ADO.NET Team Blog - useful for Entity Framework information and discussion
Bob Beauchemins Blog
Euan Gardens Blog
Simon Sabins Blog

Happy coding!

comments

LightSpeed 2 EAP Program kicks off 19 Mar 2008 11:16

JD mentioned yesterday that we are kicking off our Early Adopter Program (EAP) for LightSpeed 2.0. One of the key things we are looking for feedback on is our new LINQ provider for LightSpeed.

During the Road Trip and at the Launch Events there has been plenty of excitement around the country about developing with .NET 3.5 and LINQ. We couldnt agree more, and so we have been keenly working away building a new LINQ provider to allow LINQ querying over your LightSpeed entities. We believe LightSpeed offers a great choice for people who are looking for a more structured approach to their development and who simply want to get the job done fast ! Even faster now that you can also leverage the advantages brought by .NET 3.5 and LINQ :) Check out our thoughts as to why LightSpeed might be the ideal choice for you.

If you are interested in checking out "the beta bits", drop JD an email and let him know that you want to be part of the program. We look forward to hearing your thoughts! :)

comments

SQL User Group - Wellington - 13th March 2008 12 Mar 2008 09:30

Tommorow we have Peter Ward joining us from Queensland to give 2 presentations at an extended user group session tommorow (details below). As usual there will be pizzas and beer provided - hope to see you there!

---

Wellington SQL Server Meeting Notice

Our next meeting will be:-

13 March 2008 6pm

Intergen
L7 Plunket House
126 Lambton Quay
(Lifts lock at 17:30. If you're late and there's nobody around to swipe you up, contact Adrian on 027 435 4050)

Peter Ward is visiting us from Australia and will make 2 presentations, with refreshments in between.

Peter Ward is WARDY IT Solutions Chief Technical Architect. Peter is an active member in the Australian SQL Server community and President of the Queensland SQL Server User Group. Peter is a highly regarded speaker at SQL Server events throughout Australia and is a sought after SQL Server consultant and trainer, providing solutions for some of the largest SQL Server sites in Australia. Peter is a regular author for several SQL Server websites and has published numerous articles in the monthly SQL Server newsletter that he produces along with the highly acclaimed WARDY IT Solutions SQL Server Blog. In 2007 Peter was selected as a speaker for Tech-Ed Australia, SQL Server Open World in Denmark and as a Spotlight speaker for the 2007 SQL Pass Community Summit, the largest SQL Server event in the world. Peter has also recently been awarded as a Microsoft Most Valuable Professional for his technical excellence and commitment to the SQL Server Community.

The topics of the presentations are:-

"SQL Server 2008 - What's new, and the business value it brings"

If only they taught SQL Server in Kindergarten…
Bon Jovi had a hit song in the 90's with a chorus that I believe went something along the lines of 'The server is running slow, and your to blame, You give SQL Server a bad name'. If you look at most Information Technology courses today this parody often rings true. There are all sorts of exciting (and 'sexier') subjects such as Object Oriented Programming and Programming Abstraction but somehow the subject 'SQL Server 101' has been overlooked. As a result there are set of common mistakes that are made time and time again by developers that cause an application to negatively affect the performance of SQL Server. Peter Ward from WARDY IT Solutions (www.wardyit.com) will walk through some of the common Gotcha's when developing an application that accesses SQL Server and how to identify possible performance issues prior to deployment.

"Index, What Index? SQL Server Index Tuning"

Unfortunately SQL Server does not ship with the 'Make SQL Server Go Faster' configuration option. Despite what appears to be an obvious lack of functionality, SQL Server does ship with a feature that is often misunderstood called an index. When an index is used correctly it can be the answer to the missing 'Go Faster' configuration option. New hardware and configuration changes can improve the performance of SQL Server by small percentages; however a simple CREATE INDEX statement can improve performance by large orders of magnitude. In this session, Peter Ward from WARDY IT Solutions (www.wardyit.com) will explain the basics of indexing in SQL Server and the strategies that you can employ to improve query tuning by understating how to identify and create effective indexes.

comments

MIX 08 Keynote 06 Mar 2008 09:27

... jhas been and gone now and we got everything we expected we would and some more! :)

For a good summary of what happened, check out Tim Sneaths live blog on the event.

If you are interested in downloading bits and spending the morning having a play, here are some links:

IE 8 Beta 1: http://www.microsoft.com/windows/products/winfamily/ie/ie8/default.mspx
Silverlight 2 Beta 1: http://silverlight.net/GetStarted/#Beta
Expression Blend 2.5 Preview: http://www.microsoft.com/expression/products/download.aspx?key=blend2dot5
ASP.NET MVC Preview 2: http://www.asp.net/downloads/3.5-extensions/

comments

Road Trip: Slides and Code 03 Mar 2008 14:48

During the Road Trip, we mentioned that we we would be making all of the code and slides available for later use.

Its taken us about a week to get our of "Road Trip" mode and back into a regular routine, so without any furthor ado here they are:

Code: RoadTripCode.zip (3.90MB)
Slides: SummerRoadTrip.pptx (1.35MB)

The Code package contains a quick README on getting things set up, feel free to comment if you have more detail you want to add to this, however be warned you will want to put this in a PRE-PRODUCTION / TEST environment as it makes use of many beta bits such as SQL Server 2008 and the Entity Framework.

If you are looking to build such a VM up from scratch then these instructions may help you out in terms of what you need to install..

1. Windows Server 2008 Enterprise with No Hyper-V operating system installation
    - Roles: Web Server, File Services, Application Server: Enable TCP and Named Pipe Activation and Distributed Transactions
    - Features: Windows Powershell, Telnet Client

2. Visual Studio 2008

3. SQL Server 2008 CTP 6
    - Install only Database Server, Books Online and Client Tools
    - Execute sp_filestream_configure @enable_level = 2 after server has installed
    - Create C:\FSData directory locally for storing filestream data for the Road Trip database

4. Entity Framework Beta 3
    - Also install Visual Studio hotfix for Entity Framework Beta 3
    - Also install Entity Framework Tools for Visual Studio

From the feedback on the eval forms, it also sounds like quite a few of you would like some videos made available so you can share them around. Darryl and I have chopped up the video content we took during the session in Invercargill, so we will have a look at getting this online sometime soon :)

comments

Whats coming over the horizon at MIX? 03 Mar 2008 10:48

Only a few days before MIX 08 and it looks like ScottGu is well practiced on his keynote demo :) From the surrounding articles and other noise coming out lately I am personally expecting to see the following things talked about at MIX (although who knows if there are any other surprises waiting?):

Silverlight 2 (including Beta 1 bits)
Internet Explorer 8 (including Beta 1 bits)
Visual Studio 2008 designer support for Silverlight 2 (Beta 1 bits?)
Expression Blend 2.5 March CTP

Once again, Silverlight is very much at the fore. While its taking quite a bit of time to take hold in the market, particularly given its largely "beta" status during v1.0, I think its without question that everyone is very interested in developing richer applications on the web (RIAs) and how they can be delivered while still accepting accesibility and interoperability as key constraints so take note.

Also interesting in terms of timing, is last weeks launch of Adobe AIR. With Flash now reaching out to the desktop market, I think we will start to see a bigger push from Microsoft in getting developers to move towards WPF on the Desktop and Silverlight for the Web. Battle on, and I expect plenty of salvos to be fired from MIX.

On a side note - what do people think about the version numbering being shown there for Expression Blend?!? We dont even have version 2.0 released yet and they are already onto pushing out previews for a version 2 releases ahead.. Hmm.. So why would I use version 2.0 again? Curious.. :)

So looks like it will be an interesting week ahead - cant wait to get playing with the new Silverlight bits! :)

comments (1)

jb's Blog

history

blogs i read

links