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.