SQL Server Agent - Run Query at the end of each month with date variable

To find the first and last days of the month:

DECLARE @MonthStart DATETIME
DECLARE @Date  DATETIME
DECLARE @next DATETIME
-- create the required date. because today is the 21st i subtracted month.
-- since you're running the job on the first day of the new month,
-- you may want to change MONTH to DAY. however, if the job fails that change
-- that may cause you some problems
SET @Date = DATEADD(MONTH,-1,DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))

-- get the first day of the month of the date specified
SET @MonthStart = DATEADD(DAY, 1, @Date - DAY(@Date) + 1) -1

-- get the first day of next month
SET @Next = DATEADD(MONTH,1,@MonthStart)

-- prove to yourself that these dates are correct (sanity check)
SELECT @MonthStart, @next

I've laid it out like this for you so that you can follow the logic, you can edit to your needs.

As an aside, the NOT IN convention is somewhat of an antipattern and some experts recommend against using BETWEEN.

LEFT JOIN

INSERT INTO [SupportTracker].[dbo].[DashboardRecords]
SELECT [bg_id]
      ,[bg_short_desc]
      ,[bg_reported_date]
      ,[bg_status_updated_date]
      ,[us_firstname]
      ,[us_lastname]
      ,[LastUpdatedUserFirstname]
      ,[LastUpdatedUserLastname]
      ,[st_name]
      ,[pr_name]
      ,[ct_name]
      ,[pj_name]
      ,[AssignedUserFirstname]
      ,[AssignedUserLastname]
      ,[bg_project]
      ,[no_of_hours]
      ,[BugType]
      ,[SubType]
      ,[Device]
      ,[pj_parent_id]
FROM [SupportTracker].[dbo].[ViewIssueListwBugTypeNDevice] b
LEFT JOIN [SupportTracker].[dbo].[DashboardRecords] d
    ON b.bg_id = d.bg_id
WHERE b.bg_reported_date >= @MonthStart and b.bg_reported_date < @Next
    AND d.[bg_short_desc] IS NULL

(this assumes [bg_short_desc] is a non-nullable field in the [DashboardRecords] table.

EVEN BETTER would be

NOT EXISTS

INSERT INTO [SupportTracker].[dbo].[DashboardRecords]

SELECT [bg_id]
      ,[bg_short_desc]
      ,[bg_reported_date]
      ,[bg_status_updated_date]
      ,[us_firstname]
      ,[us_lastname]
      ,[LastUpdatedUserFirstname]
      ,[LastUpdatedUserLastname]
      ,[st_name]
      ,[pr_name]
      ,[ct_name]
      ,[pj_name]
      ,[AssignedUserFirstname]
      ,[AssignedUserLastname]
      ,[bg_project]
      ,[no_of_hours]
      ,[BugType]
      ,[SubType]
      ,[Device]
      ,[pj_parent_id]
FROM [SupportTracker].[dbo].[ViewIssueListwBugTypeNDevice] b
WHERE b.bg_reported_date >= @MonthStart and b.bg_reported_date < @Next
    AND NOT EXISTS (SELECT 1 FROM [SupportTracker].[dbo].[DashboardRecords] d
        WHERE b.bg_id = d.bg_id);

Also, why are you ordering your inserts? Ordering would be more useful when you're selecting from the DashboardRecords table, right?


Regarding "Dynamic", the non-compressed Barracuda-only format, very little has changed from compact, mainly on how blobs (and any very dynamic fields) are stored. I have never had any issues with compact vs. dynamic, so I can safely recommend Barracuda's dynamic. Remember that Barracuda also supports old redundant and compact row formats.

The article you are mentioning is probably too old (5.1) and, as Peter Z., Percona's CEO, mentions on the comments it may be a bit misleading. That doesn't mean that compression can't be a huge gain depending on the workloads. However, I would recommend you to try it on versions >= 5.6, as both Facebook and Oracle have done lots of improvements about it.

As more recent reference materials, I would recommend you:

Getting InnoDB Compression Ready for Facebook Scale
InnoDB Compression Present and Future
Benchmarking InnoDB page compression performance

In particular, I like Facebook materials as they are third party (no need for an agenda) and they have one of the largest MySQL deployments in the world. As you can see they have had very successful setups combining SSD technology with compression.

Will it benefit you? That will depend on your workload, working set and setup (IOPS, memory). Depending if you are IO bound, CPU bound or memory bound, compression can affect negatively in some cases, by adding extra CPU, memory requirements (both compressed and uncompressed pages are stored on the InnoDB buffer pool) or generating too many compression failures, augmenting the latency. It also depends on the type of data: compression can help a lot with large text blobs, but it may be useless with already-compressed data.

In my experience, in practice, there are people for which compression was a the holy grail of performance and are very happy with it, but on other cases, we had to revert to uncompressed data as no gain was obtained. While a very heavy writing workload may seem like a bad environment for compression, if in your particular case you are not cpu-bound and memory-bound, but iops-bound it may be none-less helpful.

In general, it is very difficult to predict results, usually you should setup a test environment for benchmarking and then discover why you get better or worse results (and that way you can play with different block sizes, etc.). Barracuda is completely safe. Compression may or not be for you. And you can always experiment with other compression methods like client-side compression of blobs (for example, if you end up CPU-bound) or other 3rd party engines like RocksDB and TokuDB, in which compression is a big priority, as it is focused in performance for larger datasets than InnoDB can handle.

In a nutshell: Main reasons to use Barracuda are BLOB handling, innodb_large_prefix compatibility (large indexes) and compression. Dynamic, on MySQL 8.0 is now the default file format.


set date value based on other row values

You can calculate the Delay in a cross apply and use dateadd() to add it to st_date in the main query.

select D1.Split3_ID,
       D1.CU_ID,
       D1.order_id,
       D1.st_date,
       D1.sku,
       D1.Priority,
       D1.Delay,
       dateadd(day, D2.Delay, cast(D1.st_date as date)) as CourseDate
from dbo.set_dates as D1
  cross apply (
              select isnull(sum(D2.Delay), 0)
              from dbo.set_dates as D2
              where D1.order_id = D2.order_id and
                    D1.st_date = D2.st_date and
                    D1.Priority > D2.Priority
              ) as D2(Delay)

SQL Fiddle

Update:

I am not really sure how you want to deal with different st_date within a order_id. The above query groups on order_id and st_date but I guess an alternative would be to use the lowest st_date per order_id in the dateadd function.

select D1.Split3_ID,
       D1.CU_ID,
       D1.order_id,
       D1.st_date,
       D1.sku,
       D1.Priority,
       D1.Delay,
       dateadd(day, D2.Delay, cast(isnull(D2.st_date, D1.st_date) as date)) as CourseDate
from dbo.set_dates as D1
  cross apply (
              select isnull(sum(D2.Delay), 0),
                     min(D2.st_date)
              from dbo.set_dates as D2
              where D1.order_id = D2.order_id and
                    D1.Priority > D2.Priority
              ) as D2(Delay, st_date)
order by D1.Split3_ID

Update 2:

The update statement for the second query could look like this.

with C as
(
select D1.CourseDate,
       dateadd(day, D2.Delay, cast(isnull(D2.st_date, D1.st_date) as date)) as NewCourseDate
from dbo.set_dates as D1
  cross apply (
              select isnull(sum(D2.Delay), 0),
                     min(D2.st_date)
              from dbo.set_dates as D2
              where D1.order_id = D2.order_id and
                    D1.Priority > D2.Priority
              ) as D2(Delay, st_date)
)
update C
set CourseDate = NewCourseDate

Update 3:

Or even like this if st_date is guaranteed to be the same within one order_id.

update D1
set CourseDate = dateadd(day, (select isnull(sum(D2.Delay), 0)
                               from dbo.set_dates as D2
                               where D1.order_id = D2.order_id and
                                     D1.Priority > D2.Priority), D1.st_date)
from set_dates as D1


SHOW CREATE PROCEDURE procedure_name not showing the procedure content

It just dawned on me. You must be using MySQL 8.0

I just heard about this headache not too long ago.

The SHOW CREATE PROCEDURE for MySQL 8.0 does not show a body column anymore.

The design of INFORMATION_SCHEMA.ROUTINES is also different for MySQL 8.0.

Someone wrote about this 4 months ago (See No more mysql.proc in MySQL 8.0). This paradigm will take getting used to. Personally, I am not comfortable with it, but we need to just get used to it.

You also gotta wrap your head around INFORMATION_SCHEMA.PARAMETERS, which itemizes each parameter passed to a stored procedure.

Just to skip all this other yucky stuff, MySQL 8.0 FAQ says the following:

  Stored procedures are stored in the mysql.routines and
  mysql.parameters tables, which are part of the data dictionary. You
  cannot access these tables directly. Instead, query the
  INFORMATION_SCHEMA ROUTINES and PARAMETERS tables. See Section 25.19,
  “The INFORMATION_SCHEMA ROUTINES Table”, and Section 25.12, “The
  INFORMATION_SCHEMA PARAMETERS Table”.

  You can also use SHOW CREATE FUNCTION to obtain information about
  stored functions, and SHOW CREATE PROCEDURE to obtain information
  about stored procedures. See Section 13.7.6.9, “SHOW CREATE PROCEDURE
  Syntax”.

I will leave it to you to reserch the rest of this.


Login mapping only works for the first query in a window

Considering that you are using the following SQL:

SELECT * FROM view EXECUTE AS LOGIN = 'loginname'

This is actually 2 different statements:

SELECT * FROM view;
EXECUTE AS LOGIN = 'loginname';

The SELECT statement doesn't have a clause to impersonate, you have to impersonate before issuing the SELECT statement and the impersonation remains on the current session until you execute a REVERT. So the proper SQL should have been:

EXECUTE AS LOGIN = 'loginname';
SELECT * FROM view;
REVERT; -- (if you want to revert to your original login)

As you mentioned in a comment, the error message was being generated by the view that you were querying, as it was using a linked server that wasn't correctly configured with a login against the remote server in which the impersonated login had access. If the current login is sysadmin, the linked server won't require a remote login-mapping mechanic (this can be configured), so the query always succeeded the first time.


ASPECT #1
The message you saw did not say

"Table does not support optimize, do recreate + analyze instead
OK"

but the message said

"Table does not support optimize, doing recreate + analyze instead
OK"

This means the InnoDB Storage Engine already executed
ALTER TABLE GEO ENGINE='InnoDB';
ANALYZE TABLE GEO;

There was no need to do it again.
ASPECT #2
There is a reason why fragmentation still exists
If the primary key of the table is an integer with auto_increment, all the numbers generated for the primary key are in ascending order.
When the primary key was being loaded into the BTREE index, fragmentation was made. Why?
You may find this surprising, but I wrote an answer to a post about 3 years ago entitled How badly does innodb fragment in the face of somewhat out-of-order insertions?
In that post I explained how AVL binary trees will cause height rotation about 45% of of the time. For a BTREE, this would be represented as BTREE page splits. What does means for sorted data ?

When loading data in ascending order, tree pages that split to the left would always be half full
When loading data in descending order, tree pages that split to the right would always be half full

Those half full pages is what is manifesting the fragmentation.
This is further illustrated in another post I wrote last year (Why would the size of MySQL MyISAM table indexes (aka MYI file) not match after mysqldump import?) where I mentioned this same issue for MyISAM.
CONCLUSION
The only way to really reduce fragmentation would be to drop all indexes and recreate the indexes. If the tables are gigantic, this is not worth your time.


Automating database restoration of SQL Server transaction log files on standby mode

Keep in mind that the first time your client does full backup, you will not be able to restore log files before full backup restore, so that step should be implemented in your automation.


Query is slow in SQL Server 2014, fast in SQL Server 2012

The most likely situation is that the new SQL 2014 Cardinality Estimator is yielding a poor row estimate for one or more joins in your query and this has led SQL Server to choose an inefficient plan.

If you are able to run the query in SQL 2014 with "include actual execution plan" turned on, you can use the query below in another tab to view the real-time progress of rows flowing through each query operator.  I noticed that you only have an estimated plan for 2014 (compared to an actual plan for 2012), presumably because you cannot run the query to completion in SQL 2014.  So this could give you more insight into the actual rows flowing through the query in 2014 and may lead you to a way of tweaking the query that runs efficiently using the new Cardinality Estimator.

In the meantime, until you are able to optimize the query you could use QUERYTRACEON with trace flag 9481 for this query or you could follow Brent Ozar's advice of running the database at the SQL 2012 compatibility level, carefully testing your queries with the new Cardinality Estimator, and only updating the compatibility level to 120 (SQL 2014) once satisfied with these results.

/* Live query progress in SQL 2014 */
SELECT session_id,node_id,physical_operator_name, SUM(row_count) row_count, SUM(estimate_row_count) AS estimate_row_count,
    CAST(SUM(row_count)*100 AS float)/NULLIF(SUM(estimate_row_count),0) AS percent_complete,
    SUM(elapsed_time_ms) AS elapsed_time_ms,
    SUM(cpu_time_ms) AS cpu_time_ms,
    SUM(logical_read_count) AS logical_read_count,
    SUM(physical_read_count) AS physical_read_count,
    SUM(write_page_count) AS spill_page_count,
    SUM(segment_read_count) AS segment_read_count,
    SUM(segment_skip_count) AS segment_skip_count,
    COUNT(*) AS num_threads
FROM sys.dm_exec_query_profiles
WHERE session_id <> @@spid
GROUP BY session_id,node_id,physical_operator_name
ORDER BY session_id,node_id;


Why distributed AG is preferred to normal AG for cross datacenter failover scenario?

what are benefits of using distributed AG compared to normal AG?

Complete isolation of your clusters is the only benefit.

  Can we just stretch WSFC across data center and make this happen with normal AG?

Yes, and you normally should do just that.  Stretch clusters are not complicated, and modern versions of Windows make them downright simple.  IE the cluster node voting configuration and quorum is pretty much automatic.  And you can even implement automatic cross-datacenter fail-over with Cloud Witness.


Should credits be stored as negative numbers in a double-entry bookkeeping database?

I've worked for a long time with a range of different types of accounting systems (albeit predominantly with European accounting standards) and the norm is that you use positive numbers for debit entries and negative numbers for credit entries. This means that your assets are positive and your liabilities negative, but your income is negative and costs are positive. So when you're building income statement-related reports you'll need to invert the numbers.

I've seen instances of systems that use separate columns for debit and credit, but to me that is just anti-normalization. Typically, most of those systems also provide a "total" column, which is positive in debit and negative in credit.

As you say, this way every transaction has to balance out to zero (you can probably write some type of constraint to make sure it does).


Alter failed for schema 'dbo' when trying to change schema owner

You can't change the owner of "dbo" because it is a system schema. From ALTER AUTHORIZATION, "Special Cases and Conditions":

  SCHEMA
  ... Cannot change the owner of sys, dbo, or information_schema

And you can't set a default schema for windows logons (yet).

This matters in 2 ways:

you are issuing DDL (CREATE TABLE) etc when you want objects to be in dbo
you won't have reusable query plans for DML without schema so different users will have different query plans

Basically, you have two options:

fix the queries
stop using ODBCDirect from MS Access to SQL Server.

The third option, ugly and dangerous, is to make the Windows logon "sysadmin" which means they will always map to dbo. I don't recommend this

Personally, I don't use the dbo schema any more: see dba.se (and the further links there)


What is the fastest way to verify that a json[] array is not empty?

This is not returning a JSON Array,.

SELECT '{}'::json[]

The type json[] is not a "JSON Array", it's a SQL Array of type JSON. You should never be using this kind of structure. It's almost certainly a red flag of bad design.

What you want to check is that a Real JSON Array is not empty, you can do that with jsonb_array_length(jsonb). JSON is stored as JSON, or JSONB for binary json.

SELECT jsonb_array_length( '[]'::jsonb );

To see if it's empty,

SELECT jsonb_array_length( '[]'::jsonb ) = 0;

Warning, this just counts those elements, they may be null,

SELECT jsonb_array_length( '[null]'::jsonb );

So you may want to call jsonb_strip_nulls() on the array first.


Can the FORCESEEK query hint be used on tables being queried against on a linked server? (I.e. as part of a distributed query)

You CANNOT use FROCESCAN or FORCESEEK on remote data sources.

Reference: Hints (Transact-SQL) - Table

The FORCESCAN hint has the following restrictions:

  The hint cannot be specified for a remote data source.

When using the FORCESEEK hint (with or without index parameters), consider the following guidelines:

  The hint cannot be specified for a remote data source. Error 7377 is
  returned when FORCESEEK is specified with an index hint and error 8180
  is returned when FORCESEEK is used without an index hint.


How to express functional dependency in relational calculus and SQL?

Not knowing your full situation, I recommend something like this. The foreign keys should create the dependencies you need. If your SQL structure is already in a tabular format, you might consider putting this information into temporary tables--like the ones below. But it looks like your tables are there according to your assertion comment.

From what you mentioned you'll need to connect the Candidate, Poll, and Elector tables to the Vote table with a foreign key (with the specific requirements you need--you can easily do this in the SQL Server Studio GUI as well. You'll need to do the same thing with the Elector table and the Poll table via a foreign key within Elector that points to the Poll table. Use the following below as a template/base--add/subtract as needed...

create table Candidate
(
CandidateID int primary key,
Name varchar(255) null,
PoliticalGroup varchar(255) null
)
create table Poll
(
PollID int primary key,
Date datetime null,
Round float null
)
create table Elector
(
ElectorID int primary key,
Name varchar(255) null,
FK_PollingStationID int null
)

create table Vote
(
VoteID int primary key,
FK_PollID int null,
FK_CandidateID int null,
FK_ElectorID int null
)

ALTER TABLE dbo.Elector ADD CONSTRAINT
    FK_Elector_Polling FOREIGN KEY
    (
    FK_PollingStationID
    ) REFERENCES dbo.Poll
    (
    PollID
    )

GO

ALTER TABLE dbo.Vote ADD CONSTRAINT
    FK_Vote_Candidate FOREIGN KEY
    (
    FK_CandidateID
    ) REFERENCES dbo.Candidate
    (
    CandidateID
    )

GO
ALTER TABLE dbo.Vote ADD CONSTRAINT
    FK_Vote_Poll FOREIGN KEY
    (
    FK_PollID
    ) REFERENCES dbo.Poll
    (
    PollID
    )

GO
ALTER TABLE dbo.Vote ADD CONSTRAINT
    FK_Vote_Elector FOREIGN KEY
    (
    FK_ElectorID
    ) REFERENCES dbo.Elector
    (
    ElectorID
    )

GO


Oracle: Keeping exact db clone synced up in real time

Short answer: Data Guard.
Note, this isn't "Active" Data Guard, which costs ready money, but good, "old-fashioned" Data Guard.
It's a Recovery technology, intended to keep standby databases up-to-date and supports three "flavours":
A Physical Standby is only for Recovery purposes, giving you a complete, separate copy of your database that you can switch/fail over to in the event of the Primary database going "Pop!" (and can even happen automatically, if you configure an Observer).  Transport and redo apply can be real-time, depending on how the Data Guard configuration is set up.
Downside is that it's only for Recovery purposes and you can't do anything "useful" with it, like query it.  (BTW, that's the extra bit that "Active" Data Guard provides - it "cons" a Physical Standby database into allowing queries).
A Logical Standby is probably what you want.
Logical standbys are meant to be readable, all the time.
Downsides are that the transport mechanism is a bit different and can be a bit slower, (transport and apply lag in the handfuls of seconds instead of real-time), some [structural/DDL] changes don't get carried through (so you have to apply them in multiple places) and some of the more "esoteric" Data Types aren't supported (although that list shrinks with every version).
If you wanted to, you could even create a "farm" of Logical Standbys, accessed by clients in a round-robin fashion (as defined in their TNS Naming files), to spread the "read" load even further.  (Possibly overkill, given how powerful Oracle Database is, but it's another option.  YMMV).
A Snapshot Standby (just for completeness, here) is a "halfway-house" between the two.
Most of the time, it behaves like a regular database, allowing queries to be run against it.  Then, periodically, you close it, "plug" it back into the Data Guard infrastructure (which silently converts it into an unreadable, Physical Standby) and it will automatically receive and apply redo logs from the Primary.  Then, when you need to use the Snapshot again, you "unplug" it and open it (which silently converts it into a [readable] Snapshot Standby) with data as of the Point-in-Time of the "unplugging".
Useful if you wanted, say, a daily snapshot of your financial transactions to support offline data analytics.
Redo Apply is fast and a whole day's worth of redo can easily be applied in a couple of hours (or less) but, of course, you have to have the extra disk space to store it until it gets applied.
Not for everybody, admittedly, but it's another option.


try to install\upgrade, Assuming you have Ubuntu on your machines then execute the following commands:

Upgrade:

sudo apt-get update
sudo apt upgrade

Install:

sudo apt install zsh
sudo apt-get install powerline fonts-powerline

If you have Fedora:

sudo dnf install zsh

MacOS

brew upgrade postgresql


Implementing Feature max limit (Logical)

Let's analyze how much effort COUNT(*) will take...

For those numbers, either way will probably work.  Counting 500 rows is likely to take only a fraction of a second.  If you don't need to do that a million times a day, you probably won't run out of performance.

Either approach requires performing some form of test every time the user takes a ticket, so this does not seem to be a limiting factor.


DB Engine Stolen Server Memory is too high

How many times do you see such messages? If it is once or twice it can be ignored.

'Stolen memory' as described in this older support article and How to use the DBCC MEMORYSTATUS is:

  Stolen memory describes buffers that are in use for sorting or for hashing operations (query workspace memory), or for those buffers that are being used as a generic memory store for allocations to store internal data structures such as locks, transaction context, and connection information. The lazywriter process is not permitted to flush Stolen buffers out of the buffer pool.

The memory is usually taken from Buffer Pool. If you run DBCC MEMORYSTATUS and the output shows you high Stolen Pages, this means that some process is stealing memory from buffer pool more that what is necessary and you need to find that process.

It's quite likely that there was some operation running which performed a huge sort operation and at same time SQL Server was heavily consuming memory thus leading to this message. Unless you are facing some OOM issue this message would be  more considered as a warning.


sp_blitzfirst results to table on a linked server

There is a note in the script on GitHub:

  Known limitations of this version:

  ...

  @OutputServerName is not functional yet.

And indeed, that parameter is not used in the proc.  So for now, storing the output to a table across a linked server appears to not be supported.

Since you included the SQL Server 2008 tag in your question, I'll also mention that they don't go out of their way to keep the scripts working on unsupported versions of SQL Server, and you're only a few months away from that end of support date (July 2019!).  I imagine the scripts will continue to work for a while, but there are no guarantees at that point.


You will also need to configure an additional TCP endpoint inside of SQL Server itself. If you run a query of

SELECT * FROM sys.tcp_endpoints;

I would expect you to see the default endpoint, as well as the Dedicated Admin endpoint. You will need to add another endpoint:

USE master;
GO
CREATE ENDPOINT [MyNewTCPEndpoint]
STATE = STARTED
AS TCP
   (LISTENER_PORT = YourTCPPort, LISTENER_IP =ALL)
FOR TSQL() ;
GO

And then you may need to re-grant access to the default endpoint

GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [public];

See the Microsoft documentation on configuring SQL for multiple TCP ports for full details.


Set Parameter in SPFILE still outputs the same error

Digging further: Your error indicates that you are using a container/pluggable database (CDB/PDB) architecture for your Oracle Database. The SAP IDM installation guide specifically says not to do this:

When installing the Identity Management database on Oracle, note the following prerequisites and recommendations:...
...make sure the Create as Container database option is not selected

https://help.sap.com/viewer/d77277f42c0b469db8794645abd954ea/8.0/en-US/571119cc37ce465b93de726fc0408427.html
Based on this, you will most likely need to recreate your database from scratch and make sure it is configured as a traditional instance, and not as container/pluggable database architecture.


MySQL Database Schema for Train Timetables

I would strongly consider explicitly storing all the days a particular schedule actually runs on. This would give a structure looking something like this:

By doing so, you'll make it much easier to answer questions such as "What are all the trains going to station X on 1 Oct?". It'll also makes "temporary gaps" when trains aren't running (e.g. Christmas day) possible to identify. A one-off train is now simply one with only one entry in SCHEDULE_DAYS.

As the schedule can be different on weekends to weekdays, I think it's better to have separate rows for each day. This allows linking different schedules for every day of the week, should you ever need to do this.


Copy & rename db so I can attach both copies

If You want to use the same directory for your mdf\ldf files You will have to (physically) rename them.
1. Detach files from local PC
2. Rename mdf and ldf files
3. Copy\Move the files to the server directory where the original Database is stored.
(In My example I used D:\Data for mdf and E:\Logs for ldf, change the path before running the script)
4. Use this code:

USE [master]
GO
CREATE DATABASE [myNewSite_db] ON
( FILENAME = N'D:\Data\myNewSite_db.mdf' ),
( FILENAME = N'E:\Logs\myNewSite_db_log.ldf' )
FOR ATTACH
GO

5. If You want to change the logical file name to be the same as the physical one use:
(Assuming the original logical file name was "mysite_db")

USE [myNewSite_db]
GO
ALTER DATABASE [myNewSite_db]
    MODIFY FILE (NAME=N'mysite_db', NEWNAME=N'myNewSite_db')
GO
ALTER DATABASE [myNewSite_db]
    MODIFY FILE (NAME=N'mysite_db_log', NEWNAME=N'myNewSite_db_log')
GO

Good luck,
Roi


Will SQL Server go offline if it loses network connectivity to SAN where master and msdb system databases reside?

You can't depend on the system databases being cached in memory, more likely they won't be because of their less frequent access relative to the user databases.
I think you'll end up in a quasi-functioning state, where your user databases will still be accessible but certain features of the server instance that rely on master and msdb will throw some weird errors, depending on what else your server is doing. The service for your SQL Server instance, should continue to remain online ("started" state). For example, if you have any Scheduled Agent Jobs, I would bet my money (but couldn't say for sure without testing) they'd encounter errors (either silently or apparently) when trying to run since most of their meta-data is stored in the msdb database.
If such was to occur, you'd be best to restore access to those system databases as soon as possible to guarantee 100% reliability in all features and functions.


Solution for the deletion of old Database Backups for Linux SQL Server 2017 after Backups created with Ola Hallengren scripts?

I don't know if you already found a solution to your problem, but as of this moment, the extended system stored procedure xp_delete_files is supported on SQL Server 2017 when hosted on a Linux machine.

You can edit Ola's script for database backup and remove the check when the cleanup parameter is being verified, to allow usage on a Linux machine.

I've tried that and the removal of old backups functions just ok, as when run on Windows.


It is possible to get to the desired result in a simpler way.

First, you could start with this simple query:

SELECT
  t1.id,
  t2.env,
  CONCAT(t3.inftype,
         ': [',
         GROUP_CONCAT(t3.infname ORDER BY t3.infname SEPARATOR ', '),
         ']'
        ) AS inftypeandnames
FROM
  table1 AS t1
  INNER JOIN table2 AS t2 ON t1.id = t2.id
  INNER JOIN table3 AS t3 ON t2.infid = t3.infid
GROUP BY
  t1.id,
  t2.env,
  t3.inftype

This is what you would get as the result for your data samples:

id  env  inftypeandnames
--  ---  ---------------
1   D    Db: [kli]
1   D    Srv: [pop]
1   P    Db: [hgj, kjk]
1   P    Srv: [abc, xyz]

Next, you could use the above result as a derived table and group it by id, env, concatenating the inftypeandnames values, like this:

SELECT
  id,
  env,
  GROUP_CONCAT(inftypeandnames ORDER BY inftypeandnames DESC SEPARATOR ' ') AS inftypesandnames
FROM
  (
    SELECT
      t1.id,
      t2.env,
      CONCAT(t3.inftype,
             ': [',
             GROUP_CONCAT(t3.infname ORDER BY t3.infname SEPARATOR ', '),
             ']'
            ) AS inftypeandnames
    FROM
      table1 AS t1
      INNER JOIN table2 AS t2 ON t1.id = t2.id
      INNER JOIN table3 AS t3 ON t2.infid = t3.infid
    GROUP BY
      t1.id,
      t2.env,
      t3.inftype
  ) AS s
GROUP BY
  id,
  env

The result would be very close to what you want:

id  env  inftypesandnames
--  ---  ------------------------------
1   D    Srv: [pop] Db: [kli]
1   P    Srv: [abc, xyz] Db: [hgj, kjk]

In order to split the inftypesandnames values into separate columns based on the values of env, you could duplicate the above query, apply a filter on env to each copy (env = 'D', env = 'P' and so on if there can be others), join the results and return each subset's inftypesandnames value in its own column – same as Rolando has suggested, although I would argue that you might need a full outer join rather than an inner join if it is possible for some ids to have only D while for some others only P. Since MySQL does not support the FULL JOIN / FULL OUTER JOIN syntax and the usual workaround to that is a union of a left join and a right join, I suspect the final query in this case would make Rolando's brain hurt even more.

However, there is no need to resort to duplicating subqueries like that, because you can pivot the results using conditional aggregation. Instead of grouping by id, env in the outer query, you would be grouping by id only and conditionally apply GROUP_CONCAT to the inftypeandnames values based on env, like this:

SELECT
  id,
  GROUP_CONCAT(CASE env WHEN 'P' THEN inftypeandnames END ORDER BY inftypeandnames DESC SEPARATOR ' ') AS P,
  GROUP_CONCAT(CASE env WHEN 'D' THEN inftypeandnames END ORDER BY inftypeandnames DESC SEPARATOR ' ') AS D
FROM
  (
    SELECT
      t1.id,
      t2.env,
      CONCAT(t3.inftype,
             ': [',
             GROUP_CONCAT(t3.infname ORDER BY t3.infname SEPARATOR ', '),
             ']'
            ) AS inftypeandnames
    FROM
      table1 AS t1
      INNER JOIN table2 AS t2 ON t1.id = t2.id
      INNER JOIN table3 AS t3 ON t2.infid = t3.infid
    GROUP BY
      t1.id,
      t2.env,
      t3.inftype
  ) AS s
GROUP BY
  id
;

You can verify for yourself that the result of the last query would match your requirements:

id  P                               D
--  ------------------------------  --------------------
1   Srv: [abc, xyz] Db: [hgj, kjk]  Srv: [pop] Db: [kli]


Regarding Always On Licensing (Secondary replica no license required)

"Readable Secondary" AG setting for all the replicas associated to
  Always On(including current Primary replica) should have setting
  either as “NO” or “Read-Intent Only” or it should be "NO" only?
  There should not be any Standalone Database? (not even any third party
  backup tool database such as "LiteSpeedLocal").
  Also the backup should not be configured to run on Secondary replica. Otherwise, the Secondary replica will not be truly passive. Is it true?

Must be set to "No" so that it is not readable. From the licensing data sheet:

  The secondary replica used for failover support does not need to be
  separately licensed for SQL Server as long as it is set to ‘not
  readable’. If it is readable or serving data, such as reports to
  clients running active SQL Server workloads, or performing any “work”
  such as additional backups from secondary servers, then it must be
  separately licensed for SQL Server.

A standalone database doesn't violate the passive license unless it is used for something, anything. No backups, no queries, no integrity checks, index or statistics maintenance. All of it would constitute production use and violate the terms. Easier to not have any standalone databases.
If you run backups against the secondary it will violate the passive licence terms and require a paid licence.

Further information can be found in the licensing guide and licensing data sheet. Note that this information is for SQL Server 2017. It hasn't varied too much since 2012, but best to validate with your specific version and run any concerns past your licensing reseller for official confirmation.


How to downgrade SQL Server 2012 Enterprise to 2012 Express?

The very first thing you would need to check on that instance would be for all the databases hosted, using any enterprise features from sys.dm_db_persisted_sku_features. In case the database is using any of such enterprise features, you will need to thoroughly verify , but not sure if express can get you there and most probably downgrade wont be that easy.

In addition its better to go through Supported Version and Edition Upgrades while you consider to change the edition and compare features.

There is no direct way to do the downgrade but you can follow the steps of copying the system databases of the enterprise edition instance to the instance of expressed one.Also making sure the new instance being the same with latest update to date with same patch details.

Please refer to below articles which will help you in analyzing how to analyse the downgrade process.

Downgrade enterprise SQL server to standard ( Steps can be somewhat same for express edition as well, just look for features that will support you're application in case you go with express)

Changing SQL Server Editions: Standard, Enterprise, Evaluation and More

Also, make note is good to test it out first for one of you're small servers on a test ENV with such downgrade activity.


Getting SELECT to return a constant value even if zero rows match

SELECT col1,
       col2,
       col3,
       1 AS query_id
FROM players
WHERE username='foobar'
union all
select null,
       null,
       null,
       1
where not exists (select 1 from players where username = 'foobar');

Or as an alternative (might be faster as no second subselect is required):

with qid (query_id) as (
   values (1)
)
select p.*,
       qid.query_id
from qid
  left join players as p on (p.useranme = 'foobar');

You can re-write the above to a more "compact" representation:

select p.*,
       qid.query_id
from (values (1)) as qid (query_id)
  left join players as p on (p.useranme = 'foobar');

But I think the explicit CTE (with...) is more readable (although that is always in the eyes of the beholder).


SQL Server 2005 and Windows 8.1 - Getting error 1053 when trying to start sqlexpress service

SQL Server Express does not have SQL Server Agent, so it won't start, no matter how hard you try.

Which service does not start? SQL Server or SQL Server Agent? For SQL Server, please refer to this great article by Gail Shaw.


Show clients with staff assigned and no staff assigned

You are using deprecated syntax for joining tables that doesn't allow for OUTER joins.

I'm not 100% sure what you're trying to accomplish because I can't see your data, but it seems to me like you're performing JOINS when you might want LEFT OUTER JOINS.

First thing to do is to rewrite your query using JOIN keywords.

SELECT
  g.name AS group,
  cl.name_lastfirst_cs AS client
FROM
  clients cl
JOIN
  link_group lg
ON
  cl.zzud_client = lg.zrud_client
JOIN
  groups g
ON
  lg.zrud_group = g.zzud_group
WHERE
  lg.zrud_group = ?
ORDER BY
  cl.name_lastfirst_cs

Next, try using LEFT OUTER JOINs.

SELECT
  g.name AS group,
  cl.name_lastfirst_cs AS client
FROM
  clients cl
LEFT OUTER JOIN
  link_group lg
ON
  cl.zzud_client = lg.zrud_client
LEFT OUTER JOIN
  groups g
ON
  lg.zrud_group = g.zzud_group
WHERE
  lg.zrud_group = ?
ORDER BY
  cl.name_lastfirst_cs

Here is a decent visual representation of the different kinds of JOINS using Venn diagrams: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html


You must install Oracle as a non-root user.  Generally, that user would be called oracle, but it can be anything, as long as it's not root.


Is is possible to manage SQL Server 2008 databases via R2 Management Studio?

Yes, it is possible to use the SSMS from R2 to connect to a database server from any of the previous versions (I'm not sure about version pre SQL 2000, but it's not your case anyways).

You won't have all possible features activated (like Utility Explorer), but that shouldn't stop you to use it properly.


After delete audit table doesn't have the rows that were deleted

After delete trigger is executed after the record has been removed from the table.

Joining a table Emp will yield no result because record with that ID does not exist in that table.

Also note that inserted table will always be empty in after delete trigger.


What is the recommended Buffer Pool size on a 2GB RAM PC?

The default size is 4K. You can also specify 8K, 16K, or 32K. The size of the buffer pool should really be based on the size of your tables (ie, how many bytes is it possible for one row of data to hold). If a row of your data won't fit into a 4K page, then it won't fit into a 4K buffer pool. And hence you would need larger.

The same is also true of table spaces (the "physical containers" that your tables are stored in). A table that needs an 8K buffer pool, needs to reside in an 8K table space. And actually, you would then associate that table space to the buffer pool as well....

That being said, don't just "oversize" and go to 32K table spaces and buffer pools if you really only have 4K tables. You will waste a lot of disk space and memory then.

Also, you can have as many buffer pools as you want (I'm sure there is an upper limit, though I wouldn't know off hand). But you can have any combination of them. You could have five 4K pools, one 8K pool, one 16K pool, and three 32K pools.

The biggest thing is more based on your table sizes and understanding that as mentioned before.

Although, here is the nice thing....after you create your buffer pools and table spaces, DB2 is smart enough to put the table into the correct size table space (pending it exists). You can specify it in your DDL code, but DB2 is smart enough to put an 8K table in an 8K table space (and thus it will also be associated with an 8K buffer pool).

OK...now to answer your last two questions. There is no "recommended" size really. The default of 4K is fine. Most of our tables where I work fit in a 4K table space and 4K buffer pool.

What we tend to do though, as a best practice... We set up our own 4K, 8K and 32K table spaces and buffer pools (including temporary tables spaces for reorgs). Why we don't set up 16K, I'm not sure. But we set up several sizes in all our databases. This way if any developer creates a table that will be bigger than 4K, it will automatically get placed into the appropriately bigger tables pace (and thus buffer pool).

Hopefully this was clear enough for you. I have also found this article helpful with my own understanding of buffer pools and table spaces.


I have to delete over 90 million records, do I remove or maintain the transaction's SQLWARNING?

If you are deleting much of a table it is often better (faster, etc) to copy the table over without the 'old' rows:

CREATE TABLE new LIKE real;
INSERT INTO new
    SELECT * FROM real
        WHERE dt > NOW() - INTERVAL 1 MONTH;
RENAME TABLE real TO old,
             new TO real;
DROP TABLE old;   -- or dump it for archive purposes

Better yet, make that new table PARTITIONed by day so that you can daily do the very fast DROP PARTITION to get rid of the month-old data and REORGANIZE PARTITION to get a new partition for tomorrow.  Details in http://mysql.rjweb.org/doc.php/partitionmaint

If you need to "dump" or "archive" the month-old partition, see the discussion on "transportable tablespaces".  (Note: MySQL 5.6 has one method and newer versions have another.)

Details on other deletion techniques:  http://mysql.rjweb.org/doc.php/deletebig


Levenshtein function returning different result based on limit value

The least() function returns the lesser of it's two arguments; both sets of rows have a perfect match for 'Toyota', which will have a distance of 0. So the ultimate evaluated ORDER BY value for each of the 3 rows is 0.
Adding additional ORDER BY expressions of each of the arguments to least() should do what you want: ORDER BY LEAST( levenshtein(c0.model, 'Camry'), levenshtein(c0.make, 'Toyota')), levenshtein(c0.model, 'Camry'),  levenshtein(c0.model, 'Toyota') will return all Camrys first, then any non-Camry Toyotas, and then rows whose least() expression evaluated to something higher than 0.


MySQL:How to correct a corrupted MySQL binary log on Master server?

A possible solution that comes to mind is to set the master_log_position to the next one:

change master to master_log_position = <next_pos>;
start slave;

Or just use sql_slave_skip_counter = 1.
If that doesn't work, you may try to parse the binlog (if mysqlbinlog is able to parse it ...):

mysqlbinlog filename > script.sql

Edit the script and remove the part up to and including the long statement.
Run the modified script manually on the slave and set the master_logfile
to the next binlog:

change master to master_log_file ...;
start slave;

You may also check, just in case, if both master and slave use the same *max_allowed_packet*.


Assign a unique count to each row in the resulting table of a select statement

You can use ROW_NUMBER, like this:

SELECT
    ROW_NUMBER()OVER(ORDER BY ID ASC) AS [Count],
    body
FROM @Article AS A
WHERE authorid=1

Count                body
1                    Hi
2                    there
3                    foo


Is there a SQL event being throw when the snap manager starts the backup?  If so there could be an alert setup on that event which is setup to start the job.  Check the SQL Agent alerts to see if it is configured to capture anything.


Is it possible to monitor role and privilege grant/revoke using triggers?

You could do that with DDL triggers ([before|after] grant or revoke).

The docs are in the Oracle® Database PL/SQL Language Reference. You should look carefully at the event attribute functions table, and the Event Attribute Functions for Client Event Triggers section to know what information is available in those contexts.

Here's an example trigger (probably incomplete, this is just an example) that would log basic information about grant and revoke events, assuming a log table with appropriate columns:

create or replace trigger trigger1 after grant or revoke on database
  declare
    priv  dbms_standard.ora_name_list_t;
    who   dbms_standard.ora_name_list_t;
    npriv pls_integer;
    nwho  pls_integer;
  begin
    npriv := ora_privilege_list(priv);
    if (ora_sysevent = 'GRANT') then
      nwho := ora_grantee(who);
    else
      nwho := ora_revokee(who);
    end if;
    for i in 1..npriv
    loop
      for j in 1..nwho
      loop
        insert into log values
          (
            systimestamp,
            ora_sysevent,
            who(j),
            priv(i),
            ora_dict_obj_owner,
            ora_dict_obj_name
          );
      end loop;
    end loop;
  end;

Example:

SQL> grant select on log to bar;
Grant succeeded.

SQL> revoke select on log from bar;
Revoke succeeded.

SQL> select * from log;
DT                           WHAT   WHO PRIV   OWN OBJ
25-NOV-12 05.29.19.095403 PM GRANT  BAR SELECT MAT LOG
25-NOV-12 05.29.27.004610 PM REVOKE BAR SELECT MAT LOG


How to get all non deleted messages from one user

AND has higher precedence than OR. Your condition is parsed and executed as:

    WHERE (msg.sender_user_id = 2)
       OR ( rcp.user_id = 2
        AND msg.deleted = 0
        AND rcp.deleted = 0
          )

Perhaps you mean to write:

    WHERE (msg.sender_user_id = 2 OR rcp.user_id = 2)
      AND msg.deleted = 0
      AND rcp.deleted = 0


Using Table as a Variable for a Stored Procedure

You would need to use dynamic sql.  You final select line should be:

declare @sql varchar(max)
set @sql = 'select ' + @colName + ' from ' + @TblName
exec(@sql)


Assuming you have access to the sed command line application, doing something like:

mysqladmin create newdb
mysqldump --routines olddb | sed 's/olddomain.com/newdomain.com/' | mysql newdb

will convert all strings with "olddomain.com" into "newdomain.com" from a dump we get from the database olddb and will import it into a newdb database, which was just created.

You may need to add extra parameters or a defaults client .my.cnf file for the connection options.

Be careful, this is an easy process but a bit dangerous, as it will change the string wherever it is found, be it in on table or column names and comments, and without checking if it is part of a longer url or not. This is why I have make it load it on a separate database so you can check that it is working as intended.

I use this every time to convert between table engines from mysql dumps:

sed 's/ENGINE=PERFORMANCE_SCHEMA/ENGINE=InnoDB/g' p_s_57.sql

Once you have seen that it is correct, just point your production database to the new place or rename your tables (the safest way to do that is again using mysqldump).

There are other options, that could involve scripting by querying information_schema.columns and using UPDATE table SET column = REPLACE(column, 'olddomain.com', 'newdomain.com');. Those could give you more control, but would require a bit of extra scripting.


Update two columns of two tables using inner join

You should break your UPDATE statements into two parts

--Update A
    UPDATE A
      SET
          A.isActive = 0
    FROM OperatorGrouping A
         INNER JOIN OperatorGroupingSteps B ON A.group_id = B.group_id
    WHERE A.group_id = 9;
--Update B
    UPDATE B
      SET
          B.isActive = 0
    FROM OperatorGroupingSteps B
         INNER JOIN OperatorGrouping AB ON B.group_id = A.group_id
    WHERE A.group_id = 9;


How to replace multiple unicode characters

NVARCHAR - VARCHAR conversions

This topic has been discussed heavily in a previous question on DBA.SE

Some parts of the answers on that question:

  which characters can be stored in an 8-bit / non-Unicode encoding
  depends on the code page, which is determined by the Collation.

&

  VARCHAR can no longer be referred to as "non-Unicode". So, starting
  with the first public beta of SQL Server 2019 in September 2018, we
  should refer to VARCHAR as an "8-bit datatype", even when speaking in
  terms of versions prior to SQL Server 2019. This terminology holds
  true for all 4 types of encodings that can be used with VARCHAR:

  Extended ASCII Double-Byte Character Sets (DBCS) EBCDIC UTF-8
  (Unicode)

Solomon Rutzky

  From the comments, I agree "Extended ASCII" is really bad term that
  actually means a code page that maps characters/code points in the
  128-255 range, beyond the standard 0-127 code point range defined by
  ASCII.

Dan Guzman

Depending on your collation, some characters will be able to be stored in varchar / 8-bit datatype fields, some not.

  My values do not transform to '?' when casting to varchar, your fiddle
  uses sql 2017 could that be the reason?

This can depend on collation, and should be due to converting of certain characters

I am using SQL Server 2017, collation Latin1_General_CI_AS

On my test, the Ƒ character was changed to  ƒ instead of put as ? when converting nvarchar --> varchar.

Other than that, there are no different characters when comparing both, on my collation / version.

An example:

SELECT CAST([name] AS VARCHAR(1000)), [name]
FROM dbo.Bla
WHERE [name]
NOT LIKE '%'+CAST([name] AS VARCHAR(1000))+'%';

With the small difference on the F's.

(No column name)    name
ORRISON CPA LIMITED  E¦¬Ç¦®EŒÆŒƒÈ¨ˆÅ¸«Äº‹Å‹™Æ‰€ÆŒ‰E™Å…¬Å¸   ORRISON CPA LIMITED  E¦¬Ç¦®EŒÆŒƑÈ¨ˆÅ¸«Äº‹Å‹™Æ‰€ÆŒ‰E™Å…¬Å¸

You could compare the text here

Which makes the solution below not useful for you.

Some workarounds on values that fail to convert would be to

Cast the values as varchar(1000), and then remove the ? after validating that no ?'s exist.
Only update name values that do not have regular ?'s.
Replacing the ? to a value that cannot exist in your data and then
replacing it back.

On the premise that the unicode is changed to a ? when casting them to varchar

#1 Cast the values as varchar(1000), and then remove the ?.

SELECT [name] FROM [dbo].[Table]    WHERE [name] like '%?%';

Update if none found

 UPDATE [dbo].[Table] SET [name] = REPLACE(CAST([name] AS VARCHAR(1000)),'?','');

#2 Only update name values that do not have regular ?'s.

UPDATE [dbo].[Table]
SET [name] = REPLACE(CAST([name] AS VARCHAR(1000)),'?','')
WHERE [name] NOT LIKE '%?%';

DB<>Fiddle

#3 Replacing the ? to a value that cannot exist in your data and then
    replacing it back.

With you guessed it, another REPLACE.

UPDATE [dbo].[Table]
SET [name] = REPLACE(REPLACE(CAST(REPLACE([name],'?','THISVALUECANNOTOCCURINMYDATA') AS VARCHAR(1000)),'?',''),'THISVALUECANNOTOCCURINMYDATA','?')

ReplaceCeption.

DB<>Fiddle

If there are no other, more foolproof, get all unicode values and replace these workarounds. I would use #3 just to be sure that no original ?s are replaced.


Changed values to

join_buffer_size = 2M
sort_buffer_size = 2M
read_buffer_size = 2M

Now server creates many on-disk tmp tables with zero-length. Then, re-run script and it gives:
@SMP=258, @SMP_RATE=3096

TUNING-PRIMER script gives advise to increase tmp_table_size/max_heap_table_size. Will set tmp_table_size=96M, max_heap_table_size=96M and see the reaction.


How to install pgcrypto extension in postgres 10 on CentOS 7

From the docs on CREATE EXTENSION

  PostgreSQL will create the extension using details from the file SHAREDIR/extension/extension_name.control.

You need to check what SHAREDIR is set to with pg_config --sharedir. If that's not set to /usr/share/pgsql/extension then likely your postgresql-contrib was packaged by someone different than your copy of postgresql. These are compile time variables and they should be the same. Moving them in likely won't work either because if these are packaged by two separate entities they're likely built against different libraries.

CentOS

In your case I assume

You're installing the PostgreSQL from the PostgreSQL repos.
You're installing the -contrib from the CentOS repos.

What you probably want to do is install from the CentOS repos. Uninstall all things related to PostgreSQL, and delete those files you copied or linked and run this

# Adds the PostgreSQL repo
rpm -Uvh https://yum.postgresql.org/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm

# I think this should install them explicitly from the PostgreSQL repo
rpm -Uvh https://yum.postgresql.org/10/redhat/rhel-7-x86_64/postgresql10-10.1-1PGDG.rhel7.x86_64.rpm
rpm -Uvh https://yum.postgresql.org/10/redhat/rhel-7-x86_64/postgresql10-contrib-10.1-1PGDG.rhel7.x86_64.rpm


how to update records else insert records in postgresql

You can use the UPSERT:

https://www.postgresql.org/docs/9.5/static/sql-insert.html

Example from documentation:

INSERT INTO distributors (did, dname)
VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')
ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname;


Your CASE is almost correct, but to be able to do a separate join, you have to use a subquery:

UPDATE a
SET Material = CASE
                 WHEN Element <= 300000
                 THEN 80000
                 ELSE (SELECT b.Material
                       FROM b
                       WHERE a.PCOMP = b.PCOMP
                         AND a.Ply = b.Ply)
               END;


How to dive into the PostgreSQL's optimizer and trace each step it takes to optimize the query?

No, there's no detailed optimizer audit/reporting structure or function.

Your best tool is 'gdb'. Unlike MS SQL Server, PostgreSQL is open source; you can just build with debug symbols enabled, attach a debugger, and trace exactly what it's doing.

That doesn't mean it's easy, of course. A canned set of breakpoints and gdb macros to produce a trace of what the planner/optimiser are doing would be very nice. I'm not aware of any such tool at this point.

gdb's tracepoints, or perf userspace tracepoints, will be very useful to you.

You can also enable detailed debug logging and some of the trace parameters in postgresql.conf then examine the resulting detailed log files.

There's some documentation (which I've added info on dumping plan trees, etc, to) in the PostgreSQL developer FAQ: https://wiki.postgresql.org/wiki/Developer_FAQ . This may be useful. You should also read the main developer docs.


Use LPAD function.

LPAD(str,len,padstr)
Returns the string str, left-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters.

UPDATE clients
SET clientId = LPAD(clientId, 4, '0')
WHERE
    char_length(clientId) < 4;

For example:
+------------------+
| lpad('12',4,'0') |
+------------------+
| 0012             |
+------------------+


A good start.

use InnoDB (or better yet, Postgres). You can use MyISAM slaves if you want full-text search, but otherwise, foreign keys are your friends.
watch your under_scores and camelCase. Use lowercase + underscores for max compatibility.
An order is sold by a vendor to a customer and is paid for by the bill_to party (who is often the customer). Can someone pay for the order that is not the customer? (ex. Grandma buys for Grandson, but uses Grandpas credit card). Don't forget the bill_to_address, that is often required.
order_details is usually called order_items. An order_item can be shipped to someone that is different from the customer or bill_to party. You could add ship_to and ship_to_address fields on order_details.
Are you selling only packages, or might you sell other goods, services, etc? Look into Table Inheritance.
Different parties play different roles in an order, in particular split sales commission. see def below
Can an order have the same status more than once? If not, remove order_status.status_id
I would rename order_statuses to order_status_types

Good luck!

order_role (table)
order_id FK
party_id FK
role_type FK order_role_type
commission_percent null


How can I improve this DB query? it takes too much time

Even though you didn't post table definitions in your question so I can't tell for sure what is missed, the following indexes will be beneficial  :

Index on table (user_id, created_at)
Index on users(id_str) or even users(id_str,screen_name,profile_image_url)
I guess you already have an index on table(id)

UPDATE

Change WHERE :
from WHERE UNIX_TIMESTAMP(t.created_at) > {$today}  to WHERE t.created_at > FROM_UNIXTIME({$today}), this way optimizer will use index that includes create_at column more efficiently.


Large number of VLF and shrinking the log

"But shrinking the file is also bad"

Shrinking a data file: usually bad, mostly due to the havoc it wreaks on the allocation layout and results in increased fragmentation. Why you should not shrink your data files.

Shrinking a log file: provided the log has to shrink (it did grow beyond reason), shrinking it is mostly harmless. It should not be abused though as excessive shrinking will just trigger new growth when the file needs to expand for legitimate reasons. See How to shrink the SQL Server log.

  If there are lot number of VLF then it will effect the performance of the queries

Strictly speaking it will affect the performance of recovery, not the performance of queries. Queries will largely ignore the log (except queries that query the log, of course). Even writes (DML) will not suffer too much from a large number of VLFs. Is recovery that is impacted, really really really seriously, as to make it imperative to keep the number of VLFs in check.


mysql select query to select from single table with multiple WHERE clauses

Try this:

select neuron_id, neuron_name
from neuron
where neuron_name = "a" or neuron_name = "c" or neuron_name = "e"

Edit:

The solution from ypercube is more semantically correct.

select neuron_id, neuron_name
from neuron
where neuron_name IN ('a', 'c', 'e')


.SQL files are text. You can open them in a text editor. Then, find and remove the DROP TABLE and CREATE TABLE statements. Finally, use the workbench or any other client to run the remaining INSERT statements.

On the subject of differential and incremental backups, you'll have to restore your SQL files in the correct order to get the results you hope for. That means you'll have to work out that order very carefully indeed from the files you have. Commercial RDMSs like Oracle have elaborate schemes for making incremental and differential backups, along with schemes for restoring them. MySQL doesn't have that kind of thing, so whatever you have is either home-grown or based on some sort of third party software package.

If this were my project, I'd create a sacrificial copy of the database and all the backup files, .frm files, and other stuff. I'd then mess around trying to get stuff restored in the right order.

Once I was convinced my copy was correct, I'd make an SQL backup of the whole thing. I'd actually create two backup items: one containing just the CREATE TABLE statements, and the other containing just data. The workbench can do that.

Then restore that clean backup to create a new production database.

What the workbench can't do is help you with the FRM and MYD files. To use those you'll need to figure out how to put them in the right place in the server file system and restart the MySQL server that's looking at them. Treating those files as backups is a sketchy and risky business.  If you successfully restore them, think of your accomplishment as a talking donkey. It's not a surprise it works poorly, it's a wonder it works at all.

Going forward: your ordinary SQL backup files should not contain data definition language such as DROP TABLE or CREATE table statements. You can create separate files containing only those statements and no data, and keep them around if you need them.


Should my Postgres backup server be as beefy (IO, processor, RAM) as my master?

It depends a great deal on your workload - the write volumes and write patterns on the master.

The standby is essentially doing continuous crash recovery. It reads write-ahead logs containing the changes the master made to the tables and applies that to its own tables. It does this using a single worker, so it doesn't benefit significantly from I/O subsystem concurrency.

The main thing a replica server needs is good random I/O performance. It doesn't need fast fsync() as much as the master does, unless you plan to fail over to it.

I'd start with a small machine, knowing that it's always easy to upgrade. If you do this, it's vital that you carefully monitor replication to make sure the standby isn't falling too far behind, or you make sure you have WAL archiving set up with abundant space to allow the standby to cope if it falls too far behind the master. Otherwise your replica will fall far enough behind that the master no longer has a record of all the changes required to get from where the standby is to the current master's state, and you have to re-initialise the standby. If you're using it for backups, you don't want this to happen.


Receiving duplicate results in one condition of JOINED query

Since I could not comment, I had a similar issue earlier, and I found the solution in this answer. When joining, you should group the results in order to have only one row per result.
Besides that, do you need all the columns of the table? It is always a good practice to select columns by their name, instead of select *


I was thinking of a flat file database. There was a Swedish producer that was creating them, but I cannot remember their name right now.

Something like https://filedb.codeplex.com/

(I meant this as a comment, not as an answer, but I do not have enough rights to comment these days.)


There is a rather simpler solution. (Using information_schema can be dangerous, if there are thousands of tables in it and it can be slow at time)

I propose to use an actual function. The goal is to do a pivotal query (convert col to rows). This can be accomplish using UNION ALL.
The PK of the table in question is passed to the function allowing it to calculate and return the Average.

The beauty about this is you can extend the function and do 95 Percentile and other type of Stats. You can use that function like other functions.

If a new column is added, all you need to do is add that column to the function.
I think it's easier then to have to exclude the column you don't want.

CREATE FUNCTION `row_avg`(p_pk int ) RETURNS decimal(16,2)
BEGIN

DECLARE p_output decimal(16,2);

 select avg(A.rate) into  p_output
 from (

select rate1 as 'rate' from rating where id = p_pk
union all
select rate2 as 'rate' from rating where id = p_pk
union all
select rate3 as 'rate' from rating where id = p_pk
union all
select rate4 as 'rate' from rating where id = p_pk
union all
select rate5 as 'rate' from rating where id = p_pk
union all
select rate6 as 'rate' from rating where id = p_pk
union all
select rate7 as 'rate' from rating where id = p_pk
) as A ;

  RETURN p_output;

END

table:

CREATE TABLE `rating` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `role` int(11) DEFAULT NULL,
  `student_name` varchar(10) DEFAULT NULL,
  `rate1` tinyint(4) DEFAULT NULL,
  `rate2` tinyint(4) DEFAULT NULL,
  `rate3` tinyint(4) DEFAULT NULL,
  `rate4` tinyint(4) DEFAULT NULL,
  `rate5` tinyint(4) DEFAULT NULL,
  `rate6` tinyint(4) DEFAULT NULL,
  `rate7` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

Usage:

select row_avg(id) from test.rating ;
select row_avg(id) from test.rating where id  =3 ;

Enjoy !


How is my software conecting on my SQL Server if it failover via availability groups?

I'm trying to connect via SSMS to the X.X.X.38 That is the CLUSTERs Ip but It shows the error that the instance cannot be found.

Named instances, by default, don't listen on port 1433, which is required to connect without a specific port or instance name.

However Failover Cluster Instances always listen on dedicated IPs, so you can (and should) reconfigure your FCIs to listen on port 1433 using SQL Server Configuration Manager.

You can do the same thing if your instances are standalone (non-FCI), but you have to configure them to listen on specific IPs in the case you have multiple instances installed on a server.  If you have a single named instance installed on each server you can simply configure it to listen on port 1433 on all IPs.


Refactoring/normalization - but almost empty table

Honestly I have several questions about the requirements.  For example: Can a group contain the same item under different filters?  Can a group contain the same item under the same filter? Can a term be used on multiple filters?

Based on what you have above here is what I would create. Starting with the easy part you have a term table and a filter table.  A filter can have multiple terms, or none.  The one-to-many relationship here handles the Multiple terms are defining a filter and A filter cam have no terms requirements.  Next the group_item_filter table joining the group, item and filter tables creating a many-to-many-to-many relationship.  You will want to put a unique index on this table depending on the questions I have above.  My guess is that a group can have an item only once so the unique index would be (group_id, item_id).  This table allows for the requirements Different groups may contain the same item, but different filter and A filter might be used several times and A group contains several items.

I wouldn't worry about the all but empty filter table.  It still represents a logical entity regardless of weather or not it has a description or any other field.  For that mater you never know, you may decide to add a description later, or any number of other fields depending on the real world application.

Also I realize the table group_item_filter sounds a bit funny and you could break it down into two tables item_filter and group__item_filter but honestly it doesn't work as well.  This way you can create unique indexes to further define the business rules without some of the restrictions that breaking it up will cause.

EDIT: 4/10/2013 9:24PM

Based on the comments below I have another option for you.  In this one I'm breaking up the 3 way cross reference table.  First there is a cross reference table between the group and the item tables (group_item) to support the requirement A group contains several items.  Next there is a second cross reference table that joins the group_item cross reference table with the filter table.  This supports the requirements Different groups may contain the same item, but different filter and A filter might be used several times.  The benefit here is that you don't have any unusual structures (the 3 way cross reference table) but still support the same requirements.  You also have an easy way to logically support a requirement where an item may only be used once per group by putting a unique key on the (group_id, item_id) combination in the group_item table.


DB2 CASE statement faster with parentheses around entire WHEN

In situations where one static sql (procedure) executes better than an equivalent one, I would examine:

a) whether this depends on the order of execution, i.e. does the second one benefit from the first one reading data from disk into the bufferpool? If one runs better than the other regardless of the order they are run in this is not the case.

b) are the plans equal? If one procedure is compiled with one set of statistics, and the other compiled with another set of statistics this could result in different plans. Try rebinding (or recreating) both procedures with the same statistics and see if they still differ


Cannot connect to SQL server through SSMS from other machine after IP change

Possible Solutions

Did you restart the SQL Server instance after the IP address was changed? If not, then the SQL Server was possibly still listening on the old IP address.
When connecting locally you normally utilise the Shared Memory connection, that is why you were able to connect locally, but not from remote (see 1.). The Shared Memory connection does not require the IP address. You can verify this by running the following query after connecting locally:

select
    sdec.net_transport,
    sdec.session_id,
    sdes.login_name
from sys.dm_exec_connections as sdec
join sys.dm_exec_sessions as sdes
    on sdec.session_id = sdes.session_id

Possible output:

net_transport session_id  login_name
-------------------------------------------------
TCP           51          NT SERVICE\ReportServer
Shared memory 52          Domain\Account
Shared memory 55          NT SERVICE\SQLSERVERAGENT
Shared memory 54          NT SERVICE\SQLSERVERAGENT
Shared memory 53          NT SERVICE\SQLSERVERAGENT
Session       55          NT SERVICE\SQLSERVERAGENT
Session       55          NT SERVICE\SQLSERVERAGENT
Shared memory 56          NT SERVICE\ReportServer
Shared memory 57          Domain\Account

TCP = IP Connection
Shared memory = Local Connection

Your local connection would show up as a Shared memory connection.
Did you flush the DNS cache on your other server/computer before you tried connecting? Sometimes cleaning the cache will resolve IP <> Hostname resolutions issues:

 ipconfig /flushdns

Verifying the network configuration in the SQL Server Configuration Manager can help resolve issues. Possible errors:

IP Address does not exist (See 1.: Reboot Instance)
IP Address is not configured to listen (Set Enabled: Yes and Active: Yes)

After rebooting the SQL Server instance check the SQL Server Errorlog file to verify that it is indeed listening on the TCP stack:

2018-08-30 16:15:47.12 spid15s     Server is listening on [ 'any' <ipv6> 1433].
2018-08-30 16:15:47.13 spid15s     Server is listening on [ 'any' <ipv4> 1433].
2018-08-30 16:15:47.13 spid15s     Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
2018-08-30 16:15:47.13 spid10s     The resource database build version is 12.00.5589. This is an informational message only. No user action is required.
2018-08-30 16:15:47.13 spid15s     Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
2018-08-30 16:15:47.14 Server      Server is listening on [ ::1 <ipv6> 1434].
2018-08-30 16:15:47.15 Server      Server is listening on [ 127.0.0.1 <ipv4> 1434].
2018-08-30 16:15:47.15 Server      Dedicated admin connection support was established for listening locally on port 1434.
2018-08-30 16:15:47.15 SQL Server is now ready for client connections. This is an informational message; no user action is required.

This instance was previously not listening on the TCP stack:

2018-08-24 08:36:09.06 spid15s     Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
2018-08-24 08:36:09.07 spid15s     Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
2018-08-24 08:36:09.08 Server      Server is listening on [ ::1 <ipv6> 1434].
2018-08-24 08:36:09.08 Server      Server is listening on [ 127.0.0.1 <ipv4> 1434].
2018-08-24 08:36:09.08 Server      Dedicated admin connection support was established for listening locally on port 1434.
2018-08-24 08:36:09.09 spid15s     SQL Server is now ready for client connections. This is an informational message; no user action is required.

It could be a firewall rule that hasn't been adapted to the new IP address of the server. In that case you would have to have your company's network department look at the rules on the firewall and fix the rule for your server or if you only having issues with your Windows Firewall, check the rules there.
(Added from comments)


Joining the mailbox table to a "FolderNames" table using a foreign key will be problematic if any particular user wants to change the name of any given folder, without changing that name for everyone else.  You could get around this by having a nullable CustomFolderName column in the mailbox table.

Depending on the expected number of accounts, storing the name of the mailbox in the mailbox table is probably not a deal-breaker for performance, unless perhaps there are details you haven't shared with us.  When you say these are "smallish strings", don't specify the column as having a maximum length of 1000 characters if your app will limit the folder names to 15 characters.

Overthinking optimization early on can be burdensome to the development effort.  Focus on the database, just don't go entirely overboard at the very earliest stages of development.  You're quite likely to change the structure as development progresses anyway.  Ensuring you use the correct data types as appropriate is going to be far more important.  For instance, please don't store dates in string columns.  Use a date column.  If there is going to be a time element, use a date/time column.  Don't use 128 bit integers where 32 bit will suffice.


Howto use Windows Authentication with SQL Server 2008 Express on a workgroup network?

Whatever username you are using on the "remote" machine must be set up on the "server" machine.

So, if you are logged-in to "remote" as "John," there must be a "John" user on the machine that is hosting SQL Server 2008 Express, and unless you are going to provide different credentials, it should have the same password, too.


deleting a table referenced by many foreign keys

Assuming the column you want to change is the one the FKs point to, you need to drop all of the foreign key constraints - it's not enough to simply disable them. You can't drop the table, anyway; and you can't drop the column if it's actually referenced, and the only way to add the IDENTITY property is to drop/re-create the column, or drop/re-create the table.

Here is a script that generates the commands to drop and later re-create all of the foreign keys that point at dbo.yourtable. It is based on this answer. Drop is easy, just build a simple concatenated list from sys.foreign_keys:

DECLARE @drop NVARCHAR(MAX) = N'';

SELECT @drop += N'
ALTER TABLE ' + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name)
    + ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS ct ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs ON ct.[schema_id] = cs.[schema_id]
WHERE fk.referenced_object_id = OBJECT_ID(N'dbo.yourtable');

PRINT @drop;

Create is a little more complex. We need to generate the list of columns on both sides of the constraint, even though in most cases there is only one column:

DECLARE @create NVARCHAR(MAX) = N'';

SELECT @create += N'
ALTER TABLE '
   + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name)
   + ' ADD CONSTRAINT ' + QUOTENAME(fk.name)
   + ' FOREIGN KEY (' + STUFF((SELECT ',' + QUOTENAME(c.name)
    FROM sys.columns AS c
    INNER JOIN sys.foreign_key_columns AS fkc
    ON fkc.parent_column_id = c.column_id
    AND fkc.parent_object_id = c.[object_id]
    WHERE fkc.constraint_object_id = fk.[object_id]
    ORDER BY fkc.constraint_column_id
    FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')
  + ') REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rt.name)
  + '(' + STUFF((SELECT ',' + QUOTENAME(c.name)
    FROM sys.columns AS c
    INNER JOIN sys.foreign_key_columns AS fkc
    ON fkc.referenced_column_id = c.column_id
    AND fkc.referenced_object_id = c.[object_id]
    WHERE fkc.constraint_object_id = fk.[object_id]
    ORDER BY fkc.constraint_column_id
    FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ');'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS rt -- referenced table
  ON fk.referenced_object_id = rt.[object_id]
INNER JOIN sys.schemas AS rs ON rt.[schema_id] = rs.[schema_id]
INNER JOIN sys.tables AS ct -- constraint table
  ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs ON ct.[schema_id] = cs.[schema_id]
WHERE rt.is_ms_shipped = 0 AND ct.is_ms_shipped = 0
AND rt.[object_id] = OBJECT_ID(N'dbo.yourtable');

PRINT @create;

When you're happy with the output (keep in mind that PRINT is limited to 8K, so it may look like the command is truncated), add this to the end and run it again:

EXEC sp_executesql @drop

-- drop and re-create the table here, whatever that needs to entail

EXEC sp_executesql @create;

This should be a pretty simple exercise because, based on information you've added later, these tables are empty.

If this is not the column the FKs point to, you may be able to drop just the column and re-add it with the IDENTITY property. But if you had data, this would mean you would have to accept whatever arbitrary row numbers SQL Server would assign, or be prepared to completely gut the table (and all the FK tables, or at least the FK column(s) pointing to this table) and re-populate. You would also have to accept that the column may now be in a different "physical" location within the table (you shouldn't care about this at all, but I mention it because many do for reasons unknown).

In SQL Server 2012, as a workaround, you could implement a SEQUENCE instead of using the IDENTITY property. Since you're only on SQL Server 2008, you could instead generate the increment using your own serializable MAX+1 construct. But that's a different question altogether. (I just ran a search, I have not looked at how good those answers are; also see some ideas here.) Or move to a more modern version with more options.


How to determine the cost of index creation?

If you have a non-production environment, you could simulate your insert / update workload by running SQL Query Stress.  Do that before adding the index to get a baseline, and then after to see how much slower it is, and whether that change is acceptable.

In addition to additional overhead for your DUI operations, another consideration is blocking.  You might have different blocking patterns than you're used to, since some SELECT queries will be using the new index, and inserts / updates will have to take locks on that index and the clustered index.

I only point this out to make the point that DUI "overhead" is not the only consideration.  Adding indexes can often help with blocking, since SELECT queries on the narrower NC index won't be blocked by updates to other columns in the clustered index.

Without knowing anything about your table, here's a quick walkthrough of what the SQL Query Stress approach might look like.  I'll set up a "LogTable" in a new database:

USE [master];
GO

CREATE DATABASE [232113];
GO

ALTER DATABASE [232113] SET RECOVERY SIMPLE WITH NO_WAIT;
GO

USE [232113];
GO

CREATE TABLE dbo.LogTable
(
    Id INT PRIMARY KEY IDENTITY(1,1),
    Col1 DATETIME NOT NULL,
    Col2 CHAR(4) NOT NULL
);

Then I use these two queries to simulate the INSERT / UPDATE workload:

-- insert a row
INSERT INTO dbo.LogTable
    (Col1, Col2)
VALUES
    (GETDATE(), 'val2');

-- update a random row
UPDATE dbo.LogTable
SET Col2 = 'val3'
WHERE Id = (SELECT TOP (1) Id
            FROM dbo.LogTable
            ORDER BY NEWID());

Then I run SQL Query Stress over 6 threads, 250 iterations per thread, with a 100 ms delay between each execution.  This completes in about 25 seconds with just the clustered index in place.

Now I'll create a nonclustered index on Col2, which is affected by both the inserts and the updates:

CREATE NONCLUSTERED INDEX IX_Col2 ON dbo.LogTable (Col2);

Running the test again, it took basically the same amount of time (25 seconds).  Notice that the logical reads went up, so there is some cost here (it just didn't affect the overall duration).

Of course, this is a very simple case, with no other activity on the "server" (my laptop), so the difference is minimal.  But hopefully it demonstrates the concept.


Why is the "The package can not be saved to the file system since its protection level is server storage" error?

I think you need to specify an "/en" option in order to change the encryption level.  See this link.

http://support.microsoft.com/kb/906562


How to monitor space usage on ASM diskgroups

Just use asmcmd. For example:

[oracle@oel61 ~]$ . oraenv
ORACLE_SID = [+ASM] ? +ASM
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oel61 ~]$ asmcmd lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576     30708    28479                0           28479              0             N  DATA/
[oracle@oel61 ~]$

Or get the info directly from the ASM Oracle instance:

[oracle@oel61 ~]$ . oraenv
ORACLE_SID = [+ASM] ? +ASM
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oel61 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Mar 7 10:44:44 2013

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Automatic Storage Management option

SQL> SELECT name, free_mb, total_mb, free_mb/total_mb*100 as percentage
     FROM v$asm_diskgroup;

NAME                  FREE_MB   TOTAL_MB PERCENTAGE
------------------------------ ---------- ---------- ----------
DATA                    28479      30708 92.7413052

SQL>


Does MySQL or MariaDB have a master option to strictly require TLS for ALL connections, no matter what?

As of MySQL 5.7, it does not seem to be possible to force SSL from the server side, without specifying it for each user.

https://dev.mysql.com/doc/refman/5.7/en/encrypted-connection-options.html#option_general_ssl-mode

  --ssl-mode=mode

  This option is available only for client programs, not the server. It specifies the security state of the connection to the server. These option values are permitted:

BOO.  You can only force SSL from the client side.  Opposite of what you want to do.  This is likely why you're getting "bad option" trying to use it as a server setting.

Also:

  To require use of encrypted connections by a MySQL account, use CREATE USER to create the account with a REQUIRE SSL clause, or use ALTER USER for an existing account to add a REQUIRE SSL clause. Connection attempts by clients that use the account will be rejected unless MySQL supports encrypted connections and an encrypted connection can be established.

Enforcing users to Require SSL by routinely checking the users table for REQUIRE SSL seems hacky, but it might be your best option.


How can I efficiently search for all records from a given month, or a given year?

Why not the classic way to avoid functions

WHERE startDate BETWEEN '2014-01-01' AND '2014-12-31';

or

WHERE startDate LIKE '2014-01-%' ;


Essentially this is enabled by default when using the System.Data.SqlClient data provider. There is a very good article about the semantics which can be found here:

SQL Server Connection Pooling (ADO.NET)

Also, you can view the ConnectionString property reference of the SqlConnection class here:

SqlConnection.ConnectionString Property

This contains a lot of information about the options that are available when configuring a connection string for Sql Server.

You should also note that the actual connection pool itself is kept within the Application Pool assigned to the website/web application, so be aware that if you have multiple sites/web applications using the same Application Pool they will also share the same connection pool which may not be desirable to you.

As such, I would recommend assigning a new application pool for each website and each web application underneath the site itself. You can find information about application pools at the link below:

Application Pools


Query

You UPDATE statement looks good except for one major problem. Fixed and re-formatted with some other minor improvements:

UPDATE line_items li
SET    product_id = d.latest_product_id
FROM   products               p
JOIN   vendors                v  ON v.id  = p.vendor_id
JOIN   vendorgroups           vg ON vg.id = v.vendorgroup_id
JOIN   duplicate_product_sets d  ON d.invtid    = p.invtid
                                AND d.vendor_id = p.vendor_id
                                AND d.host_id   = vg.host_id
                                AND d.latest_product_id <> p.id
WHERE  p.id = li.product_id;

You don't need to join line_items a second time. And you also forgot to join the two instances, producing a cross join (as pointed out by @a_horse in the comments). I removed the redundant table and now the join to products does the job.

Indices

  I have dropped indexes on line_items (except the primary key index) ...

Basically correct, but If you only update a small fraction of rows, you should still have an index on product_id, even if that incurs a small cost for index update. The gain should be much more substantial.

For best performance, these are the perfect indices:

li(product_id)
p(vendor_id, invtid, id)
v(id, vendorgroup_id)
vg(id, host_id)
d(vendor_id, invtid, host_id, latest_product_id)

I mean multicolumn indices where I listed multiple columns. Indices on individual columns also go a long way, but not all the way. Multicolumn indices are most efficient when index-only scans are possible. In the best case scenario, this update would only need to scan the suggested indices without touching any table except line_items.

Whether it pays to optimize this operation depends on the complete picture. Additional indices incur a small cost for write performance.

And be sure, that you use the same (adequate) data type for invtid in both columns. I see a cast to text in the EXPLAIN output that shouldn't be there.

Related:

How do I know what indexes to create for a table
Very slow simple PostgreSQL query on RDS
Working of indexes in PostgreSQL


SQL 2014 std does not support AlWays on Availability Group , You need Enterprise Edition,   But SQL 2016 std edition supports basic availability group features

you can check the state of the mirror by using the query below.

this will give you a starting point for further investigations.

--==============================================================================
-- query that shows the current state of each database in the mirroring
--==============================================================================
  SELECT   db_name(sd.[database_id])           AS [Database Name]
          ,sd.mirroring_state                  AS [Mirror State]
          ,sd.mirroring_state_desc             AS [Mirror State]
          ,sd.mirroring_partner_name           AS [Partner Name]
          ,sd.mirroring_role_desc              AS [Mirror Role]
          ,sd.mirroring_safety_level_desc      AS [Safety Level]
          ,sd.mirroring_witness_name           AS [Witness]
          ,sd.mirroring_connection_timeout AS [Timeout(sec)]
    FROM sys.database_mirroring AS sd
    WHERE mirroring_guid IS NOT null
    ORDER BY [Database Name];

make sure the sql server service account has all the relevant permissions on both servers.

I am not sure of your environment, but depending on the query below, and what I could find in the logs, on a convenient time, plan a restarting of the sql server service (which will cause a failover) and back.

Another thing that has worked for me in the past, is scripting the whole thing, then dropping it, and then setting it up again through the script, one database a time.

that way you can try to narrow down the problem.

there is a very similar question:
Database Mirroring error, but everything looks OK

the answer recommends looking at the permissions. Do that first.

another thing is to do a failover following the example below:
(the original had many databases, I left only one DB per server, but you can get the idea)

--============================================================================
--instructions:
--for each server,
--1) get the list of the alter database set safety full
--2) paste that script below
--3) run it
--4) get the list of the alter database set partner failover
--5) run the first line make sure it goes through
--6) run the other lines - failover all the databases
--version 20140624 Anne and Marcello

--============================================================================

--============================================================================
-- before failing over
-- the safety mode should be changed to FULL (SYNCHRONOUS)
--============================================================================

select 'ALTER DATABASE '+quotename(db_name(database_id))+
' SET SAFETY FULL'
from sys.database_mirroring
where mirroring_role_desc='PRINCIPAL'
and (substring(db_name(database_id),1,2) IN ('US')
 or db_name(database_id) LIKE '%ROW')
order by db_name(database_id)

*
-- sqlweblon3
ALTER DATABASE [US14AUTPProduct] SET SAFETY FULL

*
--sqlweblon4
ALTER DATABASE [USAccount] SET SAFETY FULL

--============================================================================
-- generate the failover scripts
--============================================================================
select 'ALTER DATABASE '+quotename(db_name(database_id))+' SET PARTNER FAILOVER'
from sys.database_mirroring
where mirroring_role_desc='PRINCIPAL'
and (substring(db_name(database_id),1,2) IN ('US')
 or db_name(database_id) LIKE '%ROW')
order by db_name(database_id)

*
--sqlweblon3 (principal)
ALTER DATABASE [US14AUTPProduct] SET PARTNER FAILOVER

*
--sqlWeblon4 (principal)
ALTER DATABASE [ProductOfferROW] SET PARTNER FAILOVER
ALTER DATABASE [USAccount] SET PARTNER FAILOVER
ALTER DATABASE [USProductOffer] SET PARTNER FAILOVER
ALTER DATABASE [USStock] SET PARTNER FAILOVER

ALTER DATABASE [ProductOfferROW] SET PARTNER FAILOVER

/** IF YOU ARE FAILING FROM DR TO LIVE CHECK THE LITESPEED
ACTIVITY SERVICE IS RUNNING ON THE SERVER. IF IT IS NOT LITESPEED
SQL AGENT JOBS WILL FAIL **/

--============================================================================
-- after the failover change the safety mode back to ASYNCHRONOUS
-- this is to be done on what is now the principal
--============================================================================

-- DR-sqlweblon3
ALTER DATABASE [US14AUTPProduct] SET SAFETY OFF


SQLite: trigger for multiple tables and/or multiple actions

No, SQLite doesn't appear to have database or server level triggers (like other database systems). Instead you'd have to create the same trigger on each table.

Unfortunately this is also a limitation in SQLite and not possible either.

You might be better off letting the app that connects to the SQLite database (or a secondary process / job depending on if your database is stored on a mobile device vs a computer) control what records get deleted and when. For example, if you were hosting your SQLite database on a Windows machine, you could schedule a Task that routinely cleans up the database. Otherwise you can build it into the framework of your application that whenever a database call is made, the clean up script executes first.

Here is the syntax tree for the CREATE TRIGGER statement on SQLite. It may help clarify what you can and can't do.


Basic answers

Since you select a couple of big columns an index-only scan is probably not a viable option.

  This code works (if no NULL values in data!)

While the column isn't defined NOT NULL, add NULLS LAST to the sort order to make it work in any case, even with NULL values. Ideally, use the clause in the corresponding index as well:

SELECT <some big columns>
FROM   my_table_
ORDER  BY when_ DESC NULLS LAST
LIMIT  1;

PostgreSQL sort by datetime asc, null first?

  Without any index on when_ column, does this statement require a full
  scan of all rows?

Yes. Without index, there is no other option left. (Well, there is also table partitioning where an index on key columns(s) is not strictly required, and it could assist with partition pruning. But you would typically have an index on key columns there, too.)

  With an index on when_ column, should I change this SQL to use some
  other approach/strategy of query?

Basically, this is the perfect query. There are options in combination with advanced indexing:

Advanced technique

Assuming a NOT NULL column. Else, add NULLS LAST to index and queries as suggested above.

You have a constant influx of rows with later when_. Assuming the latest _when constantly increases and never (or rarely) decreases (latest rows deleted / updated), you can use a very small partial index.

Basic implementation:

Run your query once to retrieve the latest when_, subtract a safe margin (to be safe against losing the latest rows) and create an IMMUTABLE function based on it. Basically a "fake global constant":

CREATE OR REPLACE FUNCTION f_when_cutoff()
  RETURNS timestamptz LANGUAGE sql COST 1 IMMUTABLE PARALLEL SAFE AS
$$SELECT timestamptz '2015-07-25 01:00+02'$$;

PARALLEL SAFE only in Postgres 9.6 or later.
Create a partial index excluding older rows:

CREATE INDEX my_table_when_idx ON my_table_ (when_ DESC)
WHERE when_ > f_when_cutoff();

With millions of rows, the difference in size can be dramatic. And this  only makes sense with a much smaller index. Just half the size or something would not cut it. Index access itself is not slowed much by a bigger index. It's mostly the sheer size of the index, which needs to be read and cached. (And possibly avoiding additional index writes, but hardly in your case.)
Use the function in all related queries. Include the same WHERE condition (even if logically redundant) to convince the query planner the index is applicable. For the simple query:

SELECT <some big columns>
FROM   my_table_
WHERE  when_ > f_when_cutoff()
ORDER  BY when_ DESC
LIMIT  1;

The size of the index grows with new (later) entries. Recreate the function with a later timestamp and REINDEX from time to time with no or little concurrent access. Only reindex after a relevant number of rows has been added. A couple of thousand entries won't matter much. We are doing this to cut off millions.
The beauty of it: queries don't change.

Implementation with function to update the partial index automatically:

Get latest child per parent from big table - query is too slow

More general advice:

Index optimization with dates


Why DBCC CHECKDB is multiple times in error log

I think @MartinSmith nailed it in his comment.  When you have AUTO_CLOSE set to ON, then the database will shutdown when the last user exits.  Likewise, when somebody attempts to use the database it will reopen.  When the database reopens, a message about the last time DBCC CHECKDB ran will be logged.

Take the below example (sample code, not meant to be run anywhere near a production environment) to show proof of this:

create database AutoCloseDb;
go

alter database AutoCloseDb
set auto_close on;
go

dbcc checkdb('AutoCloseDb');
go

-- rotate the below "use" statements a few times in order
-- to cause the database to close and reopen
--
use AutoCloseDb;
go

use master;
go

After we do a few rotations of use AutoCloseDb; followed by use master;, we would see something similar to the below in the SQL Server error log (I use the below PowerShell code to retrieve this):

Add-Type -Path "C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"

$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server("<your SQL Server name>")

$SqlServer.ReadErrorLog() |
    Where-Object {$_.Text -like "*AutoCloseDb*"} |
    Select-Object LogDate, Text |
    Format-List

And in my test environment, the output is the following:

  LogDate : 11/15/2013 5:47:17 AM Text    : Starting up database
  'AutoCloseDb'.

  LogDate : 11/15/2013 5:47:18 AM Text    : CHECKDB for database
  'AutoCloseDb' finished without errors on 2013-11-15 05:39:19.137
  (local time). This is an informational message only; no user action is
  required.

  LogDate : 11/15/2013 5:47:18 AM Text    : Starting up database
  'AutoCloseDb'.

  LogDate : 11/15/2013 5:47:18 AM Text    : CHECKDB for database
  'AutoCloseDb' finished without errors on 2013-11-15 05:39:19.137
  (local time). This is an informational message only; no user action is
  required.

  LogDate : 11/15/2013 5:47:19 AM Text    : Starting up database
  'AutoCloseDb'.

  LogDate : 11/15/2013 5:47:19 AM Text    : CHECKDB for database
  'AutoCloseDb' finished without errors on 2013-11-15 05:39:19.137
  (local time). This is an informational message only; no user action is
  required.

  LogDate : 11/15/2013 5:48:25 AM Text    : Starting up database
  'AutoCloseDb'.

  LogDate : 11/15/2013 5:48:26 AM Text    : CHECKDB for database
  'AutoCloseDb' finished without errors on 2013-11-15 05:39:19.137
  (local time). This is an informational message only; no user action is
  required.

  LogDate : 11/15/2013 5:49:30 AM Text    : Starting up database
  'AutoCloseDb'.

  LogDate : 11/15/2013 5:49:30 AM Text    : CHECKDB for database
  'AutoCloseDb' finished without errors on 2013-11-15 05:39:19.137
  (local time). This is an informational message only; no user action is
  required.

If it is indeed AUTO_CLOSE that is causing this logging behavior in your case, you should see the similar message of "Starting up database ..." in your error log.

If this is not what you're seeing, then please modify your question to include other error log events surrounding the DBCC CHECKDB logging.


Populate monthly table from quaterly data

If your date dimension has an Qtr Number attribute, you could join the import data to the date dimension on that field and you would create the required rows.


Possible to ship logs to database with different name, but same structure?

It's been a while since I've used the GUI, however you can do it using TSQL with the command sp_add_log_shipping_secondary

With this command you would specify the name of the secondary database and the primary, which would then restore the transaction logs to the relevant database name.

EXEC master.dbo.sp_add_log_shipping_secondary_database
@secondary_database = N'ProdDatabaseNameReporting'
,@primary_server = N'YourPrimaryDatabaseServer'
,@primary_database = N'ProdDatabaseName'
,@restore_delay = 0
,@restore_mode = 1
,@disconnect_users = 0
,@restore_threshold = 45
,@threshold_alert_enabled = 0
,@history_retention_period = 1440 ;
GO

Going through the full TSQL implementation of Log Shipping using TSQL from https://msdn.microsoft.com/en-us/library/ms190640.aspx would be a good place to start for you.