SQL Server Express? (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

WantToLearn -> SQL Server Express? (10/28/2005 16:30:09)

Does anyone know much about SQL Server Express? From the little that I have read, it seems like it could be a viable alternative to ms access. I'm hoping it will be more powerful than access in that it will be able to handle more concurrent users and yet still be a simple, easy to move around .mdf file like the .mdb file. What do you think?




Spooky -> RE: SQL Server Express? (10/28/2005 16:40:25)

Its more based on SQL server, so the 'file' is not portable like an access file.
To use the data / store the data elsewhere, it would need to be exported to a file or another server.

It is a great move to make - it will make any application more reliable than Access.



WantToLearn -> RE: SQL Server Express? (10/28/2005 17:13:36)

What is XCopy for then?

From Microsoft:
quote:

Application XCopy is a new feature in SQL Server Express that provides the ability to treat databases like files. The local database can now be moved, copied, or e-mailed along with the application. At the new location, no additional configuration is needed to make it work. There are three main features that enable the Application XCopy support in SQL Server Express: the AttachDBFilename option in the connection string, the lack of a requirement to specify the logical database name, and Auto-Close.
Source: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/sseoverview.asp






yogaboy -> RE: SQL Server Express? (10/29/2005 6:34:46)

In SQL Server 2000 you could move a database by dettaching and reattaching the database. The database is stored in a file and you can then move it. I expect the same will be happening in 2005 but they'll have simplified the process so that you just have to specify the database and XCopy will take care of the dettaching process.

To use the 2000 "Express" edition you needed a full SQL Server licence, so I expect it will also be similar for 2005. Don't be naughty! [;)]



WantToLearn -> RE: SQL Server Express? (10/29/2005 14:50:13)

Thanks Spooky and Yogaboy for your input. As you can tell, I'm a complete novice when it comes to SQL server.

How hard is it to move (or dettach and reattach) a sql server database? For example, I have a db driven ASP website. My hosting company provides 10 SQL server 2000 databases and I used their online control panel to create the database and it is now full of data. What happens if I need to change my web hosting service? Obviously with Access, it was easy: the mdb file just gets published to the new server along with all the other web files. What's the process for sql server?

Thanks in advance,



yogaboy -> RE: SQL Server Express? (10/29/2005 15:10:54)

There's a couple of ways you can move the data. The easiest (by far if you're a novice) is to attach and re-attach the database.

Using Enterprise Damager if you right-click your database and look at the properties on the datafiles tab you can see where the files for your database are kept. It's almost 100% likely that the database is on 1 file with an .mdf extension, and it should have a counterpart with an .ldf extension that holds the log files.

Using Query Analyzer, put in the follow commands

exec sp_detach_db 'yourdatabasename'

then take the files and move them to the new server and using QA put in the following commands

exec sp_attach_db @dbname = 'yourdatabasename',
@filename1 = 'c:\program files\microsoft sql server\mssql\data\yourdatabasename.mdf',
@filename2 = 'c:\program files\microsoft sql server\mssql\data\yourdatabasename.ldf'

obviously you need to substitute the filenames/database names/file paths, and if you're using a webhost then they will probably deal with the attaching of the database.

If your host asks for a backup copy, then have a look in Books Online for how to set it up, but it's not so hard if you use Enterprise Damager, but the attach method is made for this type of situation.




WantToLearn -> RE: SQL Server Express? (10/29/2005 17:36:42)

Thanks yogaboy.

So what exactly is Enterprise Manager and Query Analyzer and how do I get them? I imagine that they are insanely expensive and/or my host will not want me using them since they are probably advanced admin tools that can easily become "Enterprise Damagers". Do I need to find a host that provides them?

Thanks



yogaboy -> RE: SQL Server Express? (10/30/2005 3:39:30)

They come with the copy of SQL Server, and you can download an evaluation copy from the microsoft site. When you go through the install you can select just to install the client tools. Your host should have no problem with you accessing your databases using these tools, they should be your most basic set of tools. There are others around but QA is really good and you can do almost anything with it - it's a text based tool so you need to know your sql and T-SQL, and EM makes things easy in the Access sort of way because it's GUI based.

Make sure you install Books Online (BOL) as it is also really good documentation and there's very little they've missed out.

The eval copy is a big download, but it's probably worth running your own copy anyway, that way you can muck around with it and break it with no worries before you try it out on the production version! [&:]

If you install the full SQL Server, it will require a key after 30 days or something, but MSDE (called Express in 2005) doesn't ask for a licence key, just assumes you have one.



WantToLearn -> RE: SQL Server Express? (10/31/2005 14:57:54)

Thanks for all the great info.

Do the client tools expire or just the full sql server?



rdouglass -> RE: SQL Server Express? (10/31/2005 15:22:04)

quote:

Enterprise Damager


[:D] That's funny!

quote:

To use the 2000 "Express" edition you needed a full SQL Server licence


I was almost positive MSDE is a freebie. Are you sure about this? If so, I'm breaking EULA's all over the place. [:o]

Or is it you only have to have 1 license for as many "Express" as you want? I use MSDE all over the place for my development servers - not production servers byt my dev ones.



rdouglass -> RE: SQL Server Express? (10/31/2005 15:22:49)

quote:

Do the client tools expire or just the full sql server?


Just the SQL Server application itself. The client tools do not.



WantToLearn -> RE: SQL Server Express? (10/31/2005 15:49:31)

quote:

ORIGINAL: rdouglass
Just the SQL Server application itself. The client tools do not.


Thanks.

I just checked microsoft's site and sql 2000 eval copy is a wopping 274MB!



yogaboy -> RE: SQL Server Express? (10/31/2005 18:10:02)

quote:

I was almost positive MSDE is a freebie. Are you sure about this? If so, I'm breaking EULA's all over the place.


Have you ever known M$ to give us anything for free?[8D]

The licencing is very complicated, but the long and the short of it is for each device using MSDE you need a per-seat client access licence. And the tools are only valid for the 120 day trial too.

But! If your host has licences and you're using them then I'd be fairly sure you're covered, especially if you're using the MSDE or Express for development. It's once you bundle it with an app and sell it that you'll really get into trouble.

Don't worry, I'll come and visit you in jail RD [:D]



rdouglass -> RE: SQL Server Express? (11/1/2005 8:26:49)

quote:

each device using MSDE you need a per-seat client access licence.


Are you sure? I found this on MS's site and the very first question / answer contradicts this:

quote:

Q. Can I obtain MSDE for use with my applications without purchasing any Microsoft products or services?
A. Yes. MSDE is available for download at no charge.


http://www.microsoft.com/sql/msde/howtobuy/msdeuse.mspx

I think the licensing you were talking about has to do with the tools themselves and not the actual MSDE.



yogaboy -> RE: SQL Server Express? (11/1/2005 8:50:05)

Nope, I was getting my info from SAM's Teach Yourself SQL Server in 21 Days (it's taken more than 21 days I can tell you![:D])

anyway, the next page along from the link you posted gives a better explanation
http://www.microsoft.com/sql/msde/howtobuy/msderights.mspx for example
quote:

Office XP Professional does not convey rights to redistribute MSDE. Office XP includes MSDE 2000 on its CD-ROM in the MSDE 2000 folder.
but also
quote:

MSDE 2000 redistribution rights are included in the EULA for the ASP.NET Web Matrix tool for the purpose of developing applications in that tool that are fully compatible with SQL Server 2000. You will need client access licenses (CALs) or processor licenses to deploy MSDE 2000 applications that access SQL Server back-end data and resources.
etc etc

They've got loads of caveats, it all depends on how you're going to use it and with what other MS product, but basically you need a EULA saying you can redistribute it or use it as a server a this line says
quote:

Using MSDE does not reduce or eliminate the need for client access licenses (CALs) when interacting with SQL Server 2000 Standard Edition or SQL Server 2000 Enterprise Edition in a production environment.


But for use with development it's not a problem - MS would (and do) encourage that use, as it ends up with more people using their products. Very little point in using SQL Server for dev and then using DB2 for the production environment!



rdouglass -> RE: SQL Server Express? (11/1/2005 10:45:24)

quote:

They've got loads of caveats, it all depends on how you're going to use it


Isn't that always the case? [;)]

And they talk about interaction with SQL Server as needing CAL's. I understand that but they also say you can use MSDE (by itself) for up to 25 users. Now there is no interaction with SQL Server in that case.

What they're really trying to do is to prevent 10 MSDE servers acting as a front end to 1 SQL Server so that only 10 CAL's would be required. At least that's how I see it.

Regardless, there are situations (quite a few of them in fact) where you can use MSDE totally (and I mean totally) free.



yogaboy -> RE: SQL Server Express? (11/1/2005 11:41:54)

I disagree. The only real situation where you can use it free is when you're using an app someone else developed - so you don't have to purchase their app and then buy a SQL Server licence - or if you are developing something yourself.

Why? Because MSDE doesn't come with a GUI so you are forced to use either an app to access it, or development tools. If you are using the QA and the Damager then you need to have a SQL Server licence. Or you need to buy someone else's tools.

Download it for free. Install it for free. Then you can't access it without paying someone.


Anyway, the back to the main point, I would advise against using MSDE as a web-database because of the limited number of connections. Ok for Intranet/small-office, but not on the web. Express might be different and have a higher limit, but it would need to get close to a 1000 before I'd recommend it as a web-database.

If you want something free and solid for the web then use mySQL 4 or 5.



rdouglass -> RE: SQL Server Express? (11/1/2005 12:06:35)

quote:

I would advise against using MSDE as a web-database because of the limited number of connections.


Well, I'd consider it far more robust than Access. And I've done webs with Access that support 100+ users (very busily hitting it at minimum once every 2 minutes) with no problems whatsoever.

I'm not saying don't use mySQL. I just am saying that many times these lower-end databases take the rap for poor coding techniques.

And for MSDE having a 25 user limit, many folks seem to forget that is "concurrent" use / open connection. WIth proper coding (and staying away from those @#$% recordsets) you could easily support 500 users if the queries weren't too complex.

I've heard people say "More than 10 users? Forget Access." and I say "Hogwash." I know better from personal experience that is *not* the case. And I can provide code and examples to prove it. I recently sped up an Access based web by over 4000% (yes that's 4K - 40 times faster) by just properly opening and closing the database connections and getting rid of recordsets. That client was having a hard time supporting 25 users at a time and now can easily handle over 200.

If I was to post on this board that Access can easily handle 200 users, people would think I'm nuts. Well, maybe I am but my techniques work. And I'm not recommending Access for 200 user webs but I am recommending proper coding.

It's unfortunate but it's things like the DRW that get us into trouble. I used to use the DRW all the time 'cause it was the only way I knew how. But then I kept hitting these walls and switched to just ASP (on Win platform anyways) and I've never regretted it for a second. The DRW get's us into trouble because it's full of code bloat and is slow. So yes, if you're using a DRW then 10 is the limit. [;)]

And I close with "MSDE *is* free. The tools to work with it are not."



yogaboy -> RE: SQL Server Express? (11/1/2005 12:09:08)

I agree with you about Access, it's better than peeps think - but I still think you're nuts[;)]



rdouglass -> RE: SQL Server Express? (11/1/2005 12:21:09)

quote:

but I still think you're nuts


Yay!!!!!!!! Someone does! [:D]



Spooky -> RE: SQL Server Express? (11/1/2005 14:00:54)

[sm=bowdown.gif]



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.09375