微软SQL服务器Always-On Technologies: 针对局部损害和人为错误灾难的恢复策略 2/4/2018 8:45 PM 微软SQL服务器Always-On Technologies: 针对局部损害和人为错误灾难的恢复策略 吴家震 Senior Test Manager Microsoft SQL Server © 2007 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

数据损坏, 怎么办？ 让我们来考虑补救过程的每一步 首要问题：什么损坏了，谁干的，什么时候？ 您首先做什么? (多选题) 如果是你：你的简历准备好了吗？ 如果是别人做的并将情况告诉你：事态好一点 （他们的简历准备好了吗？） 不知道任何情况：最糟糕 您首先做什么? (多选题) Take the table offline Take the database offline Take the server offline 关掉所有东西，回家躲着 言归正传：这是个很复杂的问题，需要事先准备，临时抱佛 脚是不行的

案例分析 Simple schema：客户(Customers)从销售人员(Employees) 购买产品(Products)，销售记录存在Sales table Sales references Customers; Customers cannot be dropped* Sales references Employees; Employees cannot be dropped* Sales references Products; Products cannot be dropped* *Assuming that primary/foreign key references have been created and are enforced in the database @$#!& ?^@ Customers Sales CustomerID DROP TABLE Sales CustomerID SalesPersonID ProductID Employees Products EmployeeID ProductID

演示 Recovering a Dropped Table: The good, the bad, and the ugly! 2/4/2018 8:45 PM 演示 Recovering a Dropped Table: The good, the bad, and the ugly! © 2007 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

修复损坏的数据 当您知道什么被损坏了... 限制损坏程度 哪个更重要: 数据损失还是停机时间，哪是折衷点 恢复损坏的数据 恢复整个数据库到早期的时间点，用户可以很快恢复使用 但会发现一些数据丢失 让production环境正常运行，用另外的环境做深入分析， 手工合并数据… 把offline数据部件恢复online 防止未来发生同样事件

限制损坏程度(1 of 3) Restrict Access to the Database ALTER DATABASE dbname SET RESTRICTED_USER WITH termination Only database owners and administrators can access the database Termination options allow you to kick users out of the database with these options: ROLLBACK IMMEDIATE ROLLBACK AFTER n [SECONDS] NO_WAIT Take the Database offline (note: you cannot access the database in any way – if the database is offline) ALTER DATABASE dbname SET OFFLINE

限制损坏程度(2 of 3) Take the table offline 没有直接方式 Can take the file offline ALTER DATABASE dbname MODIFY FILE (NAME = N'datafile', OFFLINE) Taking a file offline takes the filegroup of which it’s a member offline Only works for secondary, non-primary DATA files (note: the primary filegroup (.mdf) and the transaction log (.ldf) cannot be taken offline without taking the entire database offline) Msg 5077, Level 16, State 2, Line 1 Cannot change the state of non-data files or files in the primary filegroup.

2/4/2018 8:45 PM 演示 Isolating a Critical Object for Better Control While Keeping the Object Online © 2007 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

部份数据库可及性 功能只在微软SQL 服务器2005企业版(Enterprise, Enterprise Evaluation, Developer) 只能用于 “partitioned” databases 如果数据是在不同的filegroups, 那么只有受影响数据的 filegroup 将是不能进入的。(Note: foreign key和相关数据 也会受影响) 如果根据partitioning scheme将table partition在不同的 filegroups，只有受到影响filegroup的partition是不能进入 的。(Note: Partitioned views will generate an error if ANY table on which the view is based is unavailable.)

Horizontal Partitioning (PVs or PTs) Sales2004_01 Sales Sales2004_02 874 Million Rows All Sales Since January 2003 Current Sales (INSERTs) often blocked by DSS queries. Index Rebuilds take quite a bit of time Weekly FULL Database Backups are backing up the same sales (2003-2005) repeatedly Sales2004_03 Sales2004 Sales2004_04 Table or View? . Sales2004_12 Sales2005_01 Sales2005_02 Sales2006_01 Sales2005_03 Sales2005 Sales2006_02 Sales2005_04 Table or View? Sales2006_03 Sales2006 . Sales2006_04 Table or View? Sales2005_12 . Sales2006_12 PVs require constraints for partition management Only the undamaged tables are accessible when a table of the PV is inaccessible PTs require new features: partition function and partition scheme for partition management PT is accessible when a partition of the PT is inaccessible

2/4/2018 8:45 PM 演示 Partitioning Large Objects for Fine Grained Control While Keeping the Object Online © 2007 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

限制损坏程度(3 of 3) Take the partition offline—和 taking the table offline 相同：没有直接方式 Take the file offline: ALTER DATABASE dbname MODIFY FILE (NAME = N'datafile', OFFLINE) 当file offline之后，其中的 partitions 也会offline. Taking file offline 是个很短的过程，所有用户连接都会被终止．但其他未被损坏的 数据会back online. 当试图使用offline partitions时会得到以下错误信息： Msg 679, Level 16, State 1, Line 2 One of the partitions of index 'SalesPK' for table 'dbo.Sales'(partition ID 72057594039697408) resides on a filegroup that cannot be accessed because it is offline, restoring, or defunct. This may limit the query result.

演示 Taking Part of Your Database Offline 2/4/2018 8:45 PM © 2007 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

2/4/2018 8:45 PM 演示 Recovering a Partially Damaged　Database While Keeping the　Undamaged Data Online © 2007 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

Database Restore/Recovery 用transaction logs可将整个数据库恢复到任一时间 点 用transaction logs and RESTORE … PARTIAL可 将部分数据库恢复到任一时间点 要求: Database recovery model = Full or bulk_logged* (如果是 bulk_logged recovery model，要恢复的时间点 和最后的last log backup之间不能有bulk operations) If PARTIAL, the Primary filegroup must be restored in addition to the secondary data file that is damaged

Recovery 将整个数据库恢复到某一时间点 保持production不变，把整个数据库恢复到另一个地点，人 工合并数据... 数据一贯性被保证 损伤发生之后的变动会丢失 保持production不变，把整个数据库恢复到另一个地点，人 工合并数据... 不会丢失灾难之后发生的变动 但那些变动还准确吗？ 人工合并数据可能会很复杂 保持production不变，只将受损坏的部分数据库恢复到另一 个地点，人工合并数据... 如果数据库 是partitioned, 无须restore/recover 整个数据库

修复损坏的数据 当您不知道什么被损坏了... 限制损坏程度 调查何时何处损坏发生了，由谁造成的 Determine what should be taken offline? 调查何时何处损坏发生了，由谁造成的 利用现有的Database Snapshots Restore to an alternate location and move forward with STOPAT (as shown) Recover the damaged data from the Snapshot OR the rolled forward version using STOPAT Restore user access to the data and/or bring offline components back online 防止未来发生同样事件

演示 Recovering a Dropped Table from a Database Snapshot 2/4/2018 8:45 PM 演示 Recovering a Dropped Table from a Database Snapshot © 2007 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

预防措施 最重要！帮助降低数据损失和downtime到最低. (但很多 时候是事后才认识到预防的重要) 预防技巧: Improve security Metadata access limited No direct access to production tables (use sps/views/fns) Limited sysadmin control and limited DBO rights (use SCHEMA controllers not DBO/db_owner) Auditing via Profiler and/or Event Notifications Responding via Agent Alerts (Errors, PerfMon, WMI) SCHEMABOUND Views (SQL Server 2000) DDL Triggers (SQL Server 2005)

DDL Triggers 用以在server-level和database-level来监查和防止 可能的错误DDL statements (Data Definition Language) 使用: 防止不小心的数据删除 (i.e., trapping and rolling back an attempt to drop a table) 防止不小心的schema changes Using specific commands DROP_TABLE Using groups DDL_PROCEDURE_EVENTS covers CREATE, ALTER & DROP PROCEDURE

DDL Trigger的好处 Trigger是statement/transaction的一部分，条件符 合时自动激活 Trigger可将statement/transaction ROLLBACK Trigger可执行Transact-SQL和CLR Data related to the event: can be trapped, even if the trigger causes the event to rollback Capture EVENTDATA(), Rollback the transaction and then insert eventdata into an audit table

Creating a DDL Trigger CREATE TRIGGER trigger_name ON { ALL SERVER | DATABASE } [ WITH [ ENCRYPTION ] [, EXECUTE AS clause]] { FOR | AFTER } { event_type | event_group } [ ,...n ] AS { sql_statement [;] [ ...n ] | EXTERNAL NAME < method_specifier > [;]} <method_specifier> ::= assembly_name.class_name.method_name For event_type, see BOL Topic  DDL Events for Use with DDL Triggers For event_group, see BOL Topic  Event Groups for Use with DDL Triggers

Preventing ALL DDL CREATE TRIGGER PreventAllDDL ON DATABASE WITH ENCRYPTION FOR DDL_DATABASE_LEVEL_EVENTS AS … RAISERROR ('DDL Operations are prohibited on this production database. Please contact ITOperations for proper policies and change control procedures.', 16, -1) ROLLBACK All Database level DDL events are rolled back and an error is returned Would be better to know about the attempt? Yes!

演示 Preventing a Table from Being Dropped 2/4/2018 8:45 PM © 2007 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

Event Notifications 可用于对事件的监查和非同步处理 使用: Complex and detailed secondary processing that does not have to be part of the original transaction Complex auditing and further notifications handled by a central server Works like DDL Trigger, using event groups (like DDL_PROCEDURE_EVENTS) and event types (like DROP_TABLE), but there are additional options such as some Trace Events (like SP_RECOMPILE or DATABASE_MIRRORING_STATE_CHANGE) BOL Topic  Trace Event Groups for Use with Event Notifications

Benefits of an Event Notification Transaction which caused the event notification to occur is not negatively impacted by asynchronous secondary event Statement/transaction which caused the event notification to occur cannot be rolled back (you could program a reverse operation but should consider a DDL Trigger instead) Trigger can execute Transact-SQL or CLR Data related to the event: can be trapped, even if the trigger causes the event to rollback Capture EVENTDATA(), Rollback the transaction and then insert eventdata into an audit table

Alerts SQL Server Performance Monitor Conditions 使用简单: 可针对SQL Server PerfMon情况设立alert， response可以是notifications或job. 例如，发邮件通知 DBA或take the database offline等等. ANY Performance Monitor Condition Use PerfMon Alerts to generate a cmd script that uses SQLCMD to raise a user-defined error to SQL Server Use SQL Agent alerts on the user-defined error and again—set up an appropriate “response” to the error WMI Events BOL Topic  WMI Provider for Server Events Classes and Properties

总结 制订一个灾难恢复计划 验证您的灾难恢复计划 最后: 希望你永远没必要用到它！ 让最资深的人员来设计和实施 记录整个过程, 包括code/script 估计施行需要时间 对付各种意外的方案 验证您的灾难恢复计划 让资深的人员来测试它 及时更新和测试 Restore backup以确认没有错误 最后: 希望你永远没必要用到它！

2/4/2018 8:45 PM Q&A 答惑解疑 © 2007 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

参考资源 Technical Chats and Webcasts Microsoft Learning and Certification http://www.microsoft.com/communities/chats/default.mspx http://www.microsoft.com/usa/webcasts/default.asp Microsoft Learning and Certification http://www.microsoft.com/learning/default.mspx MSDN & TechNet http://microsoft.com/msdn http://microsoft.com/technet Virtual Labs http://www.microsoft.com/technet/traincert/virtuallab/rms.mspx Newsgroups http://communities2.microsoft.com/communities/newsgroups/en-us/default.aspx Technical Community Sites http://www.microsoft.com/communities/default.mspx User Groups http://www.microsoft.com/communities/usergroups/default.mspx

参考资源 Demo Scripts, Resource Links, Additional Materials http://www.SQLskills.com http://www.SQLskills.com, Past Events SQLskills Immersion Events http://www.SQLskills.com, Events, Immersion Events SQL Server Always-On Technologies http://www.microsoft.com/sql/AlwaysOn SQL Server High Availability Technologies http://www.microsoft.com/sql/technologies/highavailability/ SQL Server VLDB Case Studies and Other Information http://www.microsoft.com/sql/bigdata Microsoft SQL Server Developer Center on MSDN http://msdn.microsoft.com/sql/ Microsoft SQL Server TechCenter on TechNet http://www.microsoft.com/communities/usergroups/default.mspx

您的意见与建议对我们非常重要。请您填写反馈表。 感谢您参与此会场！ 您的意见与建议对我们非常重要。请您填写反馈表。

2/4/2018 8:45 PM © 2007 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.