Sunday, 20 March 2011

How Do You Create A Database?

There was a question on MSDN, that paraphrased, asked what steps you need to go through to create a database. Now, of course the exact steps will be different in every situation, but there are certain things that people often miss, so I though I would compile a 15-point list of the high level steps that you should take -

1) Consult the business to decide on what data you need to store, estimated volumes, how it will be used, how many users, etc. (You should try to predict 3 years into the future)

2) Go through conceptual/logical design. i.e. Normalization, ERD diagrams, etc.

3) Decide what technology will suite your application best. Is this SQL Server (normally in my bias opinion! ;-) ), or is it Oracle, MySQL, etc.

4) Decide what hardware spec you will require to support database application, and what software versions you need. i.e. Windows, SQL editions, etc.

5) Design the physical table structure, including data types, compression, etc.

6) Design how you will get your data your data in and how you will get your data out of the database. This will involve logical steps agreed with the business, and may include physical technologies, such as SSIS, Stored Procs, Functions, Endpoints, Linked Servers, BCP, etc, etc.

7) Create physical database, specifying files, filegroups, etc.

8) Create physical structures, such as tables, programmable objects, etc.

9) Design security policies, and ensure the principle of least privilege is followed.

10) Agree SLAs with the business owners

11) Design HA and DR strategies for database, so you can set appropriate Recovery Model, configure Mirroring, etc.

12) Go through SAT cycle on Dev environment. Test code functionality, performance, HA strategy, recovery times, etc.

13) Promote database to UAT environment through Backup/Restore, Scripts, or Copy Database wizard, etc.

14) Make sure business fully test and sign-off functionality.

15) Promote to Live.

Find my book, Pro SQL Server Administration on Amazon -


United Kingdom

No comments:

Post a Comment