Microsoft have just announced that they will be releasing a version of SQL Server 2016 for the Linux operating system. This is a welcome move away from Microsoft’s historical stance of not releasing software for open source operating systems. They are starting to embrace the open source community more and more, giving end users what they want. They even support Linux operating systems on their Azure cloud platform.
Its only available in the form of an early private preview, with the final release planned for mid 2017. Hopefully they open up the preview to more people in the near future.
Microsoft’s announcement today comes just a few days before their planned SQL 2016 event in New York. (Planned for the 10th of March 2016. Click here for details).
We will need to wait to see what the SQL Server 2016 event in New York tells us, but hopefully there will be a wide range of SQL Server options available for Linux, including the free ‘Express’ versions, which might even put a dent in MySQL’s install base.
Will we see LAMP stacks consisting of Microsoft SQL Server 2016 in the near future? Its looking very possible.
For more information you can check out the Microsoft blog article at https://blogs.microsoft.com/blog/2016/03/07/announcing-sql-server-on-linux/
Microsoft SQL server provides three recovery models; full, simple, and bulk-logged. Each have their benefits. The details of the three recovery models is outlined below.
You will need to choose the best option for your scenario. For a production database that you need to be able to recover from, the best option will generally be the full recover model.
Full recovery model:
The full recovery model maintains the entire transaction log history up until the point where the database log file is backed up. The major benefit of the full recovery model, is that you have the ability to restore the database to a specific point in time. For example, you can restore back to 4:23pm, even if a full backup was not taken at that time.
If using the full recovery model, you will need to make sure the transaction logs are backed up reguarly as to prevent the log growing too big and filling up all free space on the server.
Simple recovery model:
The simple recovery model still has a transaction log file, however it will only store transactions for a short period of time, and cant be used to restore to a specific point in time. With the simple recovery model, you can only restore to a database backup file, at the time the backup was performed.
Bulk-Logged recovery model:
The bulk-logged recovery model is similar to the full recovery model, but it handles bulk data (for example, a data import) in a different way. When dealing with bulk data, instead of keeping track of every transaction in the log file, it will simply log the end result after the bulk transactions have occured. This is known as ‘minimal logging’. This helps reduce the impact that logging may have on the performance of the server.
With this model, you wont be able to restore to a specific point in time. You will only be able to restore back to the end of the transaction log backup.
Ideally, you would use this model on an as needed basis. It provides better protection in a disaster over the simple recovery model, however it lacks behind the full recovery model by not being able to recover to a specific point in time.
Sometimes you may encounter data integrity issues with one or more tables on a Microsoft SQL server. These errors may be found during a data integrity check using DBCC CHECKDB, or even via general use of the database. There are 3 options to correct the database integrity issues; safe repair, restore, and non safe repair (in that order).
Its always good practice to take an extra backup before any of these commands are run (specifically the non-safe repair option).
Safe repair using REPAIR_REBUILD:
Change the database to single user mode.
ALTER DATABASE <database_name> SET SINGLE_USER
Repair the database using a safe repair that will not cause data loss (if possible).
DBCC CHECKDB ('<database_name>', REPAIR_REBUILD)
Change the database to back to multi user mode.
ALTER DATABASE <database_name> SET MULTI_USER
Replace <database_name> with the name of your database.
Restore the database from a backup:
If the above safe repair does not work, the best option is to restore the database from a backup. Only if a database backup is not available, look at the next option, which is a non-safe repair.
Non-Safe repair using REPAIR_ALLOW_DATA_LOSS:
This option is non-safe as it will repair the database, by simply removing the data associated with the integrity errors. This option will fix the integrity errors, however it may remove needed data at the same time. Only perform this option if there the safe repair doesn’t work, and there is no good backup.
ALTER DATABASE <database_name> SET SINGLE_USER
Repair the database using a non-safe repair that will potentially cause data loss.
DBCC CHECKDB ('<database_name>', REPAIR_ALLOW_DATA_LOSS)
Change the database to back to multi user mode.
ALTER DATABASE <database_name> SET MULTI_USER
Replace <database_name> with the name of your database.
Microsoft SQL server 2008 has the ability to automatically shrink database files. By default this option is disabled. With automatic shrinking of database files turned on, the SQL server will periodically check the available free space within the database files, and will automatically shrink the files if needed.
If your database has heavy use, it might be best to keep this option disabled to prevent performace degration.
To check what the current auto shrink setting:
SELECT DATABASEPROPERTYEX('<database_name>', 'IsAutoShrink')
Replace <database_name> with the name of your database.
This will output a 1 if auto shrink is enabled, or a 0 if it is disabled.
To enable automatic file shrinking:
ALTER DATABASE <database_name> SET AUTO_SHRINK ON
Replace <database_name> with the name of your database.
To disable automatic file shrinking:
ALTER DATABASE <database_name> SET AUTO_SHRINK OFF
Replace <database_name> with the name of your database.
This howto outlines the steps to optimize a MySQL table, and shrink the physical file size of the file. This can be useful if you have removed a large number of records from a table, but the actual file size of that table on the disk hasn’t changed.
Continue Reading…
A default installation of MySQL server on a Linux machine will only listen for connections on the local interface (localhost or 127.0.0.1), so any remote connections can not be established.
This how to article outlines the steps to change the configuration to listen on a specific, or all interfaces on the machine.
Continue Reading…
This howto will outline the steps to create a new blank MySQL database. Continue Reading…
If you have a existing MySQL user account that you want to grant permissions to for a specific database, this howto will outline the steps required.
Continue Reading…
This howto outlines the steps to add a new user to MySQL, and grant the new user permissions on a specific database.
Continue Reading…
The Microsoft SQL database server supports the use of Triggers on its tables. Triggers are a special kind of stored procedure that can be executed when records in a table are either inserted, updated, or deleted.
Continue Reading…