DBA Tips Archive for SQL Server
|
DBA Tips Archive for SQL Server
|
Install SQL Server 2008 R2
by Jeff Hunter, Sr. Database Administrator
This article describes the steps necessary to install a clean version of Microsoft SQL Server 2008 R2 on the Windows Server 2008 operating environment. 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.
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 2008 R2 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 2008 R2 Enterprise Edition.
The following table provides a brief overview of the available editions of SQL Server 2008 R2.
| Premium Editions | Core Editions | Specialized Editions | Free Editions | ||||||
|---|---|---|---|---|---|---|---|---|---|
| Datacenter | Parallel Data Warehouse |
Enterprise | Standard | Workgroup | Web | Developer | Express | Compact 3.5 | |
| Target Scenarios | Enterprise-class data platform designed to deliver the maximum level of scalability to run very large database workloads | Highly scalable data warehouse appliance-based solution that delivers performance at low cost through a massively parallel processing (MPP) architecture. | Enterprise workloads that need redundancy and built-in Business Intelligence | Shared data scenarios in departments and small to large businesses | Remote offices that need local instances of company data | For web application hosting | Full featured edition for development and testing only | Entry level database, ideal for learning and ISV redistribution | Embedded database for developing desktop and mobile applications |
| Max CPUs | >8 CPU 256 Logical Processors |
8 CPU | 4 CPU | 2 CPU | 4 CPU | OS Maximum | 1 CPU | OS Maximum | |
| Max Memory | OS Maximum | 2 TB Ram | 64 GB Ram | 4 GB Ram | OS Maximum | OS Maximum | 1 GB Ram | OS Maximum | |
| Max DB Size | 524 PB | 524 PB | 524 PB | 524 PB | 524 PB | 524 PB | 10 GB | 4 GB | |
| Additional Resources | Learn More | Learn More | Learn More | Learn More | Learn More | Learn More | Learn More | Learn More | Learn More |
| Pricing | SQL Server 2008 R2 Pricing | Free | Free | ||||||
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 2008.
The following table lists the hardware and software I will be using for the SQL Server 2008 R2 installation described in this article.
| General Options | |||
|---|---|---|---|
| SQL Server Version | SQL Server 2008 R2, Enterprise Edition, x86 | ||
| Operating Environment | Windows Server 2008 Enterprise SP2, x86 | ||
| Machine Name | vmwindows1.idevelopment.info 1 | ||
| Machine Domain | IDEVELOPMENT.INFO | ||
| IP Address | 192.168.1.176 | ||
| SQL Server Instance | Default Instance (MSSQLSERVER) | ||
| SQL Server Instance ID | MSSQLSERVER | ||
| Memory | |||
| Physical Memory | 4 GB | ||
| Virtual Memory | 4 GB | ||
| Storage | |||
| 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 O:\OLAP\Log O:\OLAP\Temp O:\OLAP\Backup |
36 GB | NTFS |
| Hard Drive 7 — (Temp DB and Temp DB Logs) | T:\MSSQL\TempDB\Data T:\MSSQL\TempDB\Log |
36 GB | NTFS |
| Hard Drive 8 — (SQL Server Features) | U:\MSSQL\Integration Services U:\MSSQL\Reports U:\MSSQL\SQLDiag |
36 GB | NTFS |
| Security | |||
| User Account | Member Of | ||
| Install / Software Owner | IDEVELOPMENT\SQLServerAdmin 3 | Domain Users Local Administrators |
|
| 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 vmwindows1.idevelopment.info is a member of the IDEVELOPMENT.INFO domain.
2 During the SQL Server 2008 R2 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\MSSQL10_50.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 vmwindows1.idevelopment.info server. This account will be used to install and
setup the SQL Server 2008 R2 software.
In this guide, I don't intend to provide an exhaustive list of pre-requisite tasks for installing SQL Server 2008 R2. 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 2008 R2 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 2008 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 2008 R2.
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 2008 R2 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.
|
From 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 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 (vmwindows1.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 (vmwindows1.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
|
|
When complete, your screen should look similar to the following:
|
|
|
Verify Active Directory Login Accounts
|
|
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 2008 R2 installation.
Start SQL Server 2008 R2 Setup
Log in to the database machine with administrative privileges and double-click on the setup.exe file to start the SQL Server 2008 R2 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 server:
|
|
|
Connect to the Database Machine
|
|
|
|
|
Log In to the Database Machine With Administrative Privileges
|
|
Install the .Net Framework 3.5
The .Net Framework 3.5 SP1 (also referred to as .NET Framework 3.5.1) is a prerequisite for SQL Server 2008 R2. If the required .Net Framework is not already installed, then the following message will appear when you start the SQL Server 2008 R2 installation:
|
|
|
SQL Server 2008 R2 Setup Requires Microsoft .NET Framework
|
|
Click on OK to start the .Net Framework setup process. The wizard will extract files from the package and then load the installation components. This may take a minute or two to complete.
|
|
After the installation components are loaded, the wizard displays the .Net Framework Setup screen. Read over the license agreement. If you agree with the license agreement, click Install to continue.
|
|
|
Microsoft .NET Framework Setup
|
|
The setup will then download the .Net Framework package from the Microsoft website and install it on your machine.
|
|
|
Microsoft .NET Framework Download and Install Progress
|
|
After the .Net Framework package has been successfully installed, the following will appear. Click Exit to continue.
|
|
|
Microsoft .NET Framework Setup Complete
|
|
Once the .Net Framework is installed, the SQL Server 2008 R2 setup will commence:
|
SQL Server Installation Center
On the SQL Server Installation Center landing page, first select the Installation page, and then click the New installation or add features to an existing installation option.
)
|
|
|
Perform a New SQL Server 2008 R2 Non-Clustered Installation
|
|
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 standard or comprehensive report of the System Configuration Check. If no failures or warnings are reported then click OK to continue with the installation.
)
|
|
|
Review System Configuration Check Output
|
|
Product Key
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 2008 R2, and then click Next to continue.
)
|
|
|
Product Key
|
|
License Terms
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.
)
|
|
|
License Terms
|
|
Setup Support Files
On the Setup Support Files page, the wizard will indicate whether or not any additional setup support files are required for the SQL Server installation. If additional files are required, review the status of the files required, and then click Install.
)
|
|
|
Install Setup Support Files
|
|
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 standard or comprehensive report of the System Configuration Check. If no failures or warnings are reported then click Next to continue.
)
|
|
|
Review System Configuration Check Output
|
|
Setup Role
On the Setup Role page, select the SQL Server Feature Installation option which will allow you to individually select which feature components to install.
)
|
|
|
Setup SQL Server Feature Installation
|
|
Feature Selection
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 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.
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 (next screen) to the C:\Program Files\Microsoft SQL Server\ directory (the default).
)
|
|
|
Select the Enterprise Features to Install and Shared Feature Directory
|
|
Installation 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 standard or comprehensive report of the System Configuration Check. If no failures or warnings are reported then click Next to continue.
)
|
|
|
Review System Configuration Check Output
|
|
Instance Configuration
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 2008 R2 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.
)
|
|
|
Specify the Name, Instance ID, and Instance Root Directory for this Instance of SQL Server
|
|
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.
)
|
|
|
Review the Disk Space Requirements for the Selected SQL Server Features
|
|
Server Configuration
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.
)
|
|
|
Specify Service Accounts
|
|
)
|
|
|
Review Collation Configuration
|
|
Database Engine Configuration
The Database Engine Configuration page consists of three tabs — Account Provisioning, Data Directories, and FILESTREAM.
On the first tab, Account 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.
)
|
|
|
Specify Database Engine Authentication Mode and SQL Server Administrators
|
|
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\MSSQL10_50.MSSQLSERVER\MSSQL\Data. After the install, I intend to move the system databases to a more user friendly directory such as F:\MSSQL\Data.
)
|
|
|
Specify Default Directories
|
|
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.
)
|
|
|
Enable FILESTREAM
|
|
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 — Account Provisioning and Data Directories.
On the first tab, Account Provisioning, specify users that 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.
)
|
|
|
Specify Which Users Have Administrative Permissions for Analysis Services
|
|
Next, click the Data Directories tab to specify the directories for data, transaction logs, tempdb, and backups for the Analysis Services instance.
)
|
|
|
Specify Data Directories for SQL Server Analysis Services
|
|
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. You can specify Native mode or SharePoint integrated mode with a default configuration or you can install but not configure the report server.
If you select Native mode default configuration, 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, but do not configure the report server and then use the Reporting Services Configuration Manager application after installing SQL Server to manually configure the Report Server.
)
|
|
|
Specify Reporting Services Mode
|
|
Error Reporting
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.
)
|
|
|
Enable / Disable Feature Error Reporting Functionality
|
|
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 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 standard or comprehensive report of the System Configuration Check. If no failures or warnings are reported then click Next to continue.
)
|
|
|
Review System Configuration Check Output
|
|
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 2008 R2 installation.
)
|
|
|
Verify the SQL Server 2008 R2 Features to be Installed
|
|
Installation Progress
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.
)
|
|
|
Installation Progress
|
|
Complete
The installation and setup of SQL Server 2008 R2 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 R2 Setup screen.
)
|
|
|
SQL Server 2008 R2 Installation Complete
|
|
The SQL Server R2 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.
)
|
|
|
Perform a New SQL Server 2008 R2 Non-Clustered Installation
|
|
This section contains a short list of post-installation checks which are useful to ensure SQL Server 2008 R2 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.
|
|
|
Verify SQL Server Services
|
|
Next, start SQL Server Management Studio. From the Windows Start menu, select Start / All Programs / Microsoft SQL Server 2008 R2 / SQL Server Management Studio.
|
|
|
Start SQL Server Management Studio
|
|
|
|
|
Connect to SQL Server
|
|
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.
Windows Firewall
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:
http://vmwindows1/ReportServer
http://vmwindows1/Reports
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:
http://vmwindows1/ReportServer$WebApps01
http://vmwindows1/Reports$WebApps01
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 2008R2 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, and Windows server environment. Jeff's other interests include mathematical encryption theory, 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 18 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.
Copyright (c) 1998-2013 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 jhunter@idevelopment.info.
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 02:43:32 EDT
Page Count: 26496
