Create and Configure SQL Server Alias for SharePoint Installation

• What is SQL Server Instance and SQL Server Alias?
• Why we are using SQL Server Instance and SQL Server Alias?
• How to create and configure them?
• I will try to solve above questions. Let’s Start,

Client applications connect to an instance of Microsoft SQL Server to work with a SQL Server database.

When you are going to install SQL Server, you have to select options for SQL Server Instance:
An instance is either the default, unnamed instance, or it is a named instance.

SQL Server Default Instance

When SQL Server is installed in the default instance, it does not require a client to specify the name of the instance to make a connection. The client only has to know the server name.
MY-MACHINE-NAME /* unnamed version (default instance) */

SQL Server Named Instance

A named instance is identified by the network name of the computer plus the instance name that you specify during installation. The client must specify both the server name and the instance name when connecting.
MY-MACHINE-NAME\MSSQLSERVER /* named version */

How to make decision which to do:

• You can install only one default instance but can install multiple named instances. It is good to install only a default instance unless you have a special need to install multiple SQL Server instances on the same host, such as running different versions concurrently or other isolation requirements. These additional instances must be named.
• The underlying difference between a default and named instance is mostly a matter of network connectivity. Clients can connect to the default instance using only the host name over the well-known 1433 port. To connect to a named instance, clients specify the host and instance name (e.g. ” MY-MACHINE-NAME\MSSQLSERVER”) and the SQL Server Browser service returns the port the named instance is listening on.

Now Let go on SQL Server Alias:

A SQL Server alias is simply a friendly name or nick name, configured on the client computer that points at a SQL Server instance. This instance can either be installed locally or on a different machine on the network.

So if my SQL Server Alias name is “DevelopmentDB”,
Then you can say for SQL Server Named Instance, “MY-MACHINE-NAME\MSSQLSERVER” = “DevelopmentDB”

Note: For that we need to configure the SQL Server Alias on the SQL Server, after that we can use the SQL Alias name instead of the SQL Server Instance name.

SQL Server Alias Creation Steps:
Step 1: Run the SQL Server Configuration Manager, Programs -> Microsoft SQL Server 2012 -> Configuration Tools

SQL Server Configuration Manager

Step 2: Verify the SQL Server (MSSQLSERVER) Service is running under the SQL Server Service option.

Step 3: Verify the TCP/IP Protocol is enabled for the SQL Native Client 11.0 Configuration (32bit) and SQL Native Client 11.0 Configuration tree.

SQL Server Configuration Manager

Step 4: Create the new alias, Right click and select new alias under Aliases of SQL Native Client 11.0 Configuration (32bit) and tree.
In New Alias Creation Dialog,
• You have to set the protocol to TCP/IP,
• Port no, by default SQL Server uses 1433 port number. You can also specify other port number as well.
• Server name: SQL Server Instance Name as discussed above (default Instance or named instance)
• I have default Instance named “MyDBServer”.
• New alias name is “DevelopmentDB”
So Now, “DevelopmentDB” alias uses “MyDBServer”, port no “1433” and “TCP/IP” protocol to connect to the SQL Server Instance over the network

SQL Server Alias – 32bit

SQL Native Client 11.0 Configuration-32bit(a)

SQL Server Alias

SQL Native Client 11.0 Configuration(b)

Step 5: We have completed the create alias process. For the verification open SQL Server Management Studio and try to connect with SQL Server using newly created alias name “DevelopmentDB”. If everything configuration is fine, then it will connect to SQL Server:

SQL Server Management Studio

This is the scenario where you have one machine or server and you have successfully created SQL Server Alias.

One more beneficial use of SQL Server Alias is for the multiple servers’ environment like SharePoint Multiple Server Farm.
Assume, we have two servers

• Database Server: MyDBServer
• SharePoint Server: MySPServer

SQL Server is not installed on “MySPServer” server. But still we can use the SQL Server Instance of the “MyDBServer” using the alias configuration on “MySPServer” sever
Below Configuration, We have to do on the SharePoint Server “MySPServer” to connect to the SQL Server of “MyDBServer”.
We have already created alias named ”DevelopmentDB” on the server “MyDBServer”.

Now we have to configure SQL Server alias on the “MySPServer” server as below:
Step 1: If you haven’t installed the SQL Server 2012 client tools, you can still create an alias using the SQL Server Client Network Utility. To bring up the utility, click on Start, then run, and run cliconfg.exe.
Or you can open it form C:\Windows\System32\cliconfg.exe

Cliconfg

Step 2: Below view of SQL Server Client Network Utility, Enable the TCP/IP protocol from the General Tab,

SQL Server Client Network Utility

Step 3: Go to the Alias Tab to create a new alias, click on the Add… button.
“Add Network Library Configuration” view,
Server Alias is what the application will attempt to connect. I have already created that alias on SQL Server named “MyDevelopmentDB”
Server Name: Real Server Name, in our case server name is “MyDBServer”
Port Number: 1433 (Configured as before)
Click on apply and OK to create new SQL Server Alias on “MySPServer’ server

Network Library Configuration

One more thing we need to take care is about the Port Number.

Open the Port for Inbound Connection if Enable Firewall

When we configure the port number for SQL Server Alias Name then server does not allow the out side server connection on this port because of the Firewall Settings.
So our next step is open the custom port for inbound connection if firewall is enabled.
Create a new Inbound rule for allowing the connection for alias port (in my case it is 1433)

Step 1: Open Windows Firewall with Advance Security from the Control Pannel. Click on the“New Rule” of Inbound Rules Tab. Select the Rule Type as “Port”. Click Next.

Firewall Settings

Step 2: Domain Rule apply for To TCP and Provide the specific port number (1433). Click Next.

Firewall Setting for Port

Step 3: Apply the Rule on the Profile, Click Next and allow the connection.

Firewall Setting for Port

Firewall Setting for Port

Step 4: Provide the name of the inbound rule, Click Finish.

Firewall Setting for Port

Now while creating the new farm, provides the SQL Client Alias named “DevelopmentDB” instead of the SQL Server Instance name of “MyDBServer”. So All database for the SharePoint Configuration will be created on the SQL Server of the “MyDBServer”

Let me know if you have any suggestions or questions.

Thank you…

Get site collection owners for each site collection in SharePoint farm

This script gets list of site collection owners for each site collection for every web application within sharepoint farm and saves them to csv file for audit purposes. Also owners can be used to get maintenance and other approvals.

This script gets list of site collection owners for each site collection for every web application within sharepoint farm and saves them to csv file for audit purposes.  Also owners can be used to get maintenance and other approvals.

This csv file can be opened in Microsoft Excel for better viewing. Below is one sample output generated by script:

Assumptions:

1. This script needs to run either with farm admin privileges or a service account which has read access for every web application in the farm. Also site collections with lock state set as no access, will not be included in the script output.

2. This csv file is generated in the current working directory as per powershell prompt. If you need to save the output to some other directory, modify the value of $FileLocation variable in the script.

As a part of best practices, pls run this script in your dev/qa environment first to get familiar with script output. Here's the code:

Power Shell

############################################################################### 
# This script gets list of administrators for each site collection within  
# sharepoint farm and saves output in tab separated format (.csv) file.  
############################################################################### 
 
#Set file location for saving information. We'll create a tab separated file. 
$FileLocation = "SiteCollectionOwnersReport.csv" 
 
#Load SharePoint snap-in 
Add-PSSnapin Microsoft.SharePoint.PowerShell 
 
#Fetches webapplications in the farm 
$WebApplications = Get-SPWebApplication -IncludeCentralAdministration 
Write-Output "URL `t ID `t Site Collection Owner `t Site Collection Owner Email `t Site Collection Secondary Owner `t Site Collection Secondary Owner Email " | Out-file $FileLocation 
 
foreach($WebApplication in $WebApplications){ 
    #Fetches site collections list within sharepoint webapplication 
    Write-Output "" 
    Write-Output "Working on web application $($WebApplication.Url)" 
    $Sites = Get-SPSite -WebApplication $WebApplication -Limit All     
 
    foreach($Site in $Sites){      
            #Fetches information for each  site 
            Write-Output "$($Site.Url) `t $($Site.ID.Guid) `t $($Site.Owner.Name) `t $($Site.Owner.Email) `t $($Site.SecondaryContact.Name) `t $($Site.SecondaryContact.Email)" | Out-File $FileLocation -Append 
            $Site.Dispose() 
    } 
} 
 
#Unload SharePoint snap-in 
Remove-PSSnapin Microsoft.SharePoint.PowerShell 
 
Write-Output "" 
Write-Output "Script Execution finished" 
     
############################################################################## 
## End of Script 
##############################################################################

 

Automating site creation

For an automatic site creation we have the ability to use a lot of tools, like SharePoint Self-Service Site Creation, Workflows, Nintex, PowerShell and a lot of other tools. Also you can able to create your own power-shell script using below steps

Summary:

1. Create a list
2. Create a script
3. Add the script to the task scheduler
4. Requested site is created.

Steps for automating site creating with a SharePoint list and Power-shell:

1. Create a list where users can add there site request. The list has to have a least to following fields:
   1. Title (Text)
   2. Status (Choice: Requested, Approved, Created).
   3. URL (Hyperlink)
      
      The users have the ability to add a new item in this list. You can create a workflow to approve the requests and update the status field by the workflow.
2. Create a PowerShell script “siteCreation.ps1”. The script will create a new web for all items in the Teamsites list with status Approved.

   if((Get-PSSnapin | Where {$_.Name -eq "Microsoft.SharePoint.PowerShell"}) -eq $null) {
    Add-PSSnapin Microsoft.SharePoint.PowerShell;
   }
   
   $NewSiteURL = "http://portal/teams/"
   $sourceWebURL = "http://portal/site/"
   $sourceListName = "Teamsites"
   
   $spSourceWeb = Get-SPWeb $sourceWebURL
   $spSourceList = $spSourceWeb.Lists[$sourceListName]
   $spSourceItems = $spSourceList.Items | where {$_['Status'] -eq "Approved"}
   $list = $spSourceWeb.Lists[$sourceListName]
   $spSourceItems | ForEach-Object {
    $Title = $_['Title']
    $url = $NewSiteURL + "/" + $Title
    Write-Host "Create site: $Url"
    New-SPWeb –url $url -name $Title -template STS#0 
    $_['Status'] = "Created"
    $_['URL'] = $url + ", " + $Title 
    $_.Update()
   }

3. Create a task that will run every hour and start the site creation script.

   $A = New-ScheduledTaskAction -execute "powershell" -argument "-nologo -noprofile -noninteractive E:\[location of script]\siteCreation.ps1"
   $T = New-ScheduledTaskTrigger -Once -At 17:00PM 
   $T.RepetitionInterval = (New-TimeSpan -Minutes 60)
   $T.RepetitionDuration = ([Timespan]::MaxValue)
   Register-ScheduledTask -TaskName "SharePoint site creation" -Trigger $T -Action $A -description "Run site creation script every hour." -User "$env:USERDOMAIN\$env:USERNAME" -Password 'p@ssword' -RunLevel 1

4. The script will create a new web for all the requested sites in the Teamsites list with status Approved. After the site creation the status of the item will be modified to Created and the URL to the web is filled in.