Google Cloud SQL Server Integration with Edilitics

Google Cloud SQL Server is a managed relational database service offered by Google Cloud Platform. It lets you run Microsoft SQL Server databases on Google's infrastructure. This service simplifies managing SQL Server as Google handles patching, backups, and scaling, allowing you to focus on your data and applications. To learn more about Google Cloud SQL Server, visit the official Google Cloud SQL Server page.

Within the Edilitics platform, Google Cloud SQL Server can be used both as a source and destination for data, making it a versatile tool for data analysis workflows. This enables you to leverage Google Cloud SQL Server’s strengths for data storage and retrieval within your Edilitics environment.

This step-by-step guide equips you with the knowledge to configure your Google Cloud SQL Server database and integrate it with Edilitics for data analysis.

Before You Begin#

Make sure you have the following:

• Ensure your Google Cloud SQL Server instance is up and running.

  • Verify this on the Google Cloud SQL Instances page by checking for an "Active" status next to the instance ID.

• The SQL Server version must be 2017 or later for compatibility with Edilitics.

Setting Up Google Cloud SQL Server for Edilitics Integration#

Step 1: Whitelist Edilitics IP Addresses#

To enable Edilitics to connect to your Google Cloud SQL Server:

• Access the Google Cloud SQL Instances page and select your desired instance ID.

• In the left navigation pane, under Overview, click Connections.

• On the Connections page:

  • Select the Public IP checkbox.

  • Click + Add Network.

  
  

• In the Edit Network section, specify:

  • Name: A descriptive name (e.g., "Edilitics Access").

  • Network: Enter 0.0.0.0/0 to authorize all connections temporarily (not recommended for production) or add Edilitics IPs:

    • 34.93.42.224

    • 34.131.133.218

• Click Save to add the IP address(es) to the Authorized networks list.

Step 2: Create a Database User and Grant Permissions#

Edilitics requires a database user with read-only privileges (db_datareader) to access your data.

Option 1: Google Cloud Console#

• Go to the Google Cloud SQL Instances page.

• Click on your master SQL Server instance.

• In the left navigation pane, under Connections, click the Users tab.

• Click + ADD USER ACCOUNT.

• Provide the user account details (username and password) and click ADD.

Option 2: SQL Server Client Tool#

If using a SQL Server client tool:

• Log in to your SQL Server instance as the master user.

• Run the following SQL commands, replacing placeholders:

  
  

  USE <schema_name>; -- Replace with your schema name

  -- Optional: Create a login user

  CREATE LOGIN '<login_user>' WITH PASSWORD = '<password>';

  -- Create a database user

  CREATE USER edilitics FOR LOGIN '<login_user>';

  -- Grant read-only permissions

  EXEC sp_addrolemember 'db_datareader', 'edilitics';

  
  

  Explanation:

  • CREATE LOGIN creates a login user with a secure password.

  • CREATE USER maps the login to a user within a database.

  • sp_addrolemember grants the db_datareader role for read-only access.

Step 3: Gather Configuration Details#

To set up the Edilitics integration, you’ll need the following:

• Hostname and Port Number:

  • Access the Google Cloud SQL Instances page.

  • Locate the hostname under the Public IP address column.

  
  
  • The default port for SQL Server is typically 1433.

• Username and Password:

  • Use the credentials created in Step 2.

• Database Names:

  • In the Google Cloud SQL Instances page, click on your instance.

  • Navigate to the Databases tab to locate the database name(s).

  
  

Connecting Google Cloud SQL Server to Edilitics#

Step 4: Add the SQL Server Connector in Edilitics#

• In Edilitics, navigate to the Integrations module.

• Click on New Integration.

• Choose the Google Cloud SQL Server connector from the list or search for it.

Step 5: Configure the SQL Server Connection#

On the configuration screen, provide the following details:

Using a Connection String#

• Alternatively, connect using the connection string:

  
  

  sqlserver://<username>:<password>@<host>:<port>/<database_name>

  
  

Step 6: Test the Connection#

• Once you've entered your credentials, click the Test Connection button. Edilitics will attempt to connect to your SQL Server database. You'll see a confirmation message if the connection is successful. If there's a problem, Edilitics will provide details to help you troubleshoot.

Step 7: Verify & Finalize Integration#

• Preview (Optional): Retrieve a sample of tables and data to confirm accuracy.

• Update (Optional): Adjust connection details if the preview does not match expectations.

• Save: Finalize by encrypting and storing connection details securely in Edilitics.