New SQL 2005/2008 installation

When accepting such a role in a company you should be aware that you will not always be seen as  the nicest person that everyone will want to communicate over a coffee and create friendships. The DBA is usually the man/woman who solves problems as we speak, but in the same time – he is the same man who will be the first held responsible without analyzing other factors when something goes wrong with the database or server administration. The DBA is the man who must discover what goes wrong, what to do to solve the problem immediately, and how to prevent such situations coming back. It’s frustrating but mostly it’s nice and fun and quite often you get a chance to demonstrate your intelligence and technical skills and receive appropriate feedback when you are really good at what you’re doing.Now, this part with “really good” is hard to get. You don’t learn from books internet or universities how to be a good DBA. There are not many people who can teach you the best practices. And generally, there aren’t many DBA’s in a company given the big responsibility and trust offered. Basically you just need to have a global vision of the entire system that you manage and treat very seriously any problem. That’s what it is. A DBA deals with the problems, anticipates them and solves them. Funny? I say yes …

Although there are many articles, a DBA really learns a lot practicing on a daily basis, bravely facing any problem that arises or is presented by your technical or functional team. Nobody is really made for it, but some feel that it suits them. I opened this blog to write about the problems I face or had and I lost time to find the solution. I practice this job for almost five years and I turned into many difficult situations and I got helped a lot by other bloggers with similar problems, whom I am thankful. Although never a problem that seems just like another will have the same solution, there is always one for yours.

For starters I want to describe what should you do if you’re on a new installation of SQL 2005/2008. It’s always important who did the installation and how the server is configured. Some bad configurations, unfortunately can only be securely undone after a re-install.

If you’re on a new installation of SQL 2005 or SQL 2008: The installation process is very different from 2005-2008 but it is important to consider the following for both these installations:

1. Installation options

  1. Be careful of what edition of SQL you plan to install and on what type of hardware (32 bit or 64 bit). As a rule, Enterprise is installed for the production environment, Developer for development environments and Standard for production but with a lot limitations compared to Enterprise features. It’s good to check what version is required before the application is deployed, because the costs differ substantially.
  2. Check with the DEV team, if you need all the components proposed in the kit. SQL Server is a high consuming resources software – especially in what memory is concerned. For example: if you do not need Analysis Services (for data warehouse) do not install it. If needed you will be able to install it later.
  3. If you want to install multiple instances on the same machine, you should give a different name for you instance instead of using the DEFAULT one. Default instance ID will be MSSQLSERVER and the Named Instance will be what you want it to be – ex: MYINSTANCE.

2. Startup Accounts

  1. The 2005 install will ask you to insert the details for the Service’s account used to run SQL Server. You can use the Local System account, Domain System account if the server is connected to a domain and you previously created a Service account specifically for this purpose in Active Directory or you can use the Domain System account option to insert a local user created for this purpose with Administrator role on the server. After installation you can change the Service Accounts to your components installed from SQL Server Configuration Manager or Services from Administrative Tools.
  2. In the 2008 install you can select in the same window: the Service Accounts for all components installed, their startup type (Manual/ Automatic/Disabled) as well as the Collation type used at the server level (very important this step). To find out what type of collation you need, check the documentation of the application deployed or consult the DEV team. If no type of Collation is specified, use the default one.

3. Security Models

The next step is to select an authentication mode supported by SQL Server: Mixed mode (SQL Windows) or Windows. The type of authentication is very important in determining the way your users access the system. Microsoft’s recommendation is as much as possible, to use Windows authentication mode because it is the safest mode. With this type of authentication, user’s access is controlled by a Windows user or group, which in turn is authenticated on the client machine or by Active Directory. SQL authentication, in change opens a door for the malicious users who know the username and password and can log into the system. Also SQL username & password cross the network unencrypted unless network encryption is enabled ( SSL certificate) and can be intercepted by attackers.

4. Databases Files Location.

  1. In SQL 2005 it was possible to select a single location for User Database (Data files and Log files), a single location for the System Databases (Data files and Log files) and a single location for SQL installation files.
  2. In SQL 2008 you can set up better the storage space so that you perform less post-installation changes. You can choose separate locations for: the System databases, Tempdb (which should be placed on a separate disk space well sized), User Databases Data Files and User Databases Log files. Performance will be enhanced if Data Files will be separated from Log files on different disks because the I/O operations of the Log files are sequential and random for Data Files.

After you install SQL Server it is always very important to setup few configuration options or Server properties like:

  1. Memory minimum and maximum values.
  2. I wrote a special blog about setting memory needs and it can be read here.

  3. model database recovery option (Simple if the SQL Server is for a DEV environment) and growing options (default 1 MB and 10% for Data/Log files is a big mistake that most leave behind and later face the problems caused by it)
  4. Set Firewall rules for allowing access to the SQL port.
  5. Check the “Allow Remote connection to this server”: right click SQL Server –> Properties –> Connections page.

Leave a Comment

NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>