If you want to recover by filetype, please note the following structure in MySql database file system:

A MySQL table is the combination of three files:

The FRM file is the table definition.

The MYD file is where the actual data is stored.

The MYI file is where the indexes created on the table are stored.

Source:
[1] https://stackoverflow.com/questions/484750/restoring-mysql-database-from-physical-files
What this does not provide are the views. I did search a while and could not find where they are.
Same source tells how to restore the database from files. Copy them to  /var/lib/mysql/ on machine that has same version of Mysql installed.
That is needed to get more complex structures out of the files, and if the file sizes don't limit you, it would be also the most convenient way to do this.
Note you should close the server from running before changes in filesystem.


Postgresql one master multiple slaves failover

I manage to solve it with dezso's help.

I used cascading replication for this. Current architecture is like M -> S1 -> S2.

Master: 10.10.10.146,
S1: 10.10.10.130,
s2: 10:10:10:160,

First of all I created repuser in all three systems with below command.

sudo -u postgres createuser -U postgres repuser -P -c 5 --replication

It will ask you to assign a password, type anthing you like and repuser will be created. We will use this user for replication related operations.

On Master server current configuration is:

postgresql.conf

listen_addresses = 'localhost, 10,10,10,146'
wal_level = hot_standby
archive_mode = on
archive_command = 'test ! -f /here i wrote full directory starting from root/%f && cp %p /here i wrote full directory starting from root/%f'
max_wal_senders = 3

pg_hba.conf

# Allow replication connections
host     replication     repuser         10.10.10.130/32        md5
host     replication     repuser         10.10.10.160/32        md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             0.0.0.0/0               md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

After this configurations restart master server.

Now we need to get full backup from master to S1. First stop the postgresql service on S1. Then remove the data directory (directory is var/lib/pgsql/9.5/data for my system). After removing run full backup command:

sudo -u postgres pg_basebackup -h 10.10.10.146 -D /var/lib/pgsql/9.5/data -U repuser -v -P --xlog-method=stream

This will copy the data folder to S1 server. Now open postgresql.conf in S1, and make below changes

listen_addresses = 'localhost, 10,10,10,130'
wal_level = hot_standby
archive_mode = on
archive_command = 'test ! -f /full directory starting from root/%f && cp %p /full directory starting from root/%f'
max_wal_senders = 3
hot standby=on

Open pg_hba.conf and make below changes:

# Allow replication connections
host     replication     repuser         10.10.10.160/32        md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             0.0.0.0/0               md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

And finally create a recovery.conf file under data folder. recovery.conf should be like:

standby_mode=on
primary_conninfo='host=10.10.10.146 port=5432 user=repuser password=password_of_repuser'
trigger_file='/full directory starting from root/any_folder_name'

For example if you write trigger_file = '/tmp/trigger_failover' in recovery.conf file, then create a folder under /tmp directory named trigger_failover, S1 will start failover process and will start to serve as master server.

After above configurations start S1 server and now M and S1 should work as master and slave. Next we need to configure S2 server. Steps are pretty much same as configuring S1.

First stop the postgresql service on S2. Then remove the data directory as we did in S1. In my case S2 has same folder architecture  with S1, so data folder is also present in /var/lib/pgsql/9.5/data directory. Now run below command this time we will get full backup from S1 server.

sudo -u postgres pg_basebackup -h 10.10.10.30 -D /var/lib/pgsql/9.5/data -U repuser -v -P --xlog-method=stream

No we will change configuration files in S2. In postgresql.conf make below changes:

listen_addresses = 'localhost, 10,10,10,160'
wal_level = hot_standby
archive_mode = on
archive_command = 'test ! -f /full directory starting from root/%f && cp %p /full directory starting from root/%f'
max_wal_senders = 3
hot standby=on

And in recovery.conf make below changes:

standby_mode=on
primary_conninfo='host=10.10.10.160 port=5432 user=repuser password=password_of_repuser'
recovery_target_timeline='latest'

And you are finished. Now start postgresql service in S2 and see replication mechanism work in M, S1 and S2.

By this architecture:

if M goes down, you can trigger S1 and make it work as new master and
S1 will have replication with S2.
If S1 goes down you can easily point S2 to M just changing
primary_conninfo command in recovery.conf file in S2 and you will have
replication with M and S2.
And finally if S2 goes down M will still have replication with S1.


Surrogate key / Primary key: Better to use an existing unique data field or create a key field?

You should never assume that a data point which is outside of the control of your system will never change.  This means you shouldn't assume student names won't change.  There are lots of reasons in the real world why names might change.  Anything that is at reasonable risk of changing is a bad candidate for a primary key.  Also, names are very unlikely to be unique over a student population of any reasonable size.

Some exceptions to this might be things which are controlled by an external standards body which can be reasonably well trusted to maintain consistency.  This could include something the the IATA code for an airport or the symbol for an atomic element.

Regarding the efficiency of textual (natural) keys vs. integer (surrogate) keys, there is no easy answer to this as it depends on many factors.  On balance, it is fair to say that surrogate integer keys are more efficient than natural textual keys - especially if your textual values are much larger than a few characters.

Nevertheless, there are advantages to natural keys beyond raw file I/O and CPU cycles, as long as you can trust the natural keys to be stable.  A school's student ID is probably a pretty good candidate because you can institute an internal policy that says student IDs are granted for life and never change.


Syncing Database to Application (polling)

Polling might be an appropriate design for what you're doing here. It lets you control and tell the user exactly how fresh/stale the state is though 3 seconds is probably too frequent. 15 seconds seems to be a common threshold used by a lot of commercial applications and with Microsoft's tools (E.g. performance monitor). Some of SQL Server's system threads waked up every 15 seconds also.

That said, you can consider using query notifications (https://technet.microsoft.com/en-us/library/ms175110(v=sql.100).aspx) if you want a way to notify the app when the data you care about has changed. Essentially, you tell SQL Server the query you want to track and when the results for that query changes, your app gets a message. It is a bit of work to implement but the concept and coding are relatively simple.


Convert a date range to an interval description

This answer shows an implementation using a SQL Server (2005+) CLR function.

-- Enable CLR (if necessary)
EXECUTE sys.sp_configure
    @configname = 'clr enabled',
    @configvalue = 1;

RECONFIGURE;

Assembly and function

CREATE ASSEMBLY DBA
AUTHORIZATION dbo
FROM [encoded data, 10242 characters]
WITH PERMISSION_SET = SAFE;
GO
CREATE FUNCTION dbo.IntervalDescription
(
    @From date,
    @To date
)
RETURNS nvarchar(100)
AS EXTERNAL NAME
    DBA.UserDefinedFunctions.IntervalDescription;

Usage

SELECT
    TD.FromDate,
    TD.ToDate,
    TD.ExpectedResult,
    IntervalDescription = dbo.IntervalDescription(TD.FromDate, TD.ToDate)
FROM dbo.TestData AS TD;

Result

Source

I am not a C# programmer!

using Microsoft.SqlServer.Server;
using System;
using System.Text;

public partial class UserDefinedFunctions
{
    [SqlFunction
        (
        DataAccess = DataAccessKind.None,
        SystemDataAccess = SystemDataAccessKind.None,
        IsDeterministic = true,
        IsPrecise = true,
        Name = "IntervalDescription"
        )
    ]
    [return: SqlFacet(IsFixedLength = false, IsNullable = false, MaxSize = 100)]
    public static string IntervalDescription(DateTime From, DateTime To)
    {
        var workDate = From;
        int years = To.Year - From.Year;
        int months = 0;
        int days = 0;

        if (years != 0)
        {
            if (From.Month > To.Month || (From.Month == To.Month && From.Day > To.Day))
            {
                years--;
            }
            workDate = workDate.AddYears(years);
        }

        while (workDate < To && (workDate.Year != DateTime.MaxValue.Year || workDate.Month != DateTime.MaxValue.Month))
        {
            if (workDate.AddMonths(1) <= To)
            {
                months++;
                workDate = workDate.AddMonths(1);
            }
            else
            {
                break;
            }
        }

        while (workDate < To)
        {
            days++;
            workDate = workDate.AddDays(1);
        }

        StringBuilder sb = new StringBuilder(100);

        if (years > 0)
        {
            sb.Append(years);
            sb.Append(years == 1 ? " year" : " years");
            sb.Append((months > 0 || days > 0) ? ", " : string.Empty);
        }

        if (months > 0)
        {
            sb.Append(months);
            sb.Append(months == 1 ? " month" : " months");
            sb.Append(days > 0 ? ", " : string.Empty);
        }

        if (days > 0 || (years == 0 && months == 0))
        {
            sb.Append(days);
            sb.Append(days == 1 ? " day" : " days");
        }

        return
            sb.ToString();

    }
}


Changing the system date or the system time does not change values stored in tables.

Existing values can be changed using an UPDATE statement, though.

Datetime and timestamp columns behave differently on retrieval.

create table tz_test (
  ts timestamp not null,
  dt datetime not null
);

insert into tz_test values (current_timestamp, current_timestamp);

select * from tz_test;

ts                   dt
--
2013-06-27 09:46:14  2013-06-27 09:46:14

Set a session timezone, and compare.

set time_zone = '-8:00';
select * from tz_test;

ts                   dt
--
2013-06-27 05:46:14  2013-06-27 09:46:14
           ^^

Timezone support in MySQL

Docs show you how to change both client and server timezones for testing. You have to read that.


When considering the impact of new index, the important things to think are how often rows are added to the table, is the indexed field getting updated (often) and how many distinct values there are in that field (selectivity).

For table this size, it's probably not going improve the performance that much, but if you're getting deadlocks it might help.

If you create the index, after it you can monitor the impact from "Index Usage Statistics" -report in SSMS:

http://blog.sqlauthority.com/2014/09/19/sql-server-ssms-index-usage-statistics-physical-statistics-user-statistics/

From there you can see the number of times it's being used (scans & seeks) and the number of times it was needed to update.


Can I use SQL Server Management Studio 2012 on a 2008 server?

SSMS2012 is backwards compatible:

http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/0151c2e8-2281-4a78-86b0-20cc1bfd57ac

Edit
Giving out Aaron's link instead of the old one! Thanks Aaron.

microsoft.com/en-us/download/details.aspx?id=35579

As Kenneth mentioned, you could copy the 2008 BOL (Books online) so when you search the help it'll be relevant to 2008 instead of 2012.


How to return different statements in where clause ? (oracle)

Just use grouped WHERE conditions:

WHERE COLUMN1<= Y
   AND ((ID < X AND COLUMN1<COLUMN2)
        OR(ID >= X AND COLUMN2>COLUMN3))
AND COLUMN 3 = XYZ;

Remember you really just want to get some boolean evaluations back here - as long as you can reduce your requirements to distinct sets of conditions you can put it into WHERE which will be plenty fast.

The time to use CASE is if you have overlapping criteria that you need to short circuit.


Sorting multiple columns in a full outer join, possibly containing NULLs

Please note: The query below doesn't take into account partitioning by Session to keep it simple.

The main idea is to split the data set into two groups and process them separately. History goes first. I decided to make History more important than Steps. If some steps are swapped, they will be presented in the history order.

So, the first group is all rows from History plus possible details from the corresponding Step. This is a simple LEFT JOIN.

The second group is omitted Steps. Another LEFT JOIN with a filter.

If you run the first CTE_History ordering by timestamp you'll see that unplanned events have NULL Position, but they will be in correct position in the result set, because of their timestamp. All we need to do is fill in these blanks.

+-------------+---------------------+----------+------------------------+-----------+
| NewPosition |         ts          | Position |       NewMessage       |  Comment  |
+-------------+---------------------+----------+------------------------+-----------+
|           0 | 2017-01-01 00:01:00 | NULL     | Chef entered kitchen   | unplanned |
|           2 | 2017-01-01 00:02:00 | 2        | Chef peeled vegetables | planned   |
|           3 | 2017-01-01 00:03:00 | 3        | Chef cut meat          | planned   |
|           3 | 2017-01-01 00:04:00 | NULL     | Chef picked his nose   | unplanned |
|           4 | 2017-01-01 00:05:00 | 4        | Chef turned on stove   | planned   |
|           5 | 2017-01-01 00:06:00 | 5        | Chef started cooking   | planned   |
|           5 | 2017-01-01 00:10:00 | NULL     | Chef left kitchen      | unplanned |
|           5 | 2017-01-01 01:00:00 | NULL     | FIRE!                  | unplanned |
+-------------+---------------------+----------+------------------------+-----------+

One way to do it is to take the last non-null value from the previous rows. Unfortunately, SQL Server doesn't implement IGNORE NULLS clause in the LAST_VALUE function, so we have to use a workaround with MAX and rely on the fact that Position grows as ts grows. In a more general case it will be a bit more complicated, as Itzik Ben-Gan showed in his The Last non NULL Puzzle.

The very first NULL is changed to 0 (or some number that is less than any possible Position).

Once we have values for all Positions, it is trivial to union this result with all omitted steps and order them.

In this approach all omitted steps go after unplanned steps in case of ambiguity, so Turn off stove will appear last. If you choose to calculate MIN of following rows instead of MAX of previous, this rule will be swapped.

Sample data

DECLARE @RecipeSteps TABLE (Id int, RecipeId int, Position int, Message nvarchar(255));
INSERT INTO @RecipeSteps (Id, RecipeId, Position, Message) VALUES
(50, 123, 1, 'Wash vegetables '),
(51, 123, 2, 'Peel vegetables '), --
(52, 123, 3, 'Cut meat        '), --
(53, 123, 4, 'Turn on stove   '), --
(54, 123, 5, 'Cook and stir it'), --
(55, 123, 6, 'Turn off stove  ');

DECLARE @History TABLE (Id int, ts datetime2(0), Session int, StepId int, Message nvarchar(255));
INSERT INTO @History (Id, ts, Session, StepId, Message) VALUES
(90, '2017-01-01 00:01:00', 321, NULL, 'Chef entered kitchen  '),
(91, '2017-01-01 00:02:00', 321,   51, 'Chef peeled vegetables'),
(92, '2017-01-01 00:03:00', 321,   52, 'Chef cut meat         '),
(93, '2017-01-01 00:04:00', 321, NULL, 'Chef picked his nose  '),
(94, '2017-01-01 00:05:00', 321,   53, 'Chef turned on stove  '),
(95, '2017-01-01 00:06:00', 321,   54, 'Chef started cooking  '),
(96, '2017-01-01 00:10:00', 321, NULL, 'Chef left kitchen     '),
(97, '2017-01-01 01:00:00', 321, NULL, 'FIRE!                 ');

Query

WITH
CTE_History
AS
(
    SELECT
        ISNULL(
            MAX(Position)
            OVER (ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
            , 0) AS NewPosition
        ,H.ts
        ,S.Position
        ,H.Message AS NewMessage
        ,CASE WHEN S.Id IS NULL THEN 'unplanned' ELSE 'planned' END AS Comment
    FROM
        @History AS H
        LEFT JOIN @RecipeSteps AS S ON S.Id = H.StepId
)
,CTE_OmittedSteps
AS
(
    SELECT
        Position AS NewPosition
        ,H.ts
        ,S.Position
        ,S.Message AS NewMessage
        ,'omitted' AS Comment
    FROM
        @RecipeSteps AS S
        LEFT JOIN @History AS H ON S.Id = H.StepId
    WHERE
        H.Id IS NULL
)
SELECT * FROM CTE_History
UNION ALL
SELECT * FROM CTE_OmittedSteps
ORDER BY NewPosition, ts;

Result

+-------------+---------------------+----------+------------------------+-----------+
| NewPosition |         ts          | Position |       NewMessage       |  Comment  |
+-------------+---------------------+----------+------------------------+-----------+
|           0 | 2017-01-01 00:01:00 | NULL     | Chef entered kitchen   | unplanned |
|           1 | NULL                | 1        | Wash vegetables        | omitted   |
|           2 | 2017-01-01 00:02:00 | 2        | Chef peeled vegetables | planned   |
|           3 | 2017-01-01 00:03:00 | 3        | Chef cut meat          | planned   |
|           3 | 2017-01-01 00:04:00 | NULL     | Chef picked his nose   | unplanned |
|           4 | 2017-01-01 00:05:00 | 4        | Chef turned on stove   | planned   |
|           5 | 2017-01-01 00:06:00 | 5        | Chef started cooking   | planned   |
|           5 | 2017-01-01 00:10:00 | NULL     | Chef left kitchen      | unplanned |
|           5 | 2017-01-01 01:00:00 | NULL     | FIRE!                  | unplanned |
|           6 | NULL                | 6        | Turn off stove         | omitted   |
+-------------+---------------------+----------+------------------------+-----------+

Query for SQL Server 2008

Here I replaced the window function MAX with a subquery that I put into OUTER APPLY. It produces the same result as above.

WITH
CTE_HistoryRaw
AS
(
    SELECT
        H.ts
        ,S.Position
        ,H.Message AS NewMessage
        ,CASE WHEN S.Id IS NULL THEN 'unplanned' ELSE 'planned' END AS Comment
    FROM
        @History AS H
        LEFT JOIN @RecipeSteps AS S ON S.Id = H.StepId
)
,CTE_History
AS
(
    SELECT
        ISNULL(A.NewPosition, 0) AS NewPosition
        ,ts
        ,Position
        ,NewMessage
        ,Comment
    FROM
        CTE_HistoryRaw AS Curr
        OUTER APPLY
        (
            SELECT TOP(1)
                Prev.Position AS NewPosition
            FROM CTE_HistoryRaw AS Prev
            WHERE
                Prev.ts <= Curr.ts
                AND Prev.Position IS NOT NULL
            ORDER BY Prev.ts DESC
        ) AS A
)
,CTE_OmittedSteps
AS
(
    SELECT
        Position AS NewPosition
        ,H.ts
        ,S.Position
        ,S.Message AS NewMessage
        ,'omitted' AS Comment
    FROM
        @RecipeSteps AS S
        LEFT JOIN @History AS H ON S.Id = H.StepId
    WHERE
        H.Id IS NULL
)
SELECT * FROM CTE_History
UNION ALL
SELECT * FROM CTE_OmittedSteps
ORDER BY NewPosition, ts;


SQL Agent doesn't have perms to run a PowerShell script

I had a similar problem when running powershell scripts from a sql server job.

  If you do not have remote PowerShell enabled on both servers and
  firewall access between the servers configured properly it will have
  issues.

Run a PowerShell script on a different server - from inside a SQL Server job

the way I have solved this problem is through a proxy account.

I have created a proxy account in sql server,
that had all the relevat permissions to the remote server, folders and files that I needed and run the sql job as that account.

a proxy account in my case was a domain account, and I have granted this account the privileges on the remote server.

this might help with proxy:
How to get the AD account associated to a Proxy?

From this link below you can see:

powershell problem when running on a remote server - access is denied

As far as workarounds, there is this answer on SU. you can read the article in detail that the answer points to fully understand, but this is the main command to adjust permissions:

  If you decide you want to allow others, what you do is run the
  command:

Set-PSSessionConfiguration -Name Microsoft.PowerShell -showSecurityDescriptorUI

  Notice that this action could have a serious impact on your system so
  we ask you to confirm that you really want to do this.


Really Small details about storage ( data size ) on columns

What you want to read up on is how SQL Server allocates pages (try this). Looking at data size in a row will take different tools.

Here's sample test code for a later version of SQL Server (I ran this on 2016)

USE [TestDB]
GO

DROP TABLE IF EXISTS dbo.Aliens
CREATE TABLE dbo.Aliens(ID INT)
INSERT dbo.Aliens VALUES (1)

SELECT *
FROM sys.dm_db_database_page_allocations(DB_ID(),OBJECT_ID('dbo.Aliens'),NULL,NULL,'DETAILED')

EXEC sp_spaceused 'dbo.Aliens'

This shows an IAM page and a data page for the table, and bunch of blank pages. Why the blank pages? SQL Server reserves an entire extent for the table (because I'm using 2016 - earlier versions behave differently), expecting to use those blank pages for later inserts. In 2008r2, your table starts with a single page in a mixed extent, but the rebuild pushes it to a uniform extent.

Here's code to use if you want to look at rows

DECLARE @pageid BIGINT,
@file BIGINT,
@db INT = (SELECT DB_ID())

SELECT TOP 1 @pageid = allocated_page_page_id, @file = allocated_page_file_id
FROM sys.dm_db_database_page_allocations(DB_ID(),OBJECT_ID('dbo.Aliens'),NULL,NULL,'DETAILED')
WHERE page_type = 1

DBCC TRACEON(3604)

DBCC PAGE(@db,@file,@pageid,3)


Should select queries ever take longer than 1 second?

Yes, you should be worried*. A simple query like this should be running in milliseconds and not examine a million rows.

My guess is that there are less than 10 rows that match the condition (evidence: Rows_sent: 3) and the lack of any index results in a full table scan (evidence: Rows_examined: 1092421). The explain output for the query would help us investigate if there is any other issue.

Good news is that a simple index on (dev_name) will probably solve the issue, for this query. Bad news is that you have to go through all the slow query, one by one, run explain on them, examine the tables and their indexes and either rewrite the queries or add appropriate indexes.

*: Of course, not because Google searches are fast. Any comparison between the super farms that a giant company uses and a single server is moot.


sp_prepexec (sp_execute) vs. sp_executeSQL

Are actual stored procedures the only mechanism that implements temp table caching or do system stored procedures such as sp_executeSQL / sp_execute also take advantage of them?

You need a real stored procedure (CREATE PROCEDURE) to benefit from temporary table caching. This includes temporary stored procedures (#procname).

  Point #3 on this blog post suggests that EXEC cannot use temp table caching, but leaves out whether sp_executeSQL can.

Notice that EXECUTE is used to run sp_executesql.

Testing: There are many ways to check whether caching is occurring. Some of them are listed in my original article referenced in the question, some more methods are shown in my follow-up post, Temporary Table Caching Explained, for example:

SELECT
    DOMCC.name,
    DOMCC.pages_kb,
    DOMCC.pages_in_use_kb,
    DOMCC.entries_count,
    DOMCC.entries_in_use_count
FROM sys.dm_os_memory_cache_counters AS DOMCC
WHERE DOMCC.[type] = N'CACHESTORE_TEMPTABLES';

Stored procedure input TVPs are also cached, and starting with SQL Server 2012, these can also be cached when used with sp_executesql. See the linked CSS blog post for details.


SSRS error "something went wrong please try again later" in web portal

After some digging I found plenty of unconnected issues, but finally managed to solve them and wanted to share them all in one place so nobody else waste that much time if hits the same issue (or some of them).

First you want to access is the Reporting Services Error Catalog, depending on your installation directory, it will be something like this:

?:\Program Files\Microsoft SQL Server Reporting Services\SSRS\LogFiles

As there will be multiple files I recommend you to stop the SSRS service (PS as admin):

Get-Service -Name SQLServerReportingServices | Stop-Service

Then remove all of the files there (still in the same PS session):

Remove-Item -Path ?:\Program Files\Microsoft SQL Server Reporting Services\SSRS\LogFiles -Force

This might fail for the ReportingServicesWMI_YYYY_MM_DD_HH_MM_SS.log file as it is in use, but there is no need to stop the WmiPrvSE.exe here, you can leave it as it is.

One you have your log space cleared you can start the SSRS service again:

Get-Service -Name SQLServerReportingServices | Start-Service

And access the Web Portal again. Go as deep as you need to access the error again and stop the SSRS service again. At this point you should be able to find your error message in one of the error log files.

As your can differ I will just list all of the fixes I had to apply:

Give Full Control to the service user I used to run reporting services over the:

?:\Program Files\Microsoft SQL Server Reporting Services\SSRS\ReportServer\RSTempFiles
Give SSRS MSSQL Login permission to View any database

USE [master]
 GO
 GRANT VIEW ANY DATABASE TO [DOMAIN\SSRS_MSSQL_Login] AS [sa]
Allow Log on as a batch job to SSRS service user:

RUN --> secpol.msc
Security Settings --> Local Policies --> User Rights Assignment --> Log on as a batch job
Add User or Group

Reference Material

Reporting Services Log Files and Sources (Microsoft | SQL Docs)


What are the pros and cons of this schema for flashcards app?

Flashcards are used to test knowledge in a topic.
Each card has a question and a correct answer.
One topic has many cards.
Each card may belong to more then one topic.
User chooses a topic to test in.
User may attempt the same test many times.
Each test has many cards (questions) from the chosen topic.
Each card may be part of many test from the chosen topic.

Note:
UserTopicAttempt is as sequence number (1,2,3 ...) for each (USerID,TopicID).


How should I build a DB to hold data about another database

Strongly recommend you don't do it. There clearly are either technical deficiencies in the current database/app or you have contractual deficiencies that prevent your company from getting the 3rd party to fix/enhance their app/DB. Building a helper DB quickly becomes unmaintainable and you end up with errors that can be hard to troubleshoot.

For example, consider your scenario above where the company changes its name. If you can't modify the primary database to create triggers on that table or modify the app/SP so that changes to company name in the primary DB always ensure propagation to the helper DB, you're almost guaranteed to get out of sync over time. Regardless of whatever procedures you put in place, humans fail. On top of that, you've also tightly coupled another database to the primary so if the helper is not online and operational, there needs to be some mechanism in place to "hold changes" and make sure they get applied later. You also need to know what operations should not be permitted on the primary because they depend on the helper.

Those are just some top level things to deal with, there are more.

If you still want/have to do it, design the table logically the way you would if it was in the same DB. Using your customer name scenario, you'd have the column you want to track changes/variations of so for your example it would be company_name. You'll also need the column that authoritatively identifies the customer. This might be companyID or some other identifier the app uses. If you are on SQL Server 2016, enable temporal table for this and you'll have system managed versioning. Else, you'll need to do your own versioning. You'll need a column to track versions. Date is probably most natural but not necessarily the best for performance if you have a very large table. I suspect it won't matter for your case (if the table becomes very large, this won't be your biggest problem). You'll also need to consider creating a composite primary key or some surrogate key. Again, assuming the table won't be huge, I'd go with composite PK but you know your system best so evaluate options and recommendations carefully - this is a contentious subject.

Once you have that, create your database + tables and make sure you have the right permissions setup and whatever you do to protect the primary database, do the same for this helper DB. Make sure your backup plans will get you the right level of logical consistency and you have a scripted way to reconcile when they get out of sync.

Populating the first time is easy, keeping it in sync is a bit trickier. Since you're not really just doing a 1-1 copy, things like replication won't work without some customization. Writing an ETL job to kick in regularly will do the trick and likely easier but what is the latency you can tolerate? Is it ok to have the helper DB 10 minutes behind the primary? 20? 30? Careful with the selection. Setting high frequency can cause interesting problems if a job gets blocked/delayed for whatever reason. Change tracking or change data capture are helpful here. They are not the same thing and picking the right technology really depends on your environment. At a very high level, if changes to the same row are very infrequent and if they occur, the same row would not be changed multiple times over a short period, change tracking might be a better solution - lighter weight. If the data may be changed repeatedly over a short period, consider change tracking - more overhead but you get full history to move to the helper.

A quick and dirty solution would be to write a trigger on the table so every time a value is changed, you would push that to the helper db but triggers are easy to mess up. It's easy to make a mistake and flood the database. Also, failures are often hard to troubleshoot. I wouldn't go this route.

Whatever you finally implement, be sure you test to ensure it works correctly for deleted companies. If you "blindly" propagate a delete to the helper DB, will that delete the latest or all occurrences? What is the right behavior for the business and are there legal requirements? How do you recover from an accidental delete?


How can I use Oracle's CONNECT BY PRIOR to only get specific revisions of hierarchical records?

how about:

with preresult as (
SELECT id,itemname,
   linkleft,
   linkright,
   create_timestamp,
   value1,
   value2,
   ROW_NUMBER ()
      OVER (PARTITION BY itemname ORDER BY create_timestamp DESC)
      rownumber
   FROM somedata
   WHERE create_timestamp < :timestamp)
select * from preresult where rownumber < 2

and enter as :timestamp the desired date and time

hth,
macfly.


Possible Inconsistency Among System Tables

USER$ is a table, DBA_USERS is a view.

USER$ contains user and roles while DBA_USERS selects only the users.


Difference between Physical Reads and Read-Ahead Reads

A query always reads data from memory (a logical read). Your example query scanning the TestLarge table touched 159,185 8KB memory pages during its execution.
During execution, SQL Server does two things.
1. It reads data from the pages that belong to the table.
If the required page is already in memory, a logical read is recorded.
If the required page is not in memory, a physical read is recorded.

The page is brought into memory from persistent storage.
The query is blocked until this read completes.
This happened 348 times to your test query.
A logical read is also counted when SQL Server processes the page (that is now in memory) to satisfy your query.

2. It issues read-ahead reads.
Every so often during the scanning operation, SQL Server spends a moment managing read-ahead:

SQL Server gathers a list of 8KB pages that may well be encountered by the current operation in the near future. You can think of this as the engine "looking ahead" of the current scan position for the pages that come next. It achieves using IAM (allocation map) pages or b-tree levels above the leaf, depending on the type of scan.
Any pages on this "look-ahead" list that are not already in memory are passed to the operating system in one or more asynchronous read requests. These are counted as read-ahead reads.
The operating system handles reading the pages into SQL Server memory, and notifies SQL Server when the reads are complete.
The SQL Server thread that issued the asynchronous reads is not blocked. It can continue scanning pages that are in memory while the operating system fetches read-ahead pages in the background, on a separate thread.
Your test query read 159,209 pages into memory via the read-ahead mechanism.

An analogy
Imagine there is a book. You are given the index only. The rest of the book is in the local library. The library has a rule that the whole book cannot be checked out, and only 50 pages at most can be taken from the library at each visit.
Your task is to assemble the book at home in the order pages are referenced in the index (a to z). You are not allowed to leave home, but you have a friend that can go to the library on your behalf.
The first entry in the index is for "aardvark", which appears on page 392 of the book.
You realize it will be very inefficient to do this task a page at a time, so instead of sending your friend to the library for page 392, you read 50 entries in index order, and give your friend that list of pages to take to the library. You count 50 read-ahead reads at this point.
Now you turn back to processing "aardvark". You don't have page 392 in front of you, so you have to wait, doing nothing, until your friend gets back. This is a physical read.
When your friend arrives you count a logical read when you process page 392.
You could start on the other 49 pages your friend brought back (counting a logical read for each one), but you realize it will be more efficient if you give your friend another list of pages to fetch from the library while you are busy with the work in front of you.
Each time you send your friend to the library with a list of pages to fetch, you count read-ahead reads. Each time you process a page in front of you, you count a logical read. If you find yourself without the next page you need (because your friend is too slow), you count a physical read.
The overall task completes quicker when you and your friend can overlap your activities effectively. They can be busy fetching pages you will need soon, while you are busy processing the pages you have in front of you. When this works well, you never have to wait for the next page you need, though you do spend a little time telling your friend what to do.


DbUp automatically takes the filenames in alphnumeric order, so prefix them with a version or timestamp.


Combining multiple queries into a single query

Yes, you can update the group in one go using an UPDATE statement like this:

UPDATE
  exp_channel_data
SET
  field_id_285 =
    CONCAT_WS(
      '\n',
      CASE WHEN field_id_151 <> '' THEN 'Thursday' END,
      CASE WHEN field_id_152 <> '' THEN 'Friday'   END,
      CASE WHEN field_id_153 <> '' THEN 'Saturday' END,
      CASE WHEN field_id_154 <> '' THEN 'Sunday'   END
    )
WHERE
  channel_id = 14
  AND (field_id_151 <> ''
    OR field_id_152 <> ''
    OR field_id_153 <> ''
    OR field_id_154 <> ''
  )
;

Each of the CASE expressions produces either a week day name or null. The CONCAT_WS function concatenates the names using \n as the delimiter, omitting the nulls, if any.

However, even though you can do this with a single statement, the fact remains that this is a terrible design. Without more information it is hard to suggest a proper alternative but it seems likely that a junction table could be used here instead.


postgresql GROUP BY and latest date filter by non group by statement?

The WHERE clause goes before the GROUP BY:

SELECT
    other_key,
    SUM(quantity) AS sum_quantity,
    MAX(date)     AS max_date
FROM demo
WHERE key = ?
GROUP BY other_key
ORDER BY max_date ;

By the way key is a reserved keyword in SQL - although it isn't in Postgres. It would be best to be avoided as a column or table name.

For the additional question, to also calculate the cumulative sums (order by date) and then find the (oldest) date that these sums went positive and stayed positive, it's easier done with some window functions:

SELECT
    other_key,
    total_sum_quantity, max_date,
    CASE WHEN cumulative_sum > 0 THEN cumulative_sum END AS cumulative_sum,
    CASE WHEN cumulative_sum > 0 THEN date END AS oldest_positive_strike_date
FROM
  ( SELECT
        *,
        ROW_NUMBER()
            OVER (PARTITION BY other_key
                  ORDER BY date DESC)  AS rn
    FROM
      ( SELECT
            other_key, quantity, date,
            SUM(quantity) OVER (PARTITION BY key, other_key) AS total_sum_quantity,
            MAX(date) OVER (PARTITION BY key, other_key)     AS max_date,
            SUM(quantity) OVER (PARTITION BY key, other_key
                                ORDER BY date)               AS cumulative_sum,
            LAG(quantity) OVER (PARTITION BY key, other_key
                                ORDER BY date)               AS prev_quantity
        FROM demo
        WHERE key = '0A19882'
      ) AS t
    WHERE (cumulative_sum  > 0 AND cumulative_sum-quantity <= 0)
       OR (cumulative_sum <= 0 AND cumulative_sum-quantity  > 0)
       OR (prev_quantity IS NULL)
  ) AS t2
WHERE rn = 1 ;

Test at rextester.com.

Some notes:

The cumulative_sum returned is the cumulative sum at the point of the oldest_positive_strike_date. If the total cumulative sum is not positive, both these columns will show NULL.
The PARTITION BY key, other_key can be replaced by PARTITION BY other_key. I left as it is, in case you need to run the query not just with one key value but with more, eg. for the whole table or with WHERE key IN (...).
The ORDER BY date will be deterministic if the (key, other_key, date) has a UNIQUE constraint/index. If there is a chance that you have two rows with same key, other_key and date, replace that with something that can identify a row, eg. ORDER BY date, id.
The "obvious" index that will benefit the query would be on (key, other_key, date, quantity). Postgres may choose a different plan though, scanning the table or using the index and also checking the values against the table. this depends on various factors. Experiment with various table sizes and with the work load you expect.
Since the initial WHERE key = ? condition will restrict the rows to about 100 (from a 100K table), it might be more efficient to use a CTE that gets these rows first, using something like the following. You may get away with a simple index on (key) and have good performance:

WITH a AS
  ( SELECT *
    FROM demo
    WHERE key = ?
  )
SELECT ... ;          --- the query as it is, without the `WHERE`


MySQL replication stopped for over 10 days, will replication work correctly when expire_logs_days=10?

The reason you were able to issue a single skip slave statement and it caught up, is because the SQL thread stopped, but the IO thread continues.

As long as you see:

Slave_IO_Running: Yes

when you run SHOW SLAVE STATUS, then the slave is able to continually pull the binary logs from the master and store them into the relay logs.

Regardless of expire_logs_days setting on the master and if the binlogs disappear, as long as they have been pulled and stored into the relay logs on the slave, they can be replayed.

One caveat being that on very busy servers, replaying over 10 days worth of relay logs would be much longer than doing a fresh restore.

Regarding the differences in your counts, you could be having other issues unrelated to this replication event. It is tough to know precisely without access to binlogs, schema and my.cnf.

Some rough debugging steps:

Do count of days prior to the replication event differ at all? Do other days after differ?
Use a tool to get differences in slave and master. I recommend pt-table-checksum, but be aware of the Limitation of servers running in RBR/MIXED binlog_format:

  pt-table-checksum requires statement-based replication, and it sets binlog_format=STATEMENT on the master, but due to a MySQL limitation replicas do not honor this change. Therefore, checksums will not replicate past any replicas using row-based replication that are masters for further replicas.

Debug the root cause of any differences found by the checksum process. This could be many reasons. A couple that come to mind is using statement-based replication in some situations, or different timezone settings on master and slave.
To resolve immediate differences, you can use pt-table-sync or restore a fresh copy of the master.


Join query with different column types in "on" part

You can use ADDTIME() function:

tableA AS a JOIN tableB AS b
  ON a.datetime_column = ADDTIME(CAST(b.date_column AS DATETIME), b.time_column)

This might use an index on tableA (datetime_column) but not an index on tableB. The reverse might use an index on tableB (date_column, time_column) but not on A:

tableA AS a JOIN tableB AS b
  ON  CAST(a.datetime_column AS DATE) = b.date_column
  AND CAST(a.datetime_column AS TIME) = b.time_column

It won't hurt testing both versions. If one table is much larger than the other, then prefer to have the larger table's columns exposed (not cast) so their index might be used.

If you move to MariaDB (any version 5.3+) or MySQL 5.7 (when it's released), you can define a VIRTUAL column (or two) in one of the two tables to hold this conversion/calculation that can be persisted and indexed.

In 5.5, if efficiency is not good, which is expected with large tables, you could add a computed column yourself but it would have to be populated during inserts and kept in sync during updates by you (e.g. using triggers).


How to use a column's latest date in query?

Perhaps you can split the query up to catch the DateTimes in separate variables

SELECT lastlogin INTO @LastLogin FROM smart_allusers_karen ORDER BY lastlogin DESC LIMIT 1;
SET @ThirtyDaysAgo = @LastLogin - INTERVAL 30 DAY;
SET @SixtyDaysAgo  = @LastLogin - INTERVAL 60 DAY;
SELECT
    smart_allusers_karen.lastlogin,
    smart_allusers_karen.supemail,
    smart_allusers_karen.regionname
FROM
    smart_allusers_karen
WHERE lastlogin < @ThirtyDaysAgo  AND
      lastlogin > @SixtyDaysAgo
ORDER BY lastlogin;

Please make sure lastlogin is indexed. If it is not, run this first:

ALTER TABLE smart_allusers_karen ADD INDEX (lastlogin);

Give it a Try !!!


As you can see you have RESOURCE role granted to the user user_kshitz. And you have to query the ROLE_SYS_PRIVS.

SQL> select * from role_sys_privs where role='RESOURCE';

ROLE                   PRIVILEGE                ADM
------------------------------ ---------------------------------------- ---
RESOURCE               CREATE TRIGGER               NO
RESOURCE               CREATE SEQUENCE              NO
RESOURCE               CREATE TYPE              NO
RESOURCE               CREATE PROCEDURE             NO
RESOURCE               CREATE CLUSTER               NO
RESOURCE               CREATE OPERATOR              NO
RESOURCE               CREATE INDEXTYPE             NO
RESOURCE               CREATE TABLE             NO

8 rows selected.

Also you can query the SESSION_PRIVS to find all the privileges granted to the user.

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

9 rows selected.


Is it normal for it to take up to a minute for an execution plan to be generated (even an estimated one) on simple queries?

To process a query of this form:

SELECT *
FROM Table1
WHERE IndexedIntField1 = 12345
    AND NonIndexedVarcharField IN ('Value1', 'Value2', 'Value3')

The basic choices are to scan the whole table or to seek on IndexedIntField1, and then perform lookups for each row to see if the other predicate obtains.  If there a lots of rows IndexedIntField1 = 12345 then the table scan will be much cheaper, and if there are very few then the index seek + bookmark lookup will be much cheaper.

If the statistics necessary to decide which plan to use don't exist or are out-of-date, then, by default, SQL Server will create or update the statistics before picking a query plan.

  The Query Optimizer creates statistics for single columns in query
  predicates when AUTO_CREATE_STATISTICS is on.

https://docs.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-ver15#CreateStatistics

and

  AUTO_UPDATE_STATISTICS { ON | OFF } ON Specifies that Query Optimizer
  updates statistics when they're used by a query and when they might be
  out-of-date.

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options?view=sql-server-ver15#auto_update_statistics

You can see the existing statistics and when they were updated like this:

select *, stats_date(s.object_id, s.stats_id) stats_date
from sys.stats s
where object_id = object_id('sales.salesorderdetail')


What's the difference between EXECUTE AS USER and EXECUTE AS LOGIN that makes the second one behave unexpectedly?

A member of sysadmin group is always mapped to dbo, even if it has an explicit user in the database. So it looks like the login WEB is member of sysadmin, hence USER_NAME() correctly returns dbo in the second case.


Is ALTER TABLE CHECK CONSTRAINT redundant?

The first statement alters the table to add the constraint.  The second statement enables or disables the constraint for future inserts or updates.

So, ALTER TABLE ... CHECK CONSTRAINT ... enables the constraint for future inserts/updates.  If you specify ALTER TABLE ... NOCHECK CONSTRAINT ..., that disables the constraint, thereby allowing future inserts and updates to succeed even if the column in the table referenced by the foreign key does not contain a matching value.  You can see the utility of having both statements present through this example:

USE tempdb;

IF OBJECT_ID(N'dbo.b', N'U') IS NOT NULL
DROP TABLE dbo.b;
IF OBJECT_ID(N'dbo.a', N'U') IS NOT NULL
DROP TABLE dbo.a;

CREATE TABLE dbo.a
(
    a_ID int NOT NULL
        CONSTRAINT a_primary_key
        PRIMARY KEY
        CLUSTERED
    , SomeData varchar(10) NOT NULL
) ON [PRIMARY];

INSERT INTO dbo.a (a_ID, SomeData)
VALUES (1, 'aaaa')
    , (2, 'bbbb')
    , (3, 'cccc');

CREATE TABLE dbo.b
(
    b_ID int NOT NULL
        CONSTRAINT b_primary_key
        PRIMARY KEY
        CLUSTERED
    , SomeData varchar(10) NOT NULL
    , a_ID int NOT NULL
) ON [PRIMARY];

INSERT INTO dbo.b (b_ID, SomeData, a_ID)
VALUES (49, 'dddd', 1)
    , (50, 'eeee', 2)
    , (51, 'ffff', 3);

So, we created two tables that do not have a defined relationship.  Later, we decide we want to design-in the relationship, so we add a foreign key in dbo.b that references the primary key column in dbo.a.  We want the existing rows to be checked for validity, but don't care about new rows that might get added in future1.

ALTER TABLE dbo.b
WITH CHECK
ADD CONSTRAINT b_a_foreign_key
FOREIGN KEY (a_ID)
REFERENCES a(a_ID);

ALTER TABLE dbo.b
NOCHECK CONSTRAINT b_a_foreign_key;

All good.  The existing rows were validated, and new rows won't be validated against the foreign key:

INSERT INTO dbo.b (b_ID, SomeData, a_ID)
VALUES (52, 'gggg', 4);

The table contents:

SELECT *
FROM dbo.b
    LEFT JOIN dbo.a ON b.a_ID = a.a_ID

Results:

╔══════╦══════════╦══════╦══════╦══════════╗
║ b_ID ║ SomeData ║ a_ID ║ a_ID ║ SomeData ║
╠══════╬══════════╬══════╬══════╬══════════╣
║   49 ║ dddd     ║    1 ║ 1    ║ aaaa     ║
║   50 ║ eeee     ║    2 ║ 2    ║ bbbb     ║
║   51 ║ ffff     ║    3 ║ 3    ║ cccc     ║
║   52 ║ gggg     ║    4 ║ NULL ║ NULL     ║  <-- crazy stuff
╚══════╩══════════╩══════╩══════╩══════════╝

So, since the code that scripts tables in SSMS has to cover every eventuality, it spits out a ALTER TABLE ... CHECK CONSTRAINT ... even if it's not required, and in the vast majority of cases is certainly not required.

1 - why anyone would want existing rows to be validated, but not care about new rows is a little mysterious, but there you go.


You can reduce the number of growth events by increasing the growth factor, but be careful. Log files cannot take advantage of instant file initialisation, so if you increase the growth factor too high you can cause delays during auto-growth events.

You also need to consider the number of VLFs that will be created based on the growth increment and the number of growth events. Check out this article by Kimberly Tripp for VLF info.

If your log file is constantly increasing in size, this growth ongoing so you should consider increasing your growth factor to reduce the number of events if they are impacting performance.

If your log file is being shrunk every day, this growth is repeating, meaning it wouldn't keep increasing the log file size each day if you grew the log to a reasonable size. Stop the process that shrinks your log file and grow the log file manually to a suitable size to mitigate the high-frequency log growth events.


Test connection for linked server fails on desktop but not on server

Checking Allow inprocess for the OraOLEDB.Oracle provider seems to have resolved the problem.


Try using CASE expressions as you can specify qualifiers like what you're describing.
something like...

Code :

Select Count (*)
CASE when date >= 1st August year X THEN enteredondate = currentyear/08/01
ELSE enteredondate= previousyear/08/01
End
from DateTable


How to figure out the latest version of a table in the 'recyclebin' section of PL-SQL developer

The DROPTIME and DROPSCN columns in the DBA_RECYCLEBIN view clearly show when the object was dropped. Stop relying on a simplified GUI.


Regularly updating table with huge amount of rows

It is possible for me to move the flag column to another table and do a complete rewrite of the score table. May this be more performant than updating only a selected amount of rows (~one third is updated each day)?

This is probably the way to go, if you can get away with batching the updates up to the daily level (that is, your "business logic" does not depend on seeing the updates closer to real time in order to get correct-enough answers).  Create the new table, then build the indexes, then in one transaction drop the old one and rename the new one into place.  This renaming into place can be a bit tricky if there are foreign keys, triggers, views, user defined functions which reference it, etc.  You don't even have to move the flag column to a separate table, if you make carrying the value of the flag over to the new table part of its creation process.

  Alternatively, is writing all changed rows to a separate, index-less table and updating the primary table in-database a preferable solution?

This is unlikely to solve the problem, unless you can drop the indexes while that update is in progress, or you have an off-peak window where you just don't care about performance.  Current versions of PostgreSQL do not have a provision for efficient bulk update of live btree indexes.  Your IO queue depth problem would be concentrated into a shorter period, would be more severe during that period.


Is it normal for tempdb to not give up disk space when it releases things?

Tempdb will release space when you restart SQL Server or when you shrink the files, refer to kb307487.  As was mentioned, it is usually a bad practice to use autoshrink.  If you are under space constraints because tempdb wasn't provisioned separate storage, you can shrink the data files using dbcc shrinkfile.  If you don't have to do that, I wouldn't.  It seems you have a workload that needs the space and it having the space already allocated prevents it from having to grow again.  Microsoft has some guidance on how to improve performance of tempdb.


RBS and Fail Over Cluster with SQL Server 2012

The setup is pretty strait forward. As the TechNet article below states you need to make sure the Windows Share Name is the same across all nodes.

http://technet.microsoft.com/en-us/library/cc645886.aspx


INSERT into three tables with many-to-many from one table

The immediate cause for the error message is your abuse of parentheses. But there is more:

Data-modifying CTEs are the wrong tool for the use case. The UPSERT (ON CONFLICT DO NOTHING) indicates that some of the fruits might already exist in table fruit (or its use would be nonsense). Hence, some of the fruits might not be included in the set returned with RETURNING.

Use simple, separate INSERT commands instead. If you need the whole operation to be atomic (all or nothing), wrap it into a transaction.

Also, name is not a good name for a column. I use fruit for fruits and country for countries instead. Avoids naming conflicts, and happens to simplify the query a bit by allowing the (totally optional) USING clause for the join condition.

Your tables can look like this: (If that was even part of the question?)

CREATE TABLE fruit (
   fruit_id serial PRIMARY KEY
 , fruit    text UNIQUE
);

CREATE TABLE country (
   country_id serial PRIMARY KEY
 , country    text UNIQUE
);

CREATE TABLE fruit_country (
   fruit_id   int REFERENCES fruit
 , country_id int REFERENCES country
 , PRIMARY KEY (fruit_id, country_id)
);

Detailed instructions:

How to implement a many-to-many relationship in PostgreSQL?

To INSERT data from everything:

BEGIN;

INSERT INTO fruit (fruit)
SELECT DISTINCT fruit              -- must be distinct!
FROM   everything
ON     CONFLICT DO NOTHING;

INSERT INTO country (country)
SELECT DISTINCT country            -- must be distinct!
FROM   everything
ON     CONFLICT DO NOTHING;        -- UPSERT here as well

INSERT INTO fruit_country(fruit_id, country_id)
SELECT f.fruit_id, c.country_id
FROM   everything
JOIN   fruit   f USING (fruit)     -- matching row guaranteed now
JOIN   country c USING (country);  -- matching row guaranteed now

COMMIT;


ibdata1 stores InnoDB dictionary, undo segment(s), and doublewrite  buffer. So, ibdata1 is needed regardless innodb_file_per_table setting.


Replication from Amazon's RDS to Postgres via DMS errors out

Solution: When I first create the replication via the DMS, and only then create the replication to the RDS, it works. BEWARE: in the errorsituation, the database CANNOT BE DELETED. There's a warning that you need sysadmin rights when you try. I had to drop and recreate the instance before i could implement the solution.


I'm afraid that there's no way that you can avoid using a UNION [ALL] in this case. Your timelapses, compares and archives tables are connected to users and to cameras but not to each other, so in order to link them, we have no choice but to use a UNION.
I played around a wee bit (with a subset of the fields) and I came up with this which may help (all the code below is in a fiddle here):
WITH core AS (
  SELECT
    'Archives' AS tab_name, a.id, a.exid, LEFT(a.title, 10) AS title, a.camera_id, a.requested_by
  FROM archives a
  UNION ALL
  SELECT
    'Compares', c.id, c.exid, RIGHT(name, 10), c.camera_id, c.requested_by
  FROM compares c
  UNION ALL
  SELECT 'Timelapse', t.id, t.exid, t.title, t.camera_id, t.user_id
  FROM timelapses t)
SELECT * FROM core;

Result:
tab_name    id  exid    title   camera_id   requested_by
Archives    1   concre-r6ys     Concrete P  1   1
Archives    2   201809-27xn     2018-09-26  1   1
Archives    3   201902-htu9     2019-02-01  4   1
...
...
...
Archives    9   spark-kyzrq     Sparks Bui  1   1
Archives    10  201611-luw8     2016-11-06  2   4
Compares    1   compa-ylxwctg   o 29th Oct  1   2
Compares    2   test-hroqe      test        2   3
...
...

So, we can see that in the combined data, we have a way of getting at the source table name - by selecting it as a TEXT string at the beginning.
You can add in users info (like email, firstname, lastname) and cameras by JOINING to this "core" - see fiddle.
You can search now by doing something like this (again, see fiddle):
...
... core CTE query
...
)
SELECT * FROM core
WHERE title LIKE '%Pond%';

Result:
tab_name    tab_id         exid     title      camera_id    requested_by
Timelapse        1  longp-mvxla     Long Ponds         1               2

So, now we know that the photo with the title "Long Ponds" is in the Timelapse table and it's id in that table is 1.
A few words of advice:

I would consolidate my (photograph) tables - this will make matters considerably easier. There should be some combination of fields that is UNIQUE - and you can keep the tab_name field (and use it to enforce uniqueness).

you have some field names (location, type...) as quoted identifiers. This should be avoided - use t_type... something with an underscore - which remains legible but isn't an SQL keyword.
If you decide to consolidate, you can have NULLs where there isn't a corresponding field in the other tables.

a note on performance: As you can see from the EXPLAIN (ANALYZE, BUFFERS) at the end of the fiddle, your UNION plan has more than twice as many operations as my more_info table - this is because I perform the JOIN on users and cameras only once for all the photos whereas you do it three times.
However you decide to do it - via sub-queries or CTEs, only doing it once is the better option. You could wrap your original UNIONs (without JOINs) in a SELECT and JOIN on that if you prefer... YMMV...

I know that this probably isn't the outcome you hoped for, but I hope this has helped a little - best of luck with your project - agus  slán go fóill a chara!


SQL Server - Company wants HA for single instance with 1 userDB - sanity check and questions

If your RTO is ~ 1 minute and your RPO is 1 day of data loss then you could use log shipping. This would not require AD, a Windows Cluster or Availability Groups.

If you shipped your logs every 15 minutes the recovery time needed to bring the secondary should be well under a minute (Needs testing but I would be surprised if it wasn't from what you have explained above).

The only thing you would need to deal with would be how the application manages the connection to the secondary when the primary is down. This could be handled in the application, a load balancer (with static routes that would need to be altered on failure of the primary) or a clustered name resource (would probably need to be manually failed over).

A FCI would mean you wouldn't need to handle and monitor all the log backup/copy/restores of log shipping and your RTO would be lower. This is not possible without AD. Also you can't have Availability Groups without a Windows cluster which also requires AD.


SSMS gives error if column name is "MERGE" where as other keywords works

This is yet another flaky bug in yet another inconsistent and unreliable visual designer in SSMS. My suggestions, in this order:

Stop using the visual designers. Learn CREATE TABLE and other DDL syntax - not only does this break your dependence on terrible tools, it is also much easier to place these in source control, send them to colleagues, etc. When you are relying on pointing and clicking in a UI, your documentation of what you did will have to resort to Camtasia or other screen recording tools.
Stop using reserved words for column names.
If you must use keywords, put the brackets around them yourself.

I filed a bug on Connect, but don't hold your breath. Many of these buggy components of SSMS have been barely touched in years, and many usability bugs continue to get ignored unless they cause real problems (like data loss).


How to define shardKey on GridFS collections to achieve Location/Data Centre affinity (MongoDB)

However, what about fs.chunks? will they follow around their "parent" fs.file record and get routed to the same shard as the "parent" fs.file?

The answer is no!

You could use files.aliases or files.metadata to store that REGION.

How ever maybe best solution (both chunks and files) is NOT to shard your GridFS. Create three different GridFS databases (APAC/EMEA/AMER) and then use command movePrimary to change "primary shard" to point right shard in your cluster. Then change your application so that it selects GridFS database base on the user's location. This, of course, works with all other collections what are REGION specific.


How to alter column from float(8) to numeric(64,8)

ALTER TABLE your_table
   ALTER COLUMN the_float_column TYPE numeric(64,8);

More details in the manual: http://www.postgresql.org/docs/current/static/sql-altertable.html


You can try PIVOT, something like this:

DECLARE @t TABLE ( ID CHAR(2), Value CHAR(1) )

INSERT INTO @t
SELECT *
FROM (
VALUES
( 'AA', 'A' ),
( 'BB', 'B' ),
( 'CC', 'C' ),
( 'AA', 'D' ),
( 'BB', 'E' ),
( 'CC', 'F' ),
( 'AA', 'G' ),
( 'BB', 'H' ),
( 'CC', 'I' )
)   x ( y, z )

SELECT *
FROM
    (
    SELECT *, ROW_NUMBER() OVER( PARTITION BY ID ORDER BY [VALUE] ) rowId
    FROM @t
    ) x
PIVOT ( MAX( Value ) FOR rowId In ( [1], [2], [3] ) ) pvt

My results:


One to at most one relation or multiple null columns?

There is nothing wrong with nullable columns, even a lot of them, if that is what your data domain calls for.
On the other hand, the fact that you say these columns can be grouped logically implies to me that something else might be going on.
If they can be grouped logically because different sets of columns apply to different sets of rows, then you might have an entity-subtype situation.
Conversely, if columns can be grouped because they apply at different times, then you may have a normalization issue.  For example, if your columns are something like "January Sales", "February Sales", etc. these should be rows in a child table.
While there is nothing innately wrong with nullable columns, neither is there anything wrong with joining.  It's what RDBMS does for a living.
UPDATE:
Given additional information about the logical groups of columns:
There are two kinds of sub-typing that can be represented in a database using 1:1 relationships.  If the logical groups were mutually exclusive, then the parent entity could have what is known as a partitioning attribute that tells you which one of the subtypes is applicable.  However, without a partitioning attribute, it is possible to have zero, one or even multiple subtypes being applicable at the same time.
The same fundamental question applies then to what you do with this situation.
A good way to resolve it would be to look at the logical groups of columns.  Are the columns in logical group A the same as in logical group B - or are the totally different?  If they are different they might be best modeled in the single table with nullable fields.  If they are the same, then this might be a clue that they should be multiple child rows instead.
Another clue to look at is whether it makes sense that a logical group of columns could take on a life of its own and start attracting relationships from other tables.  If logical group B might sometime soon find itself with multiple child records from another table, then it might be a sign that it makes sense to promote that group to its own subtype style table.
One last thing to consider is physical implementation.  If a logical subgroup is very sparsely populated, you might be able to make a case for segregating these columns into another table to optimize physical storage.  This step shouldn't be done proactively.  This kind of optimization should be done when performance testing proves it is necessary.
If none of these things are true, then you are probably best off leaving the nullable columns in the original table.


Upgrade SQL Server 2000 database to 2008 R2 and enable new features

The foremost step to do is to run the Upgrade Advisor on SQL Server 2000 database and address all the issues reported by it.

As a best practice, use the Upgrade Advisor tool on your SQL Server 2000 legacy database and import a trace file to the Upgrade Advisor tool for analysis. The trace file lets the Upgrade Advisor detect issues that might not show up in a simple scan of the database, such as TSQL embedded in applications.  You can capture traces of TSQL using SQL Profiler on your SQL Server 2000 server during typical hours and analyze these traces using the Upgrade
Advisor.

So the rest of the steps would be :

On the day of migration :

script our logins on 2000 server using sp_help_revlogin.
Script out jobs and linked servers from sql 2000 server.
stop webservers connecting to 2000 server. Make sure that no applications are connecting to the 2000 server.
backup your databases and restore on destination sql 2008 R2 server.(note: Do not detach/attach as things could go wrong and you will end up with detached database and no backups !)
Once your backups are restored on the 2008 R2 server, run the output from sp_help_revlogin on 2008 R2 server to recreate logins.
Sync up orphan users (If any) and recreate sql agent jobs and linked servers on the new server.
change compatibility level on the restored databases to 100.
Dbcc checkdb with all_errormsgs  and data_purity options turned on : DBCC CHECKDB ('<db_name_goes_here>' ) WITH ALL_ERRORMSGS,NO_INFOMSGS, DATA_PURITY
run DBCC UPDATEUSAGE on the restored databases DBCC UPDATEUSAGE('database_name') WITH COUNT_ROWS
Update statistics on all tables with full scan : Update Statistics table_name with FULLSCAN
Optional : Check the fragmentation levels and depending on the fragmentation level, run a reorg/rebuild of all Indexes. You can use Ola's scripts.
Recompile all SP's using sp_recompile 'procedureName'
Refresh your views SP_REFRESHVIEW view_name
make sure to change the database option : page verify to CHECKSUM.
Change the recovery model (if different from sql 2000) to FULL. If you change to FULL recovery model, then MAKE SURE you do Transaction Log backups frequently. This will help you to recover point-in-time as well as not bloat your T-Log.
In SQL Server 2005 and up, Database Mail was introduced. So you have to migrate from SQLMail to Database Mail.

USE [master]
GO
sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'Database Mail XPs',1
GO
RECONFIGURE
GO

Also, if you have any replication, then you have to reset it up. If any DR like logshipping or Mirroring (new in 2005 and up, but depreciated in 2012) then you have to reset it up as well.

Old DTS packages needs to be migrated to SSIS using C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTSMigrationWizard.exe (command line) or using Package Migration Wizard.

Also, you can use my script found at https://dba.stackexchange.com/a/36701/8783. Though, it uses detach/attach method, I highly recommend you to use BACKUP/RESTORE method. Change the script accordingly.

As a side note:

turn on Instant File Initialization on the new server.
Have multiple tempdb data files with equal size.
Enable Trace Flag 1118
Configure max and min memory correctly. Especially Max memory away from default.
Properly adjust the MAXDOP settings. Refer to https://dba.stackexchange.com/a/36578/8783 for more details.
Best is to install sp_Blitz from Brent Ozar.  Run it and address the critical and high priority issues reported by it.
You can even use SQL Power Doc from kendalvandyke - SQL Power Doc works with all versions of SQL Server from SQL Server 2000 through 2012, and all versions of Windows Server and consumer Windows Operating Systems from Windows 2000 and Windows XP through Windows Server 2012 and Windows 8. Also useful for Planning upgrades - see what hidden features are in use on an instance.
Enable Optimize for ad-hoc workloads and Default backup compression options.

Lets address your questions ...

  What else should I do to make migration complete?

Refer to my answer. It will help you properly come up with a migration plan. Always test your migration plan in a UAT (non production) along with proper application testing by business users.

  use new features like checksumming and full recovery model.

CHECKSUM is new in SQL Server 2005 and up. I have covered it as a part of migration steps described above.

full recovery model is not new. It depends on your business type and dictates on how much data you can lose in case of disaster.

Full recovery mode with frequent Transaction log backups will allow you to restore point-in-time and there by reducing the amount of data loss.

  make this database to be exactly as it was created in SQL Server 2008 R2.

  make this database to be fully compatible, correct and be perfect suited for new, SQL 2008 R2 database engine.

Don't fully understand this ! But above migration steps will help you. You just have to restore the database and change the compatibility level 10 100 along with above steps.

  I just want to know how to correctly and completely convert old SQL Server 2000 database to new 2008 R2 database, be calm that everything is done right and be happy with all new features.

You have to be careful with this, as this will require changes to your application code as well. If your application code is changed to use the new features in SQL Server 2008 R2, then you wont be encountering any issues - PROVIDED you have fully done a complete regression testing of your application in a UAT or DEV environment. This will give you best confidence when you do the actual migration in PROD.

Note: Above are steps that I could remember and I am pretty sure that nothing is left out. If I see that I have missed some stuff, then I will add it or other experts on this site -- feel free to add !

Everything that is outlined above needs to first replayed on a NON PRODUCTION environment to avoid any surprises during the actual migration.

----------

Few more questions:

  You recommend to use backup/restore method, but I did as written above, so can I encounter any problems now? Everything worked without any problem.

If everything worked fine and you were able to attach the database, then NO you wont be having any problems. Detach/Attach vs Backup/Restore is just a method on how you move your database/s to a different place. Just FYI .. Backup/Restore is more secure and reliable as if anything goes wrong (in worst cases) then atleast you have a backup to restore and recover your database/s.

  About checksumming and full recovery model: it wasn't available/enabled on SQL Server 2000, so I want to use them now. You said that the only thing I need to do is to enable those options in database properties? I've read somewhere, that it isn't enough and I should also rebuild indexes or something. I really don't know, I just asking.

As I said, checksum is new in version 2005 and up. It is a mechanism by which SQL Server will detect page corruption especially due to I/O. Refer to my answer here for more details.

To enable CHECKSUM as well as change the recovery model to FULL, you can do it using below T-SQL code :

USE master;
GO
ALTER DATABASE [your_database_name] -- change this !!
SET RECOVERY FULL, PAGE_VERIFY CHECKSUM;
GO

Note : Once you set the database options, it will be persisted when you do migration from 2008R2 to 2012.

  I'm preparing to migrate this database to SQL Server 2012 - so first it was from 2000 to 2008 R2, now it will be from 2008 R2 to 2012 (it was impossible to do this directly because of lack of support of 2000 databases in SQL Server 2012). So I understand that I should follow your guide: backup it in 2008 R2 and restore in 2012, then do the rest of your tips, right?

Yes please. As I said, backup restore is the preferred method, unless you have a good reason not to do it.

  Please explain me the backup/restore method: Is it like a dump of database to SQL queries and then restoring it by executing a bunch of queries? Will this method by the way "defragment" my database? If not, how to defragment/optimize it manually?

Backup/restore is ... similar to dump and load used in Sybase, Oracle or probably MySQL as well. Its just SQL Server calls it .. backup/restore.

A must read : Understanding SQL Server Backups by Paul Randall.

Simple Syntax(for Full syntax refer BOL) :

backup database database_name
to disk = 'D:\backup\database_name_full.bak'
with init, stats =10

Then restore can be done on destination server as :

-- assuming the destination's disk layout doesn't match the source server's

restore database database_name
from disk = 'D:\backup\database_name_full.bak'
move 'logical_data_fileName' to 'physical_path\database_name.mdf'
move 'logical_log_fileName' to 'physical_path\database_name_log.ldf'
with recovery, stats = 10

-- assuming the destination's disk layout matches the source server's

restore database database_name
from disk = 'D:\backup\database_name_full.bak'
with recovery, stats = 10

  Will this method by the way "defragment" my database? If not, how to defragment/optimize it manually?

backup/restore will not defragment your database. You have to use Alter Index Reorganize or Rebuild depending on your fragmentation level.

Since you are new to SQL Server I would highly recommend you to use Ola Hallengren's :

SQL Server Index and Statistics Maintenance - for defragmenting your Indexes
SQL Server Backup -- for taking care of your backups
SQL Server Integrity Check -- for checking database consistency of your databases

  As we were using SQL Server 2000 Express for years (no management interface), we were doing backups simply by stopping engine and RAR the DATA directory. For now, as we are on SQL Server 2008, isn't this still better than using backup function in Management Studio?

Stopping engine is the worse thing that you can do to do a backup !!

Read Paul's link about the backups that I mentioned and Use Ola's script. Microsoft has a KB article with the script to do automated backups - How to schedule and automate backups of SQL Server databases in SQL Server Express

  Full recovery mode with frequent Transaction log backups - Where is the Transaction log stored - is it the LDF file? How sould I backup it properly?

Every SQL Server database has a log that records all the transactions and database modifications made by each transaction. The transaction log is a critical component of any database.

The usual naming convention extension for Transaction log is '.LDF', but it can be any.

I am not going to write more on this as this will make the answer very leanthy. Refer to
Transaction Log Management and my answer here has excellent links as well.

EDIT : 8/24/2016 .. This will help future readers :

If you are migrating your entire instance from one version to another version, I would highly recommend to use PowerShell based solution Start-SqlMigration


MySQL: Reference aggregate function in conditional select

Cleaver.  But no go.

To achieve what you ask for would take two passes over the data.  Things don't work that way.

The first pass is to find the max for each group; the second pass is to check all the value against the maxes.

You can do it with a subquery, an @variable or several other ways.  Follow the tag to see ways to do "groupwise max".


Your design doesn't record "which user submitted which answers".

Should there not be a relationship between SUBMISSION and USER?  You have the attribute submission_user but you haven't indicated that it is a foreign key to USER.

Should there not be a link between SUBMISSION_ANSWER and ANSWER?  How else do you know which question is being answered and what the value of the answer is?

SUBMISSION_ANSWER.attribute_name seems like you left something incomplete.


I'd imagine that all of them are processed in a 'serial' way which may reduce performance compared to tables that doesn't need to access that number.

In theory, yes. In practice, you would have to measure the effect to know if it is important.

The current rowversion value is cached in memory and atomically updated using a cmpxchg instruction (in DBTABLE::GetNewDbTimestamp). Any collisions due to multiple sessions requesting a new rowversion value concurrently are handled by a spinlock (type DBTABLE in sys.dm_os_spinlock_stats). Monitoring backoffs and sleeps in that DMV is probably the first step to see if it might be causing a problem.

Compared with the extensive work needed to actually insert or update a row, maintaining the timestamp value is probably pretty trivial. The only way to really be sure though would be to test it on a suitable workload inserting or updating many tables with rowversion columns concurrently.

As a quick test, I ran two sessions inserting 500,000 rows with a single integer value into a table variable with a rowversion column. The batch was repeated 100 times on each connection for a total of 50,000,000 inserts each.

SET NOCOUNT ON;
DECLARE @T AS table (i integer, rv rowversion);
INSERT @T (i) SELECT N.n FROM dbo.Numbers AS N WHERE N.n BETWEEN 1 AND 500000;
GO 100

Typical results were that execution took 45 seconds with:

With a binary(8) column default 0x, the same test runs for 15 seconds.

So there is definitely a cost associated with rowversion. I do doubt this would often be a major cause of problems though. Maybe if a large number of tables has rowversion columns and were all being inserted/updated at a high rate concurrently.

I suppose if natively compiled memory optimized tables ever support rowversion, we might get closer to the time when this becomes a real bottleneck with non-durable tables, but maybe not even then. Identity and sequences use a similar mechanism, and I have not heard any reports about that becoming a problem specifically.

Some related reading:

Why does @@dbts increase after backup/restore?
Diagnosing and Resolving Spinlock Contention on SQL Server
sys.dm_os_spinlock_stats.backoffs keeps increasing with no sqlos.spinlock_backoff event


SQL 2016 Always Encrypted, options appear in one environment, not the other

I see this question is a few months old, but I wanted to tell you about a great update from Microsfot if you've missed the news.

On November 16, Microsoft announced the release of SQL Server 2016 SP1. Along with the normal things found in Service Pack releases, they also included CREATE OR REPLACE functionality. And, really important for your question, they've made quite a lot of the features that you can develop for available in editions other than Enterprise.

Most critical for you here is the availability of the Always Encrypted feature in SQL Server 2016 Standard and even Express starting in SP1 of SQL Server 2016.

You can read about that here on Microsoft's blog post.


SSRS not sending scheduled email to specific contact

This question shouldn't be posted here even if it is related to SSRS.

IMHO and according the comments it seems that the SMTP service used by SSRS is miss-configured.

Take a deep look at SPF record in your DNS to ensure it belongs to your domain. Some mail servers have an strong spam-filter activated to ensure all received mails are good and belonging to the mail sender domain. Among other spam-filters.

You can use some (online) tools to check your mail server configuration.


Is this a good database design or transition? Normalize to Denormalized for Data Warehousing

(slightly medium size as of now)

You should be more precise than that when talking about data sizes. Generic words like small/medium/large will mean different things to different people, though in this instance it probably isn't significant.

  In the future, we are planning to turn this database into a data warehouse.

You shouldn't turn an application database into a data warehouse - keep the application database optimised for normal live application use and have data fed from it into the warehouse database for offline analysis.

  however in data warehousing normalization in not exactly needed.

Correct. Often warehouse solutions are explicitly denormlised in various ways to make certain reports easier to produce efficiently (especially by automated tools and relatively unskilled users).

  like extracting the data from the normalized table and putting it into a new database with the denormalized table?

This is generally the way to go.

Keep your live data structures optimised for the applications main workflows and data correctness (i.e. keep things in normal form to avoid data inconsistencies due to design issues or other bugs), and build & update (or rebuild) your warehouse from those application databases using some form of ETL process. The live, normal form, data in the production databases is your source of truth from which you can rebuild the warehouse if something goes wrong. The warehouse should not live in one application instance's database because you may later want to combine data sourced from multiple applications (and/or multiple instances of the same application) in the warehouse, and the differing IO and locking requirements of live applications and large reporting tasks usually mean you want to keep those tasks separate and not competing for resources.

There are exceptions to this of course, as with everything. For instance you may want live reports in the application that you denormalise some data for, for instance, though in this case you would still have core application tables optimised for data consistency and the application's main activity, and the reporting structures built/updated/rebuilt from that source of truth.


Event replication on slave and Slave_IO_Running:NO

For non-LVM, I recommend doing

STOP SLAVE;  -- before taking the backup
START SLAVE;  -- after taking the backup

This both avoids the problem and avoids other issues.

What kind of backup is it?

LVM "snapshot" -- I recommend stopping mysqld completely.
mysqldump (or similar) -- be sure to add the parameter that says to dump stored routines.  (I think that will capture the EVENT.)


"ORA-03113: end-of-file on communication channel" on startup

After hours of misdirection from official Oracle support, I dove into this on my own and fixed it. I am documenting it here in case someone else has this problem.

To do any of this, you must be the oracle user:

$ su - oracle

Step 1: You need to look at the alert log. It isn't in /var/log as expected. You have to run an Oracle log reading program:

$ adrci
ADRCI: Release 11.2.0.1.0 - Production on Wed Sep 11 18:27:56 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
ADR base = "/u01/app/oracle"
adrci>

Notice the ADR base. That is not the install. You need to see the homes so you can connect to the one that you use.

adrci> show homes
ADR Homes:
diag/rdbms/cci/CCI
diag/tnslsnr/cci/listener
diag/tnslsnr/cci/start
diag/tnslsnr/cci/reload

CCI is the home. Set that.

adrci> set home diag/rdbms/cci/CCI
adrci>

Now, you can look at the alert logs. It would be very nice if they were in /var/log so you could easily parse the logs. Just stop wanting and deal with this interface. At least you can tail (and I hope you have a scrollback buffer):

adrci> show alert -tail 100

Scroll back until  you see errors. You want the FIRST error. Any errors after the first error are likely being caused by the first error. In my case, the first error was:

ORA-19815: WARNING: db_recovery_file_dest_size of 53687091200 bytes is 100.00% used, and has 0 remaining bytes available.

This is caused by transactions. Oracle is not designed to be used. If you do push a lot of data into it, it saves transaction logs. Those go into the recovery file area. Once that is full (50GB full in this case). Then, Oracle just dies. By design, if anything is messed up, Oracle will respond by shutting down.

There are two solutions, the proper one and the quick and dirty one. The quick and dirty one is to increase db_recovery_file_dest_size. First, exit adrci.

adrci> exit

Now, go into sqlplus without opening the database, just mounting it (you may be able to do this without mounting the database, but I mount it anyway).

$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 11 18:40:25 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> connect / as sysdba
Connected.
SQL> startup mount

Now, you can increase your current db_recovery_file_dest_size, increased to 75G in my case:

SQL> alter system set db_recovery_file_dest_size = 75G scope=both

Now, you can shutdown and startup again and that previous error should be gone.

The proper fix is to get rid of the recovery files. You do that using RMAN, not SQLPLUS or ADRCI.

$ rman
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Sep 11 18:45:11 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
RMAN> backup archivelog all delete input;

If you've got RMAN-06171: not connected to target database, than try to use rman target / instead of just rman

Wait a long time and your archivelog (that was using up all that space) will be gone. So, you can shutdown/startup your database and be back in business.


pg_restore fails on foreign key contraints

Yes there is.

Have you tried the pg_restore --disable-triggers??? yet

If that fails to work which would be odd

You can restore just the schema with the --schema flag,  then modify the table "drop the constraints" in question" then restore the table contents

couple of useful articles
Different Dumps

and my long ago written wiki article on backups


Purged more than 100,000 records in table but size of database has not reduced

When you're faced with a database being too large, for whatever reason (growth is more than what you expected, you're running out of disk space, you run out of buffer pool space in memory) there's a few questions you probably want to ask yourself before taking action:

What happened to cause the database to be too large?
Where exactly am I seeing problems?
What steps can I take now to alleviate my immediate space concern?
What steps do I need to take to keep this from happening again in the future?

A lot of times, growth is to be expected if people are using the system that adds data to the database. This is, after all, the purpose of a database. However, understanding the root cause will be key in addressing the issue long-term, so doing that work up front will save time later. This may mean you need to take a look at use patterns using DMVs or other mechanisms.

The next thing you'll want to determine is where you're seeing the problem. If you've separated out your data from log drives you should be able to easily tell where you're seeing growth. If it's in the log file, figure out how you can better address the log file growth (more frequent log file backups?). If it's the database, your options become a bit more plentiful. If it's the Temp db, figure out what is causing temp db growth.

Once you determine what caused the data growth, you can take steps to determine how to immediately deal with the problem and address it long term. Deleting data out of a database can be a pretty drastic step if you are running with logging on (and you should be). For one thing, the log file will grow because it has to keep information about the deletes in there, until you're able to back up the log file. For another thing, as you noticed, SQL Server won't automatically adjust the size of the files when you remove data. From a long-term point of view, you can assume that if the database ended up a certain size through normal usage it will get there again... so spending time shrinking and growing the files would be wasteful.

To immediately address the issue there are a couple of things you can do:

If you are absolutely sure the database won't just grow again, you can shrink your database. You won't want to do this because the database is probably going to grow again, and it's not a long term solution that helps address the actual problem.
You can enable Page or Row compression to make the data smaller, if you aren't experiencing CPU pressure. The general trade off is that while Row and Page compression reduce size on disk (and therefore memory usage) it takes CPU cycles to compress. You also need to make sure that new objects added to the database are compressed.
You can attempt to limit the amount of data being entered into the database.

By not only pinpointing the cause but determining a course of action for the long term, you'll be better prepared for the future and may even save yourself a panic.


SQL Server - Overlapping NC Indexes, Drop Question

When two indexes both satisfy a query, you'll find that the smaller of the two is selected. Often, this means the narrower index, but it also holds true with fully duplicated, identical indexes, with the physically smaller one being preferred.
Unless you're scanning the index and the full size matters, your index tuning Spidey Sense is right. Drop IX_1 and your queries will use IX_2. Those seeks will hardly notice.


Why are global temporary tables truncated when the connection is closed?

When connection is closed, the transaction is rolled back, that's why data is not preserved.
The rule of thumb for Oracle is "never use DDLs for business logic", you should never do any DDL during normal business run.

If you drop a TABLE (persistent or temporary) "all" the PL/SQL code gets invalidated and has to be re-compiled again.

GTT are called "global" because their definition are visible to all sessions.

UPDATE: GTTs are used sporadically in Oracle. Usually when you use a temporary table in MS SQL, you use a cursor in Oracle. IMHO in most cases you can avoid using them. GTTs are used for example as staging storage for some ETL loaders, so their stucture is predefined/fixed.


I think I would solve it this way:

Create a driving stored procedure that identifies if work needs to be done.  That could mean checking a table for the next folder month to process.  This driving table would be pre-populated with the number of folders (months) that need to be processed.  You might want to include a method to determine the order of the folders to process so that you process them in the correct order.
Set up a WHILE LOOP to check IF work is to be done. Use the custom stored procedure sp_sp_start_job_wait to invoke the job that has the 4 steps.  This procedure will start the job and wait for it to complete.
Upon successful completion of the job, mark as complete or delete the folder row for the month you just processed.
The WHILE LOOP re-iterates to check for more work to be done.

To start the entire process, simply invoke the driving stored procedure.

You could also eliminate the need for the SQL Agent jobs altogether and simply invoke the SSIS packages directly from the driving stored procedure I described earlier using built-in stored procedures; e.g. ssisdb.catalog.create_execution, ssisdb.catalog.start_execution.  There is a good blog post describing that process How To Execute an Integration Services (SSIS) Package from a SQL Server Stored Procedure.  Summarizing the main points from that post:

create procedure dbo.execute_ssis_package_sample
 @output_execution_id bigint output
as
begin
 declare @execution_id bigint
 exec ssisdb.catalog.create_execution
  @folder_name = 'mssqltips'
 ,@project_name = 'exec-ssis-stored-proc-ssis-sample'
 ,@package_name = 'Sample.dtsx'
 ,@execution_id = @execution_id output
 exec ssisdb.catalog.start_execution @execution_id
 set @output_execution_id = @execution_id
end

The stored procedure ssisdb.catalog.create_execution (new in SSIS
2012) is called to create an instance of an SSIS package execution.
You pass the folder, project and package to the
ssisdb.catalog.create_execution stored procedure.
ssisdb.catalog.create_execution returns an output parameter named
@execution_id; you need this value to start executing the SSIS
package as well as to check the status of package execution.
The ssisdb.catalog.start_execution stored procedure (new in SSIS
2012) is called to start the actual execution of the package.
The SSIS package execution is asynchronous; i.e. when you call the
ssisdb.catalog.start_execution stored procedure you are not waiting
for the package to complete.

I highlighted the fact that the default behavior of ssisdb.catalog.start_execution is to run asynchronous, but there is a workaround for that as documented in this post by Tim Mitchell.  See the section Execute Packages Synchronously. Quoting from that post:

  This one is a relatively easy fix. Although the default behavior is to
  execute [catalog].[start_execution] asynchronously, there is a system
  parameter that lets you override this behavior on a per-execution
  basis. The parameter name is SYNCHRONIZED, and it expects a value of
  either 0 (not synchronized, the default behavior) or 1 (synchronized).
  When overriding this value and setting it to 1, the call to
  [catalog].[start_execution] will not return until the package has
  completed its execution. This means that [catalog].[start_execution]
  will run as long as the package is still running. The syntax to add
  this parameter is shown below.

-- Create the execution object
DECLARE @execution_id BIGINT
EXEC [SSISDB].[catalog].[create_execution]
    @package_name = N'Package1.dtsx'
    , @project_name = N'Project1'
    , @folder_name = N'Folder1'
    , @use32bitruntime = False
    , @reference_id = NULL
    , @execution_id = @execution_id OUTPUT

-- System parameters
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
    @execution_id
    , @object_type = 50                     -- System parameter
    , @parameter_name = N'SYNCHRONIZED'
    , @parameter_value = 1

-- Execute the package
EXEC [SSISDB].[catalog].[start_execution] @execution_id

  As shown, parameter values (including system, project, and package
  parameters) are added after [catalog].[create_execution] and before
  [catalog].[start_execution]. In the above example I set the
  SYNCHRONIZED system parameter to 1, forcing
  [catalog].[start_execution] to wait until the package completes before
  it returns.

  This override solves one problem but leaves another: Even though
  [catalog].[start_execution] now runs synchronously, it does not report
  the status of the package execution. Most workflows need to know the
  status of the package execution, not just that the execution has
  finished. Fortunately, there is a workaround for that as well.

Force a Script Failure if the Package Fails

  This workaround requires more than a simple execution parameter, so it
  has more moving parts. In a nutshell, here is the design pattern for
  forcing the T-SQL script to fail in the event that the package it has
  called fails:

  Create the execution
  Set the SYNCHRONIZED parameter to 1
  Execute the package
  Check the execution log for this execution, and force the T-SQL    script to fail if the package failed

  The previous script already took care of steps 1-3, so we just need to
  add the final step. This can be as simple or as complicated as needed,
  but in my example I just check [catalog].[executions] and retrieve the
  [status] value for that execution; if the value does not equal 7 (the
  status ID for Success), the script will generate a failure using
  RAISERROR.

-- Create the execution object
DECLARE @execution_id BIGINT
EXEC [SSISDB].[catalog].[create_execution]
    @package_name = N'Package2.dtsx'
    , @project_name = N'Project1'
    , @folder_name = N'Folder1'
    , @use32bitruntime = False
    , @reference_id = NULL
    , @execution_id = @execution_id OUTPUT

-- System parameters
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
    @execution_id
    , @object_type = 50                     -- System parameter
    , @parameter_name = N'SYNCHRONIZED'
    , @parameter_value = 1

-- Execute the package
EXEC [SSISDB].[catalog].[start_execution] @execution_id

-- Check package status, and fail script if the package failed
IF 7 <> (SELECT [status] FROM [SSISDB].[catalog].[executions] WHERE execution_id = @execution_id)
RAISERROR('The package failed. Check the SSIS catalog logs for more information', 16, 1)


As far as I can guess, you have 'Primary Document' and 'Discovered Document' in different rows of REP_ASS, which means you'll need to join REP_ASS twice anyway... So here it goes:

select RPrimary.[TagName] as 'Tag Number',
       RPrimary.sourceName as 'Primary Document',
       RDiscovered.SourceName as 'Discoverd Document',
       RPrimary.SourceRevision
from
    (select  RT.[TagName],
            RA.SourceName,
            RA.SourceRevision
    from dbo.REP_TTA as RT
    join dbo.REP_ASS as RA on RA.TargetName=RT.TagName
    where RA.[AssocName]='is a document for'
    ) AS RPrimary
inner join --or outer if you don't have them all in both tables yet want them all
    (select RT.[TagName],
           RA.SourceName
    from dbo.REP_TransposedTagAttributes as RT
    join dbo.REP_ASS as RA on RA.TargetName=RT.TagName
    where RA.[AssociationName]='refers to'
    ) AS RDiscovered
on RPrimary.[TagName] = RDiscovered.[TagName]


How to Loadbalance a Postgres BDR Cluster

No it's bad idea to combine BDR with pgpool.

Instead this you can use HAProxy load balancer which have various ACL rules which can be configured for your BDR cluster.