July 14, 2010 at 3:53 pm
I will post the question and answer, exactly as it is in the book. The answer is B. I would like to know if you all agree. I personally, believed that answer B could not be right because Database Mirroring cannot be configured against database with FILESTREAM and a product catalog would surely require FILESTREAM. What do you think?
"Changes to the product catalog occur only once per day during a scheduled maintenance window. Because the CTO wants to ensure that the product catalog is redundant at a secondary site that is being considered, which technology can you deploy as an availability solution?"
A. Database Mirroring in the High Performance operating mode
B. Database Mirroring in the High Availability operating mode
C. Replication
D. Log Shipping
July 14, 2010 at 4:13 pm
hxkresl (7/14/2010)
a product catalog would surely require FILESTREAM.
Why do you say that?
Is it not possible to have a product catalog without filestream?
Don't make assumptions in an exam. If the question states a fact, then that fact is true. If it doesn't state that fact then, unless it logically and obviously follows from other facts, it's not true.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 14, 2010 at 6:20 pm
OK. I think this is the right advice for me. It is probably even the strategy. You may even be behind these questions :-).
I did make this assumption because whenever the training kit wishes to illustrate a FILESTREAM column it uses product catalogues. It was a knee jerk response that I will have to fight!!
July 15, 2010 at 8:31 pm
Hold on, please. I have another question about this.
With a product catalog under 2MB one could theoretically add it directly to the column, is that right? I have never built a relational db where I added anything but data or paths to images on a fileserver. Never a whole document directly into the column. Is this really done? If it is not in xml format is formatting preserved? Is this a good practice?
Finally, do you think there is anything about the question that points to Database Mirroring in High Performance mode over Database Mirroring in High Availability Mode? I don't see it.
July 16, 2010 at 2:44 am
hxkresl (7/15/2010)
With a product catalog under 2MB one could theoretically add it directly to the column, is that right?
The size limit for varbinary(max) is 2 GB, not 2 MB (and that's per row)
I have never built a relational db where I added anything but data or paths to images on a fileserver. Never a whole document directly into the column. Is this really done? If it is not in xml format is formatting preserved? Is this a good practice?
It can and is. Sharepoint is an example. Good practice? Well, it depends. 😀 There's a whole lot more to consider before making that decision.
Because the question doesn't say anything other than 'product catalog', we've got no way of knowing it that catalog is stored in the filesystem with path and filename in the DB, if the entire files are stored in the DB in a varbinary column or if the files are stored in a varbinary filestream column.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2010 at 10:47 am
OK, if the file is under 2GB, can you give me a reason or two for not storing it in the database? Performance?
July 16, 2010 at 10:56 am
Really it depends on application, what the file is, how it's going to be used, how often it'll be updated, size of files, size of backups, etc. Not a clearcut yes/no answer.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2010 at 11:05 am
OK, that's fine. So, back to the original exam question.
Since any changes to the catalog occur during a maintenance window what information in the quesiton would favor B over A?
A. Database Mirroring in the High Performance operating mode
B. Database Mirroring in the High Availability operating mode
I understand high availability is synchronised and high performance is not but there is nothing in the question that raises the bar for high availability being the preferred mode, in my opinion.
July 16, 2010 at 11:57 am
I think it's a bad exam question 🙂
Good avator by the way. I feel like I am talking to Liam, all the time, Gail. For the record, that means, you rock.
July 16, 2010 at 12:45 pm
I am very sorry to bother you again. Suddenly, another question about FILESTREAM
Are the contents of the product catalogue, actually imported into the varbinary (max) column even when it has the FILESTREAM property enabled? ie. is the storage of the data on the filesystem is handled by SQL Server?
July 16, 2010 at 12:59 pm
http://msdn.microsoft.com/en-us/library/bb933993%28SQL.100%29.aspx
http://msdn.microsoft.com/en-us/library/cc949109%28SQL.100%29.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2010 at 1:51 pm
My final understanding on this topic, is that, in the case of a column with FILESTREAM property the file is stored on the filesystem by human intervention. A properly formatted INSERT statement makes the correlation between a column in the database and a path_name in the filesystem.
An insert would look like this:
USE AdventureWorks2008R2;
GO
CREATE TABLE myTable(FileName nvarchar(60),
FileType nvarchar(60), Document varbinary(max));
GO
INSERT INTO myTable(FileName, FileType, Document)
SELECT 'Text1.txt' AS FileName,
'.txt' AS FileType,
[highlight=#ffff11] * FROM OPENROWSET(BULK N'C:\Text1.txt', SINGLE_BLOB) AS Document;[/highlight]
GO
On the application end therefore, I imagine a form that contains an browse field with a script that uploads the file from the user's harddrive to the server that contains the FILESTREAM filegroup.
July 16, 2010 at 2:43 pm
hxkresl (7/16/2010)
in the case of a column with FILESTREAM property the file is stored on the filesystem by human intervention.
No. How you insert a document into a varbinary filestream column is no different from how you insert a document into a normal varbinary column. If the columns is varbinary filestream, SQL creates a file out in the filesystem, in the directory defined for filestream usage. If it's a regular varbinary, SQL allocates space within the data file.
There's no human intervention regarding the storage of the file.
USE AdventureWorks2008R2;
GO
CREATE TABLE myTable(FileName nvarchar(60),
FileType nvarchar(60), Document varbinary(max));
GO
There's no filestream column there. That's a regular varbinary and anything inserted into that will be stored within the data file.
A filestream looks like this
USE AdventureWorks2008R2;
GO
CREATE TABLE myTable(
FileName nvarchar(60),
FileType nvarchar(60),
Document varbinary(max) FILESTREAM
);
GO
Now something stored in that column will get put into the filesystem, in whatever directory was selected when the filestream filegroup was created.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply