John Liu Blog

Every drop counts

SQL Server Python integration

SQL Server Machine Learning Services config files are located under C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXP_2019\MSSQL\Binn pythonlauncher.config rlauncher.config When receive following error If the service worked before but not now, check the LaunchPad service has been restarted following restart of SQL Instance. If restart SQL Instance followed by restart LaunchPad service didn’t help, check LaunchPad service account has FULL CONTROL permission on the working folder configured in the above config file From SQL2019, the working folder in the config file is a shortcut link to C:\Program Files\Microsoft SQL Server\MSSQL15.

Posted by John Liu Friday, December 23, 2022

Check SQL DB size and free disk space

Following script will list the db size for all user databases and the free disk space of the driver. SELECT DBName ,Owner ,Create_Date ,DBSizeGB = SUM(DBSizeGB) ,Drive ,DriveFreeSpaceGB = MAX(available_bytes)/1024/1024/1024 FROM (SELECT DBName = D.name ,Owner = P.name ,Create_Date = CONVERT(DATE,D.create_date) ,DBSizeGB = F.size * 8 / 1024 / 1024 ,Drive = LEFT(F.physical_name,2) ,DR.available_bytes FROM sys.databases D INNER JOIN sys.server_principals P ON D.owner_sid = P.sid INNER JOIN sys.master_files F ON D.

Posted by John Liu Thursday, October 27, 2022

Draw digram using Mermaid

Mermaid lets you create diagrams using text and code. In Visual Studio Code, after install the Mermaid Editor extension, you will be able to create diagram easily. The file name needs to have .mmd file extension. Following code will generate an example digram: flowchart TB; title[<font size=6><B><u>Integration Automation Architecture</u></B>] title --> A2 style title fill:#FFF,stroke:#FFF linkStyle 0 stroke:#FFF,stroke-width:0; subgraph SG1[Power Automate Flow<br>] A1(Outlook email received) --> B(Retrieve automation config from DS5Admin DB) A2(Scheduled API call) --> B A3(File created in folder) --> B B --> B1(Load raw contents into DS5Admin DB based on config) end subgraph SG2[Data Process via SQL Agent job<br><br>] B1 --> B2(Process raw contents against target DB<br> from within Admin DB) B2 --> C{Automation type} C -- AAL --> D1(AAL SP) C -- pXRF --> D2(pXRF SP) C -- LAS --> D3(LAS SP) C -- JSON --> D4(JSON SP) C -- General CSV --> D5(CSV SP) C -.

Posted by John Liu Wednesday, October 26, 2022

SQL Server Replication

There a 4 type of replications: Snapshot replication Transactional replication Peer-to-Peer replication Merge replication Peer-to-Peer Replication Column/FK/Constraint changes are replicated but trigger/index changes are not replicated. Changes are replicated almost instantly. Table with timestamp column is not supported, no work around. Identity column needs to be managed carefully. When setup replication, identity column will automatically have the NOT FOR REPLICATION option set to YES. Don’t set the option to NO (even though you can via script after replication setup).

Posted by John Liu Tuesday, September 6, 2022

Remove SQL Server Replication

Following script helps to cleanly remove all replication objects configured on the publisher server. ONLY run this script if you want to completely remove entire replication set. DECLARE @distributionDB SYSNAME = N'distribution' -- CHANGE THIS TO THE DISTRIBUTION DATABASE NAME. default is distribution ,@publisher SYSNAME = N'' -- CHANGE THIS TO THE PUBLISHER NAME. ,@publicationDB SYSNAME = N'' -- CHANGE THIS TO THE PUBLICATION DATABASE NAME. ,@subscriber SYSNAME = N''; -- CHANGE THIS TO THE SUBSCRIBER NAME, comma separated if more than one subscriber.

Posted by John Liu Monday, September 5, 2022