→ Upcoming Keynote Event - Introducing Lansweeper's 2023 Spring Release: 'Duvel' - Learn More

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
hubertmroz
Engaged Sweeper

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.

ServerRole
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

 

  • 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

 

 

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). 
    hubertmroz_2-1684790751416.png
    After approx. 15 minutes, depending on size, connecting to both sql nodes should display that lansweeperdb is in (Synchronised) state and you can continue.
    hubertmroz_1-1684790746848.png
  • 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.
    hubertmroz_0-1684790734584.png
  • 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
1 REPLY 1
Obi_1_Cinobi
Lansweeper Tech Support
Lansweeper Tech Support

Hello there!

Impressive work there! Thanks for sharing!

New to Lansweeper?

Try Lansweeper For Free

Test Drive Lansweeper Yourself. Explore our interactive Demo or sign up for free 14-day trial.

Try Now