SQL7 V's SQL 2000

Road Runner
05-29-2002, 10:00 AM
Im just intrested to here from anyone that has the made the switch from SQL7 TO SQL 2000.

Are you happy you upragded to SQL 2000?

What benifits does SQL 2000 Have over SQL7?

Is it worth upgrading(financially) to SQL 2000?

The resaon i ask these question's is that i am thinking of upgrading to SQL 2000 myself and i just want to know what your opions are on the matter.

Regards
Shane
:)

10stone5
05-29-2002, 11:14 AM
I like: 1)ability to open multiple instances 2)improved DTS packaging 3) easier data modelling 4)better metadata functions 5)Object Browser in Query Analyzer - but check out links >>

http://www.microsoft.com/sql/evaluation/overview/WhatsNew.doc
http://www.microsoft.com/usa/presentations/TNT1-04.ppt


But pricing is tough >
http://www.microsoft.com/sql/howtobuy/production.asp

Ogey
05-29-2002, 02:29 PM
Let me re-emphasize 10stone5's second point...

SQL Server 2000's Data Transformation Services are immensely superior to SQL Server 7.

If you haven't done any DTS work yet, once you get into SS2K, you really should!

10stone5
05-29-2002, 03:17 PM
Originally posted by Ogey
Let me re-emphasize 10stone5's second point...

SQL Server 2000's Data Transformation Services are immensely superior to SQL Server 7.

If you haven't done any DTS work yet, once you get into SS2K, you really should!


Ogey ->

Question though - Have you used SQL Server Scheduler much and how successful has it been for you?

I have trouble from time to time with the SQL Server Agent 'disengaging' and hanging up scheduled tasks.

Ogey
05-29-2002, 03:34 PM
Once or twice I did have a scheduled job not actually run. The next day when I looked back at the job, it still said scheduled and waiting to run, but it had never actually fired.

Since it was only once or twice among the hundreds of jobs I've scheduled, I chalked it up to something I did wrong and didn't realize. I know you have to be real careful to make sure the "Enabled" option is selected in both places within a job to make sure it runs.

Another little tip is to always refresh the job in Enterprise Manager (the actual job line, not the whole list of jobs) and check when it thinks it is scheduled to run (I think the column is titled "Next Run Date."

Don't get me wrong, you can really tell that DTS is going through growing pains. There are plenty of quirky things that don't go right with it (most of them having to do with the UI rather than the actual processing).

Can you tell me more specifically what you mean by the Agent "disengaging" or "hanging up" on your jobs?

10stone5
05-29-2002, 04:01 PM
Thanks for the input ->

'Agent "disengaging" or "hanging up"' - is as you described above - except I seem to get the 'scheduled and waiting to run'
scenario more often, though I will run through your tips list.

Another interesting factor is all our servers [incl. SQL], LAN, EMail, etc. synchronize to a 'User Password Utility' - and this could be effecting SQL Server Agent.

Our company uses SQL to 'stage' data rather than a full blown data warehouse - and we may want to schedule 30 to 40 DTS a night. Now some are scheduled and some are just run manually.

Ogey
05-29-2002, 10:14 PM
I would worry about 30 or 40 DTS packages running if there is the possibility that some of them might be overlapping. If that's the case, you need to take extra precaution to make sure the individual packages are not using the same resources.

Rather than create individual jobs for the 30-40 packages, you can create one job that runs all of the packages in a particular order. This way you're sure only one package runs at a time. (But maybe this is what you were planning anyway). If you do run several packages in a single SQL Server Agent job, pay attention to the Advanced tab of each task. Specifically, the "On Failure" property. You can either make the whole job stop whenever one package fails, or you can make it continue on with the next one. It defaults to the former - which I don't care for. Just make sure it specifies whatever you want.

Thinker
05-29-2002, 10:25 PM
This thread is just too informative to leave in Random Thoughts.

Road Runner
05-30-2002, 01:59 AM
Hi all

Sorry fot the late reply
thanks 10stone5 and Ogey for all your information and links.


Originally posted by Ogey
Let me re-emphasize 10stone5's second point...

SQL Server 2000's Data Transformation Services are immensely superior to SQL Server 7.

If you haven't done any DTS work yet, once you get into SS2K, you really should!


i actually use the DTS quite a bit in SQL 7.
What do you see as the main improvement of the DTS in SQL2 000?

Once Again thanks for the replies

Regards Shane

Ogey
05-30-2002, 08:56 AM
Now you've put me on the spot. :) I'm trying to think about actual features that got better in SS2K DTS, but I've been working in it so long, I've almost forgotten what features weren't available in SS7. BOL helped refresh my memory.

The #1 improvement for me has been being able to use Global Variables better. I believe in SS7 you could only use global variables in ActiveX tasks. You can now incorporate Global Variable values into Execute SQL tasks and you can pass them between packages when one package calls another. It's great! This has become the heart of most of my DTS application design.

I do vaguely remember that there might have been a work-around to fake some of that in SS7 (like creating SQL within an ActiveX task and feeding the SQL into an existing Execute SQL task). But I was never too happy with the results. Anyway, it's MUCH easier now in SS2K.

A lot of people have told me that they love being able to save DTS packages to Visual Basic files, but I personally haven't had the need to do that yet.

I think the number of tasks available is greater. I like the new Dynamic Properties Task which allows you to adjust individual properties of any task, step, or global variable in the package. In fact, one task can adjust lots of properties. While you can do this through ActiveX, the new task makes it much easier. The Send Mail Task is also really convenient.

Here are two examples of the "bugginess" of DTS that still persists: There are all kinds of hangups when you try to import data from an Excel file - it just doesn't work right - save the data to a text file instead. When using a Global Variable value in an Execute SQL task, you sometimes have to fool the UI into allowing you to do what you want. The back-end processing works, it's just the UI validation that messes up.

Road Runner
05-30-2002, 09:15 AM
Ogey you make some very good points
and have given me a lot of food for thought

I appericate your input.

i have been doing a bit of reading up on the internet and I Think i will upgrade to SQL 2000.

Are yo sure you not on commesion from microsoft Ogey?:D

10stone5
05-30-2002, 09:45 AM
Ogey -> thx for the very definative commentary on DTS.

I'm sure its been mentioned before in this forum - but this site provides some great code & a nice searchable FAQ >>

http://www.sqldts.com/

Ogey
05-30-2002, 12:51 PM
Commission? Wouldn't that be nice? ;)

Maybe I should look into that.:rolleyes:

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum