# Guide: Handle upstream schema changes with zero downtime How to add a column, or drop a column, from your source SQL Server database, without any downtime in Materialize > **Note:** Changing column types is currently unsupported. Materialize allows you to handle certain types of upstream table schema changes seamlessly, specifically: - Adding a column in the upstream database. - Dropping a column in the upstream database. This guide walks you through how to handle these changes without any downtime in Materialize. ## Prerequisites Some familiarity with Materialize. If you've never used Materialize before, start with our [guide to getting started](/get-started/quickstart/) to learn how to connect a database to Materialize. ### Set up a SQL Server database For this guide, setup a SQL Server 2016+ database. In your SQL Server, create a table `t1` and populate: ```sql CREATE TABLE t1 ( a INT ); INSERT INTO t1 (a) VALUES (10); GO -- The GO terminator may be unsupported or unnecessary for your client. ``` ### Configure your SQL Server Database Configure your SQL Server database using the [configuration instructions for self hosted SQL Server.](/ingest-data/sql-server/self-hosted/#a-configure-sql-server) ### Connect your source database to Materialize Create a connection to your SQL Server database using the [`CREATE CONNECTION` syntax.](/sql/create-connection/) ## Create a source using the new syntax In Materialize, create a source using the [`CREATE SOURCE` syntax](/sql/create-source/sql-server-v2/). ```mzsql CREATE SOURCE my_source FROM SQL SERVER CONNECTION sqlserver_connection; ``` ## Create a table from the source To start ingesting specific tables from your source database, you can create a table in Materialize. We'll add it into the v1 schema in Materialize. ```mzsql CREATE SCHEMA v1; CREATE TABLE v1.t1 FROM SOURCE my_source(REFERENCE dbo.t1); ``` Once you've created a table from source, the [initial snapshot](/ingest-data/#snapshotting) of table `v1.t1` will begin. > **Note:** During the snapshotting, the data ingestion for the other tables associated with > the source is temporarily blocked. As before, you can monitor progress for the > snapshot operation on the overview page for the source in the Materialize > console. ## Create a view on top of the table. For this guide, add a materialized view `matview` (also in schema `v1`) that sums column `a` from table `t1`. ```mzsql CREATE MATERIALIZED VIEW v1.matview AS SELECT SUM(a) from v1.t1; ``` ## Handle upstream column addition ### A. Add a column in your upstream SQL Server database In your upstream SQL Server database, add a new column `b` to the table `t1`: ```sql ALTER TABLE t1 ADD b BIT NULL; INSERT INTO t1 (a,b) VALUES (20, 1); GO -- The GO terminator may be unsupported or unnecessary for your client. ``` This operation does not impact the SQL Server CDC output; the SQL Server continues to publish CDC changes only for column `a`. As such, the addition of a new column has no immediate effect in Materialize. In Materialize: - The table `v1.t1` will continue to ingest only column `a`. - The materialized view `v1.matview` will continue to have access to column `a` only. ### B. Enable CDC for your table under a new capture instance in your upstream SQL Server database In order for Materialize to begin receiving data for this new column, you must create a new capture instance for your table, explicitly specifing a new [`@capture_instance` name](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-enable-table-transact-sql?view=sql-server-ver17#----capture_instance). > **Note:** SQL Server only allows a [maximum of 2 capture > instances](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-enable-table-transact-sql?view=sql-server-ver17#----capture_instance). > If you already have 2 capture instances, you will have to [disable one of > them](#disable-unused-capture-instance), possibly resulting in downtime for your > Materialize source. ```sql EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 't1', @role_name = 'materialize_role', @capture_instance = 'dbo_t1_v2', -- MUST BE SPECIFIED @supports_net_changes = 0; GO -- The GO terminator may be unsupported or unnecessary for your client. ``` The newly created capture instance will include CDC data for column `b`. Now, when you create a new table `t1` from your source in Materialize (see next step), Materialize will select the most [recently created capture instance](/ingest-data/sql-server/#capture-instance-selection) (i.e., the capture instance with the newly added column `b`). See also: - [Capture instance selection](/ingest-data/sql-server/#capture-instance-selection) - [Enable Change-Data-Capture for the tables](/ingest-data/sql-server/self-hosted/#4-enable-change-data-capture-for-the-tables) ### C. Create a new table from the source in Materialize To incorporate the new column into Materialize, create a new `v2` schema and recreate the table in the new schema. When creating the table, Materialize uses the most [recently created capture instance](/ingest-data/sql-server/#capture-instance-selection) (i.e., the capture instance with the newly added column `b`): ```mzsql CREATE SCHEMA v2; CREATE TABLE v2.t1 FROM SOURCE my_source(REFERENCE dbo.t1); ``` The [snapshotting](/ingest-data/#snapshotting) of table `v2.t1` will begin. `v2.t1` will include columns `a` and `b`. > **Note:** During the snapshotting, the data ingestion for the other tables associated with > the source is temporarily blocked. As before, you can monitor progress for the > snapshot operation on the overview page for the source in the Materialize > console. When the new `v2.t1` table has finished snapshotting, create a new materialized view `matview` in the new schema. Since the new `v2.matview` is referencing the new `v2.t1`, it can reference column `b`: ```mzsql {hl_lines="4"} CREATE MATERIALIZED VIEW v2.matview AS SELECT SUM(a) FROM v2.t1 WHERE b = true; ``` ## Handle upstream column drop ### A. Exclude the column in Materialize To drop a column safely, in Materialize, first, create a new `v3` schema, and recreate table `t1` in the new schema but exclude the column to drop. In this example, we'll drop the column `b`. ```mzsql CREATE SCHEMA v3; CREATE TABLE v3.t1 FROM SOURCE my_source(REFERENCE dbo.t1) WITH (EXCLUDE COLUMNS (b)); ``` > **Note:** During the snapshotting, the data ingestion for the other tables associated with > the source is temporarily blocked. As before, you can monitor progress for the > snapshot operation on the overview page for the source in the Materialize > console. ### B. Drop a column in your upstream SQL Server database In your upstream SQL Server database, drop the column `b` from the table `t1`: ```sql ALTER TABLE t1 DROP COLUMN b; GO -- The GO terminator may be unsupported or unnecessary for your client. ``` Dropping the column `b` in SQL Server will not affect `v3.t1` (or on `v1.t1`) in Materialize. However, the drop affects `v2.t1` and `v2.matview` from our earlier examples. When the user attempts to read from either, Materialize will report an error that the source table schema has been altered. ## Optional ### Disable unused capture instance SQL Server only allows a [maximum of 2 capture instances](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-enable-table-transact-sql?view=sql-server-ver17#----capture_instance) per table. To find the capture instance(s) for a table: ```sql SELECT capture_instance FROM cdc.change_tables WHERE source_schema = '' AND source_table = ''; ``` After you have fully cut over to the new source version for the table, and you previously [created a new capture instance for your upstream table](#b-enable-cdc-for-your-table-under-a-new-capture-instance-in-your-upstream-sql-server-database), you may wish to disable the old capture instance if it is no longer in use. > **Warning:** Ensure that no other source tables or other applications are using the old > capture instance; otherwise, they will break. To disable a capture instance for a table: ```sql EXEC sys.sp_cdc_disable_table @source_schema = '', @source_name = '', @capture_instance = ''; ```