Technical Services Alan Knight
Who we are and what we do • Seven Engineers • Windows, Unix, Oracle, SQLServer, SharePoint, Reporting Services • Installs and Upgrades • Operating Systems • Relational database • UNIT-e/Aptos • Database Management • Fixing • Tuning and Optimising
This session will cover 3 aspects of technical work Oracle to SQLServer migration SharePoint & Reporting Services Integration UNIT-e High Availability
Oracle to SQLServer Migration Microsoft provide an excellent product in the form of SQLServer Integration Services (SSIS). We use this software in migrating from an UNIT-e Oracle database platform to SQLServer. Due to the design of UNIT-e each customer will have differing setups. We will need to generate specific processes to per customer.
Oracle to SQLServer Migration Project Managed Determine a timeframe Pre Requisites -Separate servers (Oracle & SQLServer 2008) SQL scripts are provided to tidy key elements of data…and keep it tidy Commitment for Testing Sign-off Test, before starting Live Minimise downtime
LIVE UNIT-e database Oracle to SQLServer Migration Users continue to use the LIVE (Oracle) system until Migration Prep. scripts Continue to run these scripts regularly until Live Migration is complete
LIVE UNIT-e database TEST UNIT-e database Oracle to SQLServer Migration Migration packages
LIVE UNIT-e database TEST UNIT-e database Oracle to SQLServer Migration Migration packages Oracle SQLServer 2008
LIVE UNIT-e database TEST UNIT-e database Oracle to SQLServer Migration Continue to run these scripts regularly until Live Migration is complete Prep. scripts Oracle SQLServer 2008
Oracle to SQLServer Migration LIVE UNIT-e database TEST UNIT-e database Migration packages
LIVE UNIT-e database Oracle to SQLServer Migration Migration packages Old LIVE UNIT-e database Oracle SQLServer 2008
Oracle to SQLServer Migration – Case Study Case Study St Mary’s College Blackburn and Capita working together to achieve migration from Oracle RDBMS to Microsoft SQL Server 2008
SharePoint and Reporting Services Integration Integrating reports in dashboards is hot ! If you’re organisation isn’t analyzing all the data it is gathering to answer questions about your business, you’re going to fall behind. If you have SharePoint , you can integrate Reporting Server and Analysis Server with it http://www.marcvalk.net/2009/04/sharepoint-and-ssrs-integration/
SharePoint and Reporting Services Integration What do I need? • Windows 2003/8 • SQLServer 2008 • Reporting Services • MicroSoft Office SharePoint Server (MOSS) 2007 • Knowledge of Kerberos delegation
SharePoint and Reporting Services Integration This integration enables an end-user to view and manage reports completely from within a SharePoint environment. The key thing is getting all components to “talk” to each other whilst not compromising security. Kerberos delegation allows you to flow an authenticated identity across multiple physical tiers of an application to support downstream authentication and authorisation.
SharePoint and Reporting Services Integration • Why Kerberos? • ‘double hop’ authentication across servers • Impersonate the Windows user against Reporting Services to enable UNIT-e filtered views
SharePoint and Reporting Services Integration With SQL Server 2008, deep integration with Office SharePoint Server 2007 offers numerous advantages, including: • Single consistent user interface for managing and viewing reports. • A much richer user experience. • Storing reports in Office SharePoint Server 2007 document libraries. This allows a report to use standard Office SharePoint Server 2007 features, such as workflow, versioning, and collaboration.
SharePoint and Reporting Services Integration Reports and all associated resources are stored within the Office SharePoint Server 2007 database and are automatically synchronized with the same reports that are stored and executed from the report server. Scheduling, caching, and subscriptions are stored in the report server database only because this functionality is not available natively in a SharePoint document library. Single security model for reports because they are treated like any other document inside a SharePoint document library. Common report filtering paradigm that is used in SharePoint document libraries and filtering Web Parts. http://blogs.msdn.com/sharepoint/archive/2007/02/19/microsoft-sql-server-2005-sp2-reporting-services-integration-with-wss-3-0-and-moss-2007.aspx
UNIT-e High Availability Why? Mission Critical systems should be able to cope with worst case scenarios • Jan 2009 - J Crew web site crashed after America's first family selected its affordable designs to wear on inauguration day. • July 2009 - O2 website crashed under 3G iPhone demand • April 2008 – Inland Revenue extend the tax return deadline because it’s web site could not cope with last minute on-line tax returns • September 2007 - Northern Rock web site failed due to unprecedented demand • February 2000 – Sony closed Play Station web site for two hours as it failed when receiving 400,000 hits per minute
UNIT-e High Availability Why? Institutions are now providing on-line enquiries, applications and enrolments Students and prospective students inevitably push deadlines to their limit Would your web services cope if 50% of your on-line student transactions took place in the few days leading up to published deadlines? If they did not then what would be the cost to your institution in terms of revenue and credibility? Web and database server upgrades can take place without downtime
UNIT-e High Availability Options • There are a number of techniques associated with delivering high availability / high performance computing services • Hot Spare • Network Card Teaming – Link Aggregation • Network Load Balancing • Server clustering
Hub Network Clients Node A Node B active Standby Inactive UNIT-e High Availability Hot Spare
UNIT-e High Availability Network card teaming (link aggregation)) describes using multiple network cables/ports in parallel to increase the link speed beyond the limits of any one single cable or port, and to increase the redundancy for higher availability. http://en.wikipedia.org/wiki/Link_aggregation
Internal Subnet UNIT-e Database Server Internal network Firewall Firewall Internal Web Server and Transaction Server Desktop PC UNIT-e Web Server Desktop PC UNIT-e High Availability Standard UNIT-e configuration
UNIT-e High Availability Network load Balancing: • Load balancing uses a device, which can be a server or an appliance, to balance the load of traffic across multiple servers waiting to receive that traffic. • The device sends incoming traffic based on an algorithm to the most underused machine or spreads the traffic out evenly among all the machines that or on at the time.
Desktop PC Internal Subnet Internal Network Firewall Firewall Heart Beat Desktop PC Node A Node B UNIT-e High Availability Network load Balancing: Basic Load Balanced Web Server
Hub Client PC Heart Beat Client PC Passive Node Active Node Client PC Shared Storage UNIT-e High Availability Server Clustering
Hub Client PC Heart Beat Client PC Active Node Passive Node Client PC Shared Storage UNIT-e High Availability Server Clustering
Internal Network Heart Beat NLB OLS Web Service Node A Node B Internal Subnet Heart Beat NLB Intranet Web Site Firewall Firewall Node A Node B Heart Beat Node A Node B Heart Beat Clustered SQL Server / NLB OLS Web Site Oracle Database Database Node A Node B Desktop PC UNIT-e High Availability A UNIT-e solution
Technical Services Any questions?