1 / 23

SQL 710

Week 4 Agenda. Chapter 4: Access data Create Databases Set Database Options ODBC The Web Assistant. SQL 710. Access Data. A DBA is most concerned with administering SQL Server (monitor, optimize, …) Users and developers are more concerned about accessing data from their databases

molly
Download Presentation

SQL 710

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Week 4 Agenda • Chapter 4: • Access data • Create Databases • Set Database Options • ODBC • The Web Assistant SQL 710

  2. Access Data • A DBA is most concerned with administering SQL Server (monitor, optimize, …) • Users and developers are more concerned about accessing data from their databases • Today we'll discuss the process of creating databases and methods used to access data from a database SQL 710

  3. Create Database • Topics: • Create a database • Restore the database from backup • Attach the database from file SQL 710

  4. Database Physical Storage • SQL Server stores data physically using 2 files: • The database file(.mdf extension) • The transaction log file(.ldf) • The relationship between these two files is described by the checkpoint process SQL 710

  5. Checkpoint • When data for a database is modified the data is first sent to memory • The modified data is then written sequentially to the transaction log file for that database • The data, when committed, is then written to the database file. When the data is verified by the logging process to be in the database file a mark is made on the line of data in the log file indicating that the data has been recorded. The marking process is known as a checkpoint. SQL 710

  6. Database Recovery Settings • Database recovery settings define how transaction log files are to be managed. • SQL Server supports three different database recovery settings: • Simple • Bulk-log • Full SQL 710

  7. Simple DB Recovery Setting • Data is removed from the log once it has been successfully written to the database file • Appropriate recovery setting for non-critical databases • Keeps the transaction log files small • However if database file becomes corrupted any changes made to the database since the last backup was made will be lost! SQL 710

  8. Full DB Recovery Setting • Safest option but has most overhead because of 3 step process for every transaction • Committed data is marked but not erased after writing to database file and remains on log file. • Log files may grow until the disk runs out of space • Successful backup of whole database or log file truncates (removes committed transactions) transaction log files • Full option is exactly like bulk-log except it doesn’t allow bypassing of transaction log SQL 710

  9. Bulk-log DB Recovery Setting • Bulk-log exactly like full recovery except it allows bypassing of transaction log by using special commands. • Logging is normally bypassed for bulk inserts of large amounts of stored data stored (eg batch processing) or actions like SELECT INTO based on another large table. • Bypassing logging is a recovery compromise but it greatly improves performance. However there is no transaction log so a backup should be made after the actions performed with bypass of logging are complete! SQL 710

  10. Database Growth Settings • SQL Server stores data in physical database and log files. The growth of these files can be managed in two different ways: • Specify a size for the files • Allow them to grow automatically SQL 710

  11. Database Growth Settings • Specify a size for the files • Limit the size the files can attain • Physical files and page files should not be on same partition. • If files attain the maximum size you can add another file SQL 710

  12. Automatic Growth DB Setting • Specify growth by percentage or by MB • Percentage growth – increase grows as database size grows; e.g. for 10% auto growth when database is 10 MB , growth is 1 MB, when database is 100 MB , growth is 10 MB, … • Must monitor growth regularly otherwise database can take up entire hard drive space SQL 710

  13. Tools to Access Data • SQL Server Tools: Enterprise Manager and Query Analyzer (not end user tools though!) • ODBC (Open Database Connectivity): older method • OLE-DB (sometimes refers to as DSN-Less) SQL 710

  14. ODBC • ODBC is software that is configured to connect to a specific server and a database • 2. Has two parts: • - A program designed to use ODBC • - A driver located in each user system • 3.Each configuration within the ODBC driver is called Data Source Name(DSN) • 4. MDAC Setup (a version of ODBC) comes with SQL Server 2000 SQL 710

  15. DSN • DSN is a collection of settings that access the • database from a client. There are three types of • DSN: • User DSN: allows access only to the current user • System DSN: allows access to any one logged onto the system • File DSN: is a file that contains the connection information. It requires the connection to make a trip to the file system each time it’s called. SQL 710

  16. OLE-DB • Uses APIs • DSN-less method • Used on server applications like web-hosted databases • The developer of the program may include a configuration utility SQL 710

  17. Programs and DB Access • Applications that connect to SQL Server include: • Microsoft Office: Almost all of Microsoft Office can access data from SQL Server 2000 database using ODBC or OLE-DB methods • Visual Basic: You can access SQL Server 2000 using many programming languages using ODBC or OLE-DB • Web Pages: More and more SQL Server data is accessed through web pages • Command Line: Command line tools are good when you access your server remotely and the connection is slow. Examples of command line tools are Query Analyzer and OSQL. SQL 710

  18. Database Options Database Options in Enterprise Manager controls database settings. To see settings: Right click on database name, click Properties SQL 710

  19. Database Options General : Displays general information about your database Data Files: 1. Add or delete data files(.mdf) 2. Change file growth settings: - by MB (fixed chunk added) - by percentage to be increased 3. Set maximum file size SQL 710

  20. Database Options (ctd) Transaction Log: 1. Add or delete transaction log files(.ldf) 2. Change file growth settings: - in MB - by percentage 3. Set maximum file size SQL 710

  21. Database Options (ctd) • File Groups : • Logically arrange files using options: • Restrict access • Read only • Recovery model • Compatibility SQL 710

  22. The Web Assistant SQL Server's Web Assistant tool creates read-only pages for web browsers SQL 710

  23. Exercises • Set up an ODBC Connection • Create Database by using Enterprise Manager, and by using Query Analyzer • Set Database Options • Use SQL Tools Query Analyzer to access data • Create a script • Create a web page using The Web Assistant SQL 710

More Related