Web Hosting Java Blog

114 Part I EXAM PREPARATION continued (NAME = LogStore , FILENAME = e:log directoryLogStore_MyDatabase.ldf , SIZE = 1MB, MAXSIZE = 5MB, FILEGROWTH = 1MB) You can use the CREATE DATABASE statement to create a database from script. Saving the script enables you to re-create a similar database on another server in the future. Any SQL Server object can have its creation script saved. The advantages of saving these scripts are discussed later in this chapter. Using the CREATE DATABASE statement to create a database using multiple files and log files would look similar to this: CREATE DATABASE Example ON PRIMARY ( NAME = ExampleData, FILENAME = c:mssqldatasampdat.mdf , SIZE = 10MB, MAXSIZE = 20MB, FILEGROWTH = 2MB), ( NAME = ExampleIndexes, FILENAME = c:mssqldatasampind2.ndf , SIZE = 10MB, MAXSIZE = 20MB, FILEGROWTH = 2MB), ( NAME = ExampleArchive, FILENAME = c:mssqldatasamparch.ndf , SIZE = 10MB, MAXSIZE = 20MB, FILEGROWTH = 2MB) LOG ON ( NAME = ExampleLog1, FILENAME = d:mssqllogsamplog1.ldf , SIZE = 10MB, MAXSIZE = 20MB, FILEGROWTH = 2MB), ( NAME = ExampleLog2, FILENAME = d:mssqllogsamplog2.ldf , SIZE = 10MB, MAXSIZE = 20MB, FILEGROWTH = 2MB) Important issues with regard to appropriate use of the CREATE DATABASE statement are as follows: The default growth increment measure is MB, but can also be specified with a KB or a % suffix. When % is specified, the growth increment size is the specified percentage of the size of the file at the time the increment occurs. A maximum of 32,767 databases can be defined on a server. The minimum size for a log file is 512K.

For high quality website hosting services please check cheap web hosting website.

Chapter 3 PHYSICAL DATABASE DESIGN AND IMPLEMENTATION 113 STEP BY STEP 3.1 Creating a Database Using the Create Database Wizard 1. Expand a server group, and then select the server in which to create a database. 2. On the Tools menu, click Wizards. 3. Expand Database. 4. Double-click Create Database Wizard. 5. Complete the steps in the wizard. Or, if you prefer to use the Enterprise Manager: STEP BY STEP 3.2 Creating a Database Using the Enterprise Manager 1. Expand a server group and then the server where the database is to be placed. 2. Right-click Databases, and then click New Database. 3. Enter a name for the database. 4. To specify how any specific file should grow, switch to the Data Files or Transaction Log tabs, highlight the file, select Automatically Grow File, then choose In Megabytes or By Percent and specify a value. To specify the file size limit, select Unrestricted Filegrowth or Restrict Filegrowth (MB) and specify a value. To use T-SQL to create a database, use this code: CREATE DATABASE MyDatabase ON (NAME = DataStore , FILENAME = d:data directoryDataStore_MyDatabase.mdf , SIZE = 1MB, MAXSIZE = 5MB, FILEGROWTH = 1MB) LOG ON continues

For high quality jboss hosting services please check jboss web hosting website.

112 Part I EXAM PREPARATION The file names given to all data and log files can be any desired name, although it is recommended that you select a name that gives some indication as to the content of the file. The file extensions for the primary data file, secondary data file(s), and log files can also be any chosen set of characters. It is recommended for consistency and standardization that the extensions be .mdf, .ndf, and .ldf for the primary, secondary, and log files, respectively. Creating Database Files and Filegroups All files needed for a database can be created through a single activity using SQL Server s Enterprise Manager, or with a single CREATE DATABASE Transact SQL statement. Either of these methods can be used to initialize all files and create the database and logs in a single step. The number of files you create and the placement of the files are addressed a little later in this chapter. (See the sections, Using Filegroups and File Placement. ) The default names for the primary database and transaction log files are created using the database name you specified as the prefix for example, NewDatabase_Data.mdf and NewDatabase_Log.ldf. These names and locations can be changed if desired from the default values provided for the new database file. The Transact SQL (T-SQL) syntax for creating a database is as follows: CREATE DATABASE databasename [ON[[,…n]][,[,…n ]]][LOG .ON{[,…n ]}] [COLLATE collationname][FOR LOAD| FOR ATTACH] ::= [PRIMARY] ([NAME=logicalfilename,][FILENAME= physicalfilename ] .[,SIZE=size][,MAXSIZE={size| UNLIMITED}] [,FILEGROWTH=growthincrement]) [,…n ]::=FILEGROUP filegroupname .[,…n ]] In the procedures that follow, you have the opportunity to create a database one step at a time. There are two techniques that can be used for these procedures. The first option uses the Create Database Wizard tool and Enterprise Manager in Step by Steps 3.1 and 3.2. The second option available provides for the syntax for the creation of a database using T-SQL code.

If you looking for unlimited one inclusive web hosting plan please check unlimited web hosting website.

Chapter 3 PHYSICAL DATABASE DESIGN AND IMPLEMENTATION 111 covers the exam objectives for the physical database design section of the Database Development SQL Server 2000 exam, including the hardware, the operating system, SQL Server, the database, all database objects, and the application. Many of these features are overviewed in this chapter but are further defined in other chapters throughout the blog. CREATING AND ALTERING DATABASES . Create and alter databases. Considerations include file groups, file placement, growth strategy, and space requirements. The file format in SQL Server 2000 has not significantly changed from the previous version (SQL Server 7). SQL Server uses a set of files to store the data, indexes, and log information for a database. A primary file also has some header information in it, providing SQL Server with necessary information about a database. Each database has a minimum of two files associated with it, one for the data and a second for the log. It is also possible to create multiple files for each of these purposes as described in the following paragraphs. File placement, and object placement within these files, plays an important role in the responsiveness of SQL Server. A database consists of two or more files with each file used for only a single database. A single file cannot be shared by multiple databases. Each database has one or more files used to store indexes and data. The first file created for this purpose is referred to as the primary file. The primary file contains the information needed to start up a database and is also used to store some or all of the data. If desired, secondary files might be created to hold some of the data and other objects. Some databases might be large enough or complex enough in their design to have multiple secondary files used for storage. Normally the log is maintained in a single file. The log file is used to store changes to the database before these changes are recorded in the data files themselves. The storage of information into log files in this manner enables SQL Server to use these files as an important part of its recovery process. Every time the SQL Server is started, it uses the log files for each of the databases to determine what units of work were still being handled at the time the server was stopped.

For high quality java hosting services please check java web hosting website.

110 Part I EXAM PREPARATION INTRODUCTION Chapter 1, Introduction to SQL Server 2000, looked at the modeling considerations and the logical structure of a database design. In moving from an idea to a logical structure to the actual physical elements, you must remember to consider elements that contribute to performance, reliability, and data integrity. Having a model of the system is one thing, but it must be able to meet the demands of an environment where inevitably the system must meet the intended goals of the company and add to the bottom line. This chapter explores the aspects of the physical database design. It compares and contrasts the physical design and logical data modeling of Chapter 1 and then proceeds towards the implementation of a database system. Included are discussions of the file system, data structure, index structure, SQL Server objects, hardware, and finally, troubleshooting of the database design. Of particular interest to anyone preparing for the exam will be the discussion of schema binding and data partitioning. Because these two topics represent functionality that has been provided over and above the previous version, you can certainly expect questions on these areas when you take the exam. In any physical design, the goal is to provide an efficient and responsive database system that also lends itself to appropriate maintenance tasks without becoming a database administrator s burden. At this stage of a database implementation, care is taken to provide a system structure that is usable, provides for optimum user response time, can be readily maintained, and above all meets the needs of the business for which it was designed. As each of the physical design topics are discussed, pay close attention to the options that are available. Many different database technologies can be applied. Selecting the most appropriate technique to apply, based on what the problem warrants, is a very imperfect science. To become proficient in all these technologies, a developer must have a diverse background covering many data solutions. It would be beneficial, therefore, to try to experiment with as many different types of applications as possible. You will find that a manufacturing solution is considerably different from an online sales-oriented solution, which differs as well from a scientific application s solution, and so on. The exam covers physical elements in numerous areas. The material contained in this chapter hits many of the exam topics. This chapter

If you looking for unlimited one inclusive web hosting plan please check unlimited web hosting website.

STUDY STRATEGIES . Ensure that you have a thorough understanding of the variety of objects and technologies available within the realm of physical design. Know what each technique accomplishes (advantages) and also watch out for associated pitfalls (disadvantages). . Understand the basics of the file system and its use by SQL Server. Know when to split off a portion of the database structure and storage to a separate physical disk drive. . Know the interaction between SQL Server and the OS (operating system). Some of the physical design concepts that are discussed point out the role that the OS performs and the reason for its participation. . Recognize the changes to the actual data structure and other areas of the database definition that might occur. Some technologies impact the database schema by applying their own objects. . Watch out for What s new in SQL Server 2000. Typically the exam tests on new features within the software, and this exam is certainly no different. The discussion of physical design topics reviews many important design and exam criteria, many of which are new features.

For reliable and cheap web hosting services please check javaweb hosting website.

OBJECTIVES OUTLINE . An absolute necessity of building databases that interact with SQL Server 2000 is using the appropriate database objects to obtain a usable database system while improving response times and maintaining data integrity. There are considerations and trade-offs for choosing one technique over the other. The selection of the most appropriate method to obtain the desired result requires that you know where each is best implemented. The exam will test on the appropriate application of each of these objects. Alter database objects to support replication and partitioned views. Support merge, snapshot, and transactional replication models. Design a partitioning strategy. Design and create constraints and views. Resolve replication conflicts. . A variety of technologies exist in handling multiple server environments, knowing what each technology offers as well its restrictions helps you adapt a database system applied across multiple machines. Considerations for controlling data alterations, having the data available when needed, and responding to queries in a timely manner will be the aim of questions within this area of the exam. Troubleshoot failed object creation. . Troubleshooting is certainly a broad topic. In this chapter, the focus for troubleshooting is on the interactions with objects and the server as well as application settings that are required for an object to be created and used. On the exam, troubleshooting will be approached from a wide variety of angles. In the real world, it is good practice to always view a procedure from a problem-solving perspective. Always be ready to ask yourself, What could go wrong? and What can be done to resolve the problem? Creating and Altering Databases 111 Creating Database Files and Filegroups 112 Using Filegroups 117 File Placement 119 Space Requirements 126 Creating and Altering Database Objects 129 Table Characteristics 130 Application of Integrity Options 155 Multiple Server Implementations 158 Use of Replication 158 Partitioning to Achieve a Balance 159 Troubleshooting SQL Server Objects 161 Setting Server Configuration Options 162 Setting Database Configuration Options 170 Setting the Database Options 176 Apply Your Knowledge 181 Exercises 181 Review Questions 183 Exam Questions 183 Answers to Review Questions 188 Answers to Exam Questions 188

For high quality java hosting services please check tomcat web hosting website.

OBJECTIVES Create and alter databases. Considerations include file groups, file placement, growth strategy, and space requirements. Specify space management parameters. Parameters include autoshrink, growth increment, initial size, and maxsize. Specify file group and file placement. Considerations include logical and physical file placement. Specify transaction log placement. Considerations include bulk load operations and performance. . The placement of the files related to a SQL Server 2000 database environment helps to ensure optimum performance while minimizing administration. Recoverability can also be improved in the event of data corruption or hardware failures if appropriate measures are taken. On the exam, you must be prepared to respond to these requirements and properly configure the interactions with the file system. Create and alter database objects. Objects include constraints, indexes, stored procedures, tables, triggers, user-defined functions, and views. Specify table characteristics. Characteristics include cascading actions, CHECK constraints, clustered, defaults, FILLFACTOR, foreign keys, nonclustered, primary key, and UNIQUE constraints. Specify schema binding and encryption for stored procedures, triggers, user-defined functions, and views. Specify recompile settings for stored procedures. Specify index characteristics. Characteristics include clustered, FILLFACTOR, nonclustered, and uniqueness. CHAPTER 3 Physical Database Design and Implementation

For high quality website hosting services please check java web hosting website.

106 Part I EXAM PREPARATION A PPLY YOUR K NO WLEDGE tion because you already have an IIS server available. Whether you have Internet access or not is irrelevant because everything can be performed in-house using an HTML or XML solution across the network. For more information, see the Internet Applications section. 4. A, C. By definition timestamp and unique identifier data types are guaranteed to be globally unique. The timestamp is an automatically entered value. The uniqueidentifier is usually entered using a NEWID() function to generate the uniqueidentifier. For more details, see the Using Appropriate Data Types section. 5. A, C, D. An Identity provides for uniqueness by incrementing a value continually, and therefore it is a standard choice for a column that requires a unique value. Unique indexes and unique constraints enforce the uniqueness of entered values and do not let any entry come into the system that already exists. For more information, see the section titled Entity Integrity. 6. A, C. Fullname can be broken down into first- name and lastname and is therefore not in its most decomposed form. This breaks the first normal form rule of normalization. The salesman name should not be stored in this entity because it depends on the salesmanID and not the customerID. This breaks the third normal form rule of normalization. For more information, see Understanding Database Normalization. 7. B. An automobile s VIN number, though unique, is character data and is much too large to use as a Primary Key. This is a perfect situation for an automatically incremented numeric surrogate key that will take up a lot less storage space. For more information, consult the section on Primary Keys. 8. D. According to byte sizes, int would take a quarter of the space of the current nchar(5) setting. Smallint would even be better, but has an upper limit of 32,767. Char(5) would cut the space used in half but is not as good as using actual numeric storage. Whenever a variable is going to contain only numbers, numeric storage is always more efficient. For more details, see the Using Appropriate Data Types section. 9. D. This is a tricky question to resolve, and if it were not for the space restriction there would be a temptation to use characters for the storage. At 8 bytes a piece (double that of int) the easier technique would be to track days from the beginning of recorded time in an integer. (2001 1300) 365 1/4 requires 6 digits, and therefore int is the closest to the size required. Datetime allows dates in only the 1700s and smalldatetime in the 1900s. For more information, see the Using Appropriate Data Types section. 10. B. The many-to-many relationship in this scenario occurs because many contractors can work on a Suggested Readings and Resources single site, and a single contractor can work at many sites. The connection needs to involve both sites and contractors for an appropriate relationship to be drawn. For further information, refer to the Understanding Entity Relationships section. 11. C. SQL Server is ideal for this situation.

For high quality website hosting services please check tomcat web hosting website.

106 Part I EXAM PREPARATION A PPLY YOUR K NO WLEDGE tion because you already have an IIS server available. Whether you have Internet access or not is irrelevant because everything can be performed in-house using an HTML or XML solution across the network. For more information, see the Internet Applications section. 4. A, C. By definition timestamp and unique identifier data types are guaranteed to be globally unique. The timestamp is an automatically entered value. The uniqueidentifier is usually entered using a NEWID() function to generate the uniqueidentifier. For more details, see the Using Appropriate Data Types section. 5. A, C, D. An Identity provides for uniqueness by incrementing a value continually, and therefore it is a standard choice for a column that requires a unique value. Unique indexes and unique constraints enforce the uniqueness of entered values and do not let any entry come into the system that already exists. For more information, see the section titled Entity Integrity. 6. A, C. Fullname can be broken down into first- name and lastname and is therefore not in its most decomposed form. This breaks the first normal form rule of normalization. The salesman name should not be stored in this entity because it depends on the salesmanID and not the customerID. This breaks the third normal form rule of normalization. For more information, see Understanding Database Normalization. 7. B. An automobile s VIN number, though unique, is character data and is much too large to use as a Primary Key. This is a perfect situation for an automatically incremented numeric surrogate key that will take up a lot less storage space. For more information, consult the section on Primary Keys. 8. D. According to byte sizes, int would take a quarter of the space of the current nchar(5) setting. Smallint would even be better, but has an upper limit of 32,767. Char(5) would cut the space used in half but is not as good as using actual numeric storage. Whenever a variable is going to contain only numbers, numeric storage is always more efficient. For more details, see the Using Appropriate Data Types section. 9. D. This is a tricky question to resolve, and if it were not for the space restriction there would be a temptation to use characters for the storage. At 8 bytes a piece (double that of int) the easier technique would be to track days from the beginning of recorded time in an integer. (2001 1300) 365 1/4 requires 6 digits, and therefore int is the closest to the size required. Datetime allows dates in only the 1700s and smalldatetime in the 1900s. For more information, see the Using Appropriate Data Types section. 10. B. The many-to-many relationship in this scenario occurs because many contractors can work on a Suggested Readings and Resources single site, and a single contractor can work at many sites. The connection needs to involve both sites and contractors for an appropriate relationship to be drawn. For further information, refer to the Understanding Entity Relationships section. 11. C. SQL Server is ideal for this situation.

For high quality java hosting services please check java web hosting website.