{"id":1739,"date":"2014-08-13T16:18:17","date_gmt":"2014-08-13T16:18:17","guid":{"rendered":"http:\/\/www.obieta.com\/?p=1739"},"modified":"2014-08-13T16:18:17","modified_gmt":"2014-08-13T16:18:17","slug":"1739","status":"publish","type":"post","link":"http:\/\/obieta.com\/?p=1739","title":{"rendered":"Create and Configure SQL Server Instance and Alias for SharePoint Installation"},"content":{"rendered":"<h1><span style=\"font-size: 16px;\">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<\/span><\/h1>\n<div>\n<ul>\n<li>What is SQL Server Instance and SQL Server Alias?<\/li>\n<li>Why we are using SQL Server Instance and SQL Server Alias?<\/li>\n<li>How to create and configure them?<\/li>\n<li>I will try to solve above questions. Let\u2019s Start,<\/li>\n<\/ul>\n<p>Client applications connect to an instance of Microsoft SQL Server to work with a SQL Server database.<\/p>\n<p>When you are going to install SQL Server, you have to select options for\u00a0<strong>SQL Server Instance<\/strong>:<br \/>An instance is either the\u00a0<strong>default, unnamed instance<\/strong>, or it is a\u00a0<strong>named instance.<\/strong><\/p>\n<p><strong>SQL Server Default Instance<\/strong><\/p>\n<p>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.<br \/>MY-MACHINE-NAME \/* unnamed version (default instance) *\/<\/p>\n<p><strong>SQL Server Named Instance<\/strong><\/p>\n<p>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.<br \/>MY-MACHINE-NAMEMSSQLSERVER \/* named version *\/<\/p>\n<p><strong>How to make decision which to do:<\/strong><\/p>\n<ul>\n<li>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.<\/li>\n<li>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. \u201d MY-MACHINE-NAMEMSSQLSERVER\u201d) and the SQL Server Browser service returns the port the named instance is listening on.<\/li>\n<\/ul>\n<p><strong>Now Let go on SQL Server Alias:<\/strong><\/p>\n<p>A\u00a0<strong>SQL Server alias<\/strong> 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.<\/p>\n<p>So if my\u00a0<strong>SQL Server Alias name<\/strong> is \u201c<strong>DevelopmentDB<\/strong>\u201d,<br \/>Then you can say for SQL Server Named Instance, \u201cMY-MACHINE-NAMEMSSQLSERVER\u201d = \u201cDevelopmentDB\u201d<\/p>\n<p><strong>Note: <\/strong>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.<\/p>\n<p><strong>SQL Server Alias Creation Steps:<\/strong><br \/><strong>Step 1:<\/strong> Run the SQL Server Configuration Manager, Programs -&gt; Microsoft SQL Server 2012 -&gt; Configuration Tools<\/p>\n<p><a href=\"http:\/\/codecreature.files.wordpress.com\/2014\/08\/sql1.jpg\"><img src=\"http:\/\/codecreature.files.wordpress.com\/2014\/08\/sql1.jpg?w=660\" alt=\"SQL1\" \/><\/a><\/p>\n<p><strong>Step 2:<\/strong> Verify the\u00a0<strong>SQL Server (MSSQLSERVER) Service<\/strong> is running under the\u00a0<strong>SQL Server Service<\/strong> option.<\/p>\n<p><a href=\"http:\/\/codecreature.files.wordpress.com\/2014\/08\/sql2-e1407752121856.jpg\"><img src=\"http:\/\/codecreature.files.wordpress.com\/2014\/08\/sql2-e1407752121856.jpg?w=660\" alt=\"SQL2\" \/><\/a><\/p>\n<p><strong>Step 3:<\/strong> Verify the\u00a0<strong>TCP\/IP Protocol is enabled<\/strong> for the SQL Native Client 11.0 Configuration (32bit) and SQL Native Client 11.0 Configuration tree.<\/p>\n<p><a href=\"http:\/\/codecreature.files.wordpress.com\/2014\/08\/sql6.jpg\"><img loading=\"lazy\" src=\"http:\/\/codecreature.files.wordpress.com\/2014\/08\/sql6.jpg?w=660&amp;h=246\" alt=\"SQL6\" width=\"660\" height=\"246\" \/><\/a><\/p>\n<p><strong>Step 4:<\/strong> Create the\u00a0<strong>new alias<\/strong>, Right click and select new alias under Aliases of SQL Native Client 11.0 Configuration (32bit) and tree.<br \/>In New Alias Creation Dialog,<br \/>\u2022 You have to\u00a0<strong>set the protocol<\/strong> to TCP\/IP,<br \/>\u2022\u00a0<strong>Port no<\/strong>, by default SQL Server uses 1433 port number. You can also specify other port number as well.<br \/>\u2022\u00a0<strong>Server name<\/strong>: SQL Server Instance Name as discussed above (default Instance or named instance)<br \/>\u2022 I have default Instance named \u201cMyDBServer\u201d.<br \/>\u2022 New\u00a0<strong>alias name<\/strong> is \u201cDevelopmentDB\u201d<br \/>So Now, \u201cDevelopmentDB\u201d alias uses \u201cMyDBServer\u201d, port no \u201c1433\u201d and \u201cTCP\/IP\u201d protocol to connect to the SQL Server Instance over the network<\/p>\n<p><a href=\"https:\/\/codecreature.files.wordpress.com\/2014\/08\/sql5.jpg\"><img loading=\"lazy\" src=\"http:\/\/codecreature.files.wordpress.com\/2014\/08\/sql5.jpg?w=660&amp;h=372\" alt=\"SQL5\" width=\"660\" height=\"372\" \/><\/a><\/p>\n<p>SQL Native Client 11.0 Configuration-32bit(a)<\/p>\n<p><a href=\"https:\/\/codecreature.files.wordpress.com\/2014\/08\/sql4.jpg\"><img loading=\"lazy\" src=\"http:\/\/codecreature.files.wordpress.com\/2014\/08\/sql4.jpg?w=660&amp;h=380\" alt=\"SQL4\" width=\"660\" height=\"380\" \/><\/a><\/p>\n<p>SQL Native Client 11.0 Configuration(b)<\/p>\n<p><strong>Step 5:<\/strong> 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 \u201cDevelopmentDB\u201d. If everything configuration is fine, then it will connect to SQL Server:<\/p>\n<p><a href=\"https:\/\/codecreature.files.wordpress.com\/2014\/08\/sql7.jpg\"><img loading=\"lazy\" src=\"http:\/\/codecreature.files.wordpress.com\/2014\/08\/sql7.jpg?w=660&amp;h=349\" alt=\"SQL7\" width=\"660\" height=\"349\" \/><\/a><\/p>\n<p>This is the scenario where you have\u00a0<strong>one machine or server<\/strong> and you have successfully created SQL Server Alias.<\/p>\n<p>One more\u00a0<strong>beneficial use of SQL Server Alias<\/strong> is for the multiple servers\u2019 environment like SharePoint Multiple Server Farm.<br \/>Assume, we have two servers<\/p>\n<ul>\n<li><strong>Database Server<\/strong>: MyDBServer<\/li>\n<li><strong>SharePoint Server<\/strong>: MySPServer<\/li>\n<\/ul>\n<p>SQL Server is not installed on \u201cMySPServer\u201d server. But still we can use the SQL Server Instance of the \u201cMyDBServer\u201d using the alias configuration on \u201cMySPServer\u201d sever<br \/>Below Configuration, We have to do on the SharePoint Server \u201cMySPServer\u201d to connect to the SQL Server of \u201cMyDBServer\u201d.<br \/>We have already created alias named \u201dDevelopmentDB\u201d on the server \u201cMyDBServer\u201d.<\/p>\n<p>Now we have to\u00a0<strong>configure SQL Server alias on the \u201cMySPServer\u201d server<\/strong> as below:<br \/><strong>Step 1:<\/strong> If you haven\u2019t installed the SQL Server 2012 client tools, you can still create an alias using the\u00a0<strong>SQL Server Client Network Utility<\/strong>. To bring up the utility,\u00a0<strong>click on Start, then run, and run cliconfg.exe<\/strong>.<br \/>Or you can open it form\u00a0<strong>C:WindowsSystem32cliconfg.exe<\/strong><\/p>\n<p><a href=\"http:\/\/codecreature.files.wordpress.com\/2014\/08\/sql13.jpg\"><img loading=\"lazy\" src=\"http:\/\/codecreature.files.wordpress.com\/2014\/08\/sql13.jpg?w=660&amp;h=460\" alt=\"SQL13\" width=\"660\" height=\"460\" \/><\/a><\/p>\n<p><strong>Step 2:<\/strong> Below view of SQL Server Client Network Utility,\u00a0<strong>Enable the TCP\/IP protocol<\/strong> from the General Tab,<\/p>\n<p><a href=\"http:\/\/codecreature.files.wordpress.com\/2014\/08\/sql14.jpg\"><img loading=\"lazy\" src=\"http:\/\/codecreature.files.wordpress.com\/2014\/08\/sql14.jpg?w=660&amp;h=461\" alt=\"SQL14\" width=\"660\" height=\"461\" \/><\/a><\/p>\n<p>Step 3: Go to the\u00a0<strong>Alias Tab<\/strong> to create a new alias,<strong> click on the Add\u2026 button<\/strong>.<br \/>\u201c<strong>Add Network Library Configuration<\/strong>\u201d view,<br \/><strong>Server Alias<\/strong> is what the application will attempt to connect. I have already created that alias on SQL Server named \u201cMyDevelopmentDB\u201d<br \/><strong>Server Name<\/strong>: Real Server Name, in our case server name is \u201cMyDBServer\u201d<br \/><strong>Port Number<\/strong>: 1433 (Configured as before)<br \/>Click on apply and OK to create new SQL Server Alias on \u201cMySPServer\u2019 server<\/p>\n<p><a href=\"https:\/\/codecreature.files.wordpress.com\/2014\/08\/sql151.jpg\"><img src=\"http:\/\/codecreature.files.wordpress.com\/2014\/08\/sql151.jpg?w=660\" alt=\"SQL15\" \/><\/a><\/p>\n<p>One more thing we need to take care is about the Port Number.<\/p>\n<p><strong>Open the Port for Inbound Connection if Enable Firewall<br \/><\/strong><\/p>\n<p>When we configure the\u00a0<strong>port number for SQL Server Alias Name<\/strong> then server does not allow the out side server connection on this port because of the Firewall Settings.<br \/>So our next step is open the custom port for inbound connection if firewall is enabled.<br \/>Create a new Inbound rule for allowing the connection for alias port (in my case it is 1433)<\/p>\n<p><strong>Step 1:<\/strong> Open Windows Firewall with Advance Security from the Control Pannel. Click on the<strong>\u201cNew Rule\u201d<\/strong> of Inbound Rules Tab. Select the Rule Type as \u201c<strong>Port<\/strong>\u201d. Click Next.<\/p>\n<p><a href=\"https:\/\/codecreature.files.wordpress.com\/2014\/08\/sql81.jpg\"><img loading=\"lazy\" src=\"http:\/\/codecreature.files.wordpress.com\/2014\/08\/sql81.jpg?w=660&amp;h=457\" alt=\"SQL8\" width=\"660\" height=\"457\" \/><\/a><\/p>\n<p><strong>Step 2:<\/strong> Domain Rule apply for To\u00a0<strong>TCP<\/strong> and Provide the\u00a0<strong>specific port number<\/strong> (1433). Click Next.<\/p>\n<p><a href=\"http:\/\/codecreature.files.wordpress.com\/2014\/08\/sql9.jpg\"><img loading=\"lazy\" src=\"http:\/\/codecreature.files.wordpress.com\/2014\/08\/sql9.jpg?w=660&amp;h=448\" alt=\"SQL9\" width=\"660\" height=\"448\" \/><\/a><\/p>\n<p><strong>Step 3:<\/strong> Apply the Rule on the Profile, Click Next and allow the connection.<\/p>\n<p><a href=\"http:\/\/codecreature.files.wordpress.com\/2014\/08\/sql11.jpg\"><img loading=\"lazy\" src=\"http:\/\/codecreature.files.wordpress.com\/2014\/08\/sql11.jpg?w=660&amp;h=448\" alt=\"SQL11\" width=\"660\" height=\"448\" \/><\/a><\/p>\n<p><a href=\"http:\/\/codecreature.files.wordpress.com\/2014\/08\/sql10.jpg\"><img loading=\"lazy\" src=\"http:\/\/codecreature.files.wordpress.com\/2014\/08\/sql10.jpg?w=660&amp;h=448\" alt=\"SQL10\" width=\"660\" height=\"448\" \/><\/a><br \/>Step 4: Provide the\u00a0<strong>name of the inbound rule<\/strong>, Click Finish.<\/p>\n<p><a href=\"http:\/\/codecreature.files.wordpress.com\/2014\/08\/sql12.jpg\"><img loading=\"lazy\" src=\"http:\/\/codecreature.files.wordpress.com\/2014\/08\/sql12.jpg?w=660&amp;h=450\" alt=\"SQL12\" width=\"660\" height=\"450\" \/><\/a><\/p>\n<p>Now while creating the new farm, provides the SQL Client Alias named \u201cDevelopmentDB\u201d instead of the SQL Server Instance name of \u201cMyDBServer\u201d. So All database for the SharePoint Configuration will be created on the SQL Server of the \u201cMyDBServer\u201d<\/p>\n<p>Source:\u00a0http:\/\/codecreature.wordpress.com\/2014\/08\/11\/create-and-configure-sql-server-instance-and-alias-for-sharepoint-installation\/<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>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? [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[1,8],"tags":[],"_links":{"self":[{"href":"http:\/\/obieta.com\/index.php?rest_route=\/wp\/v2\/posts\/1739"}],"collection":[{"href":"http:\/\/obieta.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/obieta.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/obieta.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/obieta.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1739"}],"version-history":[{"count":0,"href":"http:\/\/obieta.com\/index.php?rest_route=\/wp\/v2\/posts\/1739\/revisions"}],"wp:attachment":[{"href":"http:\/\/obieta.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1739"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/obieta.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1739"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/obieta.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1739"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}