sql server msde: Hi, all,

I was wondering if it's possible to Migrate data from SQL 2000 Enterprise to
SQL 2000 MSDE?

We might have a situation where we need to migrate current database on SQL
2000 Enterprise to SQL 2000 MSDE.

If it's possible, is there a how-to websites?

hi,
[quoted text, click to view]

actually there's no migration path, as MSDE 2000 is SQL Server 2000.. it is
based on the very same code base, but includes some architectural
limitations, like a 2gb size limit for each database (as the sum of all data
files of each database)..
so, as regard the relational engine, you can simply "detach" a database from
the Enterprise instance and attach it to MSDE (as long as the database is
not bigger than the upper size limit)..
regards
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
--------- remove DMO to reply

Another related question:

I have a large database (10 GB) which was created and populated by SQL
Server 2000.

However, I need to make this historical data available - in READ ONLY
mode - on another server. As it will only be used for reading, it
seems like an overkill to install and pay for full SQL Server. Since
the database is large, using Access MDB format is also out of
question.

Question: will SQL Server Express or MSDE allow me to use a 10GB
database if data will not be updated, just viewed?

Thank you.


On Sep 12, 12:10 pm, "Andrea Montanari" <andrea.sql...@virgilio.it>
[quoted text, click to view]

hi,
[quoted text, click to view]

unfortunately not..
MSDE allows max 2gb sized databases, where SQLExpress limit is 4gb for each
db..
you have to "split" that data into more databases, if you want to follow
this "path"..
regards
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
--------- remove DMO to reply

OK, although your reply is in direct contradiction with another reply
from the group some time ago, which said that you CAN mount a database
larger than 2/4 GB but that you cannot 'update' nor 'insert' data into
it - and I do just need 'select'. I don't know who to believe. I guess
I will have to try and report back my 'findings'. Thank you anyway.

On Sep 12, 6:46 pm, "Andrea Montanari" <andrea.sql...@virgilio.it>
[quoted text, click to view]

hi,
[quoted text, click to view]

I know :)
from a technical wiewpoint, the engine will throw an exception as soon as
the storage engine needs to allocate/release pages from the database.. but
there's a legal one as well, where the license only allows for 2gb size per
database.. I'm not a lawyer, but consider those implications as well :)
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
--------- remove DMO to reply

[quoted text, click to view]

You still didn't tell me if I can mount a 10GB database that will not
be written to, just read from.

So, to go back to my original question - can I mount a 10 GB MDF file
onto MSDE while NEVER wanting nor needing to do an UPDATE, DELETE or
any other data modification operation?

If you just don't know (i.e. you've never tried), you are free to
avoid answering my question. Thank you!

hi,
[quoted text, click to view]

simply attaching a database that big, won't work...
for this test, I tried simply attaching a 2200 mb database and, (as
expected) I got an exception like

CREATE/ALTER DATABASE failed because the resulting cumulative database size
would exceed your licensed limit of 2048 MB per database. (Microsoft SQL
Server, Error: 1827)

so I followed another way I've heard was "technically available"... I took a
backup of the 2200mb database and restored it on MSDE.. it (unfortunately)
worked..
the test database only had 1 empty table, and I was later able to fill it,
as the storage engine was not required to allocate pages.. did not try a
real waorkload scenario, where rows get written, updated and or deleted,
just a stress load of 500mb of data..
but, again, please consider this probably is illegal...
regards
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
--------- remove DMO to reply

[quoted text, click to view]

I don't see anything unfortunate here... ;)

[quoted text, click to view]

Fantastic. This answers my question. Thank you for your time, you are
the man.

Just to report what I did in case it can help someone else.

Just attaching a 20GB database with sp_attach_db to MSDE did not work.

However, the following approach worked: In MSDE, I created a blank
database with the same file names as the 20GB database I was trying to
use with MDSE.

Then I shut down the MSDE from the Services control panel. I replaced
the small MDF and LDF files with larger ones, and restarted the MSSQL$
service. It worked like charm. I can even create views and update the
database, because I pre-allocated extra space before moving the large
database onto MSDE.

Thanks to Andrea for his help.