locked
how do you know when... RRS feed

  • Question

  • Hi every one, i have simple yet complicate Question. Is there a way or a list to follow when you are creating a database in sql server 2008. i mean say you want to tell your company that this is the steps i will go through to make the database. you know like you build a house and you know you have to level the ground put the water pipes and electricity pipes and base of the house. hit me back with the answer please. 

    Regards 

    and good luck ;)


    Thursday, February 10, 2011 6:15 PM

Answers

  • Well, this is difficult to answer, because every database, in every project in every company is different. However, as a rough, high level list:

    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)

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

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

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

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

    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.

    Create physical database, specifying files, filegroups, etc.

    Create physical structures, such as tables, programable objects, etc.

    Design security policies, and ensure the principle of least privaledge is followed.

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

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

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

    Make sure business fully test and sign-off functionality.

    Promote to Live.

    This is off the top of my head, so if I have missed something, I appologise, but as a high level list, I think it's fairly comprehensive. Hope it helps. :)


    Peter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/
    Thursday, February 10, 2011 9:39 PM
  • Great list. One minor comment. As a security guy, I would move security up on this list. I would place security with "Design the physical table structure..." If you wait until later you may have to redesign your physical structures again.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    • Marked as answer by WeiLin Qiao Monday, February 21, 2011 12:31 PM
    Thursday, February 10, 2011 10:19 PM
    Answerer

All replies

  • Well, this is difficult to answer, because every database, in every project in every company is different. However, as a rough, high level list:

    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)

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

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

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

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

    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.

    Create physical database, specifying files, filegroups, etc.

    Create physical structures, such as tables, programable objects, etc.

    Design security policies, and ensure the principle of least privaledge is followed.

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

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

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

    Make sure business fully test and sign-off functionality.

    Promote to Live.

    This is off the top of my head, so if I have missed something, I appologise, but as a high level list, I think it's fairly comprehensive. Hope it helps. :)


    Peter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/
    Thursday, February 10, 2011 9:39 PM
  • Great list. One minor comment. As a security guy, I would move security up on this list. I would place security with "Design the physical table structure..." If you wait until later you may have to redesign your physical structures again.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    • Marked as answer by WeiLin Qiao Monday, February 21, 2011 12:31 PM
    Thursday, February 10, 2011 10:19 PM
    Answerer
  • Thanks Rick, and yes, I agree, and am slapping my wrist as I type! ;-)

    But seriously, it should move up the list, and remember, it does not just apply to DB Roles and user permissions. You also need to take things such as endpoint security, authetication mode, encryption, physical server security, etc, depending on the project's requirements.


    Peter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/
    Thursday, February 10, 2011 10:38 PM
  • Hi Guy Both of YOu guys are Grt help and Pete thanx for writing every thing....

    i can't thinks enough guy and sorry about not getting back early

    i hope i can have contact with you guys in future for more SQL love...lol

    Best Regards

    Mann

    Wednesday, March 9, 2011 7:01 AM