SQL Server 2008

We are working on a .Net project in which we are using SQL Server 2008 as a database for Online processing and reporting. The tables are normalized and the same tables are used for Online processing and reporting. The screens are designed and developed using .Net technology (aspx pages). We don’t have SQL’s in the pages. All access to the database is done using Stored Procedures. Currently we have only one database. Historical records are stored in the same table. There can only be one current record in a table. We don’t do physical deletes, only logical deletes. We use effective date, end date and a flag for differentiating between current record versus historical record versus logical deleted records. We have approximately 30 tables that we have created and are using 10 tables from Microsoft .Net User Management application. All these tables are in the same database. We have many stored procedures that we have written. In most of the tables we have created Primary Key’s but no index has been created. We will be adding another 40-50 tables in next couple of months and many more stored procedures. We are looking at the following task to be accomplished as part of this project. I am looking for someone who has worked on similar tasks before and who is willing to work full time on this project. The query execution time should be excellent. I am attaching couple of tables and stored procedures to give you an idea. • Review the table layout and suggest if we should create relationships between tables by using Primary Key and Foreign Key • Based on the suggestion and if required create relationship between tables by using Primary Key and Foreign Key • Generate a data model in pdf format showing all the relationship • Look at all the stored procedures to make sure that the stored procedures follows industry best practices and is consistent. Check for redundant stored procedures. • Look at all the Select queries in the Stored Procedure for the access path to come up with the index requirements • Create Indexes on the tables • Optimize the queries for response time. If required modify the query to improve the performance without impacting the business logic. • Create partition if required. This may not be required now because of the size of the database. • Database and table sizing based on the projected volume • Create backup and restore procedures of the database/tables. This procedures should be configurable. The configuration should have at minimum the following parameters: Date, Time, Table Name to backup, Comb-Id (this id identifies an entity and is in all the tables), Entity Name (derived based on Comb-Id). • We should be able to schedule the data backup or do the data backup in real time. I would like to know the impact of real time backup on the Online processing system. • Document all the stored procedures. I would like to see in documentation the following: Stored Procedure Name, Input, Output, Tables Used and Function (Select, Insert, Update, Delete) • Suggest a road map for the new tables and stored procedures. [REMOVED BY GAF ADMIN] Thanks, Tuhin Verma