Infrastructure at your Service

dbi services BLOG

Welcome to the dbi services Blog! This IT blog focuses on database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, EMC Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on our blog postings.

David Barbarin

Dynamic filters and “kitchen sink” queries dilemma

By | Development & Performance | No Comments
 

 Dealing with dynamic filters is a common scenario with management software or ERP. Indeed, users should like to have the flexibility to filter and sort their business data as they want in order to be efficient. Target and operate quickly on the right data is in line with the required performance every day. This is at least what I could notice from different customer places. So, through my mandates at customer places, I could see…

Read More
Daniel Westermann

The dbi services PostgreSQL reference architecture (2) – The community approach

By | Database Administration & Monitoring | No Comments
 

 In the last post I looked at how dbi services designs a PostgreSQL architecture based on the products of EnterpriseDB. In this post I’ll look at the same questions but using pure community tools. If you do not need vendor support and do trust the community for providing help and fixes this is another way to go. And usually you can trust the community around PostgreSQL very well. The architecture itself is pretty much the…

Read More
Daniel Westermann

The dbi services PostgreSQL reference architecture (1) – The commercial approach

By | Database Administration & Monitoring | No Comments
 

 When it comes to designing a PostgreSQL architecture that fits your requirements there are a couple of things to think about: Do you need a HA setup? How shall backups and restores being done and how shall all of these components get monitored? Do you need vendor support for all the tools that make up the architecture? At dbi we have a reference architecture that we can apply to most of our projects. It is…

Read More
David Barbarin

SQL Server AlwaysOn : the registry is also important for availability groups

By | Database Administration & Monitoring, Database management | No Comments
 

 A couple of months ago, we experienced with my colleague Nathan Courtine a weird issue at one of our customers. This issue concerned an AlwaysOn availability group and more precisely to the WSFC cluster layout. I can’t say strongly enough that the AlwaysOn availability group runs on the top of the WSFC and having a good understanding of the internal stuff can help a lot for troubleshooting. First of all, let me introduce the issue…

Read More
Clemens Bleile

Enable 10046 Tracing for a specific SQL

By | Development & Performance | No Comments
 

 Available methods to enable 10046 trace are described in My Oracle Support Note 376442.1. You can enable 10046-tracing – on session level (alter session) – for other sessions (e.g. with oradebug, the package DBMS_MONITOR or DBMS_SYSTEM) What is not covered with the methods above is the possibility to trace a specific SQL-statement, which runs “somewhen” in the future on the database. E.g. a SQL, which runs during a next batch job. With the introduction of…

Read More
Stéphane Haby

SQL Server Tips: Find sql logins when “windows authentication only” is enabled

By | Database Administration & Monitoring | No Comments
 

 A customer asks me to find all sql logins enabled on servers where windows authentication only is enabled. The goal is to clean sql logins on all servers through the CMS (central management server). In some cases, sql logins are created even if the authentication is set to “Windows authentication only mode”. I think that this query is interesting and I will just share it with you. DECLARE @value INT USE [master] EXEC xp_instance_regread N’HKEY_LOCAL_MACHINE’,…

Read More
Mouhamadou Diaw

About OCR backup

By | Database Administration & Monitoring | No Comments
 

 Last day Franck and me were discussing about OCR backup Let’s take a 2 nodes RAC. We can see that OCR backup is automatically done by oracle in only one node on local (with a certain frequency every day, every week and every 4 hours)) [oracle@racsrv2 ~]$ /u01/app/12.1.0.2/grid/bin/ocrconfig -showbackup racsrv2 2016/02/03 19:58:05 /u01/app/12.1.0.2/grid/cdata/racsrv-cluster/backup00.ocr 2528224568 racsrv2 2016/02/03 15:58:03 /u01/app/12.1.0.2/grid/cdata/racsrv-cluster/backup01.ocr 2528224568 racsrv2 2016/02/03 11:58:02 /u01/app/12.1.0.2/grid/cdata/racsrv-cluster/backup02.ocr 2528224568 racsrv2 2016/02/02 23:57:57 /u01/app/12.1.0.2/grid/cdata/racsrv-cluster/day.ocr 2528224568 racsrv2 2016/02/02 23:57:57 /u01/app/12.1.0.2/grid/cdata/racsrv-cluster/week.ocr 2528224568 But…

Read More
Daniel Westermann

EDB Postgres Advanced Server 9.5 new features – Profiles

By | Database Administration & Monitoring | No Comments
 

 The just released version of EDB Postgres Advanced Server 9.5 introduces profiles very much the same as in Oracle. Lets have a look at it. As in Oracle there is a default profile: (enterprisedb@[local]:5445) [postgres] > \x Expanded display is on. (enterprisedb@[local]:5445) [postgres] > select * from edb_profile; -[ RECORD 1 ]———–+——– prfname | default prffailedloginattempts | -2 prfpasswordlocktime | -2 prfpasswordlifetime | -2 prfpasswordgracetime | -2 prfpasswordreusetime | -2 prfpasswordreusemax | -2 prfpasswordverifyfuncdb |…

Read More
Mehdi Bada

Introduction to MongoDB

By | Database management | No Comments
 

 MongoDB is an open source NoSQL database management system oriented document. MongoDB allows manipulating structured and un-structured data. MongoDB is schema-less and used to store large volume of data. In this blog we are going to explain the main principle of this DBMS. MongoDB stores data in documents. Documents are stored in BSON types, the JSON binary format. In fact, we manipulate JSON document in the MongoDB shell. Documents are encapsulated into collections. A document…

Read More
Philippe Schweitzer

DBI Tail v1.2 now released !

By | Database Administration & Monitoring | No Comments
 

 Dear IT colleagues, We regularly have to read log file contents either to understand software functioning or for debugging purpose. Most of the time we access same log files located in same servers. It is very painful to login every time, then lookup and extract relevant traces for a particular case under investigation. On top of that, especially on some systems, there is no Unix “tail” command equivalent. For all these reasons, I want to…

Read More
David Barbarin

Windows failover cluster 2016 : new site awareness feature

By | Database Administration & Monitoring, Operation systems | No Comments
 

 After my first two blogs about cloud witness and domain-less dependencies features shipped with Windows Server 2016, it’s time to talk about another pretty cool feature of the WSFC : site awareness that will also benefit to our SQL Server FCIs and availability groups. But before to talk further about the potential benefits of this new feature, let’s go back to the previous and current version of Windows Server that is 2012 R2. Firstly, let’s…

Read More