Browsed by
Tag: SQL Server 2008 R2

When was the last time I deployed that SharePoint solution?

When was the last time I deployed that SharePoint solution?

Ever have trouble remembering when you last deployed a specific solution?  Now, none of us would ever allow an environment to be uncontrolled and let people willy-nilly install solutions, but in the real world sometimes things slip through our well controlled documentation and we need a hand.

For those of us with more than 5 farm solutions hunting and pecking for the last installed date can be problematic.  One of my colleagues (at this point I am forgetting who, so if it was you take a bow) helped me write a script to grab the information from SharePoint.

Here is the script:


This will output the information to a text file on your C: called solutionlog.txt.  Remove the “ >> c:solutionlog.txt “ and it will display onscreen like this:


If you are in an environment that has continuous integration coming out of TFS or another like service, you can schedule this and have it email you the output on a regular basis. 



What’s new in SQL Server Code Name “Denali” CTP3

What’s new in SQL Server Code Name “Denali” CTP3

Be sure to check out the What’s New (SQL Server “Denali”) site for full details, but here are the highlights in this PUBLIC release (no TAP NDA material here!) are:

SQL Server Installation

  1. Installing Prerequisites During SQL Server Code-Named “Denali” Setup
    1. Windows PowerShell 2.0 is a prerequisite, but not installed by the SQL Server Setup wizard
    2. .NET Framework 3.5 SP1 is a requirement, but is not installed by the SQL Server Setup wizard, it  requires you to download and install manually
    3. .NET Framework 4 is a prerequisite and is installed as a part of the SQL Server Setup wizard
    4. Minimum OS configuration is now Windows 7 SP1 & Windows Server 2008 R2 SP1
      1. check out the Hardware and Software Requirements for Installing SQL Server “Denali”
  2. You can now install Data Quality Services (DQS) using the SQL Server Setup wizard
  3. Product Update is a new setup feature which allows you to download the latest updates and apply them during install
  4. Server Core Installation is now supported
  5. SQL Server multi-subnet clustering makes it so that you can have failover nodes on different subnets
  6. Local Disk is now a supported storage option for tempdb for SQL Server failover cluster installations
  7. BUILTINadministrators and Local System (NT AUTHORITYSYSTEM) are not automatically provisioned in the sysadmin fixed server role
  8. Setup now offers default accounts for the SQL Server services whenever possible
  9. The Active Directory Helper service is no longer installed because it is no longer needed
  10. SQL Server Itanium editions are no longer supported

Database Engine (too much here to list all of the good ones, this is a MUST READ)

  1. AlwaysOn SQL Server Failover Cluster Instances
  2. AlwaysOn Availability Groups
  3. Indirect Checkpoints

Manageability Enhancements

  1. The Database Engine Query Editor introduces enhanced functionality for Transact-SQL debugging and IntelliSense.
  2. The Upgrade a Data-Tier Application wizard has been updated to perform an in-place upgrade, which replaces the side-by-side upgrade process
  3. Users import the sqlps module into PowerShell, and the module then loads the SQL Server snap-ins.
  4. The bcp Utility and sqlcmd Utility utilities now have the -K switch, which allows you to specify read-only access to a secondary replica in an AlwaysOn availability group.
  5. Database Engine Tuning Advisor Enhancements allow you to use the query plan cache to avoid having to create manual workloads from a script or trace.

Programmability Enhancements

  1. You can no longer use CREATE ENDPOINT or ALTER ENDPOINT to add or modify SOAP/HTTP endpoints
  2. The FileTable feature leverages FILESTREAM to allow files and documents to be stored in special tables and accessed using Windows applications, as though they were stored on the file system without making changes to the applications
  3. Semantic search builds upon the existing full-text search feature in SQL Server but enables new scenarios that extend beyond syntactical keyword searches
  4. Full Text Search now supports property-scoped searching on properties emitted by IFilters, Customizable NEAR option of the CONTAINS predicate or the CONTAINSTABLE function
  5. New Word Breakers and Stemmers (this one has a a bit baffled, I thought I was doing well understanding the Programmability stuff until I got to this one…)
  6. The EXECUTE statement can now specify the metadata returned from the statement by using the WITH RESULT SETS argument
  7. You can specify a range of rows returned by a SELECT statement based on row offset and row count values that you provide
  8. Three new sub-data types for geometry and geography data types can be used to store circular arc segments, CircularString,CompoundCurve, and CurvePolygon. (and my ITPro brain just imploded…)
  9. A sequence object is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created. It operates similar to an identity column, but sequence numbers are not restricted to use in a single table.
    • I know lots of Oracle DBAs who are excited by this one, personally I haven’t wrapped my head around  it completely yet…  Going to have to read more on Sequence Numbers
  10. The THROW statement can be used to raise an exception and transfer execution to a CATCH block of a TRY…CATCH construct
  11. 14 new functions:
  12. 1 changed function:
  13. New and Enhanced Query Optimizer Hints
  14. New XEvent Enhancements
  15. New Analytic Functions:

Scalability and Performance Enhancements

  1. New data warehouse query acceleration feature called columnstore indexes
  2. Indexes containing varchar(max), nvarchar(max), and varbinary(max) columns can now be rebuilt as an online operation
  3. 15k partitions are now supported by default instead of 1k

Security Enhancements

  1. User-defined server roles are now available.  This allows a role to be defined at the server level and grant or deny access across all databases
  2. You can now define a default schema for a Windows group
  3. Significant SQL Server Audit Enhancements
  4. Access to contained databases is permitted through contained database users which do not require logins
  5. The HASHBYTES function now supports the SHA2_256, and SHA2_512 algorithms
  6. The RC4 algorithm is only supported for backward compatibility
  7. The maximum length of private keys imported from an external source is expanded from 3,456 to 4,096 bits
  8. SMK and DMK encryption changes from 3DES to AES
The big news: Data Storage Changes for SharePoint 2010

The big news: Data Storage Changes for SharePoint 2010

I go on vacation for one week and they try to change the entire strategy on us, eh?  When I first started hearing rumbles about the changes announced by the Microsoft SharePoint Product Group via their blog it sounded like the world was completely changed and that all previously known storage strategies can be thrown out the window in favor of using RBS and storing whatever you want in the Content DB!

Happily that is not at all what was being said.  While the recommended limits have been revised by Microsoft to suggest that significantly more can be stored, the key word in the statement is CAN, not should.

Let’s examine a few telling things from the Product Group’s blog article:

For a SharePoint content database up to 4 TB you need to additionally plan for the following two requirements:

  • Requires disk sub-system performance of 0.25 IOPS per GB, 2 IOPS per GB is recommended for optimal performance.

  • Requires the customer to have plans for high availability, disaster recovery, future capacity, and performance testing.

  • And you need to review additional considerations in the TechNet Boundaries and Limits article.

What this part is suggesting to us is we can us a single mirrored set (RAID1) for a large content database and get what Microsoft is defining as “optimal performance” using a 15k RPM drive scenario (approximately 180 IOPS per drive).  Great if you are looking to get some separation and you are doing sequential I/O, however when you separate the BLOBs out of the ContentDB you aren’t doing sequential I/O anymore.

SQL Server Default Trace

SQL Server Default Trace

In every version since SQL 2005 there has been a “background trace” enabled by default on all SQL Server installations.  This was a bit of a shocking revelation to me, as I am not a DBA, since every time I have called Microsoft dozens upon dozens of times and talked to tier 3 support / product team members for SQL Server and none of them have mentioned this fact when we have been diagnosing issues in SQL.  We have always had to set up a new trace.

Use the following command to validate that the default trace is enabled:


This will return something that looks like:




1 1 2
1 2 E:MSSQL10_50.instanceMSSQLLo​glog_560.trc  
1 3 20
1 4 NULL
1 5 1

From MSDN we get a legend that helps us under stand the above output:

Column name Data type Description



ID of the trace.

property int

Property of the trace:

1= Trace options. For more information, see @options in sp_trace_create (Transact-SQL).

2 = File name

3 = Max size

4 = Stop time

5 = Current trace status. 0 = stopped. 1 = running.

value sql_variant Information about the property of the trace specified.

The question I have heard most often since this discovery is “What does it matter?  Microsoft has it turned on by default so that must mean they intended it that way.  It must not cause a performance impact.  Right?”

After I stopped laughing at the idea that because Microsoft turned it on by default it must be ok (see Windows 2000 and before where everything was turned on and wide open upon install) I explained that the default trace is dropped into the installed Logs directory.  This cannot be changed.  You can stop the default trace and create your own, but that will break some of the built in reports that rely upon it.

The key is knowing that this is there.  While it may be lightweight and designed to be non-impactful, if you have anything else with a write intensive load directed at the same spindles you can expect to see some contention.

The positive of it is that now that we know it is there, it is extremely useful in troubleshooting.  The default trace can be queried by using the following command to pull back the most useful data (querying * pulls back WAY too much data for my taste):



SharePoint 2010 & SQL 2008 R2 build numbers and helpful patching links

SharePoint 2010 & SQL 2008 R2 build numbers and helpful patching links

In an effort to make life simpler I have compiled a short list of useful link to build numbers and patching sites for SharePoint 2010 and SQL 2008 R2.

The Microsoft SharePoint Updates PagesSharePoint 2010 –
SharePoint 2007-
This is your official sites for downloading the SharePoint updates from Microsoft.  Highly useful rather than waiting on the blogger community to send you a link, or get the TechNet bulletin, your carrier pigeon to arrive, or any of the other 120+ ways to get your SharePoint update information!

Cornelius J. van Dyk’s Blog on Versions for SharePoint
Highly useful link if you find yourself a CU or two behind and you want to know what version of the CU is currently applied to your farm.  Simply visit your Central Admin Manage Patch Status page http://localhost:####/_admin/PatchStatus.aspx to check what version number your farm is on.

SQL 2008 R2 build numbers
This has proven to be the most reliable site I have found for listing the latest build numbers for SQL Server since 2008 R2 dropped.  To find out what what build you are running do the following:

  1. Open SQL Management Studio
  2. New Query
  3. type and then execute the following command
    select @@version 

    .externalclassaf644619e5194a77b8e785ba7f262756 .csharpcode, .externalclassaf644619e5194a77b8e785ba7f262756 .csharpcode pre
    {font-size:small;color:black;font-family:consolas, “courier new”, courier, monospace;background-color:#ffffff;}
    .externalclassaf644619e5194a77b8e785ba7f262756 .csharpcode pre
    .externalclassaf644619e5194a77b8e785ba7f262756 .csharpcode .rem
    .externalclassaf644619e5194a77b8e785ba7f262756 .csharpcode .kwrd
    .externalclassaf644619e5194a77b8e785ba7f262756 .csharpcode .str
    .externalclassaf644619e5194a77b8e785ba7f262756 .csharpcode .op
    .externalclassaf644619e5194a77b8e785ba7f262756 .csharpcode .preproc
    .externalclassaf644619e5194a77b8e785ba7f262756 .csharpcode .asp
    .externalclassaf644619e5194a77b8e785ba7f262756 .csharpcode .html
    .externalclassaf644619e5194a77b8e785ba7f262756 .csharpcode .attr
    .externalclassaf644619e5194a77b8e785ba7f262756 .csharpcode .alt
    .externalclassaf644619e5194a77b8e785ba7f262756 .csharpcode .lnum

We are working on getting a SQL Server version’s page up on CvD’s blog as well, but for now I here is a table with the info:

April 2010 RTM
May 2010 CU1
June 2010 CU2
August 2010 CU3
October 2010 CU4
December 2010 CU5