SQL Server Express...where to start (1 Viewer)

Well, I have been searching the net and experimenting the last couple of days and I'm lost... I want to try upsizing a DB to a SQL server backend... After a lot of reading I'm now unsure of which version to use, or where to find any tutorials on doing this! It looks like SQL Express 2008 is a very new release and doesn't have some of the addon features yet..(Server Management Studio Express).Should I load SQL Server Express 2005? Any advice here would be helpful.
Thanks

I would use sql 2005 express, there may be some compatability issues with sql server 2008 express.

You do not get the management tools with sql express you need to download them separately from here:

http://www.microsoft.com/technet/pro...expwssmse.mspx

Q. Can I take this and install it on a clients server and run several users on it - All for free?

Ken, this page says it's free and re-distributable, so I guess, yeah. (Note: Microsoft There are, however limitations imposed on it (e.g. no multiple CPU or utilizing more than 1 GB of RAM).

HTH.

Hum... Thanks. So whats the difference between this and the version you pay something like $1200 for and get 5 client licesnes?

It's fundamentally same; you just don't have the access to as many tools as normally available with the regular version and could hit a performance wall much sooner due to the cap.

The limitation and the fact that you will have to upgrade and/or scale up eventually was what led my company to use MySQL; they don't want to pay $$ when there's just as good software available for free. To each their own.

So with MySQL do you simply hook in using ODBC like you would with SQL Server?

Pretty much, yes. It's quite transparent, so for Access 2003, I don't expect any significant performance difference since we're using same protocols (Well, there's also OLE DB which is supposed to succeed ODBC, but in some case such as MySQL, the ODBC driver is far more optimized and developed for ODBC than OLE DB). I am not clear, however, if this is still the case with 2007 as I saw a blurb about using native driver instead of ODBC, but I could be entirely wrong.

Thanks - I'll look into MySQL.

I was hoping for a resource for SQL Server Express that would spell it out real clear regarding things like what the limits were. Like you can only use it for 5 concurent users and 1gig total db size, etc.

If the wikipedia is accurate, there's no longer the limit on concurrent users as was the case with MSDE.

Thanks - I quess there's only one way to find out for sure huh...

I guess here is about as good a place to start asking questions as anywhere. The chances are pretty good that you will come across someone who can answer your questions - even if you have to wait a while for the world to turn to accommodate everyone's time zones

I would love to spend the next 45 minutes discussing each of the various editions in-depth, but I reckon that Buck Woody has already done an excellent job at http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=7

The major points from a business point of view are:

1. An enterprise class database is not implemented just for the sake of stability of product. That is a given. It is not simply a more mature version of Access. It is not just a bigger storage space to hold tables and views, while you continue developing in Delphi/Access/VB/ASP/Whatever
2. The decision to go with any enterprise class database is not just based on the product, but also the mission and vision of you company - and how your IT infrastructure is going to adapt itself rapidly to the objective set out in your company's Vision & Mission statements - as well as the Key Performance Indicators that have been established to measure your performance by. This includes considerations about:
   • The platform you are intending investing in for the next 3-5 years (if it is going to be clearly a non-Microsoft environment then you need to consider a non-Microsoft database)
   • The cost of bringing in support in your city. If the median hourly cost of a non-Microsoft specialist is lower than that of a Microsoft specialist - and the cost of training in that environment is lower, then obviously a non-Microsoft platform is the right investment.
   • If you never intend redesigning your environment, but would consider re-engineering from scratch when you reach you next major growth point (especially since you have just hit your first major growth point - hence the enterprise DB consideration) - then it doesn't matter what DB you choose now. otherwise consider investing in the "FREE" DB from either Oracle or Microsoft, which guarantees a smooth transition upwards when you hit your next growth point.

I have a definite preference for SQL Server on a Microsoft platform and Oracle on a non-MS platform.

The reason is that both offer a very wide range of additional tools that you may not use for the first 18 months as you learn the intricacies of T-SQL and using the beast, but once you start looking for the tools that come standard with these products, such as Reporting Services, Analysis Services, Integration Services, Service Broker, Database Mail, SQL Profiler, etc,etc - then it is nice to know that you have already learnt the foundation of a single platform and can dig deeply into its depths.

I would strongly suggest investing the time in getting up to speed with SQL Server Express. It is a good product that can hold its own in a very competitive market. It cannot support failover clustering, database mirroring, 20 instances of SQL Server running or cope with 20GB of RAM, but you know that if this becomes an issue, that it will upgrade to that environment easily without you having to do anything other than spend some time in front of your server with a new copy of a purchased big brother. And when that time does eventually come, you would have had to consider digging in your pocket anyway, because MySQL doesn't provide you with all of these products that are guaranteed to work seamlessly by its developers.

I recently went through a sizable evaluation of several of the database products and security tricks and tools. I currently have several very large databases running under MySql. My data integrity requirements are sky high, and with MySql I have found data to occasionally just vanish. My audit and archive files allow me to recover, but many times I have to jump through hoops to recover the lost data. Thus, the process of evaluating products began. I found the SQL Server Express 2005 full package with tools to be the best of the bunch. Security is still an issue. I helped create the security on Mainframe systems, and advised on server environments. If there is someone who has permissions, no matter how low, on your server who has the expertise, they can access and decript everything. There is still no way to stop the expert on a server environment. (For example, I can plant code in the server that creates a 2 tier server environment. The primary tier is mine and is invisible to all but me, and I have have total access to the real server since it is running virtually under my new operating environment (I wont tell you how to do this for obvious reasons)). The point I am trying to make is your security should be high enough that the effort to compomize the environment exceeds the expected return. In most cases, this means you need the minimum amount of reasonable security. In one of my government contracts, I identified the person who broke the security on the application by matching the data that was compromised to the time the data was accessed. It was accessed at the administators lunch break. The culprit was a new freind who was on the phone, video taping the adminstrator entering his password. The site only allowed admistrator access from specific IP addresses. Thats why the freind missed lunch, to allow him to log onto the administrators PC with his id and password. See how easy it is to overcome security in today's world. SQL Server Express 2005 access speeds are significantly faster than MySql on joined requests where there are 100,000+ record databases involved. Standard SQL Server security provides the majority of the world sufficient security, with no additional encryption or security tricks. So that package should meet your data security needs. As a developer, the issue becomes more complex at keeping your applcations secure. There are several toolkits available that will store your forms and applications on SQL Server tables, load them dynamically, and make them vanish on close. This limits your development exposure to those who actually use the forms. Again, do not over estimate the value of your code, and do not underestimate the capability of others to sneak a modification into your code to suite their purposes. That is why an off-site version should be used at regular intervals to compare against the current production version. Oh by the way, 1 Gig of data may not sound lke a lot, but with proper normalization, it is incredibly huge (millions of records). I do tend to ramble at times, but I do know what you are going through. Just step back, take a breath and evaluate your needs. For every one day of planning, you save a month of development effort.
Smiles
Bob

Thanks for that feedback Bob & Tokoloshi - A lot to consider...

MagicMan,

Thanks for sharing your experience.

Can I ask what version did you use, and what storage engine did you use. The "data vanishing" part is a bit disquieting.

I will check tonight. I am at home atm...if I remember...so many thing fade with age.
Smiles
PS: Remember, I have huge complex tables with significant activity. Even with SQL Server, data can vanish, but SQL Sever has better recovery processes. Most lost data, when I track it back, is due to operator failure, power failure, application exceptions (this is the one that bothers me...the application fails on an update process and the record gets tagged in MySql...eveything is fine until the indexes are rebuilt, and poof....record is gone...seems the last thing MySql does is update the index, after a crash the data and old index are still joined, but on a rebuild, they lose the connection....haven't been able to prove this yet...but appears that way to me). So with the volume, the failure rate is very low and almost always due to an exception condition.

Thanks-

Personally, I would not bother with MySQL earlier than 5.0 for several reasons, indeed, there is a marked difference between 5.0 and the earlier version (I'm using 5.1 which is in RC stage) which made big strides in data integrity (#1 in my list of priority) and optimization (nice to have, but no big deal given the complexity of application and my users' workflow).

Version is 4.018 or something like that. So that may be part of my MySql trouble. Still, once burned, twice shy.

Thanks for sharing, MagicMan! Do you happen to remember if you used MyISAM or InnoDb (or combination?)

I certainly do not blame you for being shy; I would be myself!

I'm not sure, I really haven't even looked at the current system. I am developing a replacement for my current application. I simply unloaded the databases to excel and imported the data to ACCESS. Since then I have tweaked the application to run accde, dns and dns-less to SQL Server. From the performance, the existing MySql app appears to act more like Innobase than MyIsam. 2nd iterations of queries are faster (InnoDB) and the more memory, the faster the query (InnoDB), where MyIsam is more consistent in query speed. Innodb is perceived as slow access times, but given enough memory, it outperforms MyIsam in my experience. Yes, I know...my website server supplies MySql free to all subscribers...lol...so I have dabbled.