January 12, 2009 at 10:17 am
I know on the face of it the answer may appear to be no, this is not a safe move! But within our SQL sprawl, not only have people been installing many sql instances, but they have installed and licensed them, without thought, with the media they happened to have to hand - almost always Enterprise edition.
Although there was a push to save money, including on licensing, this was not my only reason to suggest "hold on a minute, we do not need Enterprise Edition on some of these SQL servers". I did some analysis too.
We are a fairly large organisation, and we have about 3000 users potentially hitting this sql server, and about 4000 active connections. Our transactions per second average only around 20, mind you. But there is nothing about the features of Standard edition itself that mean it limits the volume of usage (are there?!).
We have bought a new DL585, with lots of RAM, and are running windows 2003 x64. With 4 quad-core CPUs, we are allowed SQL Server 2005 x64 Standard edition. And i am confident that, while we will not truly get the equivalent of 16 CPus, the 4 modern quad cores can take on the load of the old DL740 G1 (8 physical processors). This server, 6 weeks of metrics show, is not stretched CPU-wise (or in any other way).
So with just 4 physical CPUs, and no memory limitation imposed by 2005 Standard edition, that is the hardware limitations taken care of I feel.
In terms of features, we do not need table partitioning, and do not feel our 3rd party vendor will impose this as a must have, or that our future data volumes will make it appropriate. I would be caught out if a vendor (or developer, to a lesser extent) imposed upon us the need to use cross-server Service Broker conversations, but i cannot see this happening. We can definitely live without partial restores, especially when you consider the cost of enterprise over standard edition. And i will verify that our reindexing and DBCC checks do not suffer so much from the loss of parallelism that we get potentially unmanageable maintenance windows.
But still, I am nervous! I have suggested this direction, and obviously don't want to get to production and find that I have missed something??!!
Do you think I have overlooked anything here?
Many thanks.
James
January 12, 2009 at 11:44 am
2005 is a bit antiquated these days. Why not jump to 2008 since you are a late adopter, it should be justifiable.
January 12, 2009 at 12:38 pm
-- we have about 3000 users potentially hitting this sql server, and about 4000 active connections.
Is this statement right? Is there no connection pooling?
SQL DBA.
January 12, 2009 at 12:44 pm
Whether you go 2005 or 2008, Standard should do what you need in the situation you are describing.
Do you have a test environment where you can load up the databases and test them in Standard? If not, that would be the next step to take, before you start actually migrating production databases.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 13, 2009 at 3:09 am
Thanks all for your replies.
We have a combiniation of 3rd party and custom apps and the 3rd party vendor in this case (and in many of our other examples) has not certified their apps on 2008 yet.
The core part of the app in question does not use .Net connection pooling, and in fact has about 6 connections per user (usually 3 for the app's config database and 3 for the actual database) and then a similar model for various system processes from application servers. So we have a misleading number of physical connections!
Thanks. We do have a test envoronment where we are very soon to copy the databases to and start testing. The testing will be full in terms of width - all features and aspects of the app across its architecture - but not in depth (volume of usage) adittedly. The number of users will be, well, just the testing team (and system processes from the app servers). I am going to investigate doing a sql trace on the live system and replaying it in the test system to do a load test, but from a look i had at this once it looks very fiddly to get it working! Have you ever tried this?
Thanks again.
J
January 13, 2009 at 7:05 am
Yes, I've tried running traces. It takes a bit to figure out, but it's a good way to load test a database as if real users were present.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 13, 2009 at 10:40 am
Are you using SSRS on any of your instances? You will lose the Data-Driven functionality.
January 13, 2009 at 11:34 am
If you don't care about "fancy" features like Partitioning, Database Snashots, Online-index rebuilt among others you are probably fine.
Given the transaction rate you are posting "standard" maybe ok. How large are your DBs ?
* Noel
January 13, 2009 at 12:43 pm
What you really get with the enterprise edition is the high availability and scale up functions that make the edition 'enterprise' wide, so if you don't need or currently use asynchronous mirroring or partitioned tables and such like I would say you will be fine. Standard edition doesn't process queries slower than enterprise edition.
I would think the main thing you will lose is the ability to make database housekeeping such as reindex less intrusive so consider that.
We use standard edition for everything and survive quite nicely. As suggested by others and you plan to do anyway,test your app on a standard edition server.
Looking forward to 2008 you will lose compressed backups, but hey disk is cheap and there are third party products.
---------------------------------------------------------------------
January 14, 2009 at 1:38 am
Thanks again, this is all really useful.
We will have a dedicated SSRS server (one day!) and this may need to be Enterprise Edn then from what you say. We'll do the analysis as to whether we need to the data-driven functionality. I'm not entirely sure what that does; we still use an alternative reporting solution for now.
Database snapshots! Of course, i cannot believe i missed that one. That is not referenced as being unavailable in Standard in any material I have. Naughty! Hhhmm, well i am dissapointed we will not get that. We won't be losing anything as such since it is not in 2000, but it does seem a shame. I'll have to make this clear to manangement just so they know we won't get this feature, just in case anyone was aware of it and had hopes for its use.
Although our HA and DR solution are to be one and the same, and are based on flat file SAN-to-SAN copies, i still wanted to make sure we could use mirroring or failover clustrering if we wanted to go down that route later on. I understand we can use clustering, albeit just for 2 nodes. And we can still use mirroring can we not? But, again, i did not know that Std limited us to synchronous copying of transactions, so this means we cannot use High Performance mode , is that right?
Nice to hear others are doing nicely on Standard Edition!
Many thanks.
James
January 14, 2009 at 4:56 am
jmanly (1/14/2009)
Although our HA and DR solution are to be one and the same, and are based on flat file SAN-to-SAN copies, i still wanted to make sure we could use mirroring or failover clustrering if we wanted to go down that route later on. I understand we can use clustering, albeit just for 2 nodes. And we can still use mirroring can we not? But, again, i did not know that Std limited us to synchronous copying of transactions, so this means we cannot use High Performance mode , is that right?
your understanding is correct james.
I have a miirrored database in high protection mode and no noticeable performance problems, but the database is small (5GB) and not heavy in terms of updates.
This link details differences in editions
http://www.microsoft.com/Sqlserver/2005/en/us/compare-features.aspx
fails to mention snapshots though!
---------------------------------------------------------------------
March 9, 2010 at 8:49 am
Did you make a decision on this?
We have the opposite situation at the moment.
We currently have Standard licences but are considering upgrading to Enterprise.
The main driving force is Asynchronous mirroring and snaphot (although data compression and backup compression will be useful add-ons).
My concern with Syncronous Mirroring (available as Standard) is what happens if the network connection goes down between the two mirrored instances? My understanding is that this could cause a major resource issue and would in the worst case cause a total lockup of the primary server - especially if this were to occur while not attended.
Does anyone else have any thoughts on this?
March 9, 2010 at 8:51 am
Did you make a decision on this?
We have the opposite situation at the moment.
We currently have Standard licences but are considering upgrading to Enterprise.
The main driving force is Asynchronous mirroring and snaphot (although data compression and backup compression will be useful add-ons).
My concern with Syncronous Mirroring (available as Standard) is what happens if the network connection goes down between the two mirrored instances? My understanding is that this could cause a major resource issue and would in the worst case cause a total lockup of the primary server - especially if this were to occur while not attended.
Does anyone else have any thoughts on this?
March 9, 2010 at 9:08 am
In that scenario the log would begin to fill. Eventually you would have to break mirroring and reinitialise when the network was back.
How long before that was necessary would depend on your environment.
a good network is a prerequisite of database mirroring, as it is for replication.
---------------------------------------------------------------------
March 9, 2010 at 9:24 am
Thanks for your quick response.
I agree that a good network should be a pre-requisite but it is still a point of failure.
In exactly the same way a reliable motherboard (or good disk-array/RAID setup) is a pre-requisite for a good database server - it is still a point of failure.
It seems to me that Synchronous mirroring, whilst reducing the risk of one catastrophe (e.g. motherboard failure), increases the risk of another (i.e. network failure). Hence I would question the gain.
On the other hand Asynchronous mirroring would allow the primary database (and its server) to continue unaffected even if the link was broken to the secondary - or at least that is the way I understand it.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply