SqlServer Begin's

The last few months it’s all been dbt. Dbt is a transform and load tool which is provided by fishtown analytics. For those that have created incremental models in dbt would have found the simplicity and easiness of how it drives the workload. Depending on the target datastore, the incremental model workload implementation changes. But all that said, the question is, should the incremental model use high-watermark as part of the implementation. How incremental models work behind the scenes is the best place to start this investigation. And when it’s not obvious, the next best place is to investigate the log after an test incremental model execution and find the implementation. Following are the internal steps followed for a datastore that does not support the merge statements. This was observed in the dbt log. - As the first step, It will copy all the data to a temp table generated from the incremental execution. - It will then delete all the data from the base table th...

I have been an agent of change in my pod and the staff I have been waving is postgres :). We have been working out a new project and the OLTP data store is postgres. Out of the many things I encountered this week two things really topped the list.  Most OLTP workload are random IO. Postgres heep vs cluster key that sorts data  Declarative partitioning is the way forward to keeping data sets manageble and assists with maintenance tasks. The code snippet for bellow provides details of the partition child table properties.    SELECT    nmsp_parent.nspname AS parent_schema,    parent.relname      AS parent,    nmsp_child.nspname  AS child_schema,    child.relname       AS child ,    pg_get_expr(child.relpartbound, child.oid, true) as child_expression,    child.reloptions   FROM pg_inher...

One of the first things you learn in  postgres  is the importance of getting the default privileges configured. Coming from the  SQLserver  background, I found having to assign default privileges a little precarious but once I got over that hump it has become more obvious for its place in PG.  The other day I discovered that default privileges don't get inherited of the new owner when the ownership changes of an object. 1.) login as  postgres   create table tab(id int)  2.) login as d_owner  create table  taba (id int) 3.) Grant permission for  readonly  role access data from the public schema owned by d_owner grant select on all tables in schema public to  readrole ; alter default privileges for user  d_owner   in schema public grant select on tables to feedsapi_readonly_role 4.) Change the owner of  tab  from  postgres  to d_owner     ...

I was muddling around attempting to find  away to display the ReadCapacity and WriteCapacity for the dyanmodb table with powersheel. Wanted to share this as it took me quite bit of trial error before getting this right. $PrimaryRegion = 'ap-southeast-2' $ddbtablelist = Get-DDBTableList -Region $PrimaryRegion foreach ($ddbtablelist_item in $ddbtablelist) { Get-DDBTable -Region $PrimaryRegion -TableName $ddbtablelist_item |Select- Object TableName , ItemCount, @{Name= "ProvisionedThroughput" ;Expression={ $_.ProvisionedThroughput | Select- Object ReadCapacityUnits ,WriteCapacityUnits}}|Select- Object -Property * -ExcludeProperty ProvisionedThroughput -ExpandProperty ProvisionedThroughput}

As you may have observed already , when a RDS instance is deleted all the automated snapshots disappear along with it giving us little or no option to recover. However, manual snapshots can be retained indefinitely for a additional cost. You will then realize that AWS has imposed a softlimit of 100 manual snapshots per region and account . This can be extended by speaking to AWS but this will cause the cost to increase over a period of time. There is also the other downside, were  snapshots can't be moved to a S3 bucket. After much discussuion we decided to delete the manual snapshots older than 10 days. Attached is the sample script. $20Days = (Get-Date).Adddays(-20) $ManulSonpshot = Get-RDSDBSnapshot -Region us-east-1 | Sort-Object SnapshotCreateTime | select -First 1000 | Where-Object {$_.SnapshotType -eq "manual" -and $_.SnapshotCreateTime -le $20Days } | Select-Object DBInstanceIdentifier , Engine , SnapshotCreateTime ,dbsnapshotidentifier foreach ($Ma...

For those of my Friends who are on postgres RDS and require to backup the globas for a rds instance you would soon find it can't perform a backup with pg_dumpall --globals-only.  The default user that gets created along with the RDS postgres instance does not have adequate permission to get through the backup processes. Bellow is the message that you would receive. pg_dumpall.exe : pg_dumpall: query failed: ERROR:  permission denied for relation pg_authid At line:1 char:1 + & 'C:\setup_msi\postgres\bin\pg_dumpall.exe'  -h XXXXXX.cXXXX... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~    + CategoryInfo          : NotSpecified: (pg_dumpall: que...ation pg_authid:String) [], RemoteException    + FullyQualifiedErrorId : NativeCommandError pg_dumpall: query was: SELECT oid, rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin,...

Today I had to programitacally install a .msi and .exe at the start up of an EC2 instance. By default all them code that needs to be executed during a start up is added to the user data of the EC2 instance. Bellow was the code used to install the msi and exe start-process c:\setup\<>.msi /gn -wait However executing the exe required some effort as it needed special switchers start-process c:\setup\psqlodbc-setup.exe /install /quiet /norestart  -wait -passthorugh found the extra switcher doing this

If  you are looking for the endpoint for a RDS instnace and wondered where it had gone. You should find it in the Endpoint property in Get-rdsdbinstance. I found the Get-rdsdbinstance a bit misleading , cause i expected the a expandable property to be {} and found a just text. Get-RDSDBInstance - Region $ region | Where - Object { $ _ .Engine -eq 'Mysql' }  | Select-Object - ExpandProperty Endpoint | select Address

When you use RDS as a service , what you soon find out is that you are not aware of the OS, hardware. You are forced to connect to the RDS instance remotely. I had the urge to copy data into a postgres RDS instnace and the EC2 instnace that we have backed didn't have psql intalled( the command line tool for postgres). The lasting solution was to get a new AMI backe with the shell for psql , Instead i followed these steps to get the psql shell setup. Copy the bin, scripts folders from a machine where psql is already works. It generally should be found at C:\Program Files\PostgreSQL\9.6 Download the msvcr120.dll ie link ( https://www.dll-files.com/download/9c861c079dd81762b6c54e37597b7712/msvcr120.dll.html?c=bWlMa1QxZDBNWjJvU3ZjV25aRUFzQT09 ) and copy it to the bin folder Open the runpsql.bat and change the new location of the “psql.exe" Make a shortcut of the runpsql.bat to desktop.

You soon realize the AWS platform provides some of the maintenance work that we used to do as DBA's. But you would find there are situation where you require some level of control. In late 2016 AWS provided the functionality for SQL server to perform native backups of its RDS instances extending its snapshot functionality. Solution will attempt to extend what Jeff bar has blogged by providing few extra screen shots and some additional steps. Steps to follow 1.) Enable the SQL native backups by adding the SQL_Server_Backup_Restore option group 2.) Associate the option group created in step 1 to the RDS instance  Once you have created the option group, it is mandatory that you associate the newly created option group to the RDS instance that you intend to run the SQL native backup. Failing to which you would find bellow error message at the time of executing the backup command Msg 50000, Level 16, State 0, Procedure rds_backup_database, ...

AWS is revolutionizing  many aspects of the lively hood of many folks in the tech industry. Industries have aligned its processes around the many AWS services and depend on the availability and consistency of its services. As a data person , I am supper delighted and totally on board the AWS journey to a brave new world in the cloud. For those of you who are getting on-board AWS, Simple Storage System(S3) will be one place to start the journey. I have outlined few points on S3 that can help you on your way. Is Read after write consistency for PUTS for new objects Is known as object based storage Default S3 buckets per account  =100 Storage tiers S3 Durable , Immediately available , frequently accessed S3-IA Durable , Immediately available , infrequently accessed S3 - Redundancy Reduced  redundancy storage ( data that is easily reproducible , such as thumb nails ,watermarks etc. ) 99.99 vailability Glaciar Cheaper than ...