Our client, a global biotech company with a presence in over 80 countries and a workforce of 4,000 employees, depends on a complex IT environment that includes both on-premises and cloud-based components. This infrastructure supports everything from research and development to manufacturing and corporate operations, with SQL Server playing a pivotal role across various environments.
Given the scale and criticality of their operations, the client required a partner who could not only manage and optimize their existing SQL Server infrastructure but also ensure continuous availability, security, and efficiency. ABCloudz stepped in to provide a comprehensive Operational Data Management (ODM) solution tailored to meet these diverse and demanding needs. Read on to uncover our strategies and solutions that kept this global biotech leader ahead of the curve.
Understanding the client’s infrastructure and needs
The client’s IT infrastructure is multifaceted, incorporating on-premises SQL Server environments as well as cloud-based SQL Server instances hosted on Azure. These environments serve different functions:
- Production environments are mission-critical, supporting live operations where downtime can have significant repercussions.
- Development environments support the ongoing development and testing of applications and systems.
- Validated environments adhere to strict regulatory requirements, particularly in the production of pharmaceuticals.
Each of these environments requires distinct support strategies to ensure optimal performance, compliance, and security.
Addressing critical SQL Server needs across the board
Our work spanned both on-premise and cloud-based SQL Server environments, ensuring comprehensive support across all aspects of the client’s infrastructure.
On-premises SQL Server enhancements
Our first task was to enhance the client’s on-premise SQL Server environments, ensuring they could support the demanding operational needs across different departments:
- Health check practices: We introduced regular health checks and performance tuning across various environments, proactively identifying and addressing potential issues before they could impact operations. This was particularly important in environments such as Manufacturing and Corporate, where system stability is crucial.
- Hardware upgrades: To improve performance and reliability, we upgraded key server hardware. This upgrade resulted in a notable 30% improvement in job execution times, directly enhancing operational efficiency.
- High availability clusters: Recognizing the importance of minimizing downtime, we deployed high availability clusters to ensure that critical SQL Server services remain accessible even in the event of hardware failures.
- License management: Compliance and cost-efficiency were addressed through a comprehensive license inventory, ensuring that all SQL Server instances were properly licensed and that resources were being used optimally.
Azure SQL databases support
Parallel to the on-premises improvements, we focused on optimizing the client’s Azure SQL databases (Paas and IaaS). The move to the cloud offered greater flexibility and scalability, but it also introduced new challenges, particularly in terms of security and cost management:
- Vulnerability management: We conducted thorough vulnerability assessments across all SQL Server instances on Azure, addressing any identified risks and establishing secure baselines to protect sensitive data.
- Cloud cost optimization: We thoroughly analyzed the client’s Azure instances to find ways to cut costs while maintaining performance. By adjusting service tiers and eliminating unused databases, we achieved a 73% reduction in monthly costs for specific instances.
Monitoring and incident management
Given the critical nature of the client’s operations, continuous monitoring and rapid incident response were essential components of our ODM strategy. To achieve this, we approached monitoring in four distinct phases:
Phase 0: Analysis
We conducted a thorough analysis of various monitoring systems that would best meet the client’s requirements. Our findings, previously published in a separate blog post, indicated that SQL Sentry was the most suitable solution for the client’s needs. This analysis allowed us to identify the specific features required to monitor their SQL Server environments, both on-premises and in the cloud, ensuring a targeted and effective monitoring strategy.
Phase 1: Implementation
After selecting SQL Sentry, we customized the platform using tailored templates to monitor all critical aspects of the client’s SQL Server environments. By focusing on the most relevant metrics and creating custom alerts with specific thresholds, we ensured early detection of critical issues while avoiding unnecessary noise. This approach aligned the monitoring system closely with the client’s operational objectives, allowing us to proactively address potential problems before they impacted performance.
Phase 2: ITSM integration
To further enhance the monitoring system’s efficiency, we integrated SQL Sentry with the client’s existing IT Service Management (ITSM) platform, ServiceNow. This integration streamlined the incident handling process by automatically creating tickets in ServiceNow based on alerts generated by SQL Sentry. These tickets were then assigned to the appropriate DBA teams for resolution. This automated workflow ensured that incidents were tracked and managed in a structured manner, allowing for better visibility and faster response times.
Phase 3: Incident management workflow
The incident management workflow was designed to ensure timely and effective responses. When SQL Sentry generates an alert, it is immediately sent to both the operations team and ServiceNow. ServiceNow creates a ticket and assigns it to the relevant DBA group based on the priority and nature of the incident. Simultaneously, email notifications are sent to operators, who can quickly assess the incident’s severity. For high-priority incidents, such as those categorized as first-level severity, the on-duty operator initiates an immediate phone call to the on-call DBA. This dual-layered notification system ensures that critical incidents are addressed within 15 minutes, 24/7, in line with the client’s Service Level Agreements (SLAs). This robust workflow minimizes the risk of prolonged downtime and ensures the rapid containment and resolution of any disruptions.
By structuring the monitoring process into these four phases, we delivered a comprehensive and scalable monitoring solution that aligns with the client’s operational goals while ensuring maximum uptime and performance.
Proactive maintenance and automation
To further enhance system stability and efficiency, we implemented a range of proactive maintenance strategies and automation solutions:
- Automated patching: Regular patching is essential to maintaining security and performance, but it can be time-consuming and error-prone if done manually. We developed automated scripts using PowerShell to streamline the patching process, ensuring that all SQL Server instances are kept up-to-date with minimal manual intervention.
- Regular database maintenance: Database backups, data archiving, index maintenance and queries optimization, log cleaning, general performance monitoring and availability, security management.
- User management automation: Managing database users across multiple environments can be complex. We automated the synchronization of database users, eliminating inconsistencies and reducing the workload on the client’s IT team.
Enhancing data integration and management
The client’s communication operations and data integration processes are vital to their overall business workflow. To support these processes, we focused on optimizing and automating key tasks:
- ETL process optimization: We developed and optimized PowerShell scripts to streamline ETL (Extract, Transform, Load) tasks, making data integration faster and more reliable. This included improvements to archiving workflows and the introduction of automation to reduce manual intervention.
- SQL Server 2022 upgrade for enhanced ETL performance and new features: We upgraded all ETL solutions across all environments, including clusters (a total of 10 TB of data), to the latest version of SQL Server 2022 with minimal downtime.
- SSIS package development: We enhanced the client’s SQL Server Integration Services (SSIS) packages, optimizing data management tasks and preparing the system for future growth. This included developing automated solutions for cleaning up unused tables and improving database efficiency.
By enhancing these processes, we helped the client adhere to their data exchange workflow within a strictly defined schedule, ensuring that one ETL package completes before the next begins, a critical factor for maintaining smooth and efficient operations.
Supporting O365 and SharePoint
In addition to SQL Server support, we provided extensive services for the client’s O365 and SharePoint environments, focusing on improving collaboration and content management:
Template library configuration: We configured a new SharePoint Template Library to standardize document creation processes, making it easier for the client’s teams to collaborate and maintain consistency across documents.
Platform migration: As part of the migration to new platform features, we managed the transition of content and educated users on the new tools. This included generating detailed reports and creating user guides to ensure a smooth adoption process.
These efforts streamlined the client’s internal processes, enhancing their ability to collaborate effectively and manage content across the organization.
Seeking a trusted company for ODM services?
We successfully delivered tailored solutions that ensured peak efficiency for a global biotech leader’s SQL Server and Azure environments, and we continue to support their ongoing needs.
With over 15 years of experience, we are masters of Operational Data Management, equipped to tackle challenges of any complexity in infrastructures of any scale. Our comprehensive expertise allows us to optimize your critical systems for performance, security, and stability.
Contact us today to discover how we can tailor our services to support your organization’s growth and success.
