The amount of time the “New” icon is shown in SharePoint 2010 can be changed at the Web Application level using PowerShell. Below are examples to get or change settings for the icon:

Get the Current Duration to Display the “New” Icon

$webApp = Get-SPWebApplication "http://webAppURL/"
$webApp.DaysToShowNewIndicator

Change the Duration to Display the “New” Icon

# Set to 1 Day
$webApp = Get-SPWebApplication "http://webAppURL/"
$webApp.DaysToShowNewIndicator = 1
$webApp.Update()

Prevent the “New” Icon from Displaying

# Essentially, just set the display duration to 0 days.
$webApp = Get-SPWebApplication "http://webAppURL/"
$webApp.DaysToShowNewIndicator = 0
$webApp.Update()

The scenario is that normal users are unable to see the values assigned to Managed Metadata columns or Enterprise Keywords in Lists or Libraries. However, Site Collection Administrators are able to see the values. If a normal user is promoted to a Site Collection Administrator, they are then able to see the column values.

This behavior is most likely caused by the permissions having been changed on the hidden List “TaxonomyHiddenList.” This List essentially caches managed terms and keywords for fast retrieval, rather than having to also query against the Managed Metadata database(s).

To access the List, point your browser to:

<site collection url>/Lists/TaxonomyHiddenList/

Access the permissions for the List, and add “NT AUTHORITY\Authenticated Users” with Read access. This is the default setting, along with the System Account having Full Control access.

If you test your Lists and Libraries again, normal users should now be able to see the Managed Metadata and Enterprise Keyword values.

Team Foundation Server 2010 provides functionality for testing applications with built in support for test plan and test case management    In our Agile/Scrum projects, we define all of the Test Cases in our planning meeting to define done for each User Story.  When starting new projects, team members often ask is how to format the Test Cases so they are clear.  One way we have found to be very useful is to use the same format as found in Behavior Driven Development(BDD).  BDD uses a format for communicating Test Cases called Gherkin.  The Gherkin format follows the pattern below:

Figure 1 – Given, When, and Then definition

Just like the User Story format (As a [user], I want to be able to do [business process], so that [business value]), we have found the Gherkin format is very useful for teams learning Agile.  In fact, these Test Cases can be written for Acceptance/Functional tests and for Unit Tests.  When we work with customers and our own projects we install our customized Deliveron Agile Process Template as part of the Deliveron Agile Delivery Process.  The Deliveron Agile Process Template is a slightly customized version of the MSF for Agile 5.0 process template.  In the Test Case work item, we have added fields for Given, When, and Then.  The “Then” should also match the expected result in the test steps.

Figure 2 – BDD additions for TFS Test Case Work Item

Figure 3 – Expected result matches the “Then”

Customizing the TFS Test Case Work Item Templates (WIT)  to add these fields is straight forward.  Follow the steps in this post I did a couple years ago.  It was for TFS 2008 but the steps are the same in TFS 2010.

In summary, use Test Cases to define done of the User Story and use BDD and Gherkin for the language of the Test Case.  Feel free to contact us if you have any questions about these changes or about the Deliveron Agile Delivery Process.

I recently worked on a project where I was calling a WCF service that returned a collection of values that had a Name and a Type. I needed to use that list to select values from another database using Entity Framework.  A typical result set from the WCF service looked like this:

var list = new List<NameAndType>
{
    new NameAndType { Name = "ABC", Type = "123" },
    new NameAndType { Name = "ABC", Type = "456" },
    new NameAndType { Name = "DEF", Type = "123" },
    new NameAndType { Name = "DEF", Type = "123" }
};

In a local database I had a table that had a composite primary key made up of Name and Type. I needed to write a query that took this list of name/type values and found matching rows in my database. This would be a simple thing to do if I had a single value as my key. If that were the case I could have written a query like this:

var list = List<string>
{
    "ABC",
    "DEF"
};
    
var items = from i in context.Items
            where list.Contains(i.Name)
            select i;

The above query will translate into a statement similar to SELECT * FROM Items WHERE Name IN ('ABC', 'DEF'). The problem is that I had two properties that I needed to predicate on. I wasn't sure how to write the query in Entity Framework but my first thought was something like the following:

var list = new List
{
    new NameAndType { Name = "ABC", Type = "123" },
    new NameAndType { Name = "ABC", Type = "456" },
    new NameAndType { Name = "DEF", Type = "123" },
    new NameAndType { Name = "DEF", Type = "123" }
};

var result = from a in context.Items
             join b in list
             on new { a.Name, a.Type } equals new { b.Name, b.Type }
             select a; 

This query threw an error complaining that only primitive types could be used. I tried a few more queries but I could not figure out a way to do this. I started to think about how I would actually accomplish this in SQL using a stored procedure and settled on using a Table-Valued Parameter (which I have blogged about before). At first I thought I was going to need to write a stored procedure and call that from Entity Framework, but after doing a little testing I was able to come up with a solution that allowed me to do this using an Entity Framework query:

DataTable dt = new DataTable();
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Type"< typeof(string));

list.ForEach(l => dt.Rows.Add(l.Name, l.Type));

SqlParameter tableParameter = new SqlParameter("@Ids", SqlDbType.Structured);
tableParameter.TypeName = "dbo.NameAndType";
tableParameter.Value = dt;

var items = context.Database.SqlQuery<Item>(
    "SELECT * FROM dbo.Items items " +
    "INNER JOIN @Ids ids ON items.Name = ids.Name " +
    "AND items.Type = ids.Type", tableParameter);

The code creates a DataTable and adds a row for each item in my list of NameAndType values. It then creates a SqlParameter with SqlDbType of Structured and gives it a TypeName of dbo.NameAndType and a value of the table. I then created an Entity Framework query using the Database.SqlQuery<T> method which allows you to execute some SQL code. I select from my table and join to my table valued parameter and was able to get the items from my database.

One thing to note with this approach is that all of the items returned from the query will not be tracked by Entity Framework. If you need Entity Framework to track the entities you would have to do something similar to this:

foreach ( var item in items ) {
    db.Items.Attach(item);
}

In Summary

This concludes my post on writing an Entity Framework query that uses a table-valued parameter. If you have any questions, feel free to contact me with any questions.

A SharePoint 2010 environment had experienced a change in how SharePoint would open PDFs that lived in a Document Library. Previously, when someone clicked on a PDF, it would open right away in the web browser for them to view. Now, when someone clicked on a PDF, they would see the following prompt asking them if they would like to open the document in “Read Only” or “Edit” mode:

This behavior was not acceptable, as it was disruptive for people wanting to quickly access the contents of the PDF documents.

Source of the Change

When we tracked down the source of the change, we realized it was due to following Microsoft’s KB on "SharePoint 2010 - Configuring Adobe PDF iFilter 9 for 64-bit platforms.” In step 3, the article says that you need to add the following line to the DOCICON.XML file in the 14 Hive (/TEMPLATE/XML/):

<Mapping Key="pdf" Value="pdf16.gif" />

This step is so that SharePoint will display the PDF icon in Document Libraries and other pages. However, this line is what caused the prompts to start appearing for end users.

Resolution

To stop the prompts from appearing, we needed to override the implicit OpenControl value from the previous change to the DOCICON.XML. We changed the line to the following:

<Mapping Key="pdf" Value="pdf16.gif" OpenControl="" />

After performing an IISReset, end users were no longer seeing prompts when opening SharePoint hosted PDF documents. The PDFs would open right away in their web browser as before.

I recently worked with a client to create a fairly comprehensive solution for implementing Continuous Integration and Delivery for SQL Server Databases using Visual Studio 2010 Database Projects.  I had the opportunity to give a talk on the project at SQL Saturday in Omaha.  The presentation is here if you want the slides.  I think there is some context missing with the slides alone so I wanted to do this post to further explain the solution.

Before talking about the solution, let me describe three different continuous processes.  Continuous Integration (CI) is most familiar and is often used to describe all three of these processes.  I think the differences between these three processes is more clear by using these terms.

Figure 1 – Continuous Processes

Continuous Integration – Verifying code quality by compiling and running unit tests on the build server when a developer checks in changes.  Often abbreviated as CI.

Continuous Delivery – Adds the deployment of the application and database to an isolated test environment where additional integrated and UI automated tests can be run. 

Continuous Deployment – Includes automated deployment of the application through each environment through production.

In this post I will primary review our solution for CI and Continuous Delivery.  This works lays the foundation for the deployment into Staging and Production but I will discuss this in a future post.

Database Projects

Database tools in the past have been different than the tools used application code development.  These database tools have been difficult to implement change management practices and Application Lifecycle Management (ALM) practices.  Today there is an increasingly amount of application developers managing database changes.   These are some of the reasons that have led to need for a tool like Visual Studio Database Projects (DBPro for short).  This tool is part of Visual Studio 2010 (Premium and higher).  To create a Visual Studio Database Project, select SQL Server from the process template menu and then choose SQL Server 2008 Wizard or SQL Server 2008 Database Project.

Figure 2 – SQL Server Database Project Templates in Visual Studio 2010

The primary purpose of the Database Projects are to manage the the version control of database objects in SQL Server databases. The solution we established utilizes this and many of the features of DBPro including TFS Build Integration, Data Generation, Database Unit Testing, Static Code Analysis, and Database and Data Deployments.  In this post I’m not going to cover how to use all of these features but focus on how to implement the features for Continuous Integration, Delivery and Deployment processes.  For more information, please take a look at the Visual Studio ALM Rangers Visual Studio Database Guide.  This solution is complimentary to the guide and goes into more more specifics for CI.

Figure 3 – Visual Studio Database Guide

Challenges

Visual Studio Database Projects are a great tool and I highly recommend teams utilize these for managing version control for the SQL Server Databases.  However, successfully using Database Projects can be challenging.  I believe the benefits greatly out weigh the challenges but it is important for the team to be aware of these for a successful implementation.

Visual Studio – Visual Studio probably seems like an odd challenge considering this is the tool to use for the solution, however Visual Studio is a beast.  Visual Studio has become everything development.  Developers are used to Visual Studio and I have seen DBAs and other database professions get frustrated using it when they first start.  Stay with it.  It will get easier and is the future direction of Microsoft in SQL Server 2012.  From what I have seen SQL Server Management Studio 2012 is based on Visual Studio.

“Truth Center” Shift – Development teams have been used to using a shared database server and making changes directly on server since the stone age.  Managing source control of the database in DBPro essentially changes the “truth center” of the database project to DBPro.  Changes to the schema should be made in DBPro and then executed or deployed from DBPro to the shared server.  Development can also be done in local sandbox called offline schema development where the developer can make the changes locally and check them in.  Changes made directly to the shared SQL Server database risk being overwritten by the next deployment from DBPro.

Permissions – I have found DBPro does a great job managing almost all of the artifacts for databases.  The biggest challenge and frustration has been permissions.  The problem is that the database project holds the specific version of the database.  For permissions this doesn’t work in most real world examples because permissions change in each environment.  For examples, developers need different permissions in development versus what they need in production.  In addition, many enterprises use a separate domain for each environment.  As shown in Figure 4 below, Database Roles for the most part are consistent between environments and primarily the users and their role membership in those roles will vary.  The best method I found for handling these permission differences is to exclude them altogether.  Use the following steps to handle permissions when importing the schema and adding new objects to the project.  One advantage of removing the users is that that they are normally connected to a login and the login lives outside of the database in the Master database.  Including the users and logins in the project requires an additional project called SQL Server Server Project that contains the Master database.  This solution does not require a SQL Server Server Project.

Figure 4 – Managing Permissions Across Environments

Importing Databases

When using the importing the schema and objects into your project, make sure you perform the following steps to first import all of the permissions and the remove those that will change in different events.

• Enable Import permissions in the Import Wizard to import all of the permissions including Roles, Users, and Role Membership.
• After Import has completed:
  • Role permissions are to be kept in the .sqlpermissions file.
  • Schema Users (without login) are to be kept.
  • The other users must be removed
    • from sqlpermissions
    • From Security\Users
    • From RoleMemberships

Adding New Objects

When adding new objects in the Database Project

• Use Schema View
• Manually modify the Properties\Database.sqlpermissions and add the new permission
  • EX: Grant Execute to Role for Stored Procedures EXECUTE TestRole

  <PermissionStatement Action="GRANT">
    <Permission>EXECUTE</Permission>
    <Grantee>TestRole</Grantee>
    <Object Name="spTestFromSSMS2" Schema="dbo" Type="OBJECT" />
    <Grantor>dbo</Grantor>
  </PermissionStatement>

Permission Scripts

By removing the permissions from the project, there needs to be a place to account for these.  This solution accomplishes this by creating a script in the Scripts folder for environment that essentially creates the logins, users, and assigns the role membership for each user.  This allows the flexibility to store any variations between the environments and still store these in the database project and in source control.  Do not set the Build Action to PostDeploy because you can only have one for each project and it will be combined with the Deployment script.   Instead set the “Copy to Output Directory” property on the script to “Copy Always”.  This will create an Scripts folder and the permission files in the build output directory so it can be called by the deployment scripts.

Source Control

The primary benefit for using the Database Projects is that all of the database changes can be managed in source control.  There are a lot of ways to organize your source control and with branching and merging this can become complex to manage.  I like to take a pragmatic approach to source control and keep things simple but allow for complexity if needed in the future.  The Visual Studio TFS Branching Build 2010 is a great reference for adopting branching and merging strategy.  For this post I want to simply show the relationships between Production, Development, and Work Orders.  The main points is that the database projects should be branched and merged along side the application source control with some sort of release branch that has the current production version.  The Work Order branch is for production support changes that will be made into production.  Development teams should do downward merges often to always have any work order changes incorporated early.  When the application and database changes are deployed to production, the development branch should be merged up to the Production branch.  The diagrams below show how this is organized from a logic view and physical view.

Logical View

Figure 5 – Logical View of the Database Project Source Control Branches

Physical view

Figure 6 – Physical View of the Database Project Source Control Branches

Continuous Integration (CI)

To setup the most basic CI process for your Database projects, you can simply add the Solutions containing the database projects to your CI build that is building your application code.  The benefit of this is that it will build your database projects and validate that there are no schema errors and can validate any static code analysis rules.

Continuous Delivery

For Continuous Delivery, we want to expand the process to include deploying the database, insert any test data we need, and then run the database unit tests.  This adds validation that the schema in source control can correctly build the database and that stored procedures can pass any number of validations with the unit tests.  These steps would look like the following:

Figure 7 – Simple Continuous Delivery Process

Visual Studio Database projects make implementing this process very simple and only requires a couple simple settings.  The dialog below shows the out of the box settings.  To open this dialog, select Test > Test Configuration from the menu.   The sections are slightly out of order.  To start we want to set the Deployment database project to the project we want to deploy.  Next choose the configuration.  The configuration settings in the database project will specify the target connection string and other deployment properties.  Next, the Database state setting will generate the test data for the unit tests by running one of the data generation plans.

Figure 8 – Database Test Settings

Types of Continuous Delivery

The example above basically deploys the current version of the schema to the target but doesn’t is not a good practice run into production.   It basically deploys the changes from the last deployment.  My goal of the Continuous Delivery process should be a practice run into production and essentially deploy the application and database the way it will be done for the production deployment.   There are two types of delivery based on whether or not the application is already in production.  For new systems that haven’t been deployed to production, the deployment will be to deploy all of the schema.  This is referred to as Greenfield.  For existing systems, the schema will the difference of what is currently in production with what has been developed.  This is referred to as Brownfield deployments.

From a Visual Studio Database Project standpoint, Greenfield deployments are a simple using the deploy option.  This will drop the database and execute the full schema script to create the database.

Figure 9 – Greenfield Process

For Brownfield deployments in Visual Studio Database Projects, the process is accomplished in two steps using Production and Development versions of the database projects.  The first step is to use the Production version of the Database Project to create the full CREATE script.  Next, use the compare feature to compare the Production and Development versions to create the DELTA script.  Again, the key is not to compare the development against the live production database but to use the version of the Database Project that was created either from the production database or from the Release branch in source control.  Once you have these two database scripts, run the CREATE script to drop the database and create the database to the production level.  Then execute DELTA script to bring it to the current development level.  From there you can follow the similar steps to execute the data generation plan and automated tests to complete.  See below to see how this fits together

Figure 10 – Brownfield Process

Putting this all together, here are the steps in order for a good SQL Server database Continuous Delivery process.  There is some customization that has to be done for this.  The database testing options that were available for the simple process, won’t work out of the box for this solution.  This is because the Database Project doesn’t know about the production and delta scripts.  The build by default would create the database and run the data generation plan before unit tests including the database unit tests.  However, the unit tests are run immediately after the application is built and we need to specify the a step to build create the scripts and then execute them.  I customized the build definition by moving the unit test execution activities to later in the process so I could execute the SQL scripts before the Unit Tests are run.  Once this was moved, I could use the built in features to run the data generation plan.  Below are the steps for the full end to end Database Continuous Delivery process.

Figure 11 – End to End Database Continuous Delivery Process

To combine this process into the application continuous delivery process, the same tasks above can executed along with the application steps.  This process is grouped into three groups: Build/Stage, Deploy, and Execute Automated Tests.  The process is outlined below.

Figure 12 – End to End Application and Database Continuous Delivery Process

VSDBCMD

One of the great features of Visual Studio Database Projects is that the deployment and compare functionality can be executed via a command line utility called VSDBCMD.exe.  This allows us to perform the necessary steps in our Continuous Delivery process.  I utilize a InvokeProcess Activity in the build definition to call a PowerShell script to execute the VSDBCMD commands.  Below are examples of how to create the Production CREATE script and the DELTA script.  The Production Script creates the full CREATE script from the compiled Production version of the Database Project.  The DELTA command shows how to compare two Database Projects to generate the DELTA SQL Script.

Create Production Script

& "C:\program files (x86)\Microsoft Visual Studio 10.0\VSTSDB\Deploy\vsdbcmd.exe" 

/a:deploy /dsp:sql /model:Ecommerce.dbschema /DeploymentScriptFile:c:\temp\OutputFilename2.sql 

/p:TargetDatabase="NewEcommerce"

Create Production and Development Delta Script

& "C:\program files (x86)\Microsoft Visual Studio 10.0\VSTSDB\Deploy\vsdbcmd.exe" 

/a:deploy /dsp:sql /model:Ecommerce.dbschema /DeploymentScriptFile:c:\temp\OutputFilename2.sql 

/targetmodelfile:"C:\tfs\deliveron\Production\EcommerceSolution\Ecommerce\obj\Debug\ecommerce.dbschema" 

/p:TargetDatabase="NewEcommerce"

In Summary

This concludes the overview of the solution for Continuous Integration and Delivery for SQL Server Databases.  I hope it gives you a complete overview for creating your own Continuous Delivery process.  Feel free to contact me if you have any questions or comments.

Review of Key Concepts

• There are three types of Continuous processes: Integration, Delivery, and Deployment.
• Continuous Delivery should be set up to be a practice run for Production.
• Create compare scripts between development and production database projects and don’t compare against live databases.
• VSDBCMD is a command line utility that perform the deployment and compare functionality in Visual Studio Database Projects.

VMware Disk (~20 minutes)

For Workstation 7 and later and Player 3.x and later, you can increase the virtual disk from the GUI:

1. Select the virtual machine from the Inventory.
2. Click Edit Virtual Machine Settings.
3. Click Hard Disk.
4. Click Utilities > Expand, enter the new size, then click Expand.

Reference: http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1004047

Windows 2008 OS (~2 minutes)

How to extend the boot partition in Windows Server 2008

To extend the boot partition in Windows Server 2008, follow these steps:

1. 1. Click Start, and then click Server Manager.
2. 2. In the navigation pane, expand Storage, and then click Disk Management.
3. 3. In the details pane, right-click the volume that you want, and then click Extend Volume.
4. 4. Follow the instructions in the Extend Volume Wizard to extend the boot partition.

Reference: http://support.microsoft.com/kb/325590

I had a scenario where I needed to update the Thesaurus file for a SharePoint Server 2010 install. From what I could tell from the TechNet article on Managing thesaurus files, the file (tsenu.xml – English, United States) should be located in the following directory:

%ProgramFiles%\Microsoft Office Servers\14.0\Data\Applications\<GUID>\Config\

The GUID is supposed to match the GUID of the Search Service Application. However, when I went into the Applications directory, I saw multiple GUID-based folders.

Attempt 1

I went to Central Administration, but I wasn’t seeing a matching GUID when looking at the URLs on the service applications.

Attempt 2

In a SharePoint Management Shell, I tried running Get-SPServiceApplication, however the GUID for the Search Service Application didn’t match any of the folders either.

Attempt 3

In the SharePoint Management Shell, I also tried running Get-SPEnterpriseSearchService, however no luck on the GUIDs there either.

Resolution – Using Process Monitor

I finally decided to brute force the answer. Here are the steps I took to figure out which file SharePoint was looking at:

1. I downloaded and extracted the free Process Monitor utility from Microsoft.
2. I opened the Search Center for a Site Collection.
3. In Process Monitor, I configured it to only watch for file system activity and started logging the activity.
4. I then performed a search in the Search Center.
5. After the search was completed, I stopped Process Monitor.
6. I then saved the log of the file system activity to a CVS file.
7. From within Excel, I filtered the “Path” column to only look for entries that contained the text “tsenu.xml”.
8. I only came up with a single path that matched.
9. I modified the file, entering an example expansion set (see TechNet article).
10. I then restarted the “SharePoint Server Search 14” Windows Service.
11. After the service had restarted, I went back to the Search Center, and was able to successfully search for results based on the expansion set I had put in the thesaurus file.

Last week I had the opportunity to speak at Omaha’s first SQL Saturday.  My talk was on Continuous Integration with SQL Server Databases.  I had a good turnout and some great questions at my session.  Here are the slides from my talk.  I mistakenly mentioned in the talk that column changes would be treated as a Drop and an Add, thus resulting in data loss.  Visual Studio 2010 Database Projects track the changes like this and incorporates the column change into the delta script. 

In my example, I have a Product table with existing data. 

Figure 1 – Product table with data

I renamed the column from NameOfProduct to ProductName

Figure 2 – Rename feature in Database Projects

Figure 3 – Preview Changes Dialog

You can see that the delta script that was generated by the Deploy option in the Visual Studio Database Project is aware of the column name change.  The script calls the sp_rename stored procedure to rename the column name and keep the data intact.

Figure 4 – Rename Column Script

Here are the results of table after the rename. No data loss!

Figure 5 – Product table data after the rename

In my next post I’ll discuss specifics around the CI for SQL Server databases solution.

Enjoy!

If you have created multiple managed metadata services, so that you can have multiple global term stores, you may have noticed that keywords entered into the Enterprise Keywords field are stored in only one of the term stores. Depending on your desired information architecture, you may want new keywords to be stored in a different term store rather than the default. This can easily be defined by managing the properties of the managed metadata service connections.

Steps

1) Log in to Central Administration

2) Under Application Management, click Manage Service applications

3) Find the current Managed Metadata Service where Keywords are being stored, and click the entry for the Service Connection below it:

4) In the Ribbon menu, click Properties

5) On the properties page, uncheck This service application is the default storage location for Keywords

6) Click OK

7) Find the desired Managed Metadata Service for Keywords to be stored in, and click the entry for the Service Connection below it:

8) In the Ribbon menu, click Properties

9) On the properties page, check This service application is the default storage location for Keywords

10) Click OK

All of the new Keywords entered in your environment’s folksonomy will now be stored in the new term store.