You are a senior Database Performance consultant from Superhero Computer Solutions, and have been engaged by Synergy Sol to help the aging DBAs to fix the performance problems. How would you solve this performance challenge? Please read the case study scenario carefully. You should state any assumptions

Your proposal response should draw upon the knowledge that you have learned in your lectures as well as having read the required course text book. Additional reading for this assignment through research would enhance your proposal.

Commercial Perspective:

This assignment has been designed to give you a general oversight of the typical issues that you will face as a database administrator/consultant in most commercial situations. The objective is to prepare with a solid foundation of good database design principles and troubleshooting techniques.

Part I Case Study - CRIM Workflow Scenario

Synergy Sol is a large legal firm based in Sydney. In the 35 years since establishment it has grown into a 150 strong organisation that specialises in helping Government agencies with legal matters relating to large contracts. Over the years Synergy Sol have invested heavily in IT, and in particular a document workflow management system that has been their competitive edge in keeping ahead of the competition and helping keep costs down. The core technology components have been the CRIM document and workflow management system.

Over the last 4 years, CRIM has been stable, but with the recent Infrastructure upgrade, Synergy Sol have gone for the latest Windows 8.1 Operating system as well as upgrading CRIM to work on Windows Server 2013 Release 8. They have invested heavily in New Windows workstations and new Dell Servers.

The basic architecture of the CRIM application has now been enhanced to integrate with the MS Office suite. On the desktop, the old CRIM application was replaced with an MS Office Suite plugin that connects via a SQL adapter to the database that is running on an upgraded server.

On the server side, the CRIM Application Server and Database Server runs one DELL server. Documents are now directly saved from Outlook and MS Word without the user having to save a local copy. With the old application, the user had to use the stand alone CRIM client application to upload to the CRIM documents management system. It was the promise of this automated feature that prompted the million dollar upgrade.ISYS224 Assignment Two Page 5 Semester 2, 2014 Figure 1.0 Logical Architecture Diagram of CRIMISYS224 Assignment Two Page 6 Semester 2, 2014

CRIM Upgrade History

During the infrastructure upgrade the OLD CRIM database was migrated 'copied' to the new server and the new CRIM application pointed to the old Database. 3 months after paying $3 million for the upgrade, the application started to slow down dramatically. The performance symptoms were as follows:

Microsoft Outlook would launch but is very slow. Word documents were taking forever to save. (it can take more than 30 seconds) What used to be a few mouse clicks has become a major service escalation with only pockets of user being affected at any one time. The help desk remedy was to ask their users to reboot their brand new desktops. This used to work initially, but now the solution seems to be ineffective and exacerbates the problem. There also seemed to be no logical groupings of users affected.

Your proposal should recommend a course of action to solve the database performance problems. Hints: Baseline techniques, Physical Database Design; Network Contention; I/O bottlenecks; Performance bottlenecks; database hotspots; Hardware Configuration etc. as discussed in the lectures.

Part II Case Study - Piccolo Case Manager Scenario

Piccolo is another standalone application that is used to lodge new cases. For every new client and each subsequent engagement, Synergy Sol logs the activity/investigation as a case. The Piccolo client is also installed on the SOE (Standard Operating Environment - Recommend doing some research to be familiar with this term). Due to the growth in the volume of business, the Piccolo system is currently experiencing a host of performance issues. The MS SQL 2012 Server database seems to be the cause of all the performance issues. As part of the investigation, you discovered the following problems:

1) The Audit logs and the database reside on the same hardware infrastructure.

2) A small group of users are running regular ad hoc reports against the live database - impacting the performance of the application for all users.

3) There seems to be no database backup systems in place!

Prepare a short report that details your recommendations to address these findings. This section of your report should also present a proposed architecture diagram for your recommended solution. Hint: This section is about Transaction Log / Audit Log best practices/ Database Replication and reporting copies. Database backup solutions. Consider also: DB performance Base lining Methodology

Disk Planning - RAID Types; Database Migration best practice/checklist; Database Securty etc Refer to lecture and tutorials for inspiration.