1 / 96

Introducing SQL Server

Introducing SQL Server. Introduction Adoption TCO TCA Performance Security HA Enterprise Data Management Business Intelligence Clients. Agenda. Adoption!. Why Migrate to or adopt SQL Server Strength of SQL server Range of products that make up SQL server Integration

lloyd
Télécharger la présentation

Introducing SQL Server

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Introducing SQL Server

  2. Introduction • Adoption • TCO • TCA • Performance • Security • HA • Enterprise Data Management • Business Intelligence • Clients Agenda

  3. Adoption! • Why Migrate to or adopt SQL Server • Strength of SQL server • Range of products that make up SQL server • Integration • Scalability and Performance • TCO • Security • Availability

  4. TCO and TCA

  5. The Database Market 2003 2008 Apps Apps Dev Tools DBMS CMS/Collab UX/Portal App Server SOA/BPM Business Intelligence DBMS OS (Linux) Hardware

  6. The Database MarketUnit Share, Overall and Enterprise Source:

  7. Base Product $40K $25K $25K 10g DB2

  8. Multi-core Business Intelligence High Availability Manageability Base Product $300+K $200+K DB2 OLAP: $35k DB2 Warehouse: $75k Cubeviews: $9.5k BI Bundle $40k RAC: $20k Recovery Expert: $10k Tuning Pack: 3k Diagnostic Pack: 3k Partitioning: 10k Performance Expert: 10k $25K 10g DB2

  9. Oracle 11g Base Product $ 25K $ 40K

  10. Tuning: $3K Diagnostics: $3K Partitioning: $10K (included) Manageability Base Product $ 25K $ 40K $ 56K

  11. Advanced Security: $10K Security (included) Manageability Base Product $ 25K $ 56K $ 66K

  12. Remote Mirroring: $66K High Availability Security (included) Manageability Base Product $ 25K $ 66K $ 132K

  13. OLAP: $20K Data Mining: $20K BI EE Suite Plus: $255K Data Integrator (ETL): $60K Business Intelligence High Availability Security (included) Manageability Base Product $ 25K $ 487K $ 132K

  14. Four core pricing + $487K Multi-core Business Intelligence High Availability Security (included) Manageability Total Cost Base Product $ 974K $ 25K $ 487K

  15. Microsoft SQL Server and Oracle: A comparative study on Total Cost of Administration (TCA) “Microsoft SQL Server and Oracle: A Comparative study on Total Cost of Administration” by http://www.alinean.com

  16. Report - Highlights SQL Serve DBAs manage over 3 times as many databases as Oracle DBAs Number of Databases Number of Users SQL Server DBAs support over 20% more end-users than Oracle DBAs Annual cost of administering an Oracle database is over 3 ½ times of that for SQL Server Cost

  17. Survey:Participant Profile Average # of Employee = 34,400 • Aerospace and Defense • Banking and Finance • Construction and Engineering • Education • Healthcare • High Technology • Insurance • Media & Entertainment • Manufacturing • Retail • Telecommunication • Transportation • Professional Services A higher percentage of SQL Server (66%) was being used for mission-critical applications than Oracle (63%)

  18. Comparison- of Database Administration Tasks On the average DBAs spent half as much time performing routine database operations on SQL Server as they did on Oracle Databases

  19. Performance

  20. Scalability and Performance(October 2008) • TPC-E (New July 2007) • SQL2005 8st place, 2008 all the rest of the top 10 (18 results posted ass SQL) • TCP-C , 7th October 2008 • Performance (SQL2005 10th place submitted Nov 2005, DB2 9.5 submitted June 2008) • Price Performance SQL 2005 ( Oracle 11g on Windows 1st and 2nd place) • 4th place, $0.84, submitted 27th March 2007 • 6th place, $0.91, submitted 3rd Sept 2007 • 7th place, $0.98, submitted 30th June 2006 • 8th place, $0.99, submitted 26th Sept 2005 • 9th place, $1.29, submitted 2nd Sept 2006 • 10th place, $1.33, submitted 6th August 2007 • SQL 2000 • 10th place, $1.4, submitted 24th Feb 2005 • TCP-H , 7th March 2008 • Performance • 8 out of 10 for 100Gb, 2 out of  Top 10 for 300Gb,23 out of 10 in 1000Gb,  1 out of 10 in 3000Gb, 1 out of 10 in 10000Gb • Price and Performance • 6 out of 10 for 100Gb, 3 out of 10 for 300Gb, 6 out of 10 in 1000Gb, 3 out of top 10 for 3000Gb, 1 out of 10 for 10,000Gb • Very Large Databases (World Top 10) • Information from WinterCorp for 2005 Winners • Database Size • DW, 1 SQL 4 Oracle • OLTP, 3 SQL 3 Oracle • Number of Rows • OLTP, 3 SQL 1 Oracle

  21. NUMA • NUMA – Non-Uniformed Memory Access • Minimize/eliminate front-bus contention to surpass scalability limits of SMP architecture • Performance penalty for accessing foreign node memory • Application needs to be NUMA-aware to take advantage the node-locality design Foreign Memory Access 4x local Local Memory Access

  22. NUMA • SQL Server 2005 – better NUMA support • Native support for hardware/software NUMA • Connection Affinity through TCP/IP • Workload Balancing on Scheduler Level within one Node only • Huge scalability improvements on large multi-processor box (> 8 CPUs) • Soft-NUMA • SQL Scheduler and SQL Network Interface aware • Reduce I/O and Lazy Writer bottlenecks • I/O thread and Lazy Writer per NUMA node

  23. SQL Server performance historyItanium changed the picture 1,231,433 Scalability Improvements: >100x since 1996 786,646 $89.62 tpmC price/tpmC $54.45 $27.98 234,325 $20.13 $16.80 141,138 69,901 $23.84 40,697 20,433 6,750 14,900 48,767 $11.59 $6.49 $4.82 $8.46 2/28/1997 12/31/1999 12/1/1996 12/29/1998 8/26/2000 11/15/2001 3/10/2002 5/2/2003 10/23/2003 11/28/2005 Results from www.tpc.org, Nov 11 2005 – Windows Server, Microsoft SQL Server TPC-C Benchmarks

  24. PerformanceData compression • Storage cost savings • Shrink large tables such as DW fact tables • Improves query performance • Results in less I/O but with some CPU cost • Higher buffer hit rates • Achieves a 2x to 7x compression ratio • Row-level, variable-width • Page-level dictionary • Differential encoding between rows • Independent of other features • Works for data and indexes • ISV Benchmark Results 50-80%

  25. World Record scale set by SQL Server 2008 on 4-Socket Industry Standard Blade servers in 3-Tier SAP SD Standard Application Benchmark Test (3) • Benchmark Results • High Concurrency – 34,000 SAP SD standard benchmark users • For more information visit: http://www.microsoft.com/sqlserver/2008/en/us/benchmarks.aspx Concurrent Users • SQL Server Remains a Low TCO Platform for SAP • A study covering 68 SAP customers is unveiled today by an independent firm, Wipro Technologies, that shows how migration to SQL Server for SAP/ERP pays big dividends: • ‘reduce unplanned downtime by over 20%’ • ‘cut IT labor costs by nearly 25%’ • ‘cut ongoing software support costs up to 86%’ • Learn more at: www.wipro.com/datadocs/whitepaper/TCOStudyonMicrosoftSQL.pdf HP BL680C Blade Server 4-Processors Quad-Core Intel® Xeon® 7300 series processor (1) (2) (3) (1) Certification Number 2003039: SAP SD standard R/3 Enterprise 4.70 application benchmark in 3-Tier configuration certified on July 14, 2003 with Number of benchmark users & comp.: 11,200 SD (Sales & Distribution) with an average dialog response time: 1.90 seconds running  Windows 2003 Enterprise Edition SQL Server 2000 on RDBMS database server. Configuration: RDBMS server: HP ProLiant Model DL760 G2, 8-way SMP, Intel Xeon MP, 2.8 GHz, 2 MB L3 cache, 8 GB main memory. For more details, see http://www.sap.com/benchmark (2) Certification Number 2005030: SAP SD standard R/3 Enterprise 4.70 application benchmark in 3-Tier configuration certified on June 27, 2005 with Number of benchmark users & comp.: 18,000 SD (Sales & Distribution)  with average dialog response time: 1.87 seconds running Windows Server 2003 Enterprise Edition (64-bit) and SQL Server 2005 (64-bit) on RDBMS server. Configuration: Database server: HP ProLiant DL585, 4-way SMP, Dual-core AMD OpteronprocessorModel 875 (2.2 GHz), 128 KB L1 cache, 2 MB L2 cache, 32 GB main memory. For more details, see http://www.sap.com/benchmark (3) Certification Number 2008003: SAP SD standard SAP ERP 6.0 (2005)  application benchmark in 3-Tier configuration certified on 02/26/08 with Number of benchmark users & comp.: 34,000 SD (Sales & Distribution) with an average dialog response time: 1.99 seconds  running  Windows Server 2008 Enterprise Edition (64-Bit)  and SQL Server 2008 (64-bit) on RDBMS database server. Hardware configuration of RDBMS server: HP ProLiant BL680c G5, 4 socket/16 core/16 thread Quad-Core Intel Xeon E7340 / 2.40GHz,  64GB RAM. For more details, see http://www.sap.com/benchmark

  26. TB+ CustomersData Warehousing w/ Relational Query 5 TB Retail Data Warehouse on HP Superdome Uses SQL Server OLAP, SSIS, SSRS 5.3 TB Credit Card DW, 5 Mil card holders 300 users, OLAP, SSIS, SSRS, Office BSM 4 TB Consumer Packaged Goods (CPG) DW 350 users, complex relational query, SSIS & SSRS 2 TB DW, originally migrated from Informix 300 users, complex query, OLAP & Data Mining 2 TB of Clinical DW, 50 TB storage, 90 OLAP Marts US Dept of Veterans Affairs, 1200 facilities 2 TB Healthcare DW, HMC is part of Wellpoint Inc. Complex ETL, 800 SSIS packages, OLAP, RS

  27. TB+ CustomersGeneral VLDB & DW/BI Workloads 3 TB document management system, Sybase move State of Alaska Dept of Revenue, Permanent Fund 1 TB custom document management system (VS.Net) State of Washington Archive Department, 600 users 1 TB Real-time store reporting system (SSRS) Data loaded continually (MSMQ) and daily (SSIS) 2.8 TB SAP Business Warehouse system In the process of migrating SAP R/3 OLTP to SS2005 1.5 TB BASEL II Compliance DW, complex SSIS Nationwide Building Society, UK 1 TB Staging Data Warehouse at Telecom Denmark CUBUS is massive BI system with up to 10,000 users

  28. TB+ CustomersData Warehousing w/ Relational Query 1 TB gaming data warehouse, 24 casino Heavy daily loads, relational query 1 TB data warehouse for CRM support Runs core systems SQL Server, BASELL II on SS2005 6 TB retail data warehouse from 700 stores, OLAP, RS 15 TB total across multiple instances 2 TB normalized DW, 250 concurrent BI users Nightly transaction loads: 900 TPS sustained 5 TB retail DW, 10 TB storage, data from 1000 stores Largest retailer in African continent, OLAP, RS, DTS 3 TB relational DW, DB2 migration Heavy Complex Relational Query

  29. SQL Server 2000 TB+ CustomersGeneral VLDB & DW/BI Workloads 20 TB European digital payment archive system Unisys Payments Services Archive 13 TB Geospatial DW, to reach 25 TB 4 TB data mart, 2500 field offices, 55 TB storage Multiple 1 TB instances, credit scoring analysis Uses complex OLAP & SQL Server ETL Used by 13,000 agents in 220 offices Built portal with Visual Studio .NET, OLAP 700 restaurants, 30,000 users, 700+ concurrent OLTP/LOB reporting on inventory & sales data Reporting on 1.6 TB of data, 35K+ Tx/Min 325 concurrent connections

  30. TB+ CustomersGeneral VLDB & DW/BI Workloads 20 TB European digital payment archive system Unisys Payments Services Archive 13 TB Geospatial DW, to reach 25 TB 4 TB data mart, 2500 field offices, 55 TB storage 1.5 TB DW, plus multiple OLAP data marts Data feeds from JD Edwards ERP system 1 TB DW, Informix migration Heavy concurrent usage on OLAP 1.7 TB DW, 2900 DW users ramping up to 5000 Runs on HP Superdome 6 TB Call Detail Record (CDR) DW, OLAP usage 20-way HP Superdome

  31. Security

  32. SQL Server 2005 Security InitiativesFocus on security, privacy, and tools • Encryption and decryption of data with key management • Advanced auditing, authentication, and authorization • Tracing, Events, DDL, custom triggers (Data dictionary is exposed ) • Row level Security • This is really Fined Grained Access Control • A great practical guide to how to do this. • http://www.microsoft.com/technet/prodtechnol/sql/2005/multisec.mspx • Reduction in surface area • Deployment improvements • Integration with Microsoft Update services • Common Criteria • Certification targeted at EAL4+ • Security Tools • Continued MBSA investments • Best Practices Analyzer

  33. Simplify ComplianceEnterprise security policies • Define security policies • Define audit, access policies by using Policy Management Framework • Authentication standard • Password policies • Define surface area configurations as policies • Apply across the enterprise • Using Configuration Servers • Helps to ensure enterprise-wide compliance

  34. Secure Data AccessTransparent data encryption • Encrypt data and log files • Protect backup files and detached database files • Transparent to application • No application modifications required to take advantage of encryption • Secure with Database Encryption Key (DEK); DEK encrypted with • Server certificate protected by operating system encryption • Can use a Hardware Security Module (HSM) to store keys • Use encrypted data in another server • Import DEK to new server • Attach encrypted database files • Restore an encrypted backup SQL Server 2008 DEK Encrypted Data Page Client Application

  35. SQL Server Encryption   Good Scenario: • Encrypting secrets during login • Using asymmetric keys to generate session keys • Using symmetric keys for data encryption (fast) • Using SQL Server certificates from trusted sources • Encrypting data as required by law (Not all data! speed, and flexibility of access) Bad Scenario: • Encrypting all network traffic inside an organization • Using asymmetric keys for data encryption (slow) • Using symmetric keys for main key distribution mechanism (same key) • Using SQL Server as a certificate server • Encrypting all data (SLOW, and data can't be used for indexes and joins, eg order by would not work)

  36. low-priv low-priv Execute As and Encryption Credit Card # Credit View Has access to view Low-priv needs access to both keys Credit View Better to user a function to decrypt and give no keys to user Has access to view Credit Card # Decrypt Helper EXECUTE AS DBO Low-priv has no access to keys

  37. SQL Security Benefits from SDL ESG findings are based on a three year compilation of Common Vulnerabilities and Exposures (CVE) data from the National Vulnerability Database to compare security vulnerabilities between commercial database offerings from Microsoft, Oracle and MySQL. The full report, “Microsoft SQL Server Runs the Security Table”, can be found on the campaign page www.microsoft.com/bigdata ESG considers Microsoft, with proper execution, to be years ahead of Oracle and MySQL in producing secure and reliable database products Source: Enterprise Strategy Group, Microsoft SQL Server Runs the Security Table, November 2006

  38. Oracle Delivers First Monthly Patch Roll Up Microsoft has an automatic way of developing bulletins. They're fairly open to security vulnerabilities and addressing them. Oracle will have to do the same thing. I think it's the beginning of more to come. It's the first step in an evolution of how vendors should be managing this stuff. Oracle plans to patch peck of flaws Microsoft has traditionally been a big target, and they've suffered publicly because of that… but Microsoft has adopted better internal processes to address the problem, and they've now advanced past the rest of the market in terms of their ability to respond to new issues. Oracle releases delayed security patches Many of the holes were discovered in January by security specialist David Litchfield of Next Generation Security Software Ltd. in Surrey, England, who has criticized Oracle for not releasing the patches sooner. They were ready for release more than two months ago, according to Litchfield, but Oracle delayed their release while it prepared a new system for releasing security patches. Oracle joins the monthly patch bandwagon Oracle is following Microsoft's lead in adopting a monthly patch cycle starting at the end of this month. Like Microsoft before it, Oracle reckons a monthly patch schedule is easier for its customers. Oracle Security in the Press

  39. “Oracle is years behind Microsoft and other companies on security," said Cesar Cerrudo, CEO at information security services company Argeniss in Argentina. "I think Oracle is an amateur when it comes to security." “Serious unpatched security flaws exist in certain Oracle products, according to a German security researcher who said the software maker has not fixed the bugs despite knowing about them for two years. “ Oracle/SQL Server Security in the Press “Experts are calling on [Oracle] to improve the mechanism used to secure passwords… Researchers say they have found a way to recover the plain text password from even very strong, well-written Oracle database passwords within minutes."

  40. SQL vs. Oracle 10/11g Security

  41. Reliability and HA

  42. Availability • Keep the data available • Partial Database Availability • Online Piecemeal Restore • Online index operations • Snapshot IsolationStatement-level Snapshot • Snapshot IsolationTransaction-level Snapshot (RO) • Bring the database up quickly • Instant File Initialization • Fast Recovery • Make the Database Highly Available • Failover Clustering • Database Mirroring • Log Shipping • Database Snapshots Answers ! - 2 minutes to 4 hours - 1-2 minutes - 3 seconds

  43. Database MirroringBasic Principal of Synchronous Mirroring Is Alive, are you alive Commit Acknowledge Constantly Redoing on Mirror Transmit to Mirror Write to Local Log Committed in Log Write to Remote Log DB Log Log DB

  44. Enables load-balancing and high availability Similar to Transactional Replication Warm / hot standby Small possibility of data loss on failure Data can be updated at only one site but available for update at other sites

  45. Scalable Shared Databases • Solution to scale out a read-only reporting database • Similar to a read-only version of Oracle RAC • Provides workload isolation • Guarantees an identical view of reporting data from all servers • Makes data warehouses accessible to multiple servers • Powerful for Reporting Services

  46. SQL Server 2000: Database is available after Undo completes SQL Server 2005 Standard Edition: Database is available when Undo begins SQL Server 2005 Enterprise Edition: Database recovery leverages all CPU’s  Faster Recovery Database is Available! Redo Undo Undo Redo Database is Available! Redo Undo Database is Available!

  47. Oracle PublishingTransactional Replication • Designed specifically for Oracle Publishers • v8+ on any operating system • Administered like SQL Server, from SQL Server • No Oracle side software install necessary • Requires minimal knowledge of Oracle • Leverages existing SQL Server skills • Standard Transactional and Snapshot Publications SQL Server 2005 Distributor Subscribers

  48. Ensure System AvailabilityFailover clustering • Rolling upgrade/service pack/patch support for SQL Server failover clusters to minimize downtime • Build upon Windows Server 2008 • Windows Server 2008 Cluster Validation Tool • Exploit Service SIDs to remove requirement for domain groups • IPV6 and DHCP support • Nodes need not be on the same subnet • Support up to 16-node clusters

  49. Ensure System AvailabilityDatabase mirroring • Performance improvements • Log compression • Automatic page repair • Prevents a corrupt data page from making a database unavailable • Mirroring partner detects bad database page • Requests valid copy from partner Log Stream compression

  50. 5. Transfer page 6. Write Page Log Log Data Data 2. Request page 3. Find page X 4. Retrieve page 1. Bad Page Detected Database MirroringAutomatic page repair Witness Principal Mirror

More Related