SQL 710

1. AGENDA • Tools used in SQL Server 2000 • Graphical • BOL • Enterprise Manager • Service Manager • CLI • Query Analyzer • OSQL • BCP SQL 710

2. TOOLS • Objective: • Familiarizes you with tools available for SQL Server 2000 • These tools are used to • Create, delete, manage SQL Server Objects • Backup & Restore Database • Create Users and Grant Rights • Monitor and Optimization SQL 710

3. Assignment/Exam • Basic Tools: • Books Online (BOL): • Contains References for SQL 2000 • Enterprise Manager: • Primary Management Tool for SQL Server 2000. • Almost all management could be done from here • Query Analyzer: • Primary CLI tool SQL Server 2000 • OSQL: • Operating system CLI query tool • BCP: • An import and export utility. SQL 710

4. 1.Books Online (BOL) SQL 710

5. 1.Books Online (BOL) Demo SQL 710

6. 2. ENTERPRISE MANAGER Microsoft SQL Server Microsoft SQL Server Enterprise Manager Enterprise Manager Program Program Start SQL 710 • Then click Microsoft SQL Server or click on + sign. • SQL Server Group: • Is a logical arrangement of SQL Servers. • You can create your own group . • Otherwise all servers will go to the default group

7. 2. ENTERPRISE MANAGER Microsoft SQL Server Microsoft SQL Server Enterprise Manager Enterprise Manager Program Program Start SQL 710 • Then click Microsoft SQL Server or click on + sign. • SQL Server Group: • Is a logical arrangement of SQL Servers. • You can create your own group . • Otherwise all servers will go to the default group

8. ENTERPRISE MANAGER • Taskpad: • The right hand side panel is called Taskpad. • It context sensitive of objects in the Right hand side. • Different views: • Right click on the server and click view, menu. • New Window: • from here displays a new window at that starting point. • Export list : • create a text file listing of objects underneath the object you have selected. SQL 710

9. SERVER PROPERTIES SERVER PROPERTIES • Manage the settings for SQL Server. • These are settings that effects all SQL Installation objects. • We will talk more about this in the heading “ Setting SQL Server Configuration” SQL 710

10. SERVER PROPERTIES • 1.Database: This object displays databases and allows you to manage and manipulate them. • Master: Is the main database. Contain System tables. • Model: Template for creating a new database. • msdb: used by SQL Server Agent for scheduling and saving jobs. And for Repliction and to store DTS Package • tempdb: for SQL Server internal calculation. • Northwind: Sample database for Microsoft Access users • Pubs: This database is with SQL Server from the initial version. SQL 710

11. SERVER PROPERTIES • 2.Database Objects: • Diagram • Tables • View • Stored procedure • Users • Roles • Rules • User defined data types – int, bit decimal, numeric, date, varchar, chare, text… • User defined function • Full text catalogs. SQL 710

12. SERVER PROPERTIES • 3.Data Transformation service • DTS is a tool that allows movement of data from several types of sources to either the same or different types of destination. • Eg. From text file to Microsoft Access • From SQL Server database to ORACLE database • It moves data as well as transforms data to a form that the destination database understands. SQL 710

13. SERVER PROPERTIES • 4.Management : • contains Management Tools • SQL Server Agent: It manages job which in turn contain Tasks • Backup: Creates Backup • Current Activity: Current Activity Tree and Locks. • Database Maintenance Plan: Are stored settings that schedule Maintenance for one or more databases. • SQL server logs: Contains information about Server itself. SQL 710

14. SERVER PROPERTIES • 5.Replication • Sets up and monitor automatic transfer of data or periodic tranfser of data from one server to another. SQL 710

15. SERVER PROPERTIES • 6. Secruity: • Management tool to setup server users, roles. • Creating linked server( which provides a method to query Remote server as if they are local to the server). SQL 710

16. SERVER PROPERTIES • 7.Support services • Distributed Transaction Coordinator: takes data from client programs and group data to one transaction. • Full text search: clean up full text catalog, locates the files. • Meta Data Service: stores information about Metadata( Metadata is data about data) SQL 710

17. 3. Service Manager • Is a tool controls the state of the SQL Services • Distributed Transaction Coordinator • Microsoft search • SQL server • SQL server Agent SQL 710

18. 4. Query Analyzer start Program Microsoft SQL server Query Analyzer • Enterprise manager tools Query analyzer • (Press F8 if object browser is not displayed) • Title bar • Toolbar • Menu items • Object browser • Demo • Expand pubs -> right click authors -> script object to new window as -> select compilation check (ctl +F5) execution (F5) SQL 710

19. 5. Monitoring Tools • Profiler • Is the primary tool to monitor the SQL Server • Used to track SQL statements for troubleshooting • Used to monitor the behaviour of server engine. SQL 710

20. 6.NETWORK CONFIGURATION • These tools are used to setup Network configuration • for client and server • Server Network utility: Configures network setting • for SQL Server 2000. • Start ->Programs -> Microsoft SQL Server -> Server Network utility. • Client Network Utility :Configures network settings for SQL Server 2000 clients • Start ->Programs -> Microsoft SQL Server Client Network utility SQL 710

21. Other CLI tools • Osql • replaces the old isql • It is an operating system tool. • It gives command line access to the database engine • Could be used it for local server or remote server • Used to run script • Save output to a screen or a file. SQL 710

22. Other CLT tools • Bcp • Bulk copy program • Used to move data into and out of SQL Server • bcp /? • bcp pubs.. Authors out c:\ out.txt –T – c - l SQL 710

23. Exercises • Books on line • Enterprise Manager • Registering a SQL Server • Setting SQL Server configuration • `Creating a database diagram • Accessing tables • Altering a table • Querying data from a table • Generating a Script SQL 710