SQL Server on AWS

  SQL Server on AWS

Good Option available to install  MS SQL server binaries on EC2.

Advantages:

Full Control: You can launch an EC2 instance and install SQL Server manually. This gives you more control over configurations.

Customization: Choose your operating system and SQL Server version.

Licensing: You can either bring your own license (BYOL) or use AWS's license-included options.

Data Loss Prevention (DLP) Tools

Data Loss Prevention (DLP) tools are crucial for protecting sensitive information/ data from unauthorized access, misuse, or loss. Compliance with Industry Standard security requirements  are met with these tools. They monitor and control data movement across various systems.

Some prominent DLP tools:

Symantec Data Loss Prevention (DLP): Offers comprehensive data protection across endpoints, networks, and storage. It includes advanced content inspection and contextual analysis to safeguard sensitive data.

McAfee Total Protection for Data Loss Prevention: Provides robust protection for sensitive data with policy enforcement across endpoints, networks, and cloud environments. It integrates well with McAfee’s broader security solutions.

Forcepoint Data Loss Prevention: Utilizes behavioral analytics to detect and prevent data breaches. It focuses on user activity and data usage patterns to enforce security policies.

Microsoft 365 Data Loss Prevention: Integrated into Microsoft 365, it offers protection for data within Office apps, email, and OneDrive. It provides policy templates and real-time monitoring capabilities.

Digital Guardian Data Loss Prevention: Specializes in protecting intellectual property and sensitive data across endpoints, networks, and cloud environments with flexible policy management.

Trend Micro Data Loss Prevention: Provides endpoint and network DLP solutions with advanced threat intelligence and integration with Trend Micro’s broader security suite.

IBM Security Guardium: Focuses on database activity monitoring and data protection, offering robust analytics and real-time alerts to safeguard sensitive data.

Varonis Data Security Platform: Monitors data access and usage across file systems and email systems, providing insights into potential risks and ensuring compliance with data protection regulations.

Proofpoint Enterprise DLP: Delivers content inspection and monitoring to protect data across email, cloud, and endpoints, with a focus on preventing data breaches and compliance violations.

Check Point Data Loss Prevention: Integrates with Check Point’s security infrastructure to provide policy enforcement and data protection across various platforms and applications.

These few listed tools are versatile to ensure that sensitive information or as we say data remains protected and complaint with Industry regulations.

Sharing a script that logs all long running queries, kills them :) and sends out an email alert to my inbox. I generally modify it according to the production requirement. In my environment there are a couple of production servers where I would setup this script to only alert and Not kill any query.

Please double test before implementing in realtime production environment.Follow the general thumb rule to implement all changes/scripts from lower to higher env. :) this script comes with disclaimer that person using has complete ownership for it’s results.

Ensure postfix installed and configured for emails to work. The script works for postgre user since postgre has privs over all databases in the server

############################

if [ `whoami` != "postgres" ]; then

exit 0;

fi

# selecting non-idle queries which are running since at least 6 minutes.

psql -c "select pid, client_addr, query_start, current_query from pg_stat_activity

where current_query != '<IDLE>' and current_query != 'COPY' and current_query != 'VACUUM' and query_start + '6 min'::interval < now()

and substring(current_query, 1, 11) != 'autovacuum:'

order by query_start desc" > $LOGFILE

NUMBER_OF_STUCK_QUERIES=`cat $LOGFILE | grep "([0-9]* row[s]*)" | sed 's/(//' | awk '{ print $1}'`

if [ $NUMBER_OF_STUCK_QUERIES != 0 ]; then

# Getting the first column from the output discarding alphfanumeric values (table elements in psql's output).

STUCK_PIDS=`cat $LOGFILE | sed "s/([0-9]* row[s]*)//" | awk '{ print $1 }' | sed "s/[^0-9]//g"`

for PID in $STUCK_PIDS; do

echo -n "Cancelling PID $PID ... " >> $LOGFILE

# "t" means the query is successfully cancelled.

SUCCESS=`psql -c "SELECT pg_cancel_backend($PID);" | grep " t"`

if [ $SUCCESS ]; then

SUCCESS="OK.";

else

SUCCESS="Failed.";

fi

echo $SUCCESS >> $LOGFILE

done

cat $LOGFILE | mail -s "Stuck PLpgSQL processes detected and killed that were running over 6 minutes." youremail@whatever.com;

fi

rm $LOGFILE

#######################################

MERGE Feature in PostgreSQL

From PostgreSQL 9.1, onwards user can implement the feature of Merge using the writable CTE (Common Table Expressions)

WITH  provides a way to write auxiliary statements for use in a larger query. This can be thought of as defining temporary tables that exist just for one query.Each auxiliary statement in a WITH clause can be a SELECT,INSERT,UPDATE or DELETE and the clause WITH witself is attached to a primary statement that can also cause a SELECT,INSERT,UPDATE or DELETE

Created 2 tables and insert some data into it. Now merge the 2 tables 

[root@ip--- ~]# psql tejidatabase

psql (9.2.4.8)

Type "help" for help.

tejidatabase=# select * from testmerge;

 pid | age |  name  

-----+-----+--------

   2 |  48 | RAM

   4 |  61 | SHYAM

   6 |  85 | SONIA

   8 |  44 | RAHUL

  10 |  34 | MAMTA

  12 |  45 | SURJIT

  14 |  21 | ISHIKA

  16 |  19 | IPSA

(8 rows)

tejidatabase=# select * from merge2;

 pid | age |  name  

-----+-----+--------

  18 |  56 | YASUDA

   8 |   0 | RAHUL

  14 |   5 | ISHIKA

(3 rows)

tejidatabase=# WITH upsert as (update merge2 m set age=d.age+100 ,name=d.name from testmerge d where m.pid=d.pid RETURNING m.*) insert into merge2 select a.pid,a.age,'NEW' from testmerge a where a.pid not in ( select b.pid from upsert b);

INSERT 0 6

tejidatabase=# select * from merge2;

 pid | age |  name  

-----+-----+--------

  18 |  56 | YASUDA

   8 | 144 | RAHUL

  14 | 121 | ISHIKA

   2 |  48 | NEW

   4 |  61 | NEW

   6 |  85 | NEW

  10 |  34 | NEW

  12 |  45 | NEW

  16 |  19 | NEW

(9 rows)

tejidatabase=# 

As you can see all the rows of test merge are now added in merge2 with name=’NEW’ and the matching pid of test merge and merge2, the ages have been added by 100.

The magic of Writable CTE which can make UPSERT in PostgreSQL

:-)

A few practical required PostgreSQL commands.

1. Check PostgreSQL server Status, stop and start.

Depending on what version of PostgreSQL

Check Service status ( # /etc/init.d/ppas-9.2 status

Stop Service (#  service ppas-9.2 stop)

Start Service (#  service ppas-9.2 stop)

[root@ip—~]# /etc/init.d/ppas-9.2 status

pg_ctl: no server running

[root@ip-~]# service ppas-9.2 start

Starting Postgres Plus Advanced Server 9.2: 

waiting for server to start.... done

server started

Postgres Plus Advanced Server 9.2 started successfully

[root@ip-~]# /etc/init.d/ppas-9.2 status

pg_ctl: server is running (PID: 8595)

/opt/PostgresPlus/9.2AS/bin/edb-postgres "-D" "/opt/PostgresPlus/9.2AS/data"

2. Confirm PostgreSQL version

# select version();

[root@ip- ~]# psql

psql (9.2.4.8)

Type "help" for help.

edb=# select version();

                                                      version                   

                                   

--------------------------------------------------------------------------------

-----------------------------------

 EnterpriseDB 9.2.4.8 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 2

0080704 (Red Hat 4.1.2-52), 64-bit

(1 row)

edb=# 

3. List out the databases in your connected PostgreSQL server

# \l 

Note: above is slash with lowercase L

# select datname from pg_database;

edb=# select datname,datcollate,datctype,datconnlimit from pg_database;

   datname    | datcollate |  datctype  | datconnlimit 

--------------+------------+------------+--------------

 template1    | en_US.UTF8 | en_US.UTF8 |           -1

 template0    | en_US.UTF8 | en_US.UTF8 |           -1

 postgres     | en_US.UTF8 | en_US.UTF8 |           -1

 edb          | en_US.UTF8 | en_US.UTF8 |           -1

 tejidatabase | en_US.UTF8 | en_US.UTF8 |           -1

 testdb       | en_US.UTF8 | en_US.UTF8 |           -1

(6 rows)

edb=# \l

                                  List of databases

     Name     |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   

--------------+----------+----------+------------+------------+-----------------------

 edb          | postgres | UTF8     | en_US.UTF8 | en_US.UTF8 | 

 postgres     | postgres | UTF8     | en_US.UTF8 | en_US.UTF8 | 

 tejidatabase | postgres | UTF8     | en_US.UTF8 | en_US.UTF8 | =Tc/postgres         +

              |          |          |            |            | postgres=CTc/postgres+

              |          |          |            |            | teji=CTc/postgres    +

              |          |          |            |            | testuser=CTc/postgres

 template0    | postgres | UTF8     | en_US.UTF8 | en_US.UTF8 | =c/postgres          +

              |          |          |            |            | postgres=CTc/postgres

 template1    | postgres | UTF8     | en_US.UTF8 | en_US.UTF8 | =c/postgres          +

              |          |          |            |            | postgres=CTc/postgres

 testdb       | teji     | UTF8     | en_US.UTF8 | en_US.UTF8 | 

(6 rows)

4. Last but not the least is to be able to get psql commands help and information.

# \?

Note: will show command prompt help

# \h SELECT

Note: will show details about the select command

This can be used for checking syntax of any psql commands.

edb=# \h select

Command:     SELECT

Description: retrieve rows from a table or view

Syntax:

[ WITH [ RECURSIVE ] with_query [, ...] ]

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]

    * | expression [ [ AS ] output_name ] [, ...]

    [ FROM from_item [, ...] ]

    [ WHERE condition ]

    [ GROUP BY expression [, ...] ]

    [ HAVING condition [, ...] ]

    [ WINDOW window_name AS ( window_definition ) [, ...] ]

    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]

    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]

    [ LIMIT { count | ALL } ]

    [ OFFSET start [ ROW | ROWS ] ]

    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]

    [ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]

where from_item can be one of:

    [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]

    ( select ) [ AS ] alias [ ( column_alias [, ...] ) ]

    with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]

    function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ]

    function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )

    from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]

and with_query is:

    with_query_name [ ( column_name [, ...] ) ] AS ( select | values | insert | update | delete )

TABLE [ ONLY ] table_name [ * ]

edb=# 

TNS Listener Posion attack.

Oracle rushes out a security advisory with workarounds for a dangerous Database Server security flaw. It released Security Alert CVE-2012-1675
The vulnerability was orginally discovered by Joxean Koret in 2008. Oracle Database Server  versions  released in the past 13 years contain a bug that allows hackers to  monitor all data passing between the server and end users who are connected to it. Koret is said to have commented that Oracle learned of the bug in 2008 and indicated in a recent e-mail that it had no plans to fix current supported versions of the enterprise product because of concerns it could cause "regressions" in the code base.
Interestingly , the security alert provide customers with a number of technical measures to provide effective defense against this vulnerability in all deployment scenarios. It doesnot contain any patch. It is urging customers make the configuration changes documented in the  mentioned My Oracle Support Notes as soon as possible.

What is Oracle GoldenGate?

I was looking for a short & crisp answer to the question what is Oracle Golden Gate which could be called a defination of the product as well.

Oracle GoldenGate replication technology that’s now part of the Oracle framework, is a high-performance software application for real-time transactional change data capture, transformation, and delivery, offering log-based bidirectional data replication. The application enables you to ensure that your critical systems are operational 24/7, and the associated data is distributed across the enterprise to optimize decision-making.
Oracle GoldenGate filled a gap which we did not have - heterogeneous replication, replication from Oracle to different databases and vice versa.Oracle Goldengate can be used as a replication tool, ETL, and even as a DR solution.One can move data between similar or dissimilar supported Oracle versions, or one can move data between an Oracle database and a database of another type. GoldenGate supports the filtering, mapping, and transformation of data.