###BeginCaseStudy###
Case Study 1
Litware, Inc
Overview
You are a database administrator for a company named Litware, Inc. Litware is a book
publishing house.
Litware has a main office and a branch office.
You are designing the database infrastructure to support a new web-based application that is
being developed. The web application will be accessed at www.litwareinc.com. Both internal
employees and external partners will use the application.
You have an existing desktop application that uses a SQL Server 2008 database named
App1_DB. App1_DB will remain in production.
Requirements
Planned Changes
You plan to deploy a SQL Server 2014 instance that will contain two databases named
Database1 and Database2. All database files will be stored in a highly available SAN.
Database1 will contain two tables named Orders and OrderDetails. Databasel will also
contain a stored procedure named usp_UpdateOrderDetails. The stored procedure is used to
update order information. The stored procedure queries the Orders table twice each time the
procedure executes. The rows returned from the first query must be returned on the second
query unchanged along with any rows added to the table between the two read operations.
Database1 will contain several queries that access data in the Database2 tables.
Database2 will contain a table named Inventory. Inventory will contain over 100 GB of data.
The Inventory table will have two indexes: a clustered index on the primary key and a
nonclustered index. The column that is used as the primary key will use the identity property.
Database2 will contain a stored procedure named usp_UpdateInventory.
Usp_UpdateInventory will manipulate a table that contains a self-join that has an unlimited
number of hierarchies.
All data in Database2 is recreated each day and does not change until the next data creation
process.
Data from Database2 will be accessed periodically by an external application named
Application1. The data from Database2 will be sent to a database named App1_Db1 as soon
as changes occur to the data in Database2.
Litware plans to use offsite storage for all SQL Server 2014 backups.
Business Requirements
You have the following requirements:
• Costs for new licenses must be minimized.
• Private information that is accessed by Application must be stored in a secure format.
• Development effort must be minimized whenever possible.
• The storage requirements for databases must be minimized.
• System administrators must be able to run real-time reports on disk usage.
• The databases must be available if the SQL Server service fails.
• Database administrators must receive a detailed report that contains allocation errors
and data corruption.
• Application developers must be denied direct access to the database tables.
Applications must be denied direct access to the tables.
• You must encrypt the backup files to meet regulatory compliance requirements. The
encryption strategy must minimize changes to the databases and to the applications.
###EndCaseStudy###
You need to recommend a solution for the deployment of SQL Server 2014. The solution
must meet the business requirements.
What should you include in the recommendation?
A.
Create a new instance of SQL Server 2014 on the server that hosts the SQL Server 2008
instance.
B.
Upgrade the existing SQL Server 2008 instance to SQL Server 2014.
C.
Deploy two servers that have SQL Server 2014 installed and implement Failover
Clustering.
D.
Deploy two servers that have SQL Server 2014 installed and implement database
mirroring.
Explanation:
Scenario: The databases must be available if the SQL Server service fails.
Why is C correct? I’d assume to keep licensing cost low, and with no mention of needing HA, you would just install the 2014 instance on the same box.
Scratch that I see the HA piece now.
Why not D.
The cost licenses must be minimized.
We have high available SAN.
It sounds like mirroring.
I think that there is no more mirroring in SQL 2014
Why no more mirroring?! Mirroring present in SQL 2014, but best solution is use Always ON.
Yes sorry you are right as the cost must be minimized so we do not have to get sql server enterprise edition for always on but we can get the sql server standard edition and install the mirror on it to make the database avaible even if the sever fails down
Thanks
for mirroring with this situation u need 3 servers, primary, mirror and witness. Its say that “Data needs to be available if SQL Service fails”, so if u want automatic failover with mirror u need third server as witness.
U can create FCI with Standard edition and u will use just one database on SAN, which reduce storage cost.
So its clear that right answer is C
I’m thinking cluster here due to the need to minimize storage
B
You have an existing desktop application that uses a SQL Server 2008 database named App1_DB. App1_DB will remain in production. Hence, the SQL 2008 DB can’t be upgraded (w/out affecting the App1_DB).
Also, Mirroring is been deprecated in SQL2014 upwards, so it is discouraged from being used.
C is correct
**You plan to deploy a SQL Server 2014 instance that will contain two databases named Database1 and Database2.
**All database files will be stored in a highly available SAN.
Best option is option C. The company is bothered about license cost, but is also interested in HIGH AVAILABILITY (The databases must be available if the SQL Server service fails). So?! Option C.
Agree. Failover clustering with 2x nodes is available in Standard Edition anyway. Option C.