- last edited on by
Mercedes_O
Hello,
Introduction:
Guide is for people interested to migrate their lansweeper database running on MSSQL Standalone or HA - MSSQL Failover Cluster Instances to running in MSSQL AlwaysOn AvailabilityGroup.
Fair Warning:
This is a non-standard instalation and if unsure of what you doing it's better to stop before proceeding. This setup is currently not tested by Lansweeper scenario and support may have problems troubleshooting if you overcome issues with running on it.
Not-included:
Instruction on how to create or configure application server, web server or sql server is outside of scope of this guide, but following configuration was made beforehand.
| Server | Role |
|
lans01
|
Lansweeper web and scanning server
|
|
sqlnode01
|
Node01 of SQL AoAG |
|
sqlnode02
|
Node02 of SQL AoAG
|
|
sqlnode0x\instance01
|
Name of instance running on sqlnode0x
|
|
availabilitygroup-01
|
Availability Group of sql nodes
|
|
listener-01
|
Availability Group Listener of sql nodes.
|
Steps:
- Perform Lansweeper update to the most recent up-to-date version available with guide https://community.lansweeper.com/t5/lansweeper-maintenance/update-lansweeper-classic/ta-p/64338
-
stop Lansweeper service
- stop IIS Express service or stop IIS site responsible for Lansweeper
- perform full backup of Lansweeperdb
/** Run with T-SQL **/
USE lansweeperdb;
GO
BACKUP DATABASE lansweeperdb
TO DISK = 'c:\tmp\lansweeperdb.bak'
WITH FORMAT,
MEDIANAME = 'Lansweeperdb',
NAME = 'Full Backup of lansweeperdb';
GO
- detach old database to ommit uncorrect database point on service
/** Run with T-SQL **/
use master;
go
sp_detach_db lansweeperdb
- move backup to first sqlnode01
robocopy c:\tmp\ \\sqlnode01\temp\ lansweeperdb.bak
- If servers are already deployed to prod, perform planned failover to sqlnode02, as we need to reboot sqlnode01 later on.
--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
:Connect sqlnode02\instance01
:Connect sqlnode01\instance01
ALTER AVAILABILITY GROUP [availabilitygroup-01] FAILOVER;
GO
GO
- On sqlnode01 - perform standalone sql database creation with lansweepersetup.exe with following guide point 4-5. https://community.lansweeper.com/t5/lansweeper-maintenance/move-your-sql-server-database-to-a-differ...
This will install lansweeperdb on your SQL and install .NET 4.8 on your server and require restartfor Lansweeper Classic, run .\LansweeperSetup.exe with /classic flag - on sqlnode01 - restore lansweeperdb with backup
USE [master]
RESTORE DATABASE [lansweeperdb]
FROM DISK = N'C:\tmp\lansweeper.bak'
WITH FILE = 1,
MOVE N'lansweeperdb' TO N'D:\path\to\your\sql\data\lansweeperdb.mdf',
MOVE N'lansweeperdb_log' TO N'D:\path\to\your\sql\Log\lansweeperdb_log.LDF',
NOUNLOAD, REPLACE, STATS = 5
GO
- ​on sqlnode01 - set database to Full
USE [master] ;
ALTER DATABASE [lansweeperdb] SET RECOVERY FULL ;
- on sqlnode01 - perform full backup to start log chain
USE lansweeperdb;
GO
BACKUP DATABASE lansweeperdb
TO DISK = 'c:\tmp\lansweeperdb2.bak'
WITH FORMAT,
MEDIANAME = 'Lansweeperdb',
NAME = 'Full Backup of lansweeperdb';
GO
- on sqlnode01 - modify password in script below to your needs and restore user
USE lansweeperdb;
GO
DROP SCHEMA lansweeperuser;
GO
DROP USER lansweeperuser;
GO
EXECUTE sp_droplogin
lansweeperuser;
GO
USE MASTER;
GO
EXEC sp_addlogin
'lansweeperuser',
'hard2guessuserpassword!',
'lansweeperdb',
[English];
GO
USE lansweeperdb;
GO
EXEC sp_grantdbaccess
'lansweeperuser',
'lansweeperuser';
GO
EXEC sp_addrolemember
[db_owner],
'lansweeperuser';
GO
- on sqlnode01 - Query SID of lansweeperuser and write down the SID
USE MASTER
GO
SELECT name, sid FROM sys.sql_logins where name ='lansweeperuser'
go
- connect to sqlnode02 and create account with the SID from 13.
CREATE Login lansweeperuser WITH password = 'hard2guessuserpassword!', SID = 0x12345678901234567890123456789012
- ​on sqlnode02 - reverse failover availablity group to sqlnode01, where you created your lansweeperdb
--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
:Connect sqlnode02\instance01
:Connect sqlnode01\instance01
ALTER AVAILABILITY GROUP [availabilitygroup-01] FAILOVER;
GO
GO
- add lansweeperdb to availabilitygroup
--- EXECUTE IN SQLCMD MODE.
-- CONNECTS TO PRIMARY NODE AND ADDS REPLICA WITH SEEDING SET TO AUTO(default)
:Connect sqlnode01\instance01
USE [master]
GO
ALTER AVAILABILITY GROUP [availabilitygroup-01]
MODIFY REPLICA ON N'sqlnode02\instance01' WITH (SEEDING_MODE = AUTOMATIC)
GO
--- ADDS DATABASE TO AG
USE [master]
GO
ALTER AVAILABILITY GROUP [availabilitygroup-01]
ADD DATABASE [lansweeperdb];
GO
--- CONNECTS TO SECONDARY NODE AND GRANTS PRIVILEGE TO CREATE DATABASE
:Connect sqlnode02\instance01
ALTER AVAILABILITY GROUP [availabilitygroup-01] GRANT CREATE ANY DATABASE;
GO
GO
- Connect via SSMS to your database server sqlnode02 and check replication status.
After 16. the sqlnode02 should display status of database (Restoring..), as it is creating database and synchronising it (via restore).
After approx. 15 minutes, depending on size, connecting to both sql nodes should display that lansweeperdb is in (Synchronised) state and you can continue. - Run the ConfigEditor tool, found at on the servers hosting your Lansweeper Server service and web console.
In data source field provide availabilitygroup listener name with instance name, in our example
listener-01\instance01.
In password field, provide password provided in user account creation. - Start Lansweeper service and iis express service/ or start IIS Site hosting your Lansweeper servive
- Check in web interface that Current Database in /Configuration is properly connected to listener-01
Known error:
The following errors may be visible in errorlog.txt in /bin after installation and during failover. Those are normal erros, logged due to application losing connection during failover of databases to instance. Regular failovers with only lansweeperdb, takes approx. 10 seconds and grows with the amount to data being saved before closing connection and switching it node running PRIMARY role. If timestamps between first and last occurance of logs are within 30s than those can be safely ignored, but times vary between light/heavily used instances.
System.Data.SqlClient.SqlException (0x80131904): The connection is broken and recovery is not possible. The client driver attempted to recover the connection one or more times and all attempts failed. Increase the value of ConnectRetryCount to increase the number of recovery attempts. ---> System.Data.SqlClient.SqlException (0x80131904): Unable to access availability database 'lansweeperdb' because the database replica is not in the PRIMARY or SECONDARY role. Connections to an availability database is permitted only when the database replica is in the PRIMARY or SECONDARY role. Try the operation again later.
The connection is broken and recovery is not possible. The client driver attempted to recover the connection one or more times and all attempts failed. Increase the value of ConnectRetryCount to increase the number of recovery attempts.
@Lansweeper if you could help me with the formatting, cause I can't remove all the space and change bullet > numbered list without crashing HTML - I'll be really glad. Thank you and I welcome any feedback. 🙂
Regards, Hubert
