Implementing Scale-Out Solutions with Microsoft SQL Server 2005

2. DAT309 Implementing Scale-Out Solutions with Microsoft SQL Server 2005 Don Vilen Program Manager SQL Server Microsoft Corporation – Redmond

3. Who Am I? • Program Manager on SQL Server’s Storage Engine • Working on SQL Server at Microsoft for 12+ years • Responsible for Database Mirroring and High Availability • Previously: • PM for Filestream, other features • Training for PSS Engineers on SQL Server around the world • DonV@microsoft.com

4. Agenda Technologies to Implement Scale-out Which Technology to Use? Classes of Data and Scale-out Deployment Scenarios Use Cases

5. Scaling Database Systems • This presentation is about scaling • Second 10,000 users as fast as first 10,000 • With twice as much horsepower – or less • Two ways to scale • Scale up: bigger hardware, software • Scale out: more small boxes • Scale-out has potential savings • Scale-up: redundant big server • Scale-out: 100% redundancy not required

6. Scale-Out For Everyone? • Scale-out is not the answer to every problem • Similar to dividing processing among threads • If scale is not required, solution will just be: • Unnecessarily more complex • Slower • Some scaling technologies require changing • Programs • Procedures • Best to plan this into the design ahead of time • We’ll assume you’ve addressed basic tuning • Appropriate indexes, stored procs, etc.

7. The Technologies

8. Linked Servers and Distributed Queries • Define Linked Servers and connection attributes • Use 4-part names to reference <server>.<database>.<schema>.<object> • Query optimizer will split query or DML into appropriate queries to be sent to each linked server • Each linked server processes its query and returns result set • Result sets are combined / joined • Distributed transactions for DML

9. Linked Servers and Distributed Queries Executing a Query 3. Queries run asynchronously on involved linked servers 2. Sends appropriate query / DML to linked servers, eliminating those with no relevant data 4. Data is returned by linked servers and combined 1. User issues query / DML with DPV

10. Distributed Partitioned View – DPV • Define sub-tables with appropriate constraints on the various Linked Servers • Create a UNION-ALL view SELECTing from all the sub-tables • Query optimizer will split query or DML into appropriate queries to be sent to each linked server, with dynamic elimination of uninvolved partitions • Each linked server processes its query and returns result set • Result sets are combined / joined • Distributed transactions for DML

11. Distributed Partitioned View – DPV Setting it up 1 - 999 1000 - 1999 2000 - 2999 1. Create tables with range constraints 2. Create linked servers 3. Create view with UNION ALL

12. Distributed Partitioned Views Executing a Query 1 - 999 1000 - 1999 2000 - 2999 3. Queries run asynchronously on involved linked servers 2. Sends appropriate query / DML to linked servers, eliminating those with no relevant data 4. Data is returned by linked servers and combined 1. User issues query / DML with DPV

13. Distributed Partitioned View How it Provides Scale-out 1 - 999 1000 - 1999 2000 - 2999 3000 - 3999 • Performs the work across involved nodes asynchronously • Add additional nodes as needed

14. Distributed Partitioned Views • Available since SQL Server 7.0 • SQL Server 2005 adds the asynchronous processing • Usable for read or write • Requires MSDTC for distributed transactions • Considerations for scalability • All queries and updates are affected • Response is determined by the slowest part • Locks are held for whole transaction • Not feasible across security/organization boundaries

15. Scalable Shared Database – SSD • One copy of database attached to multiple instances • New in SQL Server 2005 – Enterprise Edition only • Requires a SAN • Database itself is read-only • Refresh on a schedule • Rolling refresh possible • Sweet spot is connection by 2 to 8 instances

16. Scalable Shared Database – SSD Setting it up 1. Create database, fill with data, and detach 2. Set volumes Read Only 3. Attach the same database to multiple instances 4. Periodically attach refreshed database to instances, perhaps via SAN operations

17. Scalable Shared Database – SSD Executing a Query User queries on multiple instances use same reference data from single copy

18. Scalable Shared Database – SSD Refreshing the Data 1. Create a Clone of hot data, with recovery Hot Data Clone 2. Set Clone volume to Read Only Vn+1 Vn 4. Refresh Vn, and create Vn+2 3. For each node: Detach Instance from Vn, and attach Vn+1

19. Scalable Shared Database How it Provides Scale-out • Multiple instances access the same database files • Add additional nodes as needed

20. Setting Up SSD • Build process • Unmask SAN unit number to make volume accessible • Mount volume as read-write with DISKPART • Build or refresh one copy of the database • Attach process • Detach the database from the SQL Server instance that built it, using the DETACH command • Mark each volume as read-only by using DISKPART and dismount the disk volume from the production server • Unmask the SAN logical unit number to make the database available to each reporting server • Mount the disk volume on each reporting server by using DISKPART • Attach to one or more instances of SQL Server

21. Replication • Provides for distribution of all or subset of data • Available with various capabilities since SQL Server 7.0 • All use Publisher – Distributor – Subscriber model • All incur latency; data changes appear some time, perhaps seconds, minutes, hours, later • Snapshot Replication • Merge Replication • Transactional Replication

22. Snapshot Replication • Periodic refreshing of whole sets of data • Equivalent to BCP-out from Publisher and BCP-in to Subscriber(s) • Perhaps some rows haven’t changed at all; others have changed perhaps millions of times Publisher / Distributor Subscriber(s) Mechanism: BCP to copy whole sets of data

23. Merge Replication • Periodic exchange of changed rows in both directions • Changed rows are tracked; net changes are sent • No guarantee of transactional consistency • Can update in multiple places • Built-in conflict detection Publisher / Distributor Subscriber(s) Mechanism: Track changed rows, then exchange net changes

24. Transactional Replication • Periodically sends individual changes, maintaining transactional integrity • Changes are read from log, converted into individual T-SQL statements, and sent preserving order and transaction grouping • Guarantees transactional consistency Publisher / Distributor Subscriber(s) Mechanism: Translate each log record to T-SQL, send in same order, and with similar transactions

25. Peer-to-Peer Replication – P2P • Variety of transactional replication • New in SQL Server 2005 – Enterprise Edition only All Publish their ‘owned’ data and subscribe to others’ data

26. Peer-to-Peer Replication – P2P • Variety of transactional replication • New in SQL Server 2005 – Enterprise Edition only • Allows ring-type replication configuration • More flexible than hierarchical topologies • Allows Read and Write operations • More replication paths than bus-type • No built-in conflict detection • Each row must have an "owner"

27. Replication Topologies

28. P2P and Connectivity

29. Peer-to-Peer Replication – P2P How it Provides Scale-out • Clients work with a server with a ‘local’ copy • Add additional nodes to increase capacity • Also increases availability All Publish their ‘owned’ data and subscribe to others’ data

30. SQL Server Service Broker – SSB • Transactional messaging, asynchronous processing, and disconnected systems • Separates input from execution • Routing among instances is built in • Provides service-oriented infrastructure • Available in all SQL Server 2005 editions • Even SQL Server Express • Requires application changes / programming

31. Service Broker – SSB Setting it up 1. Enable Service Broker 2. CREATE QUEUE 3. Rather than performing all the processing, it SENDs a message 4. Message is routed to receiver who RECEIVEs it from the queue and processes it

32. Service Broker – SSB How it Provides Scale-out • Distributes the work • Processing is done elsewhere and/or later • Can specify an Activation procedure and number of instances • Can further divide the work across multiple services • Add more resources as needed

33. Query Notifications – QN • Notify my client / cache when row is changed • Notification only; client must refresh cached data • Implemented in-server using: • Indexed View-based notification technology • Service Broker for communications • Requires SQL Server 2005 – any edition • Requires "new" client stack • Used in ASP.NET "Cache Sync" 2. Data is changed on server 1. Client caches data and registers for notification Client cache 3. SSB message notifies client of a change to result set

34. Query Notifications – QN How it Provides Scale-out • Intelligent cache notifications avoid continuous polling to check for changes • Effectively “scales-out” to the clients Client cache

35. Data Dependent Routing – DDR • More architectural concept than feature • Not built in to SQL Server 2005 • Can be implemented: • By application • With SSB using built-in routing service with custom code • With middle-tier component • Can be very helpful with: • Transactional replication • Distributed Partitioned Views

36. Which Technology?

37. Agenda Technologies to Implement Scale-out Which Technology to Use? Classes of Data and Scale-out Deployment Scenarios Use Cases

38. Data Usage-Based Scale-Out • Scaling choice is based on data usage • Classify the data • Determine the database deployment topology • Determine the application topology • Choose the appropriate technology

39. Classifying Data • Four main data classifications • Resource data: customers • Traditional "data processing" data • Reference data: catalogs, price lists • Lookup tables • Service interaction data • Original order forms • Activity data • Pick lists • Shopping carts

40. Traditional Deployment • Single-tier or client-server • All data together in database • All data treated the same • All data updated directly • Service interaction is part of transaction

41. Deployment in Tiers • Multi-tiers • Reference Data can be cached and replicated • Activity and Service Interaction coupled • Reside in application servers • Direct interaction between activity, service, and resource • Multi-operation transaction

42. Deployment with Services • Some data separated into services • Activity data internal but asynchronous • Not tightly coupled to resource data • Managed through messaging • Service interactions can be disconnected • Managed through messaging • Some data replicated • Traditional transactional replication • Smart, active cache • Only resources need synchronous processing

43. Scaling and Data Types • Techniques differ with data • Reference data • Scalable Shared Database • Snapshot replication • Query notifications • Activity data • Dynamic partitioning • Data dependent routing • Service interaction data • Inherently asynchronous • Resource data • Static partitioning • Peer-to-peer or bi-directional replication

44. Storage and Data Types • Techniques differ with data • Reference data • Master copy in central database • Replicas online • Activity data • Keep copies for activity tracking, trending, mining • Service interaction data • Keep EXACT copies of request and response • For retry and recovery • Sometimes a legal requirement • Resource data • Divide processes • Updates can be async with other categories

45. Use Cases • Distributed Partitioned Views • “SQL Server Megaservers: Scalability, Availability, Manageability” (Jim Gray and Richard Waymire)http://www.microsoft.com/technet/prodtechnol/sql/2000/plan/ssmsam.mspx • Scalable Shared Database • Corporate Phone Book, Product Catalog • Peer-to-Peer Replication • “Inside Microsoft.com: Managing SQL Server 2005 Peer-to-Peer Replication” (David Lindquist)http://www.microsoft.com/technet/technetmag/issues/2006/07/InsideMSFT/default.aspx • Service Broker • “Inside MSDN: Building the MSDN Content Aggregation System”(John Mollman)http://msdn.microsoft.com/msdnmag/issues/06/07/InsideMSDN/default.aspx • Query Notifications • Cache information about server status, Windows mobile server update

46. Summary • Scale-out more achievable with SQL Server 2005 • Multiple features geared toward scale-out • Service-oriented, replicated, or distributed • Which technologies to use depends on: • Deployment scenarios • Whether app or process can be changed • Data classes used • Scale-out technologies can be combined • Data Dependent Routing useful adjunct to all

47. Resources “Scaling Out SQL Server 2005” Roger Wolter http://msdn2.microsoft.com/en-us/library/aa479364.aspx “Planning, Implementing, and Administering Scaleout Solutions with SQL Server 2005” (Bob Beauchemin) http://www.microsoft.com/technet/prodtechnol/sql/2005/implementingscaleout.mspx Bob Beauchemin's Blog: http://www.SQLskills.com/blogs/bobb “Architecting Service Broker Applications” (Roger Wolter) http://msdn2.microsoft.com/en-us/library/aa964144.aspx “Achieving Scalability and Availability with Peer-to-Peer Transactional Replication” (Michael Blythe) http://www.microsoft.com/technet/prodtechnol/sql/2005/p2ptranrepl.mspx Scalable Shared Databases Are Supported by SQL Server 2005 http://support.microsoft.com/default.aspx?scid=kb;en-us;910378 “Service-Oriented Data Architecture: App-Server:Lite?” (David Campbell) http://research.microsoft.com/research/pubs/view.aspx?tr_id=983 “Data on the Outside vs. Data on the Inside: An Examination of the Impact of Service Oriented Architectures on Data” (Pat Helland) http://msdn2.microsoft.com/en-us/library/ms954587.aspx

48. Q&A

49. Resources Technical Communities, Webcasts, Blogs, Chats & User Groups http://www.microsoft.com/communities/default.mspx Microsoft Learning and Certification http://www.microsoft.com/learning/default.mspx Microsoft Developer Network (MSDN) & TechNet http://microsoft.com/msdn http://microsoft.com/technet Trial Software and Virtual Labs http://www.microsoft.com/technet/downloads/trials/default.mspx

50. SQL Down Under Code Camp • Biggest SQL Server community event in Australia • 14 SQL Server sessions over two days • Special guest presentation from Kevin Kline • President of PASS, Director of Technology – Quest Software • When? • Weekend of the 13th and 14th of October • Where? • Charles Sturt University, Wagga Wagga • What does it cost? • This event is FREE • http://www.sqldownunder.com