Sharepoint 2013 Preview

Sharepoint 2013 is just around the corner, full of new features to take the platform to the next level

Here are some of the new features

• Visual Experience
• Metro Interface
• To brand a SharePoint site, designers just create a site design as they typically would, by implementing HTML, CSS, and JavaScript. Designers can create these files by using their design tool of choice, whether that is Adobe Dreamweaver, Microsoft Expression Web, or some other HTML editor. You don't have to use SharePoint Designer or Visual Studio 2013 to brand a SharePoint site
• App Store
• Everything in Sharepoint 2013 is an App, lists, workflow, site pages, remote web apps, etc.
• Make your apps available to everyone to try and buy through the public Store or only available to your employees through the corporate catalog
• Social Networking
• The introduction of Community Sites offers a forum experience to categorize discussions around subject areas, and connect users who have knowledge or seek knowledge about subject areas
• My Sites for micro-blogging, allows likes, hash tags and news feed functionality following content, people and sites.
• Integration
• Organize all your projects and tasks to get visibility into upcoming deliverables across SharePoint, Outlook and Microsoft Project
• Sync your content in SharePoint to your desktop with SkyDrive Pro
• Development
• Based on common web standards like HTML, CSS, and JavaScript. Furthermore, implementation relies on established protocols like the Open Data protocol (OData), and OAuth
• Support for the newly developed web-based tool "Napa" Office 365 Development Tools for developing apps
• Visual Studio 2012 now includes a new app project template in the Apps folder named Apps for SharePoint 2013
•  In addition to the use of Representational State Transfer (REST) endpoints for web services, there is a broad new API for both server and client development. Remote event receivers and now supported in addition to client-side rendering
• Cloud
•  Improved and enhanced to support the new cloud-based architecture and app-driven development framework
• Full support for development against the Windows Azure Workflow platform
• Content
• Many new features for videos and using videos on pages. A new video content type is added, and the video upload process is improved for content authors
• Content authors can insert an iframe element into an HTML field on a page. This lets content authors embed dynamic content from other sites, such as videos or map directions
•  Support for image renditions. Image renditions let you display different sized versions of an image on different pages
• Cross-site publishing lets you store and maintain content in one or more authoring site collections, and display this content in one or more publishing site collections
• Designate any library or list as a catalog. After the Cross-Site Collection Publishing feature is enabled for a site collection, you can designate any library or list within that site collection as a catalog so that content can be reused on publishing site collections
• Localization
• Built in integrated translation service that lets content authors select content for export for human translation or specify content for machine translation.

These are only a subset of all of the new and improved features and functionality in the Sharepoint 2013 Preview, for more information check out Microsoft's official Sharepoint 2013 sites at

http://technet.microsoft.com/en-us/library/cc303422(v=office.15)
http://msdn.microsoft.com/en-us/library/jj162979(v=office.15)


Download the preview at:
http://technet.microsoft.com/en-US/evalcenter/hh973397

Microsoft System Center Service Manager 2012 Gotchas

I have attempted to install SCSM 2012 unsuccessfully multiple times and learned a few things that I'll share that will hopefully save someone hours of time hunting down solutions for these issues.

The first issue I ran into was during the SCSM Data warehouse management server installation, during this install you will be asked for multiple database names, rather than selecting the default names, I chose to append '2012' to all the database names. The install will fail with 2012 in the database names, I tried this in three separate environments and it failed in all three.

The next issue was the SQL instance name, if you create a new instance besides the default MSSQL and include 'MSSQL' in the name of the new instance, the installation will fail. Tried this in a couple of different environments and always a failed installation.

The last issue is during an upgrade from SCSM 2010 to SCSM 2012, about three quarters of the way through the installation it will attempt to make changes to the SSRS server, if there is a web folder on the site with the name 'Data Sources' the installation will fail. This failure will be a catastrophic failure and require a disaster recovery scenario to rebuild/reinstall. The horrible thing about this issue is that if you have been creating reporting solutions using the SQL Business Intelligence Development Studio, when you publish your projects to the SSRS server I believe it will automatically create the Data Sources Folder. For the upgrade, rename this folder, run the upgrade and the change the name back after it succeeds.

SQL 2008 Native Client Installation Error

I recently worked on a Microsoft System Center Service Manager 2012 upgrade project and on both of the management servers during the prerequisite installation the SQL Server 2008 Native Client installation would fail. After searching around through what seemed like hundreds of sites, I found a solution that works consistently.

Error Message - An error occurred during the installation of assembly 'Microsoft.VC80.CRT...

Export and delete the following registry keys...

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\SideBySide\Winners\x86_policy.8.0.microsoft.vc80 & vc90 entries…

[HKEY_LOCAL_MACHINE\COMPONENTS\DerivedData\Components\x86_policy.8.0.microsoft.vc80 & vc90 entries

[HKEY_LOCAL_MACHINE\COMPONENTS\WINNER\Components\x86_policy.8.0.microsoft.vc80 & vc90 entries.


Note: On my 2008 R2 Server the third set of keys was not present

Then run services.msc and set the "Windows Module Installer" to automatic and Reboot.

The installation will now complete successfully.

Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_100_CI_AS” in the equal to operation

Working in a Microsoft System Center Service Manager 2010 database I had added a couple of new tables for doing Business Hour Calculations ( see SQL Calculate Business Hours post). I added the tables calendar and holiday for the business hour calculations and when I attempted to execute my SQL function for doing the business hour calculation between date ranges I received the error.

After searching and searching I found a simple solution. First a useful query to determine what the collation is for a columns in a table. In my case I performed the query on the calendar table and day_name and day_number were both set incorrectly.


SELECT
    col.name, col.collation_name
FROM 
    sys.columns col
WHERE
    object_id = OBJECT_ID('YourTableName')




This query will return the collation for each column specified, for me it instantly helped my identify the collation conflict and I was able to execute the following query to correct the issue.


ALTER TABLE YourTableName
  ALTER COLUMN OffendingColumn
    VARCHAR(100) COLLATE Latin1_General_CI_AS NOT NULL


After I ran the alter statement, I tried the business hour function again and sure enough it worked perfectly.

SQL Server 2008 - No Administrator Access

In the past I've experienced issues with SQL Server where the domain account or group that I designated for administrator access loses the ability to perform any administrative actions. Strange behavior because it will let the accounts log in to the server but anytime a domain user attempts an action that requires elevated permissions an error is thrown.

One quick solution is to logon to the machine as the local administrator rather than any domain account and use this account to re-add any domain user or group permissions. By default this account is given administrative access on the SQL server.

SQL - Calculate Business Hours Minus Holidays

Calculating Business Hours 

I was recently tasked with generating SSRS reports against a Microsoft System Center Service Manager data warehouse. The reports require that only business hours be used to extract things like how long a service management ticket was worked etc. In my case the service desk is open M-F from 7:30 to 4:30 and no weekends or Federal Holidays. I expected to find a number of good examples with a few searches but nothing came up that fit my needs exactly.

Here's a quick rundown of how I was able to satisfy the reporting requirements.

First - create a new table to store the work week hours including Saturday and Sunday, this table will be used whenever a query is generated to determine how many hours fall within the "open" hours of the service desk.

I started the process with some examples I gathered here - http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74645 I then extended the examples to include Holidays and fix the queries when the start date and end date were on the same day.

Open SQL Management Studio and attach to the database you wish to add this new table to, paste the following SQL code into the query and execute.

CREATE TABLE [dbo].[ttr_calendar] (

[day_number] [varchar] (50) NOT NULL ,

[day_name] [varchar] (50) NULL ,

[begin_time] [datetime] NULL ,

[end_time] [datetime] NULL ,

[duration] [real] NULL 

) ON [PRIMARY]

Next I'll populate the table with the hours of the service desk. Execute the following statement

insert into ttr_calendar

select 1,             'Monday',      '7:30:00 AM',    '4:30:00 PM',   32400 union all

select 2,             'Tuesday',     '7:30:00 AM',    '4:30:00 PM',   32400 union all

select 3,             'Wednesday',   '7:30:00 AM',    '4:30:00 PM',   32400 union all

select 4,             'Thursday',    '7:30:00 AM',    '4:30:00 PM',   32400 union all

select 5,             'Friday',      '7:30:00 AM',    '4:30:00 PM',   32400 union all

select 6,             'Saturday',    '7:30:00 AM',    '4:30:00 PM',   0 union all

select 7,             'Sunday',      '7:30:00 AM',    '4:30:00 PM',   0

For brevity I'll just link the F_Table_Date function that I used in conjunction with the other tables, this code will need to be copied and executed on our SQL instance, this will generate a function that is used in the query.

Using F_Table_Date - http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519&SearchTerms=F_TABLE_DATE

Function F_TABLE_DATE is a multistatement table-valued function that returns a table containing a variety of attributes of all dates from @FIRST_DATE through @LAST_DATE. In short, it’s a calendar table function.

Next I need to create a table to store the Holiday information - Note: This table will have to be manually populated with your holiday information, simply add a title i.e. Christmas, New Years, etc. and the date in the following format 12/25/2012.

USE [DWDataMart] /* <--Your Database Name */

GO

/****** Object:  Table [dbo].[ttr_Holiday]    Script Date: 05/22/2012 14:37:53 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[ttr_Holiday](

[ID] [int] IDENTITY(1,1) NOT NULL,

[Name] [varchar](50) NOT NULL,

[Date] [date] NOT NULL,

 CONSTRAINT [PK_ttr_Holiday] PRIMARY KEY CLUSTERED 

(

[ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

Now that all the pieces are in place I can generate the actual query that will return my business hours minus Holidays.

declare @start_date datetime,

@end_date datetime,

@temp int

select @start_date = '24 May 2012 07:30:00 AM',

@end_date = '29 May 2012 04:30:00 PM'

Select @temp = COUNT(ttr_holiday.date)*9 from ttr_holiday where ttr_holiday.date Between @start_date and @end_date 

select @temp, total_hours = sum(case

when dateadd(day, datediff(day, 0, @start_date), 0) = dateadd(day, datediff(day, 0, @end_date), 0) then

CASE

WHEN CONVERT(VARCHAR(8),@start_date,108) < '07:30:00 AM' and CONVERT(VARCHAR(8),@end_date,108) > '16:30:00'

THEN  

datediff(second, [DATE] + begin_time, [DATE] + end_time)

WHEN CONVERT(VARCHAR(8),@start_date,108) < '07:30:00 AM' and CONVERT(VARCHAR(8),@end_date,108) < '16:30:00'

THEN  

datediff(second, [DATE] + begin_time, @end_date)

WHEN CONVERT(VARCHAR(8),@start_date,108) > '07:30:00 AM' and CONVERT(VARCHAR(8),@end_date,108) < '16:30:00'

THEN  

datediff(second, @start_date, @end_date)

WHEN CONVERT(VARCHAR(8),@start_date,108) > '07:30:00 AM' and CONVERT(VARCHAR(8),@end_date,108) > '16:30:00'

THEN  

datediff(second, @start_date, [DATE] + end_time)

END

when [DATE] = dateadd(day, datediff(day, 0, @start_date), 0) then

case

when @start_date > [DATE] + begin_time then datediff(second, @start_date, [DATE] + end_time)

else duration

end

when [DATE] = dateadd(day, datediff(day, 0, @end_date), 0) then

case

when @end_date <  [DATE] + end_time then datediff(second, [DATE] + begin_time, @end_date)

else duration

end

else duration

end

 ) 

 

/ 60.0 / 60.0 - @temp

from F_TABLE_DATE(@start_date, @end_date) d inner join ttr_calendar c

on d.WEEKDAY_NAME_LONG = c.day_name

This query will also take into account the start date and end date being on the same day, in several examples that I looked at this part would not return the correct results.

I hope this post helps with calculating business hours.

Programmatically Change Sharepoint Web Application IP Address

Recently I was asked about changing a Sharepoint Web Application IP Addresses programatically, is this possible and how will it affect the Sharepoint sites?

I did some testing and it turns out that it is indeed possible to change the IIS Website IP Address without impacting the Sharepoint Web Application(s). For the most part Sharepoint does not care about the IP Address that are assigned to it's Web Application(s), what it cares about is how the IIS sites are mapped to it's Site Collections. The mapping is done via host header in IIS and Alternate Access Mappings in Sharepoint.

Hopefully there are no Sharepoint sites that are accessed using IP Addresses, if so when the IP Address changes, things will break.

Using  Host Headers and Alternate Access Mappings allow access via friendly names, these names are only bound by DNS so as

To create a friendly name like 'Portal' first I'll create a host header in IIS to my Sharepoint site, host headers allow me to have several IIS sites and only require one IP address.

In IIS select the Sharepoint site and click Bindings from the Actions menu, enter a friendly name, in my case 'Portal'

 Next Ill create a new A record on my DNS server so that the name Portal resolves to my Sharepoint Web Applications IP Address.

Next I'll create a Sharepoint Alternate Access Mapping so that Sharepoint knows what to do when it receives a request for this friendly name 'Portal'

Open Sharepoint Central Administration, select Application Management and under Web Applications select configure alternate access mappings.

Select the alternate access mapping collection for your site and select edit public URLs

Next add the url for your friendly name in one of the zone text boxes

After this configuration is complete Sharepoint can be accessed by typing http://portal. Next I want to programmatically change the IP address of my Sharepoint Web Application.

I found a Powershell script to perform this exact task.


$oldIp = "172.16.3.214"
$newIp = "172.16.3.215"


# Get all objects at IIS://Localhost/W3SVC
$iisObjects = new-object `
    System.DirectoryServices.DirectoryEntry("IIS://Localhost/W3SVC")


foreach($site in $iisObjects.psbase.Children)
{
    # Is object a website?
    if($site.psbase.SchemaClassName -eq "IIsWebServer")
    {
    $siteID = $site.psbase.Name


    # Grab bindings and cast to array
    $bindings = [array]$site.psbase.Properties["ServerBindings"].Value


    $hasChanged = $false
    $c = 0


    foreach($binding in $bindings)
    {
    # Only change if IP address is one we're interested in
    if($binding.IndexOf($oldIp) -gt -1)
    {
    $newBinding = $binding.Replace($oldIp, $newIp)
    Write-Output "$siteID: $binding -> $newBinding"


    $bindings[$c] = $newBinding
    $hasChanged = $true
    }
    $c++
    }


    if($hasChanged)
    {
    # Only update if something changed
    $site.psbase.Properties["ServerBindings"].Value = $bindings


    # Comment out this line to simulate updates.
    $site.psbase.CommitChanges()


    Write-Output "Committed change for $siteID"
    Write-Output "========================="
    }
    }
}

Note: Remember DNS will have to be changed first to accommodate the IP Address change.

Provided there are multiple IP Addresses available on the Sharepoint server, this script will look for an old IP Address and update the IIS website to the new IP Address. Again Sharepoint does not care about the IP Address but it only cares about the request coming to it via IIS and matching the name with a Site Collection.

Hopefully this is helpful, it can be useful in fail over scenarios where re-iping at the fail over site is required.