Mondrian is indeed a good example, in that it's a ROLAP engine (in which MDX statements are translated to SQL which are then fired to the underlying database (other than it caches results, so ends up being a Hybrid approach instead of pure ROLAP).

However, the "translation" between MDX and SQL isn't a straightforward one. A lot of MDX statements require multiple SQL statements, not just one. You can see that from Mondrian's logs (if you have them enabled), where for the same MDX query sometimes several dozen SQL statements are fired, and their results used to feed the next batch of SQL statements (e.g., a first SQL statement gets all customer keys from the customer dimension, a second one creates an IN statement with the customer keys from the previous query).


How can I prevent so many aborted clients in MySQL (increasing aborted_clients count)?

It turns out that the MySQL Connector/NET behavior is at the heart of the matter.
By default, it will automatically create a pool of connections and reuse them as necessary. When you call .Open() a connection might be created anew, or you might get a connection that already exists but was idle (SLEEP). When you call .Close() the connection is returned to the pool.
There are a lot of other questions and answers which deal with the concept of "Should I create new connections for every action or should I reuse the same connection?" - the Connector/NET deals with that for you, so you should create, open and close them. The pooling is automatic and behind the scenes.
Something of importance here: The maximum number of connections that Connector/NET can create by default is 100. If you've got a lot of clients doing concurrent queries or what have you, it's possible the server could exceed its max_connections limit. If necessary you can limit the number of connections that Connector/NET creates by specifying a Maximum Pool Size option in its connection string options.
So, why all the aborted clients? The connections in the pool managed by Connector/NET are held open for longer than your server's wait_timeout, so the server closes them and considers them a client abort.
How long are they held open? Well, it depends on your Connector/NET version, but:

Starting with MySQL Connector/Net 6.2, there is a background job that runs every three minutes and removes connections from pool that have been idle (unused) for more than three minutes. The pool cleanup frees resources on both client and server side. This is because on the client side every connection uses a socket, and on the server side every connection uses a socket and a thread.
Prior to this change, connections were never removed from the pool, and the pool always contained the peak number of open connections. For example, a web application that peaked at 1000 concurrent database connections would consume 1000 threads and 1000 open sockets at the server, without ever freeing up those resources from the connection pool. Note, connections, no matter how old, will not be closed if the number of connections in the pool is less than or equal to the value set by the Min Pool Size connection string parameter.

-- Reference
Since you're using version 6.2.2.0, they're closed after three minutes by the client, which doesn't cause the server to treat it as a warning.
Solution: Increase wait_timeout to five minutes or so. Note: watch the number of connections to the server to make sure that having connections able to SLEEP longer doesn't cause the total number of connections to approach max_connections.


Cannot start psql with user postgres “Could not change directory to /home/user ”

I solved the problem with the help of a relative.

The first problem was that my /home and /home/user folder didn't have the right permissions.

We set them correctly with chmod og+rX /home /home/user.

Then I forgot, that I created a new PGDATA directory in /home/user. And this directory had the wrong permissions. So we set them again for the user postgres with chown postgres:postrgres -R /home/user/PGDATA.

I decided to place the PGDATA directory in /home/user because of huge log-files that where produced during raster data queries with my PostGIS/PostgreSQL database. Sometimes I'm using Sublime Text 2 for queries and I quit them without stopping the database process and then the logfile became larger and larger and I was running out of space and nothing worked anymore.


Howto do a hard upgrade from postgis-1.5 to postgis-2.0

If you can't follow the correct procedures suggested by Mapperz and Andre Joost, I'd advise you to do an extraction through tools such as  or "Qgis" (ogr) in shp format, and then perform a batch import in a fresh postgis 2.0 installation with shp2pgsql-gui.

You just have to make sure that during the export value of the attributes (columns) of the various spatial layers may be slightly different because of shape format specification.

The length of the porting procedure depends strictly on the amount of data.


I think this achieves what you're looking for. Hope this helps.

 SELECT
      Authentication.Ticket
      , CAST(CASE
           WHEN Authentication.TicketExpires < GetDate() THEN 1
           ELSE 0
      END AS bit) AS 'IsExpired'
 FROM Authentication
 WHERE Authentication.accountID = @id


I think the most efficient way in your case will be:

--uncomment if you are allowed do this: drop table HUGE_TABLE;
--uncomment if you are allowed do this: drop table HUGE_TABLE_WITH_BLOBS;
create table HUGE_TABLE NOLOGGING PARALLEL 4 as select * from HUGE_TABLE@Distantdb;
create table HUGE_TABLE_WITH_BLOBS NOLOGGING PARALLEL 4 as select * from HUGE_TABLE_WITH_BLOBS@Distantdb;

You can omit: "NOLOGGING PARALLEL 4" if normal mode will be enough fast.
Remember about creating constraint and indexes on newly created tables, default only  NOT NULL constraints will be copied.


delete records in one table based on multiple column values in second table

Operation 1

Insert records into Table1 that only exist in Table2:

INSERT INTO Table1
    (address, city, state, zip)
SELECT address, city, state, zip  FROM Table2
EXCEPT
SELECT address, city, state, zip  FROM Table1 ;

Operation 2

Delete records from Table1 where the record is not in Table2 but the city is in Table2.

(assuming that the columns ar enot nullable. If they allow nulls, it would be better to rewrite the NOT IN with a NOT EXISTS subquery):

DELETE FROM Table1
WHERE (address, city, state, zip)
       NOT IN
      (SELECT address, city, state, zip  FROM Table2)
  AND city IN
      (SELECT city FROM Table2) ;

As for performance and efficiency, I have almost zero experience with DB2, so it would be better if you waited for some other answer. But an index on all the columns (address, city, state, zip) - on both tables - would probably be the best you can do for the queries, plus a single index on (city). Or perhaps combine the two suggestions in one index with all the 4 columns but choose city for the 1st part of the index: (city, address, state, zip).

You haven't provided the tables' structure but the 4 columns seem appropriate for a UNIQUE constraint so you may already have such an index.


Sort results of a query by the order of OR clause

There are two ways to do this in Postgres:

Join your table against a virtual table build from a values list:

with names (idx, name) as (
  values
    (1, 'example.co.uk'),
    (2, 'co.uk'),
    (3, 'uk')
)
select s.*
from suffixes s
  join names n on n.name = s.name
order by n.idx
limit 1;

Another shorter version would be to join against an array:

select s.*
from suffixes s
  join unnest(array['example.co.uk', 'uk', 'co.uk']) with ordinality as n(name, idx)
    on n.name = s.name
order by n.idx
limit 1

The second version has the advantage, that you don't need to supply the additional index because that's implicitly defined through the order of the values inside the array.


Configuring SQL Server for SSL Encryption

When the client connects to the server, the server provides its certificate.  If the client is not explicitly configured to trust whatever certificate the server returns, it will check the client keystore to see if it can trust the certificate.  The client keystore does not have to contain a copy of the server's certificate if it trusts the chain of certificate authorities that issued the certificate.

It is the exact same principle as to how your browser establishes an HTTPS connection to a web server.  Although you may not know it, the browser's keystore is configured to trust numerous certificate authorities, so as long as the certificate that is returned by the site was created by one of those certificate authorities, it will trust it.  This is what allows you to establish an HTTPS connection to your bank's website, for example, without installing certificates from the bank.  The bank used GoDaddy or DigiCert or a similar public certificate company, and browsers are configured by default to trust these "known good" certificate generators.

For example, an organization typically installs root and infrastructure certificates in the keystore of every Windows system on their network.  Then when SQL Server (or a web server or whatever) has a certificate installed that was generated by that organization's key infrastructure, most native Windows processes will trust the certificate because they'll use a Microsoft SQL Client driver, which refers to the Windows keystore to determine what certificates to trust.

A Java SQL client, such as Microsoft JDBC and jTDS, will NOT use the Windows keystore, so one would need to install the root and infrastructure certificates into the keystore that the associated Java process is using (a file named "cacerts", typically, but JDBC allows the keystore file to be specified).  The other option is to configure the connection string such that whatever certificate is returned by the server is trusted.  However, this would present the risk of connecting to a rogue server and it would trust its certificate.

There are a few other SQL clients out there, such as DataDirect Wire Protocol, that have options to specify a certain keystore, trust the server certificate, etc.

  Could it be that I'm allowed to establish an encrypted connection without a copy of the certificate installed client side, due to the fact that the issuing CA is a Trusted Root CA rolled out as part of our Group security policy?

Yes, as explained above.

  What is the expected behaviour if the client does not have a copy of the certificate installed and ForceEncryption=True on the server? Should a connection to the instance be refused?

I might amend this question to, "What is the expected behavior if the client does not trust the certificate..." since it doesn't have to have a copy of the certificate as long as it trusts the chain that generated it.  ForceEncryption is exactly what it sounds like--it will NOT allow an unencrypted connection.  The client has to trust the certificate that is returned and establish an SSL connection in order to connect and login to SQL Server.  If the client doesn't trust the certificate, the client will not be able to login and you'll see an error in the SQL Server error log.

  Additionally; external clients will be connecting to the instance via a Web Server in our DMZ, then routed to an internal app server which communicates with the DB server. In this example would each device in the chain need a copy of the certificate

No, the only system that needs to trust the certificate that SQL Server returns is the system that has a connection to the SQL Server.  For example, with a web application, the web server that connects to SQL Server would need to trust the certificate, or the chain that generated the certificate, that is returned from SQL Server.  The browsers that connect to the web application would need to trust the certificate that the web server returns.  The browsers are not connecting to SQL Server, and the SQL Server's certificate would not be forwarded by the web server to the browsers.  The browsers are not establishing a connection to SQL Server, so they don't need to trust the SQL Server's certificate.


Currently on MySQL 5.5+ you can use such characters on both schema names and table names, just have in mind that there are known bugs caused by such characters being confused with directory paths on the MySQL internals code base.

Up until MySQL 5.5 you would find it to not be possible.
From this link: (of the old documentation, that's not available anymore)

  Database and table names cannot contain “/”, “\”, “.”, or characters that are not permitted in file names

One way or another, using backslashes is just bad-practice, I recommend that you use an underscore character instead.


myisampack running really slow for large DBs

If you have any TEXT/BLOB fields in your MyISAM tables, you may just have bloated fields.

You verify this, go back to your older MyISAM tables and perform this:

SELECT LENGTH(TextOrBLOBField) FROM some_table;

Run this same query against more recent tables and see if the slowness is just O(n) (order n) running time based on the amount of recorded data.


How to extract the grants on default roles of PostgreSQL 10: pg_read_all_settings, pg_read_all_stats, pg_monitor and pg_signal_backend?

Have a look at https://www.postgresql.org/docs/current/infoschema-role-table-grants.html

  The view role_table_grants identifies all privileges granted on tables
  or views where the grantor or grantee is a currently enabled role.
  Further information can be found under table_privileges. The only
  effective difference between this view and table_privileges is that
  this view omits tables that have been made accessible to the current
  user by way of a grant to PUBLIC.

See also https://stackoverflow.com/a/55459289/6368697


Unable to run union in parallel on SQL Server

There is no separate MAXDOP for each side. But you could play with:

OPTION (QUERYTRACEON 8649)

This sets the cost threshold of parallelism to 0, meaning it will consider a parallel plan even if the costs are very low. You can also play with DBCC SETCPUWEIGHT, which Paul White describes here or other techniques he has for forcing parallel plans here. or even play with DBCC OPTIMIZER_WHATIF - which really should just be for playing.

There has been a suggestion on Connect to allow for a MINDOP syntax or something similar.

In any case, I'm not convinced that parallelism will necessarily help you in this case. Sure, you might get a parallel plan, but is it really going to reduce the runtime of the query? With all those GROUP BY and DISTINCT (why would you ever need both?) I think you should focus your optimization elsewhere (such as pre-aggregating some of this information perhaps). Or even something simple, like perhaps assign the COUNT(*) FROM #MatchValues to a variable instead of trying to evaluate it twice (not sure if SQL Server will do that in this case, but it can't hurt to remove the temptation).


PostGIS: how to retrieve a report of costs by decades from a table

Postgres allow to extract DECADE from a timestamp value, use a CASE statment to sum each one.

SELECT
    company,
    sum(case when extract(DECADE from movie_date) = 195 then cost else 0 end) as Total_Decade_50s,
    sum(case when extract(DECADE from movie_date) = 196 then cost else 0 end) as Total_Decade_60s,
    sum(case when extract(DECADE from movie_date) = 197 then cost else 0 end) as Total_Decade_70s,
    sum(case when extract(DECADE from movie_date) = 198 then cost else 0 end) as Total_Decade_80s,
    sum(case when extract(DECADE from movie_date) = 199 then cost else 0 end) as Total_Decade_90s,
    sum(case when extract(DECADE from movie_date) = 200 then cost else 0 end) as Total_Decade_00s,
FROM
    data_table
GROUP BY
    company
ORDER BY
    company;

In case you get an error on extract sentence, cast your date as timestamp.

extract(DECADE from film_date::timestamp)


Something like this:

SELECT coalesce(CONCAT('Component ', t.component), 'Total for all Components') AS __group__,
       CASE
          WHEN t.id > 0 THEN CASE t.status WHEN 'closed' THEN 'color: #777; background: #ddd; border-color: #ccc;' END
          ELSE 'font-weight: bold'
        END AS __style__,
        t.id AS ticket,
        case when t.id > 0 then t.summary else 'Total' end AS summary,
        SUM( coalesce(w.endtime, current_timestamp) - w.starttime) AS total
FROM ticket t
  INNER JOIN work_log w ON t.id = w.ticket
GROUP BY t.component, t.id, t.summary, t.status

The sum() will sum up the difference in milliseconds between endtime and starttime not sure what the intention behind that is.

I'm not entirely sure what the rollup does, but something along the lines:

with base_data as (
   ... the above query goes here ...
)
select __group__, ticket, __style__, summary, total
from base_data
union all
select null, null, null, null, sum(total)
from base_data
group by __group__, ticket, __style__, summary

might get you started.


Create SQL Server trigger before insert/update

From the discussion in comments, I've restated your requirements:

  When X_ID = 2, then A,B,C,D should be unique. When X_ID <> 2, then no
  uniqueness is necessary.

You can use a filtered index in SQL Server (ie, an index with a WHERE clause).

CREATE TABLE #MyTable (X_ID INT, Col_A VARCHAR(10),Col_B VARCHAR(10),Col_C VARCHAR(10),Col_D VARCHAR(10))

CREATE UNIQUE INDEX MyUniqueIndex ON #MyTable (Col_A,Col_B,Col_C,Col_D) WHERE X_ID = 2;

INSERT INTO #MyTable (X_ID, Col_A,Col_B,Col_C,Col_D)
--non-unique values for x_id = 1
VALUES (1,'A','A','A','A'),
       (1,'A','A','A','A'),
       (1,'A','A','A','A'),
--unique values for x_id = 2
       (2,'A','A','A','A'),
       (2,'A','B','A','A'),
       (2,'A','B','C','A'),
       (2,'A','B','C','D')

--non-unique value for x_id = 2
INSERT INTO #MyTable (X_ID, Col_A,Col_B,Col_C,Col_D)
VALUES (2,'A','A','A','A')

The last insert will give you an error for violating uniqueness:
    Msg 2601, Level 14, State 1, Line 17
    Cannot insert duplicate key row in object 'dbo.#MyTable' with unique index 'MyUniqueIndex'. The duplicate key value is (A, A, A, A).

Depending on whether the columns are NULLable or not, and whether you want NULL to be included in the uniqueness check you may need your CREATE INDEX statement to include checks for NULL. In the above example, SQL Server will treat NULL as a discrete value (which is different than the way Oracle handles NULL). You may instead want to use something like this:

CREATE UNIQUE INDEX MyUniqueIndex ON #MyTable (Col_A,Col_B,Col_C,Col_D)
    WHERE X_ID = 2
    AND Col_A IS NOT NULL
    AND Col_B IS NOT NULL
    AND Col_C IS NOT NULL
    AND Col_D IS NOT NULL;


SSRS and Managed Service Account - impossible to configure?

Yes you can run SSRS with a Managed Service Account.

I would double check that your MSA is installed on your SSRS server. You can do so with Powershell.

Get-ADServiceAccount -identity msaname -properties hostcomputers |select hostcomputers

If your SSRS server is not listed, you will need to install the MSA on the server. This needs to be run locally (on SSRS server). You MUST be domain admin to run Install-ADServiceAccount successfully.

Install-ADServiceAccount msaname


"If not exists" using OBJECT_ID() doesn't work for views and triggers. why?

Referencing the documentation from CREATE VIEW under REMARKS:

  The CREATE VIEW must be the first statement in a query batch.

Referencing the documentation from CREATE TRIGGER

  CREATE TRIGGER must be the first statement in the batch and can apply
  to only one table.

For VIEWS and TRIGGERS, I think you'll have to check for the existence of the object and drop in one batch and create them in another batch separated by a GO

Example:

IF EXISTS (SELECT * FROM sys.objects WHERE [name] = '[dbo].[trg]' AND [type] = 'TR')
      DROP TRIGGER [dbo].[trg] ON [dbo].[tbl]
GO
CREATE TRIGGER [dbo].[trg] ON [dbo].[tbl]
AFTER DELETE
AS
BEGIN
   //
END
GO


Extract N rows from 2 tables (t1, t2) - if t1 doesn't match N rows, then (and only then) extract data from t2

You should be able to do this by using a union to join the two queries together. This assumes that Origin is a value that signifies which table is being queried so that results from t1 take precedence.

SELECT 1 as origin, name, id

FROM t1 WHERE id = 'value'

union

SELECT 2 as origin, name, id

FROM t2 WHERE id = 'value'

ORDER BY origin, id

LIMIT 100


Circular replication with servers in different locations

CIRCULAR REPLICATION
You should upgrade to MySQL 5.5 because it features Semisynchronous Replication
There are settings that can let you dictate how much delay a Master can tolerate before switching back to asynchronous replication until latency dies down. I have written posts on Semisynchronous Replication before:

Aug 05, 2011 : Is MySQL Replication Affected by a High-Latency Interconnect?
Jul 25, 2012 : Mysql database replication on different vlan/subnet/another site
Mar 12, 2013 : Breaking Semisynchronous Replication in MySQL 5.5

Some have argued that it is not good over a geographic distance. This is debatable depending on several factors

Using Statement-Based or Row-Based Replication
Actual Distance Between Data Centers
Doing DML statements on all MySQL Masters
If you are plain old content living with latency

DATA RESILIENCY
If you have Multiple Masters in Different Data Centers for the purpose of Disaster Recovery (or as the politically correct would say, "Business Continuity"), you should try to make the Data Center intrinsically resilient for the sake of MySQL. How would you do that?
Setup DRBD in each Data Center. Then, setup Circular Replication among the DRBD Primaries.
Here are my posts on setting this up

Mar 29, 2011 : MySQL high availability, failover and replication with Latency
Dec 19, 2011 : Best way to setup master to multi master replication
Dec 31, 2012 : Automatic MySQL Replication Fail-over Strategy


How to link a MySQL Table with a binary key into Access?

I solved this by creating a updateable VIEW to the table. The view displays every column but the primary key LNEr is converted to a string with the HEX() function:

CREATE VIEW v_ereignisse AS SELECT HEX(LNEr) AS LNEr, Datum, StatNr, ...

In MS Access instead of creating a link to the table I created a link to the view. Because the view has no primary key, the "table link manager" asks for selecting one, this (of course) is the string version of LNEr.


Is it safe to disable InnoDB doublewrite buffering when setting Innodb_page_size = filesystem page size?

Background info...  The "torn page" problem occurs when part of an InnoDB block is written to disk, but the physical write died before writing all the low level (usually 512-byte) blocks.  This leads to an unreadable block for InnoDB.  The double-write buffer, and its extra write, makes it possible to recover from a torn page.

The hardware needs to "guarantee" atomic writes of 16KB.  FusionIO is the only drive that I know of to provide that guarantee.  And that gives them a advantage by saying that you can turn off double-writes.

RAID controllers with a battery-backed-write-cache should be able to provide such a guarantee, but it is unclear whether the OS confuses the issue.  But, then, writes are virtually free, so turning off double-write does not matter much.

I hesitate to say yes or no to your question because I don't know what is underneath the "filesystem".  It may turn the InnoDB block into multiple 512-byte disk writes, thereby still allowing torn pages.

O_DIRECT is a related topic to look at.

Setting innodb_page_size to 4K has other issues (even if you could get the drive to play nice) --

Records are limited to about 2000 bytes instead of about 8000.
You are asking for more I/Os.  This may be good or bad.  Random point queries of small rows will work faster with 4K blocks; most other situations may work slower.


Does a DBA need to know how to program in a system language besides SQL?

It depends.

In a big shop, maybe not because you have 1000s of servers to look after and your tools are provided. In a small shop, you'll probably need more knowledge because you have a wider remit.

Scripting languages (PowerShell, cmd.exe for example) are always useful though for monitoring, deploying etc. I've often had some Perl scripts I had to maintain (just about). Then there are the assorted ETL packages that you'd be expected to know about.

Saying that, most DBAs (that I know) would be able to write basic CLR stuff or know PL/SQL well. For me, the dividing line is knowing about the broader patterns or architecture of .net or Java. I don't need it as a DB developer or DBA. In the same way that .net or PHP or Java Monkeys don't understand database design or architecture or code as well as I do.

Personally, I decided to stop chasing the latest or best client language years ago and focus on database work. That doesn't make me a "non-programmer": I'd need to learn it again if I had to.


Best way to defrag/compact a database for archival purposes

To eliminate the physical fragmentation in the files you could as move the clustered index with drop existing to a new filegroup. As they are going to be RO make them all fillfactor 100% as no space needed for inserts, page splits caused by updates.

This would also allow you to perform a piecemeal restore and bring the database online very quickly if you decided to ever go to Enterprise. Enterprise also allows columnstore indexes in addition to massively reduce query time for this Read Only data, which is massive fillet.

You can use the shrinkfile option once before switching to read only without any serious issues with fragmentation to remove the space at the end of the file as you desired.

On a side note, just checking that you are using the latest Datatypes for your LOBS.
  i.e. nvarchar(max) or varchar(max) instead of ntext or text, varbinary(max) instead of image?


tempdb log file usage huge by sleeping status sessions but not relased the space

Use this script to determine tempDB usage per sessions :

--Lists the TempDB usage per each active session.
--It helps identifying the sessions that use the tempdb heavily with internal objects.

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


Error installing SQL Server Database Engine Services Instance Features - The User Log directory in the registry is not valid

Ok I found this article

https://blog.sqlauthority.com/2015/05/16/sql-server-service-pack-error-the-user-data-directory-in-the-registry-is-not-valid-verify-defaultdata-key-under-the-instance-hive-points-to-a-valid-directory/

It says my CU update fails because of Default Log path does not exist anymore

Default Log path can be checked by one of below ways:

Execute T-SQL

exec [master].sys.xp_regread
    @rootkey='HKEY_LOCAL_MACHINE',
    @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQLServer\',
    @value_name = 'DefaultLog'

SSMS -> Right Click Server -> Properties -> Database Settings -> Database Default Locations
Regedit -> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQLServer\DefaultLog

Updated Default Log path to point to existing location, and CU18 update installed fine without any errors


Feasibility of Automated and Continuous Performance Tuning in the future

Perfect tuning is an art and the underlying system (meaning the data and its usage patterns) is almost always evolving in ways automation can't predict or react to. Tuning is also based on past activity; the system can't predict tomorrow's growth or usage patterns, but a DBA can to some degree. We're a long way away from decent artificial intelligence and I highly doubt this will be one of its first applications when it does arise.

For a while, SQL Server was marketed as a self-tuning system, "No DBA required." I'm not sure that's a good thing even if it were possible, but it's certainly not marketed that way today and it doesn't even seem to be a secondary or tertiary goal of Microsoft. Even the new Query Store feature in 2016 is designed to allow you to make decisions about query plans and regressions, not to make them for you. As a monitoring company we, too, strive to provide you with the information for you to make decisions, and try to make the pain points extremely obvious, but we stop short of telling you to change index x and tune query y.

Think about self-driving cars for a moment - while they may reduce accident rates slightly overall, they're going to put millions and millions of people out of work, from cab drivers to delivery people to truckers... never mind that software can't think like a human. I like cruise control, but it can't predict hills or know that I'd like to pass this truck at a slightly faster pace, so I don't always rely on it. That's pretty mature technology but it's far from perfect, even in its most advanced implementations (like maintaining a certain distance from the car in front of you, adjusting for its speed - I don't know if that's a good thing, either).


Nested table hierarchy: foreign key duplication vs. join

You should go with second option obviously for many reasons:

Will you add user id again if you need to come up with an entity at same level of comment? May be it  won't happen in your scenario, but still.
Storage, as your database size grows
There won't be performance impact much as your query is highly selective and if you have good index design.


You can't enable async auto-update stats for just one table. The setting is at the database level.

I would recommend keeping auto-update stats enabled and also having an UPDATE STATISTICS job that runs on a schedule, perhaps daily. You could test doing a 10% sampling on the large tables rather than a more expensive fullscan.


Deleting rows from tables without FK relationships

Your example would allow for the cascading delete that a cascading delete foreign key would.  But I would argue that a foreign key does much more than facilitate simple cascading operations.  Borrowing from the documentation on FOREIGN KEY Constraints:

  A foreign key (FK) is a column or combination of columns that is used
  to establish and enforce a link between the data in two tables.  You
  can create a foreign key by defining a FOREIGN KEY constraint when you
  create or modify a table.

  In a foreign key reference, a link is created between two tables when
  the column or columns that hold the primary key value for one table
  are referenced by the column or columns in another table. This column
  becomes a foreign key in the second table.

  For example, the Sales.SalesOrderHeader table in the
  AdventureWorks2008R2 database has a link to the Sales.SalesPerson
  table because there is a logical relationship between sales orders and
  salespeople. The SalesPersonID column in the SalesOrderHeader table
  matches the primary key column of the SalesPerson table. The
  SalesPersonID column in the SalesOrderHeader table is the foreign key
  to the SalesPerson table.
  Blockquote

  Although the main purpose of a FOREIGN KEY constraint is to control
  the data that can be stored in the foreign key table, it also controls
  changes to data in the primary key table. For example, if the row for
  a salesperson is deleted from the Sales.SalesPerson table, and the
  salesperson's ID is used for sales orders in the
  Sales.SalesOrderHeader table, the relational integrity between the two
  tables is broken; the deleted salesperson's sales orders are orphaned
  in the SalesOrderHeader table without a link to the data in the
  SalesPerson table.

  A FOREIGN KEY constraint prevents this situation. The constraint
  enforces referential integrity by guaranteeing that changes cannot be
  made to data in the primary key table if those changes invalidate the
  link to data in the foreign key table. If an attempt is made to delete
  the row in a primary key table or to change a primary key value, the
  action will fail when the deleted or changed primary key value
  corresponds to a value in the FOREIGN KEY constraint of another table.
  To successfully change or delete a row in a FOREIGN KEY constraint,
  you must first either delete the foreign key data in the foreign key
  table or change the foreign key data in the foreign key table, which
  links the foreign key to different primary key data.


psql, I try to connect to a database and I get "psql: FATAL: Peer authentication failed for user ", why?

You're not asked for a password because the pg_hba.conf configuration file in CentOS says that a connection through a Unix domain socket implies the peer authentication. peer succeeds only if your OS user is the same as your database user. It's a reasonable default.

You may edit pg_hba.conf to indicate that anyone can connect locally to davide3 to certain databases or all databases, or more generally whatever policy is best suitable in your case.

The file is documented at:
https://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html

Alternatively, keep the default policy and try to add -h localhost to psql invocation. This should trigger a different authentication rule (md5) in the default pg_hba.conf that will ask for the password.


Setting up relationships properly with the Access UI

I'm with Michael, you should reference the IP ID, not the IP. If you don't link to the primary key, whenever you reference [IP used when creating] or [IP last signin], you are going to get the first record found. e.g. you have multiple records with the same IP in you ips table, you are going to always get the result of the first one. This is why primary keys are so important, they ensure you get the same record every time. If you can assume that a particular IP will always have the same Hostname, Location, Region, etc., then you can sue IP as your reference.

On a side note, if you have to run any SQL or VBA against your database, it's a lot easier if you avoid spaces in your names, use Camel case or something else. e.g. "IP used when creating" should be IPusedWhenCreating. This makes any type of programming a lot easier. I'm very oldschool and I like to keep variables to under 8 characters whenever possible. FirstIP and SecondIP, it just means a LOT less typing.


Resolving port 5500 not free issue during Oracle 12c Release 2 Database Configuration Assistant installation on CentOS

I encountered this exact error and found it was my mistake. Spent whole day for that.

Please, carefully observe your /etc/hostname and /etc/hosts.
In my case, "oem" was put in /etc/hosts and "eom" was in /etc/hostname.


To import data into a different schema you need to grant IMP_FULL_DATABASE role to the user. Oracle MOS Doc ID 351598.1.

Or you can use SYS user:

impdp \'/ as sysdba\' FULL=Y DUMPFILE=alpha06022014.dmp LOGFILE=alpha.log


Find insert/update/delete queries that reference a partitioned view

You can query from sql cache for long time:

SELECT cp.objtype AS ObjectType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.usecounts AS ExecutionCount,
st.TEXT AS QueryText,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st

You can add a where condition for the view name and the insert/update keyword in st.text.
You can schedule a job and save te results in a table and then check if the table contains rows for update/insert statements against your view.


boolean purpose column char(0) or tinyint(1)

You should keep in mind it is impossible to index a CHAR(0) column, even as part of a compound index.

In terms of size nothing beats NULLable CHAR(0); but how many of these do you have? As compared to other data types in your table? Typically when compared to overall data types & size this is neglect-able.

The clearest is BOOL/TINYINT UNSIGNED. It is most obvious what values are expected.

Otherwise please consider many alternatives in my post, and please see good ideas on comments: Choosing MySQL boolean data types


I would like to understand better the difference between "sql server stand alone" and "sql server failover instance"

First, lets define a few terms:

SQL Server Failover Cluster / Always On Failover Cluster Instance

These terms are interchangeable. An Always On Failover Cluster is just the modern term for a SQL Server Failover Cluster or SQL FCI. An FCI consists of multiple nodes in a Windows Server Failover Cluster with Shared Disks.

SQL Server software is installed on each node, however, the instance that is installed is a special instance that is clustered - a Failover Cluster Instance. The WSFC service manages the failover of the SQL Instance and Shared Disks between nodes to provide high availability and disaster recovery. The SQL Server service for that instance exists on all nodes but is only started and running on the active node of the cluster.

For all intents and purposes, there is only one instance and it moves between nodes along with its underlying Shared Disks. Data synchronisation between the nodes is achieved by using the same underlying disks, so data states between nodes are identical.

SQL Server Always On Availability Group

A SQL Server Always On Availability Group (SQL AG) is a modern solution for high availability and disaster recovery that does not require Shared Disk infrastructure (in fact, even a WSFC cluster is not required in some cases, but that's a different topic).

It still utilises a WSFC cluster to manage failover and availability, however, the synchronisation of data between nodes does not leverage shared disks to ensure data is up to date, instead, a mechanism is used entirely within SQL Server to transport blocks of data between nodes, or Replicas as they're called in an AG.

Each Replica in an AG architecture is an independent SQL Server instance. The underlying machine is joined to the WSFC cluster as a node and the SQL Server instances on each node have the Always On feature enabled.

When an AG is created between these replicas, the data movement mechanisms begin moving data between the replicas for the databases that are joined to the AG. This is very similar to, and built on the same technology as, database mirroring from earlier versions of SQL Server. Data synchronisation is achieved using this 'mirroring' technology.

Standalone SQL Server Instance

A Standalone SQL Server Instance is quite simply a SQL Server instance that is not linked or joined with another through HADR technology. As the name suggests, this instance stands alone.

While it may seem like an Always On architecture features multiple standalone instances, because the servers are joined via the underlying WSFC cluster and the instances are joined via the Availability Group, they are not standalone instances.

Cluster vs Cluster

Often the terms cluster, SQL cluster and Always On cluster are thrown around fairly interchangeably between Always On and FCIs but there is an important distinction - a SQL Cluster is a SQL Server Failover Cluster Instance, an Always On cluster doesn't really exist, its an Always On Availability Group and shoud be referred to as such and a 'cluster' typically refers to the underlying Windows Server Failover Cluster that the FCI or Always On Ag is built upon.

Now to your questions:

  Question: If I'm not mistaken we had a SHARED DISK failover cluster.
  So, every database was inside a single disk that was shared with all
  nodes. But what happened for an instance to change to the empty node?

A Shared Disk FCI has the software installed on each node, but the instance is moved between nodes as required. Think of the instance as whereever the master database resides. When an FCI fails over, the shared disk hosting master fails over too so when SQL Server is started on the new node, it starts the master database that has been moved and thereby starts up the instance.

  I mean, there were no SQL instalation there, no instance, but after a
  failover, the instance was there with all its jobs. Does this mean
  that the virtual machine went to the empty node?

Actually, SQL Server was installed on that node. The VM did not move, the instance and all of its DBs, jobs etc moved when the disk hosting the files moved. The SQL Server service on node 2 started up after failover and reconnected to the instance files.

  Does this mean that on that first job we had a sql server failover
  cluster installation? In this new job I just installed the normal
  stand-alone installation instance on both nodes.

In the new environment, it sounds like they have installed an Always On Availability Group architecture.

  One more question. almost weekly we are suffering from failover. I'm
  sure node1 is losing connection with node2 and then the quorum is
  lost. The concept with the quorum is, if node1 lose connection with
  node2, the quorum can say" oh, they are not communitacing with each
  other, but node1 is still alive so I will not failover them. Is this
  right?

Quorum is supposed to be the tie-breaker - if both nodes vote that they cannot see the other one, then each would want to take over the primary role (FCI or AG) and this would lead to split-brain. The WSFC cluster won't let this happen and will shut down the cluster instead.

Quorum gives you a deciding vote when you have an even number of nodes. If the cluster service on node 1 cannot communicate with node 2 but it can communicate with the quorum witness (file share or shared disk), then it has two votes (one from the node and one from the quorum witness) and node 2 only has one vote. It is overruled and node 1 stays live. If Node 2 has the two votes then failover is initiated.

It is important to always have an odd number of votes. In later versions of Windows Server, node weight and node votes are adjusted dynamically to try and enforce an odd number of votes, but with only two votes, it cannot enforce this.

UPDATE -

Below is a diagram from Microsoft that helps illustrate the differences:

As you can see from the diagram, an Availability Group is built across independent instances of SQL Server installed on servers joined to a WSFC cluster. A Failover Cluster Instance is installed as a single instance of SQL Server across multiple nodes in the WSFC cluster, using Shared Storage.

Even though the binaries and executables for SQL Server are installed on all nodes in an FCI, the instance (system databases, user databases etc) is installed on shared storage and is only active on a single node at a time.

In an AG, both independent instances are up and running at the same time, and the AG Listener (a virtual network name) only listens on the primary replica.

This link provides detailed information about business continuity in SQL Server, including AGs and FCIs.


Reading 300% rows - Problematic execution plan

That’s a lot of questions.

I write about Hash Match with Probe Residual here: http://blogs.lobsterpot.com.au/2011/03/22/probe-residual-when-you-have-a-hash-match-a-hidden-cost-in-execution-plans/ - there’s a good chance the problem with your Hash Match finishing is more related to what’s pulling the rows from it than the Hash itself.

As for your stats, the problem is all your ORs. You may find your Seek works better with an index that has KVID_Kontotype as the first key, followed by PeriodeStartDato. Then instead of having a large range scan across all dates earlier than your predicate and checking every single row to see if the Kontotype is correct, it would seek for each Kontotype with the appropriate date range. It would probably estimate better, but fundamentally would read fewer rows to get what you need.


Delete Only Log Backup History in SQL Server

There isn't any builtin code to just delete the transaction log backup history. You'd have to roll your own to achieve that.

But I would recommend that you just dump the data that you want to keep in a separate table and purge msdb backup history normally with sp_delete_backuphistory.


How to merge two large mysql tables into one with similar data keeping the latest record

Since you have very much data at hand, I suggest you merge your date and time columns first. Then you can use an index efficiently. If you don't, you will have to do something like

...WHERE CONCAT(date, ' ', time) = SELECT MAX(CONCAT(date, ' ', time)) ...

So, first do this for both tables.

ALTER TABLE tableA ADD COLUMN creation_date datetime; /*or whatever name, just make it meaningful and don't use keywords*/
UPDATE tableA SET creation_date = CONCAT(date, ' ', time);
ALTER TABLE tableA DROP COLUMN date, DROP COLUMN time;
CREATE INDEX idx_dt_tableA_creation ON tableA(creation_date);

Then you can insert both tables into your combine_table
(Note, left this for completeness, the second option is much better).

INSERT INTO combined_table
SELECT col1, col2, creation_date
FROM (
      SELECT col1, col2, creation_date
      FROM tableA
      UNION ALL
      SELECT col1, col2, creation_date
      FROM tableB
) sq /*subquery_alias*/
WHERE creation_date = (SELECT MAX(creation_date) FROM (
                                    SELECT col1, col2, creation_date
                                    FROM tableA
                                    UNION ALL
                                    SELECT col1, col2, creation_date
                                    FROM tableB
                        ) another_sq
                        WHERE sq.col1 = another_sq.col1
                       )
;

Nonetheless, this will be a heavy operation, if you really have that much data.

Now that I think of it, there's a better way of doing it.

First insert tableA

INSERT INTO combined_table
SELECT * FROM tableA;

Then do an insert on duplicate key update.

INSERT INTO combined_table c
SELECT * FROM tableB b
ON DUPLICATE KEY UPDATE
/*you can skip col1, since it's the identifying primary key here*/
col2 = IF(b.creation_date > c.creation_date, b.col2, c.col2),
creation_date = IF(b.creation_date > c.creation_date, b.creation_date, c.creation_date)
;


Mongodb recommended to have an odd number of servers in the cluster
  but I would like to ask whether or not it's mandatory for the cluster
  to function properly or would it be ok to make a cluster only with 2
  servers?

3 members (or a higher odd number) is really best, though. Replica sets go read only if a majority of the set isn't available, so if you lose a member in a two-member set the remaining member becomes read only.

You can run two full members plus an "voting-only" member (called an arbiter) as well. This is the smallest reasonably safe option for a replica set.

As per graylog documentation here We recommend to  deploy a MongoDB replica set.

MongoDB doesn’t have to run on dedicated servers for the workload generated by Graylog, but you should follow the recommendations given in the MongoDB documentation about architecture. Most important is that you have an odd number of MongoDB servers in the replica set.

In most setups, each Graylog server will also host an instance of MongoDB which is part of the same replica set and shares the data with all other nodes in the cluster.

As MongoDB Documentation here A secondary maintains a copy of the primary’s data set. To replicate data, a secondary applies operations from the primary’s oplog to its own data set in an asynchronous process. A replica set can have one or more secondaries.

The clients cannot write data to secondaries, clients can read data from secondary members

For your further ref here, here


How to serialize on SQL server metadata?

In this case, if you cant serialize this in the application code you can use try/catch or an applock

begin tran
if APPLOCK_TEST ('public','idx_create','exclusive') = 1
 begin
  EXEC sp_getapplock @Resource = 'idx_create',
                     @lockmode = 'Exclusive',
                     @DbPrincipal='public'
   print 'I can do it'

  -- EXEC sp_releaseapplock @Resource='idx_create', @DbPrincipal='public'
  -- scope is transaction see comment below
   commit
 end
else rollback ;


Azure sql database grant select on certain tables

You need to ensure the login is added on all the levels

---- ON SERVER LEVEL
CREATE LOGIN TestUser
    WITH PASSWORD = 'ThisIsAStrongPassword!'
GO

---- ON Master Level
CREATE USER TestUser
    FOR LOGIN TestUser
    WITH DEFAULT_SCHEMA = dbo

---- ON SQL DB LEVEL
CREATE USER TestUser
    FOR LOGIN TestUser
    WITH DEFAULT_SCHEMA = dbo
GO

GRANT SELECT ON OBJECT::dbo.Sales TO TestUser;
GO

You need to add the login on server level under the master context

then add a user to the master db under master context

and then add the user to the sqldb and grant its rights

EDIT You cannot use the use statement, so you will have to manually switch context in SSMS


How is possible to configure TLSv1.1 protocol for SSL connection in PostgreSQL?

@BrianEfting was correct, you can specify the appropriate cipher suites to only allow TLSv1.2 which should fit your PCI-DSS 3.1 specification needs.

Using a cipher list like this in the ssl_ciphers option in your postgresql.conf:

ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES256-GCM-SHA384:DHE-RSA-AES128-GCM-SHA256:DHE-DSS-AES128-GCM-SHA256:DHE-DSS-AES256-GCM-SHA384:DHE-RSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-SHA256:ECDHE-ECDSA-AES128-SHA256:ECDHE-RSA-AES256-SHA384:ECDHE-ECDSA-AES256-SHA384:DHE-RSA-AES128-SHA256:DHE-DSS-AES128-SHA256:DHE-RSA-AES256-SHA256:!aNULL:!eNULL:!EXPORT:!DES:!RC4:!3DES:!MD5:!PSK

along with setting ssl_prefer_server_ciphers=true, should be sufficient to allow only TLSv1.2 connections.

You can verify this using SSLyze which knows about the PostgreSQL protocol.

To test, I used the following command:

./sslyze.py --sslv2 --sslv3 --tlsv1 --tlsv1_1 --tlsv1_2 localhost:5432 --starttls=postgres --hide_rejected_ciphers

Which gave the output below under PostgreSQL 9.4 on Debian Wheezy showing that all cipher suites except for the TLSv1.2 ciphers specified were rejected, which should satisfy the requirements of PCI-DSS 3.1 by using TLSv1.1 or greater.

postgres@pgsqlsec4:~/sslyze$ ./sslyze.py --sslv2 --sslv3 --tlsv1 --tlsv1_1 --tlsv1_2 localhost:5432 --starttls=postgres --hide_rejected_ciphers

 AVAILABLE PLUGINS
 -----------------

  PluginCompression
  PluginHeartbleed
  PluginChromeSha1Deprecation
  PluginSessionRenegotiation
  PluginOpenSSLCipherSuites
  PluginSessionResumption
  PluginHSTS
  PluginCertInfo

 CHECKING HOST(S) AVAILABILITY
 -----------------------------

   localhost:5432                      => ::1:5432

 SCAN RESULTS FOR LOCALHOST:5432 - ::1:5432
 ------------------------------------------

  * SSLV2 Cipher Suites:
      Server rejected all cipher suites.

  * TLSV1_2 Cipher Suites:
      Preferred:
                 ECDHE-RSA-AES128-GCM-SHA256   ECDH-256 bits  128 bits
      Accepted:
                 ECDHE-RSA-AES256-SHA384       ECDH-256 bits  256 bits
                 ECDHE-RSA-AES256-GCM-SHA384   ECDH-256 bits  256 bits
                 DHE-RSA-AES256-SHA256         DH-1024 bits   256 bits
                 DHE-RSA-AES256-GCM-SHA384     DH-1024 bits   256 bits
                 ECDHE-RSA-AES128-SHA256       ECDH-256 bits  128 bits
                 ECDHE-RSA-AES128-GCM-SHA256   ECDH-256 bits  128 bits
                 DHE-RSA-AES128-SHA256         DH-1024 bits   128 bits
                 DHE-RSA-AES128-GCM-SHA256     DH-1024 bits   128 bits

  * TLSV1_1 Cipher Suites:
      Server rejected all cipher suites.

  * TLSV1 Cipher Suites:
      Server rejected all cipher suites.

  * SSLV3 Cipher Suites:
      Server rejected all cipher suites.

 SCAN COMPLETED IN 0.73 S
 ------------------------
postgres@pgsqlsec4:~/sslyze$


How do I override the dates with Temporal tables?

The time in a temporal table is "system time" not "business time".  That's the distinction that the feature name "System-Versioned Temporal Table" is getting at.

You inserted the data today, so it's versioned as of today.  You can use the temporal table to track the changes you make going forward, and retain change history, but it doesn't replace a slowly-changing dimension for business analytics.  As you noted, Temporal Tables belong in the "Data Lake" or "Raw" or "Staging" layer.

If you want to add additional history representing changes to the data before your initial load, you can turn system versioning off temporarily while you load data directly into the history table.


What a closure `F` of a family of functional dependencies is?

When you have a relation R with a set of functional dependencies F and a decomposition of R in R1...Rn, you must consider two different concepts:

The closure of the set of functional dependencies F. This closure, called F+, is the set of all the dependencies derived from F, by applying, until possible, a set of rules called “Armstrong’s axioms”. This set can be very lange, (exponential with the number of dependencies of F), so in general it is not calculated. What can be calculated, instead, is a cover of it, which is a small number of dependencies from which F+ can be derived (and so which is equivalent to F+). This computation is useful, for instance, when you normalize a relation.
The projection of the set of dependencies F, over the decomposition of R. By definition, this projection is, for each Ri, the set of all the dependencies of F+ that have all the attributes in Ri (and is represented as πRi(F)). But, since we do not calculate F+, as said above, we cannot calculate such projection. What can be done in practice, in simple cases, is to see if there is some dependency in F that has all its attributes contained in Ri, so we are sure that such dependency holds in  Ri. However, if in this way you don’t find a dependency in one of the decomposed relation, this is not a guarantee that such dependency is lost, since it could hold as consequence of other dependencies.

In your first example, you have two dependencies in F for the relation R(S, Ex, D):

D, St → Ex
Ex → D

So, consider R2 = (Ex, D), we can see immediately that in the dependency Ex → D holds in R2, since all its attributes are contained in R2, while the first dependency cannot hold neither in R1 and R2, since it has three attributes. Actually, in relation R1, you have neither the first, nor the second dependency (only trivial dependencies from F+ are present in R1, like St → St).

In your second example, instead, the decomposed relations are such that in the first there are all the attributes of the first dependency, so it hold trivially in it, while in the second there are all the attributes of second dependency, and again you can be sure that this second dependency holds in the second relation.

Finally, note that, while it is no computationally feasible to calculate πRi(F), there is an efficient polynomial algorithm to see if a certain decomposition preserves the dependencies, in other words, if ∪πRi(F) is a cover of F+. You can see more details in this answer.


Propper way to monitor SQL Server mirror status from service or job?

As stated in Monitoring Database Mirroring MSDN

You can monitor a mirrored database during a mirroring session to verify whether and how well data is flowing. To set up and manage monitoring for one or more of the mirrored databases on a server instance, you can use either Database Mirroring Monitor or the sp_dbmmonitor system stored procedures.

  A database mirroring monitoring job, Database Mirroring Monitor Job,
  operates in the background, independently of Database Mirroring
  Monitor. SQL Server Agent calls Database Mirroring Monitor Job at
  regular intervals, the default is once a minute, and the job calls a
  stored procedure that updates mirroring status. If you use SQL Server
  Management Studio to start a mirroring session, Database Mirroring
  Monitor Job is created automatically.

Also, if you want you can use the sys.database_mirroring view. You can query this Catalog View and raise an email alert for each mirrored database that we find in an abnormal state using below script as explained here

DECLARE @state VARCHAR(30)
DECLARE @DbMirrored INT
DECLARE @DbId INT
DECLARE @String VARCHAR(100)
DECLARE @databases TABLE (DBid INT, mirroring_state_desc VARCHAR(30))

-- get status for mirrored databases
INSERT @databases
SELECT database_id, mirroring_state_desc
FROM sys.database_mirroring
WHERE mirroring_role_desc IN ('PRINCIPAL','MIRROR')
AND mirroring_state_desc NOT IN ('SYNCHRONIZED','SYNCHRONIZING')

-- iterate through mirrored databases and send email alert
WHILE EXISTS (SELECT TOP 1 DBid FROM @databases WHERE mirroring_state_desc IS NOT NULL)
BEGIN
SELECT TOP 1 @DbId = DBid, @State = mirroring_state_desc
FROM @databases
SET @string = 'Host: '+@@servername+'.'+CAST(DB_NAME(@DbId) AS VARCHAR)+ ' - DB Mirroring is '+@state +' - notify DBA'
EXEC msdb.dbo.sp_send_dbmail 'valid_mail_profile', 'DBA@mssqltips.com', @body = @string, @subject = @string
DELETE FROM @databases WHERE DBid = @DbId
END

--also alert if there is no mirroring just in case there should be mirroring :)
SELECT @DbMirrored = COUNT(*)
FROM sys.database_mirroring
WHERE mirroring_state IS NOT NULL
IF @DbMirrored = 0
BEGIN
SET @string = 'Host: '+@@servername+' - No databases are mirrored on this server - notify DBA'
EXEC msdb.dbo.sp_send_dbmail 'valid_mail_profile', 'DBA@mssqltips.com', @body = @string, @subject = @string
END


Master mysq8 slave Mariadb 10.3 Error 22

The Master should not be a newer version than the Slave(s).  In this case, 8.0 has a lot of new collations that do not exist in 5.7.  And the defaults pull you toward them.

Plan A:  Change the collations in the Master.
Plan B:  Upgrade the Slave to 8.0.

Meanwhile, suggest filing a bug at bugs.mysql.com pointing out how awkward it is to upgrade a Master-Slave setup with 8.0 calling for a collation that does not exist in 5.7.


what is the quickest way to delete duplicate rows?

If the table is small and the number of rows you are deleting is small, then use

;WITH Customer AS
    (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY AccountCode ORDER BY (select null) ) AS [Version]
    FROM dbo.Customer
    )
    DELETE
    FROM    Customer
    WHERE   [Version] > 1;

Note: In above query you are using an arbitrary ordering in the window order clause ORDER BY (select null) (learned it from Itzik Ben-Gan's T-SQL Querying book and @AaronBertrand cited that above as well).

If the table is large (e.g. 5M records) then deleting in small number of rows or chunks will help not bloat transaction log and will prevent lock escalation.

  A lock escalation will occur if and only if a Transact-SQL statement has acquired at least 5000 locks on a single reference of a table.

while 1=1
begin
WITH Customer AS
    (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY AccountCode ORDER BY (select null) ) AS [Version]
    FROM dbo.Customer
    )
    DELETE top(4000) -- choose a lower batch size than 5000 to prevent lock escalation
    FROM    Customer
    WHERE   [Version] > 1

    if @@ROWCOUNT < 4000
    BREAK ;

end


ORA-01031: insufficient privileges ALTER PLUGGABLE DATABASE to OPEN

Just grant SYSDBA/DBA privilege to the user to all containers and login as SYSDBA to perform the task.

Demo:

SQL> create user c##admin identified by c##admin container=all;

User created.

SQL> grant sysdba to c##admin container=all;

Grant succeeded.

SQL> conn c##admin as sysdba
Enter password:
Connected.
SQL> alter pluggable database orclpdb open;


End of file reached. Error 38 in SQL. Passes verification checks

I removed all compression all together (zipping the .bak via ZipArchive library in .NET) and pass it up to google and it works fine now. Something must be going wrong in the compression. Thank you for the lead into looking at compression =)

Originally left in a comment by user Austen Swanson (the OP).

So rather than the database being corrupt, the backup was getting corrupted in the process of trying to compress (or decompress) it using third-party compression.


What to do when MySQL is not generating any logs at all on Debian?

Thanks to commenters for their insights. The solutions was:

Add "log_errors = /var/log/mysql/mysql.err" to my.cnf
See permissions error in error log
chmod log files to "mysql:root"


How do I store data with a query that's approximated?

You may want to try the retrieval (re trie val) tree, as known as the TRIE. Some refer to this as a Radix Tree.

The idea is to create tree node structures that contain branches for every character your data field can possibly contain.

Let's use a simple case, a numeric field. Obviously, the character range is 0-9. Each tree node would contain ten(10) branches. Let's take the worst case for a 4 byte unsigned integer, 2^32 - 1, which is 4294967295. What is its length? Just compute the length by take the integer of the log base 10 of 4294967295 and adding 1.

mysql> select floor(log10(power(2,32)) + 1);
+-------------------------------+
| floor(log10(power(2,32)) + 1) |
+-------------------------------+
|                            10 |
+-------------------------------+
1 row in set (0.00 sec)

So, you would have a TRIE with a maximum height of 10. Starting at the root of the TRIE, if you have the number 4294967295, you traverse branches 4,2,9,4,9,6,7,2,9, and 5. At each branch, you would perform an array-styled binary search.

If the branch located at that TRIE node is an exact match, you can assign a percentage to that level, and recursively walk down that branch to check for the next digit and return percentages from deeper TRIE nodes to add to the percentage you have at the currently searched TRIE node.

If the branch located at that TRIE node is NOT an exact match, you stop your recursive search there and return either 0 or some other percentage you may want to designate.

Given the sum of return values from all searched TRIE nodes, you may want to sum up the
percentages and divide that answer by the length of the string. In other words,

Pct per Node = (1 / (Number of TRIE nodes that need to be searched)) or Zero(0).

Sum(Pct) = (Number of TRIE nodes exactly matched) / (Number of TRIE nodes that need to be searched [length of the string being searched]).

Given the length of the numberic field you store, you have O(log n) due to field length. For each TRIE node, you have O(log n) for searching for the proper branch. Overall, your search should have O(log (log n)) search time.

This performance stands out ever more if the field is alphanumeric. Assuming using only ASCII, each TRIE node would have 256 branches. The height of the TRIE would depend on the length of the character field. Representing this TRIE for variable-length strings would produce TRIE nodes that would be very sparse, but quickly searchable nonetheless.

Regardless what database you use, carefuly plan the data types you will be using to represent the TRIE node. You may also want to partition the table so that strings of length n terminate in partition n. Thus, you will have O(log n) search time at each partition.

http://en.wikipedia.org/wiki/Trie

http://www.eecs.harvard.edu/~ellard/Q-97/HTML/root/node24.html

http://www.webreference.com/js/tips/000318.html

http://en.wikipedia.org/wiki/Radix_tree


No rows in sys.dm_exec_procedure_stats for a particluar stored procedure

By using this DMV, you can returns metrics for stored procedures, but
  it does not directly identify the stored procedure by name.  Instead,
  the DMV only identifies the object_id, and a database_id for each
  stored procedure:  Therefore to identify the actual stored procedure
  name, that the performance figures belong to, you should either join
  the output of this DMV with one of the system views within the
  appropriate database, or use a few metadata functions

Therefore you should consider using sys.dm_exec_query_stats along with one you mentioned in you're question to get stats.

Also,

You can look in the plan cache to get a pretty good idea of stored procedure usage.

SELECT DB_NAME(st.dbid) DBName
      ,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName
      ,OBJECT_NAME(st.objectid,dbid) StoredProcedure
      ,max(cp.usecounts) Execution_count
 FROM sys.dm_exec_cached_plans cp
         CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
 where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'
   group by cp.plan_handle, DB_NAME(st.dbid),
            OBJECT_SCHEMA_NAME(objectid,st.dbid),
   OBJECT_NAME(objectid,st.dbid)
 order by max(cp.usecounts)

There are various other queries via which you can analyse the metrics for your stored procedures. Monitoring stored procedure usage

Also, read here


PowerShell script to send an email with SQL results in HTML format

Following is a script I use to send query results in an HTML-formatted email.  Basically pipe the result of invoke-sqlcmd to ConvertTo-Html and then to Out-String, and then massage it a bit to get the table to display nicely.  If you don't add the padding style, everything will run together.

Send-MailMessage example below is for unathenticated, so if you have to authenticate to your SMTP server you'll need to fix that up

$Alerts = invoke-sqlcmd -ServerInstance dbserver -Database dbname -Query "SELECT * FROM some_table" | SELECT Severity, ServerName, Heading | ConvertTo-Html | Out-String
$Alerts = $Alerts.Replace("", "")
Send-MailMessage -To me@mycompany.com -From me@mycompany.com -BodyAsHtml $Alerts -Subject "Alerts" -SmtpServer "smtp.mycompany.com"


How do I develop this data model without a circular reference?


Does MySQL updated index if index key is not updated?

It is logical to think that the current databases are smart enough to not update the index which data have not changed.

But this logic should be straight supported by Mysql manual. And I've really found this statement on page which describes Speed of UPDATE Statements:

  An update statement is optimized like a SELECT query with the
  additional overhead of a write. The speed of the write depends on the
  amount of data being updated and the number of indexes that are
  updated. Indexes that are not changed do not get updated.


stress-testing MySQL slave with master's queries

I would collect queries from Master via slow log and then reply them on the slave using http://www.percona.com/doc/percona-toolkit/2.1/pt-log-player.html to do this you will need:

Enable full slow query logging. You need to be careful with the IO load if your server is really busy. You may want to do it during low time (say at night)
Collect good amount of queries to play with
Replay them using pt-log-player on slave using filter (you don't want to replay INSERTs/UPDATEs/DELETEs as it will ruin data consistency on slave and you'll have to recreate it.


Not sure that I got it right, but:

select tag, main_tag
from foo
group by tag, main_tag
having sum(value) = 0;

retrieves tag, main_tag where sum(value) is 0. You can join this result with the original table as:

select f.*
from foo f
join (
    select tag, main_tag
    from foo
    group by tag, main_tag
    having sum(value) = 0
) as t
    on t.tag = f.tag
   and t.main_tag = f.main_tag;


Can I create a new user in MS SQL from windows and provide the SID

No, when creating a login from Windows, the SID is the Windows-provided one.

There is consequently no need to remap users; they automatically assume the same SID across servers within the same domain.

As an aside, instead of saying "SQL does not like it", you should provide the actual error message.  It helps us provide a more meaningful answer, and in most cases, such as this, it also provides the actual answer.


Using the information I've received from here, the web and several internet chat rooms, I've come up with.  Web source: http://www.softwareprojects.com/resources/programming/t-how-to-use-mysql-fast-load-data-for-updates-1753.html

  DEMO: http://sqlfiddle.com/#!2/4ebe0/1

The process is:

Import into a new temp table.
Update The old table information with information in Temp table.
Insert new data into the table. (Real world I'm making a new CSV file and using LOAD INTO for the insert)
delete everything that is no longer in the data feed.
delete the temp table.

This seems the fastest processes so far.

Let me know what your opinion is.


Does `Seconds_Behind_Master` show exact Slave Lag from Master?

Seconds_Behind_Master is based on the difference between UNIX_TIMESTAMP() and the timestamp logged for the query within the binary log of the master, or the relay log on the slave. Seconds_Behind_Master actually gets you lost in context of realtime if replication processes a series of long running queries. The lag could grow astronomically until all relay log entries are processed and then Seconds_Behind_Master will suddenly drop to zero. In terms of realtime, you have no way to knowing or anticipating when the lag will eventaully dissipate until it hits zero.

Strictly using mysql, you can monitor replication lag in terms of realtime in the following manner:

From SHOW SLAVE STATUS\G, get two values

Relay_Master_Log_File represents log file containing the last successfully execute SQL statement on the Master that was executed on the Slave.
Exec_Master_Log_Pos represents the position within Relay_Master_Log_File of the last successfully execute SQL statement on the Master that was executed on the Slave.

You could perform this against that binary log:

TMSTMP=`mysqlbinlog ---start-position=EMLP RMFL | head -50 | grep "^SET TIMESTAMP=" | head -1 | sed 's/=/ /g' | sed 's/\// /g' | awk '{print $3}'`
RIGHTNOW=`date +%s`
(( REPLAG = RIGHTNOW - TMSTMP ))

where EMLP is Exec_Master_Log_Pos RMFL and is the Relay_Master_Log_File

While this is realistically the correct way to get replication lag using only the log file and postition, you must

communicate with the slave to get the needed log file and position
communicate with the master to retrieve the dump of the binlog
dump the binlog each time you check (mysqlbinlog works safely when it it not the currently open log, this may require performing FLUSH LOGS on the master before dumping the master log file)

Getting replication lag from the binary logs yourself takes a lot more leg work.

mk-heartbeat checks for a heartbeat table and record only. Using a live table on a master and comparing the slave's copy of the heartbeat table to UNIX_TIMESTAMP() on the slave is a much more concise realtime lag measurement.

You should go with mk-heartbeat.


Suitable schema to record email sent and response received and invoicing in DB

Assuming the system handles only a few actions per second and only millions of ids, either flavor of schema design should work well enough.

I can think of no strong argument for, or against, splitting the confirmations off into a separate table -- performance, sparseness (or lack of) in the Confirmations table, 1:1 relationship, coding convenience, purging, etc.

If you anticipate hundreds of actions per second or billions of ids, then you should sketch out more details, including INSERTs, UPDATEs, and SELECTs.  Plus consider a purging policy (which might include PARTITIONing by date).

You've had the foresight to think about the 'right' design -- Good.  Suggest you plan on significant redesign after maybe a year of use.  Build a layer between you application an MySQL.  Then focus on the application without fretting over the schema design.  With luck, the impact of the redesign will be mostly limited to the 'layer'.

For example, the layer would be asked to SendConfirmation(id); it would then either UPDATE (if one table) or INSERT (if 2 tables).


How to increase agent history rendering timeout in SSMS?

No unfortunately it is not possible. As @Newman mentioned, you can query the msdb.dbo.sp_help_jobhistory procedure to get the data back or you can adjust your jobs timeframe cutoff or total number of rows cutoff (whichever is more suitable to your business needs): https://docs.microsoft.com/en-us/sql/ssms/agent/resize-the-job-history-log?view=sql-server-ver15


As I understand it the resizing of the high water mark requires an exclusive table lock, which in theory might not be a problem as it would be of very short duration. However if it has to wait on other transactions before it gets the lock it will block other transactions.

https://forums.oracle.com/forums/thread.jspa?threadID=2453756

However as the docs point out, you could run a shrink space compact, which does not move the HWM, and run the shrink later on when the system is known to be quiet: http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_3001.htm#i2192484


Looks like (I'm by no means a Postgres expert) you have about two million rows in LEAD_DETAIL table that satisfy the state condition... those two million rows are retrieved, hash joined to leads, the result sorted to return the top 5000 rows. Could you move the state to leads table, so that it becomes a column there? Create and index on (upper(state), created_date_time) and try again (of course, rewriting your query to use the new state column in leads table).

And by the way, most of your indexes on leads table are useless. Why have lead_created_date_time_idx and lead_created_date_time_idx1 - they are the same, an ascending index can be used also for DESC sorts. Furthermore, why include lead_id in indexes (except the one supporting the primary key constraint)?


It's because,

When a client tries to make a connection, it uses the protocols order by priority defined by SQL Server configuration manager. A low value means a high priority. If you configured TCP/IP as first priority and SQL Server is not able to resolve Name to IP it raise the error you posted. But when you disable TCP/IP, then because TCP/IP with first priority is disabled client uses Named Pipe (with second priority) to connect to SQL Server. And get a successful connection.

Here, you can set the priority of protocols by moving it UP or DOWN.

Thanks


What should I use? A string or 15 integer fields?

If you are already talking about splitting and computing, don't store this as an array.

Regardless of the relational theory and traditional normalization rules and dogma, it's simply a design which gives you MINIMAL flexibility.

Make each exam result a row.

I'm not trying to anticipate everything, but there are a very large number of things which this more granular (and, yes, normalized) and only ever so slightly more space expensive design facilitates which you may or may not need now and may or may not need in the future:

Throwing out highest and lowest result?  You will have to slice up your array and sort it.
Averaging?  You will have to slice it up and total it
Analysis of exam result by exam across students?  You'll have to slice and pivot
Sorting for counting (or instance British GCSEs, where it might be 7 As and 2Bs)?  You'll have to slice and sort

Note that all this slicing and sorting comes very cheaply in an indexed, normalized design.


Changing field length when foreign keys reference primary key field in table

Script out Drop and Create All 30 JF.
Drop all 30 KF
Alter Columns
Create all 30 KF


SQL Server 2008 R2 copy Schema with all user security permissions

This should give you a start. It assumes that you've already cloned all the relevant objects and that all of your permissions are at the object or schema level (if you have column-level permissions, the script gets a little more complex, but not too bad).

DECLARE
  @oldschema SYSNAME = N'schema1',
  @newschema SYSNAME = N'schema1_dev';

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

SELECT @sql += N'
 ' + p.state_desc + N' '
 + p.[permission_name] + N' ON '
 + QUOTENAME(@newschema) + N'.' + QUOTENAME(o.name)
 + N' TO ' + QUOTENAME(dp.name) + N';'
FROM sys.database_permissions AS p
INNER JOIN sys.objects AS o
ON p.major_id = o.[object_id]
INNER JOIN sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
INNER JOIN sys.database_principals AS dp
ON p.grantee_principal_id = dp.principal_id
WHERE p.class_desc = N'OBJECT_OR_COLUMN'
AND s.name = @oldschema;

SELECT @sql += N'
  ' + p.state_desc + N' '
  + p.[permission_name] + N' ON SCHEMA::'
  + QUOTENAME(@newschema)
  + N' TO ' + QUOTENAME(dp.name) + N';'
FROM sys.database_permissions AS p
INNER JOIN sys.schemas AS s
ON p.major_id = s.[schema_id]
INNER JOIN sys.database_principals AS dp
ON p.grantee_principal_id = dp.principal_id
WHERE p.class_desc = N'SCHEMA'
AND s.name = @oldschema;

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

Note that if you have a lot of objects and/or a lot of users, the PRINT output might not show everything.


Error 1236 'Found old binary log without GTIDs' after master restart

Something is not right about your process.

Usually, when I see error 1236, such the following I used in my old post How can you monitor if MySQL binlog files get corrupted?

[ERROR] Error reading packet from server: Client requested master to start replication from impossible position ( server_errno=1236).
[ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position', Error_code: 1236
111014 20:25:48 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.001067', position 183468345.

here was the situation: When doing MySQL Replication without GTID, the IO Thread examines the position from the latest Master binlog. If Read_Master_Log_Pos is bigger than the actual filesize of the binlog, you get error 1236.

When doing MySQL Replication with GTID, the situation is somewhat similar. The IO Thread is looking for some kind of closure with regard to the GTID it was last using. When you restarted MySQL on the Master, you closed the last binlog on the Master and opened a new binlog upon startup. The IO Thread on the Slave was still active. Thus, the same error number is coming up.

The next time you restart a Master, remember the Slaves are active.

The slave should have reconnected after a minute, but that is not happening for you.

To play it safe, you should do the following

On the Slave, STOP SLAVE;
On the Master, service mysql restart
On the Slave, START SLAVE;

You should not have to do this. As an alternative, try setting up replication with heartbeat set at one tenth of a second:

CHANGE MASTER TO MASTER_HEARTBEAT_PERIOD = 100;

This should make the IO Thread on the Slave a little more sensitive


Are there limitations on using pg_dump on a materialized view such that the data won't be included in the results?

You can not reload data manually to a MV, so it doesn't make much sense for pg_dump to dump the data itself out for a MV.

If you dump the underlying table plus the materialized view, the dump file should load correctly by loading the tables, then refreshing the materialized view from them.  I don't think there is any simple way to give it just the MV name and have it include the dependencies, you have to track those yourself.

If you want the materialized view restored as a stand-alone table, you can stitch those together yourself.  Get the definition of the materialized view from pg_dump -s -t a.mv, but then you will have to edit it to make it create a real table, not an mv.  Then you can dump the data itself with

\copy (select * from mv) to mv.txt

And load it back on the other server with

\copy mv from mv.txt


The key architectural difference between the two, is in CAP configuration (as-in Brewer's CAP Theorem).  Riak is considered to be an "AP" database (High-availability, Partition-tolerant), whereas MongoDB is a "CP" (Strong-consistency, Partition-tolerant).  The underlying question becomes whether the more-important aspect of your system is to read your data or to write your data.  If successful writes are more-important, and you can handle the fact that not all nodes will be consistent (or will be via "eventual consistency") then I would say Riak is the better choice.  If the more-important aspect is that each read returns accurate data a high percentage of the time, and you're prepared to sacrifice a little performance (due to locking) to get it, then MongoDB is the better choice.

To get an understanding of databases/tables/buckets, make sure to read-up on the Riak data model before getting too deep in your application's design.  The only "gotchya" I would caution against, is that I've heard people use many words to describe doing a Map-Reduce, and "easy" was never one of them.  If that functionality is a key component of your application, I'd make sure to investigate that thoroughly before making any decisions.

Here's an article that does a comparison of Riak vs. MongoDB.  It's by Basho (full-disclosure), so it may have some bias.  Additionally, here's a comparison of several NoSQL databases that you may find useful as well.


Commit vs Fast Commit vs Commit Cleanout in Oracle Database

You have the basics right. There is only one type of commit (no normal, fast...).

from the concepts doc:

  When a transaction commits, the following actions occur:

  A system change number (SCN) is generated for the COMMIT.

  The internal transaction table for the associated undo tablespace
  records that the transaction has committed. The corresponding unique
  SCN of the transaction is assigned and recorded in the transaction
  table. See "Serializable Isolation Level".
  The log writer (LGWR) process writes remaining redo log entries in the
  redo log buffers to the online redo log and writes the transaction SCN
  to the online redo log. This atomic event constitutes the commit of
  the transaction.
  Oracle Database releases locks held on rows and tables.

  Users who were enqueued waiting on locks held by the uncommitted
  transaction are allowed to proceed with their work.
  Oracle Database deletes savepoints.
  Oracle Database performs a commit cleanout.

  If modified blocks containing data from the committed transaction are
  still in the SGA, and if no other session is modifying them, then the
  database removes lock-related transaction information from the blocks.
  Ideally, the COMMIT cleans out the blocks so that a subsequent SELECT
  does not have to perform this task.

So cleanout (full with redo) will be performed during commit if the blocks are still in the SGA.

In active systems, it is common for blocks with uncommited transactions to be written to disk and flushed from the SGA. In this case, the block is left as is and the next query that touches the block will perform delayed block cleanout (your point 5 doesn't happen in all cases).


Migrate database from SQL Server 2005 to SQL Server 2008 R2

In order.

Some easy options: You could look at the start->run services.msc and see which services are there; you could run powershell and look at run gsv *SQL*; or you could look at the Start menu->Microsoft SQL Server 2008 R2 and see what's there.
If you're a database newbie, I would take a look at the excellent database maintenance scripts from Ola Hallengren.  These cover backups, index maintenance and database integrity checks.
I would migrate the database by taking a backup of the old database and restoring that to the new server - this will bring all objects, tables, data, stored procedures etc.  However, you'll then have to migrate any appropriate scheduled tasks and user IDs.  There are some SQL Server Integration Services tasks that'll help with jobs and logins (indeed, they'll also help with the data migration...).

Lastly, welcome to the job.  I would recommend that you have a trawl through Red Gate's bookstore - there are a lot of free PDF versions of their books.  Have a skim of SQL Server Backup and Restore,  Troubleshooting SQL Server for the Accidental DBA.

Good luck!

edit.  A pair of thoughts, relating to your migration from 2005 to 2008 R2 specifically.

You should be OK doing this by using database backup and restore - good news.
However, be aware that it is entirely possible that some features your app relies on may be deprecated / discontinued. Once you get up & running on SQL Server 2008 R2, you can keep an eye out for these features using the sys.dm_os_performance_counters DMV.  There are various blog posts out there, but the gist is:

SELECT * FROM sys.dm_os_performance_counters
WHERE Object_name like '%:Deprecated Features'
AND cntr_value > 0

(unfortunately, tracking Deprecated Features wasn't part of the SQL 2005 release...)


Run a script as a sheduled job using sql developer scheduler GUI

You can not issue DDL commands (including ALTER) directly in PL/SQL. Use execute immediate instead.

execute immediate 'Alter Trigger  TEST.LIVE_DELETE Disable';

Replace DBMS_OUTPUT.PUT_LINE calls with logging to a table, as the output of the former will not appear anywhere in case of a scheduled job.


patindex can do want you expect:

select a.dummy_Id, b.dummy_Id
from testmain a
join testmain b on left(a.memberNo,patindex('%/%', a.memberNo)-1) = left(b.memberNo,patindex('%/%', b.memberNo)-1)
                    and b.memberNo not like '%/1'
                    and a.dummy_Id <> b.dummy_Id
and a.memberNo like '%/1'


Broken tempdb location and can't recover

Start SQL Server as an application, not a service, in minimal config mode, and only recovering master, from a command prompt:

[path to this instance]\BINN\sqlservr.exe -c -f -T3608

(Add -s InstanceName if this is a named instance.)

Now, in a different command prompt, connect using SQLCMD:

sqlcmd -S InstanceName -E

And issue a correction (triple-check this!!!), then shut the application down:

> ALTER DATABASE tempdb MODIFY FILE ...
> SHUTDOWN WITH NOWAIT;
> GO


Restore database from script created by the "Generate and Publish Scripts" wizard

You simply need to create a full database backup and restore that to your new server.

How to backup a database using SSMS

How to restore a database using SSMS


Validate id sequence consistency in records in Redshift

Let's imagine you have this setup:

INSERT INTO events
    (created_at, id)
VALUES
    ('2017-06-30 09:20:47 UTC', 100),
    -- ('2017-06-30 09:18:31 UTC', 101), -- Missing row
    ('2017-06-30 09:16:19 UTC', 102),
    ('2017-06-30 09:12:08 UTC', 103),
    ('2017-06-30 09:11:59 UTC', 104),
    ('2017-06-30 09:11:15 UTC', 105),
    ('2017-06-30 07:03:41 UTC', 106) ;

and ...

INSERT INTO deactivated_events
    (created_at, id)
VALUES
    ('2017-06-30 07:03:41 UTC', 97),
    ('2017-06-30 09:11:15 UTC', 98),
    ('2017-06-30 09:11:15 UTC', 99),
    ('2017-06-30 09:18:31 UTC', 100)     -- Repeated row
    ;

Provided that Redshift provides Window Functions, you can use a slight variation of your query to do two things:

Consider that there are repeated ids when you group by id and find the count to be > 1
Consider that there is a missing row (or more!) if the id of the previous row is not 1 + the current one. This is done by means of the LAG function.

This can be done with the following query

WITH merged_events AS
(
  SELECT
      id
  FROM
  (
      SELECT
          id
      FROM
          events
      UNION ALL  /* Must be UNION ALL, because we want to find repeated values */
      SELECT
          id
      FROM
          deactivated_events
  ) AS q0
  WHERE true -- In practice, created_at > GETDATE() - INTERVAL '3 hours'
)
SELECT
    id,
    count(id) > 1 AS repeated_event,
    ((lag(id) OVER(ORDER BY id)) /* previous id */ + 1) <> id AS previous_event_missing
FROM
    merged_events
GROUP BY
    id ;

This will produce:

 id | repeated_event | previous_event_missing
--: | :------------- | :---------------------
 97 | f              | null
 98 | f              | f
 99 | f              | f
100 | TRUE           | f
102 | f              | TRUE
103 | f              | f
104 | f              | f
105 | f              | f
106 | f              | f

You can check the whole setup (with PostgreSQL instead of RedShift) at
dbfiddle here

Caveats: your id might have gaps if it is actually from an IDENTITY column (or a SEQUENCE it Redshift happens to implement it, which I don't think is the case). So, you should first guarantee by some other means that your ids are originally consecutive...

References:

Sequence number generation function in AWS redshift which discusses different ways of getting sequences, and its tradeoffs.
How to find missing values in a sequence with SQL which shows different alternatives.


How do I get ER Studio Data Architect to automatically add SCD Fields to my Dimensions?

If you have already created these entities/tables, there is a sample macro that comes with ER/Studio that does just this.  You'll want to run this on a test model first as you may want to customize it to get the exact results you want.

The sample macro is named ADD BASE ATTRIBUTES TO PERSON ENTITY. You, of course, will want it to work on all or selected entities.   You could customize the macro to add these attributes based on the table being selected.

If you want this to happen for new entities, you can create an entity/table that contains these common attributes/columns, then use that as a template.

I have created a macro to create tables with all the properties I want to be common.  The advantage to doing this is that macros can be tied to a hotkeys, making the creation of the new table easy.

If you don't have the resources to mess with a macro, you could do the workaround of creating a new set of Domains (reusable attributes) in their own folder.  Then you can at least quickly grab all 4 or so and drag them on to the tables.  Still manual, but only once per table.

So the answer you are looking for is dependent upon what the status of your model is currently.


Why is user: system doing an hourly backup of my SQL databases?

Your System Admin probably has a program such as BackupExec which backs up everything organisation-wide.  Check with them.

Typically, you'll see the backup device listed as a unique identifier, if some other system is managing backups.

I occasionally use this query to check backup history details:

DECLARE @DBName SYSNAME;
SET @DBName = DB_NAME(); -- modify these as you desire.
SET @DBName = NULL; -- comment this line if you want to limit the history
--SET @DBName = '<some database name>';
;WITH src AS
(
SELECT DatabaseName = bs.database_name
    , BackupStartDate = bs.backup_start_date
    , CompressedBackupSizeMB = bs.compressed_backup_size / 1048576
    , ExpirationDate = bs.expiration_date
    , BackupSetName = bs.name
    , RecoveryModel = bs.recovery_model
    , ServerName = bs.server_name
    , BackupType = CASE bs.type
          WHEN 'D' THEN 'Database'
          WHEN 'L' THEN 'Log'
          ELSE '[unknown]' END
    , LogicalDeviceName = bmf.logical_device_name
    , PhysicalDeviceName = bmf.physical_device_name
    , rn = ROW_NUMBER() OVER (PARTITION BY bs.database_name
            ORDER BY bs.backup_start_date DESC)
FROM msdb.dbo.backupset bs
    INNER JOIN msdb.dbo.backupmediafamily bmf
        ON [bs].[media_set_id] = [bmf].[media_set_id]
WHERE (bs.database_name = @DBName
    OR @DBName IS NULL)
    AND bs.backup_finish_date >= DATEADD(DAY, -7, GETDATE())
)
SELECT src.DatabaseName
    , src.BackupStartDate
    , CompressedBackupSizeMB = CONVERT(INT, src.CompressedBackupSizeMB)
    , src.ExpirationDate
    , src.BackupSetName
    , src.RecoveryModel
    , src.ServerName
    , src.BackupType
    , src.LogicalDeviceName
    , src.PhysicalDeviceName
FROM src
WHERE src.rn = 1
     AND src.DatabaseName NOT IN (
        'master'
        , 'model'
        , 'msdb'
        , 'tempdb'
     );

That should return a single row for each database backed up over the last 7 days.  You can limit this to a single database by modifying the @DBName variable.


Is the front-end locked down (for design) and properly linked to the back-end?  If they are, you will need to go back to your development copy, save it on the end-users' machines individually, and re-link to the databases on the file server.  i have a VBA script that runs when the front-end starts up that checks for the existence of the backend.  if the backend is missing or has been moved, it prompts the user (or dba) for the new location.  I will provide you some more information later this evening if you are interested.  My front-end is packaged as a standalone application that resides on the end-users' computers. The backend resides on their server.  This is much safer and faster than having your users run the front-end from a networked location.

Edit:

I forgot to mention that, after linking, you should "lock-down" the front-end on the users' computers. You don't want them fiddling around with your form design.


Auto-increment key and Foreign Key as composite for relational database

You don't need composite keys to enforce your referential integrity in your case.  The reason is that you have a pretty straight-forward three tier hierarchy:

PATIENT
   +
   |
   ^
 TEST
   +
   |
   ^
SAMPLE

Your SAMPLE table just needs a simple foreign key to your TEST table and your TEST table just needs a simple foreign key to your PATIENT table.

This works because each sample record needs a test record and each test needs a patient.  If you delete a patient, their tests must be cascade deleted.  If you delete a test its samples must be cascade deleted.  Therefore you don't need patient_id in the TEST table.  All you need is to declare cascade deletion on your foreign keys, like so...

...
CONSTRAINT 'FK_TEST__PATIENT' FOREIGN KEY ('patient_id')
      REFERENCES 'PATIENT' ('patient_id') ON DELETE CASCADE
...
CONSTRAINT 'FK_SAMPLE__TEST' FOREIGN KEY ('test_id')
      REFERENCES 'TEST' ('test_id') ON DELETE CASCADE
...


CHECKDB fails on MSDB and Master system databases

There is an internal snapshot that SQL Server will try to create when you run certain DBCC commands.  There is really good information about this on the BOL reference for DBCC.

  When you execute one of these DBCC commands, the Database Engine
  creates a database snapshot and brings it to a transactionally
  consistent state. The DBCC command then runs the checks against this
  snapshot. After the DBCC command is completed, this snapshot is
  dropped.

  Sometimes an internal database snapshot is not required or cannot be
  created. When this occurs, the DBCC command executes against the
  actual database. If the database is online, the DBCC command uses
  table-locking to ensure the consistency of the objects that it is
  checking. This behavior is the same as if the WITH TABLOCK option were
  specified.

The master database is particularly interesting with this, as if the internal snapshot can't be created then DBCC CHECKDB will fail against master.  As per the above reference:

  DBCC CHECKDB fails when it is run against master if an internal database snapshot cannot be created.

So, what can cause the internal snapshot creation to fail?  The above reference has a nice list:

  An internal database snapshot is not created when a DBCC command is
  executed:

  Against master, and the instance of SQL Server is running in single-user mode.
  Against a database other than master, but the database has been put in single-user mode by using the ALTER DATABASE statement.
  Against a read-only database.
  Against a database that has been set in emergency mode by using the ALTER DATABASE statement.
  Against tempdb. In this case, a database snapshot cannot be created because of internal restrictions.
  Using the WITH TABLOCK option. In this case, DBCC honors the request by not creating a database snapshot.

  The DBCC commands use table locks instead of the internal database
  snapshots when the command is executed against the following:

  A read-only filegroup
  An FAT file system
  A volume that does not support 'named streams'
  A volume that does not support 'alternate streams'

My recommendation is to look at that information and find which one is applying to your situation.  That should answer your question and move towards the resolution.


Disabled SQL Server Endpoint - now I cannot login

STATE = STOPPED on the [TSQL Default TCP] endpoint prevents the SQL Server service from listening for incoming connections via TCP.  You have several options available that will allow you to reverse this.

Connect with the DAC dedicated admin connection.  You can connect to the DAC by specifying the name of the server as: ADMIN:MyServerName.  Once connected to the DAC, you should be able to start the endpoint again with the following statement:

ALTER ENDPOINT[TSQL Default TCP] state = started;

Use named pipes to connect to SQL server. Use SQL Server
Configuration Manager to enable named pipes if disabled.


How to ensure each user only answers a question once on the db side?

A unique constraint on Answers (questionid, providerid) was needed, as suggested by Balazs Papp.

As explicitly pointed out by Andriy M this should be a composite key.

I had to look up composite keys, as I was unfamiliar. Thanks so much to everyone for helping and responding so quickly.