Tag Archives: Database

Talkin’ smack on thwack

solarwinds-thwack-online-communityOK OK, I’m not really talking smack – this is simply my attempts at coming up with catchier blog titles and that rhymed so I thought it would be a good idea – nonetheless – I feel like it failed..

But down to business – Solarwinds and Stephen Foskett have granted myself with the honor of being a thwack ambassador for the month of September – and my topic – database analysis and performance.  I know what you might be thinking its odd for this virtualization geek to be talking about database performance, but the fact of the matter is I’m responsible for many databases within my day job so I couldn’t be more excited!  Excited to share what I know in an area that isn’t directly related to virtualization – but even more so, excited to learn more about an area that I feel I could improve in.

So, if you have some time head on over to the thwack community and check out my first two posts; “It’s always the databases fault” and “Making performance metrics make sense to your business“.  Leave a comment and you may just win yourself a jambox!

thwack isn’t just about giving away a prize to hammer you with info on Solarwinds products -Honestly I’m very impressed with the the content over on thwack!  There are some great conversations going on over there dealing with everything from virtualization monitoring to cloud to mobile to, well, database analysis.  There’s a lot to learn and thwack is definitely a great community to join in order to not just get answers to your problems, but to genuinely expand your knowledge – Be sure to check it out!

Friday Shorts – Visual Studio 2010 and BIDS, AJAX Toolkit Combo Boxes and the Great One

No support for BIDS development in Visual Studio 2010

Ahh Man!  I ended up rebuilding one of my work machines this week and during the process I thought I would be smart and maybe upgrade my Visual Studio from 2008 to 2010.  Now I do a lot of SQL report designing with SSRS and at times heavily access the BIDS functionality within VS2008.  Well guess what?  In VS2010 there is no support for SSRS projects!  WTF!  Every time I turn around Microsoft has a new and crazy problem they are throwing at me!  Official message from there ‘Introducing Business Intelligence Development Studio‘ page (pictured above)  basically says the solution is to run both of them along side each other!  Wow, great solution, you know, since the applications are soooo small <sarcasm>.  #FAIL

AJAX Toolkit Combo Box and Firefox

So another AJAX .net issue that I’ve ran into.  When utilizing the ajax toolkit with .net, more specifically the combo box control, and even more specifically the autosuggest / auto complete functionality  for some reason it will not accept any keyboard input from Firefox.  I.E. and Chrome seem to work fine.  The fix; Simply make sure that your maxlength attribute is set to a number greater than 0.  After doing that, Firefox should work fine.

Gretzky says they will get a deal done

If the great one says get a deal done you better get a deal done.  During an interview on Monday about nothing to with hockey the topic quickly changed to hockey and the NHL lockout.  The all time leading point getter said he was very optimistic that the NHL and the NHLPA will have a deal done before the Winter Classic this year, meaning we should all be watching hockey before Jan 1.  Wayne, you’ve been right a lot of times and I sure hope you are right about this one too!  #GOHABSGO

 

DR for your DR Solution – Backing up your Veeam Backup & Replication Database

Last week I published an article based around moving your Veeam Backup & Replication Database to a new server.  In that article I used a backup and restore method to move the Veeam DB somewhere else.  Well, the feedback I received from that article was mostly based around how to setup a scheduled job to backup the Veeam database, so, without further ado, here's how I would recommend doing so.

Connect to your Veeam DB server

First off, we need to initiate a connection to the SQL Server hosting your Veeam DB.  Honestly, it's probably easier just to RDP or console in to the  SQL Server hosting the database and then connect to it.  In my examples below I will be using SQL Server Management Studio to do so.  Just a note, if you are running the default installation of Veeam with SQL Server Express, you can connect to the server using 'SERVERNAME\VEEAM' as your server name and use Windows Authentication.  Otherwise, just connect to your SQL instance as you normally would.

Create the Maintenance Plan

This is a pretty easy step.  Once you are connected just create a new maintenance plan by expanding Management -> Maintenance Plans.  Right click on Maintenance Plans and select New Maintenance Plan.  You could also select to go through the wizard, but again, for this example I chose to create it from scratch.

Create the Backup Database and Maintenance Cleanup tasks

You should now be in what is called Design Mode of the maintenance plan.  In the bottom left hand corner of your screen you should see all of the Design Tasks that we are able to just click and drag over to our Design Surface in the centre.  Here is where we need to setup our first task which is to do the actual backups of our databases.  Simply select the Backup Database task and drag it over into our Design Surface.  Do the same with the Maintenance Cleanup task as well.

Configure the Backup Database Task

Ok, we are now getting into the meat of this job.  Double click the Backup Database task that we just moved over.  Here is where we will setup all of our parameters evolving around our backups.  First off we need to select our connection.  In most cases this can just be left at 'Local Server Connection', however depending on how you are setup you may need to enter in the actual hostname or IP of the server.  Leave the backup type set to Full.  Under the database(s) section is where you can select which databases you would like to backup with this job.  You can just select the Veeam DB if you want, but I chose to select 'All Databases'.  Back up to – Select your target, usually disk nowadays.  I left the expiration of the backup sets at the default, which is to not expire.  Check the create a backup for every database, and in this example I've also checked the subdirectory for each database as well.  I just find this to be a bit more polished when browsing through the backups 🙂  You can use whatever you want for the backup extension, but the standard for SQL is 'bak'.  Select a location to store the backups, click 'OK' and your done.

Configure the Maintenance Cleanup Task

This step can be skipped if you want but you will have to watch your free space.  In this step we will set the job to 'clean up after itself' by deleting backups that are a certain number of days or weeks old.  Again setup your connection the same as you did in the previous step.  You will also want to select to 'Delete Backup Files'  Select 'Search folder and delete files based on an extension' and setup the folder the same as above. Select to include first-level subfolders.  Enter in our file extension the same as above (bak).   Select your desired retention policy under the 'File Age' section.  I usually chose somewhere along the lines of a couple of weeks depending on the importance as well as the free space on the backup targets.  After all this is setup click 'OK' to save your cleanup task.

Connect the two tasks together

Here's an easy one for you.  If you single click on the Backup Database Task you will see a green arrow appear underneath it.  Simply drag that arrow over to the Maintenance Cleanup Task. You could also take the vice versa action on this one.  You could have the single click the Cleanup Task and drag arrow to the Backup Task.  Either way, whichever one task runs first you should end up with the same results.

Schedule the plan

Click the schedule button in the Maintenance Plan toolbar.  In the job schedule dialog that opens select the required times and frequency that you wish to run this job and click 'OK' to save the schedule.  The schedules are very flexible and allow for virtually every scheduling combination that you can think of.

This is all the modification that we need to do so just save your maintenance plan by going to File -> Save Selected Item or by right-clicking the tab at the top and selecting 'Save'.

Check the Agent and Test.

If you now have a look under the your SQL Agent by going to SQL Server Agent -> Jobs you should see that a new agent job has been created with a name similar to that of your maintenance plan.  You can right click on this job and select properties.  You should see your schedule as well as the SSIS call to run your maintenance plan.  If you would like to test your backup job now, simply right click your job and select 'Start Job At Step'.  This should popup a dialog showing you the status of the job.  Once completed you should see some backups in the target backup locations you inputted earlier.

So there you have it!  DR for your DR Solution.

Moving your Veeam Backup & Replication Database

As you are probably are aware, in the beginning there are few VMs.  People start out by virtualizing their low flyers, web servers, etc..  As time goes on there is something called VM sprawl which occurs.  Suddenly there are many many VMs being spun up for no real reason at all.  Tier 1 apps make there way into your vCenter, and things begin to grow exponentially.  The problem with this, is that sometimes you have solutions installed from the beginning which were initially setup in the 'small' environment you had in the beginning.  As is the case with my Veeam installation.  When I first installed it, I just used the SQL express option that came with it.  Now, since the environment has grown, and the stakes are higher when failures occur, I have the need to move this database from its' default SQL express to a full blown version of SQL that is managed by all of our monitoring and backup tools.  Below is just how I did that…

First off, you will need to get your hands on a copy of SQL Server Management Studio.  We will need this in order to perform the backups of the SQL express databases that Veeam has created.  You can find 2005 here and 2008 here.

After you have installed this on your Veeam server just follow the steps below to move your database.  Oh, and be sure to stop and / or disable any running jobs or jobs that may run while you are performing these actions.  It's probably best to just disable all of them until you are done.

Stop Veeam Services

We will need to stop (and I disabled) all of the Veeam Services.  Don't worry, when we get to re-installing the Veeam applications later the services will be re-enabled again.  The reason for this is that we do not want any data flowing into our database as we are backing it up and moving it.  You will need to stop the following services; Veeam Backup Enterprise Manager, Veeam Backup Service, Veeam Indexing Service, and the Veeam vPower NFS Service as shown below

 

Backup your VeeamBackup database

​Once the services are stopped it's time to get going on backing up our Veeam databases.  Veeam has 2 databases that it uses; VeeamBackup and VeeamBackupReporting.  We will need to back both of these up in order to restore on our new SQL server.  So, fire up SQL Server Management Studio and find the VeeamBackup database and follow the following

steps.

  • Right-Click
  • the VeeamBackup database and select Tasks->Backup
  • Make note of the location near the bottom where its' going to save the backup
  • file.
  • Click
  • 'OK' and browse to the targeted location and copy the backup to your new SQL server.

You will need to repeat the bulleted points above for the VeeamBackupReporting database as well.

Restore on your new SQL server as the same name

Now that we have the backups on the new SQL Server we will need to restore them.  The following is how I did

it.

  • Right-Click
  • Databases and Select Restore
  • in the 'To Database' field we will want to type the name of our target database.  I would just use the same name as it was before – 'VeeamBackup'
  • Here we need to select 'From Device' and then 'Add' our backup file that we have just copied
  • over.
  • After
  • we have added our file, be sure to check the checkbox under the Restore heading.
  • Check out the settings on the Options tab.  Here is where you will want to check to be sure that the mdf and ldf files will restore to the locations that you want them to.
  • When your happy, click 'OK'

Again, we need to repeat this for the VeeamBackupReporting database as well.

Security Setup

Depending on how had Veeam setup before, this may or may not need to be done, or it may need to be done in a different way.  Essentially the user that you are going to run the Veeam installation as will need to have db_owner rights to both of these databases.  So if you installed Veeam as a local user account previously, you might want to think about creating and using a domain account this time around, as now we have two servers that we need to authenticate on.  However you decide, you will need to grant that user dbo rights as follows…

  • Under Security->Logins, right click and select New Login.  Browse to the windows user account that you want to use.
  • In the 'User Mapping' section, select both VeeamBackup and VeeamBackupReporting and ensure that the db_owner role membership is checked off.
  • Also in that section, assign the Default Schema for both of those databases to dbo.

Remove and Re-Install Veeam

There may be other ways to 're-point' Veeam to the new database server (registry hacks), but for all intensive purposes it takes literally less than 5 minutes to install Veeam so i found this the easiest route.  Go into control panel and remove both Veeam Backup and Replication and the Veeam Enterprise Manager.  Once done, re-install both of these applications.  When prompted for your database location enter your desired information to point to the new SQL Server.  You should be prompted that a Veeam server is already connected to the database, when asked this just select 'Yes' to connect this (the new installation) server to the database instead.

And it's that easy!  You know have a more expandable database and the ability to utilize more RAM and CPU.

Just a note, I cannot guarantee that this will work for you…it worked great for my environment!  If in doubt, open up a support call with Veeam and have them give you a hand or directions to do so…  Also, if you are looking for a way to automate a backup of your Veeam Database, check out this post.

Giving vCenter a kick in the rearend!! – Defragmenting your vCenter Database

I've seen this happen numerous times on a number of our VMware vCenter installations.  For the first few months(sometimes days)  vCenter Server is very snappy and responsive, when moving from page to page things come up instantly and moving between different statistics and metrics on performance graphs is veryquick.  Then, as time goes by, things begin to drag down a bit, tabs start to take a little bit longer to load when moving between them, performance graphs can throw exceptions and things just generally slow down.

Of course there are many issues that could be causing this to happen, but the most common that I have found is a fairly simple one, a neglected VMware vCenter DB that is full of fragmentation and in need of a little TLC.  I don't want to go into what exactly database/index fragmentation is, but a good read if you have the time (and interest) is here.  Also, VMware has released KB Article 1003990 as well, which covers off fragmentation within the vCenter DB.

And then there is also my explanation….

When I'm looking at tuning my vCenter DB, the tables that I find myself always defragmenting are as follows..

VPX_HIST_STAT1
VPX_HIST_STAT2
VPX_HIST_STAT3
VPX_HIST_STAT4
VPX_TOPN_PAST_DAY
VPX_TOPN_PAST_WEEK
VPX_TOPN_PAST_MONTH
VPX_TOPN_PAST_YEAR

As you may guess, these tables hold historical and rolled up performance statistics and data.  Since vCenter is always  collecting data (depending of course on your Intervals and durations) these tables are constantly being updated (New stats coming in, old stats going out).  Just as in file level defragmentation, the large number of writes, updates, and deletes causes some tables to become heavily fragmented.

I'm not going to go through defragmenting all of these as it is the same steps for each table/index.  For this purpose I'll just go through VPX_HIST_STAT3.  First off, to see the fragmentation inside a table just run the following command in SQL Server Management Studio

USE [vcenter database name] GO
dbcc showcontig([tablename],[indexname])
GO

You can retrieve the names of the indexes either from  KB Article 1003990 or by expanding the Indexes folder in SSMS.  Essentially this command translates to..

Use VIM_VCDB
GO
dbcc showcontig ('VPX_HIST_STAT3','PK_VPX_HIST_STAT3')
GO

In my example this returns the following stats.

To summarize these results for a VI Admin, the lines that you should really be looking at is 'Scan Density' and 'Logical Scan Fragmentation'.  In short, you want Scan Density to be as close to 100% as possible, and Logical Scan Fragmentation to be as close to 0% as possible.  To defrag the indexes in this table we use the following command….

dbcc indexdefrag('[databasename]’,'[tablename]’,'[indexname]')

so, after filling in the values we get…

dbcc indexdefrag ('VIM_VCDB','VPX_HIST_STAT3','PK_VPX_HIST_STAT3')
 
which then returns….
 

As you can see we now have a lower Logical Scan Fragmentation and a higher Scan Density.  This is the expected result we want from the defragmentation.  Just repeat this step on all of the indexes and tables you want to defragment and you should be enjoying a much snappier, more responsive vCenter Server in no time.  Keep in mind, some smaller indexes and tables will always be fragmented and not much can be done to correct that issue.  Personally, I concentrate on the 8 tables listed above.  Keep these tables/indexes happy and you are well on your way….