Database Security and Data Recovery University of Phoenix

Database Security and Data Recovery

How secure is your database? Statistics show that there has been significant growth in data breaches over the past 14 years. In 2005 136 data breaches were reported, now more than 4,500 data breaches have been made public since 2005 with more than 816 million individual records being breached (Groot, 2019). The rapid growth of global data will continue to rise with a projection of data production will be 44 times greater in 2020 than it was in 2009. With the increasing pressure of compliance regulations and security policies deploying a high-level database protection plan is a must and should be taken seriously.

Let’s begin by first defining the maintenance and security components necessary to secure a database. First, we must minimize the SQL server exposure and do not leave open any back doors (aka open ports). We start here to take our first steps towards minimizing security risks for the SQL server even before we completed our installation and configuration of the new database. Changing the SQL Server default port numbers will help minimize service and data exposure as well as hide the SQL server instances.

Now we move on to access controls of the SQL server, this is where we decide who should be allowed access. When planning user and service account authentication we need to be super-mindful when establishing user accountability and avoid misuse of the privileged accounts. Whenever possible you want to integrate authentication with the Windows OS, integrated authentication will encrypt messages to valid users, while the built-in authentication passes through the SQL server logins and passwords across the network keeping them unprotected (Fimin, 2014).

Planning database security in advance is an important issue when dealing with securing sensitive data. Always start by identifying the needed level of protection and encryption

depending on the type of data you will store in the database. For example, if you plan to store credit card numbers of patient health information (PHI) you will need to ensure that you meet the PCI or HIPPA compliance regulations. Having a strong plan will guide you towards the best approach possible when securing your data (Matelski, 2015).

Patching your SQL servers regularly is a top priority when it comes to best security practices. Attackers are always actively looking for new security flaws in information systems, and new malware and viruses appear daily. Establishing proper mandatory patch management will ensure that your SQL servers are meeting standard security policies.

The most effective strategy to ensure database security is to keep track of what’s going on. Establishing accountability in many respects means staying up to date with configuration changes and user activity. This is an ongoing process of maintaining the actual state of security policies to make sure that all changes are authorized and documented (Fimin, 2014). Now that we have discussed some of the security aspects let’s now explore how data replication can be useful to improve the availability of your data.

DBMS Data Replication

Data Replication is the process of storing data in more than one site or node. It is simply copying data from a database from one server to another server so that all the users can share the same data without any inconsistencies. When you replicate data, the result is a distributed database in which multiple users can access data that is relevant to his/her task without interfering with others. There are three types of data replication, they are, transactional, snapshot and merge replications. For the Dream Home database project, I recommend the use of transactional replication, let’s explore the three different types of data replication.

Transactional replication occurs when users receive full initial copies of the database and then receive updates as data changes occur. Data is copied in real-time from the server to the client in the same order as they occurred. With this type of replication transactional consistency is guaranteed.

Snapshot replication distributes data exactly as it appears at a specific moment in time

and cannot be used to monitor updates to the data. The snapshot is generated and sent off to users if updates to the data occur the users will not be able to see the changes unless an updated snapshot is sent out. This method of replication is generally used when data changes are infrequent (Pandey, 2017).

Merge replication is when data from two or more databases are combined into a single database. This method of replication is the most complex type because it allows both the server and client to independently make changes to the database, this is typically used in a server-to- client environment as it allows the changes from one publisher to multiple subscribers.

DBMS Backup and Recovery Strategies.

One of the most important tasks a database administrator will face is the need to constantly protect the integrity of the database and maintain the ability to recover data quickly in case of system failure. It’s critically important to have a backup and recovery strategy in place to be prepared in case of any emergency.

Administrators and organizations must be prepared to deal with disaster recovery scenarios. One way of doing so is by testing the SQL server backup and restore strategies at regular intervals. This will help to ensure seamless recovery of data in the event of an emergency. A seamless recovery usually means faster uptime of systems with minimal or no data loss. Part

of this strategy is to also safeguard the data from various data failures which heavily tie into the backup and restore planning and strategies (Jayaram, 2018).

Key points to consider when defining a good backup strategy are to ensure frequent data backups whenever data changes occur or on a weekly basis. This may also include changes to the schema or online transaction processing. If data changes occur often daily backup of the data

may be necessary. There are also plenty of advanced techniques available such as Clustering, AlwaysOn, LogShipping and Mirroring that can help ensure higher availability, disaster recovery is all about having a well-defined and tested backup and restore process in place.

References

Fimin, M. (2014, November 12). 5 Top Tips to Maintain the Security of Your Database Environment. Retrieved from Database Trends and Applications: http://www.dbta.com/Editorial/Trends-and-Applications/5-Top-Tips-to-Maintain-the- Security-of-Your-Database-Environment-100526.aspx

Groot, J. D. (2019, January 3). The History of Data Breaches. Retrieved from Digital Guardian: https://digitalguardian.com/blog/history-data-breaches

Jayaram, P. (2018, April 26). Backup and Restore (or Recovery) strategies for SQL Server database. Retrieved from SQL Shack: 2018

Matelski, J. (2015, March 25). IOUG Insight: 5 Best Practices for Securing Databases.

Retrieved from Database Trends and Applications: http://www.dbta.com/Columns/IOUG- Insight/5-Best-Practices-for-Securing-Databases-101930.aspx

Pandey, H. (2017, June 28). DBMS Data Replication. Retrieved from Geeks for Geeks: https://www.geeksforgeeks.org/dbms-data-replication/