Nov 062012
 

Microsoft’s SQL Server Express is a fantastic product for anyone needing a relational database on a limited budget. By limited budget I’m talking free. Yes SQL Server Express is free but it comes with a few limitations such as only utilizing 1 GB of RAM,  databases are limited to 10 GB, and it does not include SQL Profiler. For low volume sites that do not need enterprise level capabilities, this is a compelling solution. Here is a complete SQL Server feature comparison of all the SQL Server editions.

There is one other important limitation that needs to be mentioned which is that SQL Server Express does not include SQL Agent. This means that you can not schedule administrative tasks such as backing up your databases.  As everyone knows backing up data is critical and with SQL Server Express you can still backup your databases but you have to do it manually. So what does one do if you don’t have a budget to license SQL Server  but you need scheduled backups of your databases? Thanks to the folks at lazycoding.com there is a solution and it’s called SQL Scheduler.

SQL Scheduler is a simple lightweight application that installs on your server and runs as a service. Using the application’s GUI you can create multiple jobs to run your backups on a predefined schedule and you can even configure it to send you an email notification if it completes or fails to run properly.

After you download the program, unzip the archive and run InstallService.bat file from the command prompt. The installation will complete quickly.

image

Once you launch the program you just need to connect to your local SQL Server Express instance on your server. Here is how it will appear without any jobs configured.

image

From the File menu create a new job. In this example we’ll create 1 job to take a full backup of each database that is configured on the server.

image

Click on the Details tab and you’ll be able to enter your SQL statements for whatever task you’re trying to accomplish. Here is an example backup script you can use to run a full backup of each database on your server. Be sure to edit the script and change the path of where you want the database backups stored on your server.

image

On the schedule tab you can define the time and date of your job will run.

image

To have the job send you an email upon completion or in the event of a failure open the SQLScheduler.WindowsService.exe.config file and enter the mail server address along with the and username and password for authentication.

<system.net>
<mailSettings>
<smtp deliveryMethod="Network" from=”somone@somewhere.com”>
<network host="locahost" userName="x" password="x” />
</smtp>
</mailSettings>
</system.net>

 

Once the job runs a full backup will be created for each database and it will be stored in a subfolder of the path you specified in the script.
Now that your daily full backups are being created locally on the server the next step would be to either configure Windows Server Backup or configure 3rd party online backup solution.

As stated in the beginning this only makes sense if you have a limited budget otherwise you should license either SQL Server Standard Edition or SQL Server Web Edition. SQL Express is an ideal product for Cloud Server hosting. If you need Windows Cloud Server hosting take a look at Orcsweb.com. I know them well and I happen to work there.

Share and Enjoy

  • Facebook
  • Twitter
  • Delicious
  • LinkedIn
  • StumbleUpon
  • Add to favorites
  • Email
  • RSS
Sep 072012
 

Have you ever struggled to figure out why your SQL Server database is using so much space? If your database is hosted on a dedicated server then perhaps it’s not an issue for you. But what if your on a server with limited storage? For example if you have a web site that is on a shared hosting plan then chances are you have a limited amount of space for your SQL Server database. If your database grows over the plan allotment then you will probably be looking at an overage fee unless you can clean up some space within the database. Regardless of where your database is hosted it’s always a good idea to keep your database growth under control.

With a SQL Server database, the transaction log is usually the first place to look at when you need to free up space.  The transaction log is used for restoring the database in the event of a system crash to the very minute before the crash occurred. If your web site is not being used for ecommerce then you probably do not need “point in time” recovery.  By switching the SQL Server recovery model to “Simple” you’ll be ale to free up a lot of space that was being used by the transaction log.

So let’s assume you’ve already switched the SQL Server recovery model to minimize the size of your database however your hosting provider is still telling you your database is over the allotment and you need to clean up more space.  With a SQL Server database you could “shrink” it but that’s a topic for another day.  So we’ll focus on the tables in the database and see if you can clean out some legacy data.

The logical question is how does one determine which tables in a SQL Server database are using the most space? Well SQL Server has a stored procedure called sp_spaceused which can show you the total size of your database or alternatively how much space a table is using. For example you can check a table called products like this:

sp_spaceused products

This is very handy however not very practical if you have a lot of tables to test. Fortunately thanks to Bill Graziano over at SQLTeam.com this important but mundane task is a now breaze. They created a SQL script called bigtables.sql that you can run to list the 25 biggest tables in your database. It will quickly and clearly show you how much space each one of your tables is using.

Let’s take it a step further by creating a stored procedure to run the script. This way the functionality is always ready to run when we need it. Here is a script that creates a stored procedure called sp_bigtables. When we run the stored procedure we see these results which clearly show which tables are using the most space. The picture below only shows the first 30 tables however the script displays every table in the database.

Now that you’ve identified the biggest tables in SQL Server database you can focus on cleaning out some legacy data. Be sure to take a backup first before deleting any data from your database.

One final tip is that you should run DBCC CHECKUSAGE (‘databasename’) on your database first before running the script above so that you get the most accurate results.

dbcc updateusage

 

Share and Enjoy

  • Facebook
  • Twitter
  • Delicious
  • LinkedIn
  • StumbleUpon
  • Add to favorites
  • Email
  • RSS