Google SQL Server (IaaS)
Author: Ronald Fung
Creation Date: 12 June 2023
Next Modified Date: 12 June 2024
A. Introduction
Google Cloud SQL is a fully managed relational database service offered by Google Cloud Platform (GCP). It allows users to deploy and manage a wide range of relational databases, including MySQL, PostgreSQL, and SQL Server. However, SQL Server on GCP is offered as an Infrastructure-as-a-Service (IaaS) solution, rather than a fully managed service. Here’s an introduction to Google SQL Server (IaaS):
Deployment: Google SQL Server is deployed on a virtual machine (VM) in Google Compute Engine, which is the IaaS offering of GCP. Users can choose the VM instance type, disk size, and operating system, and can customize the VM according to their requirements.
Configuration: Once the VM is deployed, users can install and configure SQL Server on the VM. They can also configure the network settings, firewall rules, and access control policies to ensure the security and reliability of the database.
Management: Users are responsible for managing the SQL Server instance on the VM, including backup and recovery, software updates, and performance optimization. Google provides tools and services to help users manage their SQL Server instances, such as Cloud SQL Proxy for secure access to SQL Server instances and Cloud SQL Insights for database performance monitoring.
Scalability: Users can scale their SQL Server instance on the VM vertically or horizontally, depending on their workload and performance requirements. They can also use Google Cloud Load Balancing to distribute traffic across multiple SQL Server instances for improved scalability and availability.
Integration: Google SQL Server on IaaS can be integrated with other Google Cloud services, such as Google Cloud Storage, Google Cloud Bigtable, and Google Cloud Dataflow, to build more robust and scalable data pipelines and applications.
Overall, while Google SQL Server on IaaS requires more management and configuration than a fully managed service, it provides users with greater flexibility and control over their SQL Server instances. By deploying SQL Server on a VM in Google Compute Engine, users can customize their environment, scale their instances, and integrate with other Google Cloud services to build more robust and scalable applications.
B. How is it used at Seagen
As a biopharma research company, Seagen may benefit from using Google SQL Server (IaaS) for its relational database needs. Here are some ways Seagen can use Google SQL Server (IaaS):
Migrating from Microsoft Azure: Seagen can migrate their existing SQL Server databases from Microsoft Azure to Google SQL Server (IaaS) by exporting the databases from Azure and importing them into Google Cloud SQL. This can be done using the SQL Server Management Studio or other migration tools.
Deploying new SQL Server instances: Seagen can deploy new SQL Server instances on Google Compute Engine virtual machines. They can choose the VM instance type, disk size, and operating system, and can customize the VM according to their requirements. They can also configure the network settings, firewall rules, and access control policies to ensure the security and reliability of the database.
Managing SQL Server instances: Seagen can manage their SQL Server instances on Google Compute Engine by performing tasks such as software updates, backup and recovery, and performance optimization. They can use Google Cloud SQL Proxy for secure access to SQL Server instances and Cloud SQL Insights for database performance monitoring.
Scaling SQL Server instances: Seagen can scale their SQL Server instances on Google Compute Engine vertically or horizontally, depending on their workload and performance requirements. They can also use Google Cloud Load Balancing to distribute traffic across multiple SQL Server instances for improved scalability and availability.
Integrating with other Google Cloud services: Seagen can integrate their SQL Server instances with other Google Cloud services, such as Google Cloud Storage, Google Cloud Bigtable, and Google Cloud Dataflow, to build more robust and scalable data pipelines and applications.
Overall, by using Google SQL Server (IaaS), Seagen can benefit from a flexible and scalable solution for their relational database needs. With its support for migrating existing databases, deploying new instances, managing instances, scaling instances, and integrating with other Google Cloud services, Google SQL Server (IaaS) provides a range of options that can meet the needs of a variety of workloads and use cases.
C. Features
Lift and shift SQL Server
Migrate your existing workloads to Cloud SQL or SQL Server running on Compute Engine with full compatibility. SQL Server running on Google Cloud works with all of your familiar tools like SSMS and Visual Studio. Connect your existing workloads to the best of what Google Cloud has to offer.
Reduce operational overhead
Cloud SQL for SQL Server is a fully managed database service with a 99.95% SLA. Being fully managed includes upgrades, patching, maintenance, backups, and tuning. Regional availability and various VM shapes with memory from 3.75 GB up to 416 GB and storage up to 30 TB for all workloads provide flexible scaling options to eliminate the need to pre-provision or plan capacity before you get started.
Live migration for underlying VMs
When you run SQL Server on Compute Engine, the virtual machines can live-migrate between host systems without rebooting, which keeps your applications running even when host systems require maintenance.
D. Where Implemented
E. How it is tested
Testing Google SQL Server (IaaS) involves ensuring that the database instance is properly configured and optimized for performance, reliability, and security. Here are some steps to test Google SQL Server (IaaS):
Create a test environment: Create a test environment that mimics the production environment as closely as possible, including the database schema, data, and access control policies. Ensure that the database instance is properly configured and that the security policies are in place.
Deploy the database instance: Deploy the database instance on Google Compute Engine virtual machines. Ensure that the instance is properly configured and that it can communicate with other resources, such as applications or APIs.
Test database performance: Test the database performance by sending SQL queries to the deployed instance using database testing tools, such as Google Cloud Console or SQL Server Management Studio. Ensure that the instance is responding to queries properly and that there are no errors or timeouts.
Test database scaling: Test the database scaling by increasing the number of queries sent to the instance and observing how Google Compute Engine automatically scales up the instance to handle the increased load. Ensure that the instance is scaling properly and that there are no performance or availability issues.
Test database security: Test the database security by using database security testing tools, such as SQL Server Audit or Google Cloud Security Scanner. Ensure that the instance is secure and compliant with industry and regulatory standards.
Test database optimization: Test the database optimization by using database optimization testing tools, such as SQL Server Profiler or Google Cloud SQL Insights. Ensure that the instance is optimized for performance, reliability, and security.
Test database integration: Test the database integration with other Google Cloud services or third-party tools by using database integration testing tools, such as Google Cloud Dataflow or Microsoft Azure Data Factory. Ensure that the instance is integrated properly and that there are no integration issues or errors.
Overall, by thoroughly testing Google SQL Server (IaaS), users can ensure that their database instances are properly configured and optimized for performance, reliability, and security. Additionally, users can reach out to Google Cloud support for help with any technical challenges they may encounter.
F. 2023 Roadmap
????
G. 2024 Roadmap
????
H. Known Issues
While Google SQL Server (IaaS) is a reliable and widely used solution for deploying and running SQL Server databases, there are some known issues that users may encounter. Here are some of the known issues for Google SQL Server (IaaS):
Instance health issues: Users may encounter instance health issues, such as high latency or low throughput, especially for workloads that require low latency or high throughput. This issue can often be resolved by using the appropriate machine types, adjusting the routing tables, or enabling network load balancing.
Disk performance issues: Users may encounter disk performance issues, especially for workloads that require high I/O performance or that have high storage requirements. This issue can often be resolved by optimizing the disk configuration, using the appropriate machine types, or implementing disk caching and other performance optimization techniques.
Security issues: Users may encounter security issues, such as unauthorized access or data breaches, especially for workloads that require high security. This issue can often be resolved by using the appropriate security policies and access controls, such as firewall rules and VPN connections.
Licensing issues: Users may encounter licensing issues, especially for workloads that require complex licensing models or that have licensing restrictions. This issue can often be resolved by understanding and complying with the licensing terms and conditions, and by using the appropriate licensing tools and services.
Integration issues: Users may encounter integration issues with other cloud services or third-party tools, such as data pipelines or analytics platforms. This issue can often be resolved by using industry-standard protocols and APIs to enable interoperability between different cloud services and tools.
Overall, while these issues may impact some users, Google SQL Server (IaaS) remains a powerful and reliable solution that is widely used by businesses and organizations around the world. By monitoring their performance and security alerts and logs, reviewing their database configuration and policies, and using best practices and industry standards, users can ensure that their SQL Server databases running on Google Compute Engine are optimized for performance, reliability, and security. Additionally, users can reach out to Google Cloud support for help with any known issues or other technical challenges they may encounter.
[x] Reviewed by Enterprise Architecture
[x] Reviewed by Application Development
[x] Reviewed by Data Architecture