1. Introduction to MS-SQL Server
Shared by: kih21112
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.