Database Management 101

Results 1 to 4 of 4
  1. #1
    LordJustice crosenblum is offline
    True MemberRank
    Apr 2007 Join Date
    Burnsville, MNLocation
    401Posts

    Database Management 101

    I know that many of us are not database wizards, dba's, etc. Have basic knowledge to edit/create data, tables, and set it up so that kal can connect to it.

    But databases are in some way's similar to car's, they require maintenance, they require tuning, and downtime.

    I am not a dba, but have worked with sql server for the last 4-5 years to know enough, that there are a few basic thing's, kal servers really need to do on a regular basis.

    1. Table Reindexing - Basically you have shitloads of data being added/removed, your tables get defragmented, the indexes are what help a table work efficiently. And they get screwy over time.

    Tips for Rebuilding Indexes - SQL Server Performance

    There are many different basic scripts for reindexing all tables, I would recommend you create a stored procedure, then use php, since most of us only have SQL Express, to run it once a month, every x months, to keep the table's indexed.

    Read the articles, make sure you clearly understand the pro's and con's. I am just here to get you started.

    2. Transaction Log Maintenance - Any time there are any data updates, that get's a record added to the Transaction log, so when you have an active server, there are 100's to 1000's of records or more being added to the transaction log.

    However, the transaction log can be used to do a rollback, to a specific date and time, and this can be highly useful.

    However transaction log files can grow huge and fast, and cause problems in the performance and stability of your server. This method involves clearing completely the transaction log, like getting rid of your temp files in your pc, however, once the transaction log file is gone, outside of any backup, there is no rollback method.

    3. Database Backups - Backups are important, but the key thing is what to backup, how often, what data do you want to backup, how do many days do you want to keep backups.

    SQL Server Backup, Integrity Check, Index and Statistics Maintenance

    There's so much information, it's hard to get down to what works, what doesn't and why.

    Databases are not something you can set and ignore, because data can get corrupt, you forget to do backups etc.

    I realize this is not a complete final easy DIY solution, but a starting point to hope to get you learning.

    Goood luck.


  2. #2
    :O strik3r2k5 is offline
    True MemberRank
    Jun 2006 Join Date
    849Posts

    Re: Database Management 101

    Make a batch file, delete every hour the transaction log and create a backup of kal_db & kal_auth.
    We did this on Nwk for 3 months, every day, every hour and the folder is now just around ~175GB.
    Sounds much but it's just ~100-200 mb(on server start) and after 3 months ~700mb every day...
    This is far better as
    transaction logs..

  3. #3

    Re: Database Management 101

    Of course, making MySQL database from scratch is not easy and I certainly wouldn't recommend doing it, taking into consideration programming isn't your thing. Even professional use ready solutions nowadays.

  4. #4
    Hardcore Member RevEngKal is offline
    MemberRank
    Mar 2016 Join Date
    goo.gl/QepuzrLocation
    141Posts

    Re: Database Management 101

    Quote Originally Posted by bortonsbrain View Post
    Of course, making MySQL database from scratch is not easy and I certainly wouldn't recommend doing it, taking into consideration programming isn't your thing. Even professional use ready solutions nowadays.
    Did you just revive a 6 year old thread solely to advertise with your signature?



Advertisement