How do you handle maintenance window for DDL commands on Primary replicating to readable secondary AGs We are currently running into this issue for one of our availability group design:-
On Primary :- Nightly Table switch/Purges are happening (DDL) for approx. 2-3 hours
On Secondary which is configured as readable replica there will be SELECT queries running every now and then , as we do not have a window defined for secondary, REDO thread on secondary tends to get blocked by those SELECT queries which keep coming.
Currently to mitigate this issue, manually kill all select queries which keep blocking REDO thread ( this is in turn block other readers) until we see last commit time or redone time from SSMS AG dashboard sync up with the time DDL operation is completed.
Database in question is RCSI
Questions:-

Is this expected way to keep killing SELECT if you don't have a defined maint'ce window for secondary server

How are you guys dealing with this situation? I mean is there a way or script available online that it is automated to KILL such transactions on secondary in FLY when DDL is running on primary and wait until committed on secondary "?

disable read on secondary:
USE [master]
GO
LTER AVAILABILITY GROUP [AG-Something]
MODIFY REPLICA ON N'SQLSERVER-SECONDARY' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS =  NO))
GO

do your ddll workload on primary

re enable read on secondary
USE [master]
GO
ALTER AVAILABILITY GROUP [AG-Something]
MODIFY REPLICA ON N'SQLSERVER-SECONDARY' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS =  READ_ONLY))
GO

you can chek the AG status between the step 2 and 3 with this query:
SELECT
ar.replica_server_name,
adc.database_name,
ag.name AS ag_name,
case HDRS.is_primary_replica
when 1 then 'Primary Replica'
else 'Secondary Replica'
end as Replica,
HDRS.synchronization_state_desc,
HDRS.synchronization_health_desc,
HDRS.recovery_lsn,
HDRS.truncation_lsn,
HDRS.last_sent_lsn,
HDRS.last_sent_time,
HDRS.last_received_lsn,
HDRS.last_received_time,
HDRS.last_hardened_lsn,
HDRS.last_hardened_time,
HDRS.last_redone_lsn,
HDRS.last_redone_time,
HDRS.log_send_queue_size,
HDRS.log_send_rate,
HDRS.redo_queue_size,
HDRS.redo_rate,
HDRS.filestream_send_rate,
HDRS.end_of_log_lsn,
HDRS.last_commit_lsn,
HDRS.last_commit_time
FROM sys.dm_hadr_database_replica_states AS HDRS
INNER JOIN sys.availability_databases_cluster AS adc
ON HDRS.group_id = adc.group_id AND
HDRS.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag
ON ag.group_id = HDRS.group_id
INNER JOIN sys.availability_replicas AS ar
ON HDRS.group_id = ar.group_id AND
HDRS.replica_id = ar.replica_id


Subset of Unordered Pairs of Distinct Primary Keys With Normalization I am currently in the process of designing a database. I have one table as follows:

CREATE TABLE Set (
    id INT PRIMARY KEY AUTO_INCREMENT,
    -- other values
);

Now I wish to create another table that will store unordered pairs of distinct primary keys from the Set table, without sacrificing normalization. My first idea was:

CREATE TABLE Pairs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    valueone INT NOT NULL,
    valuetwo INT NOT NULL,
    -- Appropriate foreign key constraints for valueone and valuetwo
);

But this has the problem that I could store, for example the pair valueone=1, valuetwo=1 which I do not want as they are not distinct, or I could store either both or just one of valueone=1, valuetwo=2 and valuetwo=2,valueone=1 which (depending on how I interpret the data) would clearly be de-normalized. What is a natural, normalized way to store this fairly simple piece of information?

You can enforce, that valueone < valuetwo. Unfortunately MySQL doesn't support check constraints with which this would be easy (You can define them, but they have no effect). You'd have to use triggers.

CREATE TRIGGER pairs_bi
               BEFORE INSERT
                      ON pairs
               FOR EACH ROW
BEGIN
  IF new.valueone >= new.valuetwo THEN
    SIGNAL SQLSTATE '45000'
           SET MESSAGE_TEXT = 'valueone >= valuetwo';
  END IF;
END;

CREATE TRIGGER pairs_bu
               BEFORE UPDATE
                      ON pairs
               FOR EACH ROW
BEGIN
  IF new.valueone >= new.valuetwo THEN
    SIGNAL SQLSTATE '45000'
           SET MESSAGE_TEXT = 'valueone >= valuetwo';
  END IF;
END;

To enforce uniqueness you can then use a unique constraint/index or, if it where me, I'd simply define the two columns as primary key.

ALTER TABLE pairs
            DROP id;

ALTER TABLE pairs
            ADD PRIMARY KEY (valueone,
                             valuetwo);

db<>fiddle


Mongo Storage Engine from mmap to wiredTiger We have a mongo(v3.0.3) database->collections which is in mmap storage engine.

We are trying to change it to wiredtiger

we tried the process below.But we still see the same old storage engine

http://docs.mongodb.org/master/release-notes/3.0-upgrade/#change-storage-engine-to-wiredtiger

Can some one help us with the steps to follow in migrating?

When I restore data it gives the below error messages

mongorestore --collection testCollections --db TestDataBase /backup/TestDataBase/testCollections.bson

error: E11000 duplicate key error index: TestDataBase.testCollections.$_id_ dup key: { : ObjectId('55f50ea2f7ec8be2e035800f') }

That error implies there is already data in TestDataBase.testCollections. You might consider using the --drop option:

Before restoring the collections from the dumped backup, drops the collections from the target database. --drop does not drop collections that are not in the backup.
When the restore includes the admin database, mongorestore with --drop removes all user credentials and replaces them with the users defined in the dump file. Therefore, in systems with authorization enabled, mongorestore must be able to authenticate to an existing user and to a user defined in the dump file. If mongorestore can’t authenticate to a user defined in the dump file, the restoration process will fail, leaving an empty database.


Best Way to Index Multiple Effective Date Columns I've got a table that is primarily to represent relationships between entities (i.e. mostly composed of foreign keys). These relationships change over time, and so the table has a StartDate and an EndDate column. I now need to add another dimension of start and end dates, meaning that the relationship can be viewed with two different date "lenses" (queried with two dates, @Date1 and @Date2), so the schema will look something like this:

MyJoinTable:

| Id | Entity1Id | Entity2Id | StartDate1 |  EndDate1  | StartDate2 |  EndDate2  |
|----|-----------|-----------|------------|------------|------------|------------|
|  1 | A         | B         | 1753-01-01 | 2018-09-01 | 1753-01-01 | 2025-01-01 |
|  2 | A         | B         | 2018-09-01 | 2018-10-01 | 1753-01-01 | 2018-11-01 |
|  3 | A         | C         | 2018-09-01 | 2018-10-01 | 2018-11-01 | 2025-01-01 |
|  4 | A         | B         | 2018-10-01 | 2025-01-01 | 1753-01-01 | 2018-11-01 |
|  5 | A         | D         | 2018-10-01 | 2025-10-01 | 2018-11-01 | 2025-01-01 |

The queries will mostly be joins to this table, for example:

SELECT e1.Field, e2.Field
FROM Entity1 e1
INNER JOIN MyJoinTable jt ON jt.Entity1Id = e1.Id
    AND StartDate1 <= @Date1 AND EndDate1 > @Date1
    AND StartDate2 <= @Date2 and EndDate2 > @Date2
INNER JOIN Entity2 e2 ON e2.Id = jt.Entity2Id

My questions are:

What's the best way to index this join table?

Index on Entity1Id
Index on Entity2Id
Composite Index on all four date columns? (StartDate1, EndDate1, StartDate2, EndDate2)

What's the best way to constrain the DB so that I only get one relationship row returned for any @Date1, @Date2 combination?
Do you have any recommendations on a better data model?

Introduction
I do not have the time for extensive testing, but can suggest from where
to start.
If you rewrite the query in a more symmetrical manner, to
emphasise that both entities are joined to a two-dimensional
cross-section of MyJoinTable:
SELECT E1.Field, E2.Field
FROM MyJoinTable JT
JOIN Entity1 E1 ON E1.Id = JT.Entity1Id
JOIN Entity2 E2 ON E2.Id = JT.Entity2Id
WHERE
    StartDate1 <= @date1 AND EndDate1 > @date1 AND
    StartDate2 <= @date2 AND EndDate2 > @date2

you will see that one reasonably efficient way of executing it is first
to extract that cross-section and then to join the entities to it.
The following equivalent query will serve for illustration purposes
(but do not use it!):
-- A sample to demostrate the desired order or execution:
SELECT E1.Field, E2.Field
FROM
-- 1. Calculate the cross-section:
(   SELECT JT.Entity1Id, JT.Entity2Id
    FROM MyJoinTable JT
    WHERE
        StartDate1 <= @date1 AND EndDate1 > @date1 AND
        StartDate2 <= @date2 AND EndDate2 > @date2
) SECT
-- 2. Join the entity tables:
JOIN Entity1 E1 ON E1.Id = SECT.Entity1Id
JOIN Entity2 E2 ON E2.Id = SECT.Entity2Id

It is the more important since you say that there is only

one relationship row returned for any @Date1, @Date2 combination.

For this reason, joining the entities in before MyJoinTable is reduced
to the required cross-section by @date1 and @date2 is inefficient as
it is likely to have many records for each entity so that the result
will have too many rows. The entites are best joined at the end, using
their natural key field, Id, which I assume is the clustered index.
The solutions below, therefore, propose different ways of calculating
this cross-section, corresponding to the SECT subquery of the example
above—
Soution I: The easiest
Let us try to add useful indices to the original query.  Since MSSQL's
composite indices are hierarchical, they are useless in optimising
interval comparisons, so the best we can do with the given structure is
to index one of the date fields, yet make sure to cover all the other
fields required form the table:
CREATE NONCLUSTERED INDEX SD1 ON MyJoinTable ( StartDate1 )
INCLUDE (Entity1Id, Entity2Id, StartDate2, EndDate1, EndDate2 )

The query then will be executed in the following order:

Using the index SD1, perform an index seek on MyJoinTable
to find records by StartDate1, and filter them by StartDate2,
EndDate1, and EndDate2 in the
residual predicate

Join the entity tables using their natural key Id.

This method is not very efficient because only one of the four date
predicates is fully optimised by an index, resudual predicates being
boring data grinders.
Solution II: Set intersection
Another symmetrical way to obtain the two-dimensional JOIN
cross-section goes through INTERSECTing the results of the four date
predicates:
SELECT Entity1Id, Entity2Id
FROM
(   SELECT Id, Entity1Id, Entity2Id FROM MyJoinTable
    WHERE @date1 >= StartDate1
    INTERSECT
    SELECT Id, Entity1Id, Entity2Id FROM MyJoinTable
    WHERE @date2 >= StartDate2
    INTERSECT
    SELECT Id, Entity1Id, Entity2Id FROM MyJoinTable
    WHERE @date1 <  EndDate1
    INTERSECT
    SELECT Id, Entity1Id, Entity2Id FROM MyJoinTable
    WHERE @date2 <  EndDate2 )
SECT

With the suitable indices,
CREATE NONCLUSTERED INDEX SD1 ON MyJoinTable (StartDate1)
    INCLUDE (Id, Entity1Id, Entity2Id)
CREATE NONCLUSTERED INDEX SD2 ON MyJoinTable (StartDate2)
    INCLUDE (Id, Entity1Id, Entity2Id)
CREATE NONCLUSTERED INDEX ED1 ON MyJoinTable (EndDate1  )
    INCLUDE (Id, Entity1Id, Entity2Id)
CREATE NONCLUSTERED INDEX ED2 ON MyJoinTable (EndDate2  )
    INCLUDE (Id, Entity1Id, Entity2Id)

the plan for this query includes only clean index seek (wihout residual
predicates) and hash match operations.
Observe that the entity keys in query and indices present a
fourfold redundancy, which may be removed—at the expense of a more
complicated execution plan—by joining the entities in separately:
SELECT JT.Entity1Id, JT.Entity2Id
FROM
(   SELECT Id FROM MyJoinTable WHERE @date1 >= StartDate1
    INTERSECT
    SELECT Id FROM MyJoinTable WHERE @date2 >= StartDate2
    INTERSECT
    SELECT Id FROM MyJoinTable WHERE @date1 <  EndDate1
    INTERSECT
    SELECT Id FROM MyJoinTable WHERE @date2 <  EndDate2 )
SECT
JOIN MyJoinTable JT ON JT.Id = SECT.Id

with these indices:
CREATE NONCLUSTERED INDEX SD1 ON MyJoinTable (StartDate1) INCLUDE (Id)
CREATE NONCLUSTERED INDEX SD2 ON MyJoinTable (StartDate2) INCLUDE (Id)
CREATE NONCLUSTERED INDEX ED1 ON MyJoinTable (EndDate1  ) INCLUDE (Id)
CREATE NONCLUSTERED INDEX ED2 ON MyJoinTable (EndDate2  ) INCLUDE (Id)

But since in either case each of four date predicates, bounding the date
on one end only, does not reduce the amount of data sufficiently, hash
matches may have to
spill
data into tempdb. If they do, this method is not fit for your
environment.
Solution III: A compromise
Now we can merge solutions I & II in order to come up with a plan that
does not require so much RAM and at the same time is reasonably fast:
SELECT Entity1Id, Entity2Id FROM
(   SELECT Id, Entity1Id, Entity2Id FROM MyJoinTable
    WHERE @date1 >= StartDate1 AND @date1 < EndDate1
    INTERSECT
    SELECT Id, Entity1Id, Entity2Id FROM MyJoinTable
    WHERE @date2 >= StartDate2 AND @date2 < EndDate2  )
SECT

Now the date constraints are more efficient in discaring data
because they bound the date on both ends. With the indices below:
CREATE NONCLUSTERED INDEX SE1 ON MyJoinTable (StartDate1)
    INCLUDE (EndDate1, Entity1Id, Entity2Id)
CREATE NONCLUSTERED INDEX SE2 ON MyJoinTable (StartDate2)
    INCLUDE (EndDate2, Entity1Id, Entity2Id)

each constraint uses an index seek with a residual predicate, which is
better than the single index seek in solution I and takes less RAM
than solution II. The plan shows three opportunities of parallisation:
one for each date constraint, and one for the INTERSECT operation.
A non-redundant version of this approach would be:
SELECT JT.Entity1Id, JT.Entity2Id FROM
(   SELECT Id FROM MyJoinTable
    WHERE @date1 >= StartDate1 AND @date1 < EndDate1
    INTERSECT
    SELECT Id FROM MyJoinTable
    WHERE @date2 >= StartDate2 AND @date2 < EndDate2  )
SECT
JOIN MyJoinTable JT ON JT.Id = SECT.Id

with indices
CREATE NONCLUSTERED INDEX SE1 ON MyJoinTable (StartDate1)
    INCLUDE (EndDate1, Id)
CREATE NONCLUSTERED INDEX SE2 ON MyJoinTable (StartDate2)
    INCLUDE (EndDate2, Id)

Although it should work better with your data, where the SECT subquery
returns at most one row, in my crude tests with randomly generated data
it has been less efficient because the server used a hash match instead
of a nested loop join with that single row. Do try it on your side.
Solution IV: Optimised structure
It is possible to optimise your query at the expense of introducing a
more complicated structure that requires additional maintenance and
slows down the modification of data in MyJoinTable. If you are willing
pay the price, store the date ranges as sets of days:
CREATE TABLE MyJoinTable
(   Id         INT IDENTITY(1,1),
    Entity1Id  INT,
    Entity2Id  INT,
    Range1     INT, -- reference to Ranges.Id
    Range2     INT  -- reference to Ranges.Id
)

CREATE TABLE Ranges
(   Id    INT,
    Date  Date
)

and query the relation thus:
SELECT E1.Field, E2.Field
FROM MyJoinTable JT
JOIN Entity1 E1  ON E1.Id = JT.Entity1Id
JOIN Entity2 E2  ON E2.Id = JT.Entity2Id
JOIN Ranges  R1  ON R1.Id = JT.Range1
JOIN Ranges  R2  ON R2.Id = JT.Range2
WHERE
    R1.Date = @date1 AND
    R2.Date = @date2

You will need some testing in order to determine optimal indices, but I
think the following should work:
CREATE NONCLUSTERED INDEX RD ON Ranges      ( Date   ) INCLUDE ( id )
CREATE NONCLUSTERED INDEX RR ON MyJoinTable ( Range1, Range2 )
-- optionally: INCLUDE (Entity1Id, Entity2Id)

Index RD will make sure the ranges are quickly found corresponding to
the specified dates, and index RR will help to find the record
matching these ranges. But then you shall have to devise a means of
filling the Ranges table and keeping it in sync with MyJoinTable,
because doing so by hand is out of the question.


Optimisation select with large IN and millions of records in result I work with a table which consists of 1.5B records.
DB: RDS, PostgreSQL 12.4, 16GB RAM, 4vCPU
Schema:
CREATE TABLE public.trip (
     id bigint NOT NULL,
     cell_to character varying NOT NULL,
     cell_from character varying NOT NULL,
     indicator character varying NOT NULL,
     time_id integer,
     weight double precision
);

CREATE INDEX ix_trip_cell_from ON public.trip USING btree (cell_from);
CREATE INDEX ix_trip_cell_to ON public.trip USING btree (cell_to);
CREATE INDEX ix_trip_indicator ON public.trip USING btree (indicator);
CREATE INDEX ix_trip_time_id ON public.trip USING btree (time_id);

I'm trying to pull all trips which happen within some cells (output around 7-12 million records):
EXPLAIN ANALYZE SELECT
    cell_to,
    cell_from,
    time_id,
    weight AS trips
FROM
    trip
WHERE
    cell_to IN (VALUES ... 1k values)
  AND
    cell_from IN (VALUES ... 1k values (the same as above))
  AND
    time_id IN (VALUES ... 3 to 20 values)
  AND
    indicator = 'some string';

The result you can find here https://explain.depesz.com/s/RxH4.
What I've tried:

Replaced with INNER JOINs -> got some improvements
Changed b-tree index to BRIN -> a bit improved timing
VACUUM, REINDEX, work_mem -> zero effect

The query still runs too long.
UPDATE:
Thanks to @NikitaSerbskiy and @Laurenz Albe, forcing postgresql using index and adding a multicolumn index helped a lot.

You might get bitmap index scans and better performance if you increase work_mem to something like 200MB or more, so that a bitmap for your table fits into it.
Other than that, the only remedy I can see is using more parallel workers by raising max_parallel_workers_per_gather.
But all these optimizations are questionable if you plan to run more than a single concurrent query on this tiny machine.
UPDATE:
Experiments with enable_seqscan = off suggest that PostgreSQL overestimates the cost of an index scan.
So if you lower random_page_cost to something closer to 1, PostgreSQL should choose the better plan automatically.


Source - ODBC is not compatible with this version of the DataFlow After deploying an SSIS package from a development server to another environment, execution and validation of the package fail with the following error:

  Error: The version of #5) Source - ODBC - Greenplum [object name] is not compatible with this version of the DataFlow

The SQL Server and Greenplum ODBC driver versions are the same on all relevant servers.  The servers are running SQL Server 2016, and SQL Server Management Studio 2017 is being used to deploy the package between servers.

Deploying the package with SQL Server Management Studio 2016 resolved the issue.


Within a RHEL 6.3 + High Reliability Add-on, how do I add DB2 (v9.7) into a service group to have the service controlled by the cluster? I have a cluster of machines running RHEL 6.3 with the High Reliability Add-on.

I have configured a Clustered Logical Volume Manager (CLVM) file system for DB2, which is working correctly.  Our DBA's have installed DB2 v9.7 on the cluster, providing me with a basic stop and start script.  However, after a month of research, I have not been able to figure out how to set up the service within the cluster.

Can anyone point me in the right direction?

As I mention in my comments above, setting up HADR (High Availability Disaster Recovery) with DB2 is pretty simple, especially if you are going Active/Passive with your nodes. You do not gain automatic failover per se. You would use the db2 takeover hadr on database <database-name> command for a normal takeover for maintenance and a db2 takeover hadr on database <database-name> by force in the case of a hard fail of the active node. This article is a good one for how to set that up. (I know it is about Rational Jazz, but it has the steps for how to configure simple HADR without db2haicu).

It sounds like you want automated failover. This means moving up the levels of HADR to start using db2haicu and TSA. Ember Crooks' blog has a part 1, part 2, and a part 3 that shows to set this up. This gets a little trickier because it involves another layer between the OS and the database that needs to be configured and it must know about IP addresses, ports, a virtual IP address, etc. But the benefit is that this extra layer checks to make sure your active nodes stay active, and if not, it fails over to a passive node.

I would also recommend (you and the DBA's) watching this replay from the DB2NightShow. Vikram Khatri from IBM goes through a set of slides that take HADR from its simplest to its most complex, based on what you are trying to do (ie, from active/passive on the same LPAR all the way up to multiple active/active phyiscal nodes with load balancing, etc.)

I'm guessing between Ember's blog and the DB2NightShow link you'll find what you are looking for. However, don't discount the first link. You could set up simple HADR without db2haicu and always "automate" failover with some scripts or something. It might be simpler than db2haicu until you can figure db2haicu out.


Multi-master synching in three geographically separate datacentres Our company needs to create an application that operates in all parts of the world. We are looking to have three servers (one in Europe, one in Asia and one in America). I'm looking to use multi-master synchronisation using Tungesten on MySQL with row-based replication. So far, I've tested this on three local VMs with success.

However, what happens if Database A disconnects with Database B and Database C still connected to eachother. Let's say Database A updates table Dogs and sets Dog_name = Rover where dog_id = 34, while Database B (and C) update to set dog_name = Fido where dog_id = 34. The situation can arise where B and C take the value Rover, while A takes the value Fido. I've tested this to confirm it does happen.

Tungsten allows you to generate a hash key of the databases and compare them. It generates a hash on one of the masters and sends it to the slaves which computer their own hash. This allows you to see if there is a synchronisation mismatch. However, I'm concerned in a big database this could take a lot of time and consume system resources.

Another option would be to timestamp all tables and use a script file to export all values updated say in the last hour to a central server which generates a hash keys, and if the hash keys are different (accross servers), compares the difference and mails the difference to the sysadmin to correct. The third option, would be to use Nagios to monitor and when the connection is restored, to run a hash file check.

My questions are as follows:
1. What are the risks of the above scenario occuring?
2. If anyone has any experience of this, is there a more optimal way of checking?
3. Are there any other major risks in a multi-master scenario to be aware of?

Ultimately, I plan to do extensive testing before implementing any solution like this, including breaking as much as possible to see what happens.

Many thanks.

I don't have experience of your exact situation, but could you use:

auto_increment_increment=3
auto_increment_offset=1 [then 2 and then 3]

on your different masters, so that each one generates a different set of id's.

e.g.

server 1: 1, 4, 7

server 2: 2, 5, 8

server 3: 3, 6, 9


Multiple IPs registered for listener when using AlwaysOn AGs w/ multi-subnet clustering We are testing AlwaysOn Availability Groups on a WSFC multi-subnet cluster and using dynamic DNS registration. Our issue is that sometimes the IP for the Availability Group Listener (AGL) in the active subnet is registered in DNS as well as the IP for the AGL that is in the inactive subnet.

When we initially setup the cluster, by default, RegisterAllProvidersIP is set to 1 so we expect both the active IP and inactive IP of the AGL to be registered in DNS.  We followed the steps outlined by the MS AlwaysOn Pro team to change RegisterAllProvidersIP to 0 so that only the IP in the active site is registered with DNS. We performed extensive testing once we made this change and discovered the following behavior:

About70% of the time, only the active site’s IP for the AGL is registered in DNS (Good!)
About 30% of the time, both the active IP and inactive IP for the AGL are registered in DNS (Bad!)

There is no pattern to the issue. We’ve looked at DNS when multiple records are created and they both have the exact same date/time stamps so the records are obviously being created at the same time. To clean up this issue, we simply run the following command and only the active IP is registered:
Get-ClusterResource AGL_Network_Name | Update-ClusterNetworkNameResource

Is anyone successfully using multi-subnet clustering with AA AGs in a production environment? If so, I’d appreciate any help you might be able to provide.

We’ve confirmed the issue occurs running under VMware and with physical servers. The VMs and physicals are located in physically different datacenters and subnets. We have a fast pipe between them and latency is minimal.  The VMs are running Server 2012 and the physicals Server 2012 R2. SQL Server 2012 SP2 is being used.

This document from Microsoft lists a known issue with .net 3.5 erroring out at times.   While not specifically identifying your issue in DNS --It may be the cause and is probably worth reading:

https://msdn.microsoft.com/en-us/library/hh213080.aspx#MultiSubnetFailover


Check progress of database restore running asynchronously I have a Powershell script that restores a database using the SqlRestoreAsync method.

I have a decent size database which I'm restoring (~55Gb) and I'm trying to find the progress of the restore process.

I know that if I run the restore through a T-SQL command then I can view the progress of the restore by querying a few DMV's or with the following query:

SELECT r.session_id
    , r.command
    , CONVERT(NUMERIC(6, 2), r.percent_complete) AS [Percent Complete]
    , CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time, GetDate()), 20) AS [ETA Completion Time]
    , CONVERT(NUMERIC(10, 2), r.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed Min]
    , CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0) AS [ETA Min]
    , CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS [ETA Hours]
    , CONVERT(VARCHAR(1000), (
            SELECT SUBSTRING(TEXT, r.statement_start_offset / 2, CASE
                        WHEN r.statement_end_offset = - 1
                            THEN 1000
                        ELSE (r.statement_end_offset - r.statement_start_offset) / 2
                        END)
            FROM sys.dm_exec_sql_text(sql_handle)
            )) AS [SQL]
FROM sys.dm_exec_requests r
WHERE command IN ('RESTORE DATABASE', 'BACKUP DATABASE');

This query works fine for T-SQL triggered restores  (or SSMS ones), but doesn't show up the restore progress for the restore triggered by the Powershell script.

I can see my database in SSMS's Object Explorer as being in "Restoring" state, so I know it's working, but .. is it running?

The restore process running this in Async mode seems to take a bit more than when running it through T-SQL, so far twice as much and still not finished.

Is there something I'm missing? Any help would be appreciated.

I don't see anything obvious about why this command won't be showing up like any other restore command, unless it is busy doing something other than RESTORE DATABASE.

I suggest identifying the session_id PowerShell is using (you should be able to see in sys.dm_exec_sessions or sys.dm_exec_connections), or you could change the script to obtain it and print it to the console. Then change your filter to:

SELECT command, status, percent_complete,
  wait_type, last_wait_type, wait_resource,
  blocking_session_id
FROM sys.dm_exec_requests
WHERE session_id = <x>;

This will help you verify:

if the command is actually a RESTORE or if Powershell is actually doing something else first
current status
percent complete, if that is available (it isn't always)
what it's waiting on (and if you run it repeatedly, if this is changing)
if your restore is actually being blocked by anything


Database Design question I am currently interning and one of my tasks is to design a relational database model for a wedding firm that plans and handles weddings.

There is a user log in, and the users will be bride, groom, vendors and guests. I am not exactly sure how I should architect the database as it is the first time I am making one.

USER (user_id, username, password, bride_id, vendor_id, guest_id)

BRIDE (bride_id, bride_fname, bride_lname)

VENDOR (vendor_id, vendor_fname_lname)

GUEST (guest_id, guest_fname, guest_lname)

This is a somewhat version of the schema I was designing, but I am unsure how I can have everything on the USER table instead of repeating fname, and other redundancies. Also, the reason I have not put everything in the USER table is because I am not sure how it would work out. Can someone please explain this to me?

this needs a lot more information
they are going to have registry, church, and a LOT of other stuff

you need to read a book on database design or do some tutorials as your design is not very good

starts with weddings plural
they are not going go want a separate database for each wedding

if everyone needs to logon then you need one table for that

wedding:
ID   PK
brideID   FK to user
groomID   FK to user
vendorID  FK to user

if you can have multiple vendors then need a separate table

user:
weddingID  PK  FK to weddingID
usrID      PK
password
fname  (vendor just does not have fname)
lastname

if they are not brideID, groomID or vendorID then they are a guest


You can analyze the usage of your query cache using the following formulas:

Current size compared with maximum available size:

To calculate the percentage used value for the query cache you can use the following formula:

((query_cache_size-Qcache_free_memory)/query_cache_size)*100

The Query Cache Hit Rate:

The percentage hit rate on the cache can be calculated as follows:

((Qcache_hits/(Qcache_hits+Qcache_inserts+Qcache_not_cached))*100)

e.g.The usage of 33% says that of all select statements executed, 33% of them can be satisfied by the cache and hence do not have to be re-executed.

Hits to Insert Ratio and Insert to Prune Ratio

These two ratios are calculated by the following two formulas:

Qcache_hits/Qcache_inserts
Qcache_inserts/Qcache_prunes

DO NOT allocate too much memory

Using less memory than available can reduce the performance, but using more memory than available can lead to worse performance or even crashes. A general resource allocation formula is:

memory=key_buffer+(sort_buffer_size+read_buffer_size)*max_connections


SQL Server Optimizer Time Threshold To Evaluate Candidate Plans In SQL Server, is there any time threshold to evaluate candidate plans? It is true that optimizer can't evaluate all possible plans so actually when it decides to quit and go for optimum plan execution?

If threshold does exists, is there any change in threshold for SQL Server 2017 ?

As far as I know, there is no hard "limit" in terms of some absolute value of time that the optimizer searches for a plan.

Each execution is budgeted some amount of percentage of time to explore the plan options. There are a number of factors that go into this - I trust that those factors are always changing somewhat behind the scenes between versions of SQL Server - there are optimizer improvements made and some new options or approaches to options could come out, the budget calculated could change, etc.

SQL Server (thankfully) uses a process where it looks for a "good enough plan" or a reasonable plan. The more complex your query is the more insanely high the number of permutations of choices the optimizer has to execute your query. So we want it to find a reasonable enough plan and move on - so our query can actually start executing. The whole point is "spend enough time to give me a plan that should work alright, don't spend too much time optimizing and exploring options"

We really don't have control over that. And we really shouldn't. We should look to the plan the optimizer came up with and focus more on tuning our queries, tuning our designs and tuning our indexes to best serve the business need.

Another way - in about 15 years of SQL Server Tuning, I've not really ever had to consider "darn it, if only the optimizer had 4 more ms of optimization" - normally all you need to know to make your queries faster is right there in the query plan you did get, or exploring questions like "what about a serial plan instead of a parallel plan? what happens if I throw a join hint in just to force order and see what decisions the optimizer makes so I can rewrite accordingly? What about row goals? What if I split this logic up, etc"


DBCC UPDATEUSAGE (dbname) runs successfully but does not fix the "disk usage report" I ran the DBCC UPDATEUSAGE command on our Dev box to see if it fixes the 'disk usage report'. It ran quickly ( 10 seconds) and ran successfully but did not fix the report. Any ideas about what the issue could be?
Thanks!

These numbers on the disk usage report look correct. It's important to note that the Total Space Reserved is displayed in GB, using the conversion 1024 MB = 1 GB

The formula used by the above report is straightforward:

Data File Space Reserved + Transaction Log Space Reserved = Total Disk Space Reserved

279,860.56 MB + 90.63 MB = 279,951.19 MB

279,951.19 MB / 1024 = 273.39 GB


Transaction over linked server causing time out When I execute this query on [RemoteServer] linked server, it works just fine:

INSERT INTO [RemoteServer].[DbName].[SchemaName].[TableName] VALUES (1, 'bar');

but when puting it inside a Transaction it doesn't works anymore:

BEGIN TRANSACTION
BEGIN TRY
    INSERT INTO [RemoteServer].[DbName].[SchemaName].[TableName] VALUES (1, 'bar');
    COMMIT TRANSACTION
    PRINT 'insert done!';
END TRY
BEGIN CATCH
    PRINT 'insert faild!';
    PRINT 'Error Message: ' + ERROR_MESSAGE();
    ROLLBACK TRANSACTION
END CATCH

RPC and RPC OUT are true over linked server.

When I execute secound script on my client, after timeout shows following message:

OLE DB provider "SQLNCLI11" for linked server "RemoteServer" returned message "No transaction is active.".

(0 row(s) affected)
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

It seems Windows Firewall blocks DTC packages. To allow it to communicate through Windows Firewall, go to Control Panel > Windows Firewall > Allowed Apps and from list of apps, check Distributed Transaction Coordinator for both Private and Public networks.

If it doesn't works for you check out these solutions:

Unable to begin a distributed transaction
No transaction is active message when accessing Linked Server


Relative cost of two similar queries involving XML columns I have two queries as shown below which do the same thing. Here xmlcolumn is a column with datatype XML. I use these queries to search for a string anywhere in the XML column.

I checked the execution plan of both these queries and find that the first query has a lower I/O cost and subtree cost than second one. I was expecting that the first one would have a higher cost as it uses cast and charindex, but that is not the case.

Why does it have a lower cost?

First query:

SELECT *
FROM mytable
WHERE ( Charindex('abc',CAST([xmlcolumn] AS VARCHAR(MAX)))>0 )

Second query:

SELECT *
FROM mytable t1
WHERE t1.[xmlcolumn].exist('//*/text()[contains(.,"abc")]')=1

Your queries are not equivalent. The first will give you rows where both node names and values match where your second query only check the node values.

SQL Fiddle

The extra cost for the second query comes from using a table valued function to shred the XML.

First query:

Second query:


Using XPath .exist to look for elements within a decimal range I am trying to do a range comparison in an xpath exist - but it is being ignored (returning all rows).  The element "caruwmarketfactor" is a decimal value (stored untyped) - can anybody see what I am doing wrong?

The element can appear anywhere in the document - I know in all our cases it will be in there only 1 time.  I am looking for the range of .70 to .75.

declare @table table (id int identity (1, 1), clientXml xml)

insert into @table (clientXml) values
('<root>
  <ApplicationData>
    <caruwmarketfactor>0.7468</caruwmarketfactor>
  </ApplicationData>
</root>'),

('<root>
  <ApplicationData>
    <caruwmarketfactor>0.55</caruwmarketfactor>
  </ApplicationData>
</root>'),

('<root>
  <ApplicationData>
    <caruwmarketfactor>0.76</caruwmarketfactor>
  </ApplicationData>
</root>')

select id
      ,clientxml.value('(//*/caruwmarketfactor)[1][text()]', 'decimal (22, 7)')
from @table t
where t.clientXml.exist(N'(//*/caruwmarketfactor[xs:decimal(text()[1]) gt xs:decimal("0.70")])
                       and (//*/caruwmarketfactor[xs:decimal(text()[1]) lt xs:decimal("0.75")])') = 1

UPDATE: I just tried using the same logic (value is between a range) for dates and same problem.  Whatever I am doing wrong can be applied to dates too.

exist return 1 if the expression you use returns at least one node. Put your expression in a query and look at what you get back.

select id
      ,clientxml.value('(//*/caruwmarketfactor)[1][text()]', 'decimal (22, 7)')
      ,clientXml.query(N'(//*/caruwmarketfactor[xs:decimal(text()[1]) gt xs:decimal("0.70")])
                          and (//*/caruwmarketfactor[xs:decimal(text()[1]) lt xs:decimal("0.75")])')
from @table t

Result:

id
----------- --------------------------------------- ---------
1           0.7468000                               true
2           0.5500000                               false
3           0.7600000                               false

You get one node back with the value true or false.

You can change your expression to return the value if it matches by doing the logic in the same predicate something like this.

select T.id,
       T.clientxml.value('(//*/caruwmarketfactor)[1][text()]', 'decimal (22, 7)')
from @table as T
where T.clientXml.exist('//*/caruwmarketfactor/text()[xs:decimal(.) gt xs:decimal(0.70) and
                                                      xs:decimal(.) lt xs:decimal(0.75)]') = 1;

The expression within the predicate returns true or false and if it returns true it will return one node to the exist function. If the predicate returns false there will be no node returned.

Logical Expressions (XQuery)


PgPool II: Write-Only and Read-Only DBs I'd like to setup the following infrastructure:

  PSQL 9.6 in USA (master)

  PSQL 9.6 in Europe (slave)

  PgPool-II in Europe

  App Server in Europe (it reads the DB in 90% of time).

My question is: Can I set write-only and read-only databases in pgpool? I'd like to send all my reads to slave and every write (but ONLY write) to master. Is this possible? Or in master/slave mode the read queries are sended to both master and slave?

The right way to handle this is to set up two different database users user_ro and user_rw for instance and then you grant usage on the schemas you need to access and grant only SELECT to the read-only user and all privileges to the read-write user. You then create two database connections in your application and use the appropriate one.

You cannot use pgpool-II to automatically distribute read and write queries because there is no way it can programatically inspect a query and say that it will not produce a write in the database. Even a simple select * from sometable; can trigger a write if there is a trigger on the table to record accesses ( a common scenario for sensitive data).

pgpool-II does a great job of keeping a connection pool open and ready and managing failover from master to replica transparently to the application but it won't solve the problem you have without using two separate connections and dealing with those connections in your application code. There are some ways to finesse this; like attempting all queries on all of the read replicas and retrying ones that failed on the master. But from a conceptual hygiene standpoint it's better to decide when you are are writing to the database and when you are reading from it.


Transactional replication - Table / Stored Procedure changes coming over, but not new Tables / Stored Procedures I've set up transactional replication between my on-prem database (SQL 2017) as a publisher and Azure as a subscriber.

My CHANGES (insert / update, alter stored procedure, alter table, etc) are coming over, but my NEW stored procedures aren't.  In SSMS 17, I went into Replication > Local Publications properties and checked the new stored procedure in the [Articles] tab, but it's still not making it over.

It's not clear what I need to do to get the new stored procedure to come over.  I looked at sp_addarticle but it looks like it would be the same that I did with the checkboxes in the GUI.

New objects need to be added since they are not part of the original publication  when you established replication between the publisher and subscriber.

You can add objects to the existing publication w/out needing to stop replication activity. Please see this link and the section titled "Replication Maintenance"


Is there a way to get the isolation level from sp_whoisactive out of the xml into the plain result? While I caught myself re-engineering the functionality of sp_who / sp_whoisactive using the sys.sysprocesses and sys.dm_exec_session views, to get an overview about running sessions and transactions on my server, I thought: "no, instead use something already that is available and tested!"

exec sp_whoisactive @get_additional_info = 1;

returns an additional XML column that offers lots of information, one of this is the Isolation Level.

I would like to have the isolation level in the primary resultset of sp_whoisactive. Did anyone have the same requirement and already solved it? Why is it not included in the primary resultset from scratch, since it is an important information.

sp_whoisactive

Using the built-in functionality of sp_whoisactive to return the schema of the result set, you can store that information in a temp table and then use CROSS APPLY to extract out one or more xml nodes from the additional information

--Drop temp table
IF OBJECT_ID('tempdb.dbo.sp_whois_active') IS NOT NULL
    DROP TABLE tempdb.dbo.sp_whois_active

--Use 'return_schema' to create a temp table to hold the results of sp_whoisactive
DECLARE @s VARCHAR(MAX)

EXEC sp_WhoIsActive @output_column_list = '[%]'
    ,@return_schema = 1
    ,@schema = @s OUTPUT
    ,@get_additional_info = 1

SET @s = REPLACE(@s, '<table_name>', 'tempdb.dbo.sp_whois_active')

EXEC (@s)

--Populate the temp table by executing sp_whoisactive
EXEC sp_WhoIsActive @output_column_list = '[%]'
    ,@destination_table = 'tempdb.dbo.sp_whois_active'
    ,@get_additional_info = 1

--Select all of the columns from the temp table and
--use CROSS APPLY to extract one or more xml nodes
SELECT who.*
    ,N.C.value('transaction_isolation_level[1]', 'varchar(100)') IsolationLevel
FROM tempdb.dbo.sp_whois_active who
CROSS APPLY additional_info.nodes('//additional_info') N(C)


Insert into a table selecting data from a sub query I would like to insert a few data into a table selecting data from a subquery having multiple joins.

I tried the following way:

insert into mytable (columns)
select * from
 (select columns
  from tableA
  UNION ALL
  select columns from tableB
  UNION ALL
  select columns from tableC)

Can you please suggest me a way out, since I am getting the following error while executing the above query.

  Incorrect syntax near ')'.

Add some alias:

insert into mytable (columns)
select t.* from (
    select columns from tableA
    UNION ALL
    select columns from tableB
    UNION ALL
    select columns from tableC
) t


Order by 1 shortens the result set to one row I'm running the following query:

DECLARE @sql NVARCHAR(max) = N''
SELECT @sql += N' EXEC dbo.DoSomething @Tablename = ''' + SCHEMA_NAME(T.schema_id) + N'.' + T.name + N''''
--SELECT *
FROM sys.tables T
INNER JOIN sys.indexes I ON I.object_id = T.object_id
ORDER by 1
PRINT  @sql

But I'm only getting one line printed.
If I take out the ORDER by 1 line, or replace it with
 ORDER BY t.name then I get the expected result.

Is this a bug in SQL Server?
I've tried it on SQL SErver 2016 SP1 and 2014 Sp2-Cu1.

Is this a bug in SQL Server?

No.

From PRB: Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location

  The correct behavior for an aggregate concatenation query is
  undefined.

See also this excellent answer by Martin Smith over at stackoverflow.
nvarchar concatenation / index / nvarchar(max) inexplicable behavior

Sometimes it works and sometimes it does not work and that is dependent on the execution plan. The magic works if the compute scalar doing the concatenation is positioned as the first operator below the select operator.

declare @T table(Name nvarchar(5));
insert into @T values (N'aaaa3'),(N'bbbb2'),(N'cccc1');
declare @S nvarchar(18) = N'';

select @S += ',' + T.Name
from @T as T;

But if you are unlucky you could end up with a different plan. This one has a sort operator between the select and the compute scalar.

select @S += ',' + T.Name
from @T as T
order by reverse(T.Name);

The best way to solve this (in my opinion) is to use for xml path to do the concatenation instead.

select @S  = (
             select ',' + T.Name
             from @T as T
             order by reverse(T.Name)
             for xml path(''), type
             ).value('text()[1]', 'nvarchar(max)');

Some version of SQL Server in the future will have a built in string aggregate function STRING_AGG (Transact-SQL).


What's the differences between sys.dm_exec_query_stats and sys.dm_exec_cached_plans? As per my understandings, from sys.dm_exec_query_stats, we can cross apply other DMFs to get the statement level cached plans and the query text. From sys.dm_exec_cached_plans, we can get the cached plans and the query text (by cross apply other DMFs). So what's the differences?

In terms of getting the plan, using sys.dm_exec_query_stats you can use statement_start_offset and End offset to get the plan for individual statement in a stored procedure.

Eg

select top 100 object_name(object_id),ps.execution_count as proc_exec_count, qs.plan_generation_num, qs.execution_count as Stmt_exec_count,qs.total_elapsed_time/qs.execution_count as AVG_Run_Time,   qs.total_worker_time/qs.execution_count AS Avg_CPU_Time,
qs.last_elapsed_time, qs.min_elapsed_time, qs.max_elapsed_time,qs.min_rows,qs.max_rows,
SUBSTRING ([st].[text],
        ([qs].[statement_start_offset] / 2) + 1,
        ((CASE [statement_end_offset]
            WHEN -1 THEN DATALENGTH ([st].[text])
            ELSE [qs].[statement_end_offset] END
            - [qs].[statement_start_offset]) / 2) + 1)
            AS [StatementText],cast(s3.query_plan as xml) query_plan
from sys.dm_exec_procedure_stats ps
JOIN [sys].[dm_exec_query_stats] AS [qs]
        ON [ps].[plan_handle] = [qs].[plan_handle]
cross apply sys.dm_exec_text_query_plan ([qs].[plan_handle], statement_start_offset, statement_end_offset) s3
CROSS APPLY [sys].[dm_exec_sql_text]    ([qs].[sql_handle]) AS [st]
where database_id=db_id('DB') --and qs.execution_count >100
and object_id in (object_id('SP Name'))
ORDER BY avg_run_time DESC

And obviously it has different information regarding query execution stats and plans.


How to reduce the response time of a query that yields ~4.9 million rows in ~12-13 seconds I have the following doubt about the execution performance of a query. This query takes about 12-13 seconds, and brings a total of 4,879,049 rows. Could I lessen this query's response time?

The query at issue is the following one:

  SELECT id_empresa,
         referencia,
         archivo,
         status
    FROM Outbound_call_files
   WHERE movido=0
     AND status NOT IN('Peticion')
ORDER BY id_empresa, status;

The execution plan is:

| id | select_type | table               | type | possible_keys | key           | key_len | ref   | rows    | Extra       |
+----+-------------+---------------------+------+---------------+---------------+---------+-------+---------+-------------+
|  1 | SIMPLE      | Outbound_call_files | ref  | motivo_status | motivo_status | 1       | const | 2561294 | Using where |
+----+-------------+---------------------+------+---------------+---------------+---------+-------+-

SHOW CREATE INDEX returns the following:

| Outbound_call_files |          1 | motivo_status      |            1 | movido      | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| Outbound_call_files |          1 | motivo_status      |            2 | id_empresa  | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| Outbound_call_files |          1 | motivo_status      |            3 | status      | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |

I can see the cardinality is too low.

The structure and constraints of this table are shown below:

CREATE TABLE `Outbound_call_files` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_empresa` int(11) NOT NULL,
  `id_campana` int(11) NOT NULL,
  `referencia` text NOT NULL,
  `movido` tinyint(4) NOT NULL,
  `archivo` varchar(50) NOT NULL,
  `status` varchar(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id_empresa_campana` (`id_empresa`,`id_campana`),
  KEY `motivo_status` (`movido`,`id_empresa`,`status`)
) ENGINE=InnoDB AUTO_INCREMENT=10144111 DEFAULT CHARSET=latin1

Executing profiling, this was the result:

SHOW PROFILE FOR QUERY 1;
+----------------------+-----------+
| Status               | Duration  |
+----------------------+-----------+
| starting             |  0.000070 |
| checking permissions |  0.000008 |
| Opening tables       |  0.000019 |
| System lock          |  0.000010 |
| init                 |  0.000030 |
| optimizing           |  0.000018 |
| statistics           |  0.000090 |
| preparing            |  0.000022 |
| executing            |  0.000004 |
| Sorting result       |  0.000006 |
| Sending data         | 12.512944 |
| end                  |  0.000017 |
| query end            |  0.000006 |
| closing tables       |  0.000011 |
| freeing items        |  0.000019 |
| logging slow query   |  0.000004 |
| logging slow query   |  0.000067 |
| cleaning up          |  0.000006 |
+----------------------+-----------+

Note: The database server has 150G RAM and the buffer pool is configured in 100G.

Create a new index that is based on movido,status, id_empresa, referencia, archivo

To convert the query to a covering index


Searching a database safely using SELECT I have a site that allows users to search the database for several items. However I am not that good with SQL and I have read that allowing user to type in boxes that then use "SELECT-statements" is not safe. Code below:

Search-button sends search as parameter:

protected void search_click (object sender, EventArgs e)
        {
            Response.Redirect("Search.aspx?Result=" + TextBoxSearch.Text);
        }

onLoad in my search-page it gets the value and calls getData():

search = Request.QueryString["Result"].ToString();
getData();

getData() method:

protected void getData()
        {
            using (SqlConnection con = new SqlConnection(strCon))
            {
                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "SELECT [categoryId], [category] FROM [Category] WHERE ([category]=@search)";
                    cmd.Parameters.AddWithValue("@search", search);

                    con.Open();

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            LabelCat.Text = reader.GetString(reader.GetOrdinal("category"));
                            HiddenFieldID.Value = reader.GetInt32(reader.GetOrdinal("categoryId")).ToString();
                        }
                        else
                        {

                            LabelCat.Text = "Add"; //When search is made but no cateogry found
                        }
                    }
                }
                con.Close();
            }
        }

Is this safe? I feel like it is very weak against sql-injections but then again I don't know much about that. If it's not safe, please suggest a safe way I can do this.

I am building my application in VS12 using ASP.NET and C# webforms. Building my database using SQL management studio.

EDIT: Eventually I will have to search several tables here using join-statements, don't know if that is relevant but thot I should mention it.

You have used parametrized query, which is the bare minimum expectation to prevent on SQL-Injection.

I would suggest some points you can take care of as a validation in C# code

I don't see any server side validation on the text you are getting from TextBoxSearch.Text, you can check for some set of character that can be an attempt for SQL Injection.

you can have a look at the set of input for SQL Injection and make sure that those characters are not there in input string before executing sql query.

Other suggestion is that if you are looking to have multiple joins etc in sql query a better choice is to convert it to Stored procedure.


sys.dm_db_stats_properties seems to be misbehaving for a small table - how to get the number of records of a table in a different way? I have this table in one of my databases:

IF OBJECT_ID('[dbo].[repl_Gender_Type]') IS NOT NULL
DROP TABLE [dbo].[repl_Gender_Type]
GO
CREATE TABLE [dbo].[repl_Gender_Type] (
[Gender_TypeID]  CHAR(1)                          NOT NULL,
[Gender_Desc]    VARCHAR(20)                      NOT NULL,
[Create_Date]    DATETIME                         NOT NULL,
[Create_Userid]  VARCHAR(20)                      NOT NULL,
CONSTRAINT   [PK__Gender__46DD686B]  PRIMARY KEY NONCLUSTERED ([Gender_TypeID] asc))

there are only 3 records on it.

even after running the following command:

update statistics dbo.repl_Gender_Type with fullscan

I get no results when running the following query:

select SP.*
from  dbo.sysarticles A
OUTER APPLY [sys].[dm_db_stats_properties](a.objid,1) sp
where a.objid = OBJECT_ID('dbo.repl_Gender_Type')

so dm_db_stats_properties does not keep track of small tables?
what is the work around to find the number of records in the table?

  sys.dm_db_stats_properties returns an empty rowset under any of the
  following conditions:

  object_id or stats_id is NULL.
  The specified object is not found or
  does not correspond to a table or indexed view. The specified

  statistics ID does not correspond to existing statistics for the
  specified object ID.

  The current user does not have permissions to
  view the statistics object. This behavior allows for the safe usage of
  sys.dm_db_stats_properties when cross applied to rows in views such as
  sys.objects and sys.stats.

none of the above is right, me thinks.

I like to use this script below, that comes from this question:

sp_updatestats vs Update statistics

SELECT [sch].[name] + '.' + [so].[name] AS [TableName] ,
[ss].[name] AS [Statistic],
[sp].[last_updated] AS [StatsLastUpdated] ,
[sp].[rows] AS [RowsInTable] ,
[sp].[rows_sampled] AS [RowsSampled] ,
[sp].[modification_counter] AS [RowModifications]
FROM [sys].[stats] [ss]
JOIN [sys].[objects] [so] ON [ss].[object_id] = [so].[object_id]
JOIN [sys].[schemas] [sch] ON [so].[schema_id] = [sch].[schema_id]
OUTER APPLY [sys].[dm_db_stats_properties]([so].[object_id],
[ss].[stats_id]) sp
WHERE [so].[type] = 'U'
AND [sp].[modification_counter] > 0--change accordingly
ORDER BY [sp].[last_updated] DESC;

Looking for row counts in stats objects is problematic.  Especially since stats are not updated automatically every time a row is inserted, deleted, or modified.

Use sys.dm_db_partition_stats instead.  It returns row count information for every partition in the database.

SELECT Name = QUOTENAME(sch.name) + N'.' + QUOTENAME(o.name)
    , dbps.partition_number
    , dbps.row_count
    , dbps.*
FROM sys.schemas sch
    INNER JOIN sys.objects o ON sch.schema_id = o.schema_id
    INNER JOIN sys.dm_db_partition_stats dbps ON o.object_id = dbps.object_id
WHERE o.is_ms_shipped = 0
    AND (dbps.index_id = 0 OR dbps.index_id = 1); --heap or clustered indexes only

I wrote a blog post on SQLServerScience.com that shows some of the problems with the stats properties DMV in relation to row counts.  That post uses this query to obtain row counts, and shows when the stats object was last updated:

SELECT Name = QUOTENAME(sch.name) + N'.' + QUOTENAME(o.name) + N'.' + QUOTENAME(s.name)
    , ddsp.stats_id
    , last_updated = CONVERT(datetime2(1), ddsp.last_updated)
    , ddsp.rows
    , mod_count = ddsp.modification_counter
    , ddsp.unfiltered_rows
FROM sys.schemas sch
    INNER JOIN sys.objects o ON sch.schema_id = o.schema_id
    INNER JOIN sys.stats s ON o.object_id = s.object_id
    OUTER APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) ddsp
WHERE o.is_ms_shipped = 0;


Windows Authentication for a non-local non-domain Login I have SQL Server 2008 R2 installed on a Windows Server 2008 R2 box.  There is no domain in the network.

Is there any way for me to add a Windows Login on SQL Server for a non-local and non-domain user?  For instance, if the server is Server1 and a PC on the network is PC1, how can I add PC1\User1 as a Windows Login on the SQL Server instance installed on Server1?

Create matching usernames and passwords. It really is that simple. For your example, create an account for User1 on Server1 with the same password as on PC1.

Just in case this is an issue you're having with connecting SSMS with a non-domain account, that requires jumping through an additional hoop. Some applications don't support stored usernames and passwords via the windows credential store. SSMS is one of them.

I have a bunch of runas shortcuts to deal with connecting SSMS to a variety of servers in different domains:

C:\Windows\System32\runas.exe
/netonly
/user:DOMAIN\USER
"C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

You could also make use of the SysInternals ShellRunAs extension.


How can I determine the maximum possible number of threads? What factors determine the maximum possible number of threads mysqld will create?

I only care about consideration for mysql and not limitations that may be imposed by the operating system. Further, I only care about Linux, though if you want to include information specific to Windows, feel free.

As far as I can tell, threads can be created for replication, aspects of innodb, one per client connection up to max_connections, with an additional +1 to handle super connections. What else is there, or is that it?

threads_max =   max_connections
              + 1 for super
              + 2 for replication slave threads
              + ? for InnoDB ?

The best way to see current threads (including all foreground and background threads) is now with performance_schema.  For example:

mysql 5.7.3> select thread_id, name, type, PROCESSLIST_ID from performance_schema.threads;
+-----------+----------------------------------------+------------+----------------+
| thread_id | name                                   | type       | PROCESSLIST_ID |
+-----------+----------------------------------------+------------+----------------+
|         1 | thread/sql/main                        | BACKGROUND |           NULL |
|         2 | thread/innodb/io_log_thread            | BACKGROUND |           NULL |
|         3 | thread/innodb/io_read_thread           | BACKGROUND |           NULL |
|         4 | thread/innodb/io_read_thread           | BACKGROUND |           NULL |
|         5 | thread/innodb/io_ibuf_thread           | BACKGROUND |           NULL |
|         6 | thread/innodb/io_read_thread           | BACKGROUND |           NULL |
|         7 | thread/innodb/io_read_thread           | BACKGROUND |           NULL |
|         8 | thread/innodb/io_write_thread          | BACKGROUND |           NULL |
|         9 | thread/innodb/io_write_thread          | BACKGROUND |           NULL |
|        10 | thread/innodb/io_write_thread          | BACKGROUND |           NULL |
|        11 | thread/innodb/io_write_thread          | BACKGROUND |           NULL |
|        14 | thread/innodb/srv_lock_timeout_thread  | BACKGROUND |           NULL |
|        15 | thread/innodb/srv_error_monitor_thread | BACKGROUND |           NULL |
|        16 | thread/innodb/srv_monitor_thread       | BACKGROUND |           NULL |
|        17 | thread/innodb/srv_master_thread        | BACKGROUND |           NULL |
|        18 | thread/innodb/srv_purge_thread         | BACKGROUND |           NULL |
|        19 | thread/innodb/page_cleaner_thread      | BACKGROUND |           NULL |
|        20 | thread/sql/signal_handler              | BACKGROUND |           NULL |
|        22 | thread/sql/one_connection              | FOREGROUND |              2 |
+-----------+----------------------------------------+------------+----------------+
19 rows in set (0.00 sec)

I believe the background threads are fixed in number (the actual count will depend on some configuration settings such as innodb_read_io_threads and the number of innodb_purge_threads etc.)  The FOREGROUND threads are one per connection + a potential of thread_cache_size on top of that.


Choosing the right storage block size for sql server There are many articles on what storage blocks size should be used for sql server e.g. Disk Partition Alignment Best Practices for SQL Server. The right blocks size should improve the performance of a sql server database. I’m looking for a recommendations and methods to identify which storage blocks size is appropriated for a database. Is there a guide on how to identify an appropriated block size?

The method is the one used in the article that you linked in your question: test it. You can capture a representative workload for your application/database and replay that workload on servers that have different cluster sizes. What you will find out is that SQL Sever works best with 64KB clusters, because of the way it reads data from disk (read-ahead).

However, you don't have to do that: setting a 64KB cluster size is an established best practice, as clearly stated in the article that you refer to. From the article:

  The file allocation unit size (cluster size) recommended for SQL
  Server is 64 KB; this is reflected in Figure 4.

If you decided to test the performance under different cluster sizes, you would probably discover that the difference is negligible, especially on high-end storage systems.


Get complete value of varchar(max) field - SQL Server 2005 I have searched for a couple of hours but cannot find the exact solution for this.

In my SQL Server 2005 database table (tableA) there is a varchar(max) column.

I want to get complete value of that column (now it is not displaying any value) in SQL Server Management Studio.

Can anybody provide me a solution?

If the issue you are having is Truncation you can use this XML approach that gets around most of the issues with XML entitisation.

declare @VeryLongText nvarchar(max) = '';

SELECT top 100 @VeryLongText = @VeryLongText + '

' + OBJECT_DEFINITION(object_id)
FROM sys.all_objects
WHERE type='P' and is_ms_shipped=1

SELECT LEN(@VeryLongText)

SELECT @VeryLongText AS [processing-instruction(x)] FOR XML PATH('')

PRINT @VeryLongText /*WILL be truncated*/

Make sure that the "XML data" limit in SSMS is set sufficiently high!


Assigning empty string to INT NULL field in MySQL Point me a reference what happens if empty string ('') is assigned to a INT NULL field.

Does it become NULL or 0?

Do all versions (starting from 5) of MySQL do the same?

It depends on the sql_mode you're running as. If the mode isn't strict then you'll get the inserted value to be the same as the coercion to that type,

SELECT CAST('' AS int);
+-----------------+
| CAST('' AS int) |
+-----------------+
|               0 |
+-----------------+
1 row in set, 1 warning (0.00 sec)

This however is insanity, as you can see..

CREATE TABLE foo ( id int, a int, b int NOT NULL );

INSERT INTO foo VALUES
  (1,1,NULL),
  (2,NULL,''),
  (3,'',NULL),
  (4,NULL,NULL);

SELECT * FROM foo;
+------+------+---+
| id   | a    | b |
+------+------+---+
|    1 |    1 | 0 |
|    2 | NULL | 0 |
|    3 |    0 | 0 |
|    4 | NULL | 0 |
+------+------+---+
4 rows in set (0.00 sec)

So we can tell MySQL to try to be less of a joke, and more like a real database, like PostgreSQL

SET sql_mode='strict_all_tables';

And then,

TRUNCATE foo;

INSERT INTO foo VALUES
  (1,1,NULL),
  (2,NULL,''),
  (3,'',NULL),
  (3,NULL,NULL);

But this time we get,

  ERROR 1048 (23000): Column 'b' cannot be null

For more information see,

What is MySQL's "Implicit Default Value" and how does it work with "strict mode"?


`uuid_generate_v1mc` in Postgres “uuid-ossp” module: What is random multicast MAC address? The uuid-ossp module extension (plugin) for Postgres offers this alternative method for generating a UUID value.

uuid_generate_v1mc()
This function generates a version 1 UUID but uses a random multicast MAC address instead of the real MAC address of the computer.

I assume the intent here is to address a security concern about recording the MAC address of the database server. So instead we want to use another alternate MAC address in its place as a part of generating a Version 1 UUID value.
My question is: What exactly is this “random multicast MAC address” to be used in place of the db server’s own MAC?
I have googled/binged but found no good explanation. Is this some other MAC currently in use being found on the db server’s local network?
What are the practical issues involved in using this command, in the context of a primary key in Postgres?
Example data from calling uuid_generate_v1mc repeatedly, with UUID values in first column:
e2a03f96-0e7f-11ea-9838-6bba9e946aa0    Bird    2019-11-24 06:01:41.394401
e30c64be-0e7f-11ea-8540-9b38c7ef1573    Bird    2019-11-24 06:01:42.108340
e33037ea-0e7f-11ea-ab4b-abc4bdb5ea40    Bird    2019-11-24 06:01:42.342973
e3495cca-0e7f-11ea-8070-03c037680e82    Bird    2019-11-24 06:01:42.507537
e3668dd6-0e7f-11ea-b4e7-87c2c65a3777    Bird    2019-11-24 06:01:42.699283

RFC4122 documentation (section 4.1.6) specifies:

  For UUID version 1, the node field consists of an IEEE 802 MAC
     address, usually the host address.  For systems with multiple IEEE
     802 addresses, any available one can be used.  The lowest addressed
     octet (octet number 10) contains the global/local bit and the
     unicast/multicast bit, and is the first octet of the address
     transmitted on an 802.3 LAN.

  For systems with no IEEE address, a randomly or pseudo-randomly
  generated value may be used; see Section 4.5. The multicast bit must
  be set in such addresses, in order that they will never conflict with
  addresses obtained from network cards.

If I interpret this correctly, I'd say: a random Multicast address is any randomly generated MAC adress which has just the multicast bit set. The multicast bit is just one of the bits from the node part of a UUID (for all practical purposes, this just forces one specific bit of the UUID to be set).

Side Notes

I don't think you actually can specify a MAC address to the PostgreSQL function. If it follows the RFC, the function(s) must either use any of the MAC addresses available in your system, or a random one (with a specific bit set).

Whether this random value is always the same for a specific machine (which wouldn't look very random to me) or is just purely (pseudo)random and changing every time, is not clear from this explanation... but can be very easily tested:

SELECT uuid_generate_v1mc() AS u1, uuid_generate_v1mc() AS u2

gets me right now:

'91ccbe0c-488c-11e7-8d61-b7a8bb0bd0e3','91cd902a-488c-11e7-8d61-8bdf8f55ae02'

This translates (using the PERL program from https://stackoverflow.com/questions/1709600/what-kind-of-data-can-you-extract-from-a-uuid) to

time: Sat Jun  3 20:43:43 2017 +682.51ms
clock id: 36193
Mac: b7:a8:bb:0b:d0:e3
broadcast/multicast bit set.

and

time: Sat Jun  3 20:43:43 2017 +687.889ms
clock id: 36193
Mac: 8b:df:8f:55:ae:02
broadcast/multicast bit set.

... so, the MAC are actually completely (pseudo)random.

As pointed out by @EvanCarrol: I also think you're better off with v4 UUIDs, I don't think you'll get less collision risk with a randomly generated MAC.

Besides, very many network devices (routers, switches, etc.) have programmable MAC addresses (this is very handy when you want to replace one broken device by another, and make sure all the other devices don't notice any difference at all). This, somehow, makes the MACs not as unique as you probably thought.

Alternatvies: If you work with Windows, may be this tool can let you fake a MAC address. I've not tried it myself, so, "no strings attached".


See who granted a role in Postgres I want a query which tells me which user granted a role to another user in my Postgres database. Expected output would be as follows:

SELECT  role, grantor, grantee, grant_date FROM information_schema.<table>;

role              grantor              grantee            grant_date
grp_db_admin      Tom Dwan             Eli Elezra         2019-09-09
........
grp_db_viewer     Viktor Blom          Patrick Antonius   2019-08-02

So far I've found the information_schema.role_table_grants and pg_catalog.pg_roles tables, however these don't allow me to see who it was that granted someone a role group.

Any help would be appreciated.

PostgreSQL doesn't register the timestamp when a privilege was granted. The semantics would be unclear: what should the time be when you restore a dump? The time the privilege was originally granted or the time of the restore?

Anyway, you can get the rest of the information from the system catalogs:

SELECT roleid::regrole, member::regrole, grantor::regrole
FROM pg_catalog.pg_auth_members;


Why does assigning FOR XML to an XML variable warn about CONVERT_IMPLICIT(XML)? SET STATISTICS XML ON;
DECLARE @x XML = (SELECT 'hi' "@id" FOR XML PATH ('this'), root ('xml'));

  Type conversion in expression (CONVERT_IMPLICIT(xml,[Expr1001],0)) may affect "CardinalityEstimate" in query plan choice

Why is a conversion happening? I thought that FOR XML caused the result to be the XML type already. If this warning can/should be ignored, how do I stop the warning from appearing in the execution plan (so that when I debug a bigger query with a similar warning it doesn’t distract me from actual issues)?

The return type of the FOR XML PATH expression is NVARCHAR(MAX), not XML.

I was being confused by the fact that SSMS renders any column named XML_F52E2B61-18A1-11d1-B105-00805F49916B as if it has the XML data type. If I run the following command, I get a clickable XML result cell in SSMS:

SELECT 'hi' "@id" FOR XML PATH ('this'), root ('xml');

I was able to figure out the actual type by getting SQL Server to spit out a type-conversion error:

SELECT SQL_VARIANT_PROPERTY((SELECT 'hi' "@id" FOR XML PATH ('this'), root ('xml')), 'BaseType');

Msg 206, Level 16, State 2, Line 1
Operand type clash: nvarchar(max) is incompatible with sql_variant

To fix the warning completely, simply add the TYPE directive to the FOR XML clause:

SET STATISTICS XML ON;
DECLARE @x XML = (SELECT 'hi' "@id" FOR XML PATH ('this'), root ('xml'), TYPE);


Is it recommended to clear the query plan cache Pretty much as the title describes. I have just checked a SQL Server and noticed there is a lot of query plan bloat that can be fixed by proper parametrisation.

After making the changes to the code, is it recommended that we clear the query plan cache and let it rebuild itself? Or will it clear itself up in time?

NB: Out of the top 50 biggest queries, 47 of them were the same but with different parameters.

I would recommend against this.  You have done optimization on probably a small subset of queries, and over time you will see the improvement and performance gain.  But if you just do a clean refresh of the query plan cache you will blow all of the execution plans out of there, affecting more than just your optimizations.

I'd be willing to say the immediate performance degradation isn't worth the smaller gain of using your optimized plans right away.  But again, this is another "it depends".


How to clear an Error 15404 after a DB restart (besides rebooting) Every so often (e.g. ~months) a SQL Server Agent hourly Job will begin reporting an Error 15404 and continue to do so until intervened with.

  [298] SQLServer Error: 15404, Could not obtain information about
  Windows NT group/user 'DOMAIN_NAME\SomeDomainAccount', error code
  0x6e. [SQLSTATE 42000] (ConnIsLoginSysAdmin)

Sometimes the first failure occurs immediately after a manual restart of the SQL Server Engine and SQL Server Agent services.  The problem can be cleared by rebooting the machine.

The Job Owner is the name listed in the error message and is a SQL Server Admin.

The SQL Server Engine Service account looks to be a service account (I believe it is the default install account (one notch better than generic NetworkService to prevent interference between Engine/Agent instances):

   NT Service\MSSQL$INSTNAME

It would be one thing if the job always failed but since the job succeeds after a reboot it makes me think that a service account like is supposed to be working and that there is some A/D timing issue or possibly a bug.  When IT is asked about the A/D configuration, the response is usually "nothing has changed."

Restarting the engine and agent services can cause the job to start failing.
A machine reboot clears the problem.
An immediate subsequent restart of the engine and agent no longer cause the job to fail.

Link:
How to troubleshoot a SQL Server 8198 error

Not a solution, but you can work around the issue by making the job owner a SQL account.

Each time a job is started, SQL Server verifies the identity of the job owner and checks that it has permission to execute the job. If the owner is a Windows account, the engine needs to query Active Directory. If for any reason that fails, the job will not run. It could be because the AD server is busy, down or cut off from the network, or that the SQL Server service account doesn't have rights.

Since it works immediately after rebooting, that makes me think it might have something to do with cached credentials. Windows will save the credentials it looks up for later use. That cache is cleared on reboot. Perhaps something is corrupting the cache.


how to use an index on a mysql query with a where clause and a order by I have a table

CREATE TABLE activity (
 id INT(10) UNSIGNED AUTO_INCREMENT,
 subscriberid INT(10) UNSIGNED NOT NULL DEFAULT '0',
 action VARCHAR(250) NOT NULL DEFAULT '',
 ip INT(10) UNSIGNED NOT NULL DEFAULT '0',
 cdate DATETIME NULL DEFAULT NULL,
 PRIMARY KEY (id),
 KEY cdate (cdate),
 KEY subscriberid (subscriberid)
) ENGINE = InnoDB DEFAULT CHARSET = utf8 DEFAULT COLLATE = utf8_general_ci;

how can i return all rows for a single subscriberid ordered by cdate descending, using the index on  cdate field.

if i do

explain select * from activity where subscriberid = 2 ORDER BY cdate DESC;

it seems not to use the index. If i add a group by clause it doesn't do a filesort as you can see in the link but it still scans all rows
http://take.ms/8z3Po

Try a compound index on (subscriberid, cdate).
i.e.

ALTER TABLE activity ADD INDEX subid_date(subscriberid, cdate);


SQL Server Data Warehouse stored procedure location We are building a Kimball data warehouse. We have a Staging Database, and Final Datawarehouse.

We have a stored procedure which creates the table CustomerTransaction.
Where is the best location to place the stored procedure? In the Staging Database or the Datawarehouse? Or what is the positives/negatives of each location? I just started creating data warehouses and trying to learn.

create procedure dbo.FactCustomerTransactionImport  -- should this be in StagingDB or datawarehouse DB?
as

insert into DWFinal.dbo.FactCustomerTransaction
(
     CustomerId,
     Quantity,
     Price,
     Amount
)
select
     CustomerId,
     Quantity,
     Price,
     Quantity * Price as Amount
from StagingDB.dbo.CustomerTransaction

This is a matter of taste.  The main reason I see to break things up at all is for security, and secondarily for backups.

I think of ETL activity, including loading the final star schema tables, as an aspect of the Staging layer.  That's where all the "heavy lifting" happens in my warehouses.

Here's how I typically break up a data warehouse.  These should be distinct physical databases, and may be separate VMs or physical machines if the volume of data requires it.

A separate database for each important data source (CRM, OLTP database, telephony system, etc.).  Here, I store a straightforward copy of data from the source system, with a minimum of modification.  E.g., I will record DateCaptured and DateModified for each record, but perform no cleanup of data values or types.  The idea is to grab data as quickly as possible, to minimize load on the source system.

In principle, these databases can be purged at will and repopulated from the source systems with no change to the rest of the warehouse.
Using separate databases for each source lets users (i.e., service accounts) have limited access.  My Service_SalesforceReader user has read and write privs in the Salesforce database, and nowhere else.
Writing to these databases typically happens via dedicated ETL applications, with a few SQL procedures supporting them.

A Staging database for the intermediate steps; the "T" in "ETL".  This includes lookup tables for data cleaning, tables for allocating synthetic keys, and log tables for ETL processes.

Data here is mostly static which can be recreated from source control (e.g., country codes) or transformations of the source data, which can be recreated from those systems, albeit perhaps with different synthetic keys being created.  Only people on the warehouse team should need access here.
This database has 90% of the SQL procedures.  They format and pivot the data into the star schema layout. There's one objective reason to keep the ETL logic in Staging: security. Only warehouse admins need access to those procedures, whereas many people may need access to the Warehouse layer.

A Warehouse database, with tidy fact tables and dimension tables.  This layer may have views to help with specific consumers.  E.g., if you have a visualization tool that doesn't play nice with joins, there might be a view (possibly materialized) for each star, with all dimensions joined in.

The only sprocs I have in this layer, in my current warehouse, are for logging, and I don't believe they're actually used.

In practice, you might need to grant some power users access to specific source databases, if there's data they need that doesn't rate being incorporated into the star schema.  In such a case, you may also need to grant access to the synthetic key tables in Staging that link them together, so they can traverse from (for example) a CustomerSK integer to a Salesforce AccountID string.  Doing so makes it difficult for you to change anything without breaking people's processes, so this should be a last resort.


Importing 40 gb mysql file via phpmyadmin I have a 40 GB MySQL dump file. Can anyone give me the idea how to import the file via phpmyadmin?

If you really want to restore it with PHPmyadmin you have to split the files into little chunks, because PHP will block too large files, depending on the serverconfig.

Dump just the structure of your tables
Import them
Dump the data of your tables into a csv
split the csv (on windows you can do this with gsplit for example and on unix with the split command
Import the little chunks of data

But in general i'll recommend the commandline or contact your ISP


Postgresql Chained Slaves and Slave Promotion So I have a setup like Master -> Slave1 -> Slave2. Slave1 replicates from Master and Slave2 replicates from Slave1. If I promote Slave1 to master is it possible for Slave2 to continue replicating from Slave1. This the log I see on Slave2 after I promote Slave1 to master:

2014-06-13 04:28:11 UTC LOG:  replication terminated by primary server
2014-06-13 04:28:11 UTC DETAIL:  End of WAL reached on timeline 3 at 3E/62B2C758.
2014-06-13 04:28:11 UTC LOG:  fetching timeline history file for timeline 4 from primary server
2014-06-13 04:28:11 UTC LOG:  invalid record length at 3E/62B2C758
2014-06-13 04:28:11 UTC LOG:  restarted WAL streaming at 3E/62000000 on timeline 3
2014-06-13 04:28:11 UTC LOG:  replication terminated by primary server
2014-06-13 04:28:11 UTC DETAIL:  End of WAL reached on timeline 3 at 3E/62B2C758.
2014-06-13 04:28:16 UTC LOG:  restarted WAL streaming at 3E/62000000 on timeline 3
2014-06-13 04:28:16 UTC LOG:  replication terminated by primary server

You should be able to use recovery_target_timeline='latest' in your recovery.conf, as noted in the recovery target settings documentation

If you are on PostgreSQL 9.1 or 9.2 and using streaming replication, you'll need to have a WAL archive set up as well, if you want to have this continue automatically after failover, or as @amacvar mentioned, copy the necessary WAL segments over so that the replicas can continue streaming along the newer timelines.

In 9.3 and later, this happens without needing an additional WAL archive, because the timeline switch is now included in the replication stream. Michael Paquier details this behavior in his blog post timeline switch of slave node without archives


MySQL 5.7 enforce SSL I'm using MySQL 5.7 and I want to set up SSL to force the client to use a cert.
This cert is a self-signed cert that I generate.
I have the cert for both server and client being:
- CA
- cert
- key

Where the CA has the cert for server and client.
I then configured the config file (mysqld.conf) to use the cert:

[mysqld]
ssl
ssl-ca=/database/mysql/ssl/ca-cert.pem
ssl-cert=/database/mysql/ssl/master-public.pem
ssl-key=/database/mysql/ssl/master-private.pem

And run the command to enforce it:

GRANT ALL PRIVILEGES ON `database` TO 'user'@'192.168.10.10' IDENTIFIED BY 'password' REQUIRE SSL;

I can see SSL is enabled and it's picking up the right files running the command:

SHOW VARIABLES LIKE '%ssl%';

However, from the client machine (192.168.10.10. in this example) I can connect via command line without specifying any cert and it just connects.
I can see the connection is using SSL with:

mysql> \s
SSL:            Cipher in use is DHE-RSA-AES256-SHA

But the idea is to allow t he connection only if I use the certs.
Is there a way to enforce it to make sure it won't connect without specifying the cert ?
So I can configure the application to use it and make sure other applications wouldn't access it.

That would REQUIRE ISSUER in your user definition.


AWS iops vs disk iops? Recently devops guy from our company told me that our local mysql stage environment are much faster than aws, because local disk has 10k iops, but our aws rds has only 500 iops.

As far as I understand aws iops states for throughout capacity, not the disk speed. And still ssd disk io same 10k iops or more? And considering that the load is less than 500 iops, rds might be much faster?

AWS limits the IOP size to a maximum of 16KB, so whatever IOPS you buy, you will get throughput capped at 16KB times that number. So if you buy 1,000 IOPS on EBS, this will max out at 16MB/s.

If your application does random I/O using smaller blocks that aren't adjacent, e.g. 8KB, you will get maximum throughout of 8MB/s from the same EBS volume.

Historically, IOPS on spinning disks were limited by the rotational speed of the disk. In a 7200rpm disk, the correct sector comes around only 120 times per second.

A real NVMe SSD can handle much faster throughout at lower latencies than AWS EBS.

AWS is slow and expensive - learn to live with it. That is the price you pay for the convenience of not having to host and maintain your own hardware. How much slower cloud and VMs are than bare metal? This will depend to some extent on the nature of your workload, but in general, for equal spec, VMs are often much slower than bare metal.


What permissions are needed to view dependencies? My company is moving to a much more restrictive security model in the near future.

As part of that we are changing specific servers to the new model and testing all our existing processes.  One of the processes that I use a lot utilizes the SMO framework to script out database objects in dependency order, so we can run those scripts on another server.

The dependency order piece is very important as we have a lot of nested objects (think views that reference other views).

With the new security model in place, this script stopped working correctly.

I tested in SSMS as well (SSMS 2012 against a 2008r2 instance) and using View Dependencies on a view that references another view in the same DB doesn't show the referenced view under Objects on which [this view] depends.

Even more troubling, if I run sp_depends on the view in question I do get an accurate list of dependencies.

I did a bit of research and couldn't find a definitive answer, so I'm hoping someone can help me out:

What specific permissions are needed for a user to accurately view dependencies in SQL Server 2008r2 and/or SQL Server 2012 (we are upgrading soon).

The 2012 Books Online topic "Get Information About a View" states that the permissions required for this specific task are:

VIEW DEFINITION permission on the database; and
SELECT permission on sys.sql_expression_dependencies

Note that database-level VIEW DEFINITION is required to allow the user to see information in sys.sql_expression_dependencies; object-level VIEW DEFINITION and SELECT on the DMV will not work (you will receive an empty result set).

Example:

USE Sandpit;
GO
CREATE VIEW dbo.V1 AS SELECT 1 AS const;
GO
CREATE VIEW dbo.V2 AS SELECT v.const FROM dbo.V1 AS v;
GO
CREATE USER Bob WITHOUT LOGIN;

-- These two permissions are not sufficent
GRANT VIEW DEFINITION ON OBJECT::V1 TO Bob;
GRANT VIEW DEFINITION ON OBJECT::V2 TO Bob;

-- This one permission is enough (covers the two above)
GRANT VIEW DEFINITION ON DATABASE::Sandpit TO Bob;

-- Also required
GRANT SELECT ON OBJECT::sys.sql_expression_dependencies TO Bob;
GO
EXECUTE AS USER = 'Bob';
GO
SELECT OBJECT_DEFINITION(OBJECT_ID(N'V1', N'V'));
SELECT OBJECT_DEFINITION(OBJECT_ID(N'V2', N'V'));

-- Show DMV info for the test views
SELECT
    referencing_object_name = OBJECT_NAME(sed.referencing_id),
    sed.referencing_class_desc,
    sed.is_schema_bound_reference,
    sed.referenced_class_desc,
    sed.referenced_schema_name,
    sed.referenced_entity_name
FROM sys.sql_expression_dependencies AS sed
WHERE
    sed.referenced_id = OBJECT_ID(N'dbo.V1', N'V');
GO
REVERT;
GO
DROP USER Bob;
DROP VIEW dbo.V1, dbo.V2;

Regarding sp_depends: that requires only membership of the public role but is deprecated:

  This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities instead.


Understanding RMAN Backupset I feel a bit confused when I read about RMAN!
what are the differences between:

RMAN> backup as backupset
RMAN> backup database;
RMAN> backup database plus archivelog;
RMAN> backup as backupset database include current controlfile;

what is the difference between backup database and backup as backupset
also how to backup the database + needed archivelog + controlfile + spfile in one command for a full restore when needed?
is there any site explains this stuff in details with examples?

RMAN uses backupsets by default.  The following commands will create a backup to a backupset:

RMAN> backup database;
RMAN> backup database plus archivelog;

This is assuming that you haven't changed the default RMAN parameters:
RMAN> SHOW ALL;
...
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

"Backup as backupset" is not a valid command and will return a syntax error.
"Backup as backupset database include current controlfile" might be useful if you changed the default backup type to something other than a backupset (e.g. "BACKUP TO COPY").
Source: Database Backup and Recovery Basics


How to run update query on multiple table via joins simultaneously on postgres? In MySQL, I can do a query like this

UPDATE
    prd_sectionshapename  se
inner join
    prd_shape s
ON
    s.id = se.shape_id
SET
   se.company_shape_name = 'ABC',
   s.name_en = 'Another name'
WHERE s.serial_number = '1234ST';

In Postgres, I need to split up into 2 queries

UPDATE prd_shape AS s
SET name_en = 'Another name'
WHERE s.serial_number = '1234ST';

UPDATE prd_sectionshapename AS se
SET company_shape_name = 'ABC'
FROM prd_shape s
WHERE se.shape_id = s.id and s.serial_number = '1234ST';

My question is it possible to do it in 1 query just like MySQL?

Note that other postgres update join answers I found on the internet typically show that the update effect happens only on a single table even though a JOIN is used.

My purpose here is to update multiple tables in one query. Not just one table.

I don't see why you would want to do that. Two UPDATE statements in a single transaction will do just fine and won't incur any additional overhead compared to a single statement.

Having said that: it is possible by using a writeable common table expression

with shape_update as (
  UPDATE prd_shape
   SET name_en = 'Another name'
  WHERE serial_number = '1234ST'
  returning id, serial_number
)
UPDATE prd_sectionshapename
  SET company_shape_name = 'ABC'
WHERE (shape_id, serial_number) IN (select id, serial_number from shape_update);

That solution indeed has one advantage: you have to specify the value for the serial_number only once. But it has no performance or transactional advantages.


permissions for schema compare and view job history I need to grant these permissions,
what would be the minimum level I could grant so that they can do their job?

I am looking at SQL Server Agent Fixed Database Roles
and also Database Engine Permission Basics

I still could not find exactly,
what would be the least permissions to be granted?

For the msdb SQL Server Agent roles, granting SQLAgentUserRole allows them the ability to manage jobs which only they own, and see the job history of those jobs too.

To compare schema on all table of any DB, assuming you want to also allow them to have SELECT to all tables, the db_datareader role should be fine per DB.

Otherwise, if you want to be specific, then allow explicit VIEW DEFINITION on the schema per DB which you want to all them to compare and SELECT to the tables you want them to be able to compare the data.

EXPLICIT EXAMPLES TSQL (per the article)

GRANT VIEW DEFINITION ON OBJECT::Region TO Ted
GRANT SELECT ON OBJECT::Region TO Ted

EXPLICIT EXAMPLES TSQL (per the request in your screen shot)

GRANT VIEW ANY DEFINITION TO User

USE msdb

EXECUTE sp_addrolemember @rolename = 'SQLAgentReaderRole'
    ,@membername = 'username'

I think it depends on how well you want to lock it down really, and how trustworthy they are for what you are allowing them to have access to.


Does binary logging apply to ColumnStore? When binary logging is enabled, does MariaDB's binary logging also record ColumnStore events, and not just InnoDB events?  For example, does it log an UPDATE made to a ColumnStore table row?
I've searched the MariaDB documentation and it doesn't mention whether binary logging applies only to some, but not all, storage engines.  Is binary logging independent of the storage engine used?

Reading the official documentation at Overview of the Binary Log (MariaDB | Knowledge Base) the first paragraph states:

The binary log contains a record of all changes to the databases, both data and structure, as well as how long each statement took to execute. It consists of a set of binary log files and an index.

If you rummage through MariaDB's ColumnStore documentation you will eventually find the article labelled ColumnStore Storage Architecture which in the last paragraph Transaction Log mentions:

MariaDB ColumnStore supports logging committed transaction to the server's Binary Log.

Your Question

Is binary logging independent of the storage engine used?

Yes, following the documentation we have come full circle. Any changes to the data are logged; be it a normal storage engine or the new ColumnStore engine.


Oracle create pdb manager user I create user C##ADMIN in database for manage pdbs .

sqlplsqlplus sys/rootpass@172.17.0.2/orcl as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat May 26 15:17:09 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create user C##ADMIN identified by adminpass container=all ;

User created.

SQL> GRANT CREATE SESSION, SYSOPER,PDB_DBA,DBA, CREATE PLUGGABLE DATABASE TO c##admin CONTAINER=ALL ;

Grant succeeded.

SQL>

My problem is C##ADMIN user can not list pdbs !

sqlplus C##admin/adminpass@172.17.0.2/orcl

SQL*Plus: Release 12.1.0.2.0 Production on Sat May 26 15:20:14 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Sat May 26 2018 15:17:32 +04:30

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select pdb_name,status from cdb_pdbs;

no rows selected

SQL>

Which permission is forgotten?

How the Oracle Multitenant Option Affects Privileges

  A common user connected to the root can see metadata pertaining to
  PDBs by way of the container data objects (for example, multitenant
  container database (CDB) views and V$ views) in the root, provided
  that the common user has been granted privileges required to access
  these views and his CONTAINER_DATA attribute has been set to allow
  seeing data about various PDBs.

SQL> create user C##ADMIN identified by adminpass container=all ;

User created.

SQL> GRANT CREATE SESSION, SYSOPER,PDB_DBA,DBA, CREATE PLUGGABLE DATABASE TO c##admin CONTAINER=ALL ;

Grant succeeded.

SQL> conn C##ADMIN/adminpass
Connected.
SQL> select PDB_NAME from cdb_pdbs;

no rows selected

Granting the required privilege:

SQL> conn / as sysdba
Connected.
SQL> alter user c##admin set container_data=all for cdb_pdbs container=current;

User altered.

SQL> conn C##ADMIN/adminpass
Connected.
SQL> select PDB_NAME from cdb_pdbs;

PDB_NAME
---------
PDB$SEED
PDB1