Migrating / Exporting Database from MS Access 2003 to SQL 2005 Express Edition

What you suggest .. Going with SQL 2005 Express will be better or 2008 will be better ?

Yes, i am migrating only Database and not Forms or Reports

Well, not really an SQL guy; however, I'm pretty certain that 2008 would be preferred over 2005 - now 5 years old.

mx

Also i can write Stored Procedures .. and also SQL Express edition is a light weight edition and be easily deployed on any computers .. Am i right ?

"and be easily deployed on any computers "
Not 100% sure on Express # of users.  It is limited to 4GB I believe.  I'm sure you can write Stored Procedures.

mx

Yes, Stored Procedures are available in Express.  I would recommend SQL Server 2008.  With SQL 2008 R2 Express you get up to 10gb, there is no specific user-limit; however, Express is limited to 1 cpu and 1gb ram, so they figure you will outgrow it eventually. *smile*

1 GB of ram?  Are you sure?  Doesn't make sense.  Most all new computers come with 4GB ...

Yes, unfortunately. I was reminded of this the other day.  SQL Express will not take full advantage of the computer having 4GB.  Considering this is typically a solution for desktop usage, the extra memory gets used by the users other applications anyway.  Quite honestly, I run SQL Express and do a fair bit on it and don't notice the limitation to any point of frustration.  Think its limitation is solely to stop a high-use application to grow over time and the owner never having a reason to license higher editions of SQL.

Now if you developing an application using embedded or compact edition, then the limit is whatever the OS can handle but database max is reduced to 4GB.

http://www.microsoft.com/sqlserver/2008/en/us/editions.aspx

ok.  Interesting limitation, but I *suppose* ... it makes sense ...

mx

Whether this approach will work?

1) Using SSIS, i am planning to export MS Access DB to SQL 2005 Standard Edition Database.

2) Once SQL DB is created, i will detach it and attach with SQL 2005 Express Edition

I am doing this double work, since i am afraid SSIS is not supported in Express Edition

1) I have exported MS Access DB to SQL 2005.

2) Then i detached SQL 2005 DB

3) I copied DB to my local working folder ( .mdf and .ldf )

4) I am able to attach .mdf to SQL 2005 Express Edition

Now, i am not able to create a Table or Drop a Table or .. any activity ..
It throws an exception saying that Database is readonly
Issue.JPG

Ensure the proper permissions are set as well as check the properties of the SQL database weren't set to READONLY in the transition.  It is likely the former issue, though, as the logins would not have transfered so you probably just need to map your login back to appropriate role on the database you attached.

Do i need missing some steps while detaching from SQL 2005 and attaching to SQL 2005 Express Edition?

Not really.  You are switching servers, so same issues/rules apply.  You can move the database, but you are not inheriting the *server* configuration.  Logins are a server level concern.  You just need to map your login to database user or role which should have copied over okay from detach/attach process.

Okay,

Now, i have SQL Express DB which is working well and able to interact with .Net App

On taking build version or deploying this application along with SQL Express on client machine(s), do i need to pass the same connection string or connection string varies from Client Machine - Machine ..?

As long as the clients have all installed the SQL Server client drivers you should be fine.  They just have to install everything in the same fashion -- If you are embedding the DB in an application you are deploying, you may also want to look at Compact Edition; however, you should be fine with SQL Express.  If it is real light weight, .NET has a library for SQLite also, but that might be a dirty word in a SQL 2005 thread so I will stop there before my SQL buddies here oust me from the club. *smile*

But seriously as IT professionals, it is good to love technologies like I love SQL Server; however, it is always prudent to know the alternatives and start your design vendor agnostic and then go hunting for the right pieces to the puzzle which it is fine to have a vendor of choice with which you do so.

Regards,
Kevin

If you have difficulties with the .NET side, I would recommend asking a follow question on that side of the house with more code details ...

Kevin .. i just have one last question.

My .Net Application has a local Database.
Initially, i was using MS Access and then i moved to SQL Express.

Now question is when i install .Net Application on client machine, do i need to install SQL Express Installer also on every machine ..
Or just attaching a local version of DB Is fine .. ?

If I am not mistaken, you will need to install SQL Express.  The last time I did an application like that, it appeared as if you could just use the MDF/SDF on the clients, but I always found they needed to install Express (desktop edition when I last did that) or compact edition.  Therefore, unless this is drastically changed, the clients will have to one of the free versions of SQL installed.

SQLite is just a file, so in that case you would not need anything.

Again, you can verify this with the .NET geniuses.

>>Now question is when i install .Net Application on client machine, do i need to install SQL Express Installer also on every machine ..<<
It depends.  You will need to install it whereever you have the databases.  The rest of the workstations just need the client components and it would be over-kill and a waste of CPU/Memory/disk space to install SQL Server Express on all.

That is assuming a server / client application, AC.  Sounded like the application uses a local MDF and question is does that require SQL Express to attach in application and believe the answer to be Yes.  Should it be done is a different matter which you just addressed, but can you confirm attaching MDF via .NET no longer needing SQL locally installed ?

>>Sounded like the application uses a local MDF and question is does that require SQL Express to attach in application and believe the answer to be Yes.<<
Right, if each one requires their own database then they will need to install SQL Server Express on all workstations.

>>Should it be done is a different matter which you just addressed, but can you confirm attaching MDF via .NET no longer needing SQL locally installed ?<<
You can attach a database from anywhere, so long as you belong to the local Administrators group.

Yes acperkins, you are right ... This should be a locally installed database.

Later we sync with all these local database to the corporate database.

>> Later we sync with all these local database to the corporate database.
Then it probably makes sense to install SQL Express or Compact Edition on each client as you will probably want to utilize the SQL Server Subscription Services having these endpoints all read from the Corporate SQL Server.

1)  Can i assume, each and every computer will have different connection string.

i.e I am working on Computer1 with connection string as follows ..

Server=.\SQLExpress;AttachDbFilename=c:\mydbfile.mdf;Database=dbname; Trusted_Connection=Yes;

Whether this will change from Client1Computer to Client2Computer etc ..!


2) Database which i created from VS 2005 Environment is not reflecting or not able to drag from SQL 2005 Management Studio. What should be the possible reason?