Creating SQL Server VM instances

Microsoft SQL Server is a database system that runs on Windows Server and some Linux distributions. You can use SQL Server on Compute Engine as part of the backend for your applications, as a flexible development and test environment, or in addition to your on-premises systems for backup and disaster recovery.

Compute Engine provides images with Microsoft SQL Server preinstalled on Windows Server. For these SQL Server images, Compute Engine manages the license for both Windows Server and SQL Server, and includes the cost in your monthly bill. Create VMs with SQL Server and scale out to large multi-node configurations when you need them.

Compute Engine stores your data on durable persistent disks with automatic redundancy and automatic encryption at rest. Use these persistent disks to store your SQL Server data without having to worry about the durability or security of your data. For additional performance, create your VMs with a local SSD as a cache that provides additional IOPS and performance for SQL Server queries.

You can create Compute Engine virtual machine (VM) instances that run SQL Server in the following ways:

Creating a VM using a SQL Server image. These images include one of several SQL Server editions that run on Windows Server. You can use these images to run SQL Server on Compute Engine without obtaining your own separate license from Microsoft.
Migrating your existing SQL Server licenses to a Windows Server VM on Compute Engine and manage those licenses yourself.
Installing SQL Server on a Compute Engine Linux VM.

For a full list of the available images that include SQL Server preinstalled, see Operating system details.

Machine type requirements for SQL Server images

SQL Server Standard

You can run SQL Server Standard on VMs with any machine type, but shared-core machine types will not run optimally. Google recommends that you use VMs with at least one vCPU to run SQL Server Standard.

SQL Server Enterprise

VMs that run SQL Server Enterprise must have at least 4 vCPUs. For optimal performance, Google recommends that you run SQL Server Enterprise on VMs with larger memory capacities. Depending on your workload, you should use high-memory machine types with 8 vCPUs or more. These machine types maximize the ratio of memory to each vCPU that is available on Compute Engine, which is optimal for SQL Server Enterprise VMs.

Additionally, you can use SQL Server Enterprise on Compute Engine to create SQL Server Availability Groups.

Default components

SQL Server images include several components by default. The default components depend on the edition of SQL Server that you selected.

For information about the default components included with your version of SQL Server, see Editions and supported features of SQL Server 2019.

For information about modifying the SQL Server components, see Add Features to a VM of SQL Server.

Before you begin

If you want to use the command-line examples in this guide, do the following:
1. Install or update to the latest version of the Google Cloud CLI.
2. Set a default region and zone.
If you want to use the API examples in this guide, set up API access.

Creating a SQL Server instance

To create a SQL Server instance, specify the image family for the specific version of SQL Server that you need. For a list of the SQL Server image families, see Operating system details.

For optimal performance, database server platforms require machine types with more virtual CPUs and larger amounts of memory. Google recommends that you use machine types with at least 2 vCPUs and at least 4 GB of memory when you run SQL Server instances. You can use custom machine types to configure SQL Server instances to match the performance requirements for your workload. Additionally, you can use larger persistent disks and faster SSD persistent disks to improve the performance of your applications.

Additionally, you must set specific firewall rules to allow SQL Server traffic on the VPC network or subnet that your VM is a part of. For more information see Best practices for SQL Server.

Console

To create a SQL Server instance, follow these steps:

In the Google Cloud console, go to the Create an instance page.

Go to Create an instance
Specify the VM details.
In the Boot disk section, click Change, and then do the following:
1. On the Public images tab, click the Operating system list, and then select SQL Server on Windows Server.
2. In the Version list, select a version.
3. In the Boot disk type list, select a boot disk type.
4. In the Size (GB) field, set the boot disk size.
5. Optional: To modify the advanced configuration for the boot disk, click Show advanced configuration, and then specify your settings.
6. To save the boot disk configuration, click Select.
Click Create.

After you create the VM, create a firewall rule to allow access to SQL Server on your VM. The default SQL Server port is 1433.

In the Cloud console, go to the Firewall Rules page.

Go to Firewall Rules
At the top of the page, click Create firewall rule to start creating a firewall rule.
Specify the details for this firewall rule.
- In the Name field, specify a name for the firewall rule.
- In the VPC network field, select the VPC network where your SQL Server instance is located.
- For the Direction of traffic, select Ingress.
- In Source filter field, select the range of IPs that you want to allow access on this port. For this example, you can select Allow from any source.
- In the Allowed protocols and ports section, enter the port that SQL Server uses. For this example, specify tcp:1433;, which is the default port.
Click Create to create this firewall rule and allow access to your SQL Server instance over port 1433.

If you need to add additional firewall rules to your VM, see the firewall rules documentation.

gcloud

Use the compute images list command to see a list of available SQL Server images:

gcloud compute images list --project windows-sql-cloud --no-standard-images

Use the compute instances create command to create a new VM and specify the image family for one of the Windows Server or SQL Server public images.

gcloud compute instances create VM_NAME \
    --image-project windows-sql-cloud \
    --image-family IMAGE_FAMILY \
    --machine-type MACHINE_TYPE \
    --boot-disk-size BOOT_DISK_SIZE \
    --boot-disk-type BOOT_DISK_TYPE

Replace the following:

VM_NAME: the name for the new instance.
IMAGE_FAMILY: one of the public image families for Windows Server or SQL Server images.
MACHINE_TYPE: one of the available machine types.
BOOT_DISK_SIZE: the size of the boot disk in GB. Larger persistent disks have higher throughput.
BOOT_DISK_TYPE: the type of the boot disk for your instance. For example, pd-balanced.

After you create the VM, create a firewall rule to allow access to SQL Server on your VM. The default SQL Server port is 1433.

gcloud compute firewall-rules create sql-server-1433 \
    --description "Allow SQL Server access from all sources on port 1433." \
    --allow tcp:1433 --network NETWORK

where NETWORK is the name of the VPC network where your VM is located.

If you need to add additional firewall rules to your VM, see the firewall rules documentation.

API

To create a VM with the API, include the initializeParams property in your VM creation request and specify a Windows image.

POST https://compute.googleapis.com/compute/v1/projects/PROJECT_ID/zones/ZONE/instances

instance = {
  "name": "VM_NAME",
  "machineType": "zones/ZONE/machineTypes/MACHINE_TYPE",
  "disks": [{
      "boot": "true",
      "type": "PERSISTENT",
      "initializeParams": {
         "diskName": "DISK_NAME",
         "sourceImage": "projects/IMAGE_PROJECT/global/images/family/IMAGE_FAMILY",
         "diskSizeGb": "BOOT_DISK_SIZE",
         "diskType": "BOOT_DISK_TYPE",
       }
    }],
  "networkInterfaces": [{
    "accessConfigs": [{
      "type": "ONE_TO_ONE_NAT",
      "name": "External NAT"
     }],
    "network": "global/networks/default"
  }],
  "serviceAccounts": [{
       "email": DEFAULT_SERVICE_EMAIL,
       "scopes": DEFAULT_SCOPES
  }]
}

Replace the following:

PROJECT_ID: the ID for your project.
ZONE: the zone for this instance.
VM_NAME: the name for the new VM.
MACHINE_TYPE: the available machine types.
IMAGE_PROJECT: either windows-cloud for Windows Server images or windows-sql-cloud for Windows Server images with SQL Server preinstalled.
IMAGE_FAMILY: the public image families for Windows Server or SQL Server images.
BOOT_DISK_SIZE: the size of the boot disk in GB. Larger persistent disks have higher throughput.
BOOT_DISK_TYPE: the type of the boot disk for your VM. For example, pd-ssd.

After you create the VM, create a firewall rule to allow access to SQL Server on your VM. The default SQL Server port is 1433.

POST https://compute.googleapis.com/compute/v1/projects/PROJECT_ID/global/firewalls

{
 "name": "sql-server-1433",
 "network": "global/networks/NETWORK",
 "allowed": [
  {
   "IPProtocol": "tcp",
   "ports": [
    "1433"
   ]
  }
 ]
}

Replace the following:

PROJECT_ID: the ID for your project.
NETWORK: the name of the VPC network where your VM is located.

After you create your SQL Server instance, set the initial password for the VM so that you can connect to the VM through RDP. Then, run the SQL Server Management Studio as an administrator to manage databases on your SQL Server instance.

Additionally, you can enhance the SQL Server installation with one or more of the following items:

Install SQL Server Data Tools on your SQL Server instance so that you can create and manage your SQL Server databases using Visual Studio. Connect to the VM and then download and install the SQL Server Data Tools from the Microsoft website.
SQL Server images include several default components. Optionally, you can add features to your SQL Server installation. SQL Server images always include the setup executable at c:\sql_server_install\setup.exe.
If you need to install SQL Server to a secondary persistent disk, add a secondary persistent disk to the VM. Then, connect to the VM and run the c:\sql_server_install\setup.exe installer and select a new installation path on your secondary disk.
If you require additional storage space on your boot disk or on any secondary storage disks, resize the persistent disks even while your VMs is running.

Managing SQL Server databases

Run the SQL Server Management Studio as an administrator to configure SQL Server databases. You can download and install the SQL Server Management Studio on your local workstation, and use it to connect to the database engine on your VM remotely.

If you cannot install the Management Studio on your local workstation, connect to the VM through RDP and run the Management Studio on the VM itself. SQL Server 2012 and SQL Server 2014 both include the SQL Server Management Studio by default. For SQL Server 2016, you must download the SQL Server Management Studio from the Microsoft website and install it on the VM.

By default, SQL Server uses Windows Authentication mode to control remote access to SQL Server itself. If you need to use SQL Server Authentication mode, change the authentication mode.

Manually updating SQL Server instance names

If you rename a VM that hosts SQL Server, you must update the SQL Server instance name. For more information, see Rename a Computer that Hosts a Stand-Alone Instance of SQL Server.

What's next

Verify that your VM has successfully started.
Create and attach a secondary storage disk to your VM to store your data separately from the boot disk.
Learn best practices for SQL Server on Google Cloud.
Create and configure a high-performance SQL Server instance.
Use HammerDB to run Load testing on SQL Server.
Use SQL Server with .NET applications.
Connect to your VM.