What to do when asked to create a database. First Steps

Someone has asked you to create a database for _____. Now What? Where do you go?

Step 1: What is the database for?

  • What will the database be used for? Is this a Data Warehouse, Data Mart, Reporting, or OLTP database?
  • If this database will be attached to a 3rd party tool, what are the tool requirements for the database?
  • If this database will be attached to an in house tool, what are the requirements? Don’t worry if they do not have any requirements. Sometimes we DBAs have to create the requirements for the developers.

Step 2: What version and edition of SQL Server?

  • If a 3rd party tool, what is the version the tool must have?
  • If this is the first time this database was ever created or you are designing the database. Ask management the following questions. Their answers will help decide which edition or version of SQL Server to use.
    • What is the SLA (Service Level Agreement) for up time of the instance? If there is not an SLA, create one and have them sign it. CYA (Cover your ass’ets’) . The SLA will cover topics that you need in order to decide the version of SQL Server you need.
    • What is type of High Availability do they want? Always-On? Replication?
    • What type of Failover and Disaster Recovery? Cluster? Replication? Always-on
    • Are there any special functions or SSIS/Visual Studio the developers would like to use? What versions of SQL Server work with that tool?
    • Is there a reporting tool the developers would like to use? What versions of SQL Server work with that tool?
    • Prepare a spreadsheet
      • create column headers for each edition: Standard, Enterprise, Developer, or Express (include all four editions for each version of SQL Server being evaluated) (I usually pick the most current version of SQL Server, unless directed otherwise by management.)
      • In the first row, list the license requirements and cost for each edition
      • List in each row the requirements management wants
      • Place an “X” where the edition includes the requirement.
      • Figure out your recommendation.
      • Now let management decide which edition to use.

Step 3: How much disk space is needed for the data file, index files, temp files, log files, and system files?

  • If this new database is for a 3rd party tool, find the tool database requirements.
  • If this is a new database for an internal application and the application is not designed yet. Sizing will be decided during development. In this case, sizing is an art. There are tons of blogs on how to design a database. Time to Google.
  • If this is for reporting and the database has not been designed, again turn to google to design the data mart or data warehouse.
  • If this is a current database in another edition/version of SQL Server, what is the current disk space? Is the current disk space sufficient to hold and grow for the next six months?
  • The System Admin will need you to email all the drives you would like and how much space on each one. In the old days before SAN, placement of files mattered. Now just decide how you would like to separate the files for viewing purposes. If there are no directly connected hard drives, then all the files go into one big storage server.
  • I usually have C drive for the OS, D drive for SQL Server installation and any other application installations, L drive for logs, T drive for temp files, S drive for System files, I drive for index files, and P drive for data files. Backups I place in the P drive and then move to another network directory on a separate SAN. Separate out your files however it makes logical sense to you. Try to keep consistent between all the database servers. Maybe there is a company document on what drives to place the files. Be consistent, because consistency makes finding files for maintenance easier.

 

Step 4: How many processors (CPU)?

  • If this new database is for a 3rd party tool, find the tool database requirements.
  • If this is a current database , What is the current core count? Are the cores sufficient to run the current databases on that server?
  • Do they expect a change in usage of this database?
  • If there are no changes in usage and the server runs find with the current number of processors, then stay with the same number of processors.
  • Will there be an upgrade in the Windows OS? This could affect the number of cores needed.
  • Will there be any other applications running on this VM? I hope not. Fight to keep this server for SQL only. If you lose, make sure management knows that Sql Server does its own memory and core management. The database will be slower because the application will steal resources when competing with SQL Server.

 

Step 5: How much memory?

  • If this new database is for a 3rd party tool, find the tool database requirements.
  • If this is a current database , What is the current memory? Are there any memory waits during peak times on the current database?
  • If there are memory waits during peak times, then increase the memory. This will have to be a guesstimate.
  • Will there be an upgrade in the Windows OS? This could affect the amount of memory needed. Always leave 10% of the memory for OS, the rest can be SQL Server.
  • Will there be any other applications running on this VM? I hope not, but if so the memory will need to be increased to hold the application and SQL Server.

 

In my next blog I will discuss the SQL Server Instance and Database internal settings. I hope these questions helped to decide what you need in order to install SQL Server successfully.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s