Do I need to change hardware when migrating from SQL 2000 to SQL 2005?

Hi Everybody!!!!

Do I need to consider investing in new and more powerful hardware when migrating from SQL 2000 (or any other previous version) to SQL 2005?

I have a friend / client who migrated from SQL 2000 to SQL 2005 sp 1. The performance of the server got really bad, and now he's trying to fix the problem by any means necesary.

I think it would be very understandable if investing in hardware would be recommended, as we all have seen that there are so many new platforms to use and integrate to (.NET framework, XML, soap, ADO.NET, etc, etc,) and keeping the old platforms as compatible as always (ADODB, ODBC, etc.)

Any feedback is greatelly appreciated!!!!

Tarh Ik

I would recommend having a fresh, new server with Windows Server 2003 R2 SP2 and then installing SQL Server 2005 SP2 on it. Then, you can either detach/attach or restore from a backup to migrate from SQL Server 2000. This is much less risky than upgrading in place from 2000 to 2005 and will perform better than installing a named instance on the same server where SQL 2000 is running.

As far as hardware goes, obviously, new, faster hardware is better if you have the budget for it. Hardware prices continue to get lower all the time, and nobody has ever complained about a database server being too fast.

Distrubuter has sick hardware. Need to change box.

We run our distribution on another server ie not on the publisher.
We have had some hardware errors and to be honest it's an old machine and
spares are getting sparse.
Is there a step by step for moving distribution to another server?
Thanks
Paul
The short answer is no. The longer answer is no there is not step by step
guide for moving a distributor to another server.
An even longer answer is, no, but it is possible to do a tape restore to the
new distributor with the same name bounce it and hope for the best. I don't
like remote distributors. DBAs IMHO are too quick to migrate to them, and
you need to cluster them otherwise you have a single point of failure which
can be problematic for you as you have discovered.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul Cahill" <noname@.anon.com> wrote in message
news:e6FVdryzFHA.2792@.tk2msftngp13.phx.gbl...
> We run our distribution on another server ie not on the publisher.
> We have had some hardware errors and to be honest it's an old machine and
> spares are getting sparse.
> Is there a step by step for moving distribution to another server?
> Thanks
> Paul
>

Distribution Server - Replication

What happens when Distributor Server goes down in Transactional
Replication, no corruption (power , hardware failure)??
Thanks,
JohnCan you bring the distributor back up or the distribution database is lost? Before you bring the distributor back up, you need to shut down all subscriptions. If you can restart the distributor, you need to stop the logreader right after you have started the SQL Agent on the distributor. Open the subscription first to see if they can be synced. If the subscription doesn't errored out, open the logreader. Good luck!

Distribution CleanUp creates latency

Hi,
We have one publisher, one distributor and two subscriber. We run
transactional replication.
We recently upgraded all our hardware but now we find ourselves with an
unacceptable latency. When the "Distribution Cleanup" process fires it
take up to 4 minutes to run and will cause a complete replication pause
for 1-2 minutes at times.
In a perfect world, latency would always be under 2 seconds. I'll live
with the very seldom latency of 10 seconds.
What can be done to tame the distribution cleanup jog.
Regards,
CanadianGambler
*** Sent via Developersdex http://www.codecomments.com ***
Nothing. You could run the distribution clean up job nightly and see how the
pooled commands affect performance.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Canadian Gambler" <canadiangambler@.hotmail.com> wrote in message
news:%23xLD5HnsFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have one publisher, one distributor and two subscriber. We run
> transactional replication.
> We recently upgraded all our hardware but now we find ourselves with an
> unacceptable latency. When the "Distribution Cleanup" process fires it
> take up to 4 minutes to run and will cause a complete replication pause
> for 1-2 minutes at times.
> In a perfect world, latency would always be under 2 seconds. I'll live
> with the very seldom latency of 10 seconds.
> What can be done to tame the distribution cleanup jog.
> Regards,
> CanadianGambler
> *** Sent via Developersdex http://www.codecomments.com ***
|||I guess I should provide more info.
We relocated our equipment room and in the process upgraded the hardware
for all our database servers (1 publisher, 1 distributor and 2
subscribers).
We finished this move 2 weeks ago. The serious latency issue only
started about 6 days ago. We ran the same setup at the previous
location for about 15 months without latency greater than a few seconds.
So something is not quite right. Read on, help is on the way ...
After doing more research last night and this morning we find that some
of the tables that are "suppose to be cleaned up" contain more than
12,000,000 records.
So we are now approaching this problem from a different angle. 1.) Why
are those tables so big and the "clean up" job not "cleaning up". 2.)
How do we go about cleaning those up so that the "clean up job" doesn't
take so long to do it's thing. I saw some similar post so I'm hoping
that I will find some industry wisdom that can help us figure this one
out.
Regards,
Canadian Gambler
*** Sent via Developersdex http://www.codecomments.com ***

Distributed transactions and replication creation: which is the link?

Hi all,
I'm getting a strange error after reinstalling and recovering a database
server on a new hardware. It was posted on
http://groups.google.com/group/micro...f6213ef6965f41
My new questions are:
-Is replication (or at least replication creation proccess) related to
distributed transaction coordinator (MSDTC)?
-How can I get assured the MSDTC is working properly? Is it possible to
activate a detailed, (I mean verbose) log of activity? In the Event
Viewer/Application Log the only event related to MSDTC is informative: 4156
"Session idle timeout over, tearing down the session" once or twice a day
-In the case the distributor and the publishers are located in separate
servers, if there is involved a distributed transaction, is it the
distributor who initiates the distributed transaction towards the publisher?
-In the Enterprise Manager, in the properties of the server, Connections
tab, "Enforce distributed transactions (MTS)" checkbox has any influence in
the replication configuration? In the previous configuration we tested
distributed transaction between these two servers and moved this checkbox
and other related configuration parameters but had no problems with the
working replication, even we recreated the publication many times without
any problems.
I have no clear clues. Any hint is welcomed.
Thanks in advance
Sammy
The problem was solved the following way:
The publisher server had "Enforce distributed transaction (MTS)" checked. We
unchecked this and restarted all the services, including MSDTC. After that
the problem disappeared.
Hope it helps anybody else
Sammy

Distributed SSIS server setup

I work in my organization's warehouse team and we are rearchitecting our hardware. One of the ideas on the table is to devote a server to running ETL. There would be several beefy database servers, and a separate server that would run the ETL (SSIS). I'm wondering if that actually hinders the process.

The DB servers will still do all of the query processing, so that isn't off loaded to the ETL server. Then the recordsets I think would need to go over the network to the ETL server for the SSIS packages to work on. And then another trip over the network back to their destination. Would it be better to place the ETL process on either the source or destination DB server? What could be gained by using hardware in this way as described here?

Thanks.

Hi, its a good question but at the same time there is no perfect answer. It all depends. In a perfect world everything on 1 server that is understressed would be screamingg fast. Unlimited hard drive space (and high RPMs), bucket loads of free RAM, minimal network interaction.

So it depends a lot on how stressed you feel the box would be, AND its expected growth.

Are you running ETL jobs during most of the day?

Are they small but frequent or infrequent but intensive?

Capture performance with perfmon or some other tool of even basic stuff like cpu utilization and free memory for even a day and see where are the peaks and duration of the peaks.

Eggs in your basket and downtime are other things to consider. So a huge down side of the single server is if it smoked out you lose everything.

If an isolated ETL box dies, then all your other operaitons keep going. So now a quest ion for your question is, what is your recovery/backup plan/needs/desires?

Do you have the hardware to have a duplicate of each type of server that you can work into production quickly if need be? Perhaps a good approach is you have the DBs and ETL process on multiple boxes but one is the primary DB server the other the ETL...but if something happened to the ETL server, you could in a pinch get the ETL process up and running fast on the DB box because all the needed bits are there but you just need to get it 'live'. Similarly assume your ETL box has mirrored (in some wya) data of whats on the DB server and then if the DB server has problems you could get that process live quickly from the ETL box.

Again, there is no 1 perfect answer. Hope this was useful food for thought.

|||

A distributed SSIS farm will quite well for micro-batch ETL (sub-day processing). Using Craig's terms, the job resource utilization profiles are "small (data wise), frequent AND intensive", meaning not much RAM per job (200MB), lots of CPU, and lots of jobs (thousands to millions per day). This is a generalizaton, since the there are certainly some packages which run on a less frequent schedule which have different resource utilization profiles.

The distributed SSIS server architecture can scale out nicely too. There is custom work up front to get it going, because there needs to be some type of queue-ing (database table, msmq, service broker queue, file system) mechanism which farms out the work, which operates outside of and interacts with the SSIS infrastructure.

As far as mirroring dimensional data out to the SSIS servers (in an effort to reduce network traffic) something to consider is the use of SQL Compact. SQL Compact is not 64 bit compatible (yet), but it looks like a good option if and when it does have a 64 bit version. If not, something that works right now, is a local SQL installation together with SSIS which each ETL server uses to "assist" in areas like caching of stock (relatively non-volatile) data.