Conversion MSDE to SQL Server 2000

Here is the scoop:

I have MSDE installed on a production machine together with a bunch of ASP.NET applications. I was asked to replace MSDE with SQL Server 2000 and move every application from MSDE to SQL Server 2000 with minimum downtime.

Did any of you go through a similar process?

What is the best way of doing this conversion?

How to move the backups from MSDE to SQL Server?

Is it possible to have MSDE and SQL Server 2000 coexists for a time while moving stuff around and redirecting every connection?

Any hints or suggestions would be greatly appreciated.

Thanks.You could install Sql Server 2000 & use transfer objects (with data) to move the data between the databases.
MSDE is a "lite" version of Sql Server 2000, so you shouldn't need any conversions or transformations.|||Thanks for the answer, I did some research and I found this also, perhaps it will help others as well

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q325023

Content in MSDE

We have installed a application with MSDE distributed. Is
it possible for us to have a look of the content in the
databases stored in MSDE ?
It is a standalone PC. Is it necessary for us to get the
SA password for accessing the data ? However, if the
vendor hasn't told us, what is the best way to handle it ?
Thanks
Hi,
If you are the Administrator of the OS then you could try accessing SQL
Server using Windows Trusted connection.
Login into OS using an admin account and from command prompt try this
OSQL -S servername -E
This will allow you to to sql prompt . There you can type all TSQL commands
to see the data.
Thanks
Hari
SQL Server MVP
"Daniel" <anonymous@.discussions.microsoft.com> wrote in message
news:0e2701c53fc4$d68d94f0$a501280a@.phx.gbl...
> We have installed a application with MSDE distributed. Is
> it possible for us to have a look of the content in the
> databases stored in MSDE ?
> It is a standalone PC. Is it necessary for us to get the
> SA password for accessing the data ? However, if the
> vendor hasn't told us, what is the best way to handle it ?
> Thanks
|||"Daniel" <anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:0e2701c53fc4$d68d94f0$a501280a@.phx.gbl...
> We have installed a application with MSDE distributed. Is
> it possible for us to have a look of the content in the
> databases stored in MSDE ?
Thats what databases are designed for

> It is a standalone PC. Is it necessary for us to get the
> SA password for accessing the data ? However, if the
> vendor hasn't told us, what is the best way to handle it ?
No not nocessarily, you can also use Windows Authentification to connect to
the database. As a local administrator you are autom. in the system
administrators group (if you didnt change it so far).
try using OSQL connection with the Parameter -E, there you can fire all your
Statements you want to:
If Windows Auth is not activated try changing it via EM or if not applicable
though registry:
http://groups.google.de/groups?q=reg...phx.gbl&rnum=1
HTH, Jens Smeyer.

> Thanks

Constraing Question

Converting a program from VS03/MSDE to VS05 Pro/SQL Express...

In VS05, in Datasouse tab, "edit with Dataset with designer", click on relation between two tables, choose "edit relation" from context menu, under "Choose what to create" there are 3 radio buttons:

- Both relation of foreign key constraint

- Foregin Key constraint only

- Relation only

I have discovered that the choice made dramatically affects the Fill order of tables. In general I use the first one.

I would like more information on these options than I have been able to find in help.

Here is the specific issue (simplified)...two tables UserID -> Recordings. In a specific senerio, I must fill recordings table first, and use the Foreign Key to back track to get the UserID and fill the UserID table, which means for the Recordings table I use the radio button Relation only (otherwise I get a constraint error)

Would it be better to use the first radio button, "Both relation of foreign key constraint", but relax constraints when filling Recordings/UserID tables, and then enfore constraints again when done? Which is the better way?

Thanks for your help

Bob

hi Bob,

selecting the 1st radio performs both a database relation and an application code relation...

the 1st one is an actual object in the underlying database, a constraint object of foreign key..

the 2nd one only is a "soft" relation, an object not present in the database as a real constraint, but residing "in memory" in the actual "in memory database" represented by the dataset, which should be performed after the "fill" has been finished validating (if accordingly set) eventual constraints... but this only client side, in the "in memory database"...

obviously, better protection is set when the actual constraint is defined in the database as this is the place of the real "storage", where data should alway be "correct" and consistent...

as you are loading data from a "protected" source, the database with enforced fk constraint, it's a good idea to relax constraints when filling, as you already know they would be satisifed and enforced by the underlying datasource, so you can improve performance enforcing .Net relation's constraints after the initial filling, to protect application code in the disconnected scenario we all are working.. this way, erratic handling would be reported immidiatly without having to wait for the "sync" roundtrip to the actual datasource...

regards

Constant Disk Activity

While running MSDE it looks like the service hits the disk every 30
45 seconds. Any hints as to if / how to configure the service to not
hit the disk so often?
Do you have any reason to believe that is kind of activity is unusual? Are
there any clients connected to the MSDE instance that are doing extracting
data or doing updates or deletes? If data pages are being changed the the
Lazy Writer will be flushing them out to disk on a periodic basis.
Jim
"Ryan Columbus" <ryan_columbus@.agilent.com> wrote in message
news:d1ec1be8.0405181249.8c0609f@.posting.google.co m...
> While running MSDE it looks like the service hits the disk every 30 -
> 45 seconds. Any hints as to if / how to configure the service to not
> hit the disk so often?
|||Our application only connects with and interacts with the database
infrequently. We are seeing this disk activity constantly, whether
there is any application currently accessing the database or not.
|||You might try using Filemon from www.sysinternals.com. It's a free utility
that can show any file acivity on your system by any process, including SQL
Server. You can use it to see exactly what process is hitting the file
system and what file it is accessing.
Jim
"Ryan Columbus" <ryan_columbus@.agilent.com> wrote in message
news:d1ec1be8.0405191328.657e38c3@.posting.google.c om...
> Our application only connects with and interacts with the database
> infrequently. We are seeing this disk activity constantly, whether
> there is any application currently accessing the database or not.
|||ryan_columbus@.agilent.com (Ryan Columbus) wrote:
>While running MSDE it looks like the service hits the disk every 30
>45 seconds. Any hints as to if / how to configure the service to not
>hit the disk so often?
Why are you worrying about this? What problem are you trying to solve?
- Tim Roberts, timr@.probo.com
Providenza & Boekelheide, Inc
|||The hard drives that our product will be running on have a finite
lifetime (i.e. only a certain number of continuous hours of
operation). With MSDE constantly accessing the disk, the disk is
never able to spin down. Thus, the life of our product will be
significantly shortened if we cannot stop MSDE from making these
constant disk accesses.
|||We have used a similar tool to determine that MSDE is constantly
hitting the disk. It doesn't really matter which file it is
accessing. What we need is to find a way to stop MSDE from causing
this constant disk activity.
|||I'd check if the autoclose database option is turned on...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ryan Columbus" <ryan_columbus@.agilent.com> wrote in message
news:d1ec1be8.0405181249.8c0609f@.posting.google.co m...
> While running MSDE it looks like the service hits the disk every 30 -
> 45 seconds. Any hints as to if / how to configure the service to not
> hit the disk so often?