SQL Server : keep track of all connections/disconnections

Here is a rundown of some of the options I have used to audit logins and logouts. I haven't had the chance to use Extended Events for auditing purposes yet. Below, I quickly describe tracking login and logout events using SQL Server tracing and SQL Server Audits. You'll probably end up going with setting up background server tracing which is described briefly below. If you need me to go into further detail, don't hesitate to ask.
Default Trace
The default trace is one way to audit activity on your server without making any changes. A post on what you can get out of the default trace is provided here.
Server Trace
If you are just looking for Login/Logout information, you can also create your own server trace to audit logins and logouts using SQL Server profiler. Just create a new trace and then export it as a SQL Server Trace definition file.

Open up SQL Server Profiler
Create a new trace using the blank template
Go to the Events selection tab
Under Security Audit, check Audit Login and Audit Logout

Run your trace, stop it and then under File, you can Export your trace as a SQL Server trace definition file. You can then run that trace in the background and save it to a file which can later be imported into a SQL Server table.
A portion of an example server Trace created by profiler:
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5

exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 2, @on
exec sp_trace_setevent @TraceID, 14, 66, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
.....
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

Here is the output I got from just capturing those items:

It is possible to convert server traces into extended event sessions and that is described in detail here.
Server Audits
There are also server audits which allows you to track and monitor Database/Server level events. Auditing may only be available in the Enterprise and Datacenter editions. I can't seem to find a specific article from Books Online that says otherwise.
USE MASTER
GO

CREATE SERVER AUDIT audit_test
TO FILE (FILEPATH = 'D:\Audit')
GO

CREATE SERVER AUDIT SPECIFICATION audit_test_spec
FOR SERVER AUDIT [audit_test]
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP)
WITH (STATE=ON)
GO

You can then view server audit events in SSMS by going to Security -> Audits. Then using the context menu, you can select View Audit Logs to review activity.

For more information on audits, you will find additional information here:
CREATE SERVER AUDIT (Transact-SQL)
CREATE SERVER AUDIT SPECIFICATION (Transact-SQL)
CREATE DATABASE AUDIT SPECIFICATION (Transact-SQL)


Clustered monotonically increased index insert performance

The relative cost of the components of an execution plan is not always reliable.

The INSERTED scan or deleted scan shouts TRIGGER to me. This is your problem most likely.

Are you looping over 400 rows and sending an email in an extreme example?


Oracle Database Vault was designed for that exact purpose.

Documentation link here.


BDBID in DBCC PAGE command output in SQL Server

I don't think its very much documented on Microsoft Site and other places. Possibly someone from Microsoft could shed some light on this, doing some checking on Paul Randal's site and other related information, most likely it is database id. You can read more details on this command at this page by him.
There are few more details at this site as below:
The BUFFER: section indicates the buffer page number (bpage) which represents exactly where in the buffer pool the page exists. Also included are:

bhash – The hash of the page
bpageno – the file and page number
bdbid – the database ID
references – the number of processes that have referenced the buffer page.
bcputicks – the amount of time latches have waited to access the page
bsampleCount – the number of times SQL Server has sampled the latch wait time for this page
bUse1 – the duration in seconds since the bcputicks and bsampleCount counters have been reset. This is perhaps a reasonable proxy for the amount of time the page has been in the buffer pool.
bstat, bstat2 – the status of the buffer page.
blog – the transaction log record where the page was last modified
bnext – the address of the next page in the buffer pool for this object
bDirtyContext – I’m guessing, but this looks like a pointer to the memory structure used by SQL Server’s storage engine to keep track of dirty pages.

As far Sybase ASE is concerned, I checked myself on multiple databases by running this command and could figure out that bdbid is dbid itself. Even if you pass a page number which is not present, it would still give you the same bdbid as that of dbid.
Below is the output from Sybase ASE when page is not present:
------------------------ Execute ------------------------

Page not found in Cache: default data cache.
Page read from disk.

BUFFER:
Buffer header for buffer 0xc00000039cd98000 (Mass head)
    page=0xc00000039cd96000 bdnew=0x0000000000000000 bdold=0x0000000000000000 bhash=0x0000000000000000 baccess_count=0
    bmass_next=0x0000000000000000 bmass_prev=0x0000000000000000 bdbid=6 bmass_encr=0x0000000000000000
    bvirtpg=[ 0xc00000039cd980d8 vpgdevno=0 vpvpn=45540 vdisk=0xc00000010939f230 ]
[Logical device name = xxxx]
    bmass_head=0xc00000039cd98000 bmass_tail=0xc00000039cd98000
    bcache_desc=0xc00000039cd9f178 (cid=0 cache name='default data cache')
    bpool_desc=0xc00000039cda4b30 bdbtable=0x0000000000000000
    Mass bkeep=0 Mass bpawaited=0 Mass btripsleft=0 Mass btripsleft_orig=0
    bmass_size=8192 (8K pool) bunref_cnt=0
    bdestroy_spid=0 bdestroy_curcmd=0 (GENERIC_TOKEN)
    bmass_stat=0x800(0x0000000000000800 (MASS_NOTHASHED))
    blfb_status=0x0000000000000000(0x00000000)
    bbuf_stat=0x0 (0x00000000)
     bcas_wait_cum:0 bcache_spin:0 bversion:0 bhot_keep:0    Buffer blpageno=120 bpg_size=8k Mass blpageno=120 Mass blocallockkeep=0    bxls_pin=0x0000000000000000 bxls_next=0x0000000000000000 bspid=0
    bxls_flushseq=0 bxls_pinseq=0 bcurrxdes=0x0000000000000000 bssdbuf_ptr=0x0000000000000000     LFB delta=0x0000000000000000

Latch and the wait queue:
Latch (address: 0xc00000039cd98040)

Latch wait queue:

PAGE HEADER:
Page header for page 0xc00000039cd96000
pageno=120 nextpg=120 prevpg=120 ptnid=28 syspartitions timestamp=0000 00000227
oampgcount=1 attrcount=10 indid=4: totalentries_lo=1 entrycount=1
page status bits (pstat): 0x800c (0x8000 (PG_OAMPG), 0x0008 (PG_OAMATTRIB), 0x0004 (PG_OAMSORT))

DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
------------------------- Done --------------------------

Below is scenario when page is present in the database:
------------------------ Execute ------------------------

Page found in Cache: default data cache. Cachelet: 2

BUFFER:
Buffer header for buffer 0xc00000039cd98000
    page=0xc00000039cd96000 bdnew=0xc000000c677bf780 bdold=0xc000000c677bf780 bhash=0xc000000c8c1ff8c0 baccess_count=57
    bmass_next=0xc000000c73fcbbc0 bmass_prev=0xc000000c8c1ff8c0 bdbid=4 bmass_encr=0x0000000000000000
    bvirtpg=[ 0xc00000039cd980d8 vpgdevno=4 vpvpn=484 vdisk=0xc0000001093a01b0 ]
[Logical device name = xxxx]
    bmass_head=0xc000000c677bf780 bmass_tail=0xc000000c677bf780
    bcache_desc=0xc00000039cd9f178 (cid=0 cache name='default data cache')
    bpool_desc=0xc00000039cda4b30 bdbtable=0xc0000001692ffa80
    Mass bkeep=0 Mass bpawaited=0 Mass btripsleft=0 Mass btripsleft_orig=0
    bmass_size=8192 (8K pool) bunref_cnt=0
    bdestroy_spid=133 bdestroy_curcmd=260 (ONLINE_DB)
    bmass_stat=0x4001010(0x0000000004000000 (MASS_DONT_DISCARD), 0x0000000000001000 (MASS_HASHED), 0x0000000000000010 (MASS_INWASH))
    blfb_status=0x0000000000000000(0x00000000)
    bbuf_stat=0x1 (0x00000001 (BUF_PG_REF))
     bcas_wait_cum:0 bcache_spin:0 bversion:10566 bhot_keep:0    Buffer blpageno=121 bpg_size=8k Mass blpageno=121 Mass blocallockkeep=0    bxls_pin=0x0000000000000000 bxls_next=0x0000000000000000 bspid=0
    bxls_flushseq=0 bxls_pinseq=0 bcurrxdes=0x0000000000000000 bssdbuf_ptr=0x0000000000000000     LFB delta=0x0000000000000000

Latch and the wait queue:
Latch (address: 0xc00000039cd98040)

Latch wait queue:

PAGE HEADER:
Page header for page 0xc00000039cd96000
pageno=121 nextpg=122 prevpg=0 ptnid=6 syscomments timestamp=0005 54dcda6e
nextrno=47 level=0 indid=0 freeoff=8010 minlen=20
page status bits (pstat): 0x911 (0x0800 (PG_XHEADER), 0x0100 (PG_ADDEND), 0x0010 (PG_RNOFREE), 0x0001 (PG_DATA))
second set of page status bits (dol_stat2): 0x02 (0x02 (PG2_DOL_DATAPG))
dol_pprivstat status bits: 0x01 (0x01 (DOLPG_PRIV_COMMITTED))
pagesize=8192, page version=1, ncfs=38, ndeleted=1, insert free space=124
pagetailts=0xda6e

DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
------------------------- Done --------------------------


Database Collation problem when comparing two databases

First: Collations for columns are stored per-each column.

Second: The ALTER DATABASE ... COLLATE ... statement changes only the default collation of the database itself. It does not change the collation of any existing columns within the database. This change affects:

database-level meta-data (mostly names of database-level objects: tables, columns, procedures, functions, triggers, indexes, users, views, constraints, etc, etc) except in Contained databases (where the DB-level meta-data is always Latin1_General_100_CI_AS_KS_WS_SC)
new string columns that do not specify a COLLATE clause. New via:

CREATE TABLE ...
ALTER TABLE ... ADD
DECLARE @TableVariable TABLE ... (these default to the database's collation, not [tempdb]'s collation)

altered string columns that do not specify a COLLATE clause (i.e. ALTER TABLE ... ALTER COLUMN ...)
new or altered columns that do specify COLLATE using DATABASE_DEFAULT
string literals and variables

For an in-depth look at changing database-level (and even instance-level) collations, please see my post:

Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

For more info on working with collations in general, please visit: Collations Info


How many rows can a SQL Server 2012 table hold, and when should I partition it?

How many records can a MSSQL2012 table hold?

As stated on the MSDN page for Maximum Capacity Specifications for SQL Server (for SQL Server 2012):

"Rows per table = Limited by available storage" (same for both 32-bit and 64-bit platforms)

  But when should this partitioning start happening? 1 table per year? 5 years?

This all depends on the needs of the system. There is no inherent need to partition, ever really, solely based on the issue of performance. Partitioning is mainly intended as a means of more easily managing getting mass amounts of data into, or out of, a table, as quickly as possible and causing as little contention as possible. If the desire was purely to assist query performance, maybe start TESTING it around 1 billion rows, but even then, if you have a good data model and good indexing, you probably won't need to even bother with this. Also, filtered indexes and even filtered statistics probably would do well enough for many cases where people choose to implement table partitioning (if their intention is purely performance-related).

But if your need is to quickly remove a large block of rows, perhaps for aging out older data, then table partitioning will help as you can SWITCH the "old" data out. And on this level it is not a matter of rows but a matter of what amount of time you want to manage. If you want to switch out data monthly, then do monthly partitions. If you want to age-off data yearly, then try yearly partitions.

UPDATE

Not sure why I didn't mention this earlier, but you should take a look at Partitioned Views. That is when you have multiple tables of identical schema and a View that does a UNION ALL between them, and each table has a CHECK CONSTRAINT enforcing a particular range of data within that table (and so the Query Optimizer knows where to get the data from). By doing this, you can have two tables -- current and historical -- and then have queries that hit either one or the other (if the time-frame is known ahead of time, such as a query that only hits the most recent 90 days), or uses the View if the data could be in either one. Please see the following for more info:

Partitioned Tables v. Partitioned Views–Why are they even still around?
MSDN page for CREATE VIEW (go to the Partitioned Views subsection of the Remarks section)
Using Partitioned Views

I believe you can even do a combination where the "current" table is partitioned (so that you can quickly switch in the incoming data and switch out the data that is becoming "old"), a non-partitioned table for historical, and a Partitioned View to join the two of them. Then you just need a way to get the data from the newly switched-out partition into the "historical" table.

Also, with regards to performance, there are other features offered, depending on what edition you are using (some only come with Enterprise Edition). But you should look into ColumnStore Indexes, Data Compression, and maybe a few other things.


Proper normalization for an Orders table--whether to split or no

The objective of normalization is to remove update anomalies. Specifically, changing a single fact should result in a change to one column of one row only. Consider how this would apply in your proposed designs if, say, ShippingDestination were altered, which is a very reasonable business requirement.

In the first design multiple rows may change as, potentially, there are multiple rows for a single SalesOrderNumber, each with its own PartID. With the second design only a single row is updated. So the second design is more normalized.

For a different point of view you can look at the dependencies - what values change in unison. In the first design if you go from one row to another with the same SalesOrderNumber the ShippingDestination will be the same as will DueDate. However OrderQuantity will differ between these two rows. So OrderQuantity depends on more than just SalesOrderNumber - you need to know PartID also. Two different dependencies so two different normalised tables. (There's more to Functional Dependencies / Dependency Decomposition, but that's the gist of it.)

2NF talks about "part key dependency" - whether all of the columns in the key are required to determine all non-key columns' values. Looking at the first design there is a composite key of (SalesOrderNumber, PartID). As we move from one row with a particular SalesOrderNumber to the next with a the same number some columns (ShippingDestination for example) will always be the same by definition. PartID is in the key but is not required to determine the value of ShippingDestination. So the first design is not 2NF.

3NF talks about "transitive dependency" - whether a column's value changes because the key changes or because a separate non-key column's value changes. I don't know enough about the actual business rules to say if the second design is in 3NF so I'll invent a scenario to demonstrate. Let's say a customer can belong to only one channel. As we move from one row in Order to the next we see that both CustomerNumber and Channel change value. However, Channel changes value not because there is a different SalesOrderNumber but because there is a different CustomerNumber. Channel varies by SalesOrderNumber by way of (i.e. transitively) CustomerNumber. In my made-up scenario Channel should be moved from Order to a new table (Customer) keyed by CustomerNumber.

To violate BCNF the table must have more than one candidate key which share at least one column. Neither design has this so, once in 3NF, the design is also in BCNF.

So yes, split the tables.


Is pg12 cache used with two SELECT on same table but different fields

The cache stores blocks read from from disk. A single block contains one or more rows from a table.
As both queries read the same data, they request the same blocks. So yes, the second query will be reading the blocks from the cache.


How many row level locks can I have to same row?

Quote from the manual

  PostgreSQL doesn't remember any information about modified rows in memory, so there is no limit on the number of rows locked at one time. However, locking a row might cause a disk write, e.g., SELECT FOR UPDATE modifies selected rows to mark them locked, and so will result in disk writes

(Emphasis mine)

So the only performance issue that you might get, is the number of disk writes heavy locking might cause.

While there is an "unlimited" number of row locks in the system, there is a limit on the number of objects (e.g. tables) that can participate in locking for a single transaction, which is controlled through max_locks_per_transaction

That property controls the number of objects one transaction can hold locks for, not the number of locks. A transaction can lock a million rows in a single table. However the default value of 64 for max_locks_per_transaction means that a transaction can hold locks for at most 64 tables (but e.g. a million rows in each table). So if your transactions never touch more than 64 tables, there is nothing to worry about.

A common reason to increase this value is if you are dropping a lot of tables in a single transaction e.g. through a drop owned by current_user statement to clean up a database.


postgresql: PANIC: cannot operate with inconsistent data

The main problem was different configuration in postgres.conf, after modifying shared_buffers and several others (configuration based on pgtune for given hardware):

maintenance_work_mem = 1GB
effective_cache_size = 22GB
work_mem = 15MB
wal_buffers = 8MB
shared_buffers = 7680MB
max_connections = 1024

After that I've hit another error:

2016-04-18 15:36:39 UTC [5150-1] FATAL:  could not create semaphores: No space left on device
2016-04-18 15:36:39 UTC [5150-2] DETAIL:  Failed system call was semget(5432064, 17, 03600).
2016-04-18 15:36:39 UTC [5150-3] HINT:  This error does *not* mean that you have run out of disk space.
  It occurs when either the system limit for the maximum number of semaphore sets
 (SEMMNI), or the system wide maximum number of semaphores (SEMMNS), would be
 exceeded. You need to raise the respective kernel parameter.  Alternatively,
 reduce PostgreSQL's consumption of semaphores by reducing its max_connections
 parameter. The PostgreSQL documentation contains more information about
configuring your system for PostgreSQL.

Which could be fixed by increasing kernel limits:

echo 250 32000 256 256 > /proc/sys/kernel/sem

(this shouldn't be needed for PostgreSQL > 9.3)


Relational databases are not specially good for storing information that you would say "have very variable structure".

A fully normalized structure would probably represent an image of your kind with two tables:

one for pictures (picture_id,background_color)
and another for non_background_pixels_of_pictures (picture_id, x, y, color).

But this would probably be terribly space consuming and inconvenient for image processing. It all depends on which kind of "queries" you would be performing, and which tools you'd be using.

Alternatives (based on PostgreSQL):

Store a reference to a standard image file (the path of the file). If this is done for "scientific" purposes, I guess you want a lossless storage format. This will allow to use very many libraries or programs that work directly with standard image formats (TIFF, JPEG, PNG, ...).
Store the standard image data format as binary data (a "blob", using the bytea type in PostgreSQL, for instance; or the Large Objects Facility).
Use some specific types or extensions for images.

For instance, with certain versions of PostgreSQL, you can use:

a. PostPic:

  PostPic is an extension for the open source PostgreSQL dbms that enables image processing inside the database, like PostGIS does for spatial data. It adds the new 'image' type to the SQL, and several functions to process images and to extract their attributes.

b. PostgreSQL-IE

  [...] To facilitate the development of new CBIR systems, we propose an image-handling extension to the relational database management system (RDBMS) PostgreSQL. This extension, called PostgreSQL-IE, is independent of the application and provides the advantage of being open source and portable. The proposed system extends the functionalities of the structured query language SQL with new functions that are able to create new feature extraction procedures, new feature vectors as combinations of previously defined features, and new access methods, as well as to compose similarity queries. PostgreSQL-IE makes available a new image data type, which permits the association of various images with a given unique image attribute.

NOTE: this seems to have been a doctoral thesis or some other research program... from 2006. Check also their download page.

c. pg_image, A bitmap image datatype for JPEG and PNG images.

  This extension provides an IMAGE datatype for the storage of bitmap images, which verifies that the image data is in a recognized format (PNG or JPEG) and provides accessor functions for the image metadata (format, width, height) as well as the image data itself.
  Planned features include conversion between formats, cropping and scaling, annotating with text, and compositing of multiple images.
  Depends on the GD library for image loading and manipulation.
  Compatible with PostgreSQL 9.2 and 9.3beta2.

NOTE: Looks like it's not being maintained.

Other references:

Storing Images in PostgreSQL @ Stack Overflow. It provides a very good explanation of the bytea vs large objects approach.
Filesystem vs. Postgres for images.
Store Images in the Database
Store, manipulate and analyze raster data within the PostgreSQL/PostGIS spatial database


IP configuration for MultiSubnet AAG setup

There are a few things going on in your question. To start I presume you are referring to an Always On Availability Group, just to be clear. You shared a link about multi-subnet failover.

Multi-subnet failovers are a feature, not a requirement. So the two nodes in your data center don't need to be on different subnets. Even when you go to San Jose later, it depends on if the subnet is "stretched" there whether you even need to worry about multi-subnet failover or not.

There are also various network connections involved in an Always On Availability Group.

At the very least there needs to be a public Network that all the nodes can see each other on and receive traffic from the outside on. You could setup with just this one network. You can be, as your link indicates , on separate subnets, but if you are on one? That makes life easier.

You can also add (and I say should) other networks if you don't then this one public network handles all of the functions:

Cluster Communications/Heartbeat - this is a network where the windows cluster nodes can send heartbeat traffic. These don't need to communicate on the public network and just need to be routable to one another.

SQL Server Availability Group Replication Network - The Availability Group replicas talk to each other over a network. You don't have to configure a separate network, but many environments prefer to segregate their traffic to a network that only has this replication traffic. These NICs would not need to speak to the public or rest of the netowrk, and just need to be routable to each other.

You didn't ask about those extra networks, and it seemed like you were just talking about the public network, but I wanted to throw a few thoughts out.


How do I select the value with SERIAL data type in postgres

There is no actual serial data type. serial is just a notational convenience.
The resulting data type is integer for serial or bigint for bigserial

Related answer on SO with more details:
Auto increment SQL function

You'd have to be more specific for a more specific answer.


What Triggers sp_recompile in database without triggers, stored procedures, etc?

The SQL Server engine doesn't explicitly call sp_recompile in order to remove plans from the cache.  As far as I know anyway.  There might be features of SQL Server that do this, but I haven't heard of it.

The mostly likely culprit is that some individual or user process is calling sp_recompile.  Hopefully you can tune the tracing process you mentioned to capture the user executing this call.

I recently noticed that calling this on a table or view takes a SCH-M lock on that table or view, which would explain the blocking (no reads or writes can take place against the table with this lock in place).  It does not take very long on the systems I have access to, so I'm not sure why it would cause blocking for a long time.


Performance of updating row if content has changed

If your business logic layer(s) know that there has been no change (i.e. it has already re-read the data and can compare it to the provided input then you can simply not send anything to the DB.

If you don't know before hitting the data layer if there has been any change or not then you can do something like:

UPDATE yourTable
SET    field1 = @input1
  ,    field2 = @input2
       ...   ...   ...
  ,    fieldN = @inputN
WHERE  idField = @ID
AND    (
           field1 <> @field1
        OR field2 <> @field2
           ...   ...   ...
        OR fieldN <> @fieldN
       )

This avoids any update if nothing has changed so it more efficient (the lookups needed to reject the update would be needed to perform it anyway and the extra CPU time for the comparison is vanishingly small unless you have huge data in any of the columns), but it is more work to maintain and more prone to errors so most of the time I would recommend keeping it simple and updating without checking every column, i.e. the standard:

UPDATE yourTable
SET    field1 = @input1
  ,    field2 = @input2
       ...   ...   ...
  ,    fieldN = @inputN
WHERE  idField = @ID
-- note no comparison, just update

The maintainability issue is even worse for NULLable columns as fieldN <> @fieldN has to be changed to (`fieldN <> @fieldN OR (fieldN IS NULL AND @fieldN IS NOT NULL) OR (fieldN IS NOT NULL AND @fieldN IS NULL) because comparisons between NULLs are always false (NULL is not equal to NULL, but it is also not not equal to NULL - NULL by definition is unknown so all comparisons (=, !=, <, >, ...) return false unless your DB supports non-ansi behaviour like https://stackoverflow.com/questions/9766717/). If this is all baked into you data access layer then fine, some ORMs for instance provide such optimisation as an option, but you don't want to be manually writing and maintaining it for every update statement in your application.

The DB (ever engine I'm aware of) will perform a full write of the relevant data pages even if no values have changed - they won't spend the few extra CPU cycles checking. Away from the efficiency of the update itself you need to consider knock-on effects and certain business requirements. If there are triggers that take action on updates to the table they will fire even if nothing has changed, this could be a significant consideration if you have any complex triggers to consider. From a business logic point of view do you need to log that the user actively saved the record with no changes? If you are relying on automated change capture of some sort (such as your own triggers, temporal table features in postgres and the upcoming MSSQL2016) to track changes then you (or your end users) need to decide if the data model should care that a no-change update happened or not. Of course if the record includes a "last saved" column this all becomes a moot point - there is always something to update upon save if only that one column.


After a pg_dump from DB1 and a restore to DB2 (with psql -f, most likely), ALTER SCHEMA will take care of all internal reverences. Obviously, any code you store outside the database must be updated - you mentioned that already.

What might be less obvious: functions bodies have to be updated manually, if there should be any references to the schema name. Can only include functions you wrote yourself.

There is also less common stuff like external references via dblink, including self-references, that would need to be adapted. You'll know about it if you are using it.

Other than that, the search_path must match your respective setups in the two databases. This concerns general settings in postgresql.conf, but also settings per DATABASE or per ROLE and possibly options supplied with the connection from your apps.

Then you should be good to go.

OR, and that's what I would do, you hack the dump. After you dump from DB1, replace all occurrences of the old schema name with an editor of your choice. Then run the edited SQL script via psql -f to restore to DB2. All done.

Of course, if the name is not unique across the whole dump, you will have to be careful to replace only relevant strings.

If your setup is huge and the schema name is not very selective, you might want to restore to DB2 as is, ALTER SCHEMA, pg_dump and delete from DB2 again, and do the rest in your editor before you restore for good.


Making Session Keys of SQL instances Identical in SQL Availability Group

When you restore a database that has encrypted data to a different instance, with a different Service Master Key (SMK), the data won't decrypt properly because of SQL Server's Encryption Hierarchy.  The "restore" of the master key you mention is really decrpyting the data using the Database Master Key (DMK) password and then re-ecrypting the data with the new instance's SMK:

OPEN Master Key Decryption By Password = N'<redacted>'-- Password used when creating DMK
ALTER Master Key ADD encryption by Service Master KEY

There are a number of ways around having to do this after every failover, but the way I prefer in this scenario is to backup a SMK from the current primary node in your Availability Group (AG), and restore that to all other Instances in said AG so your SMKs match throughout.  Once this is done, you may need to run the above statement against each db that's not sitting in a read-only state on any Secondary Replicas so that any encrypted data will be readable after any future listener failover events.  The whole point of having an AG is the ability to perform a quick and transparent failover, so a shared SMK shouldn't be considered a risk in my opinion.

This article goes over this and walks you through things in much more detail, but take some time to set this up so you can take full advantage of Availability Groups.


You should be able to get the result that you want by using a LEFT JOIN on each of the tables.

select a."text_area",
  c."id_cor",
  c."text_cor",
  d."type"
from area a
left join color c
  on a."id_area" = c."id_area"
left join atv t
  on c."id_cor" = t."id_cor"
  and c."id_uo" = t."id_uo"
left join dest d
  on t."id_dest" = d."id_dest"
where t."id_uo" = 1222
order by c."id_cor"

See SQL Fiddle with Demo

Result:

| TEXT_AREA | ID_COR | TEXT_COR | TYPE |
----------------------------------------
|     info1 |      1 |   create |    M |
|     info1 |      2 |    sssss |    E |

You will see that when I used the LEFT JOIN on the atv table, I joined it to the color table on both id_cor and id_uo.


As mustaccio pointed out this is a "gaps and islands" problem:

declare @date date = '2020-01-16';

with ct as
(
    /* First you must detect the gaps, filtering those rows lesser than the selected date */
    select
        OrderDate,
        case when
            datediff(day,
                     coalesce(lag(OrderDate) over (order by OrderDate), OrderDate),
                     OrderDate
                     ) <> 1 then 1 else 0 end rst
    from
        orders
    where
        OrderDate <= @date
)
, ct2 as
  (
      /* Then set an island of each group */
      select
          OrderDate,
          sum(rst) over (order by OrderDate) as grp
      from
          ct
   )
   /* And finally count the affected rows */
   select top 1
       count(OrderDate) streak
   from
       ct2
   group by
       grp
   order by
       max(OrderDate) desc;

| streak |
| -----: |
|      5 |

db<>fiddle here


some InnoDB settings in My.cnf are messing up everything, why?

The next time you start mysql successfully, please run these

SHOW VARIABLES LIKE 'innodb_log_file_size';

This will show you the size of the InnoDB Log Files. The names of those files are (by default)

/var/lib/mysql/ib_logfile0
/var/lib/mysql/ib_logfile1

In order to change the size of the InnoDB Log Files, you must

Add innodb_log_file_size = 15M to my.cnf
service mysql stop
rm -f /var/lib/mysql/ib_logfile*
service mysql start (InnoDB Log Files Get Recreated)

I wrote about this before : How to safely change MySQL innodb variable 'innodb_log_file_size'?

This will recreate the InnoDB Log Files.

With regard to

  I should note that Horde_groupware database have innoDB tables, when everything is messed up and I SELECT * FROM Horde_groupware.tblname I get the error says bad information in .frm file.

If Horde_groupware.tblname is an InnoDB table and you get this error, run this:

SHOW ENGINES;

You should see all the supported storage engines for the mysql instance. With the erro you are see on that table, there is one of three possiblilities:

InnoDB is not listed as one of the storage engines
InnoDB is marked as DISABLED
The table itself is corrupt


Is it safe to call pg_ctl reload while doing heavy writes?

Yes, it is safe, at least on 9.2, and I'm not aware of any major changes in that regard since 8.4. If absolute certainty is required you should initdb a test instance to experiment with before changing the production system.

In the following tests with Pg 9.3-pre, if PostgreSQL cannot re-read the config file due to a syntax error or other serious problem it'll skip the reload, eg:

LOG:  received SIGHUP, reloading configuration files
LOG:  syntax error in file "..../postgresql.conf" line 215, near token "!"
LOG:  configuration file "..../postgresql.conf" contains errors; no changes were applied

If an individual parameter is invalid it'll apply the other changes but skip that param:

LOG:  received SIGHUP, reloading configuration files
LOG:  -10 is outside the valid range for parameter "vacuum_cost_delay" (0 .. 100)
LOG:  configuration file "..../postgresql.conf" contains errors; unaffected changes were applied

Even if you somehow managed to find a case where PostgreSQL would shut down or even crash, the worst that you'd have to do would be start it back up again and resume your writes from the last commited point. Not-yet-committed work is guaranteed to be rolled back and committed work is guaranteed to be applied to the database after disconnect, a database restart or even after a full system crash.

If your bulk writes don't periodically commit their work and write a local checkpoint, they probably should so you can resume them if they're interrupted. Reducing transaction lengths also makes some admin tasks easier and more efficient for the database.


Finding all functions/stored procedures that reference a certain procedure/function?

sys.sql_expression_dependencies should give you what you want if you don't want to search through the definition of every single object (especially when references may or may not have schema prefixes).

I think you can get what you want with the following query:

SELECT
    o.name AS ReferencingObject,
    sd.referenced_entity_name AS ReferencedObject
FROM sys.sql_expression_dependencies  AS sd
INNER JOIN sys.objects AS o
    ON o.object_id = sd.referencing_id
WHERE sd.referenced_entity_name = 'my_object_name';


Is it possible to add a DB to an Always On AG and setup replicas at different time?

I would recommend you either restore/attach the database to the primary node on the SQL server. From there you could add it to the AG but not have it replicate. Ultimately, to get it on the 3rd(remote) node - you'd likely have to break the AG and re-add the database (if you're allowed the downtime) after it's already copied to the remote node. This would allow it to sync up.

So this approach would mean you'd have 3 stages:

Stage 1: 10TB Database on Primary Node restored/attached - not in AG
Stage 2: 10TB Database added to AG. Remove remote node from AG.
Stage 3: Copy 10TB on remote node. Rebuild AG to include remote node. Allow AG to sync and catch up with data.

If this isn't clear let me know more so I can help you (such as OS/SQL version).


SQL query to INFORMATION_SCHEMA.TABLES to only return those I have select access to

Just add WHERE clause and use HAS_PERM_BY_NAME:

select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
from information_schema.Tables
where (TABLE_TYPE = @TableType) -- Optional parameter
AND HAS_PERM_BY_NAME (
   QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME),
   'OBJECT', 'SELECT') = 1
order by TABLE_NAME


Does a change in a query executed with sp_executesql cause recompilations?

It won't actually cause a recompilation, rather you have a completely new query and so it will have to be compiled for the first time.

If these sp_executesql statements are being called from within a stored procedure there might be some other cause for the recompile (such as a temp table or option).

I would recommend following the steps in http://support.microsoft.com/kb/243586 to troubleshoot exactly what statement is causing your recompilations.


How does Log shipping knows to keep track

You can use log shipping to send transaction logs from one database (the primary database) to another (the secondary database) on a constant basis. Continually backing up the transaction logs from a primary database and then copying and restoring them to a secondary database keeps the secondary database nearly synchronized with the primary database.

Here is the answer of your questions
- If configured correctly then log shipping software start from the point the last backup-restore process happened
- The Answer of your second question is really depends upon how you configure it.

As we are using red gate software and we are taking backup after every 15 minutes and in this way we can lost maximum of 15 minutes of data, as the secondary database nearly synchronized with the primary database.

hopw it will help.

You can read more Monitor Log Shipping


Error updating multiple rows on a table with triggers

The issue is that in SQL Server, Triggers fire once per operation, not once per row. So when you do multi-row operations, the subquery in SET @Key1 = (SELECT Counter FROM INSERTED) is returning a Counter value per each row updated. You need to restructure the INSERT as being a set-based operation as follows:

CREATE TRIGGER [dbo].[NS_Art_Export_ProdUpdate]
ON [dbo].[Produtos]
AFTER UPDATE
AS BEGIN
  SET NOCOUNT ON;

  IF (UPDATE(Codigo) OR UPDATE(NOME) OR UPDATE(Familia) OR
      UPDATE(CodIVA) OR UPDATE(qtEmbal) OR UPDATE(Volume) OR
     UPDATE(PesoBrt) OR UPDATE(PesoLiq) OR UPDATE([Status]))
  BEGIN
    INSERT INTO dbo.Log_NSArtSoft
            (Tabela, Tipo, Key1, Key2, Data_Update, Data_Export, Flag_Update, Data_Export2)
      SELECT 'Produtos', 'U', ins.[Counter], ins.[Prod_Gen], GETDATE(), 0, 0, 0
      FROM   INSERTED ins;
  END
END;
GO

Please keep in mind that the UPDATE function only indicates whether or not the column is in the SET clause of the UPDATE statement (in terms of an AFTER UPDATE Trigger; the UPDATE function returns true for all columns in the context of an INSERT operation). It does not indicate whether or not the value of that column has changed. Meaning, for the query of UPDATE table SET ColA = ColA, calling UPDATE([ColA]) will return true even though the value is guaranteed to be the same.

Assuming that you do not want to log rows in which no values for the specified columns have changed, you need to JOIN the inserted and deleted tables and test each column between those two pseudo-tables. When testing the columns, you need to wrap NULLable columns in ISNULL(), and when the column is a string type (not XML) then you need to use a binary Collation to make sure that all changes get logged (even if they equate when linguistic rules are applied). Hence, the SELECT statement would change as follows (I have no idea what the datatype of any of the columns are, so the particular usage below for each one is purely for example):

  SELECT 'Produtos', 'U', ins.[Counter], ins.[Prod_Gen], GETDATE(), 0, 0, 0
  FROM   INSERTED ins
  INNER JOIN DELETED del
          ON del.[Key1] = ins.[Key1]
         AND del.[Key2] = ins.[Key2]
  WHERE  ins.Codigo <> del.Codigo -- NOT NULL number column
  OR     ISNULL(ins.[Status], -9999) <> ISNULL(del.[Status], -9999) -- NULLable number column
  OR     ISNULL(ins.PesoBrt, '1/1/1900') <> ISNULL(del.PesoBrt, '1/1/1900') -- NULLable date column
  OR     ins.NOME <> del.NOME COLLATE Latin1_General_100_BIN2 -- NOT NULL string column
  OR     ISNULL(ins.Familia, N'45#$%$%^$59jh35$%^$%~!@~!@#|{}\[]') <>
         ISNULL(del.Familia, N'45#$%$%^$59jh35$%^$%~!@~!@#|{}\[]')
         COLLATE Latin1_General_100_BIN2 -- NULLable string column

P.S. It is also a good idea to include SET NOCOUNT ON; at the beginning of Triggers and Stored Procedures (unless a client application specifically needs that output, which is usually not the case).


Using OLE DB Source SQL command vs desinging the query using the object tools

I'm having a hard time thinking about any advantage to laying it out in SSIS other than being able to visualize the logic behind the query. Which is something I would expect developers to be able to do by looking at a query, not a design surface, anyway.

The main disadvantage of doing that is that you're effectively overriding/bypassing the Query Optimizer by dictating the physical operations yourself. If cardinality estimates change behind the scenes, the physical operations that exist in the SSIS design surface may no longer be optimal to service the query.

Had the query been serviced all at once through the normal pipeline:

You gain the insights and dynamicism of the Query Optimizer
It's much easier to develop, debug, and test
It's easy to get a sense of what the process is doing (I don't want to have to open 1,000 Property dialogs to figure out where the data is coming from)
Plan cache bloat is minimized, since fewer plans will end up there
The number of roundtrips to the server is minimized, thus improving performance
This is the expected way to do it, which greatly reduces the WTF Factor

I would really only encourage using the SSIS operators to do joins if the data is coming from multiple disparate sources (the real power of SSIS!), or if you can prove that the performance will be better doing it that way (subject to the caveats above).

Even so, I would still attempt to group things as much as possible into single queries (i.e., one query for sales data, one for inventory, etc.). Depending on your environment, all the business and query logic you need could be placed inside stored procedures, views, etc., of the source database(s); this greatly simplifies the amount of SQL code that ends up in SSIS, and I highly recommend doing this if you can.


The whole point of Patroni is that you don't have to do that yourself, but that Patroni does it for you automatically.

The nodes communicate regularly via a distributed consensus store that decides who is online and who isn't, and arbiters who of the available nodes should become the leader.

You can also tell Patroni manually to switch leader, even if all nodes are up (a "switchover").


It is not nice - sort of anti-join

http://sqlfiddle.com/#!9/cb0124/3

SELECT a1.x, b.z
FROM (
        (SELECT DISTINCT x FROM a) a1,
        b
     ) LEFT JOIN a a2 ON (b.z = a2.y AND a2.x=a1.x)
WHERE a2.x IS NULL;


You can execute this to see where the error log file is supposed to be written:
show global variables like 'log_error';

This can (sometimes) show a relative path, relative to the datadir.
From the documentation:

The error log is active by default. The log-error=filename option determines where the output will be written. If no file name is specified, the log will be written to host-name.err. If no absolute path is specified, the file will be written to the data directory (determined by the value of the datadir system variable).
On Unix systems, if the --log-error option is not used, the errors are written to stderr (usually, the command line).
On Windows, if the --console option is specified, and --log-error is not used, the errors are written to the console. If --log-error is present, --console is ignored.

Additionally, for warning messages, the log_warnings variable need to be > 0. There are different warning levels, and the higher the value of log_warnings, the more kinds of warning messages are logged. For details, see the documentation for the log_warnings variable.
To see you current setting, run:
show global variables like 'log_warnings';


My best guess, in the absence of a table CREATE script, would be something like:

SELECT rt.id, rt.type, rt.total_no_of_rooms,
    IFNULL(SUM(r.no_of_rooms), 0) as "reserved rooms",
    rt.total_no_of_rooms - IFNULL(SUM(r.no_of_rooms),0) as "rooms available"
FROM Roomtypes rt
    LEFT OUTER JOIN Reservation r ON r.roomtype_id = rt.id
        AND CURRENT_TIMESTAMP BETWEEN r.check_in AND r.check_out
GROUP BY rt.id, rt.type, rt.total_no_of_rooms

This query should get the number of rooms available for each room type now (CURRENT_TIMESTAMP), but you can replace that with any other time stamp, e.g. '2018-05-17 10:00:00'.

To see how many rooms are available within a data range - check-in at 2018-04-11 10:00:00 and latest check-out at 2018-04-12 09:59:59:

SELECT rt.id, rt.type, rt.total_no_of_rooms,
  IFNULL(SUM(r.no_of_rooms), 0) as "reserved rooms",
  rt.total_no_of_rooms - IFNULL(SUM(r.no_of_rooms),0) as "rooms available"
FROM Roomtypes rt
  LEFT OUTER JOIN Reservation r ON r.roomtype_id = rt.id
      AND '2018-04-11 10:00:00' >= r.check_in
      AND '2018-04-12 09:59:59' <= r.check_out
GROUP BY rt.id, rt.type, rt.total_no_of_rooms

Here's an sqlfiddle example (MySQL 5.6) with create script.


Publishing database changes with SSDT that include altering data types of columns

I've been tempted to bypass that flag also but have come down on the side of your co-workers and now try to deal with these issues "correctly". The (marginally) less clunky route is to use pre and post deployment scripts to do the work with a rename.

Rename the existing table in a pre-deployment script.
With the existing table missing the table in focus will be created as per the new schema definition.
In a post-deployment script copy from the renamed original table to the new version.

Depending on the nature of the target you may of course need to take care of dropping and recreating foreign key constraints.


How to query database tables structures for DATE NOT NULL DEFAULT '0000-00-00' and ALTER them in MySQL

You can find that information in the INFORMATION_SCHEMA:

select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT
from information_schema.columns
where column_default = '''0000-00-00 00:00:00''';

Since MySQL lacks the concept of schema, TABLE_SCHEMA is the same as the database

Note, that the default is '0000-00-00 00:00:00', so ' is part of the value. You, therefore, need to quote with an additional ' to get it right.

When you have fixed the default and whatever data there is, I suggest you set 'NO_ZERO_DATE' in sql_mode to prevent this in the future.

'0000-00-00 00:00:00' have some counterintuitive properties, for example:

create table tt (x int, y datetime not null default '0000-00-00 00:00:00');
insert into tt (x) values (1);
select * from tt where y is null and y is not null;
+------+---------------------+
| x    | y                   |
+------+---------------------+
|    1 | 0000-00-00 00:00:00 |
+------+---------------------+

You can change the default value like:

alter table tt alter column y set default '1900-01-01 00:00:00';

So you can use the previous query to generate alter statements like:

select 'ALTER ' || TABLE_SCHEMA || '.' || TABLE_NAME
    || ' ALTER COLUMN ' || COLUMN_NAME || ' SET DEFAULT ''1900-01-01 00:00:00'';'
from information_schema.columns
where column_default = '''0000-00-00 00:00:00''';

Note that you need 'PIPES_AS_CONCAT' in sql_mode for || to work


mongodb “WriteConcernFailed” on a replica set

Just to clarify in case someone stumbles upon this in the future.

MongoDB Replicasets need a majority vote to elect a primary. So uneven amount of nodes are advised because a 4 member replica set requires 3 votes as well as a 5 member replica set.
But with a 5 member replica set 2 members can go down while with 4 only 1 can go down before the replica set enters a read only state.

The issue of OP is a different one though, it is about write concern majority. Write concern majority requires a majority vote of all the databearing nodes in a replicaset.
An arbiter is not a databearing node and thous can't vote on it but will still be counted for the total amount of votes required.

Example: In OPs deployment there are currently 2 Databearing nodes and an arbiter.
If he adds another databearing node and does a transaction that requires a write concern majority vote to pass he can't get a majority vote because the total amount of members is 4 and thous the majority is 3.
Since the third databearing node is still syncing it can't pass an OK on the write concern and that forces it to time out.
If he were to remove the arbiter and add the databearing node after it should finish syncing and be able to vote on the write concern after.
In the meantime he should not recieve any more errors because the total amount of votes required in a PSS deployment (Primary Secondary Secondary) is 2 and since there are 2 healthy data bearing nodes the write concern majority query will pass.


Index by subkey inside subkey inside array in jsonb

First off, I would consider a normalized DB design instead of the convoluted jsonb column, which makes indexing and complex queries harder.

  How to index by [array].customer_id.id?

See my answer to your previous question:

Index by subkey in jsonb

You could have two GIN indexes like instructed there, one on tour_id, another one on customer_id, or a single multicolumn index, and then query:

SELECT * FROM tbl
WHERE  f_extract_tour_ids(registration)     @> '{d61802ff-3eec-4a72-97ca-832f51b96bf0}'::uuid[]
AND    f_extract_customer_ids(registration) @> '{f492b26d-d260-4bcb-8e96-6532fdf38665}'::uuid[];

Note, this returns rows with tour_id and customer_id matching in any top-level object of the array, not necessarily in the same. If you want to find rows where tour_id and customer_id match in the same object, you have to do more ...

jsonb_path_ops index

However, with a growing number of different queries (requiring multiple special indexes), a single generic (much bigger) jsonb_path_ops index on the whole column becomes more competitive. Or even one with the default operator class operator class jsonb_ops - which is bigger, yet.

CREATE INDEX foo ON tbl USING GIN (registration jsonb_path_ops);

Queries to go with it:

For rows with tour_id and customer_id matching in the same top-level object:

SELECT * FROM tbl
WHERE  registration @> '[{"tour_id": {"id": "d61802ff-3eec-4a72-97ca-832f51b96bf0"}
                        , "customer_id": {"id": "f492b26d-d260-4bcb-8e96-6532fdf38665"}}]';

For rows with tour_id and customer_id matching in any top-level object, not necessarily the same:

SELECT * FROM tbl
WHERE  registration @> '[{"tour_id": {"id": "d61802ff-3eec-4a72-97ca-832f51b96bf0"}}
                       , {"customer_id":{"id":"f492b26d-d260-4bcb-8e96-6532fdf38665"}}]';

This solution is slower because of the much bigger index carrying all the other noise in your jsonb column. But it is much more versatile and simpler.
If the first solutions make your head ache, use this one.


According to the MySQL docs:

mysqld reads options from the [mysqld] and [server] groups.

I could trace this back to MySQL 5.0, so this has been there for quite some time.


Massive update to Clob fields oracle 10g

Something like this for each field.

--Setup.
DROP TABLE t1;
CREATE TABLE t1 (ID Number(4), VBase Clob);
INSERT INTO t1 (SELECT level, '*' || '.magic()' || '*' FROM dual
   connect by level <=2001);
COMMIT;
SELECT * FROM t1;

--Replace.
DECLARE
   cBatchSize CONSTANT Number(3) := 100;
   Cursor vQuery Is SELECT ID, VBase FROM t1 FOR UPDATE;
   Type tTable Is Table Of t1%rowtype;
   vData tTable;
   vNew Clob;
   vLocation Number(10);
   vLobLength Number(10);

   Procedure LobReplace(ioLob In Out Clob, iOld In Varchar2, iNew In Varchar2 )
   As
       vPosition number;
   Begin
       vPosition := dbms_lob.instr(ioLob, iOld);
       If (nvl(vPosition,0) > 0) Then
           DBMS_Lob.Copy( ioLob, ioLob, dbms_lob.getlength(ioLob)
              , vPosition+length(iNew), vPosition+length(iOld) );
           DBMS_Lob.Write( ioLob, length(iNew), vPosition, iNew );
           If (Length(iOld) > Length(iNew)) Then
               DBMS_Lob.Trim(ioLob
                  , dbms_lob.getlength(ioLob) - (length(iOld)-length(iNew)));
           End If;
       End If;
   End;

BEGIN
   Open vQuery;
   Loop
      Fetch vQuery Bulk Collect Into vData Limit cBatchSize;
      For i In 1..vData.Count() Loop
         LobReplace(vData(i).VBase,'.magic()',' ');
      End Loop;
      ForAll i In 1..vData.Count
         UPDATE t1 SET vBase = vData(i).VBase WHERE ID = vData(i).Id;
      Exit When vData.Count() < cBatchsize;
   End Loop;
   Close vQuery;

   COMMIT;
END;
/

--Verify.
SELECT * FROM t1;


Now I was wondering if it is locking on a Row-level or on a Table-level?

From BOL

UPDLOCK
Specifies that update locks are to be taken and held until the transaction completes. UPDLOCK takes update locks for read operations only at the row-level or page-level. If UPDLOCK is combined with TABLOCK, or a table-level lock is taken for some other reason, an exclusive (X) lock will be taken instead.
When UPDLOCK is specified, the READCOMMITTED and READCOMMITTEDLOCK isolation level hints are ignored. For example, if the isolation level of the session is set to SERIALIZABLE and a query specifies (UPDLOCK, READCOMMITTED), the READCOMMITTED hint is ignored and the transaction is run using the SERIALIZABLE isolation level.

Also have a look at Designing Transactions and Optimizing Locking

WITH (UPDLOCK) This hint is used in DML statements to cause SQL Server to acquire update locks instead of shared locks. The update locks acquired will be held for the remainder of the transaction. Update locks are compatible with shared locks but are not compatible with other update locks. Therefore, SELECT statements that acquire update locks will be blocked, while SELECT statements that only intend to read the data, and not change it, are allowed. WITH (UPDLOCK) is usually specified if data is to be read first and updated later. Because of this, it can be referred to as a “SELECT WITH INTENT TO UPDATE”.


Firebird Configuration: firebird.conf Parameters Unit of Measurement

The default firebird.conf is extensively documented in the file itself, and explicitly explains this, as an example, in Firebird 3:

# Please note, by default a number of the values are specified in **Bytes** (Not KB).
# You may add obvious abbreviations k, m and g in the end of a number to specify
# kilobytes, megabytes and gigabytes.

The support for k, m, g is new in Firebird 3, previous versions provided a conversion table.

And for values that expect a different unit, this is documented, for example:

# Maximum summary size of each user trace session's log files in MB.

Some values that expect bytes, repeat this in their specific documentation (but not all).

Not all numeric values are sizes in bytes, some represent a number of data pages, port numbers, seconds, booleans, or percentages. For each of these the unit is documented.


T-SQL: How to switch all partitions from one table to another?

For anyone wanting a solution to this, here's a stored procedure I cooked up:

------------------------------------------------------
-- Switches all non-empty partitions from a switch_in
-- table into a target table, and switches all replaced
-- partitions from the target table into a switch_out
-- table.
------------------------------------------------------
CREATE PROC [dbo].[SwitchAllPopulatedPartitions]
        @table     varchar(1000),
        @table_in  varchar(1000) = null,
        @table_out varchar(1000) = null
AS
    SET @table_in = ISNULL(@table_in, @table + '_SwitchIn');
    SET @table_out = ISNULL(@table_out, @table + '_SwitchOut');

    DECLARE @object_id     int = OBJECT_ID(@table);
    DECLARE @object_id_in  int = OBJECT_ID(@table_in );
    DECLARE @object_id_out int = OBJECT_ID(@table_out );

    DECLARE @SQL varchar(max) = 'TRUNCATE TABLE ' + @table_out + ';' + CHAR(13);

    SELECT @SQL = @SQL
                + 'ALTER TABLE ' + @table + ' SWITCH PARTITION ' + CAST(partition_number as varchar(10))
                + ' TO ' + @table_out + ' PARTITION ' + CAST(partition_number as varchar(10)) + '; ' + CHAR(13)
                + 'ALTER TABLE ' + @table_in + ' SWITCH PARTITION ' + CAST(partition_number as varchar(10))
                + ' TO ' + @table + ' PARTITION ' + CAST(partition_number as varchar(10)) + '; ' + CHAR(13)
    from sys.partitions
    where OBJECT_ID = @object_id_in and index_id = 1 and rows > 0

    SET @SQL = @SQL + 'TRUNCATE TABLE ' + @table_in + ';' + CHAR(13);

    EXEC (@SQL);
GO


ALTER TABLE ... SWITCHing from regular table to partitioned table fails

When you are working with partition switching, SQL Server will need to verify that the source table/partition boundaries can fit in the destination table/partition boundaries.  In other words, you're trying to switch data from dbo.temp_table to dbo.play_table's partition 2.  Think of it like this, the data for the c1 in dbo.temp_table is constrained only by the data type (int), so you can have values ranging from -2,147,483,648 to 2,147,483,647.  But conversely, you're destination (dbo.play_table partition 2) has a range from 4 to 2,147,483,647.

Your data does not violate this, but it is the metadata that can't allow this.  You could just as easily insert the value -10 into dbo.temp_table.  The partition switching would fail the same way and make more sense, as -10 does not fit in dbo.play_table's 2nd partition boundaries.

If you wanted to make this code work, you'd need to explicitly tell SQL Server that dbo.temp_table will never have any data that won't fit in dbo.play_table's 2nd partition.  You could do this with a check constraint:

/******************************************************************************
    your code omitted for brevity
******************************************************************************/

-- move contents of play_table to temp_table, which is not partitioned
-- but is in the same filegroup
ALTER TABLE dbo.play_table
    SWITCH PARTITION 2 TO temp_table;
PRINT 'Switched from partitioned table to non-partitioned table';

/******************************************************************************
    added check constraint so that data can fit in the destination partition
******************************************************************************/
alter table dbo.temp_table
add constraint CK_TempTable_C1 check (c1 >= 4);
go
/******************************************************************************
    end of added code
******************************************************************************/

-- move data back to partitioned play_table from unpartitioned temp_table
-- this will no longer FAIL
ALTER TABLE dbo.temp_table
    SWITCH TO play_table partition 2;
PRINT 'Switched from non-partitioned table to partitioned table';

/******************************************************************************
    your code omitted for brevity
******************************************************************************/

That above sample addition to your code makes this a working solution.  Now SQL Server knows that the data in dbo.temp_table can fit in partition 2 of dbo.play_table because of the added check constraint to dbo.temp_table.


using mysql calculate balance of each currency where credit and debit in same column?

I would try something like this to get the results that you are looking for. What this is doing is determining how to add everything up based on the record being a credit or debit and subtracting the debit values instead of adding them.

SELECT CLIENT_ID,
       SUM(IF(currency='USD',IF(ACTION_TYPE='credit',ACTION_AMOUNT, - ACTION_AMOUNT))) as USD_balance,
       SUM(IF(currency='EURO',IF(ACTION_TYPE='credit',ACTION_AMOUNT, - ACTION_AMOUNT))) as EURO_balance,
       SUM(IF(currency='DR',IF(ACTION_TYPE='credit',ACTION_AMOUNT, - ACTION_AMOUNT))) as DR_balance
FROM my_table
GROUP BY CLIENT_ID,currency


I create my DALs with LINQ. Personally I feel its a lot easier to debug queries using LINQ and I dont enjoy bouncing from MSSQL and VS so in a way It also keep things simple for me. In the past I've come around some very nasty bugs that took a while to point as a (for example) datatype error on a stored procedure, while It would have taken minutes to spot such error on the VS debugger (maybe wouldn't have even compile in the first place).

I do use stored procedures in cases when then query is just to complex and performance is worlds apart from using a LINQ. But this would account for about 10% of any given DAL class, its not really that much.

If you check some other sources you'll notice it mainly comes down to Security and Performance... There are other implications and I dig the argument of the refactoring, but if you refactor your database you still need to recreate the EF mapping and work around those changes on your stored procedures, views, etc.

More on the subject:

Linq2sql vs Stored Procedures

Why I Avoid Stored Procedures (And You Should Too)

In this answer there's an argument for readability that also makes a lot of sense from the software development side of the discussion:


Release unused space from SQL Server database

Just deleted my previous answer as I started a proposed canonical question for this type of question. In case that flagging doesn't work, a short answer for you here.

You have a couple options. Big "Warning" here is - Shrinking isn't always a good thing, it fragments your indexes and takes some time. So proceed cautiously.

You could shrink it in smaller "chunks" using DBCC SHRINKFILE. Pros: No development really required, potentially less downtime than migration. Cons: You are still shrinking a database, you still have to deal with fragmented indexes, but you should do some index rebuilds anyway because you've removed a lot of data.
You could build a new database and select data into it. Pros: You aren't shrinking. You are starting "fresh" and clean. You can use this as a time for other cleanup potentially. Cons: Probably more effort than just shrinking in chunks, may require a larger down window depending on how complex the environment is. Potential risk for missing something in the scripting.
If you chose the shrink route, remember to rebuild your indexes and deal with the resulting fragmentation.


Select products and include all cities it belong to

I have a function that I keep on the master database for these cases.
I will start by creating my function as per code below:

use master
go

create function dbo.getCities( @thecities varchar (100))
returns table
as
return

WITH    cteTally
      AS (SELECT TOP 100
            ROW_NUMBER() OVER (ORDER BY AC.[name]) AS n
          FROM
            sys.all_columns AS AC CROSS JOIN
            sys.all_columns AS AC2  )

,RADHE2 (THECITIES)AS (
    SELECT
        LTRIM(SUBSTRING(','+@THECITIES+',',N+1,CHARINDEX(',',','+@THECITIES+',',N+1)-N-1)) AS THE_CITIES
    FROM
        cteTally C
    WHERE
        -- need the - 1 because cteTally strts with 1
        n < LEN(',' + @THECITIES + ',') AND
        SUBSTRING(',' + @THECITIES + ',', n, 1) = ','

)

SELECT * FROM RADHE2
GO

then I will create my tables according to your question.
I did not bother about indexes here, but you shoudl have a look at this, because this process is bound to be very process intensive.

SET NOCOUNT ON

DECLARE @PRODUCT TABLE (ID INT,
                    TITLE VARCHAR(100),
                    CONTENT VARCHAR(100),
                    CITY VARCHAR(100))

DECLARE @CITIES TABLE (ID INT,
                   TITLE VARCHAR(100))

INSERT INTO @PRODUCT VALUES (1,'PROD1', 'BANANAS', '1,2,4')
INSERT INTO @PRODUCT VALUES (2,'PROD2', 'APPLES', '3')
INSERT INTO @PRODUCT VALUES (3,'PROD3', 'STRAWBERRIES', '3,5')
INSERT INTO @PRODUCT VALUES (4,'PROD4', 'MANGO', '1,2,4')
INSERT INTO @PRODUCT VALUES (5,'PROD5', 'PEAR', '2,4')

INSERT INTO @CITIES VALUES (1,'CARACAS')
INSERT INTO @CITIES VALUES (2,'RIO')
INSERT INTO @CITIES VALUES (3,'TALLINN')
INSERT INTO @CITIES VALUES (4,'LIMA')
INSERT INTO @CITIES VALUES (5,'MOSCOW')

this gives me the following tables:

 select * from @PRODUCT
 select * from @CITIES

then, the final step, I use CROSS APPLY with the function, as you can see on the code below:

SELECT P.*
      ,C.TITLE
FROM @PRODUCT P
CROSS APPLY MASTER.dbo.getCities( p.CITY) R
INNER JOIN @CITIES C ON R.THECITIES = C.ID
order by p.ID

and that gives the following result, which I believe is what you are looking for:


What's the best practice for representing set operations in a relational database?

Currently, when I query all the entities belonging to a given set I'm
  returning a union of all the entities in each collection belonging to
  that set.

The proper way to query all entities belonging to a given set is to JOIN your tables and use a WHERE clause to limit the set to the given set.

  …given 4 collections, when I select all the entities in set 1, I want
  to get (collection 1 INTERSECT collection 2 EXCEPT collection 3)

This statement does not make sense.  Either you are selecting all entities in set 1 or you are not.  If you are only selecting collection 1 INTERSECT 2 EXCEPT 3, then you are not selecting all entities in set 1.  You can select all entities in set one that are also in collection 1 INTERSECT 2 EXCEPT 3, let us know if this is what you meant.

It looks like you have different patterns of ways you would like to transform the data.  In Oracle, if you give each pattern a function returning a pipelined result set, you can then pass the appropriate collection identifiers to the appropriate pattern function and have it return the requested results.  So, for you first example you could call:

SELECT * FROM TABLE(MyPatterns.Pattern1(1,2,3);

The Pattern1 function would do the x INTERSECT y EXCEPT z and return the results.


Partitioned collection in TokuMX 1.5 is available now.

See Introducing Partitioned Collections for MongoDB Applications


Can you search using an index while indexes are being updated in oracle11g?

Here is what the 11.2 Concepts Guide says about doing selects while dml is changing an index (ephasis mine):

  The database automatically maintains and uses indexes after they are
  created. The database also automatically reflects changes to data,
  such as adding, updating, and deleting rows, in all relevant indexes
  with no additional actions required by users. Retrieval performance of
  indexed data remains almost constant, even as rows are inserted.
  However, the presence of many indexes on a table degrades DML
  performance because the database must also update the indexes.

So, you might see sightly degraded performance if you have a lot of indexes and a lot of DML taking place, but I suspect this is insufficient to cause the appearance of hanging.  I suggest you trace a session to see what is slowing the search down.


time_stamp - 1800 subtracts two numbers. If the value in the time_stamp column is not a number, it is converted.

From what you've shown, converting the values in the time_stamp column (which begin with the characters 2015) to a number results in the value 2015; subtracting 1800 from that results in 215. This is probably not what you want.

To group by hours offset by half an hour, just add half an hour to the timestamp, then drop the minutes/seconds:

GROUP BY strftime('%H', time_stamp, '+30 minutes')


That shows comments that you define using the command comment on:

The following table definition:

create table foo
(
  id integer primary key,
  name text not null
);

comment on table foo is 'This is the famous foo table';
comment on column foo.id is 'This is the primary key';
comment on column foo.name is 'This is the name of the thing';

Will then show up like this:

postgres> \d+
                                List of relations
 Schema |   Name    | Type  | Owner  |    Size    |         Description
--------+-----------+-------+--------+------------+------------------------------
 public | foo       | table | thomas | 8192 bytes | This is the famous foo table
(1 row)

postgres> \d+ foo
                                             Table "public.foo"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target |          Description
--------+---------+-----------+----------+---------+----------+--------------+-------------------------------
 id     | integer |           | not null |         | plain    |              | This is the primary key
 name   | text    |           | not null |         | extended |              | This is the name of the thing
Indexes:
    "foo_pkey" PRIMARY KEY, btree (id)

postgres>


SQL Server Impersonation is just NOT working

If your user does not need elevated permissions for ad hoc queries, then you can actually do better with Module Signing than with Impersonation. Impersonation comes with various complexities, especially when dealing with multiple Databases as that then requires setting TRUSTWORTHY to ON, something which is generally ill-advised as it is a security risk. Module Signing allows for more granular control over what permissions are being effectively granted, and works across Databases without needing to enable TRUSTWORTHY. I have explanations and examples of this across various answers here. One of the most recent ones, and one which includes a detailed walk-through of this cross-Database setup, is:

Stored procedure security with execute as, cross database queries, and module signing

The general concept is this:

Create a Certificate in master
Create a Login from that Certificate
Grant any required Server-level permissions to that Login

Create that same Certificate in Database A
Create a User in Database A from the Login
Grant Certificate-based User any necessary Database-level permissions
Sign one or more Stored Procedures / Functions in Database A with that Certificate
Grant real user Execute permission on the Stored Procedure(s) / Function(s)

Create a User in Database B from the Login
Grant Certificate-based User necessary permissions in Database B

In this setup, the real user/person doesn't actually have any elevated permissions. Only the code that has been signed with the Certificate has the permissions, and it can only do what the code inside it says to do. And if someone tries to change that code to do something else or more, then the module loses the signature and no longer has any elevated permissions at all. Which forces someone to resign the module, which is a good indication that the code needs to be reviewed.

UPDATE

  I have to weigh the risk between marking a database as trustworthy and creating users in every database that login will touch.

There is no real difference there. Using Impersonation, if you are creating an App User in each DB for the App Login, then it is the same to create the Certificate-based User in each DB. It is one line of code to CREATE USER [App] FROM Login [App];. BUT, if you don't need to create an App User because the App Login is in the sysadmin fixed server role, then you can just as easily place the Certificate-based Login in the sysadmin fixed server role and most likely not need to create and of the Database-level Users. However, you still end up with an account that is likely more privileged than it needs to be, just to avoid creating those Users.


SQL Server : explicitly create an index on a primary key and unique fields

How can I execute this create table statement and ensure that the two
  automatically created indexes are stored on MY_INDEX_FILEGROUP as I am
  assuming that these automatically created indexes will be stored on
  MY_DATA_FILEGROUP that the table data will be stored in.

The table will exist on whichever filegroup contains the clustered index, because the clustered index is the data.

By default, a primary key index is clustered, so if you specify a different filegroup for that, the table ends up there.

  is it possible to seperate the automatically generated indexes into a different filegroup, from the data?

Yes, but don't. Bears will eat you.

  And as a seperate but related question is it possible to enforce a constraint like PRIMARY KEY or UNIQUE whilst preventing the automatic creation of the associated index?

No, this is how SQL Server enforces the constraint behind the scenes. Technically, SQL could use any mechanism to enforce constraints; it just makes the most sense to use an index to do it.


What you're looking for is probably something like this.

Select
*
FROM @Hold h
WHERE logoncount = (select max(logoncount) from @Hold h2 where h2.name = h.name )


Can I trust the effective permissions of default database roles?

Here's what I just tried in a test database (SQL Server 2012):

GRANT EXEC ON SCHEMA::dbo TO db_datareader

...And the response I got:

Msg 4617, Level 16, State 1, Line 1
Cannot grant, deny or revoke permissions to or from special roles.

So I'm assuming you're reasonably safe. Of course, that's not to say you couldn't do something wacky like having a stored procedure testing for membership in those roles, and implementing its own security functionality based on the results.


Backups jobs are successful but cannot see differential backup in the media

This part of code clearly states that you are appending the diff backup

Set @dt = cast(year(getdate()) as varchar(4)) + @m SET @File = ''D:\SQLBackups\SQLBackups_database\database'' + @dt + ''.BAK'' EXEC master..xp_fileexist @File, @i out IF @i = 1 BEGIN --FILE EXISTS set @strSQL = ''BACKUP DATABASE database TO DISK = N'''''' + @File + '''''' WITH DIFFERENTIAL'' END ELSE BEGIN --FILE NOT EXISTS set @strSQL = ''BACKUP DATABASE database TO DISK = N'''''' + @File + '''''' WITH INIT'' END

Instead of carving out your own solution which clearly is not smart e.g. exclude offline db, take log backups etc is not good .. use Ola's backup solution.

Also, make sure you test your restore plan by actually restoring the dbs and trying a point-in-time recovery where possible.


Check the .err log file which will often be in your datadir which in your case appears to be /var/lib/mysql/, although sometimes the error log is under /var/log/. The error log file should hopefully have some good clues as to why the MariaDB server won't start up.

You can also have a look in your MariaDB config file which appears to be /etc/my.cnf and see if there's a log_error entry that defines a different location for the file.

See also the MariaDB documentation for the Error Log.

Your installation procedure may or may not have created a password for the root account. With most Linux distros I've encountered it doesn't create one. A password will in any case only be used when connecting over TCP. If you're logged in on the actual server, you can instead connect over the Unix socket with the Linux root user without a password. You can specify the socket file if you you know where it is and you want to be sure it's using the correct one, e.g. mysql -S /tmp/mysql.sock. Obviously, this won't work until you have successfully started the MariaDB server.

See also the MariaDB documentation on the Authentication Plugin - Unix Socket.


SQL Management Studio 2016 account disabled

So, my questions. Does uninstall then reinstall can give me access again?

Yes, uninstall and reinstall will give you access again

  If yes, which should I uninstall-reinstall? My sql server or ssms?

SQL Server


Best practice for storing record metadata

The columns you are talking about occupy 20 bytes (if aligned without padding):

  creation time, update time and creation source

timestamp .. 8 bytes
timestamp .. 8 bytes
integer   .. 4 bytes

The tuple header and item identifier for a separate row in a separate table alone would occupy 23 + 1 + 4 = 28 bytes plus the 20 bytes of actual data, plus 4 bytes of padding at the end. Makes 52 bytes per row. See:

Configuring PostgreSQL for read performance

Concerning storage you have nothing to gain. Concerning performance you hardly lose anything with just 16 - 24 bytes more per row.

The columns also directly belong to the row, so it makes sense to keep them together. I make it a habit to add exactly such columns (plus separate source for the last update) to all relevant tables.

It's also easier to write a TRIGGER ON INSERT OR UPDATE to keep them current.

Long story short: a strong vote for your option 1.

Where I would go for option 3:
If the metadata is updated often, while the core row is not. Then it might pay to keep a separate 1:1 table to make UPDATEs cheaper and reduce bloat on the main table - or even go for option 2.

Where I would go for option 2:
If the set of metadata columns is highly repetitive. You could have a FK column to the set of metadata in the main table(s). Does not save much for three small columns like in your example.


Setting locale for Chinese characters in Postgresql

You weren't far from finding it.
Here's the documentation you're looking for.

Yes you can define a specific locale for chosen columns (from 9.1 version).


How to execute maintenance job from script and get back the result

If your end goal is to use JAMS, then it handles many of the SQL agent job conversions for you.

JAMS includes a native conversion utility for SQL Agent jobs to streamline the transition your organization’s transition to enterprise scheduling. Comprehensive mapping preserves the SQL Agent logic you’ve created and extends each job with an array of properties for advanced scheduling.

Taken from http://www.jamsscheduler.com/platforms/sql-databases/


Solutions for Introducing a new Entity In Between An Existing Association

For such a structural change in your data model you will have to go “all in”. You can’t model the same entities in 2 ways. As you correctly observed, it will lead to endless grief for years.

That means your “old” view of the world will have to adjust.
Have you considered creating a single order to contain all batches for existing projects, and name it “Migration Order” or something meaningful? Your application may choose to ignore it when presenting, or not. This will maintain a unified hierarchy.


Like '%' search for value withing longblob column in MySql is slow

The problem was finally fixed by Increasing the INNODB_BUFFER_POOL_SIZE from 256M to 1G
After that the query run less than second.


How query errors affect Postgresql DB performance

Does massive errors count affect the DB performance?

Not really.

It will make the error log [a bit] larger, because each error gets written there, but that should be trivial in size compared to the database itself.  Each of those writes to the log will take [some] time, but not a great deal.

What will get noticed is that the Application trying to execute these statements is failing to do so.
Each of those failures is losing company Data.
That matters.

At some point, the Development team for the Application will get involved and they may well start throwing rocks in your direction because there's a table missing from the database.  Be prepared for the question: "Whose responsibility was it to create that table in the first place?"


Even after Disabling Read-Ahead and clearing buffer , why there is Logical Read?

A logical read is counted when a single page is retrieved from buffer cache during query execution. This is counted regardless of whether a physical or read-ahead was used to cache the page, or if the page already existed in the buffer cache. Consequentially, logical reads is a measure of how many times pages were actually touched in memory during query execution.

A physical read is counted when the storage engine reads one or more pages from storage because the requested page is not already in cache. This excludes physical IO done by read-ahead scans.

A read-ahead read is counted when the storage engine reads one or more full extents (each extent being 8 contiguous pages) from storage during sequential scans. Read-ahead reads prefetches data into memory aggressively so that the pages are likely to be already cached when needed by the query. The number of extents read by read-ahead scans varies by SQL Server version and edition as well as fragmentation. I've seen read-ahead scans read as much as 4MB at once using a single scatter-gather IO during scans in Enterprise Edition using the latest SQL Server version.

Note that the SQL Server storage engine behaves differently with a cold cache (e.g. after restart or DBCC DROPCLEANBUFFERS). When the cache is not yet warmed up (Buffer Manager's Target Pages met), the storage engine performs full 64K extent reads instead of single 8K page reads to read pages into cache. This warms the cache much more quickly than would otherwise occur.

I prefer to focus on logical reads rather than physical for the purpose of query and index tuning because that's a better measure of the work performed by the query. It is only by happenstance whether data is cached or not. Testing with a cold cache is more of a measure of storage performance than query performance, IMHO, and isn't representative of what will occur under a production workload with a warm cache.


Referencing label from Select clause in where, simple sql query

"Aggregates" cannot be referenced in WHERE.  But they can be moved to HAVING:

SELECT  student.name, MAX(grades.final_grade) as max_grade
    From  grades , students
    where  student.id = grade.student_id
    HAVING  max_grade = 90

Also it is =, not ==.


Clear PostgreSQL logs in AWS Ubuntu Server

That is a bash problem. The redirection (>) is not part of the command executed as root, but as your current user.
Use this workaround:
sudo sh -c "> /var/log/postgresql/postgresql-11-main.log"


This isn't an error. It means that there weren't any changes in the database.


I am using export data from query (table ) to XML in SQL when I have needs for autonomous transactions.

Way I do: I wrote a SQL-CLR stored procedure which using C# (code) and XML.Writer for writing data to disk

Here is parts of my code in C# (you need to compile this )

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static void NapisiXMLtoFile(string fileName, SqlXml xmlLog )
    {
        try
        {
            if (System.IO.File.Exists(fileName))
            {
                System.IO.File.Delete(fileName);
            }
            File.WriteAllText(fileName, xmlLog.Value.ToString());
        }
        catch (Exception ex)
        {
            SqlContext.Pipe.Send("Error writing to file " + ex.Message);
        }
    }
}

Here is how you register your procedure

CREATE PROCEDURE [dbo].[clr_write_to_file]
    @fileName [nchar](100),
    @xmlLog [xml]
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [WriteToFile].[UserDefinedFunctions].[NapisiXMLtoFile]
GO

and finally here is how you call your stored procedure to write file to disk

exec dbo.clr_write_to_file @imeFajla,@xmlLOG


How to do PostgreSQL replication on CentOS 7?

The archive_command setting doesn't fit the recovery.conf but the postgresql.conf file.

The documentation you're following is deprecated.

Please switch to the official documentation.

Here are the steps you need to perform:

Primary node settings (postgresql.conf, pg_hba.conf)
Primary node backup (pg_basebackup, pgbarman, pgbackrest... Whatever)
Restoring on secondary node (manually, pgbarman, pgbackrest... Whatever)
Secondary node settings (recovery.conf, postgresql.conf, eventually pg_hba.conf)

The part you didn't do is simply the initial backup/restore. You can't do replication without it.


ANSI NULL OFF By Default in database option

Some of the database settings are useless. As you have noticed.

The client API (ODBC etc) will override them. And, the client app (SSMS for instance) can further override whatever was overridden by the API.

So why are those database settings still there? Because stone-age API didn't do such override. I'm thinking DbLibrary here, but this might apply to other very old APIs as well.

Why do they have such weird database option values as default? Because of backwards compatibility. I.e., so you can create a database "as default" and use a stone-age API "as default" and get the same behaviour as you did back in 1989.


access all_mview or user_mviews from app code

by default all users can read the user_mviews and all_mviews views since the SELECT right is granted to public:

SQL> SELECT grantee, privilege, table_name
  2    FROM dba_tab_privs
  3   WHERE table_name IN ('USER_MVIEWS', 'ALL_MVIEWS');

GRANTEE  PRIVILEGE  TABLE_NAME
-------- ---------- ------------
PUBLIC   SELECT     USER_MVIEWS
PUBLIC   SELECT     ALL_MVIEWS

If for some reason this grant has been removed, you would need to regrant the rights to either public or a specific user (with a DBA account):

GRANT SELECT ON user_mviews TO <user>;

Edit: By definition, USER_MVIEWS describes materialized views owned by the current user while ALL_MVIEWS describes the materialized views accessible to the current user (the user needs to be granted SELECT on the mview either directly or through a role).


That is just documented behavior. I don't think anyone messed with the settings.

See data type precedence on MSDN.

  When an operator combines two expressions of different data types, the
  rules for data type precedence specify that the data type with the
  lower precedence is converted to the data type with the higher
  precedence.

As noted in the comments the empty string gets converted to 0 in any numeric type and to 1900-01-01 00:00:00.000 when converted to a date.

EDIT: I think your real problem is that your design is so that you have to join on fields of a different data type. The only way to get around this is to have a convert on your join clause which will hurt query performance. The main problem is probably with the schema design

EDIT:
There was a lot of discussion in the comments that have been moved to chat. However illogical it may seem, converting an empty string to other data types produces arbitrary values.

This code:

SELECT CONVERT(int, '')
SELECT CONVERT(float, '')
SELECT CONVERT(date, '')
SELECT CONVERT(datetime, '')

Produces this output:

0
0
1900-01-01
1900-01-01 00:00:00.000

You could expect then that this behavior is consistent between other preceding datatypes and expect that converting 0 to a date would produce the same arbitrary value but it doesn't.

SELECT CONVERT(date, 0)

Produces

  Explicit conversion from data type int to date is not allowed.

Because it's not a supported conversion

while

SELECT CONVERT(datetime, 0)

Returns

  January, 01 1900 00:00:00

So yes, it's weird and arbitrary, but actually documented and explainable.


Need help with SQL Server 2014 and disaster recovery

Typically people just have an AG node in their DR environment and do not transfer VMs there (for some of the reasons you noted + more). You should read through the documentation for how AGs work, especially planned manual failovers for when you need to switch node roles.

You could also look at SQL Server replication to replicate your data (even close to real time). It work with DNS and even though the DB is not read only per se, you can set user privileges so they don't modify anything.  The only thing to check is it requires the tables to have a primary key. This could be a good option for your reporting requirements.

Can you consider an upgrade? SQL Server 2016+ has Distributed Availability Groups, which could really solve most of your problems. With Windows Server 2016 you can have cluster nodes from different domains. Your AG would not require it to be in the same domain or network and you can even mix SQL Servers. Read more in the documentation for Distributed availability groups.


Can I trust the Database Engine Tuning Advisor recommendations?

The tuning advisor (DTA) can yield information about missing indexes, but the results are only as good as the workload you've provided and your ability to interpret the results and fill in the gaps.

Your .trc file may only contain certain events, not all, and may not cover a full business cycle. So if you have a big set of reports that people run at the end of the month, you may be missing the most important part of your workload.
DTA only considers improving the performance of read queries, and puts no thought whatsoever into what impact new indexes will have on the write portion of your workload. Indexes aren't free.
DTA has a tendency to recommend multiple redundant indexes, that only differ by one additional key column or an include column. In a lot of cases, one index might be able to satisfy both queries that generated the recommendation for the two individual indexes.

So, the information can be useful, but I would never let any client blindly implement all of the indexes. You need to take a lot more data points into consideration.

For example, how have you come to the conclusion that adding indexes will improve any performance issues you are having? Have you performed any baselines and identified any bottlenecks?


Designing a database which can track stock movements

What you're looking for is a VIEW. Here is a link for more description.

https://msdn.microsoft.com/en-us/library/office/ff836312.aspx

This is what it will look like, and you can create a form around the view.

CREATE VIEW vw_cust_mach_loc
AS
SELECT cust.CustomerName,
   cust.CustomerAddress,
   cust.SuburbTownCity,
   cust.State,
   cust.PostCode,
   cust.Country,
   cust.FirstName,
   cust.SurnameName,
   cust.PhoneNumber,
   cust.MobileNumber,
   cust.EmailAddress,
   cust.Notes,
   m_lo.CustomerReference,
   m_lo.TimeStamp,
   m_lo.StatusID,
   m_lo.OriginalCustomer,
   m_lo.Notes,
   mach.MachineSerialNumber,
   mach.MachineModeID,
   mach.MachineOtherFields,
   mach.Notes
FROM TBL_Customers cust
   INNER JOIN TBL_MachineLocations m_lo
       ON cust.CustomerID = m_lo.CurrentCustomerID
   INNER JOIN TBL_Machines mach
       ON m_lo.MachineID = mach.MachineID;

CREATE VIEW vw_cust_mach_sup_ctrl_units
AS
SELECT cust.CustomerName,
       cust.CustomerAddress,
       cust.SuburbTownCity,
       cust.State,
       cust.PostCode,
       cust.Country,
       cust.FirstName,
       cust.SurnameName,
       cust.PhoneNumber,
       cust.MobileNumber,
       cust.EmailAddress,
       cust.Notes,
       mach.MachineSerialNumber,
       mach.MachineModeID,
       mach.MachineOtherFields,
       mach.Notes,
       culs.Location,
       culs.Reference,
       culs.TimeStamp,
       culs.OriginalMachine,
       culs.OtherLocationID,
       culs.StatusID,
       culs.Notes,
       supl.SupplierName,
       supl.ContactFirstName,
       supl.ContactLastName,
       supl.ContactTitle,
       supl.Address,
       supl.City,
       supl.Region,
       supl.PostalCode,
       supl.Country,
       supl.PhoneNumber,
       supl.MobileNumber,
       supl.Fax,
       supl.[E-mail Address],
       supl.HomePage,
       supl.Notes,
       cuts.ControlUnitSerielNumber,
       cuts.ControlUnitBarCodeID,
       cuts.ControlUnitStatusID,
       cuts.TimeStamp,
       cuts.Notes
FROM TBL_Customers cust
     INNER JOIN TBL_ControlUnitLocations culs
         ON cust.CustomersID = culs.CustomerID
     INNER JOIN TBL_Machines mach
         ON culs.MachineID = mach.MachineID
     INNER JOIN TBL_Suppliers supl
         ON culs.ManufactureID = supl.SupplierID
     INNER JOIN TBL_ControlUnits cuts
         ON culs.ControlUnitID = cuts.ControlUnitIDMachineID;

You can select from it like a table.

SELECT * FROM vw_cust_mach_loc;
SELECT * FROM vw_cust_mach_sup_ctrl_units;

Example using specific columns instead of *:

SELECT
    Location,
    Reference,
    TimeStamp,
    OriginalMachine,
    OtherLocationID,
    StatusID,
    Notes,
    SupplierName,
    ContactFirstName,
    ContactLastName,
    ContactTitle,
    Address,
    City,
    Region,
    PostalCode,
    Country,
    PhoneNumber,
    MobileNumber,
    Fax,
    [E-mail Address],
    HomePage,
    Notes,
    ControlUnitSerielNumber,
    ControlUnitBarCodeID,
    ControlUnitStatusID,
    TimeStamp,
    Notes
FROM vw_cust_mach_sup_ctrl_units


Best way to build a server inventory db and web interface?

It sounds like you have two separate issues at play here.  The first one is how and where to store the data.  The second one is how to present it.

I suggest building a SQL database to store your data.  That way you have full control over what is being stored, and where.

Once you have the data stored, you can use Microsoft Reporting Services to quickly build some reports based on templates.  You can create as many reports as you like, hosting them locally and granting access to various users as required.

Heck, you could even just use Excel, and have "live" spreadsheets connecting to the database for their data, but that would not be web based.  Reporting services can definitely deploy to a website.


Most restricting permissions for querying view with linked server

The message you are getting is telling you that there is no mapping between the credentials you have supplied and credentials on the target machine. You need to ensure that the credentials you use to setup your linked server also has a login or can be mapped to a login on the linked server.

I suspect that when you set it up you specified the option Be made using the login's current security context which worked for you as you have a login on the remote machine - but it doesn't work for anyone else because they do not have sufficient credentials to query the linked server. (Just a hunch)

Here is a guide on how to set up a linked server and what all of the options are for, I suspect you may need to change the security context you are using to connect to the linked server or add the other accounts to it.

https://msdn.microsoft.com/en-us/library/ff772782.aspx


It sounds as if you simply want to pause and resume your mirror. Have you seen this link yet? http://msdn.microsoft.com/en-us/library/ms190664.aspx

The gotcha with this would be the length of time you expect to have the mirror paused. If you take too long then the principal server could experience a large amount of transaction log growth. if you do not expect to be paused for a long period of time, then you should be fine.

If you feel that you must break the mirror and resume later, then you should do the following:

Break the mirror using this against the principal:
ALTER DATABASE  SET PARTNER OFF

If you are concerned that your maintenance will take a long time, then you should perform regular transaction log backups, so as to make certain the log does not grow out of control and fill up your disks. however, the more trx log backups you have, the more you need to recover to the mirror later.

After you have restored the trx logs, you should be able to get mirroring running again.

Another option would be to reinitialize everything, but I don't know how big of a database this is, it may be too much for you to restore, so trx logs may be the better option.

This link explains it best: http://msdn.microsoft.com/en-us/library/ms189112.aspx

And this link covers more topics: http://msdn.microsoft.com/en-us/library/ms175059.aspx

HTH


SQL Server 2019 install - Python and R failing

Found the issue.  Apparently the null accountName in the error message was the account name for SQL Launchpad, which was blank during the install for some reason.  I repeated the install and gave SQL Launchpad an account name of NT Service\MSSQLLaunchpad, and everything installed correctly.


Difference between Clustered Index and Unique Clustered Index?

You can create a UNIQUE CLUSTERED INDEX only on a column (or combination of columns) that contains no duplicate data. The fact that you create the index with the UNIQUE keyword means that you are now constraining users from adding non unique data. (in the key column(s) of the clustered index)

A CLUSTERED INDEX can be created on a column (or combination of columns) that contains duplicate data. So in this case, the clustered index is not constraining you from adding non unique key data.

However, since a clustered index key is the row identifier, it needs to be able to uniquely identify a row, even if it's created on a column containing duplicate entries. Because of this, if you create a CLUSTERED INDEX for every row that is a duplicate, SQL Server adds a 4 byte uniquifier to the row, so that the index in fact becomes unique. This is all done under the hood.


How to perform multiple selects without using for loop

If you can sanitize input, you can use an IN statement.

SELECT *
FROM t
WHERE id IN ( 1,2,3 );

Or you can chain the WHERE clauses.

WHERE ID = 1 OR ID = 2;


You or some other user is probably still connected to the database (it could be your current query window, or Management Studio's Object Explorer). To break this you can set the database to single user mode, which rolls back any work they were doing (which shouldn't matter, since you're detaching the database anyway).

USE [master];
GO
ALTER DATABASE SimpleProject_Device SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

Now you should be able to detach.

However if you can delete the file then my guess is you are talking about two different physical files. Are you using SQL Server Express and the User Instances / attachDbFileName features perhaps? If so you can search the system for files named Simple_Project_device.ldf - I bet there are more than one, and the one you're trying to detach is not the same one you're trying to delete.

In any event, detach is not the right way to move a database: BACKUP / RESTORE is better. Why? Because if something goes wrong with the backup or the restore, you still have the original. If something goes wrong during detach, you now have ZERO usable copies of your database. With backup/restore, after the restore is successful, you can just drop the source database.