You are hereFeed aggregator / Categories / SQL Server MS Blogs
SQL Server MS Blogs
The SQL Server Health Check
Bucky Woody - Thu, 02/11/2010 - 08:16
My friend Brent Ozar, who is a top-notch SQL Server Professional, mentioned on his blog (http://www.brentozar.com/archive/2007/04/sql-server-health-check/comment-page-1/#comment-17520) that he brought in Microsoft Support to do a “Health Check”. There were some questions about what this actually entails – so I thought I would post that description here.
A SQL Server Health Check from Microsoft is an offering provided through our support and consulting branches. If you’ve purchased a Premier support agreement, you can use the hours you pay for to have someone come out and check out your systems using two basic vectors: standard best practices, and the best practices for your environment. Let me explain that a little further.
Microsoft Consulting Services (MCS) has an advanced set of tools and techniques to reach deep into your systems to evaluate them against a set of criteria that we establish for the best performance, safety and reliability for SQL Server. But your SQL Server Instances will be different than someone else’s, so the consultant will also spend some time in an interview with various teams to find out how you’re using the system. They’ll consult with experts in that area back at Redmond, and at the end of the engagement you get what I feel is the most valuable part of the exercise – the report. The report shows what was done, how it was done, what the findings are and what recommendations the consultant makes. It’s a thing of beauty.
You don’t have to have a Premier agreement to get a SQL Server Health Check, and companies other than Microsoft have versions of this as well. The prices vary, so if you want a Microsoft person then contact your local Microsoft office and ask for the Consulting Manager for your area. They will get you to the right person and you can work out the costs from there. If you take a hard look, most of the time the cost is well justified.
On a related note, I think it’s fantastic that Brent has the foresight to bring in “another pair of eyes” on his systems. Brent is actually in the program to gain a SQL Server “Master” certification – something only a few people in the world have. He works for a software development firm that creates fantastic products for – wait for it – SQL Server! He could evaluate his own systems, and of course he does. But he realizes that there’s always something more to learn, and someone out there may know one more little detail that he doesn’t. This is the mark of a very bright person.
Categories: SQL Server MS Blogs
Start a SQL Server Agent Job using the SQL Server PowerShell Provider
Bucky Woody - Wed, 02/10/2010 - 07:13
Whew! That’s a mouthfull. Anyway, I thought I would share part of a script I wrote today to help automate (more) from PowerShell. This assumes a few things: that you’re doing this from the SQL Server 2008 PowerShell provider (not just good old regular PowerShell) and that you change the HAL9000 and SQL2K8 parts with your server name and your Instance name. Oh, and the Jobs I start are called “Test” and then something else. That’s the Test* part. You can use a specific name and drop the * or use your own naming convention.
Script Disclaimer, for people who need to be told this sort of thing:
Never trust any script, including those that you find here, until you understand exactly what it does and how it will act on your systems. Always check the script on a test system or Virtual Machine, not a production system. All scripts on this site are performed by a professional stunt driver on a closed course. Your mileage may vary. Void where prohibited. Offer good for a limited time only. Keep out of reach of small children. Do not operate heavy machinery while using this script. If you experience blurry vision, indigestion or diarrhea during the operation of this script, see a physician immediately.
# Start a Job named Test in the SQL Server 2008 PowrShell Provider
DIR SQLSERVER:\SQL\HAL9000\SQL2K8\JobServer\Jobs\Test* | % {$_.Start()}
Categories: SQL Server MS Blogs
Help for SQL Server
Bucky Woody - Tue, 02/09/2010 - 08:29
Sometimes issues pop up with your system. You need a way to find help, quickly. Here’s a few links that might be useful – feel free to post a reply to this post with other sources you might know, from web sources to your favorite consultant.
Note – this list is not exhaustive, and I’m happy to add or edit it if you post a reply. I know a lot of SQL Server professionals, and I don’t want to leave out anyone or commit them to something!
Web Help – Use these links to do a little research on your own
Main Troubleshooting Page:
http://technet.microsoft.com/en-us/sqlserver/bb895929.aspx
The main Microsoft page for learning resources is here:
http://www.microsoft.com/sqlserver/2008/en/us/learning.aspx
There are lots and lots of great community sites. Here are just a few:
http://www.sqlcommunity.com/
http://www.mvps.org/links.html#SqlServer
http://www.sqlpass.org/
http://www.mssqltips.com/
serverfault.com (mind your manners in here and you’ll get some help!)
http://serverfault.com/Post a feature request or a bug here:
https://connect.microsoft.com/site/sitehome.aspx/?siteid=68&wa=wsignin1.0
Phone Help – Use these Links to get someone to talk with
The main launching page for Microsoft Support is here – they can do most anything over the phone:
http://www.microsoft.com/microsoftservices/en/us/support.aspx
On-site Help – Use these links to get someone at your facility
Microsoft consulting services can either help you with a deep problem or help you
http://www.microsoft.com/microsoftservices/en/us/consulting.aspx
Many of the “Most Valuable Professionals” (MVP) for SQL Server either work at support and consulting firms or know people who do. They are a great resource:
http://www.mvps.org/links.html#SqlServer
Microsoft Partners (Use SQL Server as a search term and use the filter tool for services and your location):
http://www.microsoft.com/sqlserver/2008/en/us/find-partner.aspx
I use the web search of “SQL Server” and “Consulting” to locate other folks. You can add the name of your town (like “Seattle”) to narrow the range:
http://www.bing.com/search?q=SQL+Server+consulting&form=OSDSRC
Categories: SQL Server MS Blogs
Make it Easy for People to Help You
Bucky Woody - Mon, 02/08/2010 - 08:17
OK, there are probably a dozen or more of these kinds of posts, but I’ll dive in anyway. From time to time, people send me e-mails or comments on this blog asking for help. Sometimes it’s on the topic at hand, and other times the topic just jogs their memory about something else.
Often I’m happy to help. If I know the answer without doing any research (or even if I have to do a little) I’ll interrupt what I’m doing and dash out a note with the answer. But of course I have a job (three, to be exact) and so any time I help with a question I’m lengthening my day, spending less time with my family, and so on. If you think about it, everyone that you ask (and everyone I ask) is in the same boat – when someone helps me, I am taking their most valuable asset: their time. So I learned a very important lesson very early on: Make it easy for people to help you.
So here’s the steps to follow to do that – it really isn’t that hard:
- Request, don’t demand. I got an e-mail yesterday (on Sunday) where someone found a blog entry about one topic, and basically said this about another topic: “Tell me where I can find ‘x’ so that I can alter it.” Guess what I did? That’s right, I hit the delete key. If you are asking a question from a professional, you need to understand that they normally get paid – very well, sometimes – for their time. Make sure your question is a question, not a demand.
- Be clear about the problem. Vague statements don’t help – and very few people have the time to dig the real question out of you. Be specific. Ask the single question you really need help with.
- Keep the problem limited. “Rewrite my code for me” isn’t going to happen. “help me with this line” might. “Where do I go to find out more about the SELECT statement” is even better. If your problem takes more than a few minutes for someone to answer, then you should probably get someone on-site to help you.
- Explain what you’ve already done. This, of course, means you’ve already actually done something. What have you looked up, what do you already understand, where have you looked, what have you tried?
- Be polite. Please and Thank You are magic words, whether you get the answer you were looking for or not.
I’ll add one thing when you’re responding to a blog – not just mine, anyone’s. If you want to ask a question, ask it as a reply to a post, not an e-mail. The author wants to answer the question once, and it’s almost a guarantee that you’re not the only one with that question. Also, other readers might know the exact answer and help you even more. I know, you have to register, all that stuff. Just consider it the price of getting your answer.
Categories: SQL Server MS Blogs
Quote of the Day: On What Really Makes Something True
Bucky Woody - Fri, 02/05/2010 - 08:02
“Whether you believe you can or you believe you can't, you're probably right.” - Henry Ford
Categories: SQL Server MS Blogs
Multi-Monitor Support in SQl Server Management Studio 2008
Bucky Woody - Thu, 02/04/2010 - 08:33
Did you notice that there are a lot of "little" things that were improved in SQL Server 2008? One of those was done by one of my favorite developers and deals with multi-monitor support. In previous versions of SQL Server Management Studio you could open the tool in multi-monitor and arrange the panes onto mulitple monitors. But when you disconnected the second monitory (as is often done with a laptop) you would "lose" the panes that were moved - SSMS just wasn't aware of those multiple monitors. Vlad spent a lot of time making that not true.SSMS is much better about being aware of the other monitors.
Sure, there's more to do in this area. We'll keep improving that feature, but I wanted you to know that sometimes little things take a lot of work - and aren't always noticed.
Thanks, Vlad!
Categories: SQL Server MS Blogs
Performance improvement by orders of magnitude when merging partitions in SQL Server 2008R2
SQL Server Storage Engine - Wed, 02/03/2010 - 16:38
I am publishing this Blog on behalf of Hermann Daeubler, our SAP expert
Scenario:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
Let’s assume we have a table consisting of five partitions and the one in the middle needs to be dropped. In SQL Server we need the following steps to accomplish this task
a) Create a non-partitioned target table with the same structure and indexes as the partitioned table
b) Use the switch command to move the data from the middle partition to the new target table. Now the original partition is empty but still visible and the old boundary value isn’t removed
c) Complete the task by altering the partition function by merging the empty partition
Problem:
The partition switch command is a metadata-only operation and is frequently used by customers
to add (assuming staging table has already been populated) or remove partition data almost instantaneously. This is a significant improvement over non-partitioned tables where these operations can take time proportional to the size of the data.
However, there are issues with partition merging where depending on the hardware and the size
of the partitions, the step “c)” can still take much longer than expected especially when merging partitions in the middle of a table.
For details on partitions, please refer to
http://msdn.microsoft.com/en-us/library/dd578580.aspx
http://msdn.microsoft.com/en-us/library/ms190787.aspx
Why partition merge is slow?
It is a common scenario to put partitions on different file groups. The decision was made to make the
partition function merge a deterministic function. This means that there is a fixed rule which defines
on which file group the merged partition will end up. As a result the merge will be always in the same
direction corresponding to the type (right or left). This approach has the downside that there is no
optimization related to the size of the partitions. In a scenario where the source partition has 10 million rows and the destination partition has only 1 million rows, SQL Server would therefore move
the 10 million rows of the source partition rather than moving the 1 million rows from the destination partition. The same would happen if the destination partition is completely empty as in
the sample above where one would like to drop a partition.
There is a workaround for this when the partitions to be merged are mapped to the same filegroup. In the example above where the intention is to drop a partition it is clear that the destination partition is always empty. The workaround is to
· Switch both partitions out – the source as well as the destination partition
· Merge the partition function
· Switch back in the source partition.
But this workaround has a side-effect. It temporarily removes data from the table and an application using this approach has to provide a proper cleanup or rollback in case something goes wrong
Solution with the changes in SQL Server 2008R2:
CU6 for SQL Server 2008 SP1 includes a fix for this potential issue if all partitions reside on the same
file group. It checks which partition has less rows and moves the data from this one during a partition
function merge. This also covers the special case where one partition is empty.
In case the performance of dropping partitions in the middle of a table is essential for an application
it’s necessary to place all partitions on one single file group to take advantage of the fix in CU6.
Results :
The screenshots below and the SQL Script further down use a simple example to show how the
workaround basically could be implemented. It also shows the difference after installing CU6 for
SQL Server 2008 SP1.
Figure 1 above shows that a test table was created with five partitions and five million rows in each
partition.
Figure 2 above shows that the standard way to get rid of a partition took over 2 minutes on the test system.
There were obviously millions of logical reads involved due to the move of the data during
the partition function merge
Figure 3 above shows that afterwards boundary value 3 is missing as expected and four partitions are left
Figure 4 above shows the result of the workaround. Due to the additional switch more tables are involved.
But overall we see just a few logical reads and the whole process is done within a second
compared to over 2 minutes before
Figure 5 above shows the output of the standard method after installing CU6 for SQL Server 2008 SP1.
There is only one switch necessary and it finishes like the workaround before in a second.
To run the SQL Script below, you need to follow the following steps in this sequence
· First create the four procedures
· Run the procedures one after the other as seen at the end
· Create the test table as it’s necessary to specify how many rows should be generated. It’s a
very basic algorithm which simply doubles the number of records 10 times. A parameter of
value 25000 results in 25 million rows – five million rows per partition.
-- Procedure to create a partitioned test table
CREATE PROCEDURE create_partition_function_merge_test_tables ( @nr_rows INT )
AS
BEGIN
DECLARE @counter int
SET NOCOUNT ON
IF EXISTS( select * from sys.objects
WHERE name ='partition_function_merge_test_table' and type = 'U' )
DROP TABLE partition_function_merge_test_table
IF EXISTS( select * from sys.partition_schemes
WHERE name = 'PARTITION_FUNCTION_MERGE_TEST_SCHEME' )
DROP PARTITION SCHEME [PARTITION_FUNCTION_MERGE_TEST_SCHEME]
IF EXISTS( select * from sys.partition_functions
WHERE name = 'PARTITION_FUNCTION_MERGE_TEST_FUNCTION' )
DROP PARTITION FUNCTION [PARTITION_FUNCTION_MERGE_TEST_FUNCTION]
CREATE PARTITION FUNCTION [PARTITION_FUNCTION_MERGE_TEST_FUNCTION] ( int )
AS
RANGE RIGHT FOR VALUES ( 1,2,3,4,5 )
CREATE PARTITION SCHEME [PARTITION_FUNCTION_MERGE_TEST_SCHEME]
AS PARTITION [PARTITION_FUNCTION_MERGE_TEST_FUNCTION]
ALL TO ( [PRIMARY] )
CREATE TABLE partition_function_merge_test_table
(
key_partkey int NOT NULL,
key_col1 int,
key_col2 int,
data_col1 char(15),
data_col2 char(15),
data_col3 char(15)
) ON [PARTITION_FUNCTION_MERGE_TEST_SCHEME] ( key_partkey )
CREATE CLUSTERED INDEX PFM_IDX1 on partition_function_merge_test_table ( key_partkey )
SET @counter = @nr_rows
WHILE ( @counter > 0 )
BEGIN
INSERT INTO partition_function_merge_test_table VALUES
( @counter%5,@counter,@counter,
'Testdata Col1','Testdata Col2','Testdata Col3' )
SET @counter = @counter – 1
END
SET @counter = 10
WHILE ( @counter > 0 )
BEGIN
INSERT INTO partition_function_merge_test_table
SELECT * from partition_function_merge_test_table
SET @counter = @counter – 1
END
CREATE INDEX PFM_IDX2 on partition_function_merge_test_table ( key_col1 )
CREATE INDEX PFM_IDX3 on partition_function_merge_test_table ( key_col2 )
CHECKPOINT
END
GO
-- Procedure to drop the partition in the middle via switch and merge command
CREATE PROCEDURE drop_partition_3_slow
AS
BEGIN
IF ( OBJECT_ID('temp_partition') IS NOT NULL )
BEGIN
DROP TABLE temp_partition;
END
CREATE TABLE temp_partition
( key_partkey int NOT NULL,
key_col1 int,
key_col2 int,
data_col1 char(15),
data_col2 char(15),
data_col3 char(15)
)
CREATE CLUSTERED INDEX PFM_IDX1 on temp_partition ( key_partkey )
CREATE INDEX PFM_IDX2 on temp_partition ( key_col1 )
CREATE INDEX PFM_IDX3 on temp_partition ( key_col2 )
SET STATISTICS IO ON
ALTER TABLE partition_function_merge_test_table switch PARTITION 3 TO temp_partition
ALTER PARTITION FUNCTION PARTITION_FUNCTION_MERGE_TEST_FUNCTION ()
MERGE RANGE (3)
SET STATISTICS IO OFF
END
GO
-- Procedure to drop the partition in the middle via switch and merge command.
-- This time it will also switch out the "neighbor" before the merge and switch
-- it back in after the merge. This will avoid an unnecessary copy of the rows
-- in the neighbor partition
CREATE PROCEDURE drop_partition_3_fast
AS
BEGIN
IF ( OBJECT_ID('temp_partition') IS NOT NULL )
BEGIN
DROP TABLE temp_partition;
END
IF ( OBJECT_ID('temp_partition2') IS NOT NULL )
BEGIN
DROP TABLE temp_partition2;
END
CREATE TABLE temp_partition
( key_partkey int NOT NULL,
key_col1 int,
key_col2 int,
data_col1 char(15),
data_col2 char(15),
data_col3 char(15)
)
CREATE CLUSTERED INDEX PFM_IDX1 on temp_partition ( key_partkey )
CREATE INDEX PFM_IDX2 on temp_partition ( key_col1 )
CREATE INDEX PFM_IDX3 on temp_partition ( key_col2 )
CREATE TABLE temp_partition2
( key_partkey int NOT NULL,
key_col1 int,
key_col2 int,
data_col1 char(15),
data_col2 char(15),
data_col3 char(15)
)
CREATE CLUSTERED INDEX PFM_IDX1 on temp_partition2 ( key_partkey )
CREATE INDEX PFM_IDX2 on temp_partition2 ( key_col1 )
CREATE INDEX PFM_IDX3 on temp_partition2 ( key_col2 )
SET STATISTICS IO ON
ALTER TABLE partition_function_merge_test_table switch PARTITION 3 TO temp_partition
ALTER TABLE partition_function_merge_test_table switch PARTITION 4 TO temp_partition2
ALTER PARTITION FUNCTION PARTITION_FUNCTION_MERGE_TEST_FUNCTION ()
MERGE RANGE (3)
ALTER TABLE temp_partition2 ADD CONSTRAINT partCheck_constr CHECK ( key_partkey = 3 )
ALTER TABLE temp_partition2 switch TO partition_function_merge_test_table PARTITION 3
SET STATISTICS IO OFF
END
GO
-- Procedure to list the partitions of the test table
CREATE PROCEDURE list_partitions
AS
BEGIN
DECLARE @nr_partitions int
DECLARE @counter int
DECLARE @partrows int
DECLARE @boundary_value char(10)
SET @nr_partitions = ( SELECT COUNT(*) FROM sys.partition_range_values prv,
sys.partition_functions pf WHERE pf.function_id = prv.function_id AND
pf.name = 'PARTITION_FUNCTION_MERGE_TEST_FUNCTION' )
PRINT 'Partitions for table partition_function_merge_test_table :'
PRINT ''
PRINT 'nr | boundary value | # rows in partition'
SET @counter = 1
WHILE ( @counter <= @nr_partitions )
BEGIN
SET @boundary_value = CONVERT(CHAR(10),
(SELECT value FROM sys.partition_range_values prv, sys.partition_functions pf
WHERE pf.function_id = prv.function_id AND pf.name = 'PARTITION_FUNCTION_MERGE_TEST_FUNCTION' AND
boundary_id = @counter ))
SET @partrows=(SELECT p.rows FROM sys.partitions p, sys.objects o
WHERE p.object_id = o.object_id AND o.name = 'partition_function_merge_test_table' AND
p.partition_number = @counter AND index_id IN (0,1) )
PRINT ' ' + rtrim(convert(char(5),@counter)) + ' |' +
' ' + rtrim(@boundary_value) + ' |' +
' ' + convert(char(20), @partrows)
SET @counter = @counter + 1
END
END
GO
-- Repro to show the performance impact of different ways to drop a partition.
-- The procedure to drop the partition will turn "statistics io" on to prove
-- by the number of logical reads on the test table that in the first case
-- data will be moved whereas in the second case it won't.
-- This will change by installing CU6 for SQL Server 2008 SP1 which fixes this
-- behavior. While the runtime of the workaround was always the same the standard
-- approach to drop a partition in the middle of a table improved from over 2 minutes
-- to 1 second after installing CU6 for SQL Server 2008 SP1 – as shown on the
-- screenshots above. The difference is of course dependent on the hardware
-- configuration and might be even more or also less on other systems.
execute create_partition_function_merge_test_tables 25000
execute list_partitions
execute drop_partition_3_slow
execute list_partitions
execute create_partition_function_merge_test_tables 25000
execute list_partitions
execute drop_partition_3_fast
execute list_partitions
Categories: SQL Server MS Blogs
How Does Microsoft Do IT?
Bucky Woody - Wed, 02/03/2010 - 08:17
Microsoft is a big company – and of course we have a lot of IT infrastructure that we have to manage. It might surprise you to learn that we have an IT group, just like at your company. We have a networking team, a server hardware team, software teams, DBA’s, the whole bit. In fact, we have more Mac computers than just about anyone (other than that company down south from here) and we write some of the best-selling Apple software. We have a Linux lab.
How do we do that? How do you manage 80,000+ seats, especially when most of your company are a bunch of tech-savvy geeks? It’s a tough job, but the neat thing is that we tell you how we’re doing it – everything – right here: http://technet.microsoft.com/en-us/library/bb687780.aspx. If you want to focus in on just SQL Server, just check here: http://technet.microsoft.com/en-us/library/bb687798.aspx.
(By the way - I *totally* should be doing our marketing – isn’t that title catchy? My catch-phrases and product names would be a lot better than what we normally come up with. I’m just sayin’.)
Categories: SQL Server MS Blogs
Full Text Indexing Terabytes of Files with SQL Server and Cloud Storage
MS SQL CAT Team - Tue, 02/02/2010 - 19:52
Author: Darko Sancanin, Nicholas Dritsas Reviewers and contributors: Lubor Kollar, Stuart Ozer, Michael Thomassy Business Case There are currently over 50 million files (over 10 Terabytes of data) that the customer is migrating into a custom project management...(read more)
Categories: SQL Server MS Blogs
Data Design
Bucky Woody - Tue, 02/02/2010 - 08:31
I give series of classes and presentations on Data Design. I say “data” design instead of “database” design because we should consider more than just the database. Data might actually be stored in non-relational stores, such as Excel or XML files, and it might also be located in remote data stores like “cloud” technologies. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
Here are the links I use in that presentation. Although this isn't a comprehensive list of Data Design topics, I’ll visit this topic from time to time so you may want to bookmark this page in your favorites:
Evidence and Comments:
Importance:
Yes, it matters: http://tinyurl.com/yb5ucvn
Classic software development mistakes: http://tinyurl.com/y9x9vn8
Necessity of Good Design: http://tinyurl.com/yac3um3
Importance of Database Design Ranked high for BA/Dev interaction: http://tinyurl.com/yex4sjf
Structured Methods:
Test-Driven Development: http://tinyurl.com/maus78
From the BA’s Perspective: http://tinyurl.com/yea6pbe
Data-Map Examples: http://tinyurl.com/2d2nhr
Universal Patterns for Data Modelling: http://tinyurl.com/ybj7tb3
Tools:
EF Framework Design Example: http://tinyurl.com/yeuda49
Using EF and LINQ can cause divisions: http://tinyurl.com/yaljmhn
Categories: SQL Server MS Blogs
Quick security references (QSR) on Cross-Site scripting and SQL injection.
SQL Server Security Team - Mon, 02/01/2010 - 21:11
Recently the Security Development Lifecycle (SDL) team announced the release of new type of security guidance papers called Quick security references (QSRs). The first two papers focus on Cross-Site scripting and SQL Injection. I would strongly recommend reading these interesting QSRs, as well as keeping an eye on the SDL blog.
-Raul Garcia
SDE/T
SQL Server Engine
Categories: SQL Server MS Blogs
Transparent Data Encryption and the Latest Data Breach
Bucky Woody - Mon, 02/01/2010 - 08:06
Well, It’s happened again. Hundreds of thousands of private records were stolen from a database. This one, however, was different. No one stole any passwords, no one did any social engineering, nothing was captured in-line. No, this one was accomplished by stealing the actual hard drives themselves! When a thief breaks in and steals hard drives, you can be sure they know what they are after.
So even if the company had taken all of the regular authentication precautions and so on, the thieves could still take this step. But the data loss could still be prevented…
Enterprise SQL Server 2008 (and later) has a new feature called “Transparent Data Encryption”. When you turn this feature on, you don’t have to change anything in your applications – from then on, the data files, log files, tempdb and even the data on the backup files are encrypted. Without the certificate you use to encrypt them, the thief can’t get to the data.
I’m sometimes asked why a company should upgrade to the latest version of SQL Server, or why Enterprise Edition is important. A few hundred thousand private records sounds like a pretty good reason to me.
Categories: SQL Server MS Blogs
How to have a Select All Multi-Value Cascading Parameter
SQL Server SQL CSS - Fri, 01/29/2010 - 08:31
I’ve seen several cases that involved Multi-Value Cascading Parameters and how the behavior of those work. Let me start with the default (Out of the box) behavior. The example report I’ll be using will make use of the AdventureWorksDW sample database. I will also be using SQL 2008 R2 November Release, but the same will apply to SQL 2008 Reporting Services as well.
In my report, I have three multi-value parameters. Each one dependent on the parameter before it (cascading parameters). The parameters are Category, Subcategory and Product, in that order.
As you can see above, I have Bikes selected for the category which will select all by default as that is my first selection. But what happens if I now add in another category?
Only the first three are selected after adding Components to the category list. We have seen where people feel this is a bug, but that was actually intended. We will only maintain what your current selection is. On the first selection, we didn’t have anything, we actually didn’t have a dataset yet because I didn’t have it loading by default. But that initial selection will be Select all. After that, it will be based on what you have selected. Removing entries will make it seem that we still select all, but that is because the items left were still selected previously, so nothing really changes. But adding items to the mix will cause them to not be selected by default. Only the original selection will remain.
So, then the question comes in, what if i want them to all be selected whenever it gets refreshed? We currently do not provide for that ability out of the box, but I have a way you can add the functionality into your report.
I will add a disclaimer here. The code that you will see in this blog has not been fully tested and should not be used in production. Please be sure to review it first and determine how it will affect performance within your report.
SubcategoryLets start with the Subcategory parameter as there is nothing we need to do with Category. To start, you will want to add the following into the code section of the report. This can be found by going to Report on the Menu bar and selected Report Properties.
Function EncodeParamValue(ByVal ParentParam As Object(), ByVal ChildParam As Integer) As String
Dim i As Integer
Dim EncodedChildParam As String = ""
'Concatenate the ParentParam as the prefix
For i = 0 To ParentParam.Length - 1
If i = 0 Then
EncodedChildParam = CStr(ParentParam(i))
Else
EncodedChildParam = EncodedChildParam + "-" + CStr(ParentParam(i))
End If
Next i
'Add the ChildParam at the end
EncodedChildParam = EncodedChildParam + "_" + CStr(ChildParam)
Return EncodedChildParam
End Function
Function DecodeSubcatParamValue(ByVal SubCategoryIDs As Object()) As Object()
Dim i As Integer
Dim SubCategoryID As String
'Remove the prefix on the parameter values to restore the original value
For i = 0 To SubCategoryIDs.Length - 1
SubCategoryID = SubCategoryIDs(i)
SubCategoryIDs(i) = SubCategoryID.Substring(SubCategoryID.IndexOf("_") + 1)
Next i
Return SubCategoryIDs
End Function
These two functions will be what we use to alter the parameter values. Essentially saying that the value includes everything. But we strip it out when we actually want to use it.
The next step is to modify the dataset for Subcategory to make use of the EncodeParamValue function. We will add a Calculated Field. You can do this by going to the properties of the DataSet, selecting the Fields section and click on Add, select Calculated Field.
We will call this field ModifiedSubcategoryKey. This name is based on what the value field was we were using for the Parameter itself which was the ProductSubcategoryKey field. So, we are just modifying that field a bit. The expression for that field will be the following:
=Code.EncodeParamValue(Parameters!Category.Value,Fields!ProductSubcategoryKey.Value)
We are using the EncodeParamValue to take in the Category (1st Parameter) list and tack it onto the SubCategoryKey. We then need to adjust the Available Values and Default Values to use the ModifiedSubcategoryKey instead of ProductSubcategoryKey. This is done in the Parameter Properties for Subcategory. You will want to change the Value Field to be ModifiedSubcategoryKey.
The last thing we will do for Subcategory is to change the refresh value to “Always Refresh”. This is done in the Parameter Properties for Subcategory under the Advanced section.
That is all you need to get the Subcategory parameter drop down to work correctly.
ProductNow we need to make sure Product works with the SubcategoryKey, as it expects that as a parameter for its dataset. It expects an Integer and right now we are giving it bit string. Without changing anything, you will see the following error:
We need to make use of our Decode function as the Parameter Value expression for the Product Dataset. This is done in the Product Dataset properties under the Parameters section. The expression we will use is the following:
=Code.DecodeSubcatParamValue(Parameters!Subcategory.Value)
This will take in the big string we created, and break it up to just give us the value we really want. This allows the Product Drop down to show values, but in order for the Select all behavior to work, we need to use the Encode method as well. So, we will add a Calculated Field to the Product Dataset called ModifiedProductKey. The expression for this field will be the following:
=Code.EncodeParamValue(Parameters!Subcategory.Value,Fields!ProductKey.Value)
We then want to change the Available Values and Default Values for the Product Parameter. This is done in the Parameter Properties for Product. We want to change the Value Field to ModifiedProductKey. This will get the Product Drop down to have the Select All behavior we are looking for.
This will introduce two levels of encoding on the product value, so I created a second Decode method which will strip it twice for use in the Report Dataset.
Function DecodeProductParamValue(ByVal ProductIds As Object()) As Object()
Dim i As Integer
Dim ProductId As String
'Remove the prefix on the parameter values to restore the original value
For i = 0 To ProductIds.Length - 1
ProductId = ProductIds(i)
'The first one removes the Category encoding
ProductId = ProductId.Substring(ProductId.IndexOf("_") + 1)
'The second one removes the SubCategory encoding
ProductIds(i) = ProductId.Substring(ProductId.IndexOf("_") + 1)
Next i
Return ProductIds
End Function
Report DatasetThe last thing to take into account is the Dataset for the report itself. You will need to use the Decode methods for the parameters within the Report Dataset like we did on the Product Dataset for the parameter listing. You will need to do this for both the SubCategory Parameter value as well as the Product Parameter Value using DecodeSubcatParamValue and DecodeProductParamValue respectively.
Hopefully this will help you to get the desired behavior you are interested in.
Adam W. Saxton | Microsoft SQL Server Escalation Services
Categories: SQL Server MS Blogs
Quote of the Day: On Relaxing
Bucky Woody - Fri, 01/29/2010 - 08:04
Love this:
“There is no pleasure in having nothing to do; the fun is in having lots to do and not doing it.” – Mary Little
Categories: SQL Server MS Blogs
FILESTREAM: Storing Binary Objects in a database – or not
Bucky Woody - Thu, 01/28/2010 - 08:47
Many shops need to store binary large objects (sometimes called BLOBS) in a database. There are really only two ways to do this: store in them in a table structure in the database itself using a binary data type, or store them in the operating system in a file folder somewhere and point to the file using a text field in a table.
Both of these approaches have issues. Relational Databases aren’t really designed to hold that much data in a single field – not ours, not anybody’s. And pointing to a file is risky, since the file might change, it isn’t under database control for security and backups and so on.
Enter FILESTREAM. In SQL Server 2008 we introduced a feature that actually combines the two approaches into one. Using FILESTREAM, you enter the data into a “column”, but in fact SQL Server stores the data on the hard drive. It’s lightning fast, doesn’t lose the “pointers” to the files, and keeps the whole thing under database control. You can secure it with the same permissions as the database, and it gets picked up in backups and so on.
It does have some limitations and caveats, so be sure and check out the reference data in the following link. And then try it yourself – pretty easy to set up and manage.
http://technet.microsoft.com/en-us/library/bb933993.aspx
Categories: SQL Server MS Blogs
Data as Strategy
Bucky Woody - Wed, 01/27/2010 - 06:48
I’ve created a presentation called “Data as Strategy”, and in it I cover multiple solutions for various issues that organizations face in dealing with the “new world” of data management. No longer are we just DBA’s or Developers, now we’re part of the broader business strategy of using data in strategic decisions.
That presentation covers a lot of ground, really quickly, so I thought I would post some of the links I mention during the talk here. I’ll actually come back and update this page as time goes on, so be sure to bookmark it. I’ll have a mix here of Microsoft sources, outside opinions, whitepapers, tutorials and videos wherever I can.
SQL Server 2008
New features on SQL Server 2008: http://tinyurl.com/ylks3xo
Policy Based Management Link List Page: http://tinyurl.com/yldm4co
SQL Server 2008R2 General
What's New: http://tinyurl.com/yzhdbod
Editions Overview: http://tinyurl.com/ygajpga and http://tinyurl.com/yg3a98z
Features supported by Edition: http://tinyurl.com/yf7bdzx
Unicode Compression: http://tinyurl.com/ycf892z
SysPrep and SQL Server Installs: http://tinyurl.com/yzh7t6j
SQL Azure
Azure Overview: http://tinyurl.com/y8s52vh
Windows Azure Training Kit: http://tinyurl.com/5vrt7q
Writing a Azure Program in 5 steps: http://tinyurl.com/ye6chog
Azure Data Sync: http://tinyurl.com/ylsykfb
Future of programming with Azure Video: http://tinyurl.com/ykqsbsf
Reference list for Windows Azure: http://tinyurl.com/yze9azr
SQL Server 2008R2 UCP and Management
UCP and DAC Overview: http://tinyurl.com/qd8n7m
More DAC information: http://tinyurl.com/yhvjmsb
Virtualizing the Database Layer: http://tinyurl.com/yhfl9hh
SQL Server 2008R2 Programming and Data Modeling
Data Programming and Modeling Video: http://tinyurl.com/nxuyda
More Data Modeling Videos: http://tinyurl.com/yj44zkv
SQL Server 2008R2 StreamInsight
Stream Insight Overview: http://tinyurl.com/l2jhgo
StreamInsight Whitepaper: http://tinyurl.com/mt3u6h
StreamInsight Video: http://tinyurl.com/y9llph3 and http://tinyurl.com/yg9r8bq
SQL Server 2008R2 PowerPivot
PowerPivot Resources: http://tinyurl.com/ygntujb
PowerPivot Server Install Instructions: http://tinyurl.com/yzegm5l
Self-service BI Overview: http://tinyurl.com/l3bapy
Virtual Lab for PowerPivot: http://tinyurl.com/yg9tdef
Practical overview of PowerPivot: http://tinyurl.com/yk422up
SQL Server 2008R2 Master Data Management
Master Data Services Overview: http://tinyurl.com/mc7qmx
Master Data Services Tutorial: http://tinyurl.com/yl8v37q
Master Data Management Whitepaper: http://tinyurl.com/abzpem
Categories: SQL Server MS Blogs
Attaching a Database
Bucky Woody - Tue, 01/26/2010 - 09:03
I had someone ask me yesterday how they could get to a database used by a product that they owned, but that was installed using SQL Server Express. They didn’t have access to the database, and they didn’t know the password for the service that started Express, so they wanted to know if they could look at the data.
There are a few ways to do this, but the quickest, safest and easiest for me is to “attach” the database on another Instance of SQL Server. To understand how this works, let me quickly explain how SQL Server uses databases from a physical perspective.
SQL Server uses two types of files, sometimes lots of each type. The two basic types are Data files and Log files. SQL Server basically writes data to the Log file first, and then on to the Data files. Of course it’s more complicated than that; but this is the basic flow.
When SQL Server first starts up, it reads (among other things) the master system database. And that’s where the crux of this post comes into play. In that database are two things that we care about right now: the logical name of the databases SQL Server knows about and the physical locations for the files that database uses.
With that information in mind, here’s a simple way to transfer a database from an Instance you don’t have access to into one that you do.
There are a LOT of disclaimers here.
First: don’t do this on a database you don’t own, and own all the data for. That’s illegal.
Second, don’t do this with a large database – the possibility of multiple files in the filegroups on a complex database is high, and you might not get them all.
Third: you have to be running the same (or higher) version of SQL Server on the destination server, and the same (or higher) edition of SQL Server on the destination server.
Fourth: you’ll have to stop the source Instance. If you have users on that system, you don’t want to stop it without consulting everyone first.
OK – with all of that stated, you first need to stop the “source” instance. Let it shut down completely and normally. This will (theoretically) commit all of the transactions from the logs to the databases.
Now copy the .MDF and .LDF files for the database you want to a location on your test Instance. If you don’t know what these are, you can try and copy what you think are those files, but don’t copy any of the system databases from the source system to your test Instance!
Start the source Instance back up and let the users back into the application.
On the test system, write-down the location where you copied both of those files (assuming there was one of each – it get’s trickier when there are multiples).
Open SQL Server Management Studio.
Right-click the “Databases” node in Object Explorer and select “Attach…” from the menu that appears.
Click “Add” to find the MDF and LDF files, and then “OK” to name the database.
You have two other options as well. You can use the sp_attachdb stored procedure (detail here - deprecated) or the newer CREATE DATABASE…FOR ATTACH statement (article on that here).
In any case, you now have the database, and you now “own” it in that test Instance. The logins will all be incorrect, since the names in the database security don’t match the new Instance’s master database. But you do have that data.
Categories: SQL Server MS Blogs
Tracking SQL Server Time
Bucky Woody - Mon, 01/25/2010 - 08:10
In the past few blog posts I’ve showed you how to use several methods to track things in SQL Server. You can use the “tags” to the right of this post here at this site to list things like PowerShell, Performance Tuning and so on. Now that you’re armed with these tools, what should you track?
Well, one of the items I track is time. I track the time it takes for lots of things, but they fall into three general buckets:
- Queries – Normally I track the five longest running queries with their query plans.
- Maintenance – From how long each backup takes to index reorgs and rebuilds, I want to know how long these things take.
- Jobs – Most all of us have SQL Server Agent Jobs, and developing a schedule of how long they are running is very useful.
For each of these, I track the minimum, maximum and average times. I look for outliers – things that suddenly change and so on.
There are a lot of uses for this information. From performance tuning to developing a recovery plan, all of these actions need to be taken into account.
I think I’ll write up an article sometime on how I do this – it’s a little long for a blog post.
Categories: SQL Server MS Blogs
Quote of the Day: A Boyfriend Test (1)
Bucky Woody - Fri, 01/22/2010 - 07:54
My daughter is in her teen years, and I told her early on that she should have a few “tests” for any boy she dates (when she starts dating, that is). This is is one of them:
“You can easily judge the character of a man by the way he treats those who can do nothing for him.” - Goethe
Categories: SQL Server MS Blogs
How to get a x64 version of Jet?
SQL Server SQL CSS - Thu, 01/21/2010 - 09:20
We have had a number of people ask about how they can get the Jet ODBC driver/OLE DB Provider as 64 bit. Windows only ships the 32 bit versions of these. The answer is that the windows versions won’t be x64 as those items are deprecated. What does deprecated mean? Here is the excerpt from the MDAC/WDAC Roadmap on MSDN:
Deprecated MDAC/WDAC ComponentsThese components are still supported in the current release of MDAC/WDAC, but they might be removed in future releases. Microsoft recommends, when you develop new applications, that you avoid using these components. Additionally, when you upgrade or modify existing applications, remove any dependency on these components.
And here is what it lists about the Jet Database Engine:
Microsoft Jet Database Engine 4.0: Starting with version 2.6, MDAC no longer contains Jet components. In other words, MDAC 2.6, 2.7, 2.8, and all future MDAC/WDAC releases do not contain Microsoft Jet, the Microsoft Jet OLE DB Provider, the ODBC Desktop Database Drivers, or Jet Data Access Objects (DAO). The Microsoft Jet Database Engine 4.0 components entered a state of functional deprecation and sustained engineering, and have not received feature level enhancements since becoming a part of Microsoft Windows in Windows 2000.
There is no 64-bit version of the Jet Database Engine, the Jet OLEDB Driver, the Jet ODBC Drivers, or Jet DAO available. This is also documented in KB article 957570. On 64-bit versions of Windows, 32-bit Jet runs under the Windows WOW64 subsystem. For more information on WOW64, see http://msdn.microsoft.com/en-us/library/aa384249(VS.85).aspx. Native 64-bit applications cannot communicate with the 32-bit Jet drivers running in WOW64.
Instead of Microsoft Jet, Microsoft recommends using Microsoft SQL Server Express Edition or Microsoft SQL Server Compact Edition when developing new, non-Microsoft Access applications requiring a relational data store. These new or converted Jet applications can continue to use Jet with the intention of using Microsoft Office 2003 and earlier files (.mdb and .xls) for non-primary data storage. However, for these applications, you should plan to migrate from Jet to the 2007 Office System Driver. You can download the 2007 Office System Driver, which allows you to read from and write to pre-existing files in either Office 2003 (.mdb and .xls) or the Office 2007 (*.accdb, *.xlsm, *.xlsx and *.xlsb) file formats. IMPORTANT Please read the 2007 Office System End User License Agreement for specific usage limitations.
Note: SQL Server applications can also access the 2007 Office System, and earlier, files from SQL Server heterogeneous data connectivity and Integrations Services capabilities as well, via the 2007 Office System Driver. Additionally, 64-bit SQL Server applications can access to 32-bit Jet and 2007 Office System files by using 32-bit SQL Server Integration Services (SSIS) on 64-bit Windows.
This all pertains to the components that actually ship with Windows. The Office team has since taken up Jet as part of Access and has come out with what they call the Access Control Entry (ACE) driver. For more information on the ACE Drivers, you can check out this blog post which goes into details. the ACE driver/provider is completely backwards compatible with Jet 4.0 though.
Office 2010 will introduce a 64 bit version of Office. With that is coming a 64 bit version of the ACE Driver/Provider which will in essence give you a 64 bit version of Jet. The downside is that it doesn’t ship with the operating system but will be a redistributable. There is a beta version available of this driver, as Office 2010 hasn’t been released yet.
2010 Office System Driver Beta: Data Connectivity Components
http://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en
Adam W. Saxton | Microsoft SQL Server Escalation Services
Categories: SQL Server MS Blogs
