1. Introduction to MS-SQL Server

							1. Introduction to MS-SQL Server
• Objectives
   – Learn SQL Server 2000 components
• Contents
   –   What Are the SQL Server 2000 Components
   –   What Is the Relational Database Architecture
   –   Create databases
   –   Maintain databases
   –   Capacity and planning
                                                      SQL
   –   Prestanda measurement
• Practicals
   – Installing and setting up MS-SQL Server
• Summary
MS-SQL Server components
MS-SQL Server components lined
• SQL Server 2000 rational database engine
   – The SQL Server 2000 relational database engine is an RDBMS that manages and
     stores data in relational tables.
• SQL Server 2000 Analysis Service
   – SQL Server 2000 Analysis Services provides tools for analyzing the data stored in data
     warehouses and data marts on SQL Server 2000.
• Application Support (a set of API‟s)
   – SQL Distributed Management Objects (SQL-DMO)
   – Decision Support Objects (DSO)
   – Windows Management Instrumentation (WMI)
• SQL Server 2000 Data Transformation Services (DTS)
   – You can use SQL Server 2000 Data Transformation Services (DTS) to retrieve data
     from one data source, perform simple or complex transformations on the data
• SQL Server 2000 Replication
   – You can use SQL Server 2000 replication to keep data close to individuals or
     workgroups in order to optimize performance
• SQL Server 2000 English Query
• Meta Data Services
   – SQL Server 2000 Meta Data Services is a set of services that allows meta
     data about databases and client applications to be stored and managed
SQL Server 2000 Editions
• SQL Server 2000 Enterprise Edition
  – The SQL Server 2000 Enterprise Edition supports all SQL Server 2000
    features. This is for the large corperations.
• SQL Server 2000 Standard Edition
  – This server facilitiates most functions nad is headed towards small and
    medium sized corperations.
• SQL Server 2000 Personal Edition
  – The SQL Server 2000 Personal Edition supports all of the SQL Server 2000
    features supported by the SQL Server 2000 Standard Edition exept transact
    sql.
  – This edition is for standalone applications and mobile users requiring local data
  storage on a client computer.
• SQL Server 2000 Windows CE Edition
  – The memory footprint for SQL Server CE is approximately 1 MB!
• SQL Server 2000 Developer Edition
• SQL Server 2000 Enterprise Evaluation Edition
Integration with Windows 2000
• Windows Authentication
   – An instance of SQL Server 2000 running on Windows 2000 can use
     Windows authentication and allow the operating system to control
     access to SQL Server 2000 using only trusted connections.
• SQL Authentication
   – Special SQL Database users can be added in SQL.
   – Note the common programmer invented Listuser and Sqladmin.
• Memory Management
   – An instance of SQL Server 2000 running on Windows 2000
   dynamically uses available physical memory as a data
   buffer to minimize disk I/O and maximize performance.
• Active Directory
   – When SQL Server 2000 is installed on a Windows 2000
   Server computer where Active Directory directory
   services are available, you can publish information
   about SQL Server 2000 in Active Directory.
Integration with Windows 2000, cont.
• Failover Clustering
   – SQL Server 2000 Enterprise Edition running on Windows 2000
• Microsoft Distributed Transaction Coordinator
   – MS DTC allows applications to extend transactions across two or more
     instances of SQL Server 2000 either on the same computer or across
     different computers.
• SMP (Symmetric Multi Processor)
   – An instance of SQL Server 2000 running on Microsoft Windows 2000 Data Center
     can scale effectively on up to 32 processors, 8 processors on Windows 2000
     Advanced Server
• Asynchronous and Scatter-gather I/O
• Event Logs
• System Monitor Counters
Server Components and Their Functions
• SQL Server service
   – implements the SQL Server 2000 database engine.
   – There is one service for each instance of SQL Server 2000.
• Microsoft SQL Server 2000 Analysis Services service
   – implements SQL Server 2000 AnalysisServices.
   – There is only one service, regardless of the number of instances of SQL Server 2000.
• SQL Server Agent service
   – implements the agent that runs scheduled SQL Server 2000 administrative tasks.
   – There is one service for each instance of SQL Server 2000.
• Microsoft Search service
   – implements the full-text search engine.
   – There is only one service, regardless of the number of instances of SQL Server 2000.
• Microsoft (MS DTC) service
   – Distributed Transaction Coordinator manages distributed transactions
     between instances of SQL Server 2000.
   – There is only one service
Graphical Administration Tools and Utilities
• SQL Server Enterprise Manager
   – The primary server and database administration tool, MMC snap in.
• SQL Query Analyzer
   – Used for creating and managing database objects and testing
• SQL Profiler
   – Used to monitor and capture selected SQL Server 2000 events for analysis
     and replay.
• SQL Server Service Manager
   – A taskbar application used to start, stop, pause, or modify SQL Server 2000
     services.
• Client Network Utility
   – Used to manage the client Net-Libraries and define server aliasescontaining
     custom server connection parameters
• Server Network Utility
   – Used to manage the server Net-Libraries, including enabling SSLencryption.
Command-Line Administration Utilities
• Osql
  – allows you to query an instance of SQL Server 2000, very useful for
    scripting   osql -Usa -s %1 -P %2 -i blddbase.qry -n
• Scm (Service Control Manager)
  – is used to start, stop, pause, install, delete, or modify SQL Server
    2000 services.
• Sqldiag
  – gathers and stores diagnostic information
• Bcp
  – copies data between an instance of SQL Server 2000
• Dtsrun
  – executes packages created using DTS
• Sqlmaint
  – performs a specified set of maintenance operations onone or more
    databases.
SQL System Databases
• Physically
  – A database is two or more files on one or more disks
• Logically
  – Visible to the user as tables, views and stored procedures.
• System databases:
  – master, Records all of the system-level information for a SQL Server 2000
    system, including all other databases, login accounts, and system
    configuration settings.
  – tempdb, Stores all temporary tables and stored procedures created by
      users, as well as temporary worktables used by the relational
      database engine itself.
  – model, Serves as the template that is used whenever a new database is
      created.
  – msdb, SQL Server Agent uses this system database for scheduling
      alerts and jobs, and recording operators.
SQL User Databases




• User databases
  – Used to store and operate with the work data
  – Can be created and manipulated at will
  – Sample pubs, Northwind
Physical Structure of a Database
• Physical Structure of a Database
  – Each database consists of at least one data file and one transaction
    log file
  – These files are not shared with any other database.
• Extents and Pages
  – SQL Server 2000 allocates space from a data file for tables and
    indexes in 64-KB blocks called extents.
  – A page is the fundamental unit of data storage in SQL Server 2000,
    with the page size being 8 KB
• Transaction Log Files
  – The transaction log file resides in one or more separate physical files
    from the data files and contains a series of log records,
• To maximize prestanda those files should be
  distributed over a disk array
SQL Server Authentication
• Authentication Modes
   – Windows Authentication Mode (logged in user)
   – SQL Server login account and password
   – Mixed Mode (both the above)

• User Accounts




• Guest User Account, By default, all newly created user databases
  have no guest user account.
• Roles, A database administrator uses roles to collect users into a
  single unit against which
• to set permissions.
Quickstart Creating a User Database
• The first character
   –   must begin with a letter,
   –   the underscore (_),
   –   the “at” sign (@), which signifies a local variable or parameter,
   –   or the number sign (#), which signifies a temporary table or procedure.
• Subsequent characters in the name
   – can also include numbers and the dollar sign ($).
• Embedded spaces and special characters cannot be included.
• A SQL Server 2000 reserved keyword in uppercase or lowercase
  (examples: BACKUP or PLAN) cannot be used.
• Databases can be created with:
   –   SQL Enterprice manager tools->wizard
   –   Using Create database directly within Enterpice manager
   –   SQL Query Analyzer     Create database test1                C:\> osql –E –s localhost
   –   Command line                                                1> create database test1
                                                                   2> go
                                                                   3> exit
Scripting Databases and Database Objects
• Document existing database objects
  –   Databases
  –   Tables
  –   Indexes
  –   Views
  –   Users
  –   Groups
  –   logins
• Recreate any scripted database object
  – Disatser recovery
  – Deploying applications
• Result files are standard SQL Scripts
  – Can be run on any server
  – Use the SQL Query Analyzer to restore/manipulate with the script
Scripting your database object
•   Quick Scripting with SQL Enterprice manager
    1.   Right click on database object
    2.   Select All tasks -> Generate SQL Script
    3.   Select Options and check at least Script Database
    4.   Click on OK button
    5.   Browse to storage palce for script file
    6.   Name the script with a proper name, like the name of database
    7.   Click on Save and smile
•   The resulting textfile is ANSI SQL

•   WARNING Note!
    The generated script contain DROP DATABASE if it exists
Restoring your database object from script
• Quick Restore with SQL Enterprice manager using
  script
  1.From within SQL Enterprice manager open Tools -> SQL Query analyzer
  2.On the toolbar Open Load SQL Script (ctrl shift p)
  3.Browse to storage palce for script file and select your database object script
  4.Click on open and then on the Play (execute) button to run the script
  5.Verify that the database was created with either SQL Query analyzer or
    SQL Enterprice manager
• There are other ways to backup and restore databases that
  will be covered later
Database properties
• View with enterprice manager
  – Right click on database and select properties
  – You view the current settings for database
    options using SQL Server Enterprise
    Manager or using the
    DATABASEPROPERTYEX system function.


• View with SQL Query analyzer
  – To view the current recovery model for the TSQLDB database using the
    DATABASEPROPERTYEX system function, use the following statement in
    SQL Query
  SELECT DATABASEPROPERTYEX („TSQLDB‟, ‟RECOVERY‟)

         FULL
Managing User Database Size
• Using Automatic File Growth Appropriately
  –   Pros, no need to grew databases manually
  –   Pros, automatically grewing less work to do
  –   Cons, slows down databases when insert transactions is performed
  –   Cons, can fill disks
  –   By default, this database option setting is set to trye (enabled).
• Using Automatic File Shrinkage Appropriately
  – In addition to autogrowth, you can configure a user database to shrink
    automaticallywhenever a data file or transaction log file has a large amount
    of free space.
  – By default, this database option setting is set to false (disabled).
  – Cons, slows down databases when delete transactions is performed
• Controlling Data File Size Manually
  – Monitoring the amount of free space in your data files allows you to anticipate the
    need to increase the size of your data files.
  – You can then perform this task at a time when your system is not busy.
Creating Additional Data and Transaction Log Files

• Use Enterprice manager or SQL Query analyzer
  – Adding more files for more space
    If you need to create additional data
    files or transaction log files
    (either because the additional space
    is on a separate drive or to enhance
    performance by spreading
    database files among multiple disks),
    you can use SQL Server Enterprise Manager




  – When creating additional database files using SQL Server Enterprise Manager, you
    simply add the desired file in the Properties dialog box for the database.
  – By default, all additional data files are added to the primary filegroup.
Configuring SQL Server Disk Subsystem for Performance

• Transaction Log
   – You should choose your disk subsystem for your transaction log to reflect the fact that the
     primary function of the transaction log is to provide for recoverability of your data in case
     one or more of the disks containing your data files should fail.
• Data Files
   – You should choose your disk subsystem for your data files to reflect the fact that data loss
     and downtime are generally unacceptable.
   – Using multiple data files to spread across multiple disks in a single filegroup is one way to
     accomplish this
• Tempdb
   – You should choose your disk subsystem for the tempdb database to reflect the fact that this
     database is used only for temporary storage for work files.
   – Optimizing thetempdb database means enabling it to handle a high volume of reads and
     writes.
   – Recoverability is not an issue because tempdb is rebuilt each time SQL Server 2000 starts.
   – You should begin by placing the tempdb data file on its own disk, raid 0 is acceptable
• Best performance is acheived by having separate disks/disksets for each
  file/database.
• SCSI disks fully outdo IDE/EIDE/SATA
   – SCSI disks in raid constellation is to prefere, do NOT use IDE/SATA
Moving Data and Transaction Log Files
• Detaching Databases
  – Allways detach a database before moving it!
  – To detach a database using SQL Server Enterprise Manager, right-click the
    database you want to detach, point to All Tasks, and then click Detach
    Database
  – If users are connected to this database, you can click the Clear button to
    disconnect
  – After a database has been detached, you can move one or more of the
    physical files to a new location
• Attaching Databases
  – You must attach databases before you can use them after moving
  – To reattach a database using SQL Server Enterprise Manager, right-click
    Databases, point to All Tasks, and then click Attach Database.
  – In the Attach Database dialog box, you must enter the complete name and path of
    the primary data file
  – If SQL Server 2000 does not find any of these files, it will place a red X in the
    check box next to that physical file
  – You can also change the database name at this point
Summary storage performance
• The first step in using multiple disks to optimize your SQL
  Server 2000 production environment is to use separate disks for
  your transaction log files and your data files. This separation will
  ensure recoverability in case of a disk failure.
• Use dedicated disks for all SQL Server 2000 files where
  possible. Next, use RAID 1 for your transaction log.
• Use RAID 10 if possible for your data files.
• Consider using either RAID 0 or RAID 5 if you cannot justify
  RAID 10 because of financial constraints.