What are the best practices for restricting write-access on a specific table?

By default, no direct access is permitted to either tables or stored procedures.  One must explicitly grant access in order for non-privileged users to have access.  The best practice to simplify security administration is to grant permissions to roles, and control access via role membership.

If you want to restrict access only via stored procedures, take a look at ownership chaining (https://technet.microsoft.com/en-us/library/ms188676).  This allows you to grant execute permissions to individual roles/users without allowing direct access to underlying tables as long as the object involves have the same owner (default is dbo).

Using ownership chaining in your scenario, you could grant only SELECT permission on the tables, with updates allowed only by users with execute permission on the update procs.  No direct table modifications would be allowed.

Edit:

Below is an ownership chaining example.  Note that neither user can update the table directly and the write user can update the table only using the stored procedure.  The implication of ownership chaining is that when you grant execute permissions on a stored procedure, users can access all of the encapsulated objects without direct object permissions as long as the objects have the same owner.

--create objects
CREATE TABLE dbo.MyTable(
      MyTableID int IDENTITY NOT NULL
        CONSTRAINT PK_MyTable PRIMARY KEY
    , SomeData int
    );
GO

CREATE PROC dbo.UpdateMyTable
      @MyTableID int
    , @SomeData int
AS
UPDATE dbo.MyTable
SET SomeData = @SomeData
WHERE MyTableID = @MyTableID;
GO

--create roles
CREATE ROLE TableReaderRole;
CREATE ROLE TableWriterRole;
GO

--grant permissions to roles
GRANT SELECT ON dbo.MyTable TO TableReaderRole;
GRANT EXECUTE ON dbo.UpdateMyTable TO TableWriterRole;
GO

--create user with only read permissions
CREATE USER SomeReadOnlyUser FOR LOGIN SomeReadOnlyUser;
ALTER ROLE TableReaderRole
    ADD MEMBER SomeReadOnlyUser;
GO

--create user with both read and writer permissions
CREATE USER SomeAllowedWriteUser FOR LOGIN SomeAllowedWriteUser;
ALTER ROLE TableReaderRole
    ADD MEMBER SomeAllowedWriteUser;
ALTER ROLE TableWriterRole
    ADD MEMBER SomeAllowedWriteUser;
GO

Be aware that it is the owner rather than the schema name that is relevant for ownership chaining.  If the table and proc were in different schemas, the ownership chain would still be unbroken as long as the schemas had the same owner (AUTHORIZATION).  The object owner is typically inherited from the schema owner.


Oracle will write all the datafiles and such to your DATA directory. You actually have to create a backup in the +flash_recovery_area you have created. What sort of backup are you trying to create?

What command are you using to create your backup?

For instance, if you wanted to execute a Level 0 IMAGE COPY, you could issue the following command:

backup incremental level 0 as copy database tag 'L0_COPY';


Where did you 'read' that the procedure you state is necessary to shrink the table?
Yes, you should use dbms_audit_mgmt to relocate your audit table out of the SYSTEM or SYSAUX table space.  Not to shrink the table, but because that is best practice.
Is it an online operation?  What does the doc say?

Usage Notes
The following usage notes apply:
This procedure involves data movement across tablespaces. This can be a resource intensive operation especially if your database audit

trail tables are already populated. Oracle recommends that you invoke
the procedure during non-peak hours.

Should you move it back to the SYSTEM TS?  No.
Also, when considering shrinking table you need to remember that space that is freed up by deleting rows is still allocated to the table and will be reused by future inserts.  Unless a delete is a one-time cleanup and permanent reduction in the number of rows, a shrink table is worse than wasted effort because future inserts will just incur additional overhead to re-acquire the needed space.
But remember, relocating the table into its own TS is just good management, regardless of shrinking the table.
Also, you state you are on 11.2.0.4. That is out of support and long past time to upgrade.


invalid integer value "CONNECTION" for connection option "port" Previous connection kept

It seems to me that what you really must have done is something like:
\c CREATE SUBSCRIPTION my_sub CONNECTION ...

The arguments to \c are positional, and the 4th position belongs to the port.
I see no other way your message could include 'Previous connection kept', which is exclusively generated by attempts to use \c or \connect


Can a foreign key have a default value equal to 0?

Yes, you can define a column with a default value of 0 as a Foreign Key.  However, for the constraint to work, you would need to have a row in the source table with a value of 0 as well.  Example:

CREATE TABLE dbo.Master
(MasterID INT Primary Key,
 Name NVARCHAR(20));

INSERT into dbo.Master values (0,NULL);
INSERT into dbo.Master values (1,'Full');
INSERT into dbo.Master values (2,'Partial');

CREATE TABLE dbo.Slave
(SlaveID INT Primary Key,
 Description NVARCHAR(20),
 MasterID INT FOREIGN KEY REFERENCES dbo.Master(MasterID) DEFAULT (0));

 INSERT  INTO dbo.SLAVE (SlaveID, Description) VALUES(10,'Quickly');

This works fine.  However, what if Master does not have a 0 MasterID.  Then you would get:

Msg 547, Level 16, State 0, Line 15
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Slave__MasterID__3943762B".
The conflict occurred in database "TEST", table "dbo.Master", column 'MasterID'.

There is a WITH NOCHECK option (in SQL Server) which allows you to create a Foreign Key without the required data existing in the master table.  Likewise, Foreign Key constraints can be disabled and enabled, but I would recommend against doing that for any normal circumstances.  This is defined at:

http://technet.microsoft.com/en-us/library/ms177463(v=sql.105).aspx


How to change/add to schema in a SQL Server Always On Availability Group?

I am wondering how should I go about adding a new table, or modifying the schema of an existing table in an Always On Availability group without incurring downtime?

If you Always On availability group is set up correctly and sync is working no downtime is required. Connect to the primary (preferably using listener) and do your change in the same way you will be doing in a stand-alone server.

Is this fully automatic?

Yes, it is fully automated.

I simply go ahead and create my table on the primary in an availability database and it will then get replicated to the secondary?

Yes, it will get replicated to all secondary nodes (assuming your sync is working correctly).

Few more resources to read on this:

Do schema changes “break” Availability Groups or are they handled transparently?
Whitepaper: Microsoft SQL Server AlwaysOn Solutions Guide for High Availability and Disaster Recovery


What is the fastest way to insert large numbers of rows?

One common approach:

Disable / drop indexes / constraints on target table.
INSERT dbo.[Target] WITH (TABLOCKX) SELECT ...
With credit to JNK of course, you can do the above in batches of n rows, which can reduce the strain on the transaction log, and of course means that if some batch fails, you only have to-start from that batch. I blogged about this (while in reference to deletes, the same basic concepts apply) here: http://www.sqlperformance.com/2013/03/io-subsystem/chunk-deletes
Re-enable / re-create indexes / constraints on target table (and perhaps you can defer some of those, if they are not necessary for all operations, and it is more important to get the base data online quickly).

If your partitions are physical and not just logical, you may gain some time by having different processes populate different partitions simultaneously (of course this means you can't use TABLOCK/TABLOCKX). This assumes that the source is also suitable for multiple processes selecting without overlapping / locking etc., and making that side of the operation even slower (hint: create a clustered index on the source that suits the partitioning scheme on the destination).

You may also consider something a lot more primitive, like BCP OUT / BCP IN.

I don't know that I would jump to SSIS to help with this. There are probably some efficiencies there, but I don't know that the effort justifies the savings.


Allow transparently altering View's underlying table

Looks like this can be done with the event triggers of 9.4. There are some weak points:

pg_trigger_depth() always returns zero (Is this a bug?). This is why I had to use a non-temporary table to prevent recursion.
We need to filter by the altered object name, but there's no way to get it in 9.4. current_query() returns the client's statement even if we're called from a stored procedure.

_

create UNLOGGED table IF NOT EXISTS test_dep_trk_trig_data ( pid integer primary key );

CREATE OR REPLACE FUNCTION test_dep_trk_trig() RETURNS event_trigger AS $$
DECLARE
    v_query text;
BEGIN
  if NOT exists( select * from test_dep_trk_trig_data where pid = pg_backend_pid() ) then
    insert into test_dep_trk_trig_data ( pid ) values ( pg_backend_pid() );
    v_query := lower( current_query() );
    if (v_query like '%test_dep_trk%' and not v_query like '%test_dep_trk_v%') then
      -- RAISE NOTICE '%', v_query;
      drop view if exists test_dep_trk_v;
    end if;
    delete from test_dep_trk_trig_data where pid = pg_backend_pid();
  end if;
EXCEPTION
  WHEN others THEN
    delete from test_dep_trk_trig_data where pid = pg_backend_pid();
    RAISE;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION test_dep_trk_trig_end() RETURNS event_trigger AS $$
DECLARE
    v_query text;
BEGIN
  if NOT exists( select * from test_dep_trk_trig_data where pid = pg_backend_pid() ) then
    insert into test_dep_trk_trig_data ( pid ) values ( pg_backend_pid() );
    v_query := lower( current_query() );
    if (v_query like '%test_dep_trk%' and not v_query like '%test_dep_trk_v%') then
      -- RAISE NOTICE '%', v_query;
      create view test_dep_trk_v as select * from test_dep_trk;
    end if;
    delete from test_dep_trk_trig_data where pid = pg_backend_pid();
  end if;
EXCEPTION
  WHEN others THEN
    delete from test_dep_trk_trig_data where pid = pg_backend_pid();
    RAISE;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER test_dep_trk_trig ON ddl_command_start EXECUTE PROCEDURE test_dep_trk_trig();
CREATE EVENT TRIGGER test_dep_trk_trig_end ON ddl_command_end EXECUTE PROCEDURE test_dep_trk_trig_end();


Connecting to MySQL database from Analysis Services

You can do this using MyODBC. Install the connector, then just set up a DSN and use it as normal.


Advice on fine-tuning query and index for performance

The query seems ok (although the LIMIT 1 inside the NOT EXISTS subquery is not needed) but you are probably missing some index. I suggest:

add a composite index on log_follow (id, other_id):

ALTER TABLE log_follow
  ADD INDEX id_other_id_idx
    (id, other_id) ;

an index on darkhast_follower (status, id_request) might help as well but with such a small table, probably not much.
the datatype of the various id columns (id, other_id, id_request) are varchar(20) with charset UTF8. If you only have ASCII characters there, it would be better to use Latin charset. This will lower the column width from 60 (20x3) bytes to 20, and thus reduce the index size, both in disk and memory. If you only store integers in these columns, it would be even better to use BIGINT, reducing the size to 8 bytes.


Grant the user SELECT, INSERT, UPDATE, DELETE, EXECUTE and VIEW DEFINITION rights to the database (or the schema).  This will grant the user the rights to all the objects within the database (or the schema).

You'll probably also need to give them rights like ALTER ANY OBJECT within the database as well so that they can modify the objects.


Is it safe to run a Windows Failover Cluster Public and Heartbeat on a Single NIC?

How important is it that the heartbeat is on its own network with this Quorum setup and why?

Well what you have asked is really debatable and I should also add that quorum has little significance in cluster network configuration as such. Starting from Windows Server 2008 and onward Microsoft says that you can go on and configure WSFC without any heartbeat network connection. If you have not configured dedicated network for heartbeat the cluster validation wizard will only give you warning and that means your cluster is still supported. Now, but that does not means this is all good. Allow me quote a reason for dedicated NIC for heatbeat (Source)

  Heartbeat communication is used for the Health monitoring between the
  nodes to detect node failures. Heartbeat packets are Lightweight (134
  bytes) in nature and sensitive to latency. If the cluster heartbeats
  are delayed by a Saturated NIC, blocked due to firewalls, etc, it
  could cause the cluster node to be removed from Cluster membership.
  Intra-Cluster communication is executed to update the cluster database
  across all the nodes any cluster state changes. Clustering is a
  distributed synchronous system. Latency in this network could slow
  down cluster state changes.

So if you read above you can get some fair idea why a heartbeat communication might still be important.

Since you have windows server 2016 you can easily go with heartbeat communication without worrying about network binding order, an order which tell which network/route should be given priority. By default, Windows server 2016 uses the Interface Metric property of a network adapter to determine which route has the highest priority. The lower the Interface Metric property value, the higher the priority. More information in this support article

I also believe it is not too much of overhead to configure heartbeat network, if the heartbeat network goes down the WSFC will start using public network for cluster communication and cluster communication would still go on. I think it is more of segregating things and making cluster communication more secure with heartbeat network. BUT if your public network is teamed up well and has enough bandwidth to accommodate easily both cluster and client communication, by all means go ahead without heartbeat network. Please note if you just have public network all the client and internal cluster communications will go through this link so it has to be strong.

Here is what MVP,MCM Edwin Sarmiento has to say about Heartbeat network (Source)

  But here's my take on it: even if we have multiple NICs per node that
  are teamed up, how sure are we that the network switches are redundant
  and highly available? I've seen DR exercises where only the servers
  are tested but not the underlying network architecture. Only when the
  network switches themselves fail do they realize that they are not at
  all highly available. I still recommend having a dedicated network for
  the heartbeat communication and if the customer can guarantee that the
  network layer - switches, routers, etc. - is highly available, then
  I'll be happy with a NIC teaming implementation (thus, my reason for
  having a dedicated network for the heartbeat.)

I believe he is correct, the focus should be more on making "Complete Network" redundant not just part of it.

  Am I right in thinking that if the network goes down, the Quorum Disk Witness will stop the nodes trying to failover, as it will still vote that both nodes are online, even though the nodes themselves are saying the other is offline?

By network you mean the complete public network is down, in that case this  could be single point of failure bringing down the whole WSFC. And this is what precisely Edwin emphasized on above quote. Now if you are saying due to connecting network issue one of the nodes in cluster was removed from cluster membership forcing cluster to calculate quorum and do failover, in that case, since you have 2 votes of Node and quorum disk(> 50 %) the WSFC will remain online and do failover. This network issue would not affect disk/Storage as they are connected via SAN not via cluster public network.

Additional reading:

Windows Server 2008 networking 3 part series

Disclaimer: I must add I am not an network engineer and the detailed discussion of network configuration for WSFC is not within my knowledge scope and believe a network enginner can definitely add more to this answer, I tried to answer your question to best of my knowledge. Hope this helps.


PostgreSQL strategies to deal with disk filling up

On one hand, you can have a look at one of my previous answers to see how you can keep a table size more or less steady.  There you will find a solution with triggers - of course, this can be solved using a cron job as well.  In the latter case I would first check if the row number exceeded a certain limit and the either delete the oldest rows or drop a partition.

On the other hand, as you already noticed, one has to take care of the disk space where pg_xlog is.  When it gets full, it is not that easy to recover...  But checking your database settings you can have a fair estimation how much space you need:

  There will always be at least one WAL segment file, and will normally
  not be more than (2 + checkpoint_completion_target) *
  checkpoint_segments + 1 or checkpoint_segments + wal_keep_segments + 1
  files. Each segment file is normally 16 MB (though this size can be
  altered when building the server). You can use this to estimate space
  requirements for WAL. Ordinarily, when old log segment files are no
  longer needed, they are recycled (renamed to become the next segments
  in the numbered sequence). If, due to a short-term peak of log output
  rate, there are more than 3 * checkpoint_segments + 1 segment files,
  the unneeded segment files will be deleted instead of recycled until
  the system gets back under this limit.

If you don't have replication set up, the maximum is 3 * checkpoint_segments + 1 (times 16 MB).  A typical replicationless setup will need something under 10 GB for pg_xlog, I think.


Is it safe to restart PostgreSQL if process terminated for some reason?

Postgres generally handles server restarts pretty well.  Assuming that all the correct hardware is in place (battery-backed cache, etc.), bringing up Postgres after a crash will automatically cause it to enter recovery mode, where it will attempt to replay all the WAL files since the last CHECKPOINT.  Once it has finish replaying up to a consistent state, you are free to use the database and continue with your business.  Having said that, however, it's up to you as the DBA to perform consistency checks as per your business requirements to ensure there is no corruption.

Disclosure: I work for EnterpriseDB (EDB) as a Support Engineer


Allocation strategy where tables regularly grow and shrink by large amounts

Putting those staging tables on their own filegroup allows you to do piece-meal restores.  This helps get the database up-and-running more quickly in the event of a disaster recovery effort.

Having those tables in their own filegroup will allow you to place them on their own set of disks which may increase performance.


DB2 - Not enough storage in the database heap error when trying to create database

On a 32-bit Windoes system each process, including the DB2 instance, is limited to 2 GB of memory space (3 GB at most, with the appropriate startup switch), regardless of the amount of physical memory available. Your instance configuration requests 4 GB (Size of instance shared memory (4KB) -> AUTOMATIC(1000000)), which cannot be satisfied.

Try changing the INSTANCE_MEMORY parameter to a smaller value, e.g. 50000 pages.

By the way, with DB2 10.1 you should be using automatic storage instead of specifying tablespace containers explicitly.


Linking Oracle 10g XE with SQL server using ODBC

Use the parameter NAMES.DEFAULT_DOMAIN to set the domain from which the client most often looks up names resolution requests. When this parameter is set, the default domain name is automatically appended to any unqualified net service name or service name.

For example, if the default domain is set to us.acme.com, the connect string CONNECT scott/tiger@sales gets searched as sales.us.acme.com. If the connect string includes the domain extension, such as CONNECT scott/tiger@sales.acme.com, the domain is not appended.


Which MySQL storage engine is the most similar to ARCHIVE when that is not available?

InnoDB supports compression as of MySQL 5.1 (with InnoDB Plugin), or natively in MySQL 5.5. The level of compression really depends on your table content, but I see 75% reduction in size as common.

Reads and writes to compressed tables involve more CPU. InnoDB will support your foreign keys as well as any indexes.

I would say it is a fairly good out-of-the-box solution for your problem.

MyISAM only supports compression on a read-only mode.

Or you might find an altogether NoSQL solution to do the job well for you, given that you don't really need indexes.

I'm a relational geek, so I would first go for InnoDB. Just:

ALTER TABLE my_table ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;

KEY_BLOCK_SIZE can be 1,4,8,16; Lower numbers will give you better compression. My experience shows that 4 gives good compression. 1 rarely improves much, and consumes a lot more CPU.


Using the same table for entities that are almost alike when it breaks foreign key integrity?

Typically you would design the database such that someone coming into the database alone could get a handle on how the application worked. Had they seen such a foreign key they may not be aware of it's implications.

You could approach this in two ways:
First, you could do as you mentioned. Create two separate tables and handle them there.

Second, and the way I would recommend, you would create a table to handle all the common fields between the two, and then two other tables, one for internal-specific data, and one for external-specific data. Each of those tables would have a primary-foreign key to the common table. I.e. a Guid or ID.

The downfall of the second method is the application would be required to be aware of the two different tables, and depending on the method you are using to implement it (.NET, PHP, Python, etc) you may find one method easier than the other.

I personally use .NET and C# or VB, depending on the project, and in this situation I would go with the three-table design, as I would create an abstract class ("Event") and then two classes to inherit it and all the common properties ("ExternalEvent", "InternalEvent"). The Event class would handle the interaction of the shared properties, and the InternalEvent and ExternalEvent would handle the logic specific to them.

The upside to this approach is the polymorphism of it. If you add a common field to the two in the future (for whatever unprecedented reason) you only have to add/change it in one location.


You can't use the new columns that way. use a subquery to use the data to calculate
SELECT
wxe4_usern.*,(wxe4_usern.a170_score + wxe4_usern.atw_score + wxe4_usern.x100_score) as darts_total_thrown
FROM
(SELECT
u.ID as id,
(
    SELECT `meta_value` as user_name FROM `wxe4_usermeta`
    WHERE `umeta_id` = u.ID AND `meta_key` = 'nickname'
) as user_name,
(
    SELECT SUM(rounds) FROM wxe4_170 WHERE user = u.ID
) as a170_score,
(
    select IF (count(*) > 0, count(*)*66, 0) FROM wxe4_aroundtheworld WHERE user = u.ID
) as atw_score,
(
    select IF (count(*) > 0, count(*)*100, 0) FROM wxe4_X100 WHERE user = u.ID
) as x100_score
FROM darts.wxe4_users as u) wxe4_usern


Array stringifies to using `{}` in Postgres output

From the docs in Array Input and Output Syntax

  The external text representation of an array value consists of items that are interpreted according to the I/O conversion rules for the array's element type, plus decoration that indicates the array structure. The decoration consists of curly braces ({ and }) around the array value plus delimiter characters between adjacent items. The delimiter character is usually a comma (,) but can be something else: it is determined by the typdelim setting for the array's element type. Among the standard data types provided in the PostgreSQL distribution, all use a comma, except for type box, which uses a semicolon (;). In a multidimensional array, each dimension (row, plane, cube, etc.) gets its own level of curly braces, and delimiters must be written between adjacent curly-braced entities of the same level.

Not sure if stingification of ARRAY is in the spec or not, but for PostgreSQL it's perfectly normal to stingify it with {}. Note, this is not symmetrical with the literal syntax to create an array.

SELECT ARRAY[1,2,3];
  array
---------
 {1,2,3}

But you can see that it's valid and to be expected because you can cast to an array explicitly.

SELECT '{1,2,3}'::int[];
  int4
---------
 {1,2,3}

If in your case you have two jsonb in the ARRAY, it'll get stringified as such

SELECT ARRAY[
  $${"a":"b","c":"d"}$$::jsonb,
  $${"e":"f","g":"h"}$$::jsonb
];
                              array
-----------------------------------------------------------------
 {"{\"a\": \"b\", \"c\": \"d\"}","{\"e\": \"f\", \"g\": \"h\"}"}

Note that a two JSON-objects in a PostgreSQL array, is different from two objects in a JSON array.

SELECT $$[{"a":"b","c":"d"},{"e":"f","g":"h"}]$$::jsonb;
                    jsonb
----------------------------------------------
 [{"a": "b", "c": "d"}, {"e": "f", "g": "h"}]


Yes, but...
Your query is asking a bad question. As-is, the optimizer has no idea if the value 1001 exists in the table. The predicate <> 1000 does not mean "everything under 1000", unless you give it more information.
Setup
CREATE PARTITION FUNCTION
    a_func(int)
    AS RANGE RIGHT FOR VALUES (1000);
GO
CREATE PARTITION
    SCHEME a_scheme
    AS PARTITION a_func ALL TO ([PRIMARY]);
GO

CREATE TABLE dbo.a_table
(
    id int PRIMARY KEY
) ON a_scheme(id);
GO

INSERT dbo.a_table(id)
    VALUES(999), (1000);
GO

Fix One: A Constraint
A check constraint can help, but...
ALTER TABLE dbo.a_table
    ADD CONSTRAINT ck_a_ck CHECK(id <= 1000);

You'll need to beware of getting a trivial plan/simple parameterization. If your literal value is substituted with a parameter via simple parameterization, you'll need to add something to your query to avoid that happening, like this:
SELECT
    a.*,
    $PARTITION.a_func(a.id) AS pid
FROM a_table AS a
WHERE a.id <> 1000
AND   1 = (SELECT 1);

Fix Two: Hacking The Function
If you feel like directing the optimizer manually to a partition, you can query the table like this to avoid hitting unnecessary partitions:
SELECT
    a.*
FROM a_table AS a
CROSS APPLY
(
    VALUES($PARTITION.a_func(a.id))
) AS c (pid)
WHERE c.pid = 1;


Effects of renaming FC and using its original name for AG network name

An alternative you might not have considered is DNS. Obviously changing the connection string is easiest, but if that is a problem for some reason, you could leave the connection strings alone and remap using DNS. More importantly, think about these questions:

Why do you need to rename the server/cluster?
Is the rename essential to business processes, or is it just a "tidying up" kind of thing?
Do you really need the geographical location in the name?
Will it really make sense to refer to it that way, since it seems logical
that part of the HA/DR topology might be to fail over to a different
geographical location?

In my experience most people have left that stuff alone, and documented very well that the original primary server was not renamed when they expanded geographically (or wished to rename for other reasons). You could also leave it the same and use DNS or the hosts file to let the applications and other servers/locations think the server was renamed, when really it wasn't.

The actual rename is the hard part IMHO, and I would be looking at any alternative I could to avoid doing that. DNS, hosts files on all relevant machines, aliases, and changing connection strings are all ways you could bypass the actual physical rename.


Performance for MySQL InnoDB B+Tree index with many duplicate values

90 seconds for a single UPDATE sounds too, too much. There is probably some blocking involved and should be investigated.

Apart from that, having a column that has 98% the same (NULL) value does not sound good either. You should consider putting that column in a separate table (which would have only 30K rows). It would complicate your INSERT/DELETE/UPDATE procedures a bit but you would probably gain from the smaller indexes. Suggested design:

CREATE TABLE Example (
    id INT NOT NULL AUTO_INCREMENT,
    some_data TEXT NOT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB ;

CREATE TABLE Example_secondary (
    id INT NOT NULL,
    secondary_id INT NOT NULL,
    PRIMARY KEY (id),
    INDEX (secondary_id),
    FOREIGN KEY (id)
      REFERENCES Example (id)
) ENGINE = InnoDB ;

Then your UPDATE:

UPDATE Example
SET secondary_id = NULL,
    some_data = '...'
WHERE id = 123 ;

would become:

BEGIN ;
    UPDATE Example
    SET some_data = '...'
    WHERE id = 123 ;

    DELETE FROM Example_secondary
    WHERE id = 123 ;
COMMIT ;


Can user variables be assigned of BIT datatype?

b'100' is just the number 4 represented in a different way.  Similarly for the column active and the variable @active.


Remove the last character from Variable String

SELECT
    SUBSTRING(col, 1, CHAR_LENGTH(col) - CHAR_LENGTH( SUBSTRING_INDEX(col, '.com', -1) ) ) ;

If you want update the strings from a table, chopping off what is on the right of .com:

UPDATE
    tableX
SET
    col = SUBSTRING(col, 1, CHAR_LENGTH(col) - CHAR_LENGTH( SUBSTRING_INDEX(col, '.com', -1) ) )
WHERE
    SUBSTRING_INDEX(col, '.com', -1) <> col ;


Slow mysql query full text search tag database

as I understand work of FT - it must be prepared in advance, so command

WHERE MATCH (t.tags) AGAINST ('+justin* +backgrounds*' IN BOOLEAN MODE)

against derived table:

(SELECT image_to_tag.image_id,
             GROUP_CONCAT(image_tag.`name`) AS tags
        FROM image_to_tag
        INNER JOIN image_tag
        ON (image_tag.id = image_to_tag.image_tag_id)
    GROUP BY image_to_tag.image_id) t

must not work - just because when index created no this table present

because query work (not return error from Your description) - MySQL ignore this, but need test with full table structure

also, JOIN with derived table - do not want to use index


Best way to store different types of data in a single column, which should be query friendly - MySql

This is basically a question about a multitenant application; and cal also be thought of as a particular application of an Entity Attribute Value scenario.

You can have different approaches, on top of your current one:

Have one ServiceDetails table per user/service (per tenant), with fields defined according to his/her/its needs. Your query will use ServiceDetails1 ... ServiceDetailsN tables, instead of just 1.  Instead of having your table DataTypeDetails, you can use the database metadata (information_schema) if needed. This is the most query friendly way of doing things.
Instead of having field1, field2, ..., field200, have a much larger collection of fields: field1_text, field1_integer, field1_float, field1_timestamp, field2_... and use the version of your column that has the proper type for your specific case. You will have an enormous amount of NULLs, and probably a lot of indexes. This uses only (a very large and sparse) table. It's not normally the best approach, but it is a possibility.
Use a database that allows for more flexible structures (such as JSON) for variable-schema. (PostgreSQL JSONB data-type, which can be indexed, would be a good fit for this scenario; even if you will still need to cast types).

References:

Introduction to Designing Multi-Tenant Web Applications
Three Approaches to Managing Multi-Tenant Data
Entity–attribute–value model


slaves wont understand new master. you should manually do that.
yes they are different and you should create new ones for old master.however old standby will continue to work as a master but you should set max_wal_senders on that node. you should also set pg_hba.conf of the new master after failover.
after failover (when nodes changes roles master->slave slave->master), you should transfer new wal files to new standby folders data directory which you set in recovery.conf file. or simply you can use rsync.
may be you can use pgbouncer. this way you will just change the pgbouncer server adres to new master.
EnterpriseDB has some commercial tools. may be you can check them.

and finally yes you are right. there is no single fully automatic solution to solve these questions.


Are there any inherently non-partitionable datasets?

Interesting question about the nature of data itself - we could almost stray into philosophy here!. As Phil Sumner points out, it would be a bad idea to try and do this using fields which always have a similar value - i.e. if one had a table ("Open_Source_Programmers", or even "Programmers" in general) that was partitioned on gender, the partitions would be very skewed.

See here for example - lots of articles about this (I Googled "how many open source programmers are female"), but as far as I have seen, it's certainly not above 5% and is probably closer to the 1.5% mentioned in the article I've quoted.

Similarly, I imagine that partitioning people who self-identified as a "programmer" would be very skewed on an age criterion. IMHO, few would fall outside 15-30.

From Codd's laws (The guaranteed access rule - sometimes called law 1 or 2 depending on whose definition you're looking at), every datum should be accessible "by specifying the name of the containing table, the name of the containing column and the primary key value of the containing row", every table should have a primary key.

By definition, if you, as a database practitioner are looking at a given dataset, it must be interesting to you (or, maybe more often, the business you're working for) for a reason. These business reasons will dictate your partitioning logic. Holding truly random data in a database makes no sense. If it were truly random, what criteria would you use to query it? How would you make sense of any results? A given datum (or data point) will (or should) have some sort of independent variable associated with it (time would probably be the main one).

But, to go back to my Open Source contributors example, any logical decision to partition the data would not include gender - maybe "Continent"? As far as I can see, a good first pass would be to go with "North America", "Europe" and "ROW" (rest of world).

Your question begs another, perhaps more fundamental, question. If data is not partitionable, is it data at all? Furthermore, when you query data, you are, ipso facto, partitioning it in some shape or form (SELECT * FROM.. with no WHERE clause excluded).

Information theory, philosophy are all going to come into play here (I've never formally studied either), but I'm very interested in this on an intellectual level and would be delighted for anybody to critique what I have written and point out any (hopefully not obvious) flaws.


How to view the query of another session in pg_stat_activity without being super user?

At this point, there's no right to grant, it's hardcoded to superuser. That's been discussed on the mailing list lately, and may change in 9.5 if someone finds the time to work on it.

As a workaround, you can create a SECURITY DEFINER function that is owned by the superuser, and runs the query you want. This will allow non-superusers to see the contents of pg_stat_activity by calling the function.

E.g., run as a superuser:

CREATE FUNCTION get_sa() RETURNS SETOF pg_stat_activity AS
$$ SELECT * FROM pg_catalog.pg_stat_activity; $$
LANGUAGE sql
VOLATILE
SECURITY DEFINER;

CREATE VIEW pg_stat_activity_allusers AS SELECT * FROM get_sa();

GRANT SELECT ON pg_stat_activity_allusers TO public;

Note that free access to pg_stat_activity is restricted for a reason. It's possible to snoop sensitive information from other people's queries - imagine for example if another user was using pgcrypto. Rather than granting rights to public you should grant them only to a specific user or role that is to act as a surrogate user for monitoring.


Bulk edit tables and columns descriptions

You can't add them in mass but you can generate the commands to do so pretty easily.  I've added two extra columns to the query that have the sys.sp_addextendedproperty commands added if there isn't already a description.  You would still have to manually modify the description value for each but this should save you some time.

SELECT      u.name + '.' + t.name AS [table],
            td.value AS [table_desc],
            CASE WHEN td.value IS NULL THEN
                    'EXEC sys.sp_addextendedproperty @name=N''TableDescription'', ' +
                        '@value=N''This is the table description'' , ' +
                        '@level0type=N''SCHEMA'',@level0name=N'''+QUOTENAME(u.name)+''', ' +
                        '@level1type=N''TABLE'',@level1name=N'''+QUOTENAME(t.name)+'''' END AS CreateTableNotes,
            c.name AS [column],
            cd.value AS [column_desc],
            CASE WHEN cd.value IS NULL THEN
                    'EXEC sys.sp_addextendedproperty @name=N''ColumnDescription'', ' +
                        '@value=N''This is the column description'' , ' +
                        '@level0type=N''SCHEMA'',@level0name=N'''+QUOTENAME(u.name)+''', ' +
                        '@level1type=N''TABLE'',@level1name=N'''+QUOTENAME(t.name)+''', ' +
                        '@level2type=N''COLUMN'',@level2name=N'''+QUOTENAME(c.name)+'''' END AS CreateColumnNotes
FROM        sysobjects t
INNER JOIN  sysusers u
    ON      u.uid = t.uid
LEFT OUTER JOIN sys.extended_properties td
    ON      td.major_id = t.id
    AND     td.minor_id = 0
    AND     td.name = 'MS_Description'
INNER JOIN  syscolumns c
    ON      c.id = t.id
LEFT OUTER JOIN sys.extended_properties cd
    ON      cd.major_id = c.id
    AND     cd.minor_id = c.colid
    AND     cd.name = 'MS_Description'
WHERE t.type = 'u'
ORDER BY    t.name, c.colorder


ms-access: issues with form and subform interaction

Since I am a VBA and Access newbie I didn't know you have to single quote 'text' values, not quote numeric values and quote with hashes date values when doing an insert into in vba.


Better performance with (Recompile) option, How?

What's probably going on is that you're running the query with different input values in your parameter. Let's look at this simplified example:

SELECT columns FROM table WHERE something=@var;

This query might return zero rows, a single row or millions of rows, depending on the value of @var. The first time you run this query (without RECOMPILE), SQL Server generates a query plan based on the value of the variable for that execution.

Suppose that value of @var returns a single row, the query can afford an execution plan that takes 0.1 seconds for each row it returns, completing your query in 0.1 seconds total. Now, if you apply that same plan to another value of @val where the query returns 1000 rows, that query is going to run for minutes and you would probably be better off with another plan, perhaps with different joins, aggregates, etc.

When you use WITH RECOMPILE, SQL Server will evaluate the query (and the variable values) every time it runs and recreate the execution plan every time. This will add a few milliseconds to each execution (to generate the plan) but the resulting plan will probably be better suited for your particular value of @val.

If you're fine with an execution time of two seconds, this won't be a problem. However, if your query needs to complete in milliseconds, I would start tuning indexes, updating statistics, etc.

In summary, yes, this sounds a lot like a parameter sniffing problem, and WITH RECOMPILE appears to be a good solution in your scenario. I can't make any more qualified guesses without looking at query plans, tables and indexes, statistics and the different parameter values. Hope that helps.


How do I generate a date series in PostgreSQL without hard coding the dates?

I want to generate a series of months. The start should be the first month of the current year, the stop is the current date with an interval of 1 month.

I would just keep it like d::date but you can format it however you want,

SELECT d::date, to_char(d, 'YYYYmm')
FROM generate_series(
  date_trunc('year', now())::timestamp without time zone,
  now()::timestamp without time zone,
  '1 month'
) AS gs(d);

But you can reformat it with to_char() if you want.

For more information see How do I generate a date series in PostgreSQL?


Please try below code for your issue:

SELECT
CaseNumber,
EstimateAmt,
BilledAmt,
(CASE WHEN BilledAmt IS BETWEEN ((50 * EstimateAmt)/100) and ((70 * EstimateAmt)/100) THEN '1' ELSE '0' end) as 'IsBetween50And70',
(CASE WHEN BilledAmt IS BETWEEN ((70 * EstimateAmt)/100) and ((90 * EstimateAmt)/100) THEN '1' ELSE '0' end) as 'IsBetween70And90',
(CASE WHEN BilledAmt IS BETWEEN ((90 * EstimateAmt)/100) and ((100 * EstimateAmt)/100) THEN '1' ELSE '0' end) as 'IsBetween90And100',
(CASE WHEN BilledAmt IS > (110* EstimateAmt)/100 THEN '1' ELSE '0' end) as 'IsGreaterThan110'
from CaseList


You are confused because you're mixing up a database base backup and a SQL dump (with pg_dump or pg_dumpall) which are actually two completely different things. Take a good look at the PostgreSQL documentation about Backup and Restore. These are the links to the 9.1 docs, but the basic principles of this stuff haven't changed since at least version 8.3 (except for streaming replication which was introduced with 9.0)

A database base backup is basically a file system level copy of the data directory (and any additional tablespace directories) at a certain point in time. This file system level backup can be created with any tool you like (e.g. tar or cpio). But in order to be able to recover from this backup additional information is needed: the WAL segments (binary transaction log) that have been created during the backup plus the exact start and end position of the backup creation process in the WAL segments. In recovery mode PostgreSQL is started with the backup data directory and it will replay all transactions from the available WAL archives (unless a certain end time or transaction id is configured for point-in-time recovery). The scope of a base backup is a database cluster.
A SQL dump is the plain text or binary format representation of the schema and/or data information of one (pg_dump) or all (pg_dumpall) of the databases in a cluster. It is also possible to dump just certain schemas or tables. In contrast to a base backup a SQL dump covers only an exact snapshot of the database at a single point in time. The essential difference to a base backup is that the SQL dump is independent of the physical database layout. System architecture, OS, file system and for most parts database version do not matter, the SQL dump is only the information that makes up a database and can therefore be easily restored on another system.

So in your case using pg_dump or pg_dumpall for backup and pg_restore or psql to restore the dump is the way to go.

Hope that helps.


Can I change tempdb size to a specific value and can I shrink it during our daily working hours on our production database

Multiple things to note here. tempdb is supposed to have same number of data file as many cores are available on the server(up to 8). So, in your case it is supposed to be 4 data files and they are should be of same size so that you won't have allocation contention. This is addressed by KB 2154845.
Below is explained by Mr. Brent Ozar for configuring tempdb:

configure one volume/drive for TempDB. Divide the total space by 9,
and that’s your size number. Create 8 equally sized data files and one
log file, each that size. Presto, the drive is full and your TempDB is
configured for easy performance.

I would stick to below script to find my bottlenecks for wait statistics:
WITH [Waits] AS
(SELECT
[wait_type],
[wait_time_ms] / 1000.0 AS [WaitS],
([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
[signal_wait_time_ms] / 1000.0 AS [SignalS],
[waiting_tasks_count] AS [WaitCount],
100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
FROM sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
N'CHKPT', N'CLR_AUTO_EVENT',
N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',
N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
N'EXECSYNC', N'FSAGENT',
N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',
N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE',
N'PWAIT_ALL_COMPONENTS_INITIALIZED',
N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',
N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',
N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')
AND [waiting_tasks_count] > 0
)
SELECT
MAX ([W1].[wait_type]) AS [WaitType],
CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S],
CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S],
CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S],
MAX ([W1].[WaitCount]) AS [WaitCount],
CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],
CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],
CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],
CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95; -- percentage threshold
GO

I am not very sure if this will work on SQL Server 2008R2 as I don't have any environment with this version.
Regarding your main issue i.e. filling up of space at C drive and your only device of tempdb which is stored at the same drive can make your database corrupt as well as your operating system will stop working if all the space is used. So, I would suggest you to add three more data file of equal size and keep them at different drive and at the same time disable autogrowth option from the existing device as shown below:

Check how much of data is really used in tempdb and try to shrink that to an extent(its not a good option in normal scenario however you don't have many option since we don't want to reboot service now).
After above steps are done and you manage to survive during peak hour, you may plan to get downtime of your system for few minutes and would need to reboot your SQL service in order to move 1st data file from C drive to other drive.
You may use commands given at below links and follow steps to move data/log file of tempdb:
https://blog.sqlauthority.com/2016/06/26/moving-tempdb-new-drive-interview-question-week-077/
https://www.brentozar.com/archive/2017/11/move-tempdb-another-drive-folder/
How to Move TempDB Files to a Different Drive or Folder?
Hope above helps.


Have you considered using a trace for that?
https://technet.microsoft.com/en-us/library/ms191006%28v=sql.105%29.aspx
You would be able to configure it for specific tables and store output in a table.

I also have to point out that, with the method you are trying to use, you will only be able to capture the query used to change the table.
In some situations you will also get the constant values e.g.

DECLARE @ID_to_delete INT
SET @ID_to_delete = 123
DELETE FROM SampleTable WHERE ID = @ID_to_delete

While in most other cases you will get just the query used to change data e.g.

DELETE FROM SampleTable WHERE ID IN( SELECT ID_to_delete FROM ToDeleteTable )


SSRS report slow on initial query of the day

Per the article you provided, give this a shot for another potential workaround:

On the Reporting Services server, modify the RecycleTime value of the rsreportserver.config file, and time this out so a 'recycle' at the app server level of RS occurs just before the start of the regular business day every day - like an hour or so before any production RS reports would typically be run.
Then setup a small dummy Subscription report to run every day at the same time—ensure it has enough time beforehand though for the RS app 'recycle' to complete. Running this non-production small dummy report as the first and initial report each day after the RS app server 'recycle' completes should ensure RS app server stuff loads up before any production reports are run, and production reports shouldn’t experience the problem.

This way your Reporting Services app server recycle operation happens every day at the usual and designated time per the value you set in the config file, then right after that happens (giving ample time for full completion) the dummy report runs before any production reports run for the day, and then every subsequent report for the next 24 hours runs as expected without needing to wait for the RS app server post 'recycle' load operations to complete. You also could leave out the PowerShell scripted solution altogether as well if this works.


PERSPECTIVE #1
When you update by Primary Key only in InnoDB, there is a rare but possible occasion when the clustered index (aka gen_clust_index) can get locked.
I once answered three posts from one individual on this subject

Trouble deciphering a deadlock in an innodb status log
Reasons for occasionally slow queries?
Will these two queries result in a deadlock if executed in sequence?

Please read through these carefully. The poster of these question found his own workaround based on the seeing InnoDB Clustered Index Locking behavior. Sadly, he did not post what the workaround was.
In addition, when you see the queries running slow, log into mysql and run SHOW ENGINE INNODB STATUS\G and starting looking for locks in the Clustered Index.
PERSPECTIVE #2
I see you commented out innodb_log_file_size. You have it at 5MB, the default. Since innodb_buffer_pool_size is set to 1G, innodb_log_file_size needs to be at 256M. Click here to go about setting innodb_log_file_size to 256M.
PERSPECTIVE #3
I see you are not using innodb_file_per_table. You may want to use it in order to have table updates done specifically for the one table with a million rows. Click here to see how to Clean Up InnoDB infrastructure to use innodb_file_per_table.


Try something like:

select AccountNumber, SnapshotTime, Ticket, ...
from (
    select AccountNumber, SnapshotTime, Ticket, ...
         , row_number() over (partition by AccountNumber, Ticket
                              order by SnapshotTime desc) as rn
    from OpenTrades
) T
where rn = 1;

I just reread the post and realized that I gave a solution for something different than the OP asked for, I guess something like:

select AccountNumber, SnapshotTime, Ticket, ...
from (
    select AccountNumber, SnapshotTime, Ticket, ...
         , row_number() over (to_date(SnapshotTime,'IW')
                              order by SnapshotTime desc) as rn
    from OpenTrades
) T
where rn = 1;

is closer to what the OP wants.


There are plenty of web based admin tools - PHPMyAdmin for MySQL (installed it once - seems OK and is widely used).

The MySQL Workbench tool is top-notch for administering MySQL - if you can telnet/ssh into your box, you can use it - I would recommend this approach as your starting point.

As a general tool, I also recommend SQuirreL SQL - a JDBC based tool - some shops can have strange requirements re. 3rd party tools and the like - worth looking at also!


How to recover deleted/updated record in sql server or mysql?

You always have a choice to use a recovery tools, such as ApexSQL Log for example. Not only that you can recover deleted rows, but you can also choose which rows you are going to recover.

It can recover your data from on-line, backup or detached transaction logs.

I have tried that tool and it could be the life saver.

See: How to recover SQL Server data from accidental UPDATE and DELETE operations


This works OK (tested on Oracle 11.2):

create or replace type name_student as object
(
  fname varchar2(10),
  lname varchar2(10)
);
/

create or replace type add_student as object
(
  street varchar2(10),
  city varchar2(10)
);
/

create or replace type student_detail as object
(
  name name_student,
  address add_student,
  salary number(10),
  member function getsalary return number
);
/

create or replace type body student_detail as
  member function getsalary return number as
  begin
    return (salary*10);
  end getsalary;
end;
/

show errors;

create table student_table(S student_detail);

Test:

SQL> insert into student_table values
(
  student_detail
  (
    name_student('abc','pqr'),add_student('ABCD','pqrs'),2000
  )
);  2    3    4    5    6    7

1 row created.

SQL> select * from student_table;

S(NAME(FNAME, LNAME), ADDRESS(STREET, CITY), SALARY)
--------------------------------------------------------------------------------
STUDENT_DETAIL(NAME_STUDENT('abc', 'pqr'), ADD_STUDENT('ABCD', 'pqrs'), 2000)

SQL Fiddle doesn't seem to like it too much!


It is actual quite easy if you are obvious of some caveats:

configure your services to run on one node only: srvctl modify service -db $DATABASE -service $SERVICENAME -preferred $INSTANCE1 -available $INSTANCE2 -modifyconfig
wait for the sessions to wander to the node running $INSTANCE1. This may take some time, it's heavily dependent on the type of application you are running. JAVA Applications tend to reconnect quite frequently. So check with your database, e.g. query gv$session.
Take down your node and do whatever maintenance there is to do
Reconfigure your services to run on the second node:

srvctl modify service -db $DATABASE -service $SERVICENAME -preferred "$INSTANCE1,$INSTANCE2" -modifyconfig
srvctl start service -db $DATABASE -service $SERVICENAME -node $NODE This step is crucial, if you omit it, the whole procedure won't work!
srvctl modify service -db $DATABASE -service $SERVICENAME -preferred $INSTANCE2 -available $INSTANCE1 -modifyconfig

wait again for the sessions to wander to your patched node
Take down your second node and do whatever maintenance there is to do
reconfigure your services to run on both nodes and start the services

srvctl modify service -db $DATABASE -service $SERVICENAME -preferred "$INSTANCE1,$INSTANCE2" -modifyconfig
srvctl start service -db $DATABASE -service $SERVICENAME

The procedure must be tested in your environment as there may be dependencies e.g. if you use a shared ORACLE_HOME on ACFS. The success of reconfiguration of services must be tested, especially if you have client-server applications.


Migrating/Upgrading a MySQL 5.1 schema to MySQL 5.7 schema

Some things, such as microsecond in TIMESTAMP, will automatically be available because the CREATE TABLE on 5.7 will use the new format.  innodb_file_per_table has a new default, so the tables will be laid out differently.  That should not be a problem.

mysql_upgrade is for converting the mysql tables and for ALTERing to accommodate microseconds, etc, in an existing mysql instance where you are updating the software, not where you are loading the data.

Coming forward from 4.0 can cause troubles with changed syntax, the addition of CHARACTER SET, etc.  But 5.1 is not that much different for what you are doing than 5.7.

There have been lots of changes/additions to mysql, information_schema, and performance_schema.  So, be sure not to dump and reload them.

Use pt-show-grants if you need to carry GRANTs forward.  Then check the results.

Do you have specific examples of things that concern you?


Change the status table to match

  CREATE TABLE `status` (
  `status` enum('new','approved') DEFAULT NULL,
  `status_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`status`),
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1

There is no need for AUTO_INCREMENT.

Then do this for each table:

ALTER TABLE ...
  MODIFY COLUMN `status` enum('new','approved','Pending') DEFAULT NULL;

Be sure to change all references to status_id.


Replication, and daily / hourly backups (depending on your need), probably with something like xtrabackup, and storing the binlogs in between, give your pretty good point-in-time recovery. You might even configure pt-slave-delay to let your actual backup lag somewhat, useful for those moments where you are aware of an accidental deletion within the time-frame of your delay, making the restoration a bit less cumbersome.

I do not understand the requirement 'all features should be done by using billing software'. Billing software is for billing, not for database maintenance.


What should be done before upgrading MySQL?

If you stop the MySQL Server process gracefully, the filesystem and databases won't be corrupted.

Here are a couple of things I typically do:

Verify that innodb_fast_shutdown is disabled.  If it isn't, disable it.

mysql> show variables like 'innodb_fast_shutdown';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| innodb_fast_shutdown | 0     |
+----------------------+-------+
1 row in set (0.00 sec)

According to the documentation, this is only needed when doing a major version upgrade, but I maintain that it's still a good idea -- this step increases your shutdown time but leaves your instance in a more stable state for the restart.

Next, you can lock all of the tables to prevent clients from doing anything.  This step, also, isn't technically necessary, but I like it because it gives me a sense of knowing what's going on -- and what isn't -- and when -- and it shortens the next part of the shutdown, since there won't be any client activity to wait for.

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

Note that on a busy server, that statement won't return so quickly.  It won't return until the server has successfully closed all open tables and acquired a lock on every table in the entire database -- so, if you have queries executing when you issue this statement, they will be allowed to finish, then the locks acquired, and then is when you'll get your prompt back.

mysql>

Important: leave this connection open.  Just let it sit there and move to a new window, because the connection being open is what holds the global read lock.  If you close or otherwise lose this connection, the read lock is released.

If you want to look at any variables, the processlist, etc., you can still use this connection if you want to.  Just don't disconnect from the server.

At this point (since you got your prompt back), your tables are locked from further changes and your application tier may start to behave badly because a backlog might be growing.  That's to be expected, since any queries against the server will just wait indefinitely for that read lock.  The number of client connections may be increasing, but they won't be able to do anything.

In another window, tail your MySQL error log.

shell> tail -f /usr/local/mysql/data/hostname.err

In yet another window, shutdown MySQL Server:

shell> mysqladmin shutdown

You will most likely need to add --username --password and possibly other options to this command.  You won't see much from this window, so go back and watch your error log:

The server says, "we're going to do a graceful shutdown." (Not in so many words, but that's what this next log entry means...)

130119 13:29:17 [Note] /usr/local/mysql-5.5.28-solaris10-x86_64/bin/mysqld: Normal shutdown

The server shuts down any system threads.  In this case, I left the event scheduler and even replication running to illustrate that a graceful shutdown is indeed graceful.  (Yes, my server started right back up, including replication, right after I captured this output.)  In practice, it might be better to stop replication manually with STOP SLAVE; before you flush the tables and acquire the read lock.  The Event Scheduler is an optional feature, which you may or may not be using.  Here is example output:

130119 13:29:17 [Note] Event Scheduler: Killing the scheduler thread, thread id 1
130119 13:29:17 [Note] Event Scheduler: Waiting for the scheduler thread to reply
130119 13:29:17 [Note] Slave I/O thread killed while reading event
130119 13:29:17 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.001539', position 849647640
130119 13:29:17 [Note] Event Scheduler: Stopped
130119 13:29:17 [Note] Event Scheduler: Purging the queue. 3 events
130119 13:29:17 [Note] Slave SQL thread exiting, replication stopped in log 'mysql-bin.001539' at position 849626716

InnoDB does its cleanup (including the "slow shutdown")...

130119 13:29:19  InnoDB: Starting shutdown...
130119 13:29:20  InnoDB: Waiting for 6 pages to be flushed
130119 13:29:26  InnoDB: Shutdown completed; log sequence number 2718873505306

The server shuts down...

130119 13:29:26 [Note] /usr/local/mysql-5.5.28-solaris10-x86_64/bin/mysqld: Shutdown complete

The mysqld_safe wrapper notices that the server has stopped running.  This process is charge of trying to restart the server if it crashes, but if the server has removed its own pid file, no restart is attempted, because the shutdown is assumed to be graceful.

130119 13:29:27 mysqld_safe mysqld from pid file /usr/local/mysql-5.5.28-solaris10-x86_64/data/psi2.pid ended

You're now gracefully shut down.


How can I find out that a publication has "replicated schema changes" enabled?

Run this stored procedure at the Publisher:

sp_helpmergepublication

And check this column:

replicate_ddl

A value of 1 means that schema changes are replicated


Ray Barley created a post on MS SQL Tips regarding the physical limitations of SQL 2008 R2.

Looking at the specs, it looks like Memory or CPU will be the indicators you need to use.

So what you need to do from this point is measure the CPU and Memory to see if you have issues with either or neither.

Pinal Dave gives great tips on his blog SQL Authority on figuring out if you have CPU pressure.

Tibor Nagy made a good post on finding memory bottlenecks too on MS SQL Tips.

Paul Randal on SQL Skills also talks about how to find the pain points and measure who your trouble children are.

Things to keep in mind is that it may not be the specs of SQL Express causing you issues exactly. It could still be indexes, statistics, disk speed, network, etc. That's why the DMV's are important, you can measure and find the pain points that you measure. One key note is that historical information is important in this regard and you are running SQL Express. You may need another instance of SQL Express to monitor your current one so you can store enough data to create a historical view to analyze. You can potentially export the data daily and then import to another environment to analyze so you keep your database small.

Another side option would be to install a free trial of Database Performance Analyzer / SQL Sentry / Redgate to monitor your instance for a short period of time as those typically watch the DMV's and collect similar data. I would expect within about a week or less of collecting those metrics you could identify where your pressure points are in that environment.


Do SELECT (without ORDER BY) return the same message as RECEIVE?

No. RECEIVE constructs a query AST that cannot be constructed with SELECT. RECEIVE has to guarantee EOIO and conversation group locking, SELECT does not.

Service Broker has a built-in 'peek': GET CONVERSATION GROUP. This will lock the next available conversation group, on which you are guaranteed to get a message if you issue a RECEIVE within the same transaction. I strongly discourage the use of it.

Anything else you're trying to do is broken and you need to change the application to stop doing it. Always issue a RECEIVE and react to the returned messages result set.


Possible with a CTE, but PIVOT is the easier implementation.

If for total_fio you still wanted distinct, this would work:

SELECT * FROM
 (SELECT pc_id,
         user_id,
         to_char(payment_datetime,'DD.MM.YYYY') AS "date",
         To_char(payment_datetime, 'HH24') AS "hour",
         fio,
         count(DISTINCT fio) OVER ( PARTITION BY  to_char(payment_datetime,'DD.MM.YYYY') ) AS total_fio
  FROM   foobar
  WHERE  To_char(payment_datetime, 'HH24') BETWEEN '07' AND '22' )
PIVOT
 (Count(DISTINCT fio)
   FOR "hour" IN ('07', '08', '09', '10', '11', '12',
                  '13', '14', '15', '16', '17', '18',
                  '19', '20', '21', '22'));

Or if you wanted a sum of all hour columns, I'm not sure of an elegant way to do it without getting into PL/SQL:

SELECT x.*, "07" + "08" + "09" + "10" + "11" + "12"
             + "13" + "14" + "15" + "16" + "17" + "18"
             + "19" + "20" + "21" + "22" AS total_fio
FROM
(SELECT pc_id,
        user_id,
        to_char(payment_datetime,'DD.MM.YYYY') AS date_day,
        To_char(payment_datetime, 'HH24') AS date_hour,
        fio
 FROM   foobar
 WHERE  To_char(payment_datetime, 'HH24') BETWEEN '07' AND '22')
PIVOT
(Count(DISTINCT fio)
  FOR date_hour IN ('07' AS "07", '08' AS "08", '09' AS "09", '10' AS "10", '11' AS "11",
                    '12' AS "12", '13' AS "13", '14' AS "14", '15' AS "15", '16' AS "16",
                    '17' AS "17", '18' AS "18", '19' AS "19", '20' AS "20", '21' AS "21",
                    '22' AS "22")) x;

I'm new on here so don't have enough rep to comment on your implementation, but it's a solid go at it for sure. I don't see anything wrong where you'd get inconsistent results. As for optimization, `PIVOT' does the grouping for you. So by using the CTE with the additional group by to start out, it is almost doubling the work the query has to do. My second query above is essentially an optimized version of yours (they are very similar).


How do I hide sensitive information like plaintext passwords from the logs?

It sounds like log_statement is set to all.

If you wish to prevent passwords from appearing in the logs while log_statement is set to a value that captures ALTER USER / ALTER ROLE then you'll want to override that when changing passwords. e.g.

BEGIN;
SET LOCAL log_statement = 'none';
ALTER USER ... SET PASSWORD ...;
COMMIT;

You must be a superuser to do this. Normal users cannot override logging rules.

It would be nice if PostgreSQL supported flagging some parameters to statements (or even functions) as security-sensitive and allowed users to request that they be masked in logs, pg_stat_statements, pg_stat_activity, etc. There is not currently any such feature - but hey, patches are welcome. If you're genuinely interested, post on pgsql-hackers before writing any actual code so you can get advice and comments, though. Alternately, speak to someone who does contract development.

In general PostgreSQL expects you to treat the logs as sensitive.

There are other areas where logging is a serious security concern. For example some of the pgcrypto functions take crypto keys as parameters.


Another option would be to put the data into an OLAP cube.  OLAP cubes are designed for doing aggregates.


Why does DBMS_JOB.SUBMIT execute immediately when NEXT_DATE is in the future?

Setting the job to "unbroken" reschedules it:

    SQL> DECLARE
  2    X NUMBER;
  3  BEGIN
  4    SYS.DBMS_JOB.SUBMIT
  5    ( job       => X
  6     ,what      => 'BEGIN  NULL;  END;'
  7     ,next_date => to_date('24/04/2012 12:00:00','dd/mm/yyyy hh24:mi:ss')
  8     ,interval  => 'TRUNC(SYSDATE+1)+12/24'
  9     ,no_parse  => FALSE
 10    );
 11    SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
 12
 13  COMMIT;
END;
 14   15  /
Job Number is: 5

PL/SQL procedure successfully completed.

SQL> alter session set nls_date_format='DDMMYYYY HH24:MI:SS';

Session altered.

SQL> select job,last_date,next_date,broken from dba_jobs where job=5;

       JOB LAST_DATE         NEXT_DATE         B
---------- ----------------- ----------------- -
         5                   24042012 12:00:00 N

SQL> BEGIN
  2  SYS.DBMS_JOB.BROKEN(job=>5,broken=>FALSE);
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> select job,last_date,next_date,broken from dba_jobs where job=5;

       JOB LAST_DATE         NEXT_DATE         B
---------- ----------------- ----------------- -
         5                   23042012 21:55:49 N

SQL>

Once you hit commit, the job scheduler sees the job and executes it:

SQL> select job,last_date,next_date,broken from dba_jobs where job=5;

       JOB LAST_DATE         NEXT_DATE         B
---------- ----------------- ----------------- -
         5                   23042012 21:55:49 N

SQL> commit;

Commit complete.

SQL>  select job,last_date,next_date,broken from dba_jobs where job=5;

       JOB LAST_DATE         NEXT_DATE         B
---------- ----------------- ----------------- -
         5 23042012 21:59:06 24042012 12:00:00 N

SQL>

Moral of the story: If creating a new job, don't include a call to DBMS_JOB.BROKEN().


How would I exclude a group of tables based on a name prefix foo_* from a mysqldump?

This is based on answers from How do you mysqldump specific table(s)?

To exclude all tables from a mysqldump that starts with foo_, here is the shell script to do it

MYSQL_DATA=mydb
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SET group_concat_max_len = 102400;"
SQL="${SQL} SELECT GROUP_CONCAT(CONCAT('--ignore-table=',table_name) SEPARATOR ' ')"
SQL="${SQL} FROM information_schema.tables WHERE table_schema='${MYSQL_DATA}'"
SQL="${SQL} AND table_name LIKE 'foo_%'"
EXCLUSION_LIST=`mysql ${MYSQL_CONN} -AN -e"${SQL}"`
mysqldump ${MYSQL_CONN} ${MYSQL_DATA} ${EXCLUSION_LIST} > ${MYSQL_DATA}_tables.sql

To exclude all tables from a mysqldump that starts with foo_, adjust this line

SQL="${SQL} AND table_name LIKE 'foo_%'"

to whatever pattern you need. Maybe you can use the REGEXP operator

SQL="${SQL} AND table_name REGEXP '\_BAK\_2[0-9][0-9][0-9][0-9][0-9][0-9][0-9]"

Give it a Try !!!


Importing a mysqldump from MySQL 5.0.95 to MariaDB 10.0.36: Some tables incomplete

5.0 -> 5.1 -> 5.5 -> 5.6/10.0 -- That's 3 major upgrades.  Various tables in the mysql database have been added/changed.  Lots of changes to information_schema / performance_schema.

You would be quite lucky to leap from 5.0 to 10.0 without some major hiccups.  You did run mysql_upgarde?  MariaDB tries to make that handle multiple hops, but this may be more than it can handle.

if enforce_storage_engine is turned on, this could explain the sudden appearance of unexpected .ibd files.  There can be hiccups in blindly changing the Engine.

How are you measuring number of rows?  When using SHOW TABLE STATUS, MyISAM has exact numbers; InnoDB has approximate numbers.  Even 307 versus 4000 is not unheard of.  Use SELECT COUNT(*) .. to be sure.

As for the empty table -- manually run the load for it; see if you get any extra clues.


Running a complex query for every date in a range

I would use select distinct on instead of window function, then just join the days.

select
    distinct on (date, client_id) date,
    id
from orders
inner join generate_series('2015-07-18'::date, '2015-07-19'::date, '1 day') date
  on start_date <= date and (end_date is null or date <= end_date)
order by date, client_id, order_type desc

http://sqlfiddle.com/#!15/5a420/16/0

I can elaborate more if something is not clear.


You might be able to do something along the lines of what was done in check_updates-1.0.1, if you can't make guarantees about being able to install in the system's PYTHONPATH.

In the Makefile, there is a substitution of  based off the output of pg_config for the use lib directive of Perl. You could do something similar like this:

import sys
sys.path.append("<LIBDIR>/myextension")

in the Makefile of your extension to ensure that the install directory for modules is in the PYTHONPATH in your functions, no matter where someone has things installed.

You can cache your imports too, as referenced in this StackOverflow question: How are import statements in plpython handled, which should help with performance too.

It's not "easy", though, sadly. We have to wait for future improvements to plpythonu to make such things easier.

Hope that helps answer your question. =)


When you create a clustered index this "replaces" the heap so there are no extra index pages as such (aside from the upper levels of the b-tree), and as you have absolutely no other indexes there is nothing else to affect the size of.

Therefore if your target column already exists I wouldn't expect very much extra space to be permanently taken unless you have fill factor set to something other than full (for example with a fill factor of 80% the new index would leave approximately 20% of each leaf page spare for later additions).

Some extra space may be used as the heap is rebuilt as a clustered index so your database may need to grow to accommodate this, but any such extra space will be free for future allocation once the process is complete.

For an example, see Index Disk Space Example in the product documentation.

There is even more detailed information in the Online Indexing Operations in SQL Server 2005 technical article (despite the name, it covers offline operations as well).

Extra

It could take less space than before as you have noticed. You may have had a lot of part-used or empty pages due to deletes and updates over time. Heaps do not release the space used by empty pages created by deletes unless TABLOCK was specified at the time of the deletion, and even then not always. From DELETE (Transact-SQL):

  When rows are deleted from a heap the Database Engine may use row or page locking for the operation. As a result, the pages made empty by the delete operation remain allocated to the heap. When empty pages are not deallocated, the associated space cannot be reused by other objects in the database.

There may also have been a small amount of space used by forward record pointers (which don't exist in clustered indexes), and if your rows are of variable length blocks of space in pages that are too small to use for new rows. Your newly reorganised rows will be more tightly packed (fill factor settings permitting) so may well use fewer pages.


Using a While function but running out of days

As a general rule, in SQL, you want to accomplish things with set-based operations rather than iterative processes.

So, you want the 30 most recent days where your search criteria were true for at least one entry.

I'd try this:

SELECT TOP (30) DISTINCT CAST(timestamp as DATE) targetDate
  FROM Auto_SixtyMinuteEmissionRecords
 WHERE SO2_7PCT > '44'
   AND NOX_LB_Validity = 'Valid'
   AND type = @Type
 ORDER BY targetDate DESC
;

This will locate all the records that have columnA = 1, take the unique dates from those rows' timestamps, and give you the 30 most recent.

If there aren't 30 days where this is true, you just won't get back thirty rows in the result set.

Of course, this assumes that using WHILE is not a requirement.

You could then use the above query as a derived table, and join it to your INSERT statement, as follows:

INSERT INTO #temptable
SELECT auto.*
  FROM Auto_SixtyMinuteEmissionRecords auto
         INNER JOIN (SELECT TOP (30) DISTINCT CAST(timestamp as DATE) startDay, DATEADD(day, 1, CAST(timestamp as DATE)) as endDay
                       FROM Auto_SixtyMinuteEmissionRecords
                      WHERE SO2_7PCT > '44'
                        AND NOX_LB_Validity = 'Valid'
                        AND type = @Type
                      ORDER BY targetDate DESC
                    ) dates ON (    auto.timestamp >= dates.startDay
                                AND auto.timestamp < dates.endDay)
;

This should put (up to) 30 days' worth of hourly rows into your temp table, in one statement.

NOTE: Code above is untested.


MDW Data Collector Server Activity collection step 3 suddenly stopped working

This error:

  0x80040E14 Description: "The handle that was passed to dm_exec_sql_text was invalid."

indicates a "handle" was passed into the sys.dm_exec_sql_text function that was not an actual, valid, SQL handle.

You'll see this error if you run this code:

SELECT *
FROM sys.dm_exec_sql_text(0)

sys.dm_exec_sql_text is used to obtain the query text for queries that reside in the plan cache, and can be seen in action via this sample code:

SELECT TOP(10)
    decp.plan_handle
    , dest.text
FROM sys.dm_exec_cached_plans decp
CROSS APPLY sys.dm_exec_sql_text(decp.plan_handle) dest;

It looks like the data collector has a bug; what version of SQL Server do you have installed?


Create a new database in SQL Server 2008 R2, but it is not Blank

New databases are cloned from database model. Probably some accident polluted your model database (maybe a DDL script run in the wrong database). You should probably clean it up.


Try moving some objects into another tablespace.
Documentation on this is quite confusing, referring to an uncertain pl/sql procedure, that should be used. Though, there is no mention what procedure it is. I would use alter table ... move ...

Edit: (thanks to @Jack Douglas):
Instead of alter, there is a move_procedure defined in V$SYSAUX_OCCUPANTS that should be used to move those occupants.


Active Directory domain being shutdown-- Do I have to update my DBO accounts?

Yes, you need to change the databse owners to valid principals. There are several features that require the database owner principal to be valid, most notably the EXECUTE AS infrasturcture. You may be using these feature whithouth your knowledge, for example an application may be using SqlDependency which requires Service Broker which uses EXECUTE AS. Rather than face long nights of troubleshooting cryptic error messages, do yourself a favour and change all owners on all databases to sa:

alter authorization on database::<dbname> to [sa];

And no, I did not specify sa by mistake.


Well, if you want to update ALL entries, why don't you just get rid of the condition, and execute:

UPDATE blog_entry SET allow_view_entry = 'everyone'

Otherwise, you can use a wildcard indeed, and build your statement like this:

UPDATE blog_entry SET allow_view_entry = 'everyone'
WHERE entry_id LIKE '%245%'

using the common wildcards that go with the LIKE operator.


Oracle DATE column - How to convert to UTC


Combine 4 rows based on common ID and Date

try this :

select t.user_id,t.time_log,substring_index(t.tstring,'#',1) am_in,substring_index(substring_index(t.tstring,'#',2),'#',-1) am_out,
substring_index(substring_index(t.tstring,'#',-2),'#',1) pm_in,substring_index(t.tstring,'#',-1) pm_out
from
  (select h.user_id,h.time_log,group_concat(h.stime order by h.stime separator '#') tstring
  from user_hardware h
  group by h.user_id,h.time_log
  ) t;

But ensure that same stime will not occur twice for same user_id and time_log i.e. user_id,time_log,stime should be unique key.


Is really up to you if you want to normalize or not, depends largely on the requirements of your implementation and how are you displaying (or not) the data...

I think an alternative approach to the dataset you present would benefit a lot of a structure such as:

      ----------------
      |   Authors    |
      ----------------
      | AuthorId     |<-.
      | Name         |  |
      ----------------  |
                        |
      ----------------  |
      |  Publishers  |  |
      ----------------  |
      | PublisherId  |<-+-.
      | Name         |  | |
      | IsActive     |  | |
      ----------------  | |
                        | |
      ----------------  | |
      |    Tags      |  | |
      ----------------  | |
  .-->|  TagId       |  | |
 | .--|  CategoryId  |  | |
 | |  |  TagValue    |  | |
 | |  ----------------  | |
 | |                    | |
 | |  ----------------  | |
 | |  |  Categories  |  | |
 | |  ----------------  | |
 | `->| CategoryId   |  | |
 |    | CategoryValue|  | |
 |    ----------------  | |
 |                      | |
 |    ----------------  | |
 |    |    Titles    |  | |
 |    ----------------  | |
 | .->| TitleId      |  | |
 | |  | AuthorId     |--` |
 | |  | PublisherId  |----`
 | |  | TitleName    |
 | |  | Year         |
 | |  ----------------
 | |
 | |  ----------------
 | |  |   TitleTags  |
 | |  ----------------
 | `--| TitleId      |
 `----| TagId        |
      ----------------

A junction table will store any amount of tags per title and an additional [tag]Category table could group this tags so you'll be able to query for entire groups instead of 'per tag' search.

Note that by, also, separating the Publishers you could add more fields (like IsActive in the example), so then you can have more granular queries, be more specific on what your looking for.


Can Sql Server Backups be replaced by a netapp SnapManager?

Not at all. A SAN snapshot is a bitwise copy of raw disk state. If and only if all the responsibility for business continuity is on SAN storage team, this might be acceptable. For most of the business cases, this will present such RPOs and RTOs that the solution is not going to be feasible. What's more, restoring a SAN snapshot to a stable state is problematic. If the server was up and running during the snapshot, the server will return to state like someone hit a reset button. That is, a dirty filesystem, incomplete transactions and stuff. To get a clean snapshot, the server must be shut down a priori - and that's a business outage.

With SAN snapshotting only, you'd miss at least point in time recovery, as all the databases must be in simple recovery model due missing log backups. This is a killer for most recovery objectives.

Denny Cherry has written a whole article about failure points.


Sub-query executes 1 time for a parent query or more?

Prior to MySQL 5.6, the inner query is executed once per entry in the outer row and is easy to prove:

mysql> SELECT COUNT(*) FROM sample_data;
+----------+
| COUNT(*) |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM sample_data WHERE id = (SELECT SLEEP(1) );
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (8.00 sec)

Note that the query takes 8 seconds, thus the SLEEP(1) is executed 8 times, once for each row in sample_data.

MySQL 5.6 has implemented subquery materialization which, in theory, will prevent this from happening in many cases.  MariaDB also has a greatly improved optimizer that should prevent this.

In most cases, it is preferable to avoid subqueries in MySQL.


Those packages are related to Management Data Warehouse.

If you open SQL Server Management Studio you will find a node 'Data Collection' where you can right click to configure data collection and also have access to some historical reports about server activity.

Have a look at System Data Collection Sets where for example it states:

  The Query Activity collector type collects data by using the
  QueryActivityCollect.dtsx SSIS package, and uploads data by using the
  QueryActivityUpload.dtsx SSIS package.

For more information see Data Collector Architecture and Processing.

I'm not sure how you got to a point where you get 2 versions of each package (where one is appended by (1)). I tried reproducing that by enabling/disabling collection or creating a new MDW but maybe you got into that state by upgrading and then re-enabling data collection or something.


Tempdb data files are growing fast in SQL server 2014?

Before troubleshooting TempDB space, it is good to know what objects are using TempDB.

Even though you have issues only with Version Store, knowing what objects are using TempDB will give you a good idea on where to start.

Remember, TempDB is a global resource for all databases, so this will be used for all databases. At a high level these are the objects that can use TempDB:

1. User objects

User-defined tables and indexes
System tables and indexes
Global temporary tables and indexes
Local temporary tables and indexes
Table variables
Tables returned in table-valued functions

2. Internal objects

These are internal objects created by SQL Server for Work Tables, Hash Joins, sorts...

3. Version Store

Triggers
MARS
Online index
Row version-based isolation levels: requires setting at the database level

Further Cursors, Database Mail, DBCC CHECKDB can also use TempDB. See this article for more details Capacity Planning for tempdb

Now that we know how TempDB  is used, we can track TempDB space utilization using DMVS provided by SQL Server..

To Track user objects which are consuming the most space:

space used by user objects:(Active Only)

   WITH task_space_usage AS (
    -- SUM alloc/delloc pages
    SELECT session_id,
           request_id,
           SUM(internal_objects_alloc_page_count) AS alloc_pages,
           SUM(internal_objects_dealloc_page_count) AS dealloc_pages
    FROM sys.dm_db_task_space_usage WITH (NOLOCK)
    WHERE session_id <> @@SPID
    GROUP BY session_id, request_id
)
SELECT TSU.session_id,
       TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],
       TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],
       EST.text,
       -- Extract statement from sql text
       ISNULL(
           NULLIF(
               SUBSTRING(
                 EST.text,
                 ERQ.statement_start_offset / 2,
                 CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset
                  THEN 0
                 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END
               ), ''
           ), EST.text
       ) AS [statement text],
       EQP.query_plan
FROM task_space_usage AS TSU
INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK)
    ON  TSU.session_id = ERQ.session_id
    AND TSU.request_id = ERQ.request_id
OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST
OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP
WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL
ORDER BY 3 DESC;

Space used by Internal Objects((Active Only)):

SELECT
    t1.session_id,
    t1.request_id,
    t1.task_alloc,
    t1.task_dealloc,
    t2.sql_handle,
    t2.statement_start_offset,
    t2.statement_end_offset,
    t2.plan_handle
FROM (SELECT session_id,
             request_id,
             SUM(internal_objects_alloc_page_count) AS task_alloc,
             SUM(internal_objects_dealloc_page_count) AS task_dealloc
      FROM sys.dm_db_task_space_usage
      GROUP BY session_id, request_id) AS t1,
      sys.dm_exec_requests AS t2
WHERE t1.session_id = t2.session_id AND
     (t1.request_id = t2.request_id)
ORDER BY t1.task_alloc DESC

Version Store:
You can find the list of transactions which are using version store. To see how much space they consume, refer to article at the end

SELECT top 2
    transaction_id,
    transaction_sequence_num,
    elapsed_time_seconds
FROM sys.dm_tran_active_snapshot_database_transactions

Space used by transaction will be cleaned automatically when the transaction has completed. If the transaction is open for long time, it will prevent Version Store cleanup and you may have to check why, by joining with sys.dm_exec_sessions or sys.sysprocesses

You also can automate this space usage collection for later analysis.

This is just to give you idea, but it may not work in your version as some columns might have changed.

Query to gather details:

CREATE PROC sp_sampleTempDbSpaceUsage AS
  Instance level tempdb FILE space usage FOR ALL files WITHIN
  -- tempdb
  INSERT tempdb_space_usage (
    scope,
    Instance_unallocated_extent_pages,
    version_store_pages,
    Instance_userobj_alloc_pages,
    Instance_internalobj_alloc_pages,
    Instance_mixed_extent_alloc_pages)
  SELECT
    'instance',
    SUM(unallocated_extent_page_count),
    SUM(version_store_reserved_page_count),
    SUM(user_object_reserved_page_count),
    SUM(internal_object_reserved_page_count),
    SUM(mixed_extent_page_count)
  FROM sys.dm_db_file_space_usage

    -- 2. tempdb space usage per session
    --
  INSERT tempdb_space_usage (
    scope,
    session_id,
    Sess_task_userobj_alloc_pages,
    Sess_task_userobj_deallocated_pages,
    Sess_task_internalobj_alloc_pages,
    Sess_task_internalobj_deallocated_pages)
  SELECT
    'session',
    session_id,
    user_objects_alloc_page_count,
    user_objects_dealloc_page_count,
    internal_objects_alloc_page_count,
    internal_objects_dealloc_page_count
  FROM sys.dm_db_session_space_usage
    WHERE session_id > 50
    -- 3. tempdb space usage per active task
    --
  INSERT tempdb_space_usage (
    scope,
    session_id,
    Sess_task_userobj_alloc_pages,
    Sess_task_userobj_deallocated_pages,
    Sess_task_internalobj_alloc_pages,
    Sess_task_internalobj_deallocated_pages,
    query_text)
  SELECT
    'task',
    R1.session_id,
    R1.user_objects_alloc_page_count,
    R1.user_objects_dealloc_page_count,
    R1.internal_objects_alloc_page_count,
    R1.internal_objects_dealloc_page_count,
    R3.text
  FROM sys.dm_db_task_space_usage AS R1
    LEFT OUTER JOIN
    sys.dm_exec_requests AS R2
    ON R1.session_id = R2.session_id
    OUTER APPLY sys.dm_exec_sql_text(R2.sql_handle) AS R3
  WHERE R1.session_id > 50

Once you have gathered enough data, you can analyze using below queries:

Query 1: This query reports the maximum allocated space in TempDB over all the data points collected

SELECT
  CONVERT (float, (MAX(version_store_pages +
      Instance_userobj_alloc_pages +
      Instance_internalobj_alloc_pages +
      Instance_mixed_extent_alloc_pages)))/ 128.0
    AS max_tempdb_allocation_MB
FROM     tempdb_space_usage
WHERE scope = 'instance'

Query 2: This query computes the maximum allocated pages and the version store size in megabytes over all the data points collected. If the amount of TempDB space that is allocated to the version store is large, it implies that long-running transactions are generating or consuming versions.

SELECT
  MAX(version_store_pages) AS max_version_store_pages_allocated,
  MAX(version_store_pages/128.0) AS max_version_store_allocated_space_MB
FROM tempdb_space_usage
WHERE scope = 'instance'

Query 3: This query shows the top five queries that are allocating the most pages for internal objects.

SELECT top 5 MAX ((Sess_task_internalobj_alloc_pages) - (Sess_task_internalobj_deallocated_pages))
  AS Max_Sess_task_allocated_pages_delta,     query_text
FROM tempdb_space_usage
WHERE scope = 'task' and session_id > 50
GROUP BY query_text
ORDER BY Max_Sess_task_allocated_pages_delta  DESC

Below are the references i used which can help you further:

https://blogs.msdn.microsoft.com/sqlserverfaq/2010/10/13/troubleshooting-tempdb-growth-due-to-version-store-usage/
How to identify which query is filling up the tempdb transaction log?
https://technet.microsoft.com/en-us/library/ms176029(v=sql.105).aspx
https://technet.microsoft.com/en-us/library/cc966545.aspx

Below is must read:

https://technet.microsoft.com/en-us/library/dd672789(v=sql.100).aspx
https://technet.microsoft.com/en-us/library/cc966545.aspx


Can a MSSQL job change its own active schedule?

You can, using the stored procedure sp_update_schedule

    declare @shcedule_id int

    if exists (select 1 from your_table where condition = 'met') begin

    select @shcedule_id  = s.schedule_id from sysjobs j
    inner join sysjobschedules s on j.job_id = s.job_id
    where j.name = 'test_job'

    sp_update_schedule @schedule_id = 9, @freq_type = 4, @freq_recurrence_factor = 1,
    @active_start_date = 20190529, @active_start_time = 155500

    end

This will change it to weekly, recurring every one 1 week at 3:55 pm.

you can set it back to daily by setting @freq_type = 4

here's the Microsoft doc on the SP:

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-update-schedule-transact-sql?view=sql-server-2017


Postgres configuration suitable for a single 300 GB dataset (5 billion rows)

work_mem = 25GB

With only 32GB of RAM, this is much too high.  With two people working independently, and some queries potentially having multiple sort/hash nodes apiece, you can easily oversubscribed memory here.  I wouldn't set this this higher than 4 GB.  And even that is probably aggressive if you expect much parallelism, as each parallel worker takes work_mem independently. So unless you are going to keep a window open running "top" or similar and constantly monitor it, probably 1 GB is a better upper limit.

max_worker_processes = 8
max_parallel_workers = 8
max_parallel_workers_per_gather = 4

I'd maybe double all of these.  Since you have chosen a burstable instance, I'm assuming there are long periods of idleness.  When it is active, you want to throw all the CPUs you have at "max_parallel_workers_per_gather".  There is no point saving half the CPUs for the benefit of another backend who probably doesn't exist at the moment. On the other hand, what AWS calls a vCPU is only about half a CPU, so maybe the current settings are about correct.

I didn't know what to make of your collation of "C.UTF-8", so I ran a test.  A CPU-bound index build under "C.UTF-8" took about the same amount of time as it did under "en_US", which was about two to three times as long as under "C".  (twice as long in version 12dev, three times as long in version 10.)  So I would recreate your database using "C", not "C.UTF-8", unless you have a librarian or a lawyer telling you that you can't do that.

The first where-less 'select count(*)...' after the data is loaded is going to have do much of the same work as VACUUM does, if the table hasn't already been vacuumed.  This will slow it down a lot, and so will not give you an accurate idea of what the steady state will look like.  So you should either make sure to VACUUM first, or discard the timing of the first 'select count(*)'.  Also, "forever" is not very precise, something like "I killed it after 5 hours" would at least give us a lower bound.

  Should I just give up on postgresql and resort to another database?

I don't know enough about them to recommend them by name, but a database designed specifically for analytics should certainly be better at that tailored task then a general purpose database like PostgreSQL.  There are many tradeoffs to be made, and when it really must choose between them PostgreSQL usually chooses in favor of ACID-compliant transaction processing, rather than batch processing. But squeezing the last ounce of performance out of any product is going to come with a steep learning curve. Flitting from product to product without putting in the time to learn each thoroughly will probably not lead to better results.


table is corrupted after getting 1114 error : table is full

DROP the table.
Re-import, but make sure the table is MyISAM, not InnoDB.

Now you can at least see if the data is bad.  And you can experiment with INSERT INTO inno_table SELECT * FROM myisam_table.  There is a chance (slim chance) that CSV is the problem.  MyISAM is more compact on disk, so a larger file can be loaded before hitting 4GB (in the .MYD file).

Another thought...  Did you have indexes (other than the PRIMARY KEY)?  Try loading without the indexes in place.

Was the data in PK order?  That will make the loading faster and more efficient and take less space.  Can you sort the CSV before importing?

Generally, loading with the PK in place is best for InnoDB.  Please provide the CREATE TABLE so we know what you are working with.

Another thing to try:  Use the mysql commandline tool; within it, use the LOAD DATA command.  (I don't hold out much hope for this because I would assume that mysqlimport uses the same command.)

Yet another:

Break the source file into multiple pieces (each under, say, 1GB).  Import them into different tables.  Then use SQL statements to put them together.  And get rid of the intermediate tables.


Is it possible to setup failover clustering between a 32 bit instance and a 64 bit instance?

Windows Server 2008 (no R2) did ship a 32-bit variant of the OS (last one MS shipped that way). So you can cluster it. You can install a 32-bit FCI on a 32-bit node. You cannot install a 32-bit FCI on a 64-bit node. So no, what you want is not possible. If you need a 32-bit FCI, install it on a 32-bit OS.


What will be with MySQL cluster if network problems between nodes?

It will be decided by an arbitrator node which is normally a MGM node.
It would be advisable to put this arbitrator node in a 3rd DC.

In the case of 2 DCs the DC where the arbitrator resides will survive
if the network between the DCs is broken. With a 3rd DC with the arbitrator
node, the one to first ask for arbitration will fail.


For further context, the bash script is executed from a shell_exec in a php page (fastcgi/nginx).

Try hard setting the path to the psql script so you don't rely on the environmental PATH and PHP setting that when it calls the shell,
Rather than,
$output = shell_exec('psql ...');

Try (or wherever command -v psql shows it's located)
$output = shell_exec('/usr/bin/psql ...');


Perhaps you can create a DB user whose sole purpose in life is to collect the processlist.

Your error message indicates that zabbix does not have the PROCESS privilege.

Without the PROCESS privilege, zabbix can only view the processlist of zabbix users. Not much fun, eh?

But WAIT, you said

mysqladmin -u zabbix processlist

That will attempt to connect as zabbix@localhost which you did not define. Please note:

zabbix@localhost will authenticate via the socket file
zabbix@'%' will authenticate via TCP/IP

Simply run

GRANT SELECT, PROCESS ON *.* TO 'zabbix'@'localhost';

and your troubles should be over.

Without adding another MySQL user, just specify the TCP/IP protocol

mysqladmin -u zabbix --protocol=tcp processlist


Backup File Size Almost 50% of Database File Size with TDE Enabled

Community Wiki answer generated from comments on the question by QueueMann and Aaron Bertrand♦

Database backup files throws away any unused space in your data files in the backup process.

TDE doesn't have to encrypt empty pages, and the backup process doesn't have to include the space in the backup, but they are still empty pages contributing to data file size.