Why Upgrade?
-
Should you upgrade to SQL Server 2005?
It's an interesting question and one that I've been asked quite a few times for my opinion by various people in the industry, usually friends in the Denver area. So when I was this Why Upgrade? white paper from Microsoft, I decided it was time for a few short comments.
I do think that there are some people upgrading now to get a great new application development platform, which SQL Server 2005 is. Or because they are constrained by SQL Server 2000's performance. SQL Server 2000 is a fantastic product, but 2005 is a higher performing product that does better in many places, especially if you can afford 8GB, 16GB, or more RAM.
But those are the minority and most people will wait for SP1 or until they need a new server, or until they are forced to because of some support contract or something like that. And there are always those that will wait for a long time.
From what I've seen, the most exciting new parts of SQL Server 2005 are the SSIS engine and the greater memory usage. If you need complex ETL or want to run large RAM installations then it might be the thing for you.
But for most of us, I'm not sure there's a compelling, or even positive, business case for upgrading. We're debating here at SQLServerCentral.com and are still up in the air. As we move forward, we will for sure let you know what we do and attempt to chronicle the experience.
All of the information in the white paper seems to represent a great potential for benefits, but most of it is unproven. And without detailed disclosure of the way in which the backing information was gathered and analyzed, and really without the raw information, I'm not sure how much is marketing and how much is fact.
So if you are really upgrading, we'd love some detailed chronicles of the process. I've pinged a few friends that I could visit with, but so far no one's seriously got a project in the works. For us a sticking point is the Quest software Turbo search, which we haven't had the chance to test on 2005. And third party products should be considered if you're looking at upgrading.
Or just upgrade your third party tool 🙂
Steve Jones
-
Let us be realistic please. If you need a complex ETL tool, you are not considering SQL2005. Seriously, individuals that need a complex ETL tool already have data warehouses in place and need a tool that will handle the data exchange between dimensions. The proven reality is not Microsoft.
Point in fact. The reality on the data warehouse market is as follows:
up to 300GB SQL Server; up to 800 GB oracle;
over 1 TB IBM DB2 EEE MPP (MPP-Massively Parallel Processor -not the mainframe stuff),
approaching 3TB Teradata.
-
dtevlin,
Is that really "proven" or is it just accepted wisdom? I'll grant you SQL Server 2000 won't handle the data volumes that you are talking about and I am definately not suggesting that SQL Server 2005 WILL, but it has not been "proven" either way. I know strong supporters will point to the TPC benchmarks and say it is up to the job but until its proven for a real customer in a real-world scenario that is not the case. Lets give it a chance to BE "proven" or otherwise though!
Besides, Steve's editorial was comparing SS2000 to SS2005, not comparing SS2005 to competing products. On my current project I am using SSIS for doing things (i.e. consuming web services) that simply were not possible using DTS and that alone has justifed (to the business no-less, not just us techies) the decision to go with SS2005.
Furthermore, we are making extensive use of CTEs for navigating self-referencing tables and again, this would not have been possible using SS2000. I am beginning to think that CTEs will become the "killer" feature of SS2005. Granted, Oracle (for example) has had this ability for some time and SS2005 is playing catch-up.
When I hear talk of "Why upgrade" (i.e. the title of this thread) then I need to see examples of things that can be done now in SS2005 that cold not be done before and here are two such examples.
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
What about Service Broker.
We are using Service Broker extensivly in 2005 and this alone is a excellent reason to upgrade. We are using many of the new features in a 'real world' app and even with out the extra performance these features are alone are worth it as long ad you put the time in to implement them correctly.
Shane
-
Hi Steve,
I am working on a serious SQL Server 2005 upgrade effort now. I cannot go into details, but the system is the backend for a web front, uses lots of transactional logic and some pretty slick JScript that is making its way to C# 2005.
The approach I've taken thus far is to restore a SQL Server 2000 backup to the SQL Server 2005 engine and allow the developer to go at it. We've hit a few snags, but it's nothing that halted development or even raised the specter that "this won't work." The new and various security settings were the first thing we had to deal with. Another thing that consumed an hour of head-against-the-monitor time was an error when we tried to open Database Diagrams. The solution we're testing now seems simple enough - we merely changed the database properties from SQL Server 8 to 9, and the engine converted the existing diagrams.
As for an earlier comment about the "limits" of SQL Server 2000: All server platforms are a synthesis of hardware, software, configuration, intended usage, and environment. To take one narrow metric and apply it so broadly is inaccurate. I built a 1.6 TB SQL Server data warehouse in 2003.
:{> Andy
Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics
-
... it's more accurate to say I was part of a team that built a 1.6 TB SQL Server 2000 data warehouse! My apologies to the rest of the team...
:{> Andy
Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics
-
We are in the process of upgrading as well, and the main reason is the XML features, second is the Service Broker. I work in an environment where we use XML messaging extensively as well as needing encryption within the xml messages. We ended up having a messaging gateway turn an XML message (as it could adequately shred the message) into a straight sp call and encrypting the data on our own end after we received the call. Now that we have better XML support along with Service Broker we are able to receive a message in memory, encrypt the data and store it, then we can store the message validated against a schema and use Service broker to fire off asynchronous events based on the data received. Plus being able to query the XML effectively. We are also currently looking at VS Team System, but haven't decided to move to it yet.
-
Wait
Wait some more unless you are a lab rat.
I remember the HELL we went through upgrading from 7 to 2000. We thought we had so many insurmountable walls in 7 that we HAD to upgrade. We believed Microsoft when they stated the new features, improvements, (and XML capabilities/support). Many MS bugs found by my team and a year later we were upgraded. Looking back, we should have waited until the second MSSQL service pack was released before upgrading, (starting the upgrade planning and development with the first sp). I will resist an upgrade to any working in-production system within the first year or second service pack of a Microsoft server product.
If you are starting a new system concept or development of a system that will take a year or more, consider SQL 2005. It may make sense to install MSSQL2005 and start the development to be completed a year or more down the road.
No walls. No upgrade.
Speed bumps, potholes, then repair and stay on SQL 2000 a year or two longer.
Do you believe all of the Microsoft propaganda and enjoy production pains, then upgrade to MS SQL 2005 today!
[font="Arial"]Clifton G. Collins III[/font]
-
Resistance is futile. You will be upgraded!
Bill Gates
-
Andy speaks the truth! The conventional "wisdom" regarding limitations on data capacity is just industry inertia. No one wants to fix what ain't broken. But since SQL Server 2000 size of the data should not be the overriding constraint and in cost / performance it can't be beat. SQL Server can handle it; you just have to know how. There's a big difference between scaling up and scaling out. With SQL Server you need to know your data very well. But that's changing in SQL 2005 too.
I'm usually pretty jaded about these things. Filtering the marketing crap from the gold nuggets is always a challenge. I've been using SQL Server since v4.5 when it was virtually a Sybase clone so I have more than a little skepticism in my bones. But I attended the SQL Server 2005 launch here in Boston last Thursday and Microsoft made a very strong case as far as I'm concerned. SQL Server 2000 was a huge leap forward and SQL 2005 is about to make database history IMHO.
It's not just about scalability. While that was dramatically demonstrated there are lots of other compelling new features in SQL 2005. Greatly enhanced ETL capabilities are certainly one of them. In some environments I think this could be reason enough to upgrade even though the upgrade path from DTS is not wonderful. If you’re converting DTS jobs you'll want to use the backward compatibility mode and take it very slowly. Hugely enhanced security and greater granularity in data backup and restore capabilities for higher availability is another reason to upgrade. You'll be able to restore without taking your database down and you can do partial restore so you don't have to restore the whole db.
Another area to be careful of is the much ballyhooed talk about the use of CLR for implementing stored procedures. What they don't tell you is that if you use this feature the dynamic query optimization capability is lost. Unless you have a very complex set of business rules to implement stick with TSQL for your stored procs. And if you do have a complex set of business rules then you might want to stick with the tried and true method of breaking the processes down into manageable smaller procs. The CLR implementation should only be used if your processes do not include a lot of interaction with the data that changes frequently and they require complex logic to implement.
If you want to see a real life example of the tradeoffs and implications of upgrading to SQL 2005 check out this link: http://www.microsoft.com/sql/solutions/bi/projectreal.mspx . You'll find a lot of great information about a project using real data from Barns and Noble. The project starts with about 1 Terabyte of data and grows over time. It is intended to discover and demonstrate best practices for using multi-tiered data sets to optimize the use of expensive storage used for high availability, highly volatile data by migrating it on a scheduled basis to higher density, lower cost storage systems. Migration issues, consolidated vs. distributed architecture, 32-bit vs. 64 bit performance and client connectivity issues are all discussed.
It also includes discussions of Business Intelligence ETL Design Practices and Analysis Services 2005 Migration. I know I'm sounding a lot like the marketing doofs I usually dump on but the biggest reason to consider upgrading to SQL 2005 is still price performance. ...especially for large implementations. That's because (in addition to dramatically lower basic cost) Microsoft's pricing strategy is to charge a single price per processor instead of per core the way Oracle and others are pricing their products. So if your thinking about using multiple core processors in your next db server (and who isn't) you can factor the cost of Oracle by the number of cores when doing your due diligence. You could easily be talking about orders of magnitude in price differential. Future versions of these chips will have up to 32 cores each. My guess is that this will change very quickly when Oracle and others see their market share dwindling away. When you add the additional cost of supporting products for Oracle that are standard out the box in SQL Server (Analysis and Reporting tools) you don’t have to be a genius to calculate which has the lower TOC.
I’m not advocating that everyone go right out and upgrade now to SQL 2005. There are lots of reasons you don’t want to take on an upgrade project without a lot of planning and soul searching. The learning curve on the many new features and interfaces will also be a roadblock for many. Some of the newest features may not be quite ready for prime time including things like db mirroring so think carefully about why you would want to upgrade. Forums like this one are a good place to start.
That being said I want to put this whole thing in context: I am an independent consulting data architect and while this rant may not illustrate it I generally try to remain platform neutral. But hold onto your hat folks because SQL Server 2005 is going to shake up the market....Big Time!
Ron Cicotte
ron.cicotte@gmail.com
Data Transformations, LLC -
We're upgrading our products because we're fairly new in the market for our products and don't have a lot of legacy installs. This will prevent our new customers from facing a database upgrade for a long time.
Eventually, most of us will need to upgrade. By doing it now before we get a bunch of installs, it pushes the pain of upgrades out several more years.
-
When we upgraded we found additional "built-in" functionality from Reporting Services that we did not know was in the new version, that ended up saving us some development time. The 2000 version of RS did not easily support integrating user controls into the user interface of the reports. For instance, we have a couple reports that allow the user to select a date range and our users were complaining of not having a calandar control that they could pick dates from. Now, in 2005, it is integrated when you use the date data type in your parameter definitions.
Also, now that SSIS has improved, I plan on migrating the rest of our imports from Visual FoxPro into SQL Server with SSIS. I know, there is a learning curve/curse involved - but what new product doesn't? People should expect some sort of learning curve with any new product. Just face the music - it is going to happen even if you switch products (from SQL Server to ORACLE or what ever other flavor/vender is being offered).
Our environment is fortunate enough to have 1 spare server for use as either a standby server or a test server. We were able to test & upgrade our reports before rolling them out live.
I noticed that in the new SQL Server MGT Studio that the syntax coloring missed the VarChar data type. Anyone else notice that?
-
Point of clarification: I meant to say that dynamic query optimization is lost "for the CLR stored procedure" This is because it is a compiled assembly sitting in the database. There is no way for the optimization engine to analyze it. This is why you don't want to include processes that interact with changing data. Other TSQL stored procs are unaffected by the CLR
Ron Cicotte
ron.cicotte@gmail.com
Data Transformations, LLC -
"...those WHO will wait", not "...those THAT will wait". Your object is people, not things. Sheesh!
John Scarborough
MCDBA, MCSA -
SQL 2000 is meeting our business needs, and after taking a look at SQL 2005, I see a ton of work in re-engineering DTS packages into SSIS (it would seem insane to trust a Microsoft wizard to do the re-engineering for us), a user interface that tries to look like Visual Studio (which I have always disliked), and in general, a lack of compelling reasons for us to upgrade.
Using the last "beta" of SQL 2005, I tried creating a simple SSIS package to move data from a text file to a table. Should be very easy, right? After 2 hours of aggravation, I couldn't get it to work. AND, we have many DTS packages that are infinitely more complicated than that simple example, and all of them would need to be re-engineered into SSIS. It seems like Microsoft is continuing to go in the direction of taking things that should be simple, and making them needlessly complicated (try running a stored procedure from within Microsoft Access for a good example - something that should take 1 or 2 lines of code takes an absurd quantity of complicated code).
Right now, I'm very unhappy with Microsoft, and plan to postpone the upgrade for quite a while. We may even start looking at alternatives.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply