Create and Configure SQL Server Alias for SharePoint Installation

Hello friends, today I will let you know about the SQL Server Instance and SQL Server Alias creation and configuration. When I saw first time, I was confused about

  • 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

SQL Server Configuration Manager

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

SQL2

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

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 Server Alias – 32bit

SQL Native Client 11.0 Configuration-32bit(a)

SQL Server Alias

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

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

Clicconfg

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

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

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

Firewall Settings

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

Firewall Setting for Port

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

Firewall Setting for Port

Firewall Setting for Port

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

Firewall Setting for Port

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…

 

All In One Class For ADO.NET

CRUD Operations using ADO.NET with SQL Server 2008 are mostly used in the .NET.

In the beginning, I was little confused about how and where to make connection, how many connections to open and how to manage all connection with SQL Server database.

And one more question was that which method of ADO.NET, I need to access to get optimum result from the SQL Server. So I can retrieve optimum result and manipulate it as per the need.

So I collected all the possible methods and got the differences and implemented the common class which provide support for all basic operation from open SQL connection, execution of query, receiving the result and close SQL connection.

Each method has its own purpose. like

Method Description
public override Object ExecuteScalar() Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.
public override int ExecuteNonQuery() Executes a Transact-SQL statement against the connection and returns the number of rows affected.
public virtual int Fill(DataSet dataSet) The Fill method retrieves rows from the data source using the SELECT statement specified by an associated Select Command property.

I have implemented one common class. So we can directly call the right method just passing the required parameters.

//------------DBFunctions.cs file----------------------------
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Data.SqlClient;

public class DBFunctions
{
public static string CONNSTR =
ConfigurationManager.ConnectionStrings["DBCNSTR"].ConnectionString;

public DBFunctions()
{
}

public static object
GetScalarParameterizedDB(string sql, SqlParameter[] pArr)
{
   SqlConnection conn = new SqlConnection(CONNSTR);
   object obj = null;
   try
   {
       conn.Open();
       SqlCommand cmd = new SqlCommand(sql, conn);
       for (int i = 0; i < pArr.Length; i++)
       cmd.Parameters.Add(pArr[i]);
       obj = cmd.ExecuteScalar();
       conn.Close();
   }
   catch (Exception ex)
   {
       throw ex;
   }
   finally
   {
       conn.Close(); // very important with databases
   }
return obj;
}

public static object GetScalarDB(string sql)
{
   SqlConnection conn = new SqlConnection(CONNSTR);
   object obj = null;
   try
   {
      conn.Open();
      SqlCommand cmd = new SqlCommand(sql, conn);
      obj = cmd.ExecuteScalar();
      conn.Close();

   }
   catch (Exception ex)
   {
      throw ex;
   }
   finally
   {
      conn.Close(); // very important with databases
   }
return obj;

}

public static int GetNonQueryDB(string sql)
{
   SqlConnection conn = new SqlConnection(CONNSTR);
   int rows = 0;
   try
   {
      conn.Open();
      SqlCommand cmd = new SqlCommand(sql, conn);
      rows = cmd.ExecuteNonQuery();
      conn.Close();
   }
   catch (Exception ex)
   {
      throw ex;
   }
   finally
   {
      conn.Close(); // very important with databases
   }
return rows;
}

public static DataSet GetDataSetDB(string sql)
 {
   SqlConnection conn = new SqlConnection(CONNSTR);
   DataSet ds = null;
   try
   {
      conn.Open();
      SqlDataAdapter da = new SqlDataAdapter(sql, conn);
      ds = new DataSet();
      da.Fill(ds);
      conn.Close();
   }
   catch (Exception ex)
   {
   throw ex;
   }
   finally
   {
      conn.Close(); // very important with databases
   }
   return ds;
 }
}

Thanks...

Effectively Implemented SingleTon Architecture For REST API

Many Software developers know about the various design patterns.

As per the Software Engineering, a design pattern is a general reusable solution to a commonly occurring problem within a given context in software design.

Only knowing the design patterns is not enough. But when you are going to think and apply to your solution, then you are on the way to become a Solution Architect.

I have applied one pattern on one of my WCF REST API Service Application. Named as the SingleTon.

I just have limited concepts knowledge of limited Patterns.

One day, I was implementing WCF REST API Service Application for the Mobile Developer.

REST API Service also perform the Authentication. Now this need some clarification.

How to maintain the user credentials and authentication token on the web server?

One possible solution is to create SQL Server Database which stores the credentials information and update accordingly.

But it will decrease the performance of method execution because each time it has to verify the token with SQL Server Database.

One more thing in our project is that, We don’t have any SQL Server database.

So need some different Simple Approach. At that time, I have some little knowledge about SingleTon Pattern. Like,

A Singleton is a class which only allows a single instance of itself to be created, and usually gives simple access to that instance.

Structure :

using System;
public class Singleton
{
   private static Singleton instance;
   private Singleton() {}
   public static Singleton Instance
   {
      get 
      {
         if (instance == null)
         {
            instance = new Singleton();
         }
         return instance;
      }
   }
}

If the instance is already created then, it will not allow to create instance and it is also static so we can use that instance at the class level. So If I can store authentication details in that SingleTon instance,then I can also manipulate that instance and maintain authentication information
Now I have tried to associate my authentication requirement to SingleTon Pattern.Anyhow,I need to store authenticated user’s details at the web-server. So I can verify the token.

Now, Its time for implementation,I have created one user credential class

public class UserCredential
{
  public string UserName { get; set; }
  public string Password { get; set; }
  public string Token { get; set; }
  public string Domain { get; set; }
}

And one SingleTon class, which manage the list of UserCredential Information.

public class Singleton
{
  private static Singleton instance;
  public static List<UserCredential> userCredentialList { get; set; }
  private Singleton() { }
  public static Singleton Instance
  {
    get
    {
      if (instance == null)
      {
        instance = new Singleton();
      }
      if (userCredentialList == null)
      {
        userCredentialList = new List<UserCredential>();
      }
      return instance;
    }
  }
  public List<UserCredential> AddCredential(UserCredential userCredential)
  {
    userCredentialList.Add(userCredential);
    return userCredentialList;
  }
}

SingleTon class object, only create one instance of List of User Credentials, and I have to maintain this object using another method called Add Credential, As described above.

So I am not able to create new instance of SinlgeTon, But I can maintain the SingleTon instance with the list of user credentials details. Its static, So it can be accessible by each API method.

For Authentication, and storing the user credential for authenticated user, I have implemented below API method.

public string Authentication(UserCredential userData)
{
  string token = string.Empty;
  AuthenticationResponse authResponse = new AuthenticationResponse();
  try
  {
  if (!string.IsNullOrEmpty(userData.UserName) && !string.IsNullOrEmpty(userData.Password))
  {
    string userName = userData.UserName;
    string password = userData.Password;
    string domain = userData.Domain;
    Singleton singleTon = Singleton.Instance;
    List<UserCredential> existingUserCredentialList = Singleton.userCredentialList;
    int removedUser = existingUserCredentialList.RemoveAll((user) => (user.UserName == userName && user.Domain == domain));

    service = AuthService.ConnectToServiceForAuthentication(userInfo, new TraceListener());
    UserCredential userCredential = new UserCredential();
    if (service != null)
    {
      userCredential.UserName = userName;
      userCredential.Password = password;
      userCredential.Domain = domain;
      userCredential.Token = Guid.NewGuid().ToString();
           singleTon.AddCredential(userCredential);
      token = userCredential.Token;
    }
   }
  }
  catch (Exception ex)
  {
    Logger.Write("Service.Authentication :" + ex.Message + ", " + ex.GetType());
  }
  return token;
}

Above code authenticate the user on Exchange Server. If user is valid, then I store his credentials to the singleton instance with generated token and also return the token for further method calls. So If the user will come again, So he/she has to only need to pass the token. SingleTon instance will only disposed, When we perform the web-server restart or server crash.

At the Web server side, perform the token verification from the above generated singleton instance and return the user credential.

Verification Method :

public static UserCredential VerifyToken(string token)
{
  UserCredential uc = new UserCredential();
  UserCredential userCredential = null;
  try
  {
    Singleton singleTon = Singleton.Instance;
    List<UserCredential> existingUserCredentialList = Singleton.userCredentialList;
    userCredential = existingUserCredentialList.SingleOrDefault((user) => user.Token == token);
  }
  catch (Exception ex)
  {
    Logger.Write("Service.VerifyToken :" + ex.Message + ", " + ex.GetType());
  }
  return userCredential;
}

We can also implement the Log-out method by just removing the user credentials from the SingleTon Instance of List of User Credentials.
We are not dealing with SQL Server Database and we are maintaining the information on the web-server only. So obviously, its faster.

I have just showed the example of just storing the credentials, But you can also think some out of bound scenarios and try to implement it.

Thanks.

Any Suggestions are appreciated.