DRAG DROP
You use SQL Server 2014 Enterprise Edition. Your database contains a partitioned table
named AuditData. AuditData is partitioned by year. Partition 1 contains data from the year
2010 and prior.
Management has decided to archive all AUDITDATA records from 2010 and prior.
Management wants the records to be removed from the database entirely and provided to
the backup team as a zipped text file. The data must no longer reside in the database. There
is very little tolerance for performance degradation in your environment.
You need to remove all 2010 and prior data from the AuditData table by using the least
amount of system resources possible.
Develop the solution by selecting and arranging the required SQL actions in the correct order.
You may not need all of the actions.
Answer: See the explanation.
Explanation:
Box 1: CREATE TABLE
Box 2: SPLIT RANGE
Box 3: SELECT INTO
Box 4: BCP
Box 5: DROP TABLE
Box 6: DROP PARTITIONNote:
* Create a new partitioned table with the partition function you want, and then insert the data
from the old table into the new table by using an INSERT INTO…SELECT FROM statement.* SPLIT RANGE ( boundary_value )
Adds one partition to the partition function. boundary_value determines the range of the new
partition, and must differ from the existing boundary ranges of the partition function. Based
on boundary_value, the Database Engine splits one of the existing ranges into two. Of these
two, the one where the new boundary_value resides is considered the new partition.
* BCP can be used top produce the zipped text file.
* Example:
plitting a partition of a partitioned table or index into two partitions
The following example creates a partition function to partition a table or index into four
partitions. ALTER PARTITION FUNCTION splits one of the partitions into two to create a
total of five partitions.
CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES ( 1, 100, 1000 );
GO
–Split the partition between boundary_values 100 and 1000
–to create two partitions between boundary_values 100 and 500
–and between boundary_values 500 and 1000.
ALTER PARTITION FUNCTION myRangePF1 ()
SPLIT RANGE (500);
It Should be:
1. CREATE TABLE –create the new table with same schema
2. MERGE RANGE – Merge all 2010 and prior data in one partition
3. SWITCH PARTITION – to this new table
4. BCP – to create a text file from a new table
5. DROP TABLE – this new table
You’re missing the drop partition command on the end. Also, I’m curious why you need the merge range when the data being removed (2010 and earlier) is already contained in partition 1?
Why using “Merge range”? In question says “Partition 1 contains data from the year
2010 and prior”
I agree with the other two posters. The data is already in the partition so only a switch is needed
There is no drop partition command. The merge could be after switch to eliminate empty partition, but it is not necessary in this scenario.
As per this article:
http://blogs.msdn.com/b/felixmar/archive/2011/08/29/partitioning-amp-archiving-tables-in-sql-server-part-2-split-merge-and-switch-partitions.aspx
I would do it in this order:
1. CREATE TABLE
2. SELECT INTO
3. BCP
4. DROP TABLE
5. MERGE RANGE
SWITCH PARTITION – switch works only if the partitions are in the same filegroup. That means the archive tables needs to be created in the same filegroup. But we need it to be in a separate filegroup to separate it from all the other data, and archive, no?
Yes this is the most logic Answer
1- Create a new table (to hold the old data in it and it can be a temp table also)
2- Select from the partitioned table into the new table(move the data from the partitioned table to the new table)
3- BCP (Bulk Copy Program) and it is a tool to move the data from a table to a txt file or vise versa
4- Drop the new Table (as I get all the data from it)
5- Merge Range(of the old partitioned table because there is no old data any more)
But also we need to delete the data from the Partitioned table because the select into is coping the data and keeping the original data at the old table
So instead of the upper answer
1- create table(new table)
2- Create Partition(the same as the partitioned table)
3- Switch range (to move the data and delete it from the source table)
4- BCP (to copy the data from the new table to a txt or Zip file)
5- Drop the new table (as i moved the data from it)
6- Merge range (of the old table )
There’s no ‘Create partition’, and no ‘Switch Range’ in the options provided.
1. create new table
2. switch partition
3. BCP
4. drop the new table
why do I need to do the merge at the end?
https://technet.microsoft.com/en-us/library/ms191160(v=sql.105).aspx
http://www.toadworld.com/platforms/sql-server/w/wiki/9658.switching-partitions-example
+1
We merge the partition only if we want to drop the FG which is not required here.
ADVENTURE WORKS CYCLE CASE STUDY
A. General overview
Adventure Works Cycle is an international company that sells cycling equipment and accessories.
Adventure Works cycles currently has 300 employees, and is experiencing rapid growth.
The company has a sales department, a marketing department, an accounting department, and a human resources department.
• Physical Location
The company has one main office located in Los Angeles. The office contains 100 employees. The other 200 employees, called field associates, are spread across North America and Europe.
B. Existing Environment.
• Active directory
The network contains one Active Directory forest named adventure_works.com. The forest contains a single domain. All domain controllers run Windows Server 2012 R2.
• SQL Server
Adventure Works Cycles has an existing customer relationship management (CRM) system that the company plans to replace. The company is developing a replacement application in-house. The new CRM application will be used by the field associates and by the sales management team in the main office.
The network currently contains one SQL Server 2008 server located in the main office. For the new application, the company plans to deploy additional SQL Servers for failover and redundancy and plans to use SQL Server 2014.
• Problem statements
Adventure Works cycles identifies the following issues with the current CRM system:
• Users report that a view named View1 takes a long time to run.
• Due to high network latency, users report slow database access times.
• Users do not always have current sales data and product inventory data.
• Database updates sometimes timeout. The database administrators discover the occurrence of deadlocks.
• Existing backup Solution
Currently, SQL server backups are stored on Linear Tape-open (LTO) tapes drives for long-term storage.
Adventure Works Cycle wants to eliminate completely the hardware requirements for long term backup storage.
C. Requirements
• Databases
The new CRM application will use three databases named DB1, DB2, and DB3. DB1 will be used by developers for testing purposes. DB1 will be a copy of DB2. DB2 will be the production database for the application. DB3 will be used for SQL Server Reporting Services (SSRS).
• Technical Requirements
Adventure works cycles identify the following technical requirements for the new CRM application:
• Database locking must be minimized.
• Database downtime must be minimised if a database fails.
• Users must not have direct access to the database tables.
• Multiple development teams will work on the new application. The design team must be able to modify the database tables. The development team members must not be able to modify tables, but must be able to create stored procedures and views.
• Schemas
In DB2, you create a schema named prod_schema1. You create a new database role named dev_role1. Dev_role1 must be able to retrieve data from all of the tables that belong to dev_schema1.
In DB2, you create a schema named dev_schema1. Dev¬_schemma1 contains several tables. Developers will create new tables in dev_schema1 only. Once the tables are validated, the tables will be move to prod_schema1.
• Sales.TransactionHistory
You plan to create a table named Sales.TransactionHistory. You will create an index for the table by using the following statement.
CREATE NONCLUSTERS INDEX [IX_TransHistory_PrductID] ON [Sales]. [TransactionHistory]
(
[ProductID] ASC
)
WITH (FILLFACTOR =0)
You will create a query named Query1 that uses the following statement.
SELECT [Sales].[Transaction History].prductID,
[Sales].[TransactionHistory].[Quantity]
FROM Sales.TransactionHistory
ORDERS BY Sales.TransactinHistory.Quantity ASC
During testing, you run the SQL Server profiler and you discover that Query1 uses a significant amount of disk resources while executing.
When user updates Sales.TransactionHistory, dirty reads, nonrepeatable reads, and phantom reads occur and must be prevented.
• Sales.SalesOrder
You plan to create a table named Sales.SalesOrders by using the following statement.
CREATE TABLE [Sales].[SalesOrders] (
[SalesOrderID] int IDENTITY (1, 1) NOT NULL,
[PartNum] int NOT NULL,
[DateRequested] datetime NULL,
[Qty] int NOT NULL,
[UnitAskPrice] float NULL,
[ShipDate] datetime Not NULL,
);
Go
You must ensure that when users update SalesOrders, other users are prevented from modifying the data in the table while the update is installing.
• Database Health
Adventure Works Cycles identifies the following health requirements:
• In Microsoft Azure, create and store a backup copy of DB1 once a week.
• Check the consistency between system metadata tables for DB1.
• Check the consistency of the disk space structure used by DB2
• Check the logical and physical consistency of the objects in DB3.
• User reports
Frequently, users will access a report named Report1. Report1 will be based on a view named View1. View1 will aggregate data from multiple columns. View1 will perform complex calculations.
Question 1
What should you recommend for the updates to Sales.TransactionHistory?
A. query hints
B. a SERIALIZABLE isolation level.
C. a REPEATABLE READ isolation level.
D. a READ COMMITED isolation level.
Question 2
You need to recommend a solution to back up DB1.
What should you include in the recommendation?
A. Azure Blob storage.
B. Azure Table storage.
C. Azure Queue Storage.
D. Azure Document DB.
Question 3
You need to recommend which statement should be used to update SalesOrder.
How should you recommend completing the statement? To answer, drag the appropriate elements to the correct locations. Each element may be once, more than once, or not at all.
You may need to drag the split bar between panes or scroll to view to view content.
Options to drag
EXPLICIT
ISOLATION
READ UNCOMMITED
ROLLBACK
SERIALIBLE
SNAPSHOT
TABLOCK
TRANSACTION
Answer area
SET TRANSACTION ISOLATION
LEVEL ———–
BEGIN TRANSACTION
UPDATE SalesOrder
…
COMMIT TRANSACTION
Question 4
What should you create in Azure to support the creation of the backups for DB1?
A. a storage account.
B. An Azure content delivery Network (CDN) endpoint.
C. A Service Bus namespace.
D. A cloud service.
Question 5(sent the printscrean)
On DB1, a developer creates a table names Table1.
What statement should you use after the table is validated? To answer, select appreciate options in the answer area.
Question 6
You need to assign security to dev_role1
How should you complete the code? To answer, drag the appropriate elements to the correct locations. Each element may be used once or more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
Elements
AUTHORIZATION
CONTROL
dev_role1
dev_schema1
OBJECT_ID
SCHEMA
SELECT
Answer Area
——————- ON ———–::[————-] TO [————
CONTROL ON SCHEMA dev_schema1 To dev_role1
Create Table
Split Range
BCP
Drop Table
Drop Partition
agreed with nohup, merging the range at the end is good housekeeping but the question does not ask for this.
1. create new table
2. switch partition
3. BCP
4. drop the new table
Create Table
Switch Partition
Merge Range
Switch partition will move the data to the new table, so no need for BCP or Select INTO.
The partition range is already as per the requirements; partition 1 contains records from 2012 and prior.
Merge Range to reset the partition numbers in AuditData table.
Create Table
Switch Partition
Merge Range
BCP
DROP Table
General Requirements for Switching Partitions
When a partition is transferred, the data is not physically moved; only the metadata about the location of the data changes.