[Banana]

At my work I've been a de facto DBA and they like it so much they want to expand it, which necessitates migration from Access to SQL. Now, I am already familiar with executing SQL commands, but not so with setting up and administering a SQL database.

I was hoping anyone could recommend a good resources. FWIW, we may either use Mircosoft SQL Server or MySQL, but if the resources isn't specific to a vendor, the better. I don't just want a how-to guide, which I can easily find for whatever vendor I may use. I'm more interested in one that discuss the theory and show good design practices for managing SQL database.

The other thing I'm kind of curious is, would you prefer either CLI or GUI over the other? I'm terribly tempted to say GUI as the data relationship are very complex that if I were to go CLI, I'd have to do lot of paper scribbling to make sure I have the query command correct. Some articles favoring CLI basically says that GUI just doesn't do what you intends to do and if you guys do think CLI is the way to go, I'd like to know how one would keep track of relationships with minimum of paper scribbling or whatever. I'm trying with the idea of having a dummy copy of the database in Access GUI to help me sort through the tables and fields fast then write down the SQL command myself into the CLI, but maybe you have a better suggestions.

Your advices is much appreciated.

[Wyatt]

I don't know about teaching others how to do it, but as for my two cents, I'd rather set up a database with a GUI and build queries with text. However, some GUIs are pretty slick (I really like Microsoft's SQL Enterprise Manager). For a new user, though, a GUI is probably your best bet.

[Majost]

The MySQL documentation is quite thorough and well written. I started without any experience, but that has always had the answers I needed. I'm really not a DBA, though... I just manage my personal MySQL servers for hobby-ish stuff, and one at work for minor things here and there.

Just as you mention, I use the CLI for quick checks, but I often resort to a GUI Client for more fancy stuff (or if I just forget a command or something). It's not the prettiest app, but it works (and I haven't found anything better for Macs).

[kretara]

CLI vs. GUI:
I have been a staunch CLI user for quite a while now (mySQL and Oracle) and I generally prefer it, but, now that I have a job where I am dealing with 100's of tables at a time, at least two dozen tablespaces and some 30TB of data I find myself using an Oracle GUI (PLSQL Developer) for the quick stuff (maybe 20% of the time) and the CLI for anything intensive. There are many mySQL GUI clients out there for most platforms and many of them are very nice -- as far as GUI goes).

IMHO, I would go with mySQL (using innodb only) over sql server. MySQL gives you the ability to run on pretty much every platform (instead of being limited to windows), has a pretty nice replication system, a good failover system, is pretty damn fast, uses little memory and is FREE (paid support is available). I have used mySQL since 2000 and I enjoy its ease of use, speed and reliability.

If you are looking for a more Oracle like database that is free/affordable, then look no further than Postgress SQL. Postgress is a really good database that has full support for triggers (not in mySQL yet), procedures and all that fun *REAL* database stuff. It has a few weaknesses though: 1) no support agreement available (that I know of) and 2) failover is not as robust as it should be.

sql server is OK if you want to limit yourself to windows, want to live in the GUI most of the time and are willing to jump through hoops to set up and maintain. If I had to spend that kind of money (server license plus DB license) I would go with Oracle. Oracle is a much better DB in all respects.

[Banana]

Great info!

It's good to know that MySQL documentation is able to at least a total noob up and running. But the reason I asked earlier about theortical aim was because Microsoft certainly did not address this in their Access documentation. I had to learn the hard way the rules of normalization and proper database design and only because I regularly posted to a Access forum, I learned more about what I should know about developing a database than I did from manuals. Manuals are good if you need to know how to do a specific action, but won't tell you if you really need to do that particular action. I hope MySQL documentation covers this as well?

As for cost, I'm under the impression that my company already has a SQL server, so the cost is already sunken so to speak, though last time I talked with my IT and he mentioned MySQL in passing. Whether that was intended to be an endorsement or notifying of available options, I'm not sure. Will need to ask him.

That said, the lack of triggers (is that same thing as stored procedures?) is a bit worrying. But I'm not 100% sure if I understand the whole concept of having stored procedures, since the front end clients does all the coding for actions not otherwise executable within SQL. There are few instances where I need to manage data in a certain way, so those few lines of codes are crucial... Would triggers be necessary in that circumstances?

[kretara]

Originally Posted by Banana Great info! ......

What is a stored procedure?: A stored procedure is a subroutine available to applications accessing a relational database system. Typical uses for stored procedures include data validation (integrated into the database) or access control mechanisms.

What is a trigger?: A database trigger is procedural code that is automatically executed in response to certain events on a particular table in a database. Triggers can restrict access to specific data, perform logging, or audit data modifications.


Neither are a must, but both can be nice. As far as I know (and someone please correct me here), Access does not have either native/true triggers or procedures. Moving from Access to a real sql database is a step in the right direction. You will soon find out just how much you had to limit yourself in coding just by using Access. A whole new world is now open for you. You can make extensive use of PHP, perl, C, C++, Java, shell (where I spend much of my day), python, ruby or most any other programming/scripting language with a real database and you will not have the problems that you get with Access (slow everything, very limited number of users, really bad dynamic web ability and general instability). Yes, I have used Access (and Filemaker) quite a bit on the web and as backends for apps.

In all my years using mySQL, I only found the need for a trigger once. Most of my mySQL stuff has been used on the web: storage for dynamic web site or when I built a web based product entry app. I have also used mySQL to store data for later manipulation by either client or server side programs. mySQL also has built in stuff that takes the place of triggers. My favorite is the auto_increment field. This is a numeric field that, well....auto increments the value of the field when a new row is added. Oracle does NOT have this. You have to use a sequence field and call a trigger to get the same effect (what a PITA that is).

Basically, you can take care of the need for triggers or procedures in your code and this is what I have done for years. mySQL should get triggers fairly soon.


I now make use of triggers rarely and procedures extensively, but this is only because I am working in a data warehouse and we are importing/sorting/filtering 1-3Tb of data on a daily basis (and this is just for my tiny department).

Edit:
being a DBA has multiple aspects.
1) you need to know how to keep the database working with the OS in an efficient manner
2) you need to know how to tune your database
3) you need to know how to troubleshoot your database
4) you need to know something about networking to do some troubleshooting
5) you need to know how to tune queries
6) you need to know how to build data structures (tables) and WHY to build them a certain way (normalization)

As has already been pointed out, the mysql site has extensive documentation on 2,3 and 5 above.

It takes quite a while to become proficient in all of the above. I have been working with databases on a daily basis since 2000 and I still don't know all that stuff. Just get a good overview of normalization, read the mysql docs (in many ways they apply to all sql databases) and you should be good to start off.

You really need to look over normalization. This is a very fundamental aspect of SQL databases that will come back to bite you in the ass if you don't build your tables/structure your data to account for it. Access let you get away with sloppy data habits, a SQL database is not as forgiving.

[Banana]

Thanks for taking time to explain the differences. It's also good to be reassured that they're not totally necessary.

Yes, you're correct that Access doesn't natively support stored procedure, though it could be mimed. Access was just fine when it was just for our office. But now the entire company (which has two other offices) wants to use it, Access is going to be simply a front end client; no way I'm going to hog the network bandwidth and pissing off IT guy in the process.

Do you have any idea of how easy it is to migrate data? One benefit (doubtful at that, really) of using SQL server is that there's a upsizing wizard which may help streamline the move but I've been told that it's not always accurate and I may have to get my hands dirty anyway. And as I indicated before, I'll just keep Access as a front-end mainly because I can develop new clients much more faster in Access than if I did the same thing with VB or C# or webpage. Shorter and simpler the devleopment cycle, the better, especially as the database is based on contracts that may change year to year.

[kretara]

Originally Posted by Banana Do you have any idea of how easy it is to migrate data? One benefit (doubtful at that, really) of using SQL server is that there's a upsizing wizard which may help streamline the move but I've been told that it's not always accurate and I may have to get my hands dirty anyway. And as I indicated before, I'll just keep Access as a front-end mainly because I can develop new clients much more faster in Access than if I did the same thing with VB or C# or webpage. Shorter and simpler the devleopment cycle, the better, especially as the database is based on contracts that may change year to year.

The SQL Server migration wizard is indeed buggy. Don't be surprised if some of your data ends up as garbage, ends up in the wrong fields or the migration tool errors out again and again. Of course, you might get lucky.

It has been a while since I used Access, but I believe that you can export your table structure (which can be used to create tables in a SQL database) and then export your data in a CSV limited file (minus column names). You can very quickly and easily import the CSV file into most any SQL database. You could also write an app in *insert your favorite language here* to move your data over for you, validate the data and fix/flag any data with issues.

I would look at this as a fantastic time to go through the data and fix any issues with it and normalize it if possible. This will take quite a bit of time, but in the end it is well worth it since it will greatly assist in speeding up your database, your coding process and your debugging process.

[Banana]

Yeah, that'd make for a good excuse to review my data structure. I've already normalized the database last summer with lot of consultation with Access gurus, but another review, especially for my queries would be great. I've had moments where I wondered if I could be doing my queries better.

Once again, thanks for the information!

That said, I'm still hoping someone can recommend a good resource specifically about good practices for administration of a SQL database.

[JKitterman]

For Oracle, there is the 2 day dba tutorials. Have you searched Microsoft for anything there yet?