Active8 years, 9 months ago
I'm trying to find some good references for best practices for planning and configuring an MS-SQL 2008 R2 server.
I'm thinking in the lines of storage configuration, file locations, recommended application or user separation, common pitfalls etc.
- Can someone tell me how to install SSIS? I've gone through Add/Remove programs, and attempted to update SQL Server 2005 but cannot seem to find the correct components to add, and since I cannot see the SSIS name in my Services panel, i assume it hasn't been installed.
- This article explains how to install SQL Server with the Installation Wizard. It applies to SQL Server 2016 (13.x), and SQL Server 2017 (14.x). This article provides a step-by-step procedure for installing a new instance of SQL Server using the SQL Server setup installation wizard. The SQL Server.
- In the first tip (SQL Server Integration Services (SSIS) - Best Practices - Part 1) of this series I wrote about SSIS design best practices. To continue down that path, this tip is going to cover recommendations related to the SQL Server Destination Adapter, asynchronous transformations.
Roy
RoyI am trying to add Integration Services an existing SQL Server 2008 instance. I went to the SQL Server Installation Center and clicked the option to 'New installation or add features to an existing installation.' At this point, a file system window pops up. I am asked to browse for SQL Server 2008 R2 Installation Media. Not 100% backwards-compatible. A SSIS 2008-edited package that extracts data from flat file and doesn't specify row & column delimiters will work in 2008, but if you run that package on a SSIS 2012 server, the empty row & column delimiters will cause the extract to go nuts, it will import zillions of columns/rows (because it never finds a column/row ending).
Roy3,0813 gold badges26 silver badges45 bronze badges
3 Answers
http://msdn.microsoft.com/en-us/sqlserver/bb671432.aspx is worth a look.
jl.jl.
I think SQLCat had a whitepaper on MSSQL 2008 R2 Best Practices.
Sql Server 2008 Install
Also Microsoft has the Best Practice Analyzer (BPA) for MS-SQL 2008 R2.
paceypacey3,4731 gold badge11 silver badges30 bronze badges
There's a few things from my experiences that I'll mention.
File Locations
You should try to split your data, log, and temp files up as well as keeping them away from the OS. Keeping them away from the OS is to improve performance as well as make sure that if your databases end up filling the drives, it only causes a problem for SQL and not the entire server.
The data files should reside on a RAID 5 setup if you'll be doing mostly reads or a RAID 1/RAID 10 setup if you'll be doing a large mix of reading and writing. Our data warehouses which are mostly read are set up on RAID 5 so we could squeeze the most space out of the drives. We have a server that runs OLTP databases that uses RAID 1 for the data files for the performance gain. Remember, RAID 5 pays a hefty penalty for write operations. The downside of RAID 1 or RAID 10 is that it ends up costing a lot more to get the space you need.
Log files are very read/write based so try to put these on RAID 1/RAID 10. I would strongly suggest against RAID 5 for the log files and would only put them on one if money really stopped you from doing anything else.
TempDB also does a lot of writes so we put ours on RAID 1. I'd say this is the most important database to keep on it's own. We had problems in the past when our TempDB was growing too much and filling up the OS drives. Unless you have a very good handle on your TempDB usage you'll definitely want it to be separated.
The system databases (master, msdb, model) are pretty safe anywhere. I usually just leave these in the default SQL install directory.
Security
The Windows/mixed mode authentication is dependent on your situation. Microsoft recommends Windows authentication if you can get by with just that.
For the service accounts, we generally create a generic account to run SQL for us. This account does not need to be an administrator. SQL Server will add whichever account you choose to run the services to groups that are created during the installation. Do not use one of the employees accounts to run the services. If that employee ever leaves and their account gets disabled SQL will not be able to run.
If you're using mixed mode authentication, make sure you use a strong password for SA. I end up disabling the SA account after the installation as I don't like generic admin accounts but this is up to you.
Microsoft has a page for Security Considerations for a SQL Server Installation. I recommend you read that as well to get a good idea of a few other things to look out for.
Services
Only install the services you need. Determine if you'll need to install Analysis Services, Reporting Services, or Integration Services before doing the install and make sure you don't install anything you don't need. It's easy to install components you missed later and this way you won't have any services unnecessarily taking up resources on your machine.
The only service I would say is a pretty safe bet is Integration Services. One of the big uses for it is the maintenance plan tools it provides. Unless I'm very sure that I won't be making use of SSIS maintenance plans I always tend to install Integration Services.
Outside of those things, there's not a lot of other decisions to be made during the installation. Things like the collation should be left alone unless you know what you're doing and have a good reason to go outside of the defaults. The above 3 things are what I consider whenever I've had to do a new SQL Server installation.
Sean HowatSean Howat1,5644 gold badges17 silver badges33 bronze badges