SQL Server

1. Linux

See https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-ubuntu.

1.1. SQL Server 2017

1.1.1. Installation

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
sudo add-apt-repository "$(curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server.list)"

sudo apt-get update
sudo apt-get install mssql-server
sudo /opt/mssql/bin/mssql-conf setup

sudo apt-get install -y mssql-tools unixodbc-dev
sqlcmd -S localhost -U SA -P '********'
???

2. Windows

2.1. Microsoft SQL Server editions

Microsoft SQL Server Express editions

Edition                         Version         Date Published  Support End Date
------------------------------  ------------    --------------  ----------------
SQL Server 2005 Express         1               2005-11-07      2016-04-12
SQL Server 2008 Express         10.00.1600.22   2009-02-08      2019-07-09
SQL Server 2008 R2 RTM Express  10.50.1600.1    2010-04-16      2019-07-09
SQL Server 2008 R2 SP1 Express  10.50.2500.0    2011-07-12      2019-07-09
SQL Server 2008 R2 SP2 Express  10.50.4000.0    2012-07-26      2019-07-09
SQL Server 2012 Express         11.0.2100.60    2012-05-14      2022-07-12
SQL Server 2014 Express         12.0.2000.8     2014-06-25      2024-07-09
SQL Server 2016 Express         13.0            2016-06-01      2026-07-14
SQL Server 2017 Express         14.0            2017-09-29      2027-10-12
SQL Server 2019 Express         15.0            2019-11-04      2030-01-08
SQL Server 2022 Express         16.0            2022-11-16      2033-01-11

2.2. Linked Server

Start Microsoft SQL Server Management Studio.
- Select Start > Programs > Microsoft SQL Server 2017 > SQL Server 2017 Management Studio.
Connect to the local SQL Server with the sa account and SQL Server Authentication.
Right-click Server Objects and select New > Linked Server.
Select the General page.
Type <Hostname> in the Linked server field.

Type the hostname, a comma and the port number in the Linked server field.

Linked server:                  <Hostname>
Server type:
● SQL Server
○ Other data source
    Provider:                   Microsoft OLE DB Provider for SQL Server
    Product name:
    Data source:
    Provider string:
    Location:
    Catalog:

Select the Security page.

Choose Be made using the login’s current security context.

For a login not defined in the list above, connections will:
○ Not be made
○ Be made without using a security context
● Be made using the login’s current security context
○ Be made using this security context
    Remote login:
    With password:

Click OK.
Close Microsoft SQL Server Management Studio.

2.3. SQL Server 2017 Express

Note: Microsoft SQL Server 2017 Express also installs the following.

Microsoft ODBC Driver 13 for SQL Server
Microsoft SQL Server 2012 Native Client
Microsoft SQL Server 2017 (64-bit)
Microsoft SQL Server 2017 Setup (English)
Microsoft SQL Server 2017 T-SQL Language Service
Microsoft Visual C++ 2015 Redistributable (x64) - 14.0.23506
Microsoft VSS Writer for SQL Server 2017

2.3.1. Installation

Note: Database are pid-hph, pid-mkt, pid-mla, pid-wpa.
Note: ORC computers are SMRU-ORC-MKT, SMRU-ORC-MLA, SMRU-ORC-WPA, SMRU-ORC-MSL.
Note: PMS servers are SMRU-PMS-MKT, SMRU-PMS-MLA, SMRU-PMS-WPA, SMRU-PMS-MSL.

Run the SQLServer2017-SSEI-Expr.exe with administrative privileges.
Select Custom.
```
C:\SQLServer2017Media
```
Click Install.
Click New SQL Server standalone installation or add features to an existing installation.
Check I accept the license terms.
Click Next.
Click Next.
Click Unselect All.

Check Database Engine Services.

Instance Features
    ■ Database Engine Services
        □ SQL Server Replication
        □ Machine Learning Services (In-Database)
           □ R
           □ Python
        □ Full-Text and Semantic Extractions for Search
        □ PolyBase Query Service for External Data
Shared Features
    □ Client Tools Connectivity
    □ Client Tools Backwards Compatibility
    □ Client Tools SDK
    □ SQL Client Connectivity SDK
    □ LocalDB
Redistributable Features

Instance root directory:        C:\Program Files\Microsoft SQL Server
Shared feature directory:       C:\Program Files\Microsoft SQL Server
Shared feature directory (x86): C:\Program Files (x86)\Microsoft SQL Server

Click Next.
Choose Named instance.
Type SQLEXPRESS for the Named Instance.

Type SQLEXPRESS for the Instance ID.

○ Default instance
● Named instance:               SQLEXPRESS
Instance ID:                    SQLEXPRESS
SQL Server directory:           C:\Program Files\Microsoft SQL Server\MSSQL14.<ID>

Click Next.

Select the Service Accounts tab.

Service                         Account Name                    Password        Startup Type
--------------------------      ---------------------------     --------        ------------
SQL Server Database Engine      NT Service\MSSQL$SQLEXPRESS                     Automatic
SLQ Server Browser              NT AUTHORITY\LOCAL SERVICE                      Disabled

Select the Collation tab.

Database Engine:                SQL_Latin1_General_CP1_CI_AS

Click Next.
Select the Server Configuration tab.
Choose Mixed Mode (SQL Server authentication and Windows authentication).
Type the password of the sa account in the Enter password field.

Type the password of the sa account in the Confirm password field.

○ Windows authentication mode
● Mixed Mode (SQL Server authentication and Windows authentication)
Enter password:                 ********
Confirm password:               ********

Optional: Select the Data Directories tab.

Optional: Select the User Instances tab.

■ Users are allowed to run a separate instance of the Database Engine

Optional: Select the FILESTREAM tab.

□ Enable FILESTREAM for Transact-SQL access
  □ Enable FILESTREAM for file I/O access
    Windows share name: SQLEXPRESS
      □ Allow remote clients access to FILESTREAM data

Click Next.
Wait about 10 minutes for the installation to finish.
Optional: Click OK at the Computer restart required message.
Click Close.
Close the SQL Server Installation Center window.
Remove the C:\SQLServer2017Media folder.
Restart the computer.

2.3.2. Configuration

Continue with SQL Server Configuration Manager.

2.4. SQL Server 2019 Express

2.4.1. Installation

Note: Database are pid-hph, pid-mkt, pid-mla, pid-wpa.
Note: PMS servers are SMRU-PMS-MKT, SMRU-PMS-MSL, SMRU-PMS-WPA.

Run the SQLServer2019-SSEI-Expr.exe with administrative privileges.
Select Custom.
```
C:\SQL2019
```
Click Install.
Select New SQL Server stand-alone installation or add features to an existing installation.
Check I accept the license terms and Privacy Statement.
Click Next.
Click Next.
Click Next.
Click Unselect All.

Check Database Engine Services.

Instance Features
    ■ Database Engine Services
        □ SQL Server Replication
        □ Machine Learning Services and Language Extensions
           □ R
           □ Python
           □ Java
        □ Full-Text and Semantic Extractions for Search
        □ PolyBase Query Service for External Data
           □ Java connector for HDFS data sources
Shared Features
    □ Client Tools Connectivity
    □ Client Tools Backwards Compatibility
    □ Client Tools SDK
    □ SQL Client Connectivity SDK
    □ LocalDB
Redistributable Features

Instance root directory:        C:\Program Files\Microsoft SQL Server
Shared feature directory:       C:\Program Files\Microsoft SQL Server
Shared feature directory (x86): C:\Program Files (x86)\Microsoft SQL Server

Click Next.
Choose Named instance.
Type SQLExpress for the Named instance.

Type SQLEXPRESS for the Instance ID.

○ Default instance
● Named instance:               SQLExpress

Instance ID:                    SQLEXPRESS
SQL Server directory:           C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS

Click Next.

Select the Service Accounts tab.

Service                         Account Name                    Password        Startup Type
--------------------------      ---------------------------     --------        ------------
SQL Server Database Engine      NT Service\MSSQL$SQLEXPRESS                     Automatic
SLQ Server Browser              NT AUTHORITY\LOCAL SERVICE                      Disabled

□ Grant Perform Volume Maintenance Task privilege to SQL Server Database Engine Service

Select the Collation tab.

Database Engine:                SQL_Latin1_General_CP1_CI_AS

Click Next.
Select the Server Configuration tab.
Choose Mixed Mode (SQL Server authentication and Windows authentication).
Type the password of the sa account in the Enter password field.

Type the password of the sa account in the Confirm password field.

○ Windows authentication mode
● Mixed Mode (SQL Server authentication and Windows authentication)
Enter password:                 ********
Confirm password:               ********

Optional: Select the Data Directories tab.
Optional: Select the TempDB tab.
Optional: Select the Memory tab.

Optional: Select the User Instances tab.

■ Users are allowed to run a separate instance of the Database Engine

Optional: Select the FILESTREAM tab.

□ Enable FILESTREAM for Transact-SQL access
  □ Enable FILESTREAM for file I/O access
    Windows share name: SQLEXPRESS
      □ Allow remote clients access to FILESTREAM data

Click Next.
Wait about 10 minutes for the installation to finish.
Optional: Click OK at the Computer restart required message.
Click Close.
Close the SQL Server Installation Center window.
Remove the C:\SQL2019 folder.
Restart the computer.

2.4.2. Configuration

Continue with SQL Server Configuration Manager.

2.5. SQL Server 2022 Express

2.5.1. Installation

Note: Database are pid-hph, pid-mkt, pid-mla, pid-wpa.
Note: PMS servers are SMRU-PMS-MKT, SMRU-PMS-MSL, SMRU-PMS-WPA.

Run the SQL2022-SSEI-Expr-16.2211.5693.3.exe with administrative privileges.
Select Custom.
```
C:\SQL2022
```
Click Install.
Select New SQL Server standalone installation or add features to an existing installation.
Check I accept the license terms and Privacy Statement.
Click Next.
Click Next.
Uncheck Azure Extension for SQL Server.
Click Next.
Click Unselect All.

Check Database Engine Services.

Instance Features
    ■ Database Engine Services
        □ SQL Server Replication
        □ Machine Learning Services and Language Extensions
        □ Full-Text and Semantic Extractions for Search
        □ PolyBase Query Service for External Data
Shared Features
    □ LocalDB
Redistributable Features

Instance root directory:        C:\Program Files\Microsoft SQL Server
Shared feature directory:       C:\Program Files\Microsoft SQL Server
Shared feature directory (x86): C:\Program Files (x86)\Microsoft SQL Server

Click Next.
Choose Named instance.
Type SQLExpress for the Named instance.

Type SQLEXPRESS for the Instance ID.

○ Default instance
● Named instance:               SQLExpress

Instance ID:                    SQLEXPRESS
SQL Server directory:           C:\Program Files\Microsoft SQL Server\MSSQL16.SQLEXPRESS

Click Next.

Select the Service Accounts tab.

Service                         Account Name                    Password        Startup Type
--------------------------      ---------------------------     --------        ------------
SQL Server Database Engine      NT Service\MSSQL$SQLEXPRESS                     Automatic
SLQ Server Browser              NT AUTHORITY\LOCAL SERVICE                      Disabled

□ Grant Perform Volume Maintenance Task privilege to SQL Server Database Engine Service

Select the Collation tab.

Database Engine:                SQL_Latin1_General_CP1_CI_AS

Click Next.
Select the Server Configuration tab.
Choose Mixed Mode (SQL Server authentication and Windows authentication).
[note] Note: When connected to the domain, the password must meet the domain’s strong password rules.
Type the password of the sa account in the Enter password field.

Type the password of the sa account in the Confirm password field.

○ Windows authentication mode
● Mixed Mode (SQL Server authentication and Windows authentication)
Enter password:                 ********
Confirm password:               ********

Optional: Select the Data Directories tab.
Optional: Select the TempDB tab.
Optional: Select the Memory tab.

Optional: Select the User Instances tab.

■ Users are allowed to run a separate instance of the Database Engine

Optional: Select the FILESTREAM tab.

□ Enable FILESTREAM for Transact-SQL access
  □ Enable FILESTREAM for file I/O access
    Windows share name: SQLEXPRESS
      □ Allow remote clients access to FILESTREAM data

Click Next.
Wait about 10 minutes for the installation to finish.
Optional: Click OK at the Computer restart required message.
Click Close.
Close the SQL Server Installation Center window.
Remove the C:\SQL2022 folder.
Restart the computer.

2.5.2. Configuration

Continue with SQL Server Configuration Manager.

2.6. SQL Server Management Studio

See Official website.

2.6.1. Installation

Run the SSMS-Setup-ENU-19.0.1.exe file with administrative privileges.
```
C:\Program Files (x86)\Microsoft SQL Server Management Studio 19
```
Click Install.
Click Close.
Restart the computer.

2.7. Add AD group for DM

Log on as SMRU\<User> in <Database server>.
Start Microsoft SQL Server Management Studio.
- Select Start > Programs > Microsoft SQL Server Tools 18 > Microsoft SQL Server Management Studio 18.
Connect to the local SQL Server with the sa account and SQL Server Authentication.
Expand <Database server> > Security.
Right-click Logins and select New login.
Select General tab.
Click Search.
Click Object Types.
Type your domain user name and password.
Click OK.
Check Groups.
Click OK.
Click Locations.
Select Entire Directory > smru.shoklo-unit.com.
Click OK.
Type Data-DbDev name in Enter the object name to select field.
Click Check Names.
Click OK.
Select Server Roles tab.
Check Sysadmin.
Select User Mapping.
Check model and make Default Schema dbo.
Check db_owner under Database role membership for: model.
Optional: Check <database> and make Default Schema dbo.
Optional: Check db_owner under Database role membership for: <database>.
Check tempdb and make Default Schema dbo.
Check db_owner under Database role membership for: tempdb.
Click OK.
Close Microsoft SQL Server Management Studio.

2.8. Assigning System Administrator Role

Log on as SMRU\<User> in SMRU-DBD-MST, SMRU-DBP-MST.
Start Microsoft SQL Server Management Studio.
- Select Start > Programs > Microsoft SQL Server 2017 > SQL Server 2017 Management Studio.
Connect to the local SQL Server with the sa account and SQL Server Authentication.
Expand SMRU-DBD-MST > Security.
Right-click Logins and select New login.
Select General tab.
Click Search.
Click Locations.
Select Entire Directory > smru.shoklo-unit.com.
Click OK.
Type <User> name in Enter the object name to select field.
Click Check Names.
Click OK.
Select Server Roles tab.
Check Sysadmin.
Click OK.
Close Microsoft SQL Server Management Studio.

2.9. Creation of database

Log on as Administrator.

Start SQL Server Management Studio.

Server type:            Database engine
Server name:            <PMS server or ORC computer>\SQLEXPRESS         or      <PMS server or ORC computer>,1433
Authentication:         SQL Server Authentication
    Login:              sa
    Password:           ********                (sa account)
                        □ Remember password

Click Connect.
Right-click Databases and select New Database.

Select the General page.

Database name:          pid-<site>
Owner:                  <default>

Optional: Select the Options page.
Optional: Select the Filegroups page.
Click OK.
Click the Disconnect button.
Close SQL Server Management Studio.

2.10. Creation of Functions

Start SQL Server Management Studio.

Server type:            Database engine
Server name:            <PMS server or ORC computer>\SQLEXPRESS         or      <ORC computer or ORC computer>\SQLEXPRESS
Authentication:         SQL Server Authentication
    Login:              sa
    Password:           ********                (sa account)
                        □ Remember password

Click Connect.
Select File > New > Query with Current Connection.

Paste the following code in the query.

USE [master]
GO
/****** Object:  UserDefinedFunction [dbo].[SplitString]    Script Date: 2020-07-23 15:58:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




-- Parameter @Input: Any string that contain separator
-- Paramter @Character : The string spliter (comma , tab , colon , semicolon , newline , white space and so on)
-- Sample to use this function :
-- SELECT Item
-- FROM dbo.SplitString('Apple,Mango,Banana,Guava', ',')
---------------------------------------------------
-- IF EXISTS(SELECT * FROM #tmp)
--  DROP TABLE #tmp ;

-- SELECT 'Apple' AS Item INTO #tmp;
-- SELECT * FROM
-- #tmp A INNER JOIN (
-- SELECT Item
-- FROM dbo.SplitString('Apple,Mango,Banana,Guava', ',')
-- ) B ON B.Item = A.Item ;
---------------------------------------------------
-- Create by Manop Y. on 2019-11-20
---------------------------------------------------

CREATE FUNCTION [dbo].[SplitString]
(
      @Input NVARCHAR(MAX),
      @Character CHAR(1)
)
RETURNS @Output TABLE (
      Item NVARCHAR(1000)
)
AS
BEGIN
      DECLARE @StartIndex INT, @EndIndex INT

      SET @StartIndex = 1
      IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
      BEGIN
            SET @Input = @Input + @Character
      END

      WHILE CHARINDEX(@Character, @Input) > 0
      BEGIN
            SET @EndIndex = CHARINDEX(@Character, @Input)

            INSERT INTO @Output(Item)
            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
      END

      RETURN
END
GO

Select Query > Execute.
Close SQL Server Management Studio.

2.11. Creation of Stored Procedure

2.11.1. spr_BackupDatabases

Start SQL Server Management Studio.

Server type:            Database engine
Server name:            <PMS server or ORC computer>\SQLEXPRESS
Authentication:         SQL Server Authentication
    Login:              sa
    Password:           ********                (sa account)
                        □ Remember password

Click Connect.
Select File > New > Query with Current Connection.

Paste the following code in the query.

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[spr_BackupDatabases]    Script Date: 2020-07-23 16:07:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROC [dbo].[spr_BackupDatabases]
@FilePath VARCHAR(255) =  '', --'C:\SQLDB_BACKUP\ is default directory',
@DBNameList VARCHAR(255) = '' --If not specific any name that mean backup all database ex: 'pid-mkt,pid-wpa,pid-mla'
AS
BEGIN
        DECLARE @FileName VARCHAR(512)
        DECLARE @SQLCmd NVARCHAR(2024)
        DECLARE @DBName VARCHAR(50)
        IF @FilePath = ''
                --SET @FilePath = 'C:\SQLDB_BACKUP\'
                SET @FilePath = 'D:\SQLDB_BACKUP'

    IF @DBNameList = '' -- Not specific procedure parameter
                BEGIN
                        DECLARE DB_CURSOR CURSOR FOR

                        SELECT name as DatabaseName FROM master.sys.databases
                        WHERE name NOT IN ('master','tempdb','msdb','model','ReportServer','ReportServerTempDB')-- AND name NOT LIKE 'pid-%'
                        ORDER BY Name

                        OPEN DB_CURSOR
                        FETCH NEXT FROM DB_CURSOR INTO @DBName
                        WHILE @@FETCH_STATUS = 0
                        BEGIN
                                SET @FileName = @FilePath+'\'+@DBName+'.bak' -- +'_' + Convert(varchar(8), GETDATE(), 112) + '.bak'
                                SET @SQLCmd = 'EXECUTE AS LOGIN=''sa''; BACKUP DATABASE ['+@DBName+'] TO  DISK = '''+@FileName+''' WITH NOFORMAT, NOINIT,  NAME = N''DELTA_DB_Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'

                                PRINT @SQLCmd ;
                                EXEC sp_executesql @SQLCmd ;
                                SET @SQLCmd = ''
                          FETCH NEXT FROM DB_CURSOR INTO @DBName
                        END

                        CLOSE DB_CURSOR
                        DEALLOCATE DB_CURSOR
                END
        ELSE
                BEGIN
                        DECLARE DB_CURSOR CURSOR FOR
                        SELECT Item
                        FROM dbo.SplitString(@DBNameList, ',')

                        OPEN DB_CURSOR
                        FETCH NEXT FROM DB_CURSOR INTO @DBName
                        WHILE @@FETCH_STATUS = 0
                        BEGIN
                                SET @FileName = @FilePath+'\'+@DBName+'.bak' --'_' + Convert(varchar(8), GETDATE(), 112) + '.bak'
                                SET @SQLCmd = 'EXECUTE AS LOGIN=''sa''; BACKUP DATABASE ['+@DBName+'] TO  DISK = '''+@FileName+''' WITH NOFORMAT, NOINIT,  NAME = N''DELTA_DB_Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'

                                PRINT @SQLCmd ;
                                EXEC sp_executesql @SQLCmd ;
                                SET @SQLCmd = ''
                          FETCH NEXT FROM DB_CURSOR INTO @DBName
                        END

                        CLOSE DB_CURSOR
                        DEALLOCATE DB_CURSOR
                END

        REVERT ;
END

--exec spr_BackupDatabases @DBNameList='pid-mla'
GO

Select Query > Execute.
Close SQL Server Management Studio.

2.11.2. Patient_Update

Start SQL Server Management Studio.

Server type:            Database engine
Server name:            <PMS server or ORC computer>\SQLEXPRESS
Authentication:         SQL Server Authentication
    Login:              sa
    Password:           ********                (sa account)
                        □ Remember password

Click Connect.
Select File > New > Query with Current Connection.

Paste the following code in the query.

USE [<Database name>]
GO
/****** Object:  StoredProcedure [dbo].[Patient_Update]    Script Date: 2020-07-27 14:16:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Patient_Update]
        -- Add the parameters for the stored procedure here
        @IDCard nvarchar(9),
        @FullName nvarchar(40),
        @BurmeseName nvarchar(40),
        @MotherName nvarchar(40),
        @FatherName nvarchar(40),
        @Sex INT,
        @Age INT,
        @Month INT,
        @Day INT,
        @Address nvarchar(40),
        @Villages nvarchar(40),
        @HouseNo nvarchar(40)
AS
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

    -- Insert statements for procedure here
        UPDATE Patients

        SET
        FullName = @FullName,
        BurmeseName = @BurmeseName,
        MotherName = @MotherName,
        FatherName = @FatherName,
        Sex = @Sex,
        Age = @Age - DATEDIFF(year, AdmissionDate, GETDATE()), -- Compute the age on AdmissionDate based on currentAge given
        Month = @Month,
        Day = @Day,
        Address = @Address,
        Villages = @Villages,
        HouseNo = @HouseNo

        WHERE IDCard = @IDCard
END
GO

Select Query > Execute.
Permission configuration for dbwriter.
- Right-click Databases > <Database name> > Security > dbwriter and select Properties.
- Select Securables.
- Click Search.
- Choose All objects belonging to the schema.
- Select dbo in Schema name dropdown list.
- Click OK.
- Select Patient_Update.
- Check Execute in Grant column.
- Click OK.
Close SQL Server Management Studio.

2.12. Creation of user account

2.12.1. dbbackup

Start SQL Server Management Studio with administrative privileges.

Server type:            Database engine
Server name:            <PMS server or ORC computer>\SQLEXPRESS
Authentication:         SQL Server Authentication
    Login:              sa
    Password:           ********                (sa account)
                        □ Remember password

Click Connect.
Right-click Security > Logins and select New Login.

Select the General page.

Login name:                     dbbackup

Choose SQL Server authentication.

Password:                       ********
Confirm password:               ********

□ Enforce password policy
□ Enforce password expiration
□ User must change password at next logon

Uncheck Map to Credential.

Default database:               <Database>
Default language:               <default>

Select the Server Roles page.

Server roles:
■ public
■ sysadmin              # This is needed only for MLA and MSL sites and SMRU-DBP-MST servers.

Select the User Mapping page.

Users mapped to this login:
■ <Database>            dbbackup

Database role membership for: <Database>
■ db_backupoperator
■ public

Select the Securables page.
Select the Status page.
Click OK.
Click the Disconnect button.
Close SQL Server Management Studio.

2.12.2. dbreader

Start SQL Server Management Studio with administrative privileges.

Server type:            Database engine
Server name:            <PMS server or ORC computer>\SQLEXPRESS
Authentication:         SQL Server Authentication
    Login:              sa
    Password:           ********                (sa account)
                        □ Remember password

Click Connect.
Right-click Security > Logins and select New Login.

Select the General page.

Login name:                     dbreader

Choose SQL Server authentication.

Password:                       ********
Confirm password:               ********

□ Enforce password policy
□ Enforce password expiration
□ User must change password at next logon

Uncheck Map to Credential.

Default database:               <Database>
Default language:               <default>

Select the Server Roles page.
```
Server roles:
■ public
```

Select the User Mapping page.

Users mapped to this login:
■ <Database>            dbreader

Database role membership for: <Database>
■ db_datareader
■ public

Select the Securables page.
Select the Status page.
Click OK.
Click the Disconnect button.
Close SQL Server Management Studio.

2.12.3. dbwriter

Start SQL Server Management Studio with administrative privileges.

Server type:            Database engine
Server name:            <PMS server or ORC computer>\SQLEXPRESS
Authentication:         SQL Server Authentication
    Login:              sa
    Password:           ********                (sa account)
                        □ Remember password

Click Connect.
Right-click Security > Logins and select New Login.

Select the General page.

Login name:                     dbwriter

Choose SQL Server authentication.

Password:                       ********
Confirm password:               ********

□ Enforce password policy
□ Enforce password expiration
□ User must change password at next logon

Uncheck Map to Credential.

Default database:               <Database>
Default language:               <default>

Select the Server Roles page.
```
Server roles:
■ public
```

Select the User Mapping page.

Users mapped to this login:
■ <Database>            dbwriter

Database role membership for: <Database>
■ db_datareader
■ db_datawriter
■ public

Select the Securables page.
Select the Status page.
Click OK.
Click the Disconnect button.
Close SQL Server Management Studio.

2.12.4. dm<User>

Start SQL Server Management Studio with administrative privileges.

Server type:            Database engine
Server name:            <PMS server or ORC computer>\SQLEXPRESS
Authentication:         SQL Server Authentication
    Login:              sa
    Password:           ********                (sa account)
                        □ Remember password

Click Connect.
Right-click Security > Logins and select New Login.

Select the General page.

Login name:                     dm<User>

Choose SQL Server authentication.

Password:                       ********
Confirm password:               ********

□ Enforce password policy
□ Enforce password expiration
□ User must change password at next logon

Uncheck Map to Credential.

Default database:               <Database>
Default language:               <default>

Select the Server Roles page.
```
Server roles:
■ dbcreator
■ public
```
Select the User Mapping page.

Select the Securables page.
Select the Status page.
Click OK.
Click the Disconnect button.
Close SQL Server Management Studio.

2.13. Database Backup

Start SQL Server Management Studio.

Server type:            Database Engine
Server name:            <PMS server or ORC computer>\SQLEXPRESS
Authentication:         Windows Authentication
□ Remember password

Click Connect.
Right-click Databases > <Database name> and select Tasks > Back Up.
Select the General page.
Click Remove.
Click Add.

Select the empty C:\Tmp\<File name>.bak file.

Database:               <Database name>
Recovery model:         SIMPLE
Backup type:            Full
    □ Copy-only Backup
Backup component:
    ● Database
    ○ Files and filegroups


Name:                   <Database name>-Full Database Backup
Description:
Backup set will expire:
    ● After:            0 days
    ○ On:

Destination:
Back up to:             ● Disk
○ Tape

    C:\Tmp\<File name>.bak

Select the Options page.

● Back up to the existing media set
    ○ Append to the existing backup set
    ● Overwrite all existing backup sets
    □ Check media set name and backup set expiration
        Media set name:
○ Back up to a new media set, and erase all existing backup sets
    New media set name:
    New media set description:

■ Verify backup when finished
■ Perform checksum before writing to media
□ Continue on error

Set backup compression: Do not compress backup

Click OK.
Wait.
Click OK at the The backup of database completed successfully message.
Close SQL Server Management Studio.

2.14. Database Restore

Optional: Extract <Database>_backup_<Date>.7z in to C:\Tmp folder.

Start SQL Server Management Studio.

Server type:            Database Engine
Server name:            <PMS server or ORC computer>\SQLEXPRESS
Authentication:         Windows Authentication
□ Remember password

Click Connect.

Right-click Databases and select Restore Database.
Select the General page.
Choose Device.

Click the … button.
Select File for the backup media.
Click Add.
Select the <File name>.bak file.
Click OK.
Click OK.

Check <Database>_backup.

Source
  ○ Database:
  ● Device:             C:\Tmp\<File name>.bak
     Database           <Database>

Destination
  Database:             <Database>
  Restore to:           The last backup taken (<Data and Time>)

■ <Database>_backup Database Full <PMS server or ORC computer>\SQLEXPRESS <Database>

Select the Options page.

Restore options
■ Overwrite the existing database (WITH REPLACE)
□ Preserve the replication settings (WITH KEEP_REPLICATION)
□ Prompt before restoring each backup
□ Restrict access to the restored database (WITH RESTRICTED_USER)

● Leave the database ready to use by rolling back uncommitted transactions
○ Leave the database non-operational, and do not roll back uncommitted transactions
○ Leave the database in read-only mode

Click OK.
Wait.
Click OK at the The restore of database completed successfully message.
Close SQL Server Management Studio.

2.15. SQL Server Configuration Manager

Start SQL Server Configuration Manager with administrative privileges.
- Select Start > Programs > Microsoft SQL Server <Year> > Configuration Tools > SQL Server <Year> Configuration Manager.
Expand SQL Server Network Configuration.
Select Protocols for SQLEXPRESS.
Right-click TCP/IP and select Properties.
Select the Protocol tab.
Select Enabled | Yes.

Select Listen All | No.

General
Enabled:                        Yes
Keep Alive:                     30000
Listen All:                     No

Select the IP Addresses tab.
Select IP1 | Enabled | Yes.
Type the host name of this computer for IP1 | IP Address.
Clear IP1 | TCP Dynamic Ports.

Set IP1 | TCP Port to 1433.

IP1
Active:                         Yes
Enabled:                        Yes
IP Address:                     SMRU-PMS-<Site> or SMRU-ORC-<Site>
TCP Dynamic Ports:
TCP Port:                       1433

Click Apply.
Click OK at the Warning message about any changes will not take effect until the service is stopped and restarted.
Click OK.

Select SQL Server Services.
Right-click SQL Server (SQLEXPRESS) and select Restart.
Close SQL Server Configuration Manager.

2.15.1. Fixes

Cannot connect to WMI provider.
You do not have permission or the server is unreachable.
Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager.
Invalid class (0x80041010).

See https://learn.microsoft.com/en-us/troubleshoot/sql/tools/error-message-when-you-open-configuration-manager.

Enter the following commands at a Command Prompt with administrative privileges.

cd %programfiles(x86)%\Microsoft SQL Server\140\Shared
mofcomp "sqlmgmproviderxpsp2up.mof"

Enter the following commands at a PowerShell Command Prompt with administrative privileges.
```
Get-Service winmgmt | Restart-Service -Force
```

SQL Server

Home

1. Linux

1.1. SQL Server 2017

1.1.1. Installation

2. Windows

2.1. Microsoft SQL Server editions

2.2. Linked Server

2.3. SQL Server 2017 Express

2.3.1. Installation

2.3.2. Configuration

2.4. SQL Server 2019 Express

2.4.1. Installation

2.4.2. Configuration

2.5. SQL Server 2022 Express

2.5.1. Installation

2.5.2. Configuration

2.6. SQL Server Management Studio

2.6.1. Installation

2.7. Add AD group for DM

2.8. Assigning System Administrator Role

2.9. Creation of database

2.10. Creation of Functions

2.11. Creation of Stored Procedure

2.11.1. spr_BackupDatabases

2.11.2. Patient_Update

2.12. Creation of user account

2.12.1. dbbackup

2.12.2. dbreader

2.12.3. dbwriter

2.12.4. dm<User>

2.13. Database Backup

2.14. Database Restore

2.15. SQL Server Configuration Manager

2.15.1. Fixes