New Server Setup
- Download SQL Server from https://www.microsoft.com/en-in/sql-server/sql-server-downloads.
Microsoft provides two specialized free SQL download editions to work on MS SQL server:
Developer – It has all feature which MS SQL server offers but we cannot use it in production. From the learning perspective, is it an ideal candidate to start.
Express: This is also a free SQL server download version but with the limited set of features with no business intelligence applications.
- For this article, we are installing SQL Express. Run the SQL20xx-SSEI-Expr.exe and choose Custom.
- Choose the media location and click Install.
- In the SQL Server Installation Center, choose New SQL Server standalone or add features to an existing installation.
- Accept the license terms and click next.
- Choose if you want to use Microsoft Update and click next.
- Make sure any warnings are addressed then click Next.
- Uncheck Azure Extensions for SQL Server and click Next.
- Make sure Full-Text and semantic Extensions for Search is checked then click Next.
- Choose If you want to use the default instance or Name the instance yourself then click Next.
- Click Next if the service accounts look correct.
- Choose the preferred authentication method then click Next.
- Close out of the setup screen.
- Open the appropriate ports in Windows defender firewall. There is Microsoft documentation on this at https://learn.microsoft.com/en-us/sql/sql-server/install/configure-the-windows-firewall-to-allow-sql-server-access?view=sql-server-ver16.
Adding and Updating a Database
Create the Database
Using standard Microsoft SQL Server Management Studio to create the database to store UAAR DataLog sales.
This can be done by right-clicking on Databases and then select New Database.
In the New Database dialog, choose a name for the database (If the Owner is left to <default>, whoever created the database will have all permissions by default). Click OK.
Run the Database Update Tool
The application DB-Update.exe (see: https://agwarefiles.com/maven/) is a tool we use to create and/or update the database tables. Download and save the most current db-update.zip file in a folder of your choice on your machine.
In a command window, type db-update and hit the “Enter” key, you will be prompted to enter a server name (the name of your SQL Server including the instance), and a database name:
When successfully running the tool in the command window you should see some output that looks like this:
If an error occurs during the database creation or update, the issue will be displayed in the command window.
Add SQL Server or Domain Logins for the users or groups that need access. The DataLog can assign users to the Database and Database Roles but it does not create the Server Logins. Microsoft SQL Server Management Studio or a similar tool must be used to create Server Logins for the appropriate domain users or groups.
Ensure that the first user that will be doing setup has access to the Database. The first user that will be adding other users to the database need to have access to the database and be a member of the UAAR_SysAdmin database role. Administrators are members of both the database and the database role by default. If a different user will be completing the setup, that user must have a Server Login, Database Login, and be a member of the UAAR_SysAdmin role. That can be setup using SQL Server Enterprise Manager.
See more about the roles definitions on the Security page.