R710 SqL virtualization

Creating the database we modified the database

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: _20gb.sh We waited until the processes finished before we moved onto the next step. Creating the database We modified the database creation SQL Server scripts in the DVD Store distribution package to build the database schema, which includes the file structure, tables, indices, stored procedures, triggers, and so on. We built a master copy of the 20GB database version for SQL Server 2008, and then used that master copy to restore our test database to each test server VM between each test run. We stored the backup file on the VM C: drive for quick access. We followed these steps to create the database: 1. We created the database and file structure using database creation scripts provided with DS2. We made size modifications specific to our 20GB database and the appropriate changes to drive letters. 2. We created database tables, stored procedures, and objects. 3. We set the database recovery model to bulk-logged to prevent excess logging. 4. We loaded the data that the database generated. For data loading, we used the import wizard in SQL Server Management Studio. Where necessary, we retained options from the original scripts, such as Enable Identity Insert. 5. We created indices, full-text catalogs, primary keys, and foreign keys using the database-creation scripts. 6. We updated statistics on each table according to database-creation scripts, which sample 18 percent of the table data. 7. We created ds2user SQL Server login and user for testing using the following Transact SQL (TSQL) script. USE [master] GO CREATE LOGIN [ds2user] WITH PASSWORD=N’’, DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO 8. We set the database recovery model back to full. We made the following changes in the build scripts: • Because we varied the size of the datasets, we sized the files in our scripts to reflect the database size and the number of files per filegroup. We allowed for approximately 40 percent free space in our database files to ensure that filegrowth activity did not occur during the testing. Principled Technologies, Inc.: Virtualized OLTP workload performance comparison of end-to-end solutions: Dell PowerEdge R710 with Dell EqualLogic storage vs. HP ProLiant DL385 G5 with HP StorageWorks EVA 4400 storage 15 • • • • • • We followed Microsoft’s recommendation of having 0.25 to 1 file per filegroup per core. We used two files per filegroup on our 2 virtual processor VMs. We did not use the DBCC PINTABLE command for the CATEGORIES and PRODUCTS tables, both because Microsoft recommends against this practice and because the commands do nothing in SQL Server 2008. We added the FORCESEEK hint in the BROWSE_BY_ACTOR stored procedure because in initial testing we found that SQL Server 2008 was choosing an index scan instead of the preferred index seek, adding unnecessary overhead. We created a SQL Server login called ds2user and mapped a database user to this login. We made each such user a member of the db_owner fixed database role. Using the DVD Store scripts as a referen...
View Full Document

Ask a homework question - tutors are online