How to setup a full version of Microsoft SQL Server to work with ESC

Overview

ESC is designed to work with both express and full versions of Microsoft SQL Server. When installing the SQL Express version that accompanies ESC, dESCO has added in a customized .ini file that tells the installation what settings are required to install it for the software's needs. However, if a user has a full version of SQL Server, these settings will need to be applied to the program to allow ESC to properly work.

Installing SQL Server Standard

If your database is at or around 10 GB or you are looking for more speed when accessing your database from ESC, you may need to install SQL Standard, the full version of SQL. This guide will go over the step-by-step instructions for installing the trial or licensed version. 

• Note: Always backup ESC before beginning this process.

1. Start by downloading the program and let it run the included installer. Once that's done you will see the screen in the image below.

2. From this window, select Installation, then click on New SQL Server stand-alone installation or add features to an existing installation.

3. The setup will check Global Rules and look for product updates. Click Next until you get to the product key portion of the installation. If the product key is blank, then you'll need to add your key. If you haven't purchased yet, you can use the free evaluation edition, which only works for 180 days and then requires a product key to continue functioning.

4. Click Next and check the box on the page next to I accept the license terms and privacy statement. Clicking on the link first will allow you to read the terms.

5. On the next page,  you will see a list of items to install. Make sure to check the box for Database Engine Services. You can also check additional features if desired. Below you can see a list of the typical features that we recommend when installing.

6. Click Next to head to the Instance Configuration. Normally you will select Named instance and enter ESC as the Name. If you already have SQL Express and haven't uninstalled it, use a name that you'll remember. For example, ESC2 or ESC 2024.

7. Click Next and head to the Server Configuration section. Most of the time you can leave this as is and click Next.

8. Select Next to go to the Database Engine Configuration. It's very important to choose Mixed Mode and enter the Password !dESCO?2010 in the two boxes below it. If the Specify SQL Server administrators textbox is empty, click the button Add Current User before clicking Next.

9. If all went well you are now ready to install. Click Install and let the program run until it tells you the installation is complete.

10. Once the installation is complete, there's another step to the configuration. Locate SQL Server 2019 Configuration Manager and open it. The year listed may differ. If you can't find it as seen below, then use this path in File Explorer: C:\Windows\System32 and look for a file with a similar name.

11. Make sure that both Named Pipes and TCP/IP are set to a Status of Enabled.

12. Next, restart the services. You'll be told to do this but we'll be restarting soon, so you can safely exit after making any needed changes.

13. Detaching and attaching your database

• If you are migrating, you can now restart the server and return to the migration process.
• If you already have SQL Server Management Studio, skip to step 14.

If you do not have SQL Server Management Studio, you'll need to download and install it. You can currently download it HERE. There are also installation instructions on the download page. 

14. Next, restart the server. Once you have, we'll move on to detaching and attaching the database. Make sure no one is using ESC. If a user is logged into ESC, they'll need to log out for you to be able to continue.

15. Once everyone is logged out of ESC, launch SQL Server Management Studio (SSMS). You'll see a screen that prompts you to Connect to Server. Your server name will be ServerName\InstanceName refer to the example below. 

• Note: You may need to use the drop-down and browse for more if you don't see yours listed right away. If you uninstalled SQL Express before installing SQL Standard, skip to step 20.

16. You will be looking for the instance you were using before, not the one you just created. Set the Authentication to SQL Server Authentication, the Login: to sa, set the Password to !dESCO?2010 then click Connect.

17. Locate your database under Databases. Right-click on it and choose Tasks, then select Detach.

18. Next, check the box for Drop Connections and Update Statistics and click OK. After clicking OK you may need to wait while your database is detached.

19. Once the window disappears, you'll see the database isn't on the list anymore. Now, click File, then choose Disconnect Object Explorer, then choose File Connect Object Explorer.

20. Now it's time to go to your new instance. Select it under the Server name. Set the Authentication to SQL Server Authentication. Then enter the Login: to sa and the Password to !dESCO?2010 and click Connect.

21. Next, attach the database by right-clicking on Databases and selecting Attach.

22. In the box that pops up, click Add and find your database. Once you have it selected click OK.

23. The Attach Database dialog box will now show you some details. Click OK and wait while the database is attached. Once this is completed, you should see the instance name didn't change. 

24. If the instance name did change, then you'll need to tell the workstation to contact the new instance. You may also need to configure the firewall for the new instance. To do that you'll need to allow C:\Program Files\Microsoft SQL Server\MSSQL15.ESC\MSSQL\Binn\sqlservr.exe through. Your number may differ from MSSQL14.ESC and ESC will be your instance name, so it might be listed as ESC2 or ESC2024, etc.

25. You'll now need to go to each workstation, cancel out of the login screen, and click File, then choose Open Company. In the window that appears, select the server name that ends in your new instance name. Then fill in the User Name and Password with the user's valid ESC login credentials. Use the database drop-down menu to choose the proper database and click OK.

Once you have connected to the database, click the X in the upper right corner to close ESC. It will now remember your new login path. Once you have logged into each workstation using the proper instance and database, you're all set! 

Additional Info

ESC Technical Support cannot provide installation assistance with full versions of SQL. However, once the software has been installed on a computer, if you have any issues applying these settings, we will be able to adjust and confirm that these settings have been properly applied.

• Note: If you are upgrading from SQL Server Express, you need to make sure the full version of Microsoft SQL Server is equal to or newer than the version ESC is currently running on. You can view the current version in ESC by going to Help → About.