SQL Server for the Oracle DBA Sept 16 th 2009. Presents. Arie d. jones ( aj ) Principal Technology manager. Speaker Background. Principal Technology Manager for Perpetual Technologies 20+ years of programming experience 11 years of experience with SQL / About as long with Oracle….
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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.
SQL Server for the Oracle DBASept 16th 2009 Presents Arie d. jones (aj) Principal Technology manager
Speaker Background Principal Technology Manager for Perpetual Technologies 20+ years of programming experience 11 years of experience with SQL / About as long with Oracle…. Lead author ‘SQL Functions’ book Author ‘Learn SQL in 24 Hours’ Certified Oracle 9i Trained all the way up to Oracle data warehousing Etc.
Logical Database Structure Instance Database Schema Table
Database Object Comparision • Oracle and SQL Server share a large number of objects. They are merely named differently.
Physical Database Structure } DATA1.MDF FILE GROUP DATA2.NDF PRIMARY & SECONDARY DATA FILES DATA3.NDF Instance Database LOG1.LDF LOG FILES LOG2.LDF
Security SysAdmin BulkAdmin DiskAdmin ProcessAdmin Etc…. Server Roles Login Objects Roles Users Database Instance Objects Application Role
Physical Backups Full Online or HOT Backup Full Offline or COLD Backup Database Differential or Incremental Backup Instance Archive Log Backup Database Snapshot
Memory Management Oracle allows memory to be managed by the DBA or to automatically be managed by the instance. Memory configuration in Oracle can be down as far as the subcache level. With SQL Server 2005 you can only manage memory for the entire memory pool With SQL Server 2008 Resource Governor you can manage much more specifically.
Locking • Isolation Levels • Read Uncommitted • Read Committed • Repeatable Read • Serializable • Snapshot • Locking • Row • Page • Table • Lock escalation
Views Views types comparison between SQL Server and Oracle
Triggers Trigger type comparison between SQL Server and Oracle
Database Demo Chris Zeis Oracle DBA Part owner of multi-million dollar IT company.
Real Database Demo • Let’s see some of the database in action. • And learn some of the tricks you will need to understand in order to control the system.
High Availability • SQL Server contains a good number of high availability options to increase uptime • Clustering • Log Shipping • Replication • Mirroring • Peer-to-Peer
Clustering Shared Storage Node 3 Node 1 Node 2
Log Shipping LOG FILE IS BACKED UP LOG FILE IS RESTORED Partner Instance LOG BACKUP
Replication / Mirroring Transactions are shipped and applied to the partner database Partner Instance
SQL Server Tools • There are a variety of tools that come with SQL Server that enable you to take advantage of different aspects of database development. • ETL -> SQL Server Integration Services (SSIS) • Reporting -> Reporting Services (SSRS) • Data Warehousing -> SQL Server Analysis Services (SSAS)
SQL Server Integration Services Full fledged ETL suite Drag and Drop development environment Underlying utility used for Wizards in SQL Server Ability to customize!
SQL Server Reporting Services • Enterprise class reporting platform. • Consists of 2 parts • Portal • Web Service • Customizable • Lots of functionality • Scheduling • Exporting • Etc….
SQL Server Analysis Services Sits on top of SQL Server. Can be used to extract data from multiple underlying systems Combines with Reporting Services to provide powerful MDX reporting capabilities.
Custom Database Development • Provides ability to further automate the system • Server Management Objects( SMO) • .NET based class library • Basis of a lot of the GUI interfaces that you have seen • More customization can be done through Powershell if scripting is your ‘cup of tea’ • Demo a SMOogle application
Thank You! • Slides can be found on my blog • http://www.programmersedge.com • Or http://events.perptech.com • Email: email@example.com • Questions & Open Discussion