SolidPractice Information

Domain and Role/Product

SOLIDWORKS PDM 

What's Covered? - Jump to each section using the Contents List in the top right

• SQL server failover network topology
• Installing and configuring SQL Server
• Enabling alwayson
• Testing your SQL server setup

Revision History

Note
All SolidPractices are written as guidelines. It is a strong recommendation to use these documents only after properly evaluating your requirements. Distribution of this document is limited to Dassault Systèmes SolidWorks employees, VARs, and customers that are on active subscription. 

You may not post this document on blogs or any internal or external forums without prior written authorization from Dassault Systèmes SolidWorks Corporation.

This document was updated using version SOLIDWORKS 2023 SP02. If you have questions or need assistance in understanding the content, please get in touch with your designated reseller.

Your Feedback Requested
We would like to hear your feedback and suggestions for new topics. After reviewing this document, please take a few minutes to fill out a brief survey. Your feedback will help us create the content that directly addresses your challenges.

1) Preface

This SolidPractice document provides an overview of the general process to install a Microsoft SQL Server 2019 Failover Cluster Instance (FCI) on an existing Windows Server 2019 Failover Cluster (WSFC) installation without a witness SQL Server. If you encounter issues during or after SQL Server FCI setup, either consult with your IT representative or engage Microsoft for troubleshooting assistance. SOLIDWORKS Technical Support does not typically assist with troubleshooting SQL Server installations. For assistance with setting up WSFC, contact your IT representative to accomplish the task. The SQL Server Enterprise Edition is necessary to enable the AlwaysOn Availability feature, which SOLIDWORKS PDM Professional relies on for database replication.

This document is subject to the following stipulations:

• Dassault Systèmes SolidWorks Corp and SOLIDWORKS resellers do not test or fully support clustered SQL Server environments.
• Use of clustered SQL Server environments is at the user’s own risk. SOLIDWORKS Technical Support and SOLIDWORKS resellers provide only basic-level assistance for problems that arise from use in these environments. Problems that are reproducible outside of the special environment are supported in compliance with the terms of an active subscription service agreement.
• Modifications to Dassault Systèmes SolidWorks Corp software that relate to fixes for unsupported environments may not be practicable.

2) Network Topology of a SQL Server Failover Cluster

a) Single Site

SQL Server FCI requires shared storage as shown below to enable Availability Groups (AG).

b) Single Site with Additional Node

Additional read-only SQL Server AG through LAN. SQL Servers that are deployed for SOLIDWORKS PDM Professional database replication feature, they can be standalone instances configured for SQL Server AG with AlwaysOn Availability.

c) Primary and Remote Sites

Additional read only SQL Server AG in a remote site through WAN.

3) Install and Configure SQL Server

Note: Screen capture images reflect installation of the SQL Server 2019 Enterprise edition.

a) Install SQL Server on a Primary Replica Machine

From one of the two Windows Servers in the WSFC setup, install SQL Server Service.

1. Run setup.exe from the SQL Server installation media to open the SQL Server Installation Center wizard.

2. In the Installation section, select New SQL Server failover cluster installation.

3. Enter the product key and then click Next.

4. On the License Terms page, accept the terms and then click Next.

5. On the Microsoft Update page, either select or skip the Use Microsoft Update to check for updates (recommended) option.

6. If you do not select the update option, click Next in the Product Updates page.

7. Before proceeding with the installation, review and resolve all warnings. 

8. On the Feature Selection page, select to install the following features and then click Next:

- Database Engine Services, SQL Server Replication, Client Tool Connectivity, Integration Services, and Client Tools Backwards Compatibility.

9. On the Instance Configuation page, enter a name for the SQL Server FCI network and the instance ID, and then click Next. The SOLIDWORKS PDM SQL Server connection uses the FCI network name and instance and not the actual physical server name.

10. The Cluster Resource Group page displays the Windows® cluster resource groups that SQL Server cannot use. Your setup will differ from the example shown.

11. On the Cluster Disk Selection page, select the disks to use with the SQL Server FCI, and then click Next.

12. On the Cluster Network Configuration page, enter the static IP address to reserve for the SQL Server FCI, and then click Next. The following image shows assignment of the IP address 172.16.100.200 for the SQLCluster.

13. On the Server Configuration page, enter the Windows account name and password for the FCI as shown in the following image.

14. On the Database Engine Configuration page, select the Mixed Mode option and then specify a password to use for the ’sa’ account. Adding an additional account is useful for account recovery or reset.

15. Click the Data Directories tab and change the directory locations as appropriate.

.As the following image depicts, you can also change the data directories, initial file size, and number of TempDB MDF files. To continue the installation, click Next.

16. On the Ready to Install page, click Install to begin the installation process for the SQL Server service.

i) Confirm the SQL Server FCI Installation

• Open the Failover Cluster Manager tool and confirm the installation and settings.

b) Install SQL Server on a Secondary Read-Only Replica Machine

1. From a server that is part of the WFCI, open the SQL Server Installation Center tool.

2. In the Installation section, select the Add node to a SQL Server failover cluster option.

3. Enter the product key, and then click Next.

4. On the License Terms page, accept the terms and then click Next.

5. On the Microsoft Update page, either select or skip the Use Microsoft Update to check for updates (recommended) option.

6. If you do not select the update option, click Next in the Product Updates page.

7. Before proceeding with the installation, review and resolve all warnings.

8. On the Cluster Node Configuration, select the appropriate cluster and then click Next. The following image depicts selection of the MSSQLSERVER instance name and the TS-REPLICA1 cluster.

9. Select the IPv4 option for the existing FCI.

10. Review the information on the Ready to Add Node page and then click Install.

11. On the Service Accounts page, enter the password for the user account for the SQL Server Database Engine and the SQL Server Agent, and then click Next. The user account must be the same as the initial FCI setup.

12. When the installation is complete, click Close. 

4) Enable SQL Server AlwaysOn on both nodes

1. Open the SQL Server Configuration Manager.

2. Click the SQL Server Services Node > right-click the SQL Server (MSSQLSERVER) service > click Properties.

3. On the AlwaysOn High Availability tab of the SQL Server (MSSQLSERVER) Properties dialog box, select the Enable AlwaysOn Availability Groups option, and then and click OK.

5) Testing Setup by Using a New Vault

a) Create a New Vault

1. Open the SOLIDWORKS PDM Administration tool > right-click the archive server > select the Create new vault option.

2. In the Select Vault Type dialog box, select the SOLIDWORKS PDM Professional Vault option, and then click Next.

3. In the Create New Vault dialog box, enter a name for the test vault, and then click Next.

4. On the Select vault archive folder page, select the vault archive folder, and then click Next.

5. On the Choose database page, enter or select the name of the SQL Server FCI network, and then click Next.

6. On the License Server for Vault page, select the SolidNetWork Licensing (SNL) server, and then click Next.

7. On the Select regional settings page, select the appropriate language option, and then click Next.

8. On the Create the admin user page, either select the Use the default admin password option, or enter a vault-specific password, and then click Next.

9. On the Configure vault page, select the Default option, and then click Next.

10. On the Select configuration details page, click Next.

11. On the Review information page, click Finish to create the vault.

b) Configure SQL Server High Availability Group

1. Open Microsoft SQL Server Management Studio and connect to the SQL Server FCI network name.

2. Expand the Databases node. Nodes for the new vault and the ConisioMasterDb databases appear in the tree.

3. Perform a full backup of the new vault. This action is required to set up the availability group. Otherwise, SQL Server prevents the continuation of the setup process. The ConisioMasterDb database is not required for the database replication.

4. In the Object Explorer, right-click the Always On High Availability node > click New Availability Group Wizard.

5. On the Specify Availability Group Options page, enter a name for the new availability group, and then click Next.

6. Before creating a new availability group, you must perform a full backup for the new vault. Select the new vault and then click Next. The ConisioMasterDb is not required for SOLIDWORKS PDM database replication.

7. On the Specify Replicas page, click Add Replica.

8. In the Connect to Server dialog box, enter the name of the remote SQL Server.

9. On the Specify Replicas page, select Yes from the Readable Secondary menu for the Secondary replica, and then click Next.

10. On the Select Initial Data Synchronization page, you can use the Automatic seeding option or the Full database and log backup option.

- Automatic seeding

- Full database and log backup

11. On the Validation page, review the results, and then click Next to finish the setup. The SOLIDWORKS PDM database replication does not require the listener configuration.

12. Confirm that the information on the Summary page is correct, and then click Finish.

13. On the Results page, review the result status and then click Close to finish the setup.

14. Either refresh Microsoft SQL Server Management Server or reconnect to the SQL FCI server and expand the Databases node.

15. In the Object Explorer, expand Always on High Availability > Availability Groups.

16. Connect to the secondary replica database. 

17. In the Object Explorer, expand the Databases node and notice that the SOLIDWORKS PDM vault database shows the Synchronizing label.

18. In the Object Explorer, expand Always on High Availability > Availability Groups.

c) Enable the Use of a Secondary Replica Machine

1. Open the SOLIDWORKS PDM Administration tool and log in to the vault.

2. In the tree view, expand the Replication node, and then double-click the Database node.

3. Click the Add Server option.

4. In the Server Configuration dialog box, enter the name of the secondary replica and then click Test Settings.

5. If clicking Test Settings does not return an error, then click the Add Member option.

6. Select the groups and users for whom you want to allow use of the secondary replica, and then click OK.

Note: Only assign users and groups in the local area network (LAN) and very low wide area network (WAN) latency to the secondary SQL Server location. Assigning users who are not in a LAN environment to a secondary SQL Server can result in slow performance.

7. In the Server Configuration dialog box, click OK.

Your Feedback Requested
We would like to hear your feedback and suggestions for new topics. After reviewing this document, please take a few minutes to fill out a brief survey. Your feedback will help us create the content that directly addresses your challenges.