Wednesday, May 30, 2007

MSSQL 2005 Knowledge Base 1

-- Estimating table storage requirements
1. Calculate the space used by a single row of the table.
Row_Size = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap + Row_Header
Variable_data_size = 2 + (num_variable_columns × 2) + max_varchar_size
Null_bitmap = 2 + ((number of columns + 7) ÷ 8)
Row_Header = 4
2. Calculate the number of rows that will fit on one page.
8096 ÷ (RowSize + 2)
3. Estimate the number of rows the table will hold.
4. Calculate the total number of pages that will be required to hold these rows.
Number of rows in the table ÷ number of rows per page (rounded up to the nearest whole number).
5. Multiply the number of pages by the actual size of a page (8,192 bytes),

-- Partitioning table
1. Creating a partition function
2. Creating a partition scheme
3. Creating table using partitioned scheme

-- Implementing indexes
1. Specify the filegroups
2. Specify the index type
3. Specify the relational index option
4. Specify the columns
5. Specify the partition scheme when creating an index
6. Creating an online index by using an ONLINE argument

-- Implementing a full-text search
1. Creating a full-text search catalog
2. Creating an index
3. Specify a full-text population method

-- Implementing a Service Broker Architecture
1. Enable broker architecture
2. Create message type on initiator and target
3. Create contract
4. Create queues
5. Create service on the sender and recipient
6. Create a conversation dialog to send messages
7. Alter queue for automatic activation & create store procedure to process messages

-- Set up database mirroring
1. Prepare for mirroring
a. Configure security and communication between instances
b. Creating the Mirror Database
c. Establish a Mirror session
2. Creating Endpoints
3. Specifying Partners and Witnesses
a. Configure the pricipal as a partner on the mirror
b. Configure the mirror as a partner on the pricipal
c. Optionally configure the witness
4. Configuring the operating mode
5. Switching roles

-- Implementing Log Shipping
1. Backup the transaction log on the primary database
2. Copy the log files to the secondary database
3. Restore the log file onto the secondary server

-- Changing roles in log shipping
1. Manually failover the primary server to the secondary server
2. Disable the log shipping backup job on the primary server, copy and restore opetions on the secondary server
3. Enable log shipping job on the secondary server

Latest Posts