What SQL Server is my SharePoint machine pointed to?

I recently powered on a SharePoint 2016 box that I haven't used in a while, when I powered it on and tried to browse to the default SharePoint site at http://machinename I received the classic .net error:

Server Error in '/' Application.

Runtime Error

I also attempted to open the SharePoint Central Administration page and received the same error.

I opened the web.config for the default SharePoint site at  c:\inetpub\wwwroot\wss\VirtualDirectories\80 and searched for the word 'error' located the xml node...

<customErrors mode="On" /> and changed this setting to 'Off'

Refreshing the http://machinename page now displays the hidden error message...

'This operation can be performed only on a computer that is joined to a server farm by users who have permissions in SQL Server to read from the configuration database. To connect this server to a server farm, use the SharePoint Products Configuration Wizard, located on the Start menu in Microsoft Sharepoint 2016 Products.

Unfortunately, if I attempt to run the SharePoint Products Configuration Wizard it also displays an error message 'Failed to detect if this server is joined to a server farm,,,'

Turns out the SQL server was just turned off but in my environment I have many SharePoint Servers, SQL Servers, Web Servers, etc. I wasn't sure which SQL server this SharePoint machine was pointed to and I only wanted to power on this one SQL Server.

There are probably multiple ways to determine which SQL Server the SharePoint box is pointed to but the way I chose to find out was to just reference a registry key that defined the connection string to the configuration database. The key name is dsn and the Data value is populated with the connection string.

My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\16.O\Secure\ConfigDb

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.

VMWare vCloud Director - Installation and Configuration Part 2

Part 1

Part 3

In this part we will install vShield Manager, create SSL certificates for vCloud Director and install the CENTOS Linux operating system which will host the vCloud Director software. vCloud Director requires a Linux operating system and cannot be installed on Microsoft Windows operating systems.

1. vShield Manager - Download, deploy and configure
2. SSL Certificates - Download Java Development Kit, generate certificates
3. CENTOS - Download, install and configure 
4. vCloud Director - Download install and configure

vShield Manager - Download and deploy the vShield Manager virtual appliance from vmware.com vShield Download

Note: for each vCenter server that you add to the vCloud Director environment you will first need a vShield Manager server configured and available.

Deploy the vShield Manager appliance, from vCenter select File | Deploy OVF Template, browse to the VMware-vShield-Manager-5.0.0-473791.ova file downloaded from vmware.com

After the vShield Manager is deployed, power on the virtual appliance. You will be prompted to login, the username is: admin and the password is: default.

Type en or enable followed by the password default to enter privileged mode. Type setup and complete all of the networking information. Save the configuration and type exit to logout. You can log back into the console and type show interface to view the configuration.

This completes the vShield Manager installation/configuration

SSL Certificates - The SSL certificates can be installed using multiple methods, first the Java Developement Kit contains the keytool required to create the certificates. The JDK can be downloaded and installed on a separate machine and the certificates can be created prior to performing the vCloud Director installation. The second method is to use the vCloud Director machine to generate the certificates, this can be accomplished mid way through the software installation and will require a couple of extra steps.

Method 1

Download and install the Java Software Development Kit  - Download Java Development Kit

Create the certificates using the command line tool, note the directories listed below will vary depending on the version of the JDK that you download.

"C:\Program Files\Java\jdk1.7.0_01\bin\keytool.exe” -keystore certificates.ks -storetype JCEKS -storepass password -genkey -keyalg RSA -alias http

“C:\Program Files\Java\jdk1.7.0_01\bin\keytool.exe” -keystore certificates.ks -storetype JCEKS -storepass password -genkey -keyalg RSA -alias consoleproxy

“C:\Program Files\Java\jdk1.7.0_01\bin\keytool.exe” -storetype JCEKS -storepass password -keystore certificates.ks -list

Note: The certificates will be created and stored in a file named certificates.ks located in the C:\Program Files\Java\jdk1.7.0_01\bin directory (or alternate version directory). The certificates created in this example all have the password: password. Make sure to copy these files to a shared location, they will need to be copied to the CENTOS machine before the vCloud Director installation can be completed

CENTOS Installation / Configuration - I chose to download the latest CENTOS v 6.0 x64 version.

Create a new virtual machine

Provide a name for the virtual machine

Select an operating system for the new machine

Add one additional Network Interface Card for a total of 2 to the new virtual machine.

After the virtual machine has been created, attach the CD drive of the virtual machine to the ISO file (CentOS-6.0-x86_64-bin-DVD1.iso) downloaded from vmware.com

Make sure that during the installation of CENTOS you configure the two network adapters with 2 unique ip addresses on the same subnet.

Note: make sure that the network cards are configured to connect automatically and available to all users.

I chose web server from the available package group options during the installation, I also chose customize now and selected several of the available options. Unfortunately I don't know the CENTOS very well and I can't list which options to select to install the tools necessary to successfully build a host for vCloud Director so choose most of the available options. I did not include the ones that made no sense such as the main frame, san, databases, graphics, etc.

The important thing to include is the security package, without the firewall being installed and disabled, I was not able to remotely access the vCloud Director configuration page. 

If after you complete the installation and you power on the virtual machine you see no firewall, you can manually add this be entering the following command at the command line.

yum install system-config-securitylevel

After the installation is complete and rebooted, log in to the machine as root. Open the firewall configuration by selecting System | Administration | Firewall and choose disable.

Map a network drive to the shared location where your certificates.ks file is located.

Open Computer and select File | Connect to Server

Copy the certificates.ks file to the CENTOS desktop, repeat these procedures for the vCloud Director software - vmware-vcloud-director-1.5.0-464915.bin file. Copy the vCloud Director sofware into the root's Home folder on the desktop.

Note: you must change the permissions on the vmware-vcloud-director-1.5.0-464915.bin file by right clicking on the file and selecting the permissions tab and check the Allow executing file as a program checkbox.

vCloud Director

Select Applications | System Tools | Terminal and enter ls -al at the command prompt, this will list all of the available files. type ./vmware-vcloud-director-1.5.0-464915.bin and press enter. This will begin the vCloud Director installation process.

The following prompts will be displayed

Run the installer - type “y”

If you chose Method 1 for creating the SSL certificates earlier in the tutorial, make sure you copy the certificates.ks file into the /opt/vmware/vcloud-director directory now and then you can come back to this prompt and answer "y" to the Run the configuration script and skip the certificates creation step, otherwise type “n”

Method 2 for creating SSL Certificates

Create self signed certificates, from the terminal window type the following commands:  

/opt/vmware/vcloud-director/jre/bin/keytool -keystore certificates.ks -storetype JCEKS -storepass password -genkey -keyalg RSA -alias http -dname “cn=vcloud, ou=vmware, o=vmware, c=US” -keypass password

/opt/vmware/vcloud-director/jre/bin/keytool -keystore certificates.ks -storetype JCEKS -storepass password -genkey -keyalg RSA -alias consoleproxy -dname “cn=vcloud, ou=vmware, o=vmware, c=US” -keypass password

A file named “certificates.ks” will be created in the root's Home folder, copy this file to the /opt/vmware/vcloud-director directory. If the file is located in this directory the installer will not need a path specified.

Continue the installation process by typing /opt/vmware/vcloud-director/bin/configure

• Select your first IP address, this will be the IP address which is used for vCD web page or press enter to accept the default 
• Select your second IP address, this will be the IP address which is used for the VM Remote Console or press enter to accept the default 
• Because we copied the certificates.ks file into the vcloud-directory already just type certificates.ks 
• Enter the password: password Press enter to skip the “syslog server”
• Select Oracle or SQL Server 
• Enter the machine name or IP address for the database server 
• Press enter to use default database port (1521) Oracle or (1433) SQL 
• Type the database name, press enter for default (vcloud) 
• Type the database username 
• Type the database password 
• Now the database will be initialized and the vCD install will be completed 
• Type “y” to start the vCD service

You can monitor the progress of the vCD service start up as follows 

type: tail -f /opt/vmware/cloud-director/log/cell.log

This is an important step to verify your installation has completed successfully.

It will show you the percentage of the initialization of the application that has completed. If everything installs correctly you will see a message “Application Initialization: Complete.

Note: I had a few failed attempts where the application initialization would hang, ussually around 18%. I found that if for whatever reason you have to run the installation again, you must delete the old database from your database server before you re-run the install.

I also had to had to start over rebuilding the CENTOS and re-running the installation a couple of times before I figured out some of these gotchas. After the installation is complete, you should be able to launch a browser and enter the vCloud Director IP Address (the first one assigned) to access the vCloud Director configuration page.

This completes Part 2

Part 1

Part 3

Automated Sharepoint 2010 Install - Auto SPInstaller

I know this tool has been around for a while but I just stumbled onto it, for a free utility you can't beat the functionality.

http://autospinstaller.codeplex.com/

This tool will greatly simplify the database naming mess that Sharepoint leaves you with after an install, here's a screenshot of the default database names after a typical install...

And here's what the database names will look like after using AutoSPInstaller...

Much more manageable and far less confusing, no GUIDS in the naming conventions and short DB names.