top of page

Search Results

Search this site

45 results found with an empty search

  • 5 Ways Veeam Provides Ransomware Protection

    A simple google search will yield tons of statistics on the cost of ransomware, and how many companies have fallen victim to its attacks. The numbers vary so greatly that I'm not going to cite any particular source, but it is clear that ransomware is real, not going anywhere and can cost your business a significant amount of time and money. What is Ransomware? First, let's start with a high-level overview on what ransomware is and how it works. Ransomware most commonly sneaks in via email attachments or links, but it can also come from clicking or downloading a link from a malicious website. Once the infection has been downloaded to its new home it begins to spread. It starts by installing a program on the local machine it gained access to and ensures the program starts on reboot. From there ransomware will look to spread across the network to gain access to as many endpoints as possible. Once it has achieved that it will begin to search for files with important extensions such as .doc, .xlsx, ppt, etc and encrypt them, so they are unusable. More sophisticated attackers will wait until you're infected for 30 days or more before demanding payment because at that point you may have fallen out of your retention policy. Your backups would now be useless, since you've been protecting encrypted files. Spoiler alert: later we will cover how Veeam can detect this behavior! How to defend against Ransomware? As part of your ransomware strategy your first line of defense should be yourself! Similar to a cold or flu, ransomware is an infection. If you never wash your hands, constantly eat finger-foods, and always touch your face, then your chances of infection are significantly greater! The same is true for ransomware. If you don't take proper precautions by using strong passwords, applying software patches, and limiting/restricting access to endpoints on the network, then you're putting yourself at greater risk. Your second line of defense are tools and vendors that offer solutions specifically to fight ransomware! Think of these as your yearly flu shots. They aren't guaranteed to keep illness away, but they are usually effective. They will scan the network for suspicious activity, analyze emails for phishing content, prevent suspicious downloads and much more. Your final line of defense should be a backup solution that can save the day if needed and has ransomware built-in to the heart of the product. Just like the flu there is no cure you can take, but with proper fluids and medicine you'll be back to normal in no time! With that, let's cover the 5 ways Veeam can help. 5 ways Veeam can help: 1. Alarm notification if backup file size growth is abnormal! Despite the spoiler above this is a great defensive strategy to have in a backup solution. The best way not to pay attackers their ransom is to simply restore from a backup, but if it's a smart attacker who waited days or even weeks to demand payment then you might not have a valid backup. Veeam provides an alarm that can notify you if backup file size growth is abnormal, moreover, Veeam can take automatic remediation via a provided script to turn off the VM or remove it from the network. The reason this alarm can be helpful is because if an attacker is encrypting all your files than the backup sizes should be significantly larger since there are more changed files and less files that can be compressed and deduplicated. This is a great indicator to check if these VMs have encrypted files on them. In the alarm, you can define rules for what abnormal file size growth looks like. For example, below we've specified Veeam to look for any backup files that exceed 150% growth, and we've instructed Veeam to compare the file to the previous 3 backup files in the "analysis depth" field. You can setup Veeam to simply send you an alarm notification when it gets to the warning (150% growth) or error (200% growth) stage, or you can have Veeam take automatic remediation actions. In the actions tab, you can insert a script that will run if the above rule is meant. A common script might be to shut off the VM or remove it from the network. As you can see in the "execution type" field you can choose to receive an email first to approve the execution of the script. 2. Scan for possible malware activity. A second capability Veeam provides as a layer of protection against ransomware is an alarm that looks for high cpu usage or a high write rate on the underlying datastore. Similar to the previous alarm you can set rules to look for high cpu usage, high write rates, latency on the datastore, network transmission rate, and many other variables. High CPU usage is a great indicator that files on the VM are being encrypted as that is a CPU intensive task. Now, it might be common in your environment to have VMs with high CPU usage. In that case, leaving it as an alarm notification with no automatic remediation might be best. In addition, you can suppress the alarm during snapshot deletion or creation activities. 3. Immutable backups offsite. Offsite backups in and of itself is a great strategy against ransomware. This gets a copy offsite and to a separate network. In some situations this isn't enough though. Veeam offers a capability with AWS to make secondary copies of data immutable. This functionality will extend to other public cloud and on-premises vendors in the future, but there was no reason to let this capability with AWS sit idle. When adding an AWS S3 bucket to the Veeam console, you can choose to select the number of days you want backups to be immutable. For this period of time nobody can modify or delete the backup. Not a Veeam admin user. Not a root AWS user. It locks the objects in what AWS calls compliance mode. Not even a support call can lead to deletion of this data. As a side note, I really like that there is an option to limit storage consumption if you know the business shouldn't exceed a certain amount of TBs or PBs. As expected, if we try and delete objects in the AWS bucket as a root user it gives us an access denied because the object is locked for 7 days. And if we try to delete the backup files in Veeam as an admin account it also fails because the objects are locked. 4. Automatically test backups in an isolated environment for ransomware or malware. Veeam SureBackup and SureReplica have been in the product for years. This is a powerful capability that enables users to put their backups to work. Whether it is to automate DR tests, spin up RA environments for test/dev, security, DBAs, analytics, etc. The use-cases are countless. One of those use-cases though is to test your backups for malware or ransomware. First, you create what I refer to as the plumbing. Veeam calls this a Virtual Lab though to be more official. This is where you define the host, datastore, isolated network, and masquerade IP you want these VMs to spin-up in. Once the plumbing is setup you can point VMs to the virtual lab and have automatic tests run once they are turned on. For example, you can check if a DNS port is open, a SQL database is online, guest tools is installed, or you can insert your own scripts to execute. In addition, you can have Veeam scan the files for any suspicious malware or ransomware. Not only can you do this in an isolated lab, but also when you do any type of restore back to production. Before restoring the data Veeam will mount the backup and scan it for viruses. If anything is found it will abort the recovery. As you can see, this quickly becomes an option to take annual DR testing to the next level. I've seen customers who run this on a daily basis after their backups. I understand that might not work for customers with thousands of VMs, but why not make what used to be a yearly DR operational restore or backup validity test and turn it into a quarterly test? 5. API to mount any virtual disk to any machine for ransomware or malware testing. Veeam offers a Data Integration API that enables users to easily re-use and access their backup data for a number of use-cases. Scanning for viruses being one of them. Here is how it works! $backup = Get-VBRBackup -Name "Name_of_Backup_Job" $targetServerName = “192.18.138.125” $targetAdminCredentials = Get-VBRCredentials -name "DEMO\Administrator" $restorepoint = Get-VBRRestorePoint -Backup $backup | Sort-Object –Property CreationTime | Select -Last 1 $session = Publish-VBRBackupContent -RestorePoint $restorepoint -TargetServerName $targetServerName -TargetServerCredentials $targetAdminCredentials Ransomware is prevalent and any company that doesn't have or is not forming a ransomware strategy to prevent, detect, and recover from a ransomware attack is at a significantly higher risk of infection. As a first line of defense, do what you can to harden your infrastructure. That includes Veeam as well. If I can only recommend one thing it is to enable 2FA on all Veeam login server sessions. See this post from Rhys Hammond for a how-to. Second, find tools or vendors that can help with prevention and detection. Lastly, choose a backup software that has ransomware protection built-in to the product. God Speed! #dataprotection #Veeam #ransomware #backup #LinchTips

  • Oracle RMAN Protection with Veeam

    Much like protection for SQL Servers, that we covered in the last post, there is typically a line in the sand on who is responsible for protecting Oracle databases within larger organizations. It is either the Oracle database administrators (DBAs) or the Data Protection team. Unlike SQL though, it is much less of a power struggle to determine who is responsible. This is because Oracle makes it simple for DBAs to take ownership of database protection through the RMAN (Oracle Recovery Manager) utility. What is Oracle RMAN? As the name implies RMAN is a tool built into Oracle that enable DBAs to easily manage database backups and recoveries. It has been part of Oracle for over 20 years, and it comes at no additional cost. Through the command-line of RMAN DBAs can easily trigger full database and archivelog backups. Protecting archivelogs allows databases to be recovered from any particular point in time, and it allows the databases to be protected in hot backup mode, meaning the database does not need to be shutoff during backup. If you do not protect the archivelogs (noarchivelogs) then the database has to be shutoff during backup. In addition, from the RMAN command-line it is simple to recover whole databases or just a tablespace partition. RMAN is the recommended backup method for Oracle databases, so understandably Oracle DBAs would be a little confused if you came knocking on their door asking to start protecting their databases. Why am I reading a blog from a backup vendor then? Despite Oracle offering their own backup and recovery tool for DBAs, it is still crucial that any enterprise level backup solution plug-in to RMAN. It is the data protection team's responsibility to ensure all workloads are meeting required RPOs, and that data is recoverable in an outage, disaster, ransomware attack, etc. Veeam offers an Oracle RMAN plug-in that provides the best of both worlds. It enables DBAs to create backups and restores as they've always done through RMAN, while giving the data protection team insight into those backups. All while never speaking a word to each other! How great is that? Veeam Plug-in for Oracle RMAN in Depth Oracle DBAs simply need to install the Veeam RMAN plug-in on their servers and go through a basic setup wizard, where they'll enter the Veeam server, credentials and select Veeam repositories to be the destination of backups. As you can see below, the Veeam plug-in will then update the backend RMAN configuration settings, enabling DBAs to use the same RMAN backup and restore commands they're accustomed to. OracleRMANConfigTool --wizard Once the Veeam Plug-in is installed, the data protection team will see all RMAN backups in the Veeam console. As you can see below, when an Oracle DBA triggers a RMAN backup command the Veeam plug-in is utilized, and the Veeam admin will see the backups taking place on the backup server. RMAN> backup database plus archivelog; RMAN backup statistics seen in the Veeam Console. Oracle DBAs have many tools available to them thanks to RMAN. For example, prior to restoring a database, they can check to see if the current database is valid by checking for any missing files. In addition, before performing a restore DBAs can validate the backup itself is not missing any database files. This will look at the backup file on the Veeam repository and verify it is recoverable and has the proper files in place. RMAN> restore database validate; Now, you could run a restore with peace of mind, knowing that the point-in-time isn't corrupted. As you can see below, when an Oracle DBA triggers a restore from the RMAN command-line, RMAN knows to pull data files from the backup on the Veeam repository. RMAN> restore database; RMAN> recover database; The Veeam plug-in for RMAN offers more than just visibility into backups the Oracle database team is taking though. Veeam admins also have the capability to easily restore databases from the Veeam console. In a situation where the DBA doesn't show up for work or is locked out of their machines in a ransomware attack, the Veeam admin can trigger a restore from the backup server. They simply select the point-in-time and choose to restore the database to the original or alternate location. It is important to note, that Oracle DBAs have the ability to restore more granularly than the entire database with RMAN. They can restore a specific tablespace or tablespace partition. The same way you can validate a database before restoring it, you can do the same for a tablespace. As you can see below, restoring the tablespace from RMAN pulled the data from a Veeam repository. RMAN> validate tablespace users; RMAN> restore tablespace users; It is much more common for Oracle DBAs to own backup and recovery of their databases because RMAN is such a proven and effective utility. That does not mean the data protection team shouldn't be part of ensuring these workloads are protected though. It's important an enterprise backup solution provides levels of protection across all the critical workloads in an organization. An ideal data protection solution should allow Oracle DBAs to backup and recover their databases as if a third party tool isn't involved, while also providing visibility into the protection of Oracle databases to the backup team. #RMAN #Oracle #Veeam #linchtips #dataprotection #backups

  • The 6 Most Helpful Backup Alarms

    In a perfect world backups would have a 100% success rate and backup speeds would always be over 1 GB/s. Unfortunately, IT infrastructure is ever-changing and there are a number of variables that can cause a backup to either fail or not achieve expected performance. That is why it is important to have alarms that notify you or even take automatic action as changes in the environment inevitably effect backups. There are over 200 alarms in Veeam ONE but below are my six favorites that every customer should use. 1. Automatically Remove VM Snapshots Based on Age Veeam monitoring goes beyond typical capacity planning and protected machine alarms. In order to successfully troubleshoot an issue, it is crucial to understand the network, storage and infrastructure components. For example, a common reason for slower performance on backups is because the source storage on VMware datastore is busy or is running out of space. Veeam understands this and digs into the virtual infrastructure itself. You certainly can set an alarm about datastore utilization, but you also can set an alarm that automatically deletes VM snapshots that are older than "x" days. Removing snapshots that are old is a great way to save space on the underlying storage for the VMs. While creating this alarm you can define if you would simply like to be notified of snapshots older than "x" days, or you can instruct Veeam to actually delete them. In addition, any image based backup software occassionally might forget to delete the snapshot used for backup. Not everyone or everything is perfect. You can set a simliar alarm that deletes orphaned snapshots automatically. 2. Power on VMs Automatically At first glance, you might think, "Brad, I don't want to power on any VM that's powered off." And I would completely agree with you. What is great about this alarm is you can assign it to a specific part of your virtual infrastructure rather than the entire vCenter or even cluster. You can define a specific subset by tag to identify mission critical VMs that should never be turned off, and if they are this rule will automatically turn them back on. 3. Backup VMs that Missed RPO Window An alarm that checks that all VMs have been backed up within the defined RPO is great, but an alarm that will automatically backup any VM that's fallen out of its RPO is even better! Veeam ONE offers the capability to define your RPO for either your whole virtual infrastrucutre, or you can assign this alarm to a subset of VMs like we did in the previous alarm. Veeam ONE will go out and look for any VMs defined that have not been backed up in the last 24 hours and trigger a backup. You can assign this alarm to a whole vCenter, cluster or to a tag that is for mission critical workloads. 4. Compare Backup File Size Growth for Possible Ransomware Activity It wouldn't be a backup monitoring blog without mentioning ransomware. It is important to keep your infrastructure hardened as attackers know to target backup servers in an enterprise environment. At the bare minimum, I suggest enabling two-factor authentication on the Veeam server and Veeam repositories. In a scenario where the attacker has already penetrated the network and started encrypting files, monitoring backup file size growth can be a great way to look for ransomware. Encryption will cause significantly larger backup files as there are more block changes and less dedupe and compression. The above example looks for backup files that have grown by 150% or more. It is analyzing the last three backup file sizes for comparison, and it is doing this for all the jobs. If extreme backup file size growth is discovered an email will be sent out. You could also enable a script to run that would spin up the backup in an isolated environment to search for encrypted files. 5. Scan for Possible Ransomware Activity. Another helpful capability to detect ransomware, is an alarm that looks for high CPU usage and high write rates on the underlying datastores. High CPU usage is a great indicator that files on the VM are being encrypted since encryption is a CPU intensive task. It might be common in your environment to have VMs with high CPU usage though, so you can suppress the alarm during snapshot deletion or creation activities. 6. SQL Permissions or Volume Shadow Copy Issues One of the most common reasons for SQL backup failures are because the permissions have changed. With Veeam ONE you and the SQL DBAs can be notified if SQL backups are failing due to a permissions issue. Another common reason for SQL backups failing is because VSS (Volume Shadow Copy) which is used to quiesce the database during backup operations is not running. You can create an alarm that notifies you and the SQL DBAs if the SQL Writer service for VSS has stopped. It is crucial to have a backup monitoring tool that goes beyond just the backup components. Often times slow performance or failed backup jobs can be a sign of a network, application, storage or permissions issue just to name a few. Without a tool that can dig deeper into the root of the issue, the problem is likely to reoccur. I see many customers choose to purchase Veeam without Veeam ONE just to change their mind within a few months. #dataprotection #monitoring #ransomware #linchtips #Veeam #VeeamONE

  • Apply and Organize vSphere Tags

    It feels like everywhere you look vSphere tags are there. VMware talks about how great tags are. Third party vendors talk about how great it is that they integrate with tags. And then you start talking public cloud and tagging now seems mandatory. Despite all the buzz about tags, I personally don't see them applied much in enterprise sized organizations though. Why is this? Typically the company isn't against using tags, but applying and organizing tags at a large scale can be intimidating. Individually applying tags to thousands of VMs is a task not even an intern should ever have to do. Leveraging PowerCLI can be a great way but not everyone knows PowerCLI well enough to whip up tags that make sense for their organization. And that's just the execution piece which is the easy part. Getting different teams to agree on how to organize tags is the real uphill battle. Before jumping into that though let's first make sure we all know what tags are. What are Tags? Tags were introduced in vSphere v5.1 because VMware observed customers attaching custom attributes to VMs to make them more findable. Tags are a label you assign to a VMware object (host, VM, cluster, datastore, etc) to help organize your environment more effectively. For example, you could have a category for applications, and within that category could be tags assigned to VMs for Oracle, SQL, Exchange, etc. Another example is a category for operating systems, and within that category could be tags assigned to VMs for Window 2012, Windows 2016, Ubuntu v18, RHEL v 7, etc. Essentially, tagging assigns metadata to vSphere objects to make them more searchable and discoverable. How do we organize Tags though? Objects can have multiple tags associated with them. This way multiple different teams can create categories and assign tags to their resources. A great example of this is for the Data Protection team. They can have their own category and then create and apply tags to VMs based on their SLAs for business continuity and disaster recovery. This is a classic case of something that is easier said then done though. In an enterprise sized environment with thousands of VMs how is the data protection team supposed to assign tags to every VM? How do they determine what tags to assign? These are great questions that I am asked and witness businesses go through frequently. So...how can Veeam help? Veeam's monitoring and reporting tool, VeeamONE, offers a capability to organize your virtual infrastructure by pushing out tags to vSphere based on custom parameters you define. In the below example, I have created a category called, "Data Protection," and then created tags within that category for windows, Linux, SQL, Oracle and large VMs. VeeamONE goes out and looks for VMs with the parameters defined and assigns a tag based on the rule. There are over 30 properties you can choose from when creating rules for your tags. Once the rules have been set, we can see in vSphere that VeeamONE effectively created a category with the underlying tags. Furthermore, we can confirm VeeamONE assigned those tags to VMs. Let's drill down into the "Gold - SQL" to verify that all my SQL VMs have been tagged. Based on the conditions set in VeeamONE, any VM containing the name SQL AND is not a replica should be tagged, "Gold - SQL." LinchTip: run the object properties collection task in VeeamONE Reporter, so you don't have to wait for the tags to be exported to vSphere. Why do Tags matter though for the Data Protection team? For larger organizations with thousands of VMs, backup job creation and organization requires a little planning. With a soft limit of 300 VMs per job for Veeam, organizing jobs by cluster or datastore might not be ideal. There is no technical reason you can't have a thousand VMs in a job, but we see that maintenance and management can become difficult if you decide to reorganize your infrastructure at that size. This is why tags can be so powerful, and using VeeamONE to organize and push those tags to vSphere makes it much easier to actually leverage tags. LinchTip: I never recommend organizing jobs by datastore if you're leveraging vSphere DRS (distributed resource scheduler) which will vMotion VMs to another host based on resource consumption. Whereas if your jobs are organized by tags or clusters, we will still protect them with no manual intervention. Tags in principle sound great! Actually putting it into practice and implementing them at a large scale is a challenge though. The majority of the larger customers I work with want to use tags, but figuring out how to organize their environment and push those tags out is where things get stalled. VeeamONE is a great tool for the data protection team to leverage to push out tags under their own category without relying on any other teams. #Veeam #VeeamONE #LinchTips #vsphere #vmware #tags

  • Two-Factor Authentication on Veeam Components

    There are a number of features Veeam offers to both detect and protect against ransomware, but hardening your Veeam infrastructure is the first and most important step to a proper ransomware strategy. Sophisticated attackers know to target the backup software before requesting a ransom and encrypting files on the network. There are a number of recommendations that you can read through in Veeam's Best Practice Guide to harden your infrastructure, but if there is one key takeaway it is to setup two-factor authentication (2FA) on your Veeam backup server and other Veeam components. Setting up 2FA on your Veeam servers (backup server, proxy and repository) should help you sleep at night, assuming you have your phone nearby. Even if attackers access the proper credentials to your Veeam server, they will be prompted to approve or enter in a code that was sent to the Veeam admin's phone. Unless the hackers also social engineered their way into your life to the point where they know the passcode on your phone (which at that point kudos to them considering my fiance doesn't even know mine), they'll be stopped dead in their tracks. Some of you might be rolling your eyes at me right about now because 2FA on your Veeam servers will be very annoying if your security team has a policy to lockout RDP sessions every 10-15 minutes. But you know what is more annoying? A ransomware attack that deletes all your backups and costs your company thousands if not millions of dollars. Plus, your job to boot most likely. We at Veeam talk about 2FA all the time, but in all my posts I try to walk the talk. Let's get into the how-to. How-to Setup 2FA on Veeam Servers In this example, we are going to use Duo as our MFA software, but there are many other vendors that offer the same service to enable your RDP sessions with 2FA. Why I chose Duo? The same reason people at home or small companies use Veeam. It's free for up to 10 users. First, I create a Duo account by just going to Duo.com. Then you select an application to configure 2FA on. They have just about every application you can think of. AWS, Oracle, Palo Alto and of course RDP as well as hundreds of others. In the example below, I am setting up 2FA for my Veeam server logins. When you create 2FA for RDP sessions an integration key and secret key is provided which will be used during the installation of Duo. During the application setup you can attach a policy to define the requirements for 2FA. There are a number options from blocking a specific OS, browser or network to defining the location logins are permitted. Most importantly though, you can enforce 2FA in all scenarios which I recommend. You then download Duo software to your Veeam server and enter in the keys created in your Duo account for Veeam RDP sessions. I recommend doing this for your backup servers and repositories at minimum. When you first attempt to login to your Veeam server the below message will appear. The next step is to create a user in the Duo admin console that is permitted to access this machine. Enter in the username that will access the Veeam servers and their email (don't use the email in the image). The user(s) will receive a registration email from Duo to create an account. They can choose to download the app for push notifications or enter their phone number to be texted a code or called. Now, when the Veeam admin RDP's to their servers the below image will popup. And if they downloaded the Duo app and selected push notifications for 2FA, they will get the below notification that someone is attempting to login. They can approve or deny the request. Lastly, as a security admin or diligent backup admin, you can monitor the login activity for this user and RDP sessions. There are a number of Veeam hardening recommendations such as taking the backup server off domain, having an immutable backup copy, limiting who has access to the backup server and many others. In addition, Veeam offers a number of ransomware detection and protection options from immutability to scanning backup files for malware. None of this matters though if an attacker gains access to your backup server. 2FA is the single best way to stop an attack and make him/her realize they picked the wrong person and company to mess with. #MFA #2FA #Veeam #ransomware #dataprotection #Linchtips

  • Monitoring SQL and Oracle Backups

    Database Administrators (DBAs) care about protecting their databases arguably more than backup admins. DBAs are the backbone and lifeblood to a company's most important data. In my time spent with DBAs, I've learned the three most important factors they care about from a database protection standpoint are: Peace of mind - verifying backups are usable for restores Restore performance - ability to control the restores and ensure recovery time objectives (RTOs) are satisfactory to spin up environments for test/dev Visibility - DBAs arguably care about backup just as much if not more than the backup admin. It is crucial they can easily monitor backups Peace of Mind In a previous post, we covered how DBAs can still verify their backups even if Veeam is in charge of database protection. Not only is this a common task in the DBA world to ensure backups are usable, but also Veeam's publish feature allows DBAs to run their CHECKDB commands on storage separate from the storage their production database is running on. This is a great way to provide peace of mind to the DBA that backups are usable, while at the same time not having to contend for resources. Restore Performance Which leads to how Veeam achieves fast RTOs for DBAs. Veeam can backup to any repository to achieve fast recoveries. The Veeam repository can be a NAS share, attached disk to the database server itself or leverage Veeam to recover from primary storage snapshots. The flexibility Veeam offers from a repository perspective allow DBAs to still leverage the same infrastructure they did prior to using Veeam, giving them similar if not better restore performance as using native database protection tools. Visibility The last factor is visibility, which is the focus of this blog post. It is key DBAs can easily monitor backups of their database. This is a topic that is covered rarely and there is little to no documentation on it, so I thought it might be helpful to share a few things on how SQL and Oracle DBAs can monitor backups even if Veeam is taking them. Monitoring for SQL Let's start with SQL first. There are many SQL queries and PowerShell scripts DBAs can run to monitor database backups even if they aren't in control of the backups anymore. Here are a few that I like. Databases with backups older than 24 hours is one my favorite SQL queries to monitor backups. You can put this into a SQL Server Agent job so that it runs every morning, and sends the DBA group an email of any databases missing backups over the last 24 hours. There are several system databases (databases that are created upon install) for SQL. One of them is the MSDB database that logs all backups and restores. Veeam logs its backups in this same database when it creates a VSS snapshot. Below is both the query and snippet of what the result looks like. MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date, DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE()) AS [Backup Age (Hours)] FROM msdb.dbo.backupset WHERE msdb.dbo.backupset.type = 'D' GROUP BY msdb.dbo.backupset.database_name HAVING (MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(hh, - 24, GETDATE())) Last database backup is a good query to run if you discover in the previous report that databases have not been backed up in the last 24 hours. This will show you the last time that database had a successful backup. As you can see below, Veeam is logging its backups in MSDB. SELECT A.[Server], A.database_name, A.last_db_backup_date, B.backupset_name, B.has_backup_checksums, B.is_damaged, B.backup_start_date, B.backup_size, B.physical_device_name, B.description FROM ( SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, msdb.dbo.backupset.database_name, MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id WHERE msdb..backupset.type = 'D' GROUP BY msdb.dbo.backupset.database_name ) AS A LEFT JOIN ( SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date, msdb.dbo.backupset.backup_size, msdb.dbo.backupset.has_backup_checksums, msdb.dbo.backupset.is_damaged, msdb.dbo.backupmediafamily.physical_device_name, msdb.dbo.backupset.name AS backupset_name, msdb.dbo.backupset.description FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id WHERE msdb..backupset.type = 'D' ) AS B ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date] ORDER BY A.database_name PowerShell script for last database backup is similar to the previous SQL query, but this offers a way to achieve the same thing via PowerShell instead. The only change that has to be made is to edit the name of the SQL server. This will report on the last TLOG and full backup. [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "" $dbs=$s.Databases #Retrieves the last backup dates - both for FULL and LOG backups $dbs | SELECT Name,LastBackupDate, LastLogBackupDate | Format-Table -autosize Restore history and who did a restore is a great report for audit purposes to confirm who did restores, and when they did them. SELECT [rs].[destination_database_name], [bs].[database_name] as [source_database_name], [rs].[restore_date], [rs].[user_name], [bmf].[physical_device_name] as [backup_file_used_for_restore] FROM msdb..restorehistory rs INNER JOIN msdb..backupset bs ON [rs].[backup_set_id] = [bs].[backup_set_id] INNER JOIN msdb..backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id] ORDER BY [rs].[restore_date] DESC Monitoring for Oracle Similar to SQL there are several commands native to Oracle Recovery Manager (RMAN) that can be executed to monitor backups. It is important to note that unlike SQL though, Veeam has a RMAN plug-in. Meaning that any backup, restore, validate, duplicate, flashbackup, etc command an Oracle DBA ran in RMAN prior to Veeam still works with the Veeam RMAN plug-in. From a 10,000 foot view all the Veeam plug-in does is route the database backups to a Veeam repository and give the data protection admin visibility into the backups. Below are a few RMAN commands I like for monitoring backups. List of all backups provides a nice summary of the database backups. list backup summary; List of database files missing backups over the last day is a great way to confirm your Oracle database has a backup in the last 24 hours. REPORT NEED BACKUP RECOVERY WINDOW OF 1 DAYS DATABASE DEVICE TYPE sbt; Detailed backup history will include the size of the backup, type of backup and the compression ratio. This command needs to run on the actual database itself by connecting to SQLPLUS. COL in_size FORMAT a10 COL out_size FORMAT a10 SELECT SESSION_KEY, INPUT_TYPE, COMPRESSION_RATIO, INPUT_BYTES_DISPLAY in_size, OUTPUT_BYTES_DISPLAY out_size FROM V$RMAN_BACKUP_JOB_DETAILS ORDER BY SESSION_KEY; In summary, Veeam is able to meet the most important needs DBAs have from a database protection standpoint. For Oracle, there is absolutely zero change to a DBAs day-today as the Veeam RMAN plug-in functions the same as without Veeam. For SQL, DBAs have given up control of their backups to the backup admin, but they can still verify, monitor and restore in a similar way as before. #Monitoring #SQL #Oracle #Veeam #restore #DBAs #verify

  • Backup and Restore MySQL Databases

    In previous posts, we covered Oracle and SQL in great detail. In this post, we are going to put on a clean t-shirt, give ourselves a shave (I could use one with all this WFH) and become a little more modern by jumping into MySQL. Much like SQL and Oracle it is common to use native tools such as mysqldump to protect databases. These dumps can target a local file on disk, attached disk or even AWS S3. First, let's chat about how to backup/restore with native tools for MySQL. Backup and Restore of MySQL with Native Tools (mysqldump) Protecting databases in MySQL is a common and familiar task to a DBA. The capability is built into MySQL via mysqldumps. The below command will backup"testdb" database to a local file named "dump.sql" in /var/lib/mysql. This directory location is the default location for MySQL database files, but it is common to place database files in a different location or attached disk for larger environments. Backup database: mysqldump -u [user] -p [database_name] < /var/lib/mysql/dump_file.sql Now that we have a backup, let's up the stakes and actually drop (delete) the database "testdb." Before we delete it though, take note of the two tables inside "testdb." Above we can confirm "testdb" no longer exists in our list of databases on the server. There is no need to fear though. Simply recreate the database, point it to the backup file, and all the database contents will be restored. Create database: create database [database_name] Restore database: mysql -u [user] -p [database_name] < /var/lib/mysql/dump_file.sql To prove it worked, we can connect to MySQL and dive into "testdb" to confirm the two tables have been restored. MySQL dumps are an easy and effective way to ensure databases are protected and easily recoverable, but what if we want to protect more than just the database? What if we want to protect both the server and the database together? Or what if we don't have a dedicated DBA to spend the cycles on this? As you may have guessed by now, Veeam has a solution for this. Backup and Restore MySQL with Veeam Veeam Agent for Linux integrates with Oracle, MySQL and PostgreSQL. Today, we are focused on MySQL, but previous posts have focused on Oracle, and PostgreSQL may find its way into future posts. Not only does Veeam protect the server, but also protects the underlying database regardless if the storage engine is MyISAM or InnoDB. What is MyISAM and InnoDB though? Both are the underlying storage engines for databases that come pre-built with MySQL that serve their own purposes. Choosing one over the other isn't as easy as you might think or hope, but at a high-level it comes down to what MySQL refers to as "Locking." MySQL locks a table (MyISAM) or row (InnoDB) for data integrity purposes during queries on the database. Typically, InnoDB is leveraged for high IO workloads and MyISAM is leveraged for heavy read workloads. When creating a backup job simply check, "Enable-application-aware processing" and specify credentials with privileges to SELECT and LOCK TABLES (for MyISAM only). If you prefer to create the job via CLI refer to this guide. And what is the point of backups? To do a restore! Similar to mysqldump we could restore files if a database was dropped, but let's use a more common scenario where you want to clone/refresh the database from a backup. First, we open the Veeam Explorer to search for our database files. In Veeam Explorer, we can easily find our "testdb" database files in the default database location (/var/lib/mysql). In addition, we can see the tables inside "testdb." Once you find the directory for the database (testdb) that you want to restore right-click on the folder and choose which restore option you like. If we were trying to restore a dropped database "Overwrite" would be the option to choose. Since we are cloning/refreshing a database copy we are going to "Keep" a copy of the database files. Below we can see Veeam adds a ".Restored" suffix to the kept restored files, and we can confirm that the underlying database and tables were restored with the directory. Most likely this isn't what you want your database to be named though. The below command will change the name of the directory and the database to "clonetestdb." mv /var/lib/mysql/testdb.RESTORED-20200901180536 /var/lib/mysql/clonetestdb Now, you can connect to MySQL and confirm "clonetestdb" is available and see the underlying tables in the database. In summary, Veeam offers a great solution to protect both the server and the MySQL databases within the server. Protecting both empowers users to not only restore singular databases in the case of an accidental deletion or clone/refresh, but also opens a plethora of options in a true disaster scenario. #MySQL #databases #Veeam #backup #dataprotection

  • Protecting SQL and Active Directory Running on AWS EC2

    Your first thought when reading this title might be why run databases on Amazon Compute Cloud (EC2) instead of Amazon Relational Database Service (RDS)? At a high-level, RDS tends to be a great solution for smaller IT organizations who might not have dedicated databases administrators (DBAs), and/or organizations who don't have strict security and availability requirements. This won't provide a deep dive into when and why to choose one over the other as I assume you're running SQL/Active Directory on EC2s and looking for a way to protect and granularly restore if you're still reading this so let's dive in! Prerequisites and Assumptions: A lot of chatter and documentation exists on the basics to setup, configure and take crash consistent backups of EC2 instances with Veeam Backup for AWS. Instead this post will focus on the nuances and specifics to backup and restore SQL/AD on AWS EC2s. Before illustrating how-to backup and restore SQL let's meet the prereqs first. 1. Install AWS Systems Manager Agent (SSM) on any Windows EC2 instances that need app-aware processing. Most commonly these will be SQL or Active Directory servers. Invoke the below commands via PowerShell. Invoke-WebRequest ` https://s3.amazonaws.com/ec2-downloads-windows/SSMAgent/latest/windows_amd64/AmazonSSMAgentSetup.exe ` -OutFile $env:USERPROFILE\Desktop\SSMAgent_latest.exe Start-Process ` -FilePath $env:USERPROFILE\Desktop\SSMAgent_latest.exe ` -ArgumentList "/S" rm -Force $env:USERPROFILE\Desktop\SSMAgent_latest.exe 2. Install the VSS package via PowerShell which is used to achieve app-aware processing for Windows machines. Set-AWSCredentials –AccessKey {key_name} –SecretKey {key_name} Set-DefaultAWSRegion -Region us-east-2 (enter the region you're using) Send-SSMCommand -DocumentName AWS-ConfigureAWSPackage -InstanceId "instance_ID"-Parameter @{'action'='Install';'name'='AwsVssComponents'} 3. Install Veeam's External Repository in Veeam Backup and Replication server. This is covered well here in public documentation. Protecting SQL and Active Directory Running on EC2s Veeam offers AWS native protection for SQL and AD workloads. You can schedule both snapshots and backups to S3 Buckets. For organizations with more stringent backup and archival needs this is crucial because backing up to S3 is significantly cheaper than storing snapshots. EBS snapshots are ~$.05 per GB whereas S3 is ~$.02 per GB depending on which region you live in. If you have a retention of 30 days and tens or hundreds of TBs this can become very costly to say the least. Similar to on-premises, snapshots should still be leveraged for short-term retentions and quick recoveries, but long-term retentions should be placed on cheaper media for an overall better total cost of ownership to the business. 1. Enable app-aware processing on the policy. For bonus setup your policies by tag to automate the backup of SQL and AD servers 2. Define snapshot, backup and replication schedules and retentions to meet the availability needs for your mission critical servers. 3. Verify integration with VSS. Restore SQL and Active Directory Running on EC2s Now for the fun part! What good is a backup without a restore? And the beauty of this integration is you can leverage the famous Veeam Explorers for SQL and AD for granular recoveries. 1. Granularly restore databases by going to Veeam's External Repository and select Application Item Restore. 2. Restore a specific item, entire database or publish a database. Publishing is a common task to spin-up an environment for test/dev or for DBAs to run DBCC CHECKDB commands to verify they have a usable backup. 3. Verify Active Directory backups match production by opening the Veeam Explorer for AD. This is a great feature that scans the backup files for any changes compared to production. If anything looks odd you can restore only the differences. Not only it is important to have a solution that can protect your databases running on EC2s, but also it is crucial that the solution is cost effective. Having the ability to orchestrate snapshots, backups and replicas from one console and one license empowers IT organizations to design a solution that meets their availability needs on a budget. #SQL #AWS #EC2 #AD #dataprotection #backup

  • Scan Linux Backups for Malware

    I can still feel the buzz in the air from Veeam's v11 release. We are approaching two months since the GA annoucement, and the excitement around CDP (Continuous Data Protection), Linux Hardened Repository and object storage enhancements hasn't let up. With that said, I want to highlight a v10 feature that never received the love it deserved - Data Integration API. What is the Data Integration API? You can find plenty of content on how the Data Integration API can be used with Windows, but I want to highlight how it can be used with Linux too. For those new to the concept, Data Integration API allows users to easily reuse their backup data. You can mount any virtual (VMware and Hyper-V) or physical (Windows and Linux) backup to a target server of your choosing for malware scans, data mining, devops and many other purposes. How Can I use it for a Linux Backup? Once you have a virtual or physical Linux machine backup you simply need to run the below commands in PowerShell. The variables in the script contain the following: The backup job that contains my Linux machine. Note: you could choose a specific machine rather than a whole job $backup = Get-VBRBackup -Name "Orcl" The Linux server my backup will be mounted to: $targetServerName = "198.18.128.102" Credentials to that mount server. I have multiple root credentials so the "where" parameter chooses the one with the description I want. Note: you do not need to be root. I am just a bit lazy with security best practices in my home lab $targetAdminCredentials = Get-VBRCredentials -name "root" | where {$_.description -eq "root"} The restore point I want to use. In the below example, I am using the most recent restore point $restorepoint = Get-VBRRestorePoint -Backup $backup | Sort-Object –Property CreationTime | Select -Last 1 Publish the backup content $session = Publish-VBRBackupContent -RestorePoint $restorepoint -TargetServerName $targetServerName -TargetServerCredentials $targetAdminCredentials -EnableFUSEProtocol Once the Linux backup contents are published, you can SSH into the Linux mount server and see the file system in /tmp. A great use-case from this point would be to-do an anti-virus scan to ensure your backups are safe from malware. Personally, I used ClamAV because it was easy to install and free. Plus, you can run an AV scan on a specific directory with ClamAV. I didn't see that option with other free tools. Below is the command I ran on the centos-root directory confirming that the mounted Linux machine is clean of malware. clamscan -r -i /centos-root In Veeam a restore session will kickoff, and it should look like the below. In summary, Veeam's Data Integration API is a great way to check for malware on Linux backups. Veeam's Secure Restore in combination with SureBackup automates this process, but it only works for Windows machines today. Leveraging Data Integration API for Linux backups in conjunction with Secure Restore/SureBackup for Windows is a great method to ensure all backups are useable.

Subscribe Form

bottom of page