SQL Server 2008 for Developers - Some additional questions

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 :)

Add a new comment:

Name:

Website:

Comment:

jb's Blog

history

blogs i read

links