Installing and Getting around SQL Server
Several versions of the SQL Server are available. An appropriate version should be procured and installed only after a thorough assessment of organizational data management needs is done. SQL Server 2000 is a robust version that is still preferred by many organizations. SQL Server 2005 offers many unique technical features and components. SQL Server 2005 Express is a handy free version that may be used to support small to medium enterprise data needs. The Windows Installer takes users through the SQL Server installation.
We will examine SQL Server 2005 installation in detail in this chapter. SQL Server 2005 allows installation and upgrade of individual components. For example, you may simply install the SQL Server database engine without analysis services or choose to install analysis services later. Significant improvements have been made to Error logging and the installation directory structure. One of the best new features of the SQL Server 2005 installation is the system configuration checker. This component checks the computer on which the SQL Server is being installed to make sure it has the requisite software, hard disk space and so on. Unattended and silent installs are also supported.
Inserting the SQL Server CD or DVD or clicking the appropriate file from the download opens an introductory screen that outlines product components and requirements. Select the 'Server components, Tools, Books Online, and Samples' option for a comprehensive install. You will have to accept the license agreement by clicking on the appropriate checkbox. Click Next to continue to the next screen.
SQL Server 2005 installation requires prior installation of the .NET Framework and MSXML. Some components use IIS - if you do not have IIS installed, go to settings -> Control Panel -> Add or Remove Programs. Click on the 'Add or Remove Windows Components' icon on the left hand bar. If the box next to 'Internet Information Services' is empty, click on it so that it contains a check mark and click next. IIS will be installed on your machine. You may have to insert the Windows XP CD to complete installation. Proceed to the next step once the prerequisites have been installed.
SQL Server Prerequisites
Next, the System Configuration Checker scans the system to make sure it contains the minimum set of resources required for a SQL Server 2005 install. Any problems are relayed to the user via warnings on a result screen. The first task completed by the wizard is a scan of your system to make sure it meets the minimum requirements for SQL Server 2005. You may fix minimum hardware requirement related warnings by delegating more RAM to the virtual machine running SQL Server.
System Configuration Check
You will next be prompted for the business name and other details, and your registration key. Afterwards, you should select the components you wish to install along with SQL Server 2005. Select the SQL Server Database Services option. Also select Analysis Services and SSIS (SQL Server Integration Services) as we will be using these in this tutorial. You could either go with a default installation or click the Advanced button and select the features yourself.
SQL Server Components for Install
Install SQL Server with the default instance rather than a named instance as it is simplest to work with a default instance. Click on the radio button next to 'Default Instance' and proceed to the security configuration step. The SQL Server Service should be able to log into the system in order to access key resources. The simplest way to allow this is to specify a single account and a set of credentials for all SQL Server services. You may also choose to start services automatically after installation. Check the box next to the SQL server Agent to start this service upon installation and leave the other default selections as is.
Automatic Service Startup
Select Windows Authentication Mode for simple configuration and connection. This way, SQL Server is as secure as the system itself. However, you shuld change the password of the DBA account 'sa' once you open the SQL Server Management Server GUI.
Security
A Collation is a set of characters and sort orders that customizes the SQL Server installation to a particular language and region in the world. The default collation is in Latin - General for English and other languages that use the Latin Script. Leave the default as it is. Note that the option 'Dictionary Order, Case-Insensitive, for use with 1252 Character set' is highlighted in the lower window. This makes your SQL Server installation case insensitive. Windows collation sets are generally more consistent than SQL collations for the XP environment.
Collation Specification
If you selected Reporting Services, install it with the default configuration options. You may even install Reporting services without specifying configuration at this time. The final step summarizes your installation configuration. Click the 'Install' button to start installation. A progress window will continuously display installation status and messages. After installation, you may browse the SQL Server program group from the 'Start' menu.
The Management Studio GUI tool allows users to administer an SQL Server installation. The GUI combines the features of the Query Analyzer tool and the Enterprise Manager GUI of SQL Server 2000 along with value-added enhancements. Click on the 'SQL Server Management Studio' item in the SQL Server program group list to open the Management Server Studio GUI. You may have to log into the Server with the credentials you specified at installation time to open the default local server (if you chose windows authentication, you need not enter the username and password again). You will see an initial screen with several panels. Frequently used panels include the Object Explorer, Registered Servers panel, Database Object Property and the Query Panel.
Management Studio with Object Explorer
You may restrict the panels you wish to see displayed at any time by clicking on the appropriate item from the 'View' menu on the top tool bar. The Registered Servers panel contains all the database servers you have connected to from your installation. The constituent databases of any registered server may be displayed and manipulated via the Object Explorer as if the registered server were a local server.
Register Server Dialogue
You may create a new registration by clicking on the Database Engine Cylinder icon on the top bar of the Registered Servers panel. Right click on 'Database Engine' and click on 'New -> Server Registration'. Now, enter the server and connection information to register the new server. A server group is a way of organizing several servers in a common folder. You may start, stop, or configure a server by right clicking on its name and clicking on an item in the list. If you have not already done so, right click on the entry for your default local folder and select 'Connect' to connect to the database
The Object Explorer shows a tree view of all objects including databases, procedures, views, security, and jobs that constitute the currently connected Server. The Databases folder contains one folder per database with sub folders for tables, views, and Programmed Objects such as functions and procedures. Note that every SQL Server Installation comes with a set of System databases and associated objects. The Security folder under each database contains lists of user account login names and roles. The Server Objects folder contains Linked Servers, Triggers and other server-wide objects. The database mailbox, Maintenance related plans, Full Text configuration, Transaction Coordinator and so on are stored under the Management folder. A folder is also included for Notification Services.
One of the first things you should do if you chose Windows Authentication is to expand the security folder and change the default password assigned to user 'sa'. To do this, click on the '+' sign next to the security folder and the '+' sign next to logins. Right click on the 'sa' user entry and click on 'properties'. Enter a new password and confirmation in the pop-up dialogue and click 'OK'.
Change sa password
To open a new query window, click on the 'New Query' icon on the left hand side of the top bar. You may enter one or more T-SQL commands in the new Query panel and execute the commands by clicking the Execute '!' icon or hitting the F5 key. A Query window for the 'Master' database opens up by default; to open a window to query a specific database, expand 'Databases' by clicking the '+' sign, right click on the database name and select 'New Query'. The Query editor allows users to design queries graphically rather than by typing a command. We will use this tool to create a new view later in the tutorial. This editor may be used to design both data manipulation/search queries and data modification (UPDATE, INSERT, DELETE) queries.
The Solution Explorer allows users to organize groups of T-SQL scripts into a .NET framework-like project. Such solutions may be version controlled and managed through Microsoft Visual Source Safe. Maintenance tasks such as backups and logging may be packaged into a Maintenance Plan. Such plans are saved in the Management Folder under Maintenance Plans. A job wizard allows users to create schedules for maintenance plans and other jobs. The activity monitor in the Management folder allows users to view system processes and locks on processes and objects. The DBA may use the constituent views to monitor the server.
