Planning the rebuild of a merge publication (publisher) server

I am currently running a SQL Server 2000, which hosts the replication
publisher & distributor of a large merge publication DB. The current NT 4.0
server is scheduled for complete replacement and upgrade (to Win Server 2003
w/ SQL Server 2000) and I am looking for best practice recommendations for
transferring this database and replication topology from one system to
another. If possible, I would prefer to not to have to perform a complete
rebuild the replication topology. SQL BOL seems to indicate that Backup &
Recovery is not an option. Any ideas?
Thanks, Kevin
Kevin,
you should be able to detach and attach your publication database provided
you migrate master, msdb and the distribution database first, and the new
server name is identical to the old one. If this is too much hassle, then
you'd be better off scripting out the replication settings, tailoring them
with the new server name and then recreating from scratch.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Planning ahead w/ SQL Server...

With the current web app that I am writing, I am starting to plan ahead to the scalability problems that I am planning on encountering with the number of users that I may have...

My hosting plan now allows for a 50MB SQL Server database, but, I know that will not last long, each user will be using 3-5MB each of the database, so I am going to outgrow my space fast.

Would looking into (until i have enough subscribers to get a dedicated host), SQL Hosting be a good idea? Atleast to start off with something like http://www.alentus.com/hosting/sqlserver.asp ?

But then again, would a SQL Database growing to large get bad? Within a few years, i expect to have at max 5,000 users, so that could grow to a 25GB database... with millions of rows.

Would breaking it up into smaller databases for each N amount of users be a wise idea? Or would it not really matter?

Any help is much appreciated

Happy New Year
Harold.Sql Server can easily handle huge amounts of data. In the schema of things 25 Gb and millions of rows isn't that big, particularly if the data is organized correctly and you have appropriate indexes defined. Breaking it up into smaller databases won't really help you and it would create a maintenance headache.

Of greater importance would be planning for the type of database server to handle future needs. But to start off with almost any decent server will do.

Pivot Tables

Greetings, can anyone tell me how to create a pivot table view? (If at all) I want to manipluate data to a view to use in Crystal reports. Current table has producta "A", "B" & "C" with three options "i", "ii" & "iii" each with amounts per product & options:

A i 10
A ii 20
A iii 30
B i 5
B ii 7
B iii 9
C i 2
C ii 4
C iii 6

I want to create a view that lists the product "A" and Options "i" in the rows, with Colums "i", "ii" and "iii" and their corresponding amounts listed in the relevant colums. Thanx!there's probably a better solution out there, but this will get you started:

declare @.tbl table (
product char(1) not null, options char(3) not null, price money not null)
insert @.tbl values ('A', 'i', 10)
insert @.tbl values ('A', 'ii', 20)
insert @.tbl values ('A', 'iii', 30)
insert @.tbl values ('B', 'i', 5)
insert @.tbl values ('B', 'ii', 7)
insert @.tbl values ('B', 'iii', 9)
insert @.tbl values ('C', 'i', 2)
insert @.tbl values ('C', 'ii', 4)
insert @.tbl values ('C', 'iii', 6)

select p.product, [i]=t1.price, [ii]=t2.price, [iii]=t3.price
from (select distinct product from @.tbl) p
inner join @.tbl t1
on p.product = t1.product and t1.options = 'i'
inner join @.tbl t2
on p.product = t2.product and t2.options = 'ii'
inner join @.tbl t3
on p.product = t3.product and t3.options = 'iii'|||It sounds like you are trying to create a CROSSTAB query, and you can look up how to do it using CASE statements by searching Books Online for keyword "crosstab".

However, this will NOT give you a try pivot table report, which is dynamically configurable by the user. I'm not sure that Crystal can even do this. As a matter of fact, for a pivot table you don't want oto have your data in crosstab format. If you truly want pivot functionality and the ability to slice, dice, and summarize your data dynamically, create a pivot table in Excel or in an ASP page that links to the data in your table, (through a view, preferably), and leave your data in its current columnar format.

blindman|||actually crystal was able to do it even when it was part of vb4.0

but i still think that having this static approach is better than allowing users to build pivots dynamically, because if they transpose columns and rows (intentionally or not) the whole thing will croke (or may croke).|||Would this not be equivilent to a cube? It seems that A, B, and C would be one dimension, and i, ii, and iii would be a second dimension, and the values would be the intersection points of the the two dimensions...

| A | B | C
------
i | 10 | 5 | 2
------
ii | 20 | 7 | 4
------
iii | 30 | 9 | 6
------|||this is exactly what i was talking about in the previous post!|||Originally posted by ms_sql_dba
this is exactly what i was talking about in the previous post!

Bah.. sorry.. I started typing, and got side tracked for a little while and didn't get to finish my post until there had been 3 other replies!

SQL supports the creation of cubes if you didn't want to do a bunch of joins.. that might get costly if you had lots of dimensions...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agcubesintro_80qb.asp|||Thanx for the advice! Appreciate your time!