Category: Technology

Setup SQL Server on Linux VM

wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add – sudo add-apt-repository “$(wget -qO- https://packages.microsoft.com/config/ubuntu/18.04/mssql-server-2019.list)” sudo apt-get update sudo apt-get install -y mssql-server sudo /opt/mssql/bin/mssql-conf setup ### Command line Tools sudo apt install curl curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add – curl

OctoberCMS Development Tips

 File :  /vendor/symfony/http-foundation/ResponseHeaderBag.php  Default :   return ‘no-cache, private’; Modified to cache : return ‘public, max-age=14400’; <pre class=”lang:default decode:true ” title=”OctoberCMS cache headers”> /*** Returns the calculated value of the cache-control header.** This considers several other headers and calculates or modifies the*

Inspect curl Requests headers

$ curl -w ‘\ncontent_type=%{content_type}\nfilename_effective=%{filename_effective}\nftp_entry_path=%{ftp_entry_path}\nhttp_code=%{http_code}\nhttp_connect=%{http_connect}\nlocal_ip=%{local_ip}\nlocal_port=%{local_port}\nnum_connects=%{num_connects}\nnum_redirects=%{num_redirects}\nredirect_url=%{redirect_url}\nremote_ip=%{remote_ip}\nremote_port=%{remote_port}\nsize_download=%{size_download}\nsize_header=%{size_header}\nsize_request=%{size_request}\nsize_upload=%{size_upload}\nspeed_download=%{speed_download}\nspeed_upload=%{speed_upload}\nssl_verify_result=%{ssl_verify_result}\ntime_appconnect=%{time_appconnect}\ntime_connect=%{time_connect}\ntime_namelookup=%{time_namelookup}\ntime_pretransfer=%{time_pretransfer}\ntime_redirect=%{time_redirect}\ntime_starttransfer=%{time_starttransfer}\ntime_total=%{time_total}\nurl_effective=%{url_effective}\n\n’ -o /dev/null -s ‘https://h1bsalary.online’ curl -L -v -s -o /dev/null google.de -L, –location follow redirects -v, –verbose more output, indicates the direction -s, –silent don’t show a progress bar -o, –output /dev/null don’t show received body Or the shorter version:

Import DACPAC file to SQL Server

Import DACPAC File https://docs.microsoft.com/en-us/azure/sql-database/scripts/sql-database-import-from-bacpac-powershell # Login-AzureRmAccount # Set the resource group name and location for your server $resourcegroupname = “myResourceGroup-$(Get-Random)” $location = “westeurope” # Set an admin login and password for your server $adminlogin = “ServerAdmin” $password = “ChangeYourAdminPassword1” #

SQL Server Performance Queries

SQL Server Performance Tuning   — Top 10 sessions caused blocking SELECT TOP 10 r.session_id, r.plan_handle, r.sql_handle, r.request_id, r.start_time, r.status, r.command, r.database_id, r.user_id, r.wait_type, r.wait_time, r.last_wait_type, r.wait_resource, r.total_elapsed_time, r.cpu_time, r.transaction_isolation_level, r.row_count, st.text FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st

SQL Server IO Stall Statistics

— Read IO Stalls Statistics SELECT @@VERSION GO SELECT type, SUM(io_stall_read_ms) AS io_stall_read, SUM(io_stall_write_ms) AS io_stall_write, SUM(num_of_reads) AS num_of_reads, SUM(num_of_writes) AS num_of_writes FROM sys.dm_io_virtual_file_stats(NULL, NULL) fs JOIN sys.database_files df ON fs.file_id = df.file_id WHERE database_id = DB_ID() GROUP BY df.type

Azure – Troubleshooting

Azure – Troubleshooting AKS Recommended KeepAlive  Settings sudo bash -c “echo 120 > /proc/sys/net/ipv4/tcp_keepalive_time” sudo bash -c “echo 30 > /proc/sys/net/ipv4/tcp_keepalive_intvl” sudo bash -c “echo 8 > /proc/sys/net/ipv4/tcp_keepalive_probes”    

Change Database Recover Model

Change Database Recover Model USE master DECLARE @isql VARCHAR(2000), @dbname VARCHAR(64), @logfile VARCHAR(128) DECLARE c1 CURSOR FOR SELECT d.name, mf.name AS logfile –, physical_name AS current_file_location, size FROM sys.master_files mf INNER JOIN sys.databases d ON mf.database_id = d.database_id WHERE recovery_model_desc

Deadlocks in Azure SQL Database

Deadlocks in Azure SQL Database : Recently we were working with Azure Logic Apps to invoke Azure Functions. By Default, Logic App runs parallel threads and we didn’t explicitly control the concurrency and left the default values. So Logic App

Azure SQL Database Connectivity Architecture

Azure SQL Database Connectivity Architecture Connection policy Azure SQL Database supports the following three options for the connection policy setting of a SQL Database server: Redirect (recommended): Clients establish connections directly to the node hosting the database. To enable connectivity, the

Azure Database Firewall Queries

— Server Level Firewall Rules SELECT * FROM sys.firewall_rules — Database Level Firewall Rules SELECT * FROM sys.database_firewall_rules — — Remove server level firewall setting EXECUTE sp_delete_firewall_rule N’AllowAllWindowsAzureIps’; — Remove database-level firewall setting EXECUTE sp_delete_database_firewall_rule N’Allow Azure’; — Add database-level

Videos Watched

Videos Watched   October 201 October 20 Meet My Next Guest, Richard M Stallman https://www.youtube.com/watch?v=VMM6D9vuHkY October 21 Monitor your infrastructure and analyze operational logs at scale with Azure Monitor – BRK3354 https://www.youtube.com/watch?v=_0ccWoScTyE Uncle” Bob Martin – “The Future of Programming

Stored Procedure Error Logging

Stored Procedure Error Logging Create table to capture Errors Create Stored Procedure to Log Error Invoke Stored Procedure in TRY/CATCH Block USE TEMPDB; GO IF OBJECT_ID(‘APM.ErrorLog’) IS NOT NULL DROP TABLE APM.ErrorLog; GO CREATE TABLE APM.ErrorLog ( ErrorLogID INT IDENTITY(1,

Install and Run SQL Server Docker Container on Mac

Install and Run SQL Server Docker Container on Mac Like most people, I use Mac , Windows as well Linux OS for development and testing purposes. Primarily I use Mac for Development purpose. I have few projects which uses SQL Server as

Bulk Load Data Files in S3 Bucket into Aurora RDS

Bulk Load Data Files in S3 Bucket into Aurora RDS  We typically get data feeds from our clients ( usually about  ~ 5 – 20 GB) worth of data. We download these data files to our lab environment and use shell

MYSQL / AURORA Database Troubleshooting

Number of Connections by Host  SELECT SUBSTRING(HOST, 1, 10) , DB,USER , COUNT(*) AS Count FROM information_schema.processlist group by SUBSTRING(HOST, 0, 10) , DB,USER ORDER BY Count desc ; — ‘10.10.50.22’, ‘Portal’, ‘webguest-dev’, ’46’ Aurora Max Connections select AURORA_VERSION(); select

Memory-Optimized Tables

Monitor Memory Optimized Table Space Usage : ; WITH system_allocated_memory ( system_allocated_memory_in_mb ) AS ( SELECT ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_allocated_for_table_kb) + SUM(TMS.memory_allocated_for_indexes_kb) ) / 1024.00) FROM [sys].[dm_db_xtp_table_memory_stats] TMS WHERE TMS.object_id <= 0 ), 0.00) ), table_index_memory ( table_used_memory_in_mb,

Brute force attack on SQL Server

Brute force attack on SQL Server  If your business needs the SQL Server to be accessible on public network, you may be very vulnerable for brute force attacks. Following query will help you identify the failed login attempts and you

When Does SQL Server Trial Edition Expire?

When Does SQL Server Trial Edition Expire? I installed SQL Server Docker Container on Linux Machine running on EC2. I took the AMI Image and launched the existing container and wanted to know when will the SQL Server licence expires

SQL Injection attack on Website hosted on EC2 Machine

SQL Injection attack on Website hosted on EC2 Machine: I setup a website http://h1bsalary.online with publicly available dataset. As soon as I launched website, numerous trolls and automated bots sending traffic to identify the vulnerabilities. Safe-Guards I have taken so far :

Top