‎05-22-2023
11:42 PM
- last edited on
‎04-02-2024
07:56 AM
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:
/** 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
/** Run with T-SQL **/
use master;
go
sp_detach_db lansweeperdb
robocopy c:\tmp\ \\sqlnode01\temp\ lansweeperdb.bak
--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
:Connect sqlnode02\instance01
:Connect sqlnode01\instance01
ALTER AVAILABILITY GROUP [availabilitygroup-01] FAILOVER;
GO
GO
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
USE [master] ;
ALTER DATABASE [lansweeperdb] SET RECOVERY FULL ;
USE lansweeperdb;
GO
BACKUP DATABASE lansweeperdb
TO DISK = 'c:\tmp\lansweeperdb2.bak'
WITH FORMAT,
MEDIANAME = 'Lansweeperdb',
NAME = 'Full Backup of lansweeperdb';
GO
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
USE MASTER
GO
SELECT name, sid FROM sys.sql_logins where name ='lansweeperuser'
go
CREATE Login lansweeperuser WITH password = 'hard2guessuserpassword!', SID = 0x12345678901234567890123456789012
--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
:Connect sqlnode02\instance01
:Connect sqlnode01\instance01
ALTER AVAILABILITY GROUP [availabilitygroup-01] FAILOVER;
GO
GO
--- 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
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. 🙂
‎05-23-2023 11:46 AM
Hello there!
Impressive work there! Thanks for sharing!
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now