SQL Server Edition - which is the best one to choose for your database needs?
When I look at the question, I feel that no direct answer to it!
The reason being SQL Server has outgrown into an important enterprise RDBMS product from a simple desktop based database application, but still maintains the ease of installation and use. This is what I hear from the Users & Professionals whenever I engage a talk about SQL Server, but what misses is how best to configure and keep up the optimum performance of your database application.
Recently, for me it has been a great 3 days of interaction with many IT professionals and users in Hyderabad in attending the Microsoft's Tech-Ed 2009 conference in India. In fact it was a first technical conference for me to attend in my hometown that too as a speaker to deliver SQL subject session, well the topic is getting diverted which I will discuss these ones on a seperate blog,
Coming back to the subject line, as a DBA/Developer/Designer/Architect what is the best edition will you choose for SQL Server? I have been referred with similar lines of questions within Tech-Ed conference such as 'Can anyone point me to where I can find a list of differences in Server 2008 Standard versus Enterprise?" The favourble position for me is about the version as the user was particulary interested in differences that pertain to SQL Server installs and usual answer that was obtained was when they find a Server 2008 edition as STANDARD and enquired about Operating System team why they haven't chosen/favour ENTERPRISE instead, the reply was "the only difference is that enterprise allows for clustering", so this was a big question and dilemma for that user asking me is Clustering is only feature that differs from Enterprise to Standard?
As you may agree with me it is not and everytime I suggest and refer to look at SQL Server 2008: Compare Enterprise vs. Standard and you may agree with me the list that link is exhaustive and endless when you look at the features that SQL Server can offer to keepup the application's Scalability, Availablity & Performance! To continue and educate that user who has raised the big question about feature comparison on the editions of SQL Server I have enquired what is most important feature that application needs/required. I have chalked out the following list of features based on the feedback that I have receieved:
- The Application Code is optimized enough and database has been normalized as per the best practices, but still the previous version (SQL 2005 Enterprise Edition) Server suffers the performance inspite of adding few more resources to the hardware (memory & disks). So my answer was to take help of parallel index operations from Enterprise Edition as it doesn't affect the performance on CPU and as per the BOL: SQL Server uses the same algorithms to determine the degree of parallelism (the total number of separate threads to run) for index operations as it does for other queries. The maximum degree of parallelism for an index operation is subject to the max degree of parallelism server configuration option. You can override the max degree of parallelism value for individual index operations by setting the MAXDOP index option in the CREATE INDEX, ALTER INDEX, DROP INDEX, and ALTER TABLE statements. The reason to chose this option is whenever query plans built for the index operations that create or rebuild an index, or drop a clustered index, allow for parallel, multi-threaded operations on computers that have multiple microprocessors.Further I suggest to see this Parallel-index-configure article for more information.
- The data growth is expected to be huge (atleast 80%) in next 3 years that may go upto 2.5 TB (Terabytes) and very little chance for data archival processes, so the my option was to go about table & index partitioning. In the previous versions taking such help by indexed views by tying partitions together involved creating a partitioned view or a wrapper stored procedure that figured out where the data lived and executed another stored procedure to hit only the partitions needed to return the dataset. So talking about the edition feature only Enterprise and Developer editions of SQL Server (from 2005 onwards) lets you partition large amounts of data contained in a single table into multiple smaller partitions that can be managed and maintained more effectively. As the application is based for production usage Developer edition is not an option at all. So to have such an ability to create data segments that are accessed through a single point-of-entry reduces many of the administration issues that came with the old way of doing things. Using the table/index partition with a single point of entry (table name or index name) hides the multiple data segments from the application code and allows the administrator or developer to change the partitions as necessary without having to adjust the code base.
- Next biggest threat to the application data is configuring the size of data file, as the increase of data varies from day to day and choosing default value of 10% is not sufficient in some cases causing error to the application. What I have suggested is to compare the size of data files on that database on regular basis (in some cases every 4 hours to see any difference), if not every 8 hours or so, by taking care of such baseline and benchmarking procedures it will help to define a nominal value to the data file in order to optimize the sizes efficiently by controlling the capacity with an altering mechanism.
- Further the user was interested to get some tips to monitor the performance over a period of time on system using TSQL or GUI, as the answer was to continue configurating and running the Management Data Warehouse that was one of the best feature in SQL 2008 and Performance Studio.
So the first 2 features that I have opted was from Enterprise edition and to configure the Management Data Warehouse you could use Developer edition too.
To continue this dialogue of tips and tricks to the user I will post more on the topic such as best practices on federated databases, data partitioning and user-schema best practices questions that were raised by the users at this conference, please keep watching this space.