Hi,
I have a Active DB which is now over 16GB and growing, my worry is that one day my server is hacked and the hacker deletes the entire database. I know the server can create backups but even losing one booking could be a nightmare for us. I was thinking about maybe cloning the database but if the hacker was to wipe the Db the clone would
Also get wiped. Is there a solution where we can have a clone running on another server but protects us from this?
You can start exploring AlwaysON Availability groups.
so just a few questions regarding the Always on. If a hacker cleared the DB on node 1, but left all tables in tact, and node 2 was simply mirroring that would all the data also not be cleared on node 2?. How would we restore back to before it was hacked and cleared. Let's say the tables were cleared at 1am and we only found out at 8am how do we roll back?
Yes, any changes made to the primary database will be reflected to all the secondary nodes.
Having backups will help you to restore the lost data prior to 1 AM.
Besides cloning what levels of security do you have in your network and on your sql server.
Cloning in of itself is not optimal security mechanism. Cloning is more of DR plan which a wipe out does qualify as a disaster but does not fully address how they even commandeered the server. what layers or lack of security layers did they bypass to get to the server in the first place is what you really need to look at. Users direct access, weak passwords, sql authentication vs svc accounts, encryption etc
Do you have all your ducks in a row currently as far as security
The way to protect against a hacker wiping our your database is to perform FULL database backups and transaction log backups and encrypting the backup files.
A full backup performed every day - and transaction log backups every 5 minutes would allow you to recover the data up to the point in time where the hacker wiped out the database. Encrypting the backup files prevents the hacker from messing around with those files - and copying those files off to another location decreases the risks of something happening where you lose those files.
For example - let's say a hacking wipes out all data in your database at 9:16am. Once you have made sure that hacker can no longer access the system, you can then perform these steps:
You may end up losing some data - depends on if you can identify exactly when the commands to delete your data were executed. And since you have multiple tables those commands could be across time so you may end up losing several hours.
The only way to prevent that from happening at all is to make sure you secure the system. Unfortunately, that is a lot of work - but it needs to happen.
