SQL Server Proposal ITS410 - Database Management

SQL Server Proposal Margaret E. McWhorter ITS410 - Database Management Colorado State University – Global Campus Dr. Mazen Alkhatib January 4, 2017 SQL Server 2012 Corporate Proposal Overview Thank you for the opportunity to present this proposal on behalf of McWhorter Consulting. Our recommendation is that Shiv Corp transitions their systems from MySql to SQL Server 2012. This proposal provides details for the transition, system requirements, data security, performance monitoring, and a back-up plan in case of system outage or failure. Which Edition Should you Choose? There are a number of editions and versions of SQL server 2012 for a company to choose from and utilize. The editions range in functionality and size and include, Express, Web, Standard, Enterprise, and Business intelligence. It is our recommendation that Shiv Corporation utilizes the Enterprise edition. This version of SQL Server 2012 is the most in depth, it contains all of the tools necessary to run a in depth SQL program without having to purchase additional software. The other editions are all reasonable options, but will not be all inclusive of the items that the company may need in order to operate and ensure there is no impact to their daily productivity. Though these options from a start up cost may be cheaper, we believe that the overall start up investment will save money in the future when compared to the other editions of SQL Server 2012. SQL Server 2012 Enterprise Edition is the better option over the standard version as it will provide more functionality for Shiv Corporation. SQL Server 2012 Enterprise allows for more than 64GB of memory per instance which will help stabilize and ensure enough memory is available to keep up with task being assigned to the server. Enterprise Edition also offers Partition Switching, AlwaysOn Availability groups, Resource Government, Columnstore Indexes, Online Reindexing, Advanced Adapters, Advanced Transforms for SSIS, Change Data Capture, and SSRS Data Driven Subscription just to name a few (McCown, J, 2014.). All of these functions help to make the system administrators job a little easier and the can manage the database more efficiently. SQL Server 2012 Enterprise Edition can handle up fifty multiple instances at one time, which increases scalability and performance in growing corporations. The features mentioned above are what allow for its ability to perform at a higher level than the other editions available. Enterprise Edition includes such scalability features as table and index partitioning, data compression and partition table parallelism. Enterprise Edition comes with PowerPivot for SharePoint and Master Data services. This features allow for easier access to data thus increasing the ability to quickly efficiently analyze data in the SharePoint environment, and helps to ensure data consistency and manageability across your company (Sheldon, R., n.d.). System and Security Requirements There are a few requirements in order to have a successful install of SQL Server 2012. Hardware must meet certain minimums in order to run SQL server. There are also a few recommendations that Microsoft provides in order to best optimize SQL server 2012. SQL server is designed to run on multiple types of devices, from desktops and laptops. The minimum processing speed for the 32-bit implementation is 1.0GHz and for the 64-bit implementation is 1.4GHz, it is our recommendation that Shiv Corporation utilizes the 64-bit implementation as this will allow for use of all features. The 64-bit addition allows for hot swapping of memory which will allow the system administrator to switch out CPU’s without having to shut down the server. Microsoft recommends, but does not require that with either implementation you utilize a 2.0 GHz processor. SQL server 2012 Enterprise Edition requires at a minimum of 1GB of memory be available. Microsoft recommends a minimum of 4GB of memory, we would recommend that 6GB of memory be available to ensure the system is operating at a high level. These system requirements should not be a problem and Shiv’s current systems should be able to meet these requirements, as the requirements are on the low side considering the PC’s on the market today. Security Protocols must be in place to ensure that hackers and unauthorized users cannot access the server and compromise business data. If sensitive company data, such as credit card information and client address are secured on the server and security protocols are not in place Shiv Corporation could be liable financially for theft or unlawful use of this information. We would recommend that Shiv Corporation store their servers in a secured location, with key or card access. Back-Up servers should be stored in a different location than the servers, preferably off-site. This practice will allow for back-up of data if there is damage to the main servers at the primary location, by having the back-ups off site you decrease the probability of damaging both servers. System Administrators will need to ensure that they have plans in place to install updates on a timely and consistent basis. Shiv Corporation will need to ensure that it’s using firewall software to aide in preventing unauthorized access to the servers. Certificate based security should be used for server authentication. Utilizing this type of security will allow a secure connection between the client and server. Certificate based security will ensure that the CPU connecting to the server is authorized. This security type also allows for verification to take place even when the client is outside the local area network (LAN). We will implement an IPsec policy to ensure that connections to the server are also secure within the local area network (LAN) (Akkawi, F., Akkawi, K., & Schofield, G, 2014). There are two server authentication modes: Windows Authentication Mode and Mixed Authentication Mode. Windows Authentication mode utilizes window’s log-in to allow a user access to the server. This prevents users from having to log-on every time they need to access the server, while still providing security. Window’s users will be provided access to the servers based group policy objects assigned by system administrators. Mixed Authentication mode utilizes windows log in and a separate SQL server log in to grant access to servers. When users are required to remember multiple log on ID’s they often times write these ID’s down thus leaving Shiv Corporation vulnerable to unauthorized users gaining access to servers. Given this risk of information falling into the wrong hands we would recommend that Shiv Corporation utilize the Windows Authentication Method over the Mixed Authentication Method. Database Administrators will be responsible to ensure access to the system is being provided to users based on the level of access needed to preform daily tasks. System administrators can grant access levels and permissions to users on an individual or group basis. Caution should always be utilized when providing access and access should not be granted a greater need then required. Administrators have the ability to create roles to make granting access easier based on job titles and needs, allowing administrators more time to focus on other areas of the database. An additional layer of security we would recommend is the use of asymmetric keys to encode the data contained on the server. SQL Server 2012 offers 2 types of encryption: Symmetric and asymmetric keys to secure data. Symmetric encoding requires less processing power to encrypt data than asymmetric and allows the same key to be utilized to encrypt and decrypt data. Asymmetric keys are more secure than symmetric keys because a different key must be utilized in order to encrypt and decrypt data. Even though asymmetric encryption requires more system resources it provides additional security that we believe is required to protect Shiv Corporations sensitive data form hackers. (Akkawi, F., Akkawi, K., & Schofield, G., 2014). Optimization and Back-Up Optimization tools should be used by system administrators to ensure database, query, schema, and index performance is maintain at an optimal level. Utilizing index views of certain data types will help to increase performance. Ensuring that constraints and relationships are defined, and using specific data types when running queries will utilize less space and increase speed. Administrators should not use commands such as Like, this command uses large amounts of system resources to run (Akkawi, F., Akkawi, K., & Schofield, G., 2014). DBA’s should ensure that they have daily weekly, and monthly checklists in place that provide for reviewing and updating indexes form time to time, as required to optimize performance. Monitoring performance of SQL server system is a key function for administrators to handle. Administrators should utilize performance counters in the system monitor function to monitor resources. There are a number of counters available within the tools, we recommend that DBA’s are monitoring counters for processor information, disk input and output, and length of time for process to complete, at a minimum. DBA’s should utilize these monitoring tools and configure them to what works best for the business and the user. Database administrators should be utilizing concurrency control to protect the integrity of the data in the system. When numerous edits are made in the same time period data integrity could be lost. Database Administrators can help prevent bad overwrites in the system by ensuring they are following these best practices; Complete table edits in the same order, break down large query edits into small ones, attempt to keep transactions as short as possible. To ensure there is no loss of time or business essential functions Shiv Corporation should ensure that there is a full recovery plan in place. A full recovery plan will allow for a quick restoration of systems if there is an outage or the crash on the servers. This plan should include full database back-up, differential database back-up, and transaction log backup. The full database backup model takes snapshots of the entire database on a periodic basis to allow for full system restore in case of the system is lost entirely. The differential database backup utilizes less a method where it captures changes to the system and backs up those changes only, this method is shorter to run then a full backup and can be utilized when there is loss to data for a given period of time and not the entire system. It is also a good practice for DBA’s to ensure they are backing up transaction logs so they can be referenced when restoration is required. “For a given type of backup, the optimal interval depends on factors such as the importance of the data, the size of the database, and the workload of the server” (Microsoft, 2014b). Shiv Corporation should consider utili