DBA Tips Archive for SQL Server
No Title[an error occurred while processing this directive]
by Jeff Hunter, Sr. Database Administrator
This article describes the steps necessary to perform a SQL Server 2012 Service Pack 1 standalone installation on the Windows Server 2012 operating system. Installing SQL Server is not a difficult task by any means, however, critical decisions will be discussed during the setup and configuration described in this guide that will have a great impact on your installation. Please note that this guide only documents the steps to perform a clean install of SQL Server and does not provide steps to perform upgrades or apply patches.
This guide will include instructions for installing the SQL Server Database Engine instance, Analysis Services, Reporting Services, Integration Services, as well as all supplementary sub-features like SQL Server Replication, Full-Text Search, Shared Components, and all of the tools that ship with SQL Server.
SQL Server 2012 provides a variety of different editions of the product that are all tailed to meet the needs of different organizations and application requirements. In most cases, it will be apparent which edition of SQL Server you will need to install. For the purpose of this guide, I will be installing Microsoft SQL Server 2012 Enterprise Edition.
Please refer to the following links for more information on the available SQL Server editions:
While this guide provides step-by-step instructions for successfully installing SQL Server, it is by no means a substitute for the official SQL Server documentation from Microsoft. Microsoft's official SQL Server documentation site is http://msdn.microsoft.com/en-us/library/bb545450.aspx.
In addition to this guide, users should also consult the following Microsoft SQL Server documents to gain a full understanding of alternative configuration options, installation, and administration with SQL Server 2012.
The following table lists the hardware and software I will be using for the SQL Server 2012 installation described in this article.
|SQL Server Version||SQL Server 2012, Enterprise Edition, x86_64|
|Operating Environment||Windows Server 2012 Standard, x64|
|Machine Name||sqlprod1.idevelopment.info 1|
|SQL Server Instance||Default Instance (MSSQLSERVER)|
|SQL Server Instance ID||MSSQLSERVER|
|Physical Memory||4 GB|
|Virtual Memory||4 GB|
| ||Directory||Volume Size||File System|
|Hard Drive 1 (SQL Server Software)||C:\Program Files\Microsoft SQL Server||73 GB||NTFS|
|Hard Drive 2 (System Databases)||F:\MSSQL\Data 2||36 GB||NTFS|
|Hard Drive 3 (User Databases)||G:\MSSQL\Data||73 GB||NTFS|
|Hard Drive 4 (Database Backups)||K:\MSSQL\Backup||146 GB||NTFS|
|Hard Drive 5 (Transaction Logs)||L:\MSSQL\Log||36 GB||NTFS|
|Hard Drive 6 (Analysis Services / OLAP Databases)||O:\OLAP\Data
|Hard Drive 7 (Temp DB and Temp DB Logs)||T:\MSSQL\TempDB\Data
|Hard Drive 8 (SQL Server Features)||U:\MSSQL\Integration Services
| ||User Account||Member Of|
|Install / Software Owner||IDEVELOPMENT\SQLServerAdmin 3||Domain Users
|SQL Server Service Account (Agent)||IDEVELOPMENT\SQLServerAgent||Domain Users|
|SQL Server Service Account (Database Engine)||IDEVELOPMENT\SQLServerEngine||Domain Users|
|SQL Server Service Account (Analysis Services)||IDEVELOPMENT\SQLServerAnalysis||Domain Users|
|SQL Server Service Account (Reporting Services)||IDEVELOPMENT\SQLServerReporting||Domain Users|
|SQL Server Service Account (Integration Services)||IDEVELOPMENT\SQLServerIntegration||Domain Users|
1 The machine sqlprod1.idevelopment.info is a member of the IDEVELOPMENT.INFO domain.
2 During the SQL Server 2012 setup on the Database Engine Configuration page, the wizard allows you to specify the Data root directory which in this guide will be set to F:\MSSQL\. This setting is used by the wizard to configure the default directories for the various database files and transaction log files required for the instance (system databases, user databases, transaction logs, tempdb, and backups). All of the default directories chosen by the wizard are user-configurable during the setup process with the exception of the system databases. With the SQL Server instance ID in this guide set to MSSQLSERVER and the Data root directory set to F:\MSSQL\, the wizard will automatically specify a non-user configurable default directory for the system databases as F:\MSSQL\MSSQL11.MSSQLSERVER\MSSQL\Data. After the install, I intend to move the system databases to a more user friendly directory such as F:\MSSQL\Data.
3 The IDEVELOPMENT\SQLServerAdmin Active Directory user account, known in this guide as the SQL Server Administrator, is a member of the IDEVELOPMENT.INFO Domain Users group and a member of the local Administrators group on the sqlprod1.idevelopment.info server. This account will be used to install and setup the SQL Server 2012 software.
In this guide, I don't intend to provide an exhaustive list of pre-requisite tasks for installing SQL Server 2012. I will, however, highlight several considerations that should be made which will provide for a successful installation.
It is recommended to copy the entire directory structure from the SQL Server 2012 installation media to the C: drive of the machine you will be performing the installation on. Although the copy process can take a little time to complete, it does provide for the following advantages:
It makes the installation and setup process significantly faster than running it from the CD/DVD installation media.
It allows you to easily add or remove components at a later time without having to search for and locate the CD/DVD installation media.
If the CD/DVD installation media is damaged for any reason and a file isn't accessible, you have the advantage of knowing now rather than halfway through the installation process.
The following is what my system looks like after copying the directory structure from the CD/DVD installation media to the local disk:
Copy the Software Installation Files to Local Drive
Whether you are installing SQL Server 2012 in a lab or for a critical production application, you must ensure that the hardware and software being used meets the minimum system requirements. Click here for a detailed overview of the hardware and software requirements for installing SQL Server 2012.
Note that Windows XP, Windows Server 2003 and Windows Server 2000 are no longer supported for installing SQL Server 2012.
The user account that is running SQL Server Setup must have administrative privileges on the SQL Server database server. The account used in this guide to perform the SQL Server installation is an Active Directory user account named IDEVELOPMENT\SQLServerAdmin. This account is a member of the IDEVELOPMENT.INFO Domain Users group and a member of the local Administrators group on the database server.
Perform the installation of SQL Server and create databases on a NTFS file system. Installing SQL Server 2012 on a computer with FAT32 file system is supported but not recommended as it is less secure than the NTFS file system. Do no attempt to install SQL Server 2012 on a compressed, encrypted, or read-only drive, as this will cause Setup to fail.
For security reasons, Microsoft does not recommend that you do not install SQL Server 2012 on a domain controller. SQL Server Setup will not block installation on a computer that is a domain controller, but the following limitations apply:
You cannot run SQL Server services on a domain controller under a local service account.
After SQL Server is installed on a computer, you cannot change the computer from a domain member to a domain controller. You must un-install SQL Server before you change the host computer to a domain controller.
After SQL Server is installed on a computer, you cannot change the computer from a domain controller to a domain member. You must un-install SQL Server before you change the host computer to a domain member.
SQL Server failover cluster instances are not supported where cluster nodes are domain controllers.
SQL Server Setup cannot create security groups or provision SQL Server service accounts on a read-only domain controller. In this scenario, Setup will fail.
Verify Windows Management Instrumentation service is running. (Control Panel / Administrative Tools / Services)
Verify Windows Management Instrumentation Service is Running
If the Windows Firewall is enabled on the database server, make sure the appropriate ports are open to enable remote access. See the rules documentation at http://go.microsoft.com/fwlink/?LinkId=94001 for information about protocols and ports to open for each feature.
.NET 3.5 SP1 is a requirement for SQL Server 2012 when you select Database Engine, Reporting Services, Master Data Services, Data Quality Services, Replication, or SQL Server Management Studio, and it is no longer installed by SQL Server Setup.
If you run Setup on a computer with the Windows Vista SP2 or Windows Server 2008 SP2 operating system, and you do not have .NET 3.5 SP1, SQL Server Setup requires you to download and install.NET 3.5 SP1 before you can continue with the SQL Server installation. The error message includes a link to the download center, or you can download .NET 3.5 SP1 from Windows Update. To avoid interruption during SQL Server Setup, you can download and install .NET 3.5 SP1 before you run SQL Server Setup.
If you run Setup on a computer with the Windows Server 2008 R2 SP1 operating system, you must enable .NET Framework 3.5 SP1 before you install SQL Server 2012.
If you run Setup on a computer with the Windows Server 2012 or Windows 8 operating system, the SQL Server Setup automatically downloads and installs the .NET Framework 3.5 SP1. This process requires Internet access.
If there is no Internet access, it is required to download and install .NET Framework 3.5 SP1 before you run Setup to install any of the above mentioned components. For more information about the recommendations and guidance on how to acquire and enable .NET Framework 3.5 in Windows Server 2012 or Windows 8, see Microsoft .NET Framework 3.5 Deployment Considerations (http://msdn.microsoft.com/library/windows/hardware/hh975396).
.NET 4.0 is a requirement for SQL Server 2012. SQL Server installs .NET 4.0 during the feature installation step.
If you are installing the SQL Server Express editions, ensure that an Internet connection is available on the computer. SQL Server Setup downloads and installs the .NET Framework 4 because it is not included in the SQL Server Express media.
SQL Server Express does not install .NET 4.0 on the Server Core mode of Windows Server 2008 R2 SP1 or Windows Server 2012. You must install .NET 4.0 before you install SQL Server Express on a Server Core installation of Windows Server 2008 R2 SP1 or Windows Server 2012.
All of the core components in SQL Server run as services. Depending on the components that you decide to install, SQL Server Setup installs the following services:
During the SQL Server 2012 setup process, you will be asked to assign a low-privilege login account for each of the SQL Server services. You can assign the same login account to all SQL Server services, or you can configure each service account individually. In this guide, I will create a separate Active Directory login account for each of the SQL Server services (see below). In addition, I'll create a dedicated Active Directory login account that will be used to install and setup the SQL Server software (known in this guide as the SQL Server Administrator). These login accounts will need to be created prior to installing SQL Server.
It is not mandatory that service accounts or the SQL Server Administrator be created as Active Directory users. If the computer is not part of a domain, it is fully acceptable to use local user accounts or in some cases, built-in Windows accounts. However, if the service needs to interact with network services or access domain resources, you will need to create an Active Directory user account. Local user accounts can be created using Control Panel / User Accounts.
To create Active Directory login accounts (Domain User accounts) log on to the Active Directory Domain Services (AD DS) machine. Bring up the Active Directory Users and Computers application and select the Action / New / User menu option. Create the Active Directory login accounts by supplying the Full Name and User Logon Name. After the user is created, the login account will be a member of the Domain Users group.
Create Active Directory Login Account for Services
Repeat the same procedure to create the service accounts listed in the following table (if you are not using Active Directory, these can be setup as local user accounts):
|Purpose||Full Name||User Logon Name||Member Of|
|SQL Server Service Account||SQL Server Service - (Agent)||SQLServerAgent||Domain Users|
|SQL Server Service Account||SQL Server Service - (Database Engine)||SQLServerEngine||Domain Users|
|SQL Server Service Account||SQL Server Service - (Analysis Services)||SQLServerAnalysis||Domain Users|
|SQL Server Service Account||SQL Server Service - (Reporting Services)||SQLServerReporting||Domain Users|
|SQL Server Service Account||SQL Server Service - (Integration Services)||SQLServerIntegration||Domain Users|
Next, create a dedicated Active Directory login account for the SQL Server Administrator that will be used to install and setup the SQL Server software (or a local account if you are not using Active Directory). The Active Directory login account used in this guide will be named IDEVELOPMENT\SQLServerAdmin and will be a member of the Domain Users group as well as the local Administrators group of the database machine (sqlprod1.idevelopment.info).
Create Active Directory Login Account for SQL Server Administrator
After creating the SQL Server Administrator login account, add it to the local Administrators group for the database machine. This task can be performed from the AD DS server if the database machine has been added to the domain or it could be done from the database machine itself while logged in as an Administrator. Bring up the Active Directory Users and Computers application from the AD DS machine and click on the Computers folder under the domain.
Computer Management Screen
Right-click on the database machine (sqlprod1.idevelopment.info) and click Manage.
Computer Management Screen
The Computer Management window will be displayed. Under Computer Management / System Tools / Local Users and Groups, click on the Groups folder.
Computer Management Screen
Double-click the Administrators group and Add the SQL Server Administrator login account.
Add SQL Server Administrator Login Account to Local Administrators Group
Click OK and verify that the SQL Server Administrator login account has been added to the local Administrators group for the database machine.
Verify Local Administrators Group
It's a good idea to reboot the machine prior to starting the SQL Server installation process. This ensures that you do not have any pending reboot requests which could block the SQL Server installation and setup process.
Once all of the pre-requisites have been met, it's time to start the SQL Server 2012 installation.
Start SQL Server 2012 Setup
Log in to the database machine with administrative privileges and double-click on the setup.exe file to start the SQL Server 2012 installation process. For the purpose of this guide, I will be performing the SQL Server installation as the domain user IDEVELOPMENT\SQLServerAdmin which is a member of the local Administrators group on the database server.
Connect to the Database Machine with Administrative Privileges
SQL Server Installation Center
On the SQL Server Installation Center landing page, first select the Installation page, and then click the New SQL Server stand-alone installation or add features to an existing installation option.
Setup Support Rules
The wizard will run the system validation tool to perform a System Configuration Check which looks for conditions on the computer that might block the setup of SQL Server. On the Setup Support Rules page, review the output from the System Configuration Check to ensure all tests associated with the operation passed without any failures or warnings. Click the Show details button (as shown below) or View detailed report to review a comprehensive report of the System Configuration Check. If no failures or warnings are reported then click OK to continue with the installation.
On the Product Key page, specify a free edition of SQL Server such as Evaluation or Express Edition or provide a SQL Server 25-character product key to validate this instance of SQL Server 2012. In almost all cases the product key is included in the installation media. Click Next to continue.
Read through the Microsoft Software License Terms. If you agree with the license agreement, choose I accept the license terms, and then click Next to continue.
On the Product Updates page, the wizard will indicate whether or not any additional setup support files are required for the SQL Server installation. If additional updates are required, review the status of the files required.
Click Next which will install the Setup updates.
Setup Support Rules
The wizard will again run the system validation tool to perform a System Configuration Check which looks for conditions on the computer that might block the setup of SQL Server. On the Setup Support Rules page, review the output from the System Configuration Check to ensure all tests associated with the operation passed without any failures or warnings. Click the Show details button (as shown below) or View detailed report to review a comprehensive report of the System Configuration Check.
The system validation check may display a warning if the Windows Firewall is enabled. If the Windows Firewall is enabled on the database server, make sure the appropriate ports are open to enable remote access. See the rules documentation at http://go.microsoft.com/fwlink/?LinkId=94001 for information about protocols and ports to open for each feature. Although this warning will not prevent SQL Server from being installed, I opted to disable the Windows Firewall on the database server. After disabling the Windows Firewall, the warning will disappear after clicking the "Re-run" button.
If no other failures or warnings are reported then click Next to continue.
On the Setup Role page, select the SQL Server Feature Installation option which will allow you to individually select which feature components to install.
On the Feature Selection page, choose which features to install. For the purpose of this guide, I will be installing the SQL Server Database Engine Service, Analysis Services, Reporting Services, Integration Services, as well as all supplementary sub-features like SQL Server Replication, Full-Text Search, Shared Components, and all of the tools that ship with SQL Server. A description of each feature will appear in the "Feature description" area when you click on a feature.
In addition to selecting which features to install, set the directory that should be used to install SQL Server shared features. For this guide, I will be installing the shared features and the SQL Server software to the default directories C:\Program Files\Microsoft SQL Server\ and C:\Program Files (x86)\Microsoft SQL Server\ (for 32-bit files).
The wizard will again run the system validation tool to perform a System Configuration Check which looks for conditions on the computer that might block the setup of SQL Server. On the Installation Rules page, review the output from the System Configuration Check to ensure all tests associated with the operation passed without any failures or warnings. Click the Show details button (as shown below) or View detailed report to review a standard or comprehensive report of the System Configuration Check. If no failures or warnings are reported then click Next to continue.
On the Instance Configuration page, specify whether to create a Default Instance, which is MSSQLSERVER, or a Named Instance of SQL Server. Note that each instance ID name must be unique and there can only be one default instance per SQL Server system. In this guide I will be configuring a Default Instance.
On the same screen, enter the Instance Root Directory where the SQL Server 2012 software should to be installed. By default, the instance root directory is C:\Program Files\Microsoft SQL Server\. For this guide, I will be using the default instance root directory to install the SQL Server software.
Disk Space Requirements
On the Disk Space Requirements page, review the disk space requirements for the SQL Server components and features you selected for your installation, and then click Next to continue.
On the Server Configuration page, specify the low-privilege login account name and password for the appropriate service accounts that you created in the SQL Server Service Accounts section to this guide. Next, specify whether services should start automatically, are started manually, or are disabled. Note that the default startup type for the SQL Server Agent service is manual which means when the machine is rebooted, the service does not start up and any jobs that the agent handles will not run. If you intend to execute jobs and automate administrative tasks, it is recommended to change the SQL Server Agent service to start automatically. I also prefer to change the SQL Server Browser service to start automatically. Finally, review the collation settings for the Database Engine (and Analysis Service if it was selected for install), and then click Next to continue.
Database Engine Configuration
The Database Engine Configuration page consists of three tabs Server Provisioning, Data Directories, and FILESTREAM.
On the first tab, Server Provisioning, specify the Authentication Mode which consists of either Windows Authentication Mode or Mixed Mode (SQL Server authentication and Windows authentication). With Windows authentication, you can only authenticate connections to a SQL Server instance using Windows accounts (Active Directory user accounts, local user accounts, or groups). With Mixed Mode authentication, users can authenticate to a SQL Server instance using SQL Server accounts or Domain / Local User accounts. If you select Mixed Mode authentication, enter and confirm a strong password for the built-in SQL Server system administrator (sa) account. Although Microsoft highly recommends Windows-only authentication, many third party applications cannot authenticate with Windows and rely on SQL Server logins to operate correctly. In cases like this, enabling Mixed Mode authentication is needed.
Next, provision a SQL Server Administrator that will have administrative permissions on the Database Engine by either selecting the option Add Current User or using the Add button to specify other SQL Server administrator accounts.
Select the second tab, Data Directories, to specify the default database directories associated with the installation of this SQL Server instance. Depending on the number of disks available on the database server, it is highly recommended to place the System Databases, User Databases, User Databases Logs, TempDB Data, TempDB Log and Backup directory on different volumes other than the default. At a minimum, it is a best practice to store database log files on a separate disk volume then database data files. Performance can be significantly improved as log file I/O's are sequential and database data file I/O's are mostly random. It is also recommended to keep TempDB related data and log files on their own volume to further avoid I/O contention and improve performance.
Notice that the wizard allows you to specify the Data Root Directory which in this guide will be set to F:\MSSQL\. By default, all sub-directories build off the Data Root Directory for the various database files and transaction log files required for the instance. All of the default directories chosen by the wizard are user-configurable during the setup process with the exception of the system databases. With the SQL Server instance ID used in this guide set to MSSQLSERVER and the Data Root Directory set to F:\MSSQL\, the wizard will automatically specify a non-user configurable default directory for the system databases as F:\MSSQL\MSSQL11.MSSQLSERVER\MSSQL\Data. After the install, I intend to move the system databases to a more user friendly directory such as F:\MSSQL\Data.
Finally, click on the FILESTREAM tab if you would like to enable FILESTREAM for the setup of this SQL Server instance. FILESTREAM integrates the SQL Server Database Engine with an NTFS file system by storing varbinary(max) binary large object (BLOB) data as files on the file system. Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data. Win32 file system interfaces provide streaming access to the data.
Use the following options to enable FILESTREAM during the setup of this SQL Server instance.
Enable FILESTREAM for Transact-SQL access
Select to enable FILESTREAM for Transact-SQL access. This control must be checked before the other control options will be available.
Enable FILESTREAM for file I/O streaming access
Select to enable Win32 streaming access for FILESTREAM.
Windows share name
Use this control to enter the name of the Windows share in which the FILESTREAM data will be stored.
Allow remote clients to have streaming access to FILESTREAM data
Select this control to allow remote clients to access this FILESTREAM data on this server.
Because many databases do not store unstructured data and given the restrictions imposed on using FILESTREAM, many organizations will not make use of this feature. It is advisable to leave FILESTREAM disabled unless you intend to use it. If you decide to use FILESTREAM after SQL Server has already been installed, it can be easily enabled using either SQL Server Configuration Manager or by running the following Transact-SQL code from Query Editor:
Analysis Services Configuration
If you selected to install an Analysis Services instance, then the Analysis Services Configuration page will be displayed with two tabs Server Provisioning and Data Directories.
On the first tab, Server Provisioning, select "Install Analysis Services in Multidimensional and Data Mining Mode" to install Analysis Services with support for online analytical processing databases and data mining models. Select "Tabular Mode" to install Analysis Services with support for tabular models.
Next, specify users that will have administrative permissions for Analysis Services by either selecting the option Add Current User or using the Add button to specify other SQL Server administrator accounts.
Next, click the Data Directories tab to specify the directories for data, transaction logs, tempdb, and backups for the Analysis Services instance.
Reporting Services Configuration
If you selected to install a Reporting Services instance, then the Reporting Services Configuration page will be displayed.
On the Reporting Services Configuration page, specify options that determine how the Report Server instance will be installed.
If you select Native Mode "Install and configure", the Report Server will be available as soon as the SQL Server setup is finished.
It is important to note that with a default configuration, the Report Server is installed on the SQL Server instance currently being configured which means that the names used for various components will reflect the instance name. If you do not want to use the default configuration, select the option to Install only and then use the Reporting Services Configuration Manager application after installing SQL Server to manually configure the Report Server.
Use the Error Reporting page of the SQL Server Installation Wizard to enable feature error reporting functionality for SQL Server. By enabling this option, any error information from SQL Server can be sent over secure HTTP (HTTPS) to Microsoft by default or to a designated corporate error reporting server if you have one configured in Active Directory Group Policy. This option can be safely skipped if you do not wish to share any of this information.
Installation Configuration Rules
The wizard will again run the system validation tool to perform a System Configuration Check which looks for conditions on the computer that might block the setup of SQL Server. On the Installation Configuration Rules page, review the output from the System Configuration Check to ensure all tests associated with the operation passed without any failures or warnings. Click the Show details button (as shown below) or View detailed report to review a standard or comprehensive report of the System Configuration Check. If no failures or warnings are reported then click Next to continue.
Ready to Install
On the Ready to Install page, review the various configuration options you selected during the setup wizard. If you need to make any changes to the installation settings, click the Back button. If the installation settings are correct, click the Install button to start the SQL Server 2012 installation.
The Installation Progress page will track the progress of the components as they are being installed as well as the progress of the entire SQL Server installation.
Since the database server I am using for this guide is running Windows Server 2012, I decided to not enable the .NET Framework 3.5 SP1 before running Setup. If SQL Server 2012 Setup is run on a computer with the Windows Server 2012 or Windows 8 operating system, the SQL Server Setup automatically downloads and installs the .NET Framework 3.5 SP1. This process requires Internet access. Setup will display "Enabling OS feature 'NetFx3'" while it is downloading and installing the .NET Framework 3.5 SP1.
After .NET Framework 3.5 SP1 is successfully installed, Setup will continue with the remainder of the SQL Server installation.
The installation and setup of SQL Server 2012 is finally complete! The Complete page provides a link to a summary log file of the installation. This log contains important information about the SQL Server setup and should be saved for future reference. Click the Close button to exit from the SQL Server 2012 Setup screen.
The SQL Server 2012 Setup screen will close and you will be returned to the SQL Server Installation Center landing page. Click on the Close button in the top right of the screen to exit the SQL Server installer.
This section contains a short list of post-installation checks which are useful to ensure SQL Server 2012 was installed correctly and connections can be made to the various SQL Server components.
One of the first checks should be to verify that all required SQL Server services have been started.
Next, start SQL Server Management Studio and connect to the new SQL Server instance.
Start SQL Server Management Studio
After authenticating to the new SQL Server instance, run a simple query. Right-click on the new SQL Server instance and select New Query.
Start a New Query
Enter the following query and hit F5 to execute:
Enter and Run Sample Query
Check that the new SQL Server instance can be accessed from the network. From another SQL Server on the same domain, run SQLCMD -L.
List SQL Server Instances
If you can't see the new SQL Server in this list or the server list is empty, check the following:
SQL Server Browser Service
Check that the SQL Server Browser service is started on the machine where you have just installed SQL Server.
Check that Windows Firewall is disabled.
HideInstance Network Protocol Option
Check that the HideInstance network protocol option is set to No. On the machine running the new SQL Server instance, open SQL Server Configuration Manager, expand SQL Server Network Configuration, and right-click the Protocols for <Instance ID> node and click Properties. If HideInstance is set to Yes, this instance will be hidden from sqlcmd -L.
Next, log on to the new instance from another SQL Server on the same domain using SQLCMD.EXE.
Log On to SQL Server Instance through the Network
Check that the SQL Server Agent is running (if you enabled the service to start automatically). You should see a green arrow next to the SQL Server Agent node.
SQL Server Agent Running
If you selected to install a Reporting Services instance and setup a native mode default configuration, verify that the Report Server and Report Manager is functioning correctly. The virtual directories to use for the Report Server and Report Manager can be accessed from a Web browser as follows:
SQL Server Default Instance
For a default SQL Server instance, use http://ServerName/DirectoryName where ServerName is the host name or DNS name for the machine and DirectoryName is the name of the virtual directory for either the Report Server (ReportServer) or Report Manager (Reports). For example:
SQL Server Named Instance
For a named SQL Server instance, use http://ServerName/DirectoryName$InstanceName where ServerName is the host name or DNS name for the machine, DirectoryName is the name of the virtual directory for either the Report Server (ReportServer) or Report Manager (Reports), and InstanceName is the SQL Server instance name. For example:
Click here for instructions on how to authenticate to the Report Server and resolve any permission errors you may receive while accessing the Reporting Services.
Starting with SQL Server 2005, the AdventureWorks sample databases are not installed by default due to security concerns. Users are now required to download and manually install the sample databases after successfully setting up SQL Server. Please refer to the following guide for instructions on how to download and install the AdventureWorks 2012 and AdventureWorks DW 2012 sample databases:
Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX / Linux server environment. Jeff's other interests include mathematical encryption theory, tutoring advanced mathematics, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 20 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science and Mathematics.
Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.
All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at firstname.lastname@example.org.
I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.
Last modified on
Saturday, 23-Mar-2013 15:30:25 EDT
Page Count: 23318