There are various ways to build a report with that information. You could do this with PowerShell or TSQL using linked servers or SSIS.

There are sample scripts on the web and this forums site for pulling backup status for a server or given database. Just need to come up with method to pull it all together for a central report in your environment.

Update

It will depend on what you mean by daily backup status. I will also assume this is going to be a manual process, but for the most part I would generally follow the steps below, for starters:

Use PowerShell:

Build T-SQL query with desired dataset I wanted for a single server.
Create PowerShell script that has function to execute that query (use Invoke-SqlCmd or System.Data.SqlClient)
Export that data to CSV (e.g. $reportdata | Export-Csv -path MyFile.csv -Delimiter "|")
Import that CSV file into Excel

I wrote this function sometime back for an SSIS package to provide to a client as an example, but it should work in your case of just passing in the query to execute in order to get an Excel file. It works the same as if you went into Excel to the Data ribbon and selected "From text" to get external data:

function SqlTo-Excel {
    [cmdletbinding()]
    param (
        [string]$server,
        [string]$database,
        [string]$csvFile,
        [string]$excelFile,
        $query,
        [string]$delimiter = '|'
    )

    if (Test-Path $excelFile)
    {
        Remove-Item $excelFile -Force
    }
    if (Test-Path $csvFile)
    {
        Remove-Item $csvFile -Force
    }

    $sqlcn = New-Object System.Data.SqlClient.SqlConnection
    $sqlcn.ConnectionString = "Data Source=$($server);Database=$($database);Integrated Security=SSPI;"
    $sqlcn.Open()

    $sqlcmd = $sqlcn.CreateCommand()
    $sqlcmd.CommandText = $query
    $sqldata = $sqlcmd.ExecuteReader()
    $dt = New-Object System.Data.DataTable
    $dt.Load($sqldata)
    $dt | Export-Csv -Path $csvFile -NoClobber -NoTypeInformation -Delimiter "|"
    $sqlcn.Close()

    $xl = New-Object -ComObject "Excel.Application"
    $xl.Visible = $false
    $wb = $xl.Workbooks.Add()
    $ws = $wb.WorkSheets.Item(1)

    $txtConnector = ("TEXT;" + $csvFile)
    $connector = $ws.QUeryTables.Add($txtConnector,$ws.Range("A1"))
    $qTable = $ws.QueryTables.Item($connector.name)

    $qTable.TextFileOtherDelimiter = $delimiter

    $qTable.TextFileParseType = 1
    $qTable.TextFileColumnDataTypes = ,2 * $ws.Cells.Columns.Count
    $qTable.AdjustColumnWidth = 1

    $qTable.Refresh()
    $qTable.Delete()

    $wb.SaveAs($excelFile,51)
    $xl.Quit()

    [System.Runtime.InteropServices.Marshal]::ReleaseComObject($xl)
    [System.Runtime.InteropServices.Marshal]::ReleaseComObject($wb)
    [System.Runtime.InteropServices.Marshal]::ReleaseComObject($ws)

    Remove-Variable xl,wb,ws,qTable,connector,txtConnector,q,csvFile,excelFile
}

Use T-SQL

This could go anywhere from just using Registered Servers in SSMS to get the results for all servers at one time; or from a single server that can talk to them all creating linked servers to each one.

To automate a T-SQL option only, would require a bit more if you want to email it, but you could dump all the data into a single table and then BCP it out to Excel possibly. I am not much for using BCP when I have PowerShell to get it straight into the format I want.


ERROR: no schema has been selected to create in

This happens when not having USAGE privilege on any of the schemas of search_path. By default the pseudo-role public (all users) has this privilege on the publicschema, so this error happens only after revoking it explicitly with:

revoke usage on schema public from public;

This is required when it's not desirable that people peek into other people schemas, even without selecting data from tables (which is granted through different privileges).

If this REVOKE hasn't been done in that database, it may have happened in the template database by which new databases are modelled (see CREATE DATABASE).

When a user has USAGE privilege, but lacks CREATE privilege on the schema, it's a different error when trying to create an object: permission denied for schema public.

To check the privileges inside psql, use \dn+ public.

By default (shown with extended display \x for readability):

# \dn+ public
List of schemas
-[ RECORD 1 ]-----+-----------------------
Name              | public
Owner             | postgres
Access privileges | postgres=UC/postgres
                  | =UC/postgres
Description       | standard public schema

lack of a rolename before = means it's for all roles (=public)

Without public USAGE privilege

Name              | public
Owner             | postgres
Access privileges | postgres=UC/postgres
                  | =C/postgres
Description       | standard public schema

Without public USAGE or CREATE privileges

Name              | public
Owner             | postgres
Access privileges | postgres=UC/postgres
Description       | standard public schema


Fetch data if exist in distinct of another column

I could achieve the result you expected with this query:

;WITH CTE (CompanyName, Total)
AS
(
    SELECT companyName, COUNT(1)
    FROM TestData
    GROUP BY CompanyName
)
SELECT TD.CompanyName, TD.TaxPaidOn, TD.OtherInfo
FROM CTE
    INNER JOIN TestData AS TD
    ON CTE.CompanyName = TD.CompanyName
WHERE Total = (SELECT MAX(Total) FROM CTE)
ORDER BY TD.TaxPaidOn;

It doesn't have a DISTINCT though.


How is the default tablespace determined when creating a table?

When you create a new user, you can optionally specify the default tablespace and default temporary tablespace for any objects created by that user. For example:

CREATE USER phil IDENTIFIED BY l33t
       DEFAULT TABLESPACE philtablespace
       TEMPORARY TABLESPACE philtemp;

If you omit the clauses when creating the user, the user will inherit the database default values. These can be queried as follows:

SQL> select *
  2  from database_properties
  3  where property_name like 'DEFAULT%TABLESPACE';

PROPERTY_NAME                  PROPERTY_VALUE       DESCRIPTION
------------------------------ -------------------- ----------------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP                 Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE   USERS                Name of default permanent tablespace

SQL>

... and modified like so:

alter database default tablespace PHILTS;

alter database default temporary tablespace PHILTEMP;

To query the default values for a given user, use the following query:

SQL> select USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE
  2  from DBA_USERS
  3  where USERNAME='PHIL';

USERNAME                       DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
PHIL                           USERS                          TEMP

SQL>


Refer to renamed columns in WHERE clause

You should use a View in your database

USE mydb;
CREATE VIEW name_capita AS
SELECT name, gdp/population as capita,continent FROM world;

Then, apply the WHERE clause to the View

USE mydb;
SELECT * FROM name_capita
WHERE continent = 'Europe' AND
capita > (SELECT gdp/population FROM world WHERE name = 'United Kingdom');

or even re-using the view:

SELECT * FROM name_capita
WHERE continent = 'Europe' AND
capita > (SELECT capita FROM name_capita WHERE name = 'United Kingdom');

GIVE IT A TRY !!!


what's the optimal partition setup for a linux db server?

Most of the partitioning of data in SQL Server is done for performance, recover-ability (separating log/data files to different physical disk arrays), and then management of data.  For example, if you have different logical drive letter names, you could look at the IO metrics for them separately through your monitoring solution.

It looks like you only have 1 large RAID 10 with 1TB of total usable storage.  Correct?  If so, at the end of the day, it's not going to matter too much from an architecture point of view since your hands are tied to having everything on the same spindle.

Now as to how to setup the drives on the LinuxOS, it depends on the scope and scale of the servers lifetime.  I'll let your guys decide on that how that should look outside of the vanilla:

1- swap (RAM X2 or whatever you guys decide)
2- /boot (Usually small primary partition - ext3)
3- / (big data drive)
4- / (log drive)
5- / (Backup Drive)

I would advise on separating the data and log drives for more granular performance metric collection, and separating the backup drive so they don't balloon up to a point where your data files have no room to grow.  Make sure to pre-grow your files when installing.

Again this is highly subjective and many opinions might differ.  Implement what makes most sense to you.


You would probably want to use the general query log.

  The general query log is a general
  record of what mysqld is doing. The
  server writes information to this log
  when clients connect or disconnect,
  and it logs each SQL statement
  received from clients.

One important thing with logging for security is that an attacker cannot access the log to erase traces of their presence, so consider append-only files.

FWIW in Oracle we can send logs automatically to a remote syslog, but I don't believe MySQL has this feature yet. Perhaps you could fake it with SNMP but I have not tried it.


How to include empty string in string_agg?

You can use COALESCE to map null to an empty string for all attributes that may contain null:

string_agg( COALESCE(P.house_number, '')
         || ' '
         || COALESCE(P.street, '')
         ...
) AS commercial_properties


I ended up running this script in cron:

#!/usr/bin/php -q
<?php

/*
 * MySQL quota script
 * written by Sebastian Marsching
 * updated by Daniel Santos
 *
 */

/*
 *  This program is free software; you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation; either version 2 of the License, or
 *  (at your option) any later version.
 *
 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with this program; if not, write to the Free Software
 *  Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 */

/*
 * Create table for quota data with the following statement:
 *
 * CREATE TABLE `quota` (`db` CHAR(64) NOT NULL,
 * `limit` BIGINT NOT NULL,
 * `exceeded` ENUM('Y','N') DEFAULT 'N' NOT NULL,
 * PRIMARY KEY (`db`), UNIQUE (`db`));
 *
 * The field 'db' stores the information for which database
 * you want to limit the size.
 * The field 'limit' is the size limit in bytes.
 * The field 'exceeded' is only used internally and must be
 * initialized with 'N'.
 */

/*
 * Settings
 */

$mysql_host  = 'localhost';
$mysql_user  = 'root'; // Do NOT change, root-access is required
$mysql_pass  = '';
$mysql_db    = 'quotadb'; // Not the DB to check, but the db with the quota table
$mysql_table = 'quota';

/*
 * Do NOT change anything below
 */

$debug = 0;

// Connect to MySQL Server

$con = mysqli_connect($mysql_host, $mysql_user, $mysql_pass, $mysql_db);

if (mysqli_connect_errno()) {
    echo "Connection to MySQL-server failed!";
    exit;
}

// Check quota for each entry in quota table

$sql = "SELECT * FROM $mysql_table;";
$result = mysqli_query($con, $sql);

while ($row = mysqli_fetch_array($result)) {
    $quota_db = $row['db'];
    $quota_limit = $row['limit'];
    $quota_exceeded = ($row['exceeded']=='Y') ? 1 : 0;

    if ($debug) {
        echo "Checking quota for '$quota_db'...\n";
    }

    $qsql = "SHOW TABLE STATUS FROM $quota_db;";
    $qresult = mysqli_query($con, $qsql);

    if ($debug) {
        echo "SQL-query is \"$qsql\"\n";
    }

    $quota_size = 0;

    while ($qrow = mysqli_fetch_array($qresult)) {
        if ($debug) {
            echo "Result of query:\n";
            var_dump($qrow);
        }
        $quota_size += $qrow['Data_length'] + $qrow['Index_length'];
    }

    if ($debug) {
        echo "Size is $quota_size bytes, limit is $quota_limit bytes\n";
    }

    if ($debug && $quota_exceeded) {
        echo "Quota is marked as exceeded.\n";
    }
    if ($debug && !$quota_exceeded) {
        echo "Quota is not marked as exceeded.\n";
    }

    if (($quota_size > $quota_limit) && !$quota_exceeded) {
        if ($debug) {
            echo "Locking database...\n";
        }
        // Save in quota table
        $usql = "UPDATE $mysql_table SET exceeded='Y' WHERE db='$quota_db';";
        mysqli_query($con, $usql);
        if ($debug) {
            echo "Querying: $usql\n";
        }
        // Dismiss CREATE and INSERT privilege for database
        mysqli_select_db($con, 'mysql');
        $usql = "UPDATE db SET Insert_priv='N', Create_priv='N' WHERE db='$quota_db';";
        mysqli_query($con, $usql);
        if ($debug) {
            echo "Querying: $usql\n";
        }
        mysqli_select_db($con, $mysql_db);
    }

    if (($quota_size <= $quota_limit) && $quota_exceeded) {
        if ($debug) {
            echo "Unlocking database...\n";
        }
        // Save in quota table
        $usql = "UPDATE $mysql_table SET exceeded='N' WHERE db='$quota_db';";
        mysqli_query($con, $usql);
        if ($debug) {
            echo "Querying: $usql\n";
        }
        // Grant CREATE and INSERT privilege for database
        mysqli_select_db($con, 'mysql');
        $usql = "UPDATE db SET Insert_priv='Y', Create_priv='Y' WHERE db='$quota_db';";
        mysqli_query($con, $usql);
        if ($debug) {
            echo "Querying: $usql\n";
        }
        mysqli_select_db($con, $mysql_db);
    }
}

mysqli_close($con);
?>

You first need to put your mariadb root password in the script.
You then need to create a database named quotadb and add this table to it:

CREATE TABLE `quota` (`db` CHAR(64) NOT NULL, `limit` BIGINT NOT NULL, `exceeded` ENUM('Y','N') DEFAULT 'N' NOT NULL, PRIMARY KEY (`db`), UNIQUE (`db`));

In this table you will add the values of the database you want quota for and the value for the size of the quota (in bytes).

Execute this script on cron and you are good to go


Grant schema permissions effective for everything existing and everything that will exist

One way to do this is to create a custom role.  Many places use the DBA role out of the box but this often grants way more permissions than are really needed such as "DROP DATABASE".

Create a role, for example, LIMITED_PRODUCTION_DBA_ROLE, and grant

select, insert, update, delete any table;

This allows "Query tables, views, or materialized views in any schema except SYS". You can grant other privilege as required to the role.  Then grant the role to your users.  For development you will want to have more privileges so in your refresh script you can add a DEVELOPMENT_DBA_ROLE and grant that.


OBSERVATIONS

According to the MySQL 5.0 Documentation

  As of 5.0.2, the server requires that month and day values be legal, and not merely in the range 1 to 12 and 1 to 31, respectively. With strict mode disabled, invalid dates such as '2004-04-31' are converted to '0000-00-00' and a warning is generated. With strict mode enabled, invalid dates generate an error. To permit such dates, enable ALLOW_INVALID_DATES. See Section 5.1.7, “Server SQL Modes”, for more information.

  Before MySQL 5.0.2, the MySQL server performs only basic checking on the validity of a date: The ranges for year, month, and day are 1000 to 9999, 00 to 12, and 00 to 31, respectively. Any date containing parts not within these ranges is subject to conversion to '0000-00-00'. Please note that this still permits you to store invalid dates such as '2002-04-31'. To ensure that a date is valid, you should perform a check in your application.

The second paragraph is not in the 5.1, 5.5, or 5.6 Documentation. This may have been left out of the Documentation when MySQL 5.1 went GA. If this is still happening, this is a big oversight.

Let try this out on MySQL 5.5.37

mysql> create database bluecollarcoder;
Query OK, 1 row affected (0.00 sec)

mysql> use bluecollarcoder;
Database changed
mysql> CREATE TABLE `test` (
    ->   `a` datetime NOT NULL
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values ('999-01-01 01:01:01');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test values ('1-01-01 01:01:01');
Query OK, 1 row affected (0.02 sec)

mysql> select * from test;
+---------------------+
| a                   |
+---------------------+
| 0999-01-01 01:01:01 |
| 0001-01-01 01:01:01 |
+---------------------+
2 rows in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.37-log |
+------------+
1 row in set (0.00 sec)

mysql>

I just did this on my laptop running MySQL 5.5.37 for Windows. Same Problem.

You will have to check for invalid dates in your code.

SUGGESTION

If you want to fix all dates so that invalid datetimes are defaulted to a valid datetime, you can run this:

update test set a = from_unixtime(unix_timestamp(a));

When I ran it, it set to FROM_UNIXTIME(0) in my timezone

mysql> update test set a = from_unixtime(unix_timestamp(a));
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select a,unix_timestamp(a) from test;
+---------------------+-------------------+
| a                   | unix_timestamp(a) |
+---------------------+-------------------+
| 1969-12-31 19:00:00 |                 0 |
| 1969-12-31 19:00:00 |                 0 |
+---------------------+-------------------+
2 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!


Why are some of my migrated tables showing "The given key was not present in the dictionary" in WebMatrix's interface?

Here is what you need to do

Open the table in definition view
Select a cell in the column which is empty.
Add an entry of that data type (I picked the datetime in an example where this happened to me)

When done, you should be able to click the data button and see all the data.


There's really no one 'best way' to store time series data, and it honestly depends on a number of factors.  However, I'm going to focus on two factors primarily, with them being:

(1) How serious is this project that it deserves your effort to optimize the schema?

(2) What are your query access patterns really going to be like?

With those questions in mind, let's discuss a few schema options.

Flat Table

The option to use a flat table has a lot more to do with question (1), where if this isn't a serious or large-scale project, you'll find it much easier not to think too much about the schema, and just use a flat table, as:

CREATE flat_table(
  trip_id integer,
  tstamp timestamptz,
  speed float,
  distance float,
  temperature float,
  ,...);

There aren't many cases where I'd recommend this course, only if this is a tiny project which doesn't warrant much of your time.

Dimensions and Facts

So, if you've cleared the hurdle of question (1), and you want a more performance schema, this is one of the first options to consider.  It includes some basic normailization, but extracting the 'dimensional' quantities from the measured 'fact' quantities.

Essentially, you'll want a table to record info about the trips,

CREATE trips(
  trip_id integer,
  other_info text);

and a table to record timestamps,

CREATE tstamps(
  tstamp_id integer,
  tstamp timestamptz);

and finally all of your measured facts, with foreign key references to the dimension tables (that is meas_facts(trip_id) references trips(trip_id) & meas_facts(tstamp_id) references tstamps(tstamp_id))

CREATE meas_facts(
  trip_id integer,
  tstamp_id integer,
  speed float,
  distance float,
  temperature float,
  ,...);

This may not seem like it is all that helpful at first, but if you have for example thousands of concurrent trips, then they may all be taking measurements once per second, on the second.  In that case, you'd have to re-record the time stamp each time for each trip, rather than just using a single entry in the tstamps table.

Use case: This case will be good if there are many concurrent trips for which you are recording data, and you don't mind accessing all of the measurement types all together.

Since Postgres reads by rows, any time you wanted, for example, the speed measurements over a given time range, you must read the whole row from the meas_facts table, which will definitely slow down a query, though if the data set you are working with is not too large, then you wouldn't even notice the difference.

Splitting Up Your Measured Facts

To extend the last section just a bit further, you could break apart your measurements into separate tables, where for example I'll show the tables for speed and distance:

CREATE speed_facts(
  trip_id integer,
  tstamp_id integer,
  speed float);

and

CREATE distance_facts(
  trip_id integer,
  tstamp_id integer,
  distance float);

Of course, you can see how this might be extended to the other measurements.

Use case: So this won't give you a tremendous speed up for a query, perhaps only a linear increase in speed when you are querying about one measurement type.  This is because when you want to look up info about speed, you need only to read rows from the speed_facts table, rather than all the extra, unneeded info that would be present in a row of the meas_facts table.

So, you need to read huge bulks of data about one measurement type only, you could get some benefit.  With your proposed case of 10 hours of data at one second intervals, you'd only be reading 36,000 rows, so you'd never really find a significant benefit from doing this. However, if you were to be looking at speed measurement data for 5,000 trips that were all around 10 hours, now you're looking at reading 180 million rows.  A linear increase in speed for such a query could yield some benefit, so long as you only need to access one or two of the measurement types at a time.

Arrays/HStore/ & TOAST

You probably don't need to worry about this part, but I know of cases where it does matter.  If you need  to access HUGE amounts of time series data, and you know you need to access all of it in one huge block, you can use a structure which will make use of the TOAST Tables, which essentially stores your data in larger, compressed segments.  This leads to quicker access to the data, as long as your goal is to access all of the data.

One example implementation could be

CREATE uber_table(
  trip_id integer,
  tstart timestamptz,
  speed float[],
  distance float[],
  temperature float[],
  ,...);

In this table, tstart would store the time stamp for the first entry in the array, and each subsequent entry would be the value of a reading for the next second.  This requires you to manage the relevant time stamp for each array value in a piece of application software.

Another possibility is

CREATE uber_table(
  trip_id integer,
  speed hstore,
  distance hstore,
  temperature hstore,
  ,...);

where you add your measurement values as (key,value) pairs of (timestamp, measurement).

Use case: This is an implementation probably better left to someone who is more comfortable with PostgreSQL, and only if you are sure about your access patterns needing to be bulk access patterns.

Conclusions?

Wow, this got much longer than I expected, sorry.  :)

Essentially, there are a number of options, but you'll probably get the biggest bang for your buck by using the second or third, as they fit the more general case.

P.S.: Your initial question implied that you will be bulk loading your data after it has all been collected.  If you are streaming the data in to your PostgreSQL instance, you will need to do some further work to handle both your data ingestion and query workload, but we'll leave that for another time. ;)


Select permission denied when using linked server in a function unless inlining is off

UDF inlining is now blocked when the definition references remote tables.
This restriction was added in Microsoft SQL Server 2019 CU6.


Will Installation of Service Pack 3 on SQL Server 2012 Web Edition (existing production) require database restore activity?

Installing service packs usually goes like this: detailed step by step guide

It shouldn't affect databases, but you may want to test installing SP3 on the test server first. It will help you get an idea how long it takes (management will want to know that beforehand). It will also give you a chance to find out if any of your applications (or anything else) got affected. Safety always comes first so production is never the best place to patch first.
No, you don't need to restore anything. Existing databses will be in the same state as they were before the installation. Settings (collation etc) will remail unchanged as well.
You may want to read about bugs in SP3 and potential problems that can occur during instalation. There is plenty of examples on Google. You may also want to kave a look at the release notes to understand better what fixes are contained in SP3.
I always do (just in case), but you don't have to. It is really about how concerned your managers are about data integrity, safety etc. It is usually recommended to backup all databases before any maintenance.


You can't have two Distribution databases. Put the distribution database in a Failover Cluster Instance that way it is highly available.

Maybe one day MS will allow the distribution database to be in an AG. I hear you can have an FCI on a cluster that also has AG (having both on the same cluster but completely distinct instances - so two standalone instances with an AG and one FCI), though I've never done it, and probably wouldn't recommend it.

Clustering is how you make the distribution database highly available. So if High Availability is important for your organization and you need replication, then you need to learn Failover Clustering and setup an FCI.


Can somebody tell me what are the direct equivalents of these features in SQL Server 2012?

An Oracle Pluggable Database is similar to a SQL Server User Database.

Before Oracle 12c an Oracle Instance could only have one Database.  With Oracle 12c Oracle added the ability for a single Instance to manage multiple Databases, like SQL Server has always done.

See Introduction to the Multitenant Architecture from Oracle Docs.

And Database Engine Instances from SQL Docs.


If you go the route I suggested in your previous post I'm going to say < 10 minutes.  But again, soooo much that isn't given will affect the wall time.  Is this a dedicated MySQL server or is other stuff going on?  Specifically things that will be affecting the disk i/o?  How fast are the disks? Are they SSD? Is it write through or write back cache?

In short there is no "explain" for operations like this that will give you a time value.

Don't forget to disable/enable keys during the modifications.

Report back and let us know the results.


Hold down the SHIFT key while opening your database.

-> Depending on the macro security settings for your database, you might see one or more security messages on startup. You must hold down the SHIFT key until you close the security messages, or the startup options will not be bypassed.


I don't think you can get the restore point back, but if you want to flashback the database you can use FLASHBACK DATABASE TO SCN <number> instead. If you know the approximate time you want to revert to you can get the SCN with TIMESTAMP_TO_SCN. This assumes that the data is still there, of course. If not it is gone.


Most  brands provide methods to retrieve the key an insert created in an identity or auto_increment column.

Unfortunately there is no general method for this, it will depend on the brand.

For example in MySQL it's called LAST_INSERT_ID(), in Transact SQL (SQLServer) @@identity (better: scope_identity()).


What is better? Many articles in one publication or many publications with one article each one?

My last position we had dozens of publications. We would group the articles by size where the largest tables with millions or tens of millions of rows would have their own publication and others would be grouped together. In some cases publications were grouped by tables that were needed for a single application. Some critical tables were also in their own publications so that if we needed to reinitialize it during the day, it wouldn't be because of some minor table.

But this also depends on your hardware, applications, etc. Some publications we would reinitialize during the day and no one complained. Others could only be done after hours.

We probably had over 1000 tables that we published so one table per article would be a no go. There is no hard answer on this.


hi Linked servers in general not best practice.  I generally recommend against them.  To many reasons to explain here....this would be come a white paper.  hahahah  I have been doing ETL and data security for 25 years.  please consult an architect and don't ask people on internet claiming to be architect.  there are very few people out there with my level of experience.  there is never one answer either...it depends on many factors how you approach this. AND testing small amount of records is not valid test.  do more intense testing.   best of luck!


Multiple SQL Server instances with FileStream enabled

Did found the answer in SQL Server support site.
https://connect.microsoft.com/SQLServer/feedback/details/793017/filestream-problem-when-installing-sql-server-2012-sp1-side-by-side-with-sql-server-2008-r2
It's a bug in SQL Server 2012. Hear is the copy of workaround suggested.

A workaround is to put the FILESTREAM containers of either a SQL Server 2012 instance or a prior version instance in a different volume by relocating or recreating them.
This will make the SQL Server 2012 RsFx driver (RsFx020x) attached to a different volume, so it won't interfere with the requests to the driver of a prior version.
For more info on relocating filestream dirs, please see http://msdn.microsoft.com/en-us/library/ms345483.aspx.
After relocation or recreation (it might need to restart the SQL Server), "fltmc instances" command will indicate if the RsFx drivers are attached to different volumes. For example, the result should look like this (excluding unrelated lines):
Filter                Volume Name                             Altitude        Instance Name
RsFx0201             C:                                     41002.01    RsFx0201 MiniFilter Instance     0
RsFx0105             D:                                     41001.05    RsFx0105 MiniFilter Instance     0


Graceful Test for Linked Server Availability Inside a TVF or View

The solution is to create a stored procedure on the local server that includes error handling for queries to remote servers and returns a single dataset. Then to create a view that calls the stored procedure using OPENROWSET.
The stored procedure has to use a table variable for holding the data it gets from each server. It can’t use a UNION between them because that would defeat the purpose of error trapping.
Here is an example of the stored procedure. In this example, I’m querying sys.Databases because that’s a view that everyone has and can easily test:

USE MyDatabase
GO
CREATE PROCEDURE TEST_PROC
AS
    SET FMTONLY OFF
    SET NOCOUNT ON

    DECLARE @OUTPUT TABLE (
        Server_Name NVARCHAR(100),
        Name        NVARCHAR(128),
        Database_ID INTEGER,
        State       INTEGER,
        State_Desc  NVARCHAR(60) )

    BEGIN TRY
        INSERT INTO @OUTPUT (Server_Name, Name, Database_ID, State, State_Desc)
        SELECT 'SERVER_A',  Name, Database_ID, State, State_Desc
        FROM SERVER_A.Master.sys.Databases
    END TRY
    BEGIN CATCH
        INSERT INTO @OUTPUT (Server_Name, Name, Database_ID, State, State_Desc)
        SELECT 'SERVER_A',  'Server is Unavailable', Null, NULL, ''
    END CATCH

    BEGIN TRY
        INSERT INTO @OUTPUT (Server_Name, Name, Database_ID, State, State_Desc)
        SELECT 'SERVER_B',  Name, Database_ID, State, State_Desc
        FROM SERVER_B.Master.sys.Databases
    END TRY
    BEGIN CATCH
        INSERT INTO @OUTPUT (Server_Name, Name, Database_ID, State, State_Desc)
        SELECT 'SERVER_B',  'Server is Unavailable', Null, NULL, ''
    END CATCH

    BEGIN TRY
        INSERT INTO @OUTPUT (Server_Name, Name, Database_ID, State, State_Desc)
        SELECT 'SERVER_C',  Name, Database_ID, State, State_Desc
        FROM SERVER_C.Master.sys.Databases
    END TRY
    BEGIN CATCH
        INSERT INTO @OUTPUT (Server_Name, Name, Database_ID, State, State_Desc)
        SELECT 'SERVER_C',  'Server is Unavailable', Null, NULL, ''
    END CATCH
GO

You will have to create a TRY/CATCH block for every server you want to query. With a little creativity, I’m guessing that it’s possible to do the above using a cursor loop based on sys.servers to generate and execute the above TRY/CATCH blocks for all linked servers.
Here is the associated view:

CREATE VIEW TEST_VIEW
AS
    SELECT SELECT Server_Name, Name, Database_ID, State, State_Desc
    FROM OPENROWSET('SQLNCLI11', 'Server=.;Trusted_Connection=yes;', 'EXEC MyDatabase.dbo.Test_Proc')
GO

Note that “Server=.;” in the connection string points to the local server. My understanding of SQL data sources is a little shaky, so this may not work for everyone. Also, you may have to update the rest of the connection string to match the needs of your environment.
The only downside I’ve encountered in testing is that if one of the servers is offline, it’s got about a 21 second timeout. So it takes at least that long to return. When all servers are up, it runs in a fraction of a second.  I’ve tried changing the timeout parameters in the linked servers definitions, but they don’t appear to make a difference. I’ll experiment more and update this solution if I find better results.
Thanks J.D. I wouldn’t have known to go this route without your suggestions.
-Robbie


PAGELOCK Conversion Deadlock From Update to Intent Exclusive

First of all, the process is holding IX and want to convert to U. This is expected with the hint in the query.

Both queries will take the IX lock during the select and then convert to U when the change (if any) needs to happen.

This can be solved by adding: WITH (XLOCK) as a hint to the temporary table.


How to retrieve the value of deleted key in jsonb?

The below function won't work on keys that are inside arrays.  If you want something like that, you'll first have to define the behaviour for such JSON structures.

The main idea is to create a table returning function, which both returns the remaining jsonb and the popped key-value pair.  The rest is finding and reconstructing the key-value pair in the source structure.

CREATE OR REPLACE FUNCTION jsonb_pop(source jsonb, key text)
    RETURNS TABLE (result jsonb, popped jsonb)
    LANGUAGE sql AS
$$
WITH elem(popped) AS (
    SELECT jsonb_strip_nulls(jsonb_build_object(key, source -> key))
)
SELECT source - key, NULLIF(popped, '{}'::jsonb) FROM elem;
$$;


Advice on Measures that have properties on their own

It sounds like you want to put aggregation rules (based on anything, doesn't matter) in the database.  Nothing particularly wrong with that, unless you're planning to use it to generate SQL dynamically.  That's a slippery surface: proceed with caution, because SQL is much easier to debug when you can see it.

Will the users be able to add to your putative Measure table?  If not, you might want to consider statically generating the SQL as stored procedures.  That would be easier to understand, both for the human being and likely for the query optimizer.

I'm also a little leery of your question because ISTM you're not quite sure yet what the properties of the measures will be.  In such a situation, it's often best to wait until a dozen or so use cases develop.  At that point, you'll have a concrete set that you can actually use, with identified properties.  Then you'll know enough that I expect the question will answer itself.  It wouldn't surprise me, though, if that many cases never do develop, in which event the Measure abstraction will have proved more trouble than it would have been worth, and was fortuitously avoided.

HTH.


MySQL error #1064 while creating a trigger

There are two problems

PROBLEM #1 : ENDIF is wrong. It's END IF

PROBLEM #2 : END IF $$ is wrong. It's END IF;

This is how it should appear

DELIMITER $$
DROP TRIGGER IF EXISTS brandRep $$
CREATE TRIGGER brandRep AFTER UPDATE ON sales_flat_order
 FOR EACH ROW
BEGIN
IF (NEW.status <> OLD.status AND OLD.applied_rule_ids <=> 45182) THEN
    UPDATE reps_transaction_history_ids
    SET order_status = NEW.status
    WHERE order_id = OLD.increment_id;
END IF;

END $$
DELIMITER ;

Give it a Try !!!


SQL Server 2016 SP2 CU2 Availability Group Online but Cluster Offline

but the Cluster on which it resides is saying offline.

It's not offline, otherwise clussvc would stop and you'd have a spew of errors in your errorlog that the AG shutdown due to loss of cluster services and that it's waiting on the cluster services to startup before proceeding.
I believe you're basing the above quoted conclusion on:

Cluster IP Address:                Failed
Cluster IP Address XX.XX.XXX.XX    Failed  (2nd subnet node)
Cluster name:                      Offline

This is just letting you know that the cluster name and associated IPs are not online. These are part of the core cluster resources but will not stop the cluster itself nor most of the services on it. In fact, if you try to remotely connect to the cluster using the cluster name (like RSAT) tools it should fail but that's just name resolution and administrative endpoints. This won't impact the cluster resources in a different resource group unless for some reason they use that name (I couldn't fathom why, though).

The databases are all synced and online. Availability group works fine.

Yes, this is because the cluster is running though you have some failed resources that should probably be taken care of sooner rather than later.

What should I be checking for basic Cluster running (other than AVG is running)?

This is going into the realm of Windows and while I have no problem talking about it, I'm not sure if this is the right place for it so I'll give you a small primer of items to check with WSFC (Windows Server Failover Clustering):

Is the "clussvc" running?
Is the witness online?
Can you remotely connect to the cluster? (Get-Cluster MyCluster)
Are any resources in a state other than up or running? (Get-ClusterResource -Cluster MyCluster)
What are the current votes in the cluster? (Get-ClusterNode -Cluster MyCluster | Select Name, NodeWeight, DynamicWeight [WS2012+])

Note that you don't have to use powershell but it's one of the easiest and fastest ways to get information about cluster resources without writing your own calls via the clustering API.
Aside: I wouldn't call Availability Groups AVG's, typical nomenclature would be AG or AGs.


How can I join two tables while filtering the results with a" WHERE IN" clause?

not familiar with mySQL syntax, but I believe there are multiple errors with your query. you've got an extra ")" or you tried to use "order by" in a subquery, the join clause looks weird, it's returning everything and there is no group by with sum(votes), etc.

For problems like these, it is best to start with a working query and expand it one step at a time. For example, first join 2 tables, then change the * to the list of fields you want, then restrict it with the third, then sum up votes, then sort them, ...

try:

SELECT b.id,sum(bm.totalvotes)
FROM
bills b
inner join favoritebills fb on b.id = fb.billid
inner join billvotesmeta bm on b.id = bm.billid
WHERE
userid='$_SESSION[userid]'
group by b.id
order by sum(bm.totalvotes) desc;

You may have to rename some fields. the names aren't clear to me.
if you want to add fields to your resultset, be sure to add them to the group by clause at the bottom too.


Database design for a time tracking application

Have a look at the Timesheet model on databaseanswers.org. This covers it nicely

Simply, a table with

Date
User (FK to User table)
Project (FK to Project table)
Time worked

The first 3 columns are PK.

Now, do you want to:

log activity eg Analysis, Coding, testing separately?
log project phases separately?
log non-project work eg training, holidays (eg user focus or project focus?)
...


Redesigning a multi-instance MySQL server for performance and resiliency

I would recommend that you set up separate clusters for different database instances. You should be able to set up multiple clusters sharing some or all of the hosts, just make sure each cluster has its own ports defined.


Temp DB space allocated- will it be reclaimed ever?

Like all databases, free (unallocated) space in tempdb is reused for new objects.  No action is needed for this space to be reused.

The physical size of tempdb files will reflect the high-water mark of growth since the instance was last started.  The size will revert back to the initial file size after an instance restart, freeing space on the disk volume if growth occurred.  It is also possible to release unused file space using DBCC SHRINKFILE.  That should not be done routinely but may be desirable if the growth was an anomaly, such as due to a bad query.  It is best to size files to accommodate expected space requirements and provide growth as a safety net for unexpected needs.

Multiple tempdb data files of equal size and growth can improve performance by reducing allocation page contention for some workloads.  However, that is unrelated to space reclamation.


TSQL to get a list of all users and when they last logged on in crowddb

You have an extra SELECT in your SQL.

Remove the SELECT and change the DATEADD function slightly.

SELECT
  cwd_user.user_name,
  DATEADD(SECOND,(CAST(cwd_user_attribute.attribute_value AS BIGINT)/1000),'1970-01-01')
FROM cwd_user, cwd_user_attribute
WHERE cwd_user_attribute.user_id = cwd_user.id
  AND cwd_user_attribute.attribute_name = 'lastAuthenticated';

Additionally, you should probably rewrite to support the 'new' JOIN syntax (by 'new', I mean ANSI-92)

SELECT
  cwd_user.user_name,
  DATEADD(SECOND,(CAST(cwd_user_attribute.attribute_value AS BIGINT)/1000),'1970-01-01')
FROM cwd_user
INNER JOIN cwd_user_attribute
   ON cwd_user_attribute.user_id = cwd_user.id
  AND cwd_user_attribute.attribute_name = 'lastAuthenticated';


Issue installing Firebird ODBC drivers -- driver eventually no longer found

The issue here is that I had to create a System DSN, which is actually odd because the SSIS job is scheduled to run with the account for which the User DSN exists, and it does work as long as I am logged into the server with that account.
Anyway I am way too tired to try to understand why the User DSN didn't work. So I decided to go for a System DSN instead.

It is not that I am against System DSN (on the contrary!), but it wasn't me that set this User DSN and I do not know the password for the credentials used in order to create a System DSN. So I had to be a little creative.

I had to use Registry Editor and notepad to convert a User DSN into a System DSN. Here is how I did it:

Log in with the user that has the User DSN entries that you with to convert to System DSN
Open the Registry Editor and navigate to HKEY_LOCAL_CURRENT\SOFTWARE\Microsoft\ODBC\
Right-click ODBC.INI and select export to save it as a file on the desktop (or anywhere else you fancy)
Open the .reg file with a text editor such as Notepad
Replace the text HKEY_CURRENT_USER with HKEY_LOCAL_MACHINE. Save your changes
Double click the .reg file and proceed to import it into the registry.

The process outlined above basically copies all User DSN entries as System DSN entries. If you to have just one entry copied over, then you will need to delete their corresponding keys under .\ODBC.INI as well as their corresponding entries in .\ODBC.INI\ODBC Data Sources.


Is it possible to extract (not reset) the sa or other passwords from SQL Server?

Depending on access rights to the server and the context in which it operates, there are a variety of ways someone might surreptitiously obtain a SQL username and password.

For example, if a user:

Has rights to attach a debugger to the client application or to the SQL Server process, they could read the password when it’s in memory.
Is able to temporarily redirect the client application away from the SQL Server (e.g. by editing DNS/hosts file, editing a SQL alias, temporarily stopping the SQL Server process then capturing the port used by that process, etc.), they could write a simple program that mimics TDS’s prelogin and login stages, have the client application connect to it, then walk away with the SQL authentication credentials used by the client application.
Is able to access the SQL login’s password hash (e.g. they have rights to sys.sql_logins), they could pull off a brute force attack using SQL queries (e.g. using PWDCOMPARE). At first, this may seem to require extraordinary computing resources and so be unlikely to succeed. However, if the user has some knowledge about the password (e.g. they’ve watched it being typed in and determined that it’s exactly 10 characters long and that only one letter is capital), the amount of effort required for them to do a brute force attack to crack the password is drastically reduced.
Has the private key to SQL Server’s SSL certificate, they potentially could intercept credentials by using a wire sniffer to decrypt TDS's login message.


How do you select non-valid column names used by views / functions and SPs?

In practice, you can't because dynamic SQL is effectively impossible to predict - you can't predict what string is going to be built before it runs.
Even if you try, queries can have branching logic - for example, based on parameters, you may build different SQL.


PostgreSQL 9.5 won't start after Windows 10 fall update

Thanks to suggestion from Daniel Vérité I was able to fix it without dumping the entire db server. My understanding of locales on Windows is very limited but from what I've learned it seems like Microsoft changed region name of czech locale from "Czech Republic" to "Czechia" during Fall Creators update (presumably so it conforms to ISO 3166-1).

Postgres somehow loads locale by combination of language name and region name, so databases created before this change cannot be connected to since the combination of language and region is no longer valid.

So to fix it, I downloaded Locale Builder 2.0 from Microsoft, and then:

Created new locale based on Czech locale (cs-CZ), kept the same locale name (cs-CZ)
Changed region name to "Czech Republic" (from "Czechia")
Created installer by selecting "Build" -> "Build Locale Installer"
Installed the newly created locale by running .msi file created in previous step
Restarted the PostgreSQL server, this time it started without problem

Now I can run PostgreSQL 9.5 with 'Czech_Czech Republic.1250' and PostgreSQL 10 with 'Czech_Czechia.1250' and they both work.

I'm not sure whether it's ok to create the locale with same locale name (cs-CZ), but it doesn't seem to cause any problems, maybe someone can elaborate more.


Best way to update the content of large lookups tables

Assuming that the script contains only INSERTs, UPDATEs and DELETEs to the target table(s), and that the app never attempts row locks (SELECT ... FOR UPDATE/SHARE) or DML on those tables, then the script shouldn't affect the app except for the increased load on the DB server.

When the script commits, the changes will instantly become visible to new statements (if the transaction is in READ COMMITTED isolation) or transactions (if they're in SERIALIZABLE isolation).

However...

  Nightly we'd like to update this information by deleting all the content and running a script that run a series of inserts and updates. This script could be a couple of million lines long.

You should use COPY for this instead of a script full of inserts and updates, if possible.

Just:

DELETE FROM mytable;
VACUUM mytable;
COPY mytable FROM 'inputfile.csv' WITH (FORMAT CSV)

or similar.

It will be a lot faster. Because of the next point, you might want to load several smaller files though.

  Is there something I should be aware of when running large transactions?

Long running data modifying transactions prevent VACUUM from cleaning up rows in other tables that they might be able to "see". This can create quite a lot of table bloat.

If you use READ COMMITTED isolation and stick to individual statements that don't take an extremely long time you won't have a problem, because the snapshot that controls which rows the transaction can see gets updated after each statement.

It's mostly a concern if you're using cursors, working in SERIALIZABLE isolation, or running extremely long-running individual data modifying statements.


Select date ranges with a 50% overlap to another date range

This can be expressed using Postgres' range types quite nicely

First query: find all ages that overlap with the Roman age:

select *
from ages a1
where period_name <> 'Roman'
  and exists (select *
              from ages a2
              where period_name = 'Roman'
                and int4range(a1.start_date, a1.end_date) && int4range(a2.start_date, a2.end_date))

For the second one it's necessary to calculate the length of the overlap, this can be done using the intersection operator for ranges: *, e.g. int4range(0,100,'[]') * int4range(-50,75,'[]') yields 76. This result has to be compared to the total length of the Roman age to get the percentage.

As this requires access to both ages (the roman and the overlapping) at the same time, this is better done using a join.

To make the final query easier to read, I use common table expression to turn the two columns into a proper range, then I use that to make the join.

Once I have that. the overlapping percentage is easy to calculate:

with ranges as (
   select period_name,
          int4range(start_date, end_date, '[]') as period,
          upper(int4range(start_date, end_date, '[]')) - lower(int4range(start_date, end_date, '[]')) as years
   from ages
), overlapping as (
  select o.period_name as overlapping_period_name,
         r.period as roman_period,
         o.period as overlapping_period,
         r.period * o.period as overlap_length,
         r.years as roman_years,
         o.years as other_yers
  from ranges r
    join ranges o on r.period && o.period and o.period_name <> 'Roman'
  where r.period_name = 'Roman'
)
select overlapping_period_name,
       overlapping_period,
       (upper(overlap_length) - lower(overlap_length))::numeric / roman_years as overlap_percent
from overlapping;

The cast ::numeric is needed because otherwise it would be an integer division and the result would be 0 not 0.75

To display only those with a minimum of 40% overlap just add a where condition:

....
select *
from overlapping
where (upper(overlap_length) - lower(overlap_length))::numeric / roman_years >= 0.4

Online example: http://rextester.com/JIOAV33074

If you need to do a lot of calculations on the "periods", it might be a good idea to store them as a range directly, that saves a lot of converting back and forth when doing calculations.


Restoring ORACLE 11gR2 database with Flash Recovery Area Only

Just start rman, and issue the command:

CATALOG START WITH 'c:\path\to\your\backups';

... and it'll prompt you for confirmation & add the backups to the rman catalog.


Parameter triggers different execution plan, but why?

Your first query has constants for the parameters, so when sql compiles the plan it knows exactly what the parameters are going to be, and can use that knowledge to pick an optimal plan.

In the second query, the values are parameterized, therefore sql will create a plan that will work for any potential values for the parameters.

You can test this by running the parameterized query with the OPTION (RECOMPILE) clause.  This will allow sql to create an optimal plan for those specific values.

declare

@StartDate date,
@EndDate date,
@StartTime time(3),
@EndTime time(3),

SET @StartDate = '2012-10-08';
SET @EndDate ='2012-10-08';
SET @StartTime ='07:00:00.000';
SET @EndTime = '08:00:00.000';

SELECT *
FROM [dbo].[QueryNumbers] (@StartDate,@EndDate,@StartTime,@EndTime) OPTION (RECOMPILE)


InnoDB has no tool to do anything like what you describe.

InnoDB has a limit of 767 bytes per column in an index.  That has room for a VARCHAR(255) utf8 or a VARCHAR(191) utf8mb4.  Also, InnoDB wants to put long VARCHARs in a different block if the whole record is bigger than about 8KB.  Will this be common?  (Don't blindly use VARCHAR(500) when you can reasonably state a smaller limit.)

Plan A:  Compress (in the client) the name and store it into VARBINARY(255).  Assuming it is typical text, the compression will be about 3:1.  Use that instead of the Hash.

Plan B:  Split the name into 2 or 3 columns so the index limit is abided by.  (An ugly solution!)

Plan C:  Change the 767 limit.  (It's possible, but I forget the details at the moment.)

Something to note:  Any decent "hash" is very random.  That is, each new row will land in some random place in the table.  Once the table exceeds innodb_buffer_pool_size, you will be doing more and more I/O, thereby slowing down.

What will the SELECTs be like?  Will you have other indexes on this table?  Will you JOIN on name_hash?  All of these may have a bearing on the 'best' way to design the schema.


Find the source of a recurrent mass SQL edit on a server

SQL Injection is hard to track from SQL Server side. Instead of looking at sql server, you should look at your web server IIS logs.

Use Log Parser to parse your IIS Logs to track down the source of sql injection. e.g.

  logparser.exe -i:iisw3c -o:Datagrid -rtp:100 “select date, time, c-ip, cs-uri-stem, cs-uri-query, time-taken, sc-status from C:\wwwlogs\W3SVCXXX\u_ex1207*.log where cs-uri-query like ‘%declare%’”

Read up on

SQL Injection – a Serious Security Issue by Erland Sommarskog
SQL Injection: How it Works and How to Thwart it
Protecting Yourself from SQL Injection in SQL Server - Part 1 and Part 2 from Aaron Bertrand.


Add a column at a specific position in system versioned temporal table

Once you have fully turned versioning off for that table, the base table and the history table are just two normal tables. So you can:

Turn of history
Add the column into the base table in the place you want (you'll need to use the "new table, copy data, drop old, rename new" approach as you can't add columns in a particular place otherwise)
Repeat this column add for the history table
Recreate the versioning relationship between the two tables

But I would recommend avoiding the extra complication and just adding the new column to the end unless this is causing you a problem beyond aesthetics.


Why does postgresql differentiate between named and numeric time zone notations?

Because, as documented, one uses POSIX and other uses ISO-8601.

  Another issue to keep in mind is that in POSIX time zone names,
  positive offsets are used for locations west of Greenwich. Everywhere
  else, PostgreSQL follows the ISO-8601 convention that positive
  timezone offsets are east of Greenwich.


Database diagram for linked server tables

There's no way to do this with the native tools within SQL Server Management Studio. You could get a third party tool like ER/Studio, but even that won't automatically model the linked tables. You'll have to enter them manually. External tables just aren't considered to be a part of the fundamental model of a given database. They're external, additional, other.


Casting int YYYYMMDD to date produces incorrect value in Squirrel-SQL

For integer to date conversion you may consider using the system function in msdb called dbo.agent_datetime(date int, time int).  This will automatically convert to a datetime (which you could then cast as a date).

Source: https://blog.sqlauthority.com/2015/03/13/sql-server-interesting-function-agent_datetime/


ER Diagram | Group, Post, User relationship

the bridge table group_user between User and group is the right idea, when there is a many to many relationship
But users should be connected directly to posts like in the first image, because only pone user can have written the post-
Conclusion make a new mixed version


How to include Innodb_trx_id in MySQL slow log

The extended fields that Percona added are not available in stock MySQL.

The only other option is to add a comment to your SQL query in your app. The comments are added to the log, and pt-query-digest can parse fields out of comments with the --embedded-attributes option.

It's a bit tricky to discover the transaction id. You can query the transaction for your current thread:

SELECT TRX_ID FROM INFORMATION_SCHEMA.INNODB_TRX
WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID();

But you find no row in that table until your transaction has created any locks. That is, a transaction that does nothing but SELECT will not show up in that table.

You could simply embed CONNECTION_ID() into your query comments, on the assumption that grouping by connection is as good as grouping by transaction.


Query to check the timestamp of a table accessed recently

If you are the application guy, it may be easier to configure the application to log activity directly from the app layer.  Maybe configure some sort of verbose logging option?

The ALL_TAB_MODIFICATIONS table, which is what I think you mean, is not a reliable source for what you're trying to identify, as it's not updated immediately after a change is made.

  For performance reasons, the Oracle Database does not populate these
  views immediately when the actual modifications occur.

If logging via the application isn't a feasible approach, hopefully you can enlist the help of your DBA.  Point him/her to the Auditing functionality within Oracle, specifically the section on Auditing SQL Statements.  This should be available on both Standard and Enterprise Editions, and this is one way to identify when people are reading data from a table.

What you can do is specify an audit for a given DML command, and log that acitvity to the audit log.  The Tutorial on that page will run you through the entire process, so I'm not going to go into specific syntax in this post.

A few words of warning though.  If you have a lot of activity, you will want to be very particular about what you audit and for how long.  You will want to make sure you setup the appropriate maintenance process to purge the log after a reasonable amount of time.  A good script can be found here.  Additionally, you will also want to ensure your audit table is moved out of the SYSTEM tablespace.  To do that, you'll need to take advantage of the DBMS_AUDIT_MGMT package.

Auditing is not something I would ever recommend you setup quickly or without forethought.  Be careful about what you collect as the logs can fill quickly, especially within environments under heavy activity.


MySQL is using swap memory while I have plenty of free RAM

Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total shows about 15% free. Given the low number of dirty pages and the very low Innodb_buffer_pool_reads vs Innodb_buffer_pool_reads_requests you probably could trim your buffer_pool size.

Innodb_buffer_pool_pages_free == 0 is perfectly acceptable if you aren't reading from disk frequently (low Innodb_buffer_pool_reads).


Left join where there is no match on the left (IS NULL):

SELECT campaign_id as id, tbl1.nickname as name
FROM tbl1
LEFT JOIN tbl2 ON nickname=name
WHERE tbl2.id IS NULL

Alternately there is a MariaDB-10.3 syntax:

select version();

| version()                                   |
| :------------------------------------------ |
| 10.3.11-MariaDB-1:10.3.11+maria~stretch-log |

create table tbl1 (name varchar(20))

✓

insert into tbl1 values ('n1'),('n2'),('n6')

✓

create table tbl2 (name varchar(20))

✓

insert into tbl2 values ('n1'),('n2'),('n12')

✓

select name from tbl1 except select name from tbl2

| name |
| :--- |
| n6   |

db<>fiddle here


Clarification on Cardinality and Participation

After further research, and for anyone else in the future, I thought I would post what I have learned looking into this topic. Credit mostly goes to "Data Modeling and Database Design" by Narayan S. Umanath and Richard W. Scamell (2nd Edition).

I came up with following participation and cardinality:

In reference to the relationship above, I determined the participation and cardinality for the entity "Objects" in the "has" relationships to be optional (0) with a cardinality of many (m). The reason is because the foreign key "schedule_ID" in the "Objects" table is an optional attribute, meaning "Objects" does not have mandatory participation in the relationship. Additionally, the business logic dictates that each tuple in the "Objects" table can only correspond to one tuple in the "schedule" table.

For the (0,m) that defines the participation and cardinality of the "schedule" entity in the relationship "has", parent entities in relationships are rarely total participation, meaning in majority of cases (unless specific business logic requires it) they have partial partition (denoted by the "0"). For the cardinality, each tuple in the "schedule" table can be related to many objects.

Additionally, I compiled the following notes while looking into this that might be helpful in these types of cases (creating ERD for existing databases):
- by default, relationships between parents and children are (0,m)(0,1).
- can not enforce mandatory participation on parent side without the use of triggers.
- in order to for participation on child entity, put a NOT NULL constraint on the foreign key fields.
- to specify alternative keys (candidate keys), you can place a UNIQUE constraint.

I invite others to offer comments and critique. Hope this helps someone!


Create Trigger that updates the same table before (or after ??) the Insert

CREATE TRIGGER `populate_country_id` BEFORE INSERT ON `city`
FOR EACH ROW
SET NEW.country_id = (SELECT CASE WHEN NEW.country_id = 0
                                  THEN country_id
                                  ELSE NEW.country_id END
                      FROM country
                      WHERE country = NEW.country
                      LIMIT 1); -- as a precaution

BEGIN-END and DELIMITER reassign are excess.

PS. If NEW.country value is absent in country table then ... anycase inserted data is wrong.


Cannot drop the user because it does not exist or you do not have permission

I don’t have an answer, just a handful of suggestions, each possibly less useful than the last.

DB created on server A, moved.copied to server B, and database user does not “synch up” with instance Login. Happens with SQL Authentication. I reall don’t think this is it, but it’s worth a mention.

Poorly chosen name (user “Table”), or oddball characters (“My Login”)? Also extremely unlikely.

Try running this, then run the generated script? Rival DBAs may have created a login with a trailing space?

SELECT 'DROP USER [' + name + ']'
 from sys.database_principals
 where principal_id = XX  --  Target Id goes here

Similarly, go through SSMS, Object Explorer, drill down to Security, Users, right click the problem entry, select “Script User as”, “DROP to”, send to a new window, try that script?

Check if you do have sysAdmin rights? The following should return 1:

PRINT is_srvrolemember('sysadmin')

Might be tied in with something subtle and stealthy like replication, or its the database owner, or something equally obtuse and obscure

Run DBCC CHECKDB, see if any internals are out of whack?

The irritating thing is this is vaguely familiar, but I can’t remember where I came across it before. Please post if/when you figure it out!

If all else fails, nuke it from orbit (drop and recreate database from scratch). It’s the only way to be sure.


When to rebuild and when to reorganize indexes

As per followed practice you can rebuild index when fragmentation is >30 % and can reorganize when fragmentation is between 10 and 30 %. You should also include page_count value in your query. If page_count value( which is there in DMV sys.dm_db_index_physical_stats) is <1000 you don't need to rebuild or reorganize such indexes even though they show large fragmentation. I have seen sometimes for such indexes when rebuilt index fragmentation increases but it would hardly matter.

In your case page_count is 3 so absolutely no need to rebuild or reorganize index.

I would also like to see your full rebuild script it it quite possible you may have used less value of fill factor to rebuild index that would cause more number of pages after rebuilt and thus increasing fragmentation.

Below is a article written by Bob Dor from Microsoft where he actually pointed out that due to MAXDOP setting when used during index rebuild can cause increase in fragmentation I am  sure it is not applicable to your environment but have a look:
http://blogs.msdn.com/b/psssql/archive/2012/09/05/how-it-works-online-index-rebuild-can-cause-increased-fragmentation.aspx.

Please use below query and then find out indexes which needs rebuild:

select
    ips.index_type_desc,
    ips.index_depth,
    ips.index_level,
    ips.avg_fragmentation_in_percent,
    ips.fragment_count,
    ips.page_count,
    ips.avg_page_space_used_in_percent,
    ips.record_count
FROM
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
INNER JOIN
    sys.tables t ON ips.OBJECT_ID = t.Object_ID
INNER JOIN
    sys.indexes i ON ips.index_id = i.index_id AND ips.OBJECT_ID = i.object_id
WHERE
    AVG_FRAGMENTATION_IN_PERCENT > 0.0 and page_count >1000
ORDER BY
    AVG_FRAGMENTATION_IN_PERCENT, fragment_count


SSMS crashes when attempting to create a database diagram

I've found that the database diagram tool seems to crash if you modify things outside of the diagram.
Diagrams appear to remain stable if:

The database diagram tool is used to do all of your modifications, thus retaining all metadata of the process within the diagram.
Your database structure is modified outside of the diagram which is then deleted and recreated each time.
The entire database is built, ground up, to a point that you are satisfied and feel that it won't change.  The database should then be diagrammed post-beta.

Whichever method is used, this is a known issue.
To confirm, I have this issue while using SSMS 18.4 against a SQL 2016 Express instance.
Current Status - 10/03/2021 - SSMS Version 18.7.1
I still have issues with the diagram tool. It is slightly more stable, though, if you modify anything outside of a current diagram, the editor complains about metadata and you lose your work. In fact, I found that I had to also close and reopen SSMS to ensure I could edit or create a diagrams.  So, while SSMS remains a little more stable (that's debatable), whatever has been done is not a solution.
Roll on version 37.n - let's hope it's fixed by then!
Further Notes From a Small Island (12/03/2021)
There also appears to be an issue with putting your PC to sleep, particularly if you're working over a VPN. I found that when you sleep your computer, then attempt to modify a diagram that was left open, the editor errors with various meta-data related faults (there has been more than one fault). The remedy to this was to reconnect the VPN, then refresh the database diagrams folder in SSMS...

This should allow you to save the diagram.


Mongo Collection `Size` is *larger* than `storageSize`?

storageSize is the sum of all extents for that data, excluding indexes.

So that collection takes up 2 extents, they are ~2GB each, hence ~4GB. size includes indexes and I believe a couple of other things which inflate the number. Neither really represents the proper on-disk size. For disk size, db.stats() has a filesize field which is closer to what you want I think you're looking for.

The manual is somewhat better at outlining what the various fields mean, see here for collections:

http://docs.mongodb.org/manual/reference/collection-statistics/

And here for database stats:

http://docs.mongodb.org/manual/reference/database-statistics/

Some other potentially relevant information:

The compact command does not shrink any datafiles; it only defragments deleted space so that larger objects might reuse it. The compact command will never delete or shrink database files, and in general requires extra space to do its work, usually a minimum of one extra extent.

If you repair the database it will essentially rewrite the data files from scratch, which will remove padding and store them on disk as efficiently as you are going to get.  However you will need to have ~2x the size on disk to do so (actually less, but it's a decent guide).

One other thing to bear in mind here - repair and compact remove padding.  The padding factor varies between 1 (no moves of documents caused by documents growing), to 2 (lots of moves caused by documents growing).  Your padding factor of ~1.67 would indicate you are growing (and hence causing moves) quite a bit.

When you compact or repair a database you remove that padding - subsequent document growth is therefore going to trigger even more moves than before.  Because moves are relatiely expensive operations, this can have a serious impact on your performance.  More info here:

http://www.mongodb.org/display/DOCS/Padding+Factor


Multiple Inserts vs. Bulk Insert From Disk for hydrating SQL Server reference tables

Separate Files

Keep CREATE TABLE, INSERT, GRANT scripts separately.  Data MIGHT change "per project" (eg 'Oui' vs 'YES').  Additionally, this seems to be a common design for most projects I've seen/worked on.

INSERT vs Other

Bulk operations (f#,python,CSV) are faster than multiple single-row INSERT statements.

I would definitely use INSERT for a few rows.  The "cutoff point" between INSERT and Code/CSV would be a personal decision.

Usage of Code

I would only consider using code (f#,python) if and only if the data is Calculated based on input parameter(s).  (eg a DAY_DIMENSION table that loads n days at a time)

CSV Scripts

For sizable data, you'd want to use a CSV Script for speed

The script that loads the CSV (or other format) file should use the Filename as a parameter. All utilities (eg SQL*Loader) do this; you just need to filter the parameter up to the Shell Script level.
This allows you to load a different (project specific/updated revision) file at-will.

Make sure you note if globbing (eg LoadMyData.sh data_0[1-3]*.csv) is allowed.

Dumps

One of the easiest ways to port tables is to use Dumps (as Dave has suggested). (eg Oracle's expdp/impdp)

You should still have the DDL statements available so that you can recreate the tables (and dump) as necessary.


Change MariaDB 10.0.17 (MySQL) master to a slave

I would say your basic process is quite straight forward.

1) Ensure that slave1 has got log-bin and log-slave-updates enabled (this will require a restart if not)

2) Take a copy of Slave1 and set up Slave2 as a slave of Slave1. You now have
Master -> Slave1 -> Slave2

3) Once you are happy that Slave2 is performing as expected, set GLOBAL READ_ONLY=on on the master, and move all your writes to the Slave1.

4) Once this has completed, Shut down the master.

The only downtime you would have would be restarting Slave1 if required, and the time it takes to move your writes from the Master to the Slave1. This part is entirely dependent on your situation.


Designing a database for Questions, Options and Answers

The relationship between questions and options is one-to-one, so if you wanted to simplify your table structure, then these two tables could be merged into one.

Are you 100% certain that every question will have only 5 (or less) options? For flexibility reasons, it might make sense to have only one answer option per record. In that case, the options table must remain separate from the questions table, and it becomes a one-to-many relationship.

Which is the right answer option? You should probably indicate that somehow.

In the answers table, the answer_idis obviously the primary key. The question_id is a foreign key. And you seem to have forgotten a foreign key to options, as well as a foreign key to a users table to indicate who gave this answer.

A minor issue: In the questions table, the question column is a MEDIUMTEXT. This is up to 16 million bytes. Do you really need that many? A TEXT column can store up to 64K bytes, maybe that would be enough?

Also, you would want to use int unsigned for your auto_increment columns. The reason for this is that auto incrementing ints start at 1, so by allowing signed ints you're effectively wasting a bit and limiting the range of values you can use.

There may be other opportunities for improvements, these are just a few I noticed.


What does a RED X on a database user mean?

It does not mean the user is disabled (you can only disable logins), it means the user does not have connect privileges to the database. I'm not sure exactly how your users were created, but the easiest way to demonstrate this is:

CREATE LOGIN u1 WITH PASSWORD = 'x', CHECK_POLICY = OFF;
GO
USE tempdb;
GO
CREATE USER u1 FROM LOGIN u1;
GO
ALTER LOGIN u1 DISABLE;
GO
-- u1 has no red x even though the login has been disabled

CREATE USER u2 WITHOUT LOGIN;
GO
-- check Object Explorer, u2 has no red x

DENY CONNECT TO u2;
GO
-- check Object Explorer, u2 now has a red x!

CREATE USER u3 WITHOUT LOGIN;
GO
-- check Object Explorer, u3 has no red x

REVOKE CONNECT FROM u3;
GO
-- check Object Explorer, u3 now has a red x!

(You may need to refresh Object Explorer between GO commands because, well, caching.)

To fix it (assuming you actually want them to be able to connect to the database):

GRANT CONNECT TO [DomainName\BI360Consultants];
GRANT CONNECT TO [DomainName\BI360Users];

Surely you'll need to apply more permissions depending on what you need them to be able to do in the database.

There may be other, more obscure ways to get into this state (e.g. adding a domain group to a role in a database without actually adding a user, as described in MichaelK's answer). Though I'll be honest, when I tried to do what the OP did, the old way or the right way, I was not able to add the domain group to a role without a user present:

-- the old way
EXEC sys.sp_addrolemember N'db_datareader', N'[CAKE\MyGroup]';

  Msg 15410, Level 11, State 1, Procedure sp_addrolememberUser or role '[CAKE\MyGroup]' does not exist in this database.

-- the right way
ALTER ROLE db_datareader ADD MEMBER [CAKE\MyGroup];

  Msg 15151, Level 16, State 1Cannot add the principal 'CAKE\MyGroup', because it does not exist or you do not have permission.

Of course with this outcome I did not see any such user in sysusers (deprecated; stop using it) or sys.database_principals. However, if I did this (thanks to sepupic's answer):

GRANT SELECT ON dbo.SomeTable TO [CAKE\MyGroup];

Then the user did show up in those views and did show up as a user in Object Explorer with the red x due to HAS_DBACCESS() = 0. Which still amounts to roughly the same thing: "can't access the database." So if the above GRANT CONNECT does not work (in my case, that did get rid of the red x, but I did not try to actually query the database as that account), also try the following, knowing that it might fail:

CREATE USER [DOMAIN\Group] FROM LOGIN [DOMAIN\Group];

In my case, when I granted connect to this user, it prevented me from running the CREATE USER command:

  Msg 15023, Level 16, State 1, Line 16User, group, or role 'CAKE\MyGroup' already exists in the current database.

This state will always be true for guest/INFORMATION_SCHEMA/sys - with the exception of the guest account on certain system databases. Ignore that and leave them alone.

From the sp_addrolemember topic:

From the sys.sysusers topic:


I am unable to join tables in correct manner

Try this out,

This should work, or at least get you on the right path of getting the results you are looking for... pay attention to how the tables are alias and how the joins are used as that is key for getting the proper results...

1)
SELECT P.PID
     , CONTACT(R.FNAME,' ',R.LNAME) AS FULL_NAME
     , P.PCONTENT
FROM REGISTRATION R
JOIN POST ON R.RID = P.RID

2)
SELECT P.PID
     , P.PCONTENT
     , COUNT(PL.PID) AS TOTAL_LIKES
FROM POST P
JOIN PLIKESTATS PL
ON P.PID = PL.PID
WHERE PL.LIKESTATUS = 1
GROUP BY P.PID
       , P.PCONTENT

3)
SELECT P.PID
     , P.PCONTENT
     , COUNT(C.PID) AS COMMENT_COUNT
FROM POST P
JOIN COMMENT C ON P.PID = C.PID
GROUP BY P.PID
       , P.PCONTENT


Data Loss when forced failed over to secondary replica on Sql Server 2014

What am I missing here

You should first read How It Works: Always On–When Is My Secondary Failover Ready?. This would tell you about scenarios when your secondary is not failover ready and what could lead to "no automatic" failover

  Why did the data loss happen? My understanding is that since two replicas was on synchronous commit, there would be no data loss when either one of them goes down

The data loss happened because you forced failover over to replica which was not synchronized. Look at the word "FORCE_FAILOVER_ALLOW_DATA_LOSS" it says itself that if you use me you "might" face data loss. What this command does is this beings online database discarding any transaction log blocks which originated on primary but somehow did not made on secondary and since secondary has no information about it it cannot replay it and hence you might face data loss when database comes online.

  Is it the data in the Log transactions that was lost. Would periodic log shrink prevented this data loss ?

Let us call it transaction log block not data. This log block was on primary but due to unexpected shutdown it was not delivered to secondary and secondary was not synchronized. If you are able to bring primary online after some time without doing any forced failover you might have been able to save data loss but since you forced failover it is not possible. May be later if you bring primary replica online and the information is there in transaction log you can get it in primary replica. Shrinking has NO business here at all.


How to GRANT the USAGE privilege on SCHEMAS that will be created in the future

Your ALTER DEFAULT PRIVILEGES needs to be for the role who will be creating the schema, so it needs to be FOR ROLE other_role.


MyISAM tables sometimes get corrupted in a power failure.  REPAIR TABLE is the remedy.

InnoDB checks and repairs all its tables after any crash.

If a crash occurs in the middle of, say, a multi-row UPDATE in MyISAM, some of the rows will be updated, some won't.  In InnoDB, the entire "transaction" will be automatically rolled back.  That is, it's all or none.

Check your indexes when moving:  http://mysql.rjweb.org/doc.php/myisam2innodb


I can't answer your question as to the absolutely best way. But I can offer a different way of solving the problem, which may or may not be better. It has a reasonably flat execution plan, and I think it will perform well. (I'm eager to know so share the results!)

I apologize for using my own syntax style instead of yours--it helps query wizardry come to me when everything lines up in its usual place.

The query is available in a SqlFiddle. I threw in an overlap for EmpID 1 just to be sure I had that covered. If you eventually find that overlaps cannot occur in presence data, then you can remove the final query and the Dense_Rank calculations.

WITH Points AS (
  SELECT DISTINCT
    T.EmpID,
    P.TimePoint
  FROM
    (
      SELECT * FROM dbo.WorkTime
      UNION SELECT * FROM dbo.BreakTime
      UNION SELECT * FROM dbo.Absence
    ) T
    CROSS APPLY (VALUES (StartTime), (EndTime)) P (TimePoint)
), Groups AS (
  SELECT
    P.EmpID,
    P.TimePoint,
    Grp =
      Row_Number()
      OVER (PARTITION BY P.EmpID ORDER BY P.TimePoint, X.Which) / 2
  FROM
    Points P
    CROSS JOIN (VALUES (1), (2)) X (Which)
), Ranges AS (
  SELECT
    G.EmpID,
    StartTime = Min(G.TimePoint),
    EndTime = Max(G.TimePoint)
  FROM Groups G
  GROUP BY
    G.EmpID,
    G.Grp
  HAVING Count(*) = 2
), Presences AS (
  SELECT
    R.*,
    P.Present,
    Grp =
       Dense_Rank() OVER (PARTITION BY R.EmpID ORDER BY R.StartTime)
       - Dense_Rank() OVER (PARTITION BY R.EmpID, P.Present ORDER BY R.StartTime)
  FROM
    Ranges R
    CROSS APPLY (
      SELECT
        CASE WHEN EXISTS (
          SELECT *
          FROM dbo.WorkTime W
          WHERE
            R.EmpID = W.EmpID
            AND R.StartTime < W.EndTime
            AND W.StartTime < R.EndTime
        ) AND NOT EXISTS (
          SELECT *
          FROM dbo.BreakTime B
          WHERE
            R.EmpID = B.EmpID
            AND R.StartTime < B.EndTime
            AND B.StartTime < R.EndTime
        ) AND NOT EXISTS (
          SELECT *
          FROM dbo.Absence A
          WHERE
            R.EmpID = A.EmpID
            AND R.StartTime < A.EndTime
            AND A.StartTime < R.EndTime
        ) THEN 1 ELSE 0 END
    ) P (Present)
)
SELECT
  EmpID,
  StartTime = Min(StartTime),
  EndTime = Max(EndTime)
FROM Presences
WHERE Present = 1
GROUP BY
  EmpID,
  Grp
ORDER BY
  EmpID,
  StartTime;

Note: performance of this query would be improved you combined the three tables and added a column to indicate what kind of time it was: work, break, or absence.

And why all the CTEs, you ask? Because each one is forced by what I need to do to the data. There's an aggregate, or I need to put a WHERE condition on a windowing function or use it in a clause where windowing functions aren't allowed.

Now I'm going to go off and see if I can't think up another strategy for accomplishing this. :)

For amusement I include here a "diagram" I made to help solve the problem:

------------
   -----------------
                ---------------
                           -----------

    ---    ------   ------       ------------

----   ----      ---      -------

The three sets of dashes (separated by spaces) represent, in order: presence data, absence data, and the desired result.


How to prevent a trigger from running on certain statements?

I answered a question like this back on Jan 20, 2012 : Disable trigger for just one table

It was a workaround for MySQL I suggested. No negative feedback so far.


Is it possible to change the drive that Microsoft DPM backs up to?

DPM and Windows Server Backup make a determination when the agent is installed as to which drive it would use for System State Backups.

This by default is the drive with the most available free space. That information gets saved into the PSDataSourceConfig.XML file.

WARNING:
If the server you are protecting is a member of a cluster, it is possible that a cluster drive will be selected in this process. If that drive ownership has been switched to another node, then next time system state for the node is done, the drive is no longer available and the backup will fail. In this situation, you will need to modify the PSDataSourceConfig.XML to point it to a local drive.

On the server you are backing up, navigate to C:\Program Files\Microsoft Data Protection Manager\DPM\Datasources.
There you will find a file named PSDataSourceConfig.XML. Right click that file and choose to edit.
Find the section for  which is followed by a drive letter and WindowsImageBackup. This will be the current location WSB will put the files.
Modify the file to reflect the drive letter you want WSB to use.


You're going to have to do quite a bit more work on requirements if you want to do this properly, or you could cheat and buy SSDs and do minimal benchmarking.

Prior to SSDs putting anything on RAID6 for SQL Server was planned out very carefully, and done mostly for read only scenarios.  With the advent of SSDs we can often use RAID 6 with no impact to performance outside of some negligent latency.

Performance Metrics:

You will want to collect the following metrics at a minimum in Perfmon to see your usage.  Include all data and log files:

-Logical Disk
--Disk Sec/Read
--Disk Sec/Write
--Disk throughput/sec

RAID Considerations:

You'd want to invest in a good raid controller and with RAID 6 you can suffer a 2 disk outage instead of a single disk outage from RAID 5.  This requires more parity to be calculated and written thus reducing performance but the Samsung 850 Pro line can churn through that very easily.

File Placement:

The log file and tempdb were almost always split up and depending on your workload/high availability solution/acceptable data loss policy you might still be bound to some of that.  Just note that if you go with 1 big array and the disks go down, you lose the log file data that hasn't been backed up yet either; thus affecting your acceptable data loss policy.

I know it's rather unorthodox to recommend commodity hardware for SQL Server but after using thousands of these hard drives in high throughput and volume environments, I stand by it.

Do note that your disk controller and server are still a single point of failure.  You should also do real HA such as log shipping/mirroring/AGs/etc.


Concatenate text and tochar(): Why is there a space between the two?

Because to_char() pads the result with spaces (I never understood why).

You need to use the "fill mode":

'ABC' || to_char('1','FM000000')


Partitioned view with dedicated "routing" column

One approach if you can't use a partitioned view is INSTEAD OF triggers like the examples below.  Note that the UPDATE trigger assumes that one never changes a primary key value after the initial insert.

CREATE TRIGGER [TRI ALL COMPANIES$Employee] ON
    [ALL COMPANIES$Employee]
    INSTEAD OF INSERT
AS

INSERT INTO [Company 1$Employee] (Col1, Col2, Col3)
SELECT Col1, Col2, Col3
FROM inserted
WHERE Company = 'Company 1';

INSERT INTO [Company 2$Employee] (Col1, Col2, Col3)
SELECT Col1, Col2, Col3
FROM inserted
WHERE Company = 'Company 2';

INSERT INTO [Company 3$Employee] (Col1, Col2, Col3)
SELECT Col1, Col2, Col3
FROM inserted
WHERE Company = 'Company 3';

INSERT INTO [Company 4$Employee] (Col1, Col2, Col3)
SELECT Col1, Col2, Col3
FROM inserted
WHERE Company = 'Company 4';
GO

CREATE TRIGGER [TRU ALL COMPANIES$Employee] ON
    [ALL COMPANIES$Employee]
    INSTEAD OF UPDATE
AS

UPDATE target
SET
      Col2 = inserted.Col2
    , Col3 = inserted.Col3
FROM [Company 1$Employee] AS target
JOIN inserted ON inserted.Col1 = target.Col1
WHERE inserted.Company = 'Company 1';

UPDATE target
SET
      Col2 = inserted.Col2
    , Col3 = inserted.Col3
FROM [Company 2$Employee] AS target
JOIN inserted ON inserted.Col1 = target.Col1
WHERE inserted.Company = 'Company 2';

UPDATE target
SET
      Col2 = inserted.Col2
    , Col3 = inserted.Col3
FROM [Company 3$Employee] AS target
JOIN inserted ON inserted.Col1 = target.Col1
WHERE inserted.Company = 'Company 3';

UPDATE target
SET
      Col2 = inserted.Col2
    , Col3 = inserted.Col3
FROM [Company 4$Employee] AS target
JOIN inserted ON inserted.Col1 = target.Col1
WHERE inserted.Company = 'Company 4';
GO

CREATE TRIGGER [TRD ALL COMPANIES$Employee] ON
    [ALL COMPANIES$Employee]
    INSTEAD OF DELETE
AS

DELETE target
FROM [Company 1$Employee] AS target
JOIN deleted ON deleted.Col1 = target.Col1
WHERE deleted.Company = 'Company 1';

DELETE target
FROM [Company 2$Employee] AS target
JOIN deleted ON deleted.Col1 = target.Col1
WHERE deleted.Company = 'Company 2';

DELETE target
FROM [Company 3$Employee] AS target
JOIN deleted ON deleted.Col1 = target.Col1
WHERE deleted.Company = 'Company 3';

DELETE target
FROM [Company 4$Employee] AS target
JOIN deleted ON deleted.Col1 = target.Col1
WHERE deleted.Company = 'Company 4';
GO


Would it be better for query plans to be split by statement for re-usability?

If a user executes a statement that is one of the statements in the multi-statement query can it use that relevant part of the query plan already in the cache for the multi-statement query?

No. The basic unit of plan reuse in SQL Server is the batch.

  Would it be better to hash each statement in a multi-statement query so they could be used by users running individual statements from the query?

A system tuned for high levels of plan reuse will place common code (at a suitable granularity) in reusable objects (e.g. procedures, functions, triggers) on the SQL Server. It will also explicitly parameterize any application-generated or client-side code. For maximum plan reuse, these generated batches should differ only in parameter values.

  I expect there are complications that I'm not taking into account

It sounds like you are asking why SQL Server was designed to cache and reuse at the batch level, rather than at the statement level. I doubt anyone aside from the original designers could answer this question authoritatively. Anyway, it seems to me that a batch is a natural granularity to use because it is relatively self-contained natural unit of work, and represents a reasonable trade-off between implementation complexity and plan reuse probability.

There are a few things that make batches not entirely self-contained (for example local temporary tables created and referenced across stored procedure boundaries). These exceptions reduce orthogonality and have been associated with unexpected 'by design' behaviours and bugs over the years.


Oracle UPDATE statements lock race condition

As you can see from the documentation you supplied, oracle places an exclusive lock on the row during an update. This means that the second transaction in thread 2 will wait until thread 1 has committed and thereby releasing the lock on the row. If you only execute the UPDATE statement oracle will handle it for you.


Is it still best practice to avoid using the default ports for SQL Server?

Historically, it has been recommended not to use the default ports for connections to SQL Server, as part of security best practice.

Which was asinine then and still asinine now. Security through arguably obscurity isn't security at all.

  Is this advice still relevant

IMHO it was never relevant. It was required for some compliance purposes because the people drafting up those compliances did not understand what they were doing, again, IMHO.

  Should ALL of the above ports be changed?

I wouldn't change any.


Are there any problems with deploying an SQL Server database to a production server by taking a backup?

Everything is wrong with using backup files as deployment. But the burden is not on the DBA to provide the DDL, is on development. Your design and development artifacts should had been database installation and upgrade scripts. Never change anything in the database manually, everything should be modified using your app. Rails gets this in spades with he whole migrations infrastructure and you should try to adopt it too. I have for long advocated using similar techniques, see Version Control and your Database.

First let me make the case why source code based deployment/upgrade is superior to binary based deployment (.bak, or diff tools):

source code can be checked in into source control. This alone should settle the whole argument. Source control gives yo history, a future you can look back and read the check in notes, understand the reasoning behind current state.
source code can be quickly inspected at a glance. You look at it and read it. binary databases require to be attached and require extensive knowledge of the metadata catalogs to read even the basic properties
source code is clean. You see CREATE TABLE Foo (...), which clearly conveys the intent. Binary distribution, if you want to extract the object, showers you in a plethora of default properties. You loose the original intent.
source code can be peer reviewed at check in.
source code integrates in contiguous deployment

And I also have arguments why deploying by backup is bad (very bad):

you only postponed the issue. The very first update of the app will face the issue of deploying an update without loosing the data. This can occur the next day after deployment, when an issue is noticed in production, and you will be left facing the void: how to modify the production DB to match the dev DB?
Databases are not self contained. Deployment invokes objects outside DB (logins, SQL Agent jobs, maintenance plan etc) none of which can be deployed with a backup.
You never know what you deployed. Forgotten tables left over during dev? Test data? Is very difficult to cleanup a database, but keeping your source code up to date and correct is natural.


I have an oracle database table with date format as 'mm/dd/yyyy'

No you don't.. ;)

You have an Oracle database table with a date datatype.  Period.

Any type of "format" is for display only, and is easily modified.

  SQL> create table junk ( id number,  dd  date );

  Table created.

  SQL> insert into junk values ( 1, sysdate );

  1 row created.

  SQL> commit;

  Commit complete.

  SQL> select * from junk;

          ID DD
  ---------- ---------
           1 31-MAY-16

  SQL>
  SQL> alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss'
    2  /

  Session altered.

  SQL> select * from junk;

          ID DD
  ---------- --------------------
           1 31-may-2016 11:17:44

  SQL>

An Oracle DATE datatype holds Year + Month + Day + Hours + Minutes + Seconds information.
It's up to you how to display/use it.

Note that an Oracle TIMESTAMP datatype holds milliseconds in addition to what a DATE holds.

It would be much easier to give you a more specific answer if you'd actually show your table definition, and your query and explain the problem you're having, and what you want to see.


How do I fix and prevent corrupted MySQL tables?

Have you tried

REPAIR TABLE;

or

 myisamchk --recover tbl_name

http://dev.mysql.com/doc/refman/5.1/en/repair-table.html

--EDIT--

As mentioned in the comments under your question, convert your tables to InnoDB tables which are transactional. This should help prevent corruptions in the future.


Oracle DB export, possible charset conversion if NLS_LANG not set

The NLS_LANG parameter is used by the Oracle network layer to do character translation between the client and the database. This way the client can display all the characters in the database in a 'correct' way.

Your NLS_LANG should match the character-set of your database when you do an export. If you export a UTF-8 database in US7ASCII then you risk to loose information when you import this data back into a database. Why? The UTF-8 character-set can hold much more different characters then US7ASCII. The 'unknown' characters are replaced by a single character. In the past I have seen this happening that we lost all characters with accents. They were replaced by a '?' if I remember well.

If you import then you must be sure that the NLS_LANG is set to the value of your import file. Oracle will then do the mapping to the character-set of the database in which you import.


What is this upside-down silver key icon

It's signifying a unique constraint. In SQL Server, a unique constraint is actually implemented as an index under the covers; this is why it appears under the Indexes node as well (I can't give you a good explanation of why it is not at least listed under the Constraints node, nor why they chose an upside down key instead of just a different color).

You should see a result here to validate that this is actually implemented as an index:

SELECT index_id, type_desc, is_unique_constraint
  FROM sys.indexes
  WHERE name = N'IX_tblCustIndex';

You said that you saw "Script Constraint As..." - if you followed through with that it would have provided you a script something like:

ALTER TABLE [dbo].[tblCustIndex] ADD UNIQUE NONCLUSTERED
(
  [some_column] ASC
) WITH (...a bunch of default options...)

But don't run it because this syntax does not use a name and it will just create a second, redundant unique constraint...

Here is a very simple repro, and proof that I get the upside-down key even with very basic syntax, on 2014:

CREATE TABLE dbo.foo(id INT UNIQUE);

   click to enlarge

Now, you can get a different icon if you create a unique index explicitly (and the only differences you'll see in sys.indexes are the name and the value of is_unique_constraint):

   click to enlarge

For other differences see this Stack Overflow answer.


Why is this (postgres) composite gin(scalar, scalar, array gin__int_ops) index not using the array criteria?

It turns out the index gets used with the following array operator:
q.dataset_ids @> ARRAY[195]


Is it impossible to store 2^266 records on a database of some kind?

Absolutely possible, because someone with the resources to store
6021188640340442162025691220451771208370039202613309330051794368412 Terabyte
 definitely has enough money to get a custom made database system for his purposes as well.

I might be available as a contractor, for only 0.01€ per Terabyte.


What drawbacks are there in scheduling an event to run every too often,say after every 60 seconds?

A server has very many events happening every minute.  Having an event that happens every 60 seconds is not automatically too frequent

If the event runs quickly and affects a small amount of data, then there should be no particular worry.  The bigger the impact of the event (CPU,I/O,memory,etc) the more cautious you need to be about scheduling.


Stop SQL Server service(s) before defragmenting drive?

Your problem is not disk fragmentation. Your problem is RAM and application table scans:

  4GB RAM ... 68GB ... Page life expectancy 20 seconds

You need way more RAM. As in your new server should have way, way, way, way more than 12GB. Start with 64 GB, it costs basically dimes. And yes, fix your app to use indexes. 20 seconds is very clear indication of table scans that trash the buffer pool. You need to fix the app, add the required indexes and fix your queries. For your case defragmenting the drives is as much a red herring as red herrings get.

Oh, and please move the log to separate physical spindles from data. Alone.


SQL Server 2012 - How to Remove Partition on Publisher but not Subscriber?

You can override the way DELETE commands are replicated to the subscriber by using the @del_cmd parameter for sp_addarticle. You can specify a custom stored procedure that takes care of the archive logic or ignore DELETE commands altogether by setting it to NONE.

However, the rows kept at the subscriber won't be replicated any more if for some reason you re-initialize the subscription, which I suppose is not exactly desirable.

I don't think that replication is the right way to skin this cat. Probably a custom SSIS package would do the trick more cleanly.


Enable or disable a task based on parameter

Actually this is way simpler than I thought.

Right-click the task and choose Parametrize...
Choose the property to be parametrized. From there you can choose an existing parameter or create a new one. It is easy-peasy.

In this case I created a boolean parameter and gave it the default value of FALSE. Then I bound it to the property Disable of the task. When the task is disabled it skips that task but any connections after the task continues.