Need basic guidance on massive access to sql conversion

I have a rather daunting task ahead of me. I have ot take an access mdb and bring it into sql express. Converting the tables is not a biggie, but I have more than 2500 queries that need to be brought over. I have several questions.

1. In many places I use queries with parameters as views. For example I might have a query that takes a couple of parameters and then turn around write another query that uses the first query as a table. Is there a way to do parameterized views in Sql?

2. Does anyone know of a decent automated tool to do this? The upsizing wizard is useless and I tried teh sql migration assistant, and it doesn't convert queries that have parameters.

3. Has anyone done anything on this kind of scale before?

Thanks ... Ed

1. The closest to parameterized views is using Table Valued Functions. Check in Books Online about using TVFs.

2. No 'decent tools' known. Parameterized views can be 'troublesome'.

3. Yes, and it will take a lot of dilligent and concentrated effort.

and 4. Good Luck -because you WILL need it!

Need advice on massive database

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.clustering:14782
Hi,
We're in the process of architecting a very large SQL2K database. We
believe this database will grow 2TB per month. 99% of the inserts will be
done via bulk inserts. Approximately 3-4 per day an application that we
wrote will query and pull approximately 6 million records at a time.
* Will this work with SQL Enterprise or should I use DataCenter?
* Will this operate on Win2k Enterprise or should I use Datacenter?
* What I/O recommendations do you suggest (RAID or fiberchanner / RAID
array, SAN, or NetSAN, memory on controller, etc) to keep up with a bulk
insert of approximately 2,000 records per second?
* Will failover clustering work with a database this size (stupid
question but I have to ask)?
Thanks!
Jack
Comments Inline
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jack Victor" <Nomail@.JustPostReply.tv> wrote in message
news:pan.2004.05.21.14.06.17.720000@.JustPostReply. tv...
> Hi,
> We're in the process of architecting a very large SQL2K database. We
> believe this database will grow 2TB per month. 99% of the inserts will be
> done via bulk inserts. Approximately 3-4 per day an application that we
> wrote will query and pull approximately 6 million records at a time.
> * Will this work with SQL Enterprise or should I use DataCenter?
DataCenter is an OS-level difference. SQL stops at Enterprise Edition, but
it will run on DataCenter OS. You may need DataCenter, but only for the #of
CPUs it will support.
> * Will this operate on Win2k Enterprise or should I use Datacenter?
>
Again, you may need the memory and/or CPU scalability of Datacenter. A lot
depends on the number of concurrent users and the total load.
> * What I/O recommendations do you suggest (RAID or fiberchanner / RAID
> array, SAN, or NetSAN, memory on controller, etc) to keep up with a bulk
> insert of approximately 2,000 records per second?
SAN. Big SAN with multiple HBAs and paths.
> * Will failover clustering work with a database this size (stupid
> question but I have to ask)?
Sure. It may take some time to go through the recovery process during a
failover, but clustering does not affect scalability.
> Thanks!
> Jack
>