Module 19 Managing Multiple Servers

1. Module 19 Managing Multiple Servers

2. Module Overview • Working with Multiple Servers • Virtualizing SQL Server • Deploying and Upgrading Data-Tier Applications

3. Lesson 1: Working with Multiple Servers • Overview of Central Management Servers • Executing Multi-server Queries • Demonstration 1A: Executing Multi-server Queries

4. Overview of Central Management Servers Central Management Servers are used to hold details of servers within an organization and to organize the servers within groups. • Configuration requires: • Define CMS server (typically only one server) • Create server groups • Register servers • Group actions permitted: • Execute T-SQL statements • Evaluate and Import Policy-Based Management policies • Browse Object Explorer • Usage requires Windows Authentication and membership of ServerGroupReaderRole

5. Executing Multi-server Queries Different permissions might apply on each server instance ! • T-SQL queries can be executed on multiple servers at the same time • Results can be merged • Additional columns are provided for source server and login information

6. Demonstration 1A: Executing Multi-server Queries In this demonstration, you will see: • How to create a Central Management Server • How to execute multi-server queries

7. Lesson 2: Virtualizing SQL Server • Discussion: Advantages and Disadvantages of Virtualizing SQL Server • Overview of SQL Server Virtualization • Common Virtualization Scenarios • Considerations for Virtualizing SQL Server • Overview of System Center Virtual Machine Manager

8. Discussion: Advantages and Disadvantages of Virtualizing SQL Server • Do you use virtualization of SQL Server in your environment? • If you are using virtualization, why are you using it? • What are the main advantages provided by virtualization? • What are the main concerns with virtualizing SQL Server?

9. Overview of SQL Server Virtualization Child Partition Windows Server SQL Server Child Partition Windows Server SQL Server Root Partition Windows Server Hyper-V Hypervisor Hardware

10. Common Virtualization Scenarios

11. Considerations for Virtualizing SQL Server • Use POC to ensure that virtualization meet your needs • Provide appropriate I/O resources • Use pass-through disks • Apply best practice as for non-virtualized systems • Use SQLIO for pre-deployment testing • Provide appropriate CPU resources • Provide additional CPU for network intensive systems • Avoid the use of emulated devices • Install integration components for Hyper-V that provide synthetic devices

12. Overview of System CenterVirtual Machine Manager System Center Virtual Machine Manager (SCVMM) is a tool that allows you to manage resource allocations between multiple virtual machines. • Component of the System Center suite • Often used in conjunction with System Center Operations Manager (SCOM) • Allows balancing resources across multiple virtual machines • Avoids much of the need to balance resources within each machine

13. Lesson 3: Deploying and Upgrading Data-Tier Applications • Data-tier Application Overview • Deploying Data-tier Applications • Upgrading Data-tier Applications • Extracting Data-tier Applications • Demonstration 3A: Working with Data-tier Applications

14. Data-tier Application Overview • Unit of deployment for T-SQL applications • Targeted at departmental applications • Not intended for large line of business applications • Wraps deployment intent as policies • Simplifies deployment • Install • Uninstall • Upgrade Data-tier Application Component Schema LOGICAL Tables Views Constraints Procedures UDFs PHYSICAL Users Logins Indexes DEPLOYMENT PROFILE Requirements Policies

15. Deploying Data-tier Applications • Wizard-based deployment • Requires target database name • Checks server-selection policy • Creates database and objects • Default database settings are used • Recovery model from the model database • Review and test .dacpac files beforeproduction deployment

16. Upgrading Data-tier Applications • Wizard-based upgrade process • Used to change schema and properties of deployed applications • Not T-SQL script-based • New database created with new schema • Application name must match currently deployed file • Data is migrated • Original database renamed and set to read-only • New replacement database created • Space usage must be considered • Data migration time must be considered • Process can be managed via PowerShell

17. Extracting Data-tier Applications • Data-tier applications can be extracted from many existing databases • Not all databases can be extracted • Reverse engineering process: • Extract through SSMS • Create Visual Studio Project • Develop new application version • Compile and build • Upgrade application to new version

18. Demonstration 3A: Working with Data-tier Applications • In this demonstration, you will see: • How to deploy a data-tier application • How to upgrade a data-tier application

19. Lab 19: Managing Multiple Servers • Exercise 1: Configure CMS and execute multi-server queries • Exercise 2: Deploy a data-tier application • Exercise 3: Register and extract a data-tier application • Challenge Exercise 4: Upgrade a data-tier application (Only if time permits) Logon information Estimated time: 45minutes

20. Lab Scenario You have configured a Management Data Warehouse. You need to configure a solution that allows you to easily manage multiple SQL Server instances. You have noticed that on each computer that you connect to your SQL Server network, a different set of servers has been configured. You decide to configure a central management server to provide a consistent list of server groups. Your developers have begun using Data-Tier applications for some of their development. You need to deploy one of these applications to the new server, register an existing database as a Data-Tier application and, if time permits, upgrade a Data-Tier application.

21. Lab Review • At what stage is the server selection policy checked when deploying a data-tier application? • Can multi-server queries be used with servers that you need to connect to using SQL Server authentication?

22. Module Review and Takeaways • Review Questions • Best Practices