How do you block ad-hoc distributed queries from specific sql server instances? For one reason or another, a particular development SQL Server is unable to openrowset or opendatasource to production servers, but can hit other dev servers. I've tried sqloledb and sqlncli providers.

I'm not sure if this is a database or network configuration.

If it is a database configuration, is it set on the dev server, or ALL of the prod servers?

Is it a firewall setting?

It's weird. I have full access from the Sql Server instance running from my PC, but not the one on the dev server. I'm using SSMS 2008 in both cases. The dev server is 2005, and the remote access is to other 2005 and 2000 servers.

I ask the question as "how to block queries" because I'm looking to know what settings/configuration I need to disable to allow it again. Thanks!

The error message returned is as follows:

OLE DB provider "SQLNCLI" for linked server "(null)" returned message
"Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "(null)" returned message
"An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote connections.".
Msg 5, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [5].

Edit: Not sure if folks who answered are clear on what I am looking for. Ad hoc Queries are enabled for all boxes, but from this particular development box, querying production boxes results in the error message above. How did they block things so specifically?

It's a simple setting for sp_configure (that should work for all newer versions of SQL Server - 2005, 2008, 2008 R2):

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

This will enable the use of OPENROWSET and OPENDATASOURCE.


Single user mode when was configured I'm trying to find when a database on our instance was configured as SINGLE USER MODE but can't find that information. Either using tsql or the GUI. Maybe it is not possible to know that information? Are these actions logged somewhere? Or do we need to force this logs?

Maybe it is not possible to know that information?

Exactly. It is assumed to be non-critical. You may (should) be lucky with the sql server logs but then those rorate and it is quite likely to no there - anymore (unless it happened very recently).

Check the SQL Server logs , imho your best chance.


Curious About SQL Server Registry Entries I'm working on building some documentation across my systems and planned on simply using Powershell to script out the legwork of what I wanted to do. While that's going all well and good, I ran into an issue with my SQL Server registry entries. The problem being that some of my SQL servers have all of their registry values while some don't.

For example, I'm pulling down the SQL Shared Features entry so I know where that directory is on all servers. Now some of them are pulling data back (E:\yada\yada) while others aren't pulling anything back. After further investigation, I have noticed that several of my SQL servers don't have the required registration data saved within the registry. This happens with several different registry entries that should be there.

Any reason it's like this?

Instead of reinventing the wheel (there is a cost associated to it), use SQL PowerDoc written by  kendalvandyke. Its a powershell based solution.

Documentation can be found here.

  I have noticed that several of my SQL servers don't have the required registration data saved within the registry.

SQL server will have everything in the registry settings. For shared Features, I believe the registry is HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-518\Components\0D1F366D0FE0E404F8C15EE4F1C15094

Refer to File Locations for Default and Named Instances of SQL Server


Full Text Catalog not populating I just created a full text catalog :

Enable full text (right click on db => file => enable ful text indexing)
Create catalog (Storage => Fulle text Catalog => New Catalog => "FTCatalog" => OK)
Add column to catalog (right click on catalog => properties => table/views add table/columns , automatic => ok)

The table has 270k lines, I created the catalog this morning and still it has 0 Item Count and the populate status is 1.
The logs in [...]\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLFT0000800007.LOG doesn't show anything more than (i tried to recreate it 10 min ago)

2014-06-30 16:32:03.93 spid23s     Informational: Full-text Full
population initialized for table or indexed view '[XXX].[dbo].[XXX]'
(table or indexed view ID '1771153355', database ID '8'). Population
sub-tasks: 4.

All SPs are installed.
I tried with some smaller table, it's the same.
The service "SQL Server FullText Search" is running.
Nothing on the EventViewer.
Any idea ?
UPDATE
In Sql Server Log I have these two thing :

Changing the status to PAUSE for full-text catalog "XXX" (7) in database "XXX" (8). This is an informational message only. No user action is required.
Changing the status to PAUSE for full-text catalog "XXX" (7) in database "XXX" (8). This is an informational message only. No user action is required.

You might try scripting it out to see if you get a different behaviour.  This simple example runs in under a minute on my local SQL 2005 instance:

USE master
GO

SET NOCOUNT ON
GO

IF EXISTS ( SELECT * FROM sys.databases WHERE name = 'fullTextDemo3' )
BEGIN
    ALTER DATABASE fullTextDemo3 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE fullTextDemo3
END
GO

IF NOT EXISTS ( SELECT * FROM sys.databases WHERE name = 'fullTextDemo3' )
CREATE DATABASE [fullTextDemo3]
GO

ALTER DATABASE fullTextDemo3 SET RECOVERY SIMPLE
GO

USE fullTextDemo3
GO

CREATE TABLE dbo.nearnessTest (
    rowId           INT IDENTITY,
    keywords        VARCHAR(MAX) NOT NULL,
    notes           VARCHAR(100) NOT NULL,

    CONSTRAINT PK_nearnessTest PRIMARY KEY ( rowId )
)
GO

INSERT dbo.nearnessTest
--SELECT TOP 270000 m1.[text], ''
SELECT m1.[text], ''
FROM sys.messages m1
--  CROSS JOIN sys.messages m2
WHERE m1.language_id = 1033
  --AND m2.language_id = 1033
GO

-- Create the catalog
IF NOT EXISTS ( SELECT * FROM sys.fulltext_catalogs WHERE name = N'ftc_List3' )
CREATE FULLTEXT CATALOG ftc_List3
GO

-- Create the full-text index
CREATE FULLTEXT INDEX ON dbo.nearnessTest (keywords) KEY INDEX PK_nearnessTest ON ftc_List3 WITH CHANGE_TRACKING MANUAL -- CHANGE_TRACKING OFF, NO POPULATION
GO

SELECT 'before' ft, * FROM sys.fulltext_indexes
GO

DECLARE @i INT
SET @i = 0

WHILE EXISTS ( SELECT * FROM sys.fulltext_indexes WHERE has_crawl_completed = 0 )
BEGIN

        SELECT outstanding_batch_count, *
        FROM sys.dm_fts_index_population
        WHERE database_id = DB_ID()

        --SELECT *
        --FROM sys.dm_fts_outstanding_batches
        --WHERE database_id = DB_ID()

    WAITFOR DELAY '00:00:05'

    SET @i = @i + 1
    IF @i > 60 BEGIN RAISERROR( 'Too many loops!', 16, 1 ) BREAK END

END

SELECT 'after' ft, * FROM sys.fulltext_indexes
GO

SELECT *
FROM dbo.nearnessTest
WHERE CONTAINS( keywords, 'error' )

What happens when you issue an explicit start for the index population, eg

ALTER FULLTEXT INDEX ON dbo.yourTable START FULL POPULATION;

Run these summary functions

SELECT FULLTEXTSERVICEPROPERTY ( 'IsFulltextInstalled' ) IsFulltextInstalled
SELECT DATABASEPROPERTY('fullTextDemo3','IsFulltextEnabled') IsFulltextEnabled
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'AccentSensitivity ') AS AccentSensitivity
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'IndexSize ') AS IndexSize
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'ItemCount ') AS ItemCount
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'LogSize ') AS LogSize
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'MergeStatus ') AS MergeStatus
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'PopulateCompletionAge ') AS PopulateCompletionAge
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'PopulateStatus ') AS PopulateStatus
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'UniqueKeyCount ') AS UniqueKeyCount
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'ImportStatus ') AS ImportStatus


SQL Server IDENTITY column automatic re-seed I work with huge datasets. Many of the transactions that take place in my database are enormous - trillions of rows, and more.

Some of the tables use IDENTITY columns, not for unique IDs, just because it's simple and fast, and to provide a concurrent solution to providing an incrementing number. However, when the IDENTITY column reaches its limit, I want it to automatically reseed immediately within the statement when it reaches its limit.

I appreciate this is odd behaviour for most, but it would make sense to at least have this functionality as an option, surely? You can't even do a reseed within a transaction, and I cannot use truncate (don't want to delete).

Why is this not possible? Has anyone else come across this as a problem before?

Here's the functionality: In SQL Server I have a table that acts as a sequential number generator, like a Sequence in Oracle. The maximum we want the number to be is 999999, after that, reset to 0. This number is added on to some other fields (one of them a datestamp) to generate reference numbers.

The system is highly concurrent and I need it to be fairly obvious when the reference number was generated. As it stands, there is a task that runs every day to reseed the IDENTITY column, but due to the large number of records daily, if there are > 999999 records processed, I get an error.

You can use the bigint data type in order to avoid reaching the max value too often.

The range for bigint is -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807), storage = 8 Bytes. Are you often out of this limit?

In case you want to reseed, you should be able to run the dbcc checkident statement inside a transaction, but you'll have to do more error handling inside your code.

I don't know of an IDENTITY property to automatically reseed its value, only manually, by truncating the table or by using dbcc checkident. You could create a trigger on your table and once the last inserted value gets close to the maximum then you'd be able to reseed. But this will probably add cost to your transactions.


Is there a way to get the max,count, or etc for all columns without naming them individually? Normally if you want to find something across all columns you have to

select max(col1),max(col2),max(col3)...max(col99) from blah;

Is there a way to do this without naming every column manually?

https://mariadb.com/kb/en/library/procedure-analyse/  (Also works in MySQL.)

SELECT * FROM blah PROCEDURE ANALYSE();

For example, using a table of Canadian cities; notice the min and max values:

mysql> SELECT * FROM canada PROCEDURE ANALYSE()\G
*************************** 1. row ***************************
             Field_name: world.canada.id
              Min_value: 291724
              Max_value: 297207
             Min_length: 6
             Max_length: 6
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 294465.5000
                    Std: 1583.0944
      Optimal_fieldtype: MEDIUMINT(6) UNSIGNED NOT NULL
*************************** 2. row ***************************
             Field_name: world.canada.country
              Min_value: ca
              Max_value: ca
             Min_length: 2
             Max_length: 2
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 2.0000
                    Std: NULL
      Optimal_fieldtype: ENUM('ca') NOT NULL
*************************** 3. row ***************************
             Field_name: world.canada.ascii_city
              Min_value:
              Max_value: zehner
             Min_length: 0
             Max_length: 42
       Empties_or_zeros: 1
                  Nulls: 0
Avg_value_or_avg_length: 10.0376
                    Std: NULL
      Optimal_fieldtype: VARCHAR(42) NOT NULL
*************************** 4. row ***************************
             Field_name: world.canada.city
              Min_value: (dup population)
              Max_value: Zehner
             Min_length: 1
             Max_length: 31
       Empties_or_zeros: 3
                  Nulls: 0
Avg_value_or_avg_length: 9.7861
                    Std: NULL
      Optimal_fieldtype: TINYTEXT NOT NULL
*************************** 5. row ***************************
             Field_name: world.canada.state
              Min_value:
              Max_value: 14
             Min_length: 0
             Max_length: 2
       Empties_or_zeros: 1
                  Nulls: 0
Avg_value_or_avg_length: 1.9996
                    Std: NULL
      Optimal_fieldtype: ENUM('','01','02','03','04','05','07','08','09','10','11','12','13','14') NOT NULL
*************************** 6. row ***************************
             Field_name: world.canada.population
              Min_value: 540
              Max_value: 4612187
             Min_length: 3
             Max_length: 7
       Empties_or_zeros: 4771
                  Nulls: 0
Avg_value_or_avg_length: 4300.6415
                    Std: 85242.3200
      Optimal_fieldtype: MEDIUMINT(7) UNSIGNED NOT NULL
*************************** 7. row ***************************
             Field_name: world.canada.lat
              Min_value: 0
              Max_value: 82.4832992553711
             Min_length: 1
             Max_length: 7
       Empties_or_zeros: 1
                  Nulls: 0
Avg_value_or_avg_length: 50.09833726667297
                    Std: 5.212114845361912
      Optimal_fieldtype: FLOAT NOT NULL
*************************** 8. row ***************************
             Field_name: world.canada.lng
              Min_value: -140.98300170898438
              Max_value: 0
             Min_length: 1
             Max_length: 8
       Empties_or_zeros: 1
                  Nulls: 0
Avg_value_or_avg_length: -89.74846842687205
                    Std: 23.8526411452119
      Optimal_fieldtype: FLOAT NOT NULL
*************************** 9. row ***************************
             Field_name: world.canada.stripe
              Min_value: 0
              Max_value: 1190
             Min_length: 1
             Max_length: 4
       Empties_or_zeros: 1
                  Nulls: 0
Avg_value_or_avg_length: 966.6869
                    Std: 37.9899
      Optimal_fieldtype: SMALLINT(4) UNSIGNED NOT NULL
*************************** 10. row ***************************
             Field_name: world.canada.province
              Min_value: Alberta
              Max_value: Yukon
             Min_length: 5
             Max_length: 25
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 10.9220
                    Std: NULL
      Optimal_fieldtype: ENUM('Alberta','British Columbia','Manitoba','New Brunswick','Newfoundland and Labrador','Northwest Territories','Nova Scotia','Nunavut','Ontario','Prince Edward Island','Quebec','Saskatchewan','Yukon') NOT NULL
10 rows in set (0.04 sec)


SQL 2005 Replication - How do I get the DA Profile MaxBCP option to work with a Transactional Publication? I have two publications on a database.  One is a transactional publication, the other is a snapshot publication.  We have a stand-alone Distributor (separate from the publisher) and both publications have push subscriptions (to the same database on the Subscribers).  There are two subscribers (our client is transitioning from SQL Server 2005 to SQL Server 2008 R2, so we're pushing the data to both machines currently).  The Publisher and Distributor are both running SQL Server 2005.

I'm using the same Distribution Agent Profile for both publications.  When the snapshot is applied at the subscriber, the snapshot publication will honor the MaxBCP setting and push multiple BCP files simultaneously.  Unfortunately, the Transactional publication (which is configured to use the same profile for the Distribution Agent) will only push the BCP files serially.

We have a pretty fast link between our site and the subscriber (on the other side of the country), but the single streams only utilize about 2-2.5 Mbit.  Utilizing multiple BCP would allow us to take full advantage of the bandwidth.  We tested setting MaxBCP to 16 with the snapshot publication and that actually allowed us to burst up to 35 Mbit with an average rate of about 29Mbit.

We're planning on migrating the database to SQL Server 2008 R2 on our side as well and we'd like to take full advantage of the available bandwidth when we reinitialize it on SQL 2008 R2 as the DB is very large (~250 GB).

I'd like to thank Hilary Cotter for providing the answer to my dilemma.  His contribution to the SQL Server community is greatly appreciated!

To address the issue we needed to recreate the publication and specify the sp_addpublication argument @sync_method = 'native'.

According to SQL Server 2005, 2008 and 2008 R2 Books Online, the default @sync_method is character for snapshot publications (non-concurrent character mode BCP output) and concurrent_c (concurrent character mode BCP output) for all other publication types. However, in my experience, the actual defaults are native (non-concurrent native mode BCP output) for snapshot publications and concurrent (concurrent native mode BCP output) for all other publication types.  This is actually a good thing as the native SQL BCP snapshot format is faster/more efficient to deliver to the subscriber. The character formats are only necessary for publications with non-SQL Server publishers.

Also from SQL Server Books Online: When applying a snapshot that was generated at the Publisher using the concurrent snapshot option, one thread is used, regardless of the number you specify for MaxBcpThreads.

The default in SQL Server 2000 was native and according to SQL Server 2012 Books Online, the default has been changed back to native for all SQL Server publications.

Now that I've recreated the publication using the argument @sync_method = 'native', I can now push multiple BCP files simultaneously to the subscriber, thus taking full advantage of the large pipe between our site and our customer's site.

I just finished blogging on this topic (with more detail) here: http://mattslocumsql.blogspot.com/2012/06/distribution-agent-for-transactional.html

Hilary Cotter's swift response was invaluable to solving my issue.  Thank you again, sir!


shopping cart database schema I need help creating the schema for a database to hold information relative to a web user's shopping cart/session. Each session will have a session ID, a time stamp holding time of creation, an expiry time stamp, a boolean indicating whether or not the purchase was completed, and then a list of products, and their associated quantities and prices. Every user will have only one ID, origination time stamp, expiry time stamp, and purchase boolean, but they may reference one or many products with varying quantities and prices.

My initial thought was to have a session table and a product table, but I can't seem to get past thinking the session ID and the product information will become redundant in big orders and across different sessions. In short, if different rows of the product table consistently reference repeat product numbers and session IDs, is that going to get messy?

Am I on the right track? Or can you propose an alternative design pattern that I have yet to consider?

Please let me know if you need more information. Thank you in advance!

From your description, you'll need the following tables ("..." means you might need additional fields in that entity):

User (UserID, EmailAddress, Password ...)
Session (SessionID, CreatedDate, UserID, ExpiryDate, PurchaseCompleteInd ...)
Product (ProductID, Name, UnitPrice ...)
SessionProductLink (ProductID, SessionID, Quantity ...)

The SessionProductLink is a Junction table, which is the 'bit of the design' you were missing.

The field names in italics are foreign keys (it should be obvious which fields they link to ...). the ID fields are the clustered key fields in the first three entities, for SessionProductLink you'd want a compound key of (ProductID, SessionID). (You can use a surrogate key there if you want, that's a style decision you can make)

Other things you might want to think about:

Security. You want to store your user passwords securely encrypted (i.e. use bcrypt), if you support credit cards you legally are not allowed to store the full credit card details in most cases, etc.
Invoicing - which payment methods are you going to support? Do you want people to be able to pay for multiple 'sessions' with one invoice? (This is more common for business accounts, typically retail customers are one purchase = one invoice = one payment method) Do you want to accept foreign currencies? (Between this point and the first one, you'll almost need a payment processor to make sure you don't land yourself in massive strife ...)
What happens if a product price changes? You don't want your past purchases/invoice prices to change, right?
You'll need to store your sessions (or the completed ones at least) forever (depending on your country's legal requirements for document storage for commercial transactions), since they're a legal record. Similarly, you can't just delete old Product records, you need to mark them obsolete so they don't show up as available for purchasing but do show up in historical purchase records (otherwise your app will show for old items that someone purchased 5 of product ID 119 and 2 of product ID 135 and you don't know what the hell they actually bought...).

That's all I can think of right now, but that should be enough to get you started.


upgrading Oracle forms and reports 6i to 12c We are planning a migration of an Oracle Forms and report 6i application to 12c, and at the same time we will upgrade to the latest version of Oracle (from 10g to 12c).

I would like to know the detailed steps that we can follow, or from where to start?
Is there anything that we must consider before doing this?
Any problems that we might encounter?
How much time will it take?
Taking in consideration that we have Over 900 .fmb/fmx files and Over 700 .rep/rdf files.

Any information whatsoever would be helpful !

I would recommend consulting with a consultant/company that specialises in migrating Oracle Forms and Reports. Why? Because we recently performed a similar migration and after 2 months we were not finished.

Oracle has changed some things internally and features that previously worked can cease to function with the new version.

Oracle has a special site dedicated to this task:

Fusion Middleware Upgrading Oracle Forms 6i to Oracle Forms 12c

I know this is not a complete answer, but so much depends on the complexity of your current environment, that it would be very hard to give you an estimation.


Reporting Services - Host header and Windows Authentication SQL 2016 - Enterprise Edition
Windows 2016
Reporting Services runs on domain account (SPN registered for new host header)

Problem:
When adding a host header in Reporting Services Configuration tool, like reports.server.net,  users need to login to the reporting services.
When I remove the host header and try to connect to hostname/reports I do not get prompted for user/password.

Fact:
 - When i use the default site address http://servername/Reports, before adding a new host header, the site opens up without login prompt

What I've done:

Created a Host record in DNS for the new address, pointing at reporting services server
Registered a SPN for the new host record to the service account for reporting service
Added the new host header to reporting services for web service url and web portal url
Played around authentication types in the reportserver.config file according to (https://docs.microsoft.com/en-us/sql/reporting-services/security/configure-windows-authentication-on-the-report-server?view=sql-server-ver15&viewFallbackFrom=sql-server-ver13)
Enabled delegation for the reporting services service account "just in case"

I can't seem to add a host header without beening forced to login, despite above.

Anyone been here?

Solved issue by adding new host header address to local intranet site in the browser.


Recommendations for PostgreSQL training I'm looking for recommendations for PostgreSQL training (on-site, off-site, remote) that covers administration, performance optimization, and clustering solutions like Slony and pgpool.  Finding courses via web searches hasn't gotten me very far as it seems many of the linked pages are out of date, and where they are updated, I'm not sure how to judge the quality of the course they offer.  Does anyone have recommendations for courses they took, had colleagues take, or themselves teach?

I think the best to start with is http://www.postgresql.org/docs/manuals/

These are really well written and cover everything you will need to know that is PostgreSQL specific.


LEFT OUTER JOIN versus WHERE IS NULL For some irrelevant reasons it's hard for me to get LEFT OUTER JOINS working with multiple ON clauses. Since simply moving these clauses to the WHERE statement results in different result sets, I add an additional OR IS NULL check.

LEFT OUTER JOIN ON query

SELECT t1.id, t2.ShardKey
FROM Table1 t1
LEFT OUTER JOIN Table2 t2 on t1.table2 = t2.id and t1.ShardKey = t2.ShardKey
WHERE t1.id = @id and t1.ShardKey = @shardkey

LEFT OUTER JOIN WHERE OR IS NULL query

SELECT t1.id, t2.ShardKey
FROM Table1 t1
LEFT OUTER JOIN Table2 t2 on t1.table2 = t2.id
WHERE t1.id = @id and t1.ShardKey = @shardkey
AND (t1.ShardKey = t2.ShardKey OR t2.ShardKey IS NULL)

Simplified definition for both tables:

CREATE TABLE [dbo].[Table1](
    [Id] [int] NOT NULL,
    [ShardKey] [int] NOT NULL,
    [Table2] [int] NULL,
PRIMARY KEY CLUSTERED
(
    [Id] ASC,
    [ShardKey] ASC
))

CREATE TABLE [dbo].[Table2](
    [Id] [int] NOT NULL,
    [ShardKey] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
    [Id] ASC,
    [ShardKey] ASC
))

These queries seem to give exactly the same query plan in SQL Server Management Studio, but I wonder if there're situations where the one outperforms the other.

The LEFT OUTER JOIN ON version is the standard way to write this logic, so I would highly recommend using this version rather than fiddling around with OR for performance reasons.

However, as ypercube has helpfully pointed out, the queries are not quite equivalent if the data and sharding methodology means that a Table1.Id value can end up in a different ShardKey than the ShardKey of the corresponding Table1.table2 value. If that happens, then the OR t2.ShardKey IS NULL portion of the 2nd query will be triggered and retain a row with matching Ids even if the ShardKey does not match.

In general, I suspect that they will typically result in similar query plans.  Given two equivalent queries (which yours may or may not be, depending on the sharding methodology), by formulating the query in the standard way you are more likely to be working with the query optimizer rather than forcing it to recognize that the two queries can be expressed in similar ways and apply the appropriate simplifications.

If you want to be even more explicit about applying @shardkey to each table in the join, you can do that directly in the LEFT OUTER JOIN. However, SQL Server will be able to easily recognize that your original query is equivalent to this one.

SELECT t1.id, t2.ShardKey
FROM Table1 t1
LEFT OUTER JOIN Table2 t2 on t1.table2 = t2.id and t2.ShardKey = @shardkey
WHERE t1.id = @id and t1.ShardKey = @shardkey

An example of different query plans

On my SQL 2014 instance, the queries have (slightly) different query plans and the query plan for the LEFT OUTER JOIN ON version (top) is superior to the ORM-generated LEFT OUTER JOIN WHERE OR IS NULL version (bottom).

In particular, the seek into Table2 should be a seek on both id and ShardKey, but in the LEFT OUTER JOIN WHERE OR IS NULL version it is a seek only on id and a subsequent Filter operator tests all matching rows in order to apply the appropriate ShardKey restriction.

This difference may or may not be meaningful in your specific case (especially if Id is unique without adding in the ShardKey). However, if your real version actually uses table partitioning by ShardKey (and this just isn't represented in the simple sample query), you will likely be losing the benefit of partition elimination for the seek into Table2.


Will one SQL instance work whilst the other 2 are patched? My change request to patch 3 SQL instances on the same server was denied because of one instance - they say a server reboot is fine, but not a few hours downtime.

Seeing as each instance is standalone, should it not be fine to patch 2 instances and leave the third?

I see there is a 'shared components' feature - what is this exactly, is this down for all instances during the patching, and does it matter?

How can I assuage the fears of my colleagues that the third instance will still be working - the business are not willing for it to go down even for a couple of hours in the evening.

Firstly, it always better to test patches in DEV environment before applying on Production.

To answer, there won't be downtime/interruption for Instance 3 while patching/removing path on Instance 1, all you need select particular instance during the wizard or command line as follows:

KBXXXX.exe /qs /IAcceptSQLServerLicenseTerms /Action=Patch /InstanceName=Intance1

There might be restart required (manual restart) to proceed with Instance 2 patching after completing instance 1.

Shared features are updated (patched) once. In your case during the Instance 1 or which ever starts first, and not applied while patching other instances on the same server. We never experienced any interruption with shared features (considered following): But if you got Integration Services and it's heavily used you must find maintenance windows where it's got less overhead.

Client Tools Connectivity
Client Tools Backward Compatibility

My environment was 3 instances on 1 server, all of them are AG replicas. One primary other two secondary, and there are 3 servers total with same configuration.


transaction log backup format sector size I am trying to backup a log file in SQL Server but I obtain an error message while yesterday I did it successfully.
The only difference between yesterday and now is that I just truncate the principal table in the database.
The error message in french I get is the following:

USE master BACKUP LOG MQ  TO DISK = 'D:\MSSQL\BackupToSendToFtp\MQLog.bak'

  Impossible d'utiliser le fichier de sauvegarde
  'D:\MSSQL\BackupToSendToFtp\MQLog.bak' car il a été formaté à
  l'origine avec une taille de secteur de 512 et se trouve maintenant
  sur une unité dont la taille de secteur est 4096.

Which in English should be:

  Cannot use the backup file 'D:\MSSQL\BackupToSendToFtp\MQLog.bak'
  because it was originally formatted with sector size 522 and is now on
  a device with sector size 4096.

A few days ago I upgraded the Azure VM to get a more powerful one. Can this be the main cause of the backup failure?

If yes then why did the backup succeed yesterday?

Correct. You can't restore backups to storage with a different sector size (this is something to do with the way the log is structured internally where it would be unsafe to do so - there's a big article on it which is as clear as mud).

Are you using Storage Spaces?

  The problem with all of this is that the when creating a virtual disk
  with Storage Spaces, if you do not specify a LogicalSectorSize via the
  Powershell cmdlet, the system will create a virtual disk with a
  LogicalSectorSize equal to the greatest PhysicalSectorSize of any disk
  in the pool. This means if you have SSD’s in your pool and you created
  the virtual disk using the GUI, your virtual disk will have a 4k
  LogicalSectorSize.

If so then you're out of luck, you can reformat the Azure disks as described to use 512 byte sectors but you'll then get a massive and permanent performance hit.

There's no way to backup/restore that database onto the new storage. You'd have to export the database schema and data from your old storage and import it to a new database on the new storage. Crazy, I know.


What's stored in spatial_ref_sys? Should I copy data in this table when migrating to the other server?

No, it comes with PostGIS as a standardized catalog of the spatial reference systems your database can work with. It will be recreated when you CREATE EXTENSION postgis;. In fact, copying the table may cause the installation of PostGIS to fail. PostGIS is supposed to provide that table and the schema. You can see the table documented here.

That said, if you have a custom SRS installed such as the ones provided by http://spatialreference.org you will have to copy those rows over, from the docs,

  Various US state plane spatial reference systems (meter or feet based) - usually one or 2 exists per US state. Most of the meter ones are in the core set, but many of the feet based ones or ESRI created ones you will need to pull from spatialreference.org.


Unexpected behavior of the “Money” field in PostgreSQL I started developing a web sistem using Linux Ubuntu and at some point I had to do the following with the data type "money":

explode(" ", "R$ 3,000.00"); // [0] => "R$" and [1] => "3,000.00"

However when I installed the software in Windows I realized that the data is saved without space, that is, "R$3,000.00". Soon, the code snippet fails to function properly.

Note: 1 could "fix" this using:

preg_replace("/[R$]+/", "$0 $1", "R$3,000.00"); // "R$ 3,000.00"

But certainly not a better way.

Note 2: The version of PostgreSQL used is 9.5

Would anyone have any suggestions for resolving this?

Thank you very much.

Quoting the documentation on monetary types on PostgreSQL:

  Since the output of this data type is locale-sensitive, it might not work to load money data into a database that has a different setting of lc_monetary. To avoid problems, before restoring a dump into a new database make sure lc_monetary has the same or equivalent value as in the database that was dumped.

(emphasis mine)

The interpretation of the local for lc_monetary is system-dependent, and doesn't work the same way in Ubuntu as in Windows.

Check Best data type to store money values in MySQL, a good part of the answers are not MySQL specific, and they will give you a good understanding.

The natural tendency is to not deal with money types within databases, but use numeric types instead (and normally, with 4 decimals during calculations, not just 2), and handle the formatting at the app level. I should add also that, having a money type that doesn't store a currency doesn't make much sense to me.

With regard to locale information:

In Windows, you can actually set with very much detail how currency should be shown. The following picture shows the Windows 10 settings of my box:

They're quite different that those of my Ubuntu 16 box:

And slightly different from the Mac that hosts them all:


Backup and Restore SQL server files and filegroups I have a database called Admin. It has only one default filegroup called primary.

File group: primary
files in the file group: Admin_data.mdf

Now I have taken a full filegroup backup.

Backup file name: fg_full.bak

Then created a new ndf in the same file group called sec_file.ndf and created few tables, inserted 1000 rows on each table.

After an hour took a file backup of my MDF and NDF files

MDF backup: Admin_Data.bak
NDF backup: sec_file_ndf.bak

File Backup using SSMS

Here somehow my sec_file get deleted so I want to restore latest NDF backup, how can I restore it?

Output of restore headeronly

Restore filelistonly from disk = 'D:\sec_file_ndf.bak'

Query the backup file with

RESTORE HEADERONLY FROM DISK = 'D:\sec_file_ndf.bak'

and

RESTORE FILELISTONLY FROM DISK= 'D:\sec_file_ndf.bak'

...to verify the contents of your D:\sec_file_ndf.bak file.

You might have multiple backups in one *.BAK file. If so add the option FILE = 2 to your RESTORE command to restore your most recent backup of the *.NDF file.


Best way to generate a report on overall SQL Server performance to show management? I support a transactional SQL Server as a dba and have recently been told by a management consultant that they need a new replication server set for reporting only, claiming poor performance on the main server due to too many processes running at once. When I open activity monitor at any given time, I see no more than 5% processor time, 1 mb/sec, etc. I have worked on performance issues with them before but it always comes down to code errors, inefficient queries, or missing indexes (for example there is one index that according to the missing index dmv has an avg_estimated_impact of 33 minutes).

I'm fighting it because it will cost them a lot of money in our org and also I don't believe it will help them with their performance issues that much/at all.

Does anyone have any nice queries or methods they can share to help prove server performance is fine in terms of computing resources? I can screenshot activity monitor but I'd like to provide management with something more concrete (perhaps over 24 hours) that it is not a lack of computing resources that is part of the perceived slowness.

I don't think this is necessarily a good fit for the SE Q&A model, but I sympathize with datadawg2000's pain so will add some words that may help.

  ...claiming poor performance on the main server due to too many processes running at once. When I open activity monitor at any given time, I see no more than 5% processor time, 1 mb/sec, etc.

Let's be honest - that isn't the right away to get a snapshot of current server load nor is a screenshot of activity monitor going to be convincing to anyone. And besides, you really shouldn't be relying on Activity Monitor for...anything, anyways.

You didn't specify your SQL Server version, but at your disposal to see performance you have SSMS performance reports, perfmon, any number of third party applications, open source tools like Brent's First Responder Kit and sp_WhoIsActive, and a ton more with a little Googling (or Binging if you really love Microsoft).

Those can help you see if you really have a problem, and where it is. Being able to proactively come to management when there is a problem and/or retroactively assess the root cause will be a huge start.

  I have worked on performance issues with them before but it always
  comes down to code errors, inefficient queries, or missing indexes
  (for example there is one index that according to the missing index
  dmv has an avg_estimated_impact of 33 minutes).

Great! So you have a proven track record of fixing issues. Use that to make sure that you or someone who knows what they're doing is reviewing code, queries, etc. before they get pushed to production. 99% of the time these issues can be stamped out in a lower environment or through proper testing.

  I'm fighting it because it will cost them a lot of money in our org and also I don't believe it will help them with their performance issues that much/at all.

I don't know your setup, but  this will play out in one of two ways assuming you're correct:

They add a new reporting instance, nothing gets better. You can say "I told you so" and proceed to get them to fix their bad code / indexing / testing process.
They listen to you and fix their bad code / indexing / testing process.

Either way, you can win here. Unless the cost of the new server is coming out of your paycheck, make your advisement and then relax. You probably don't have the power to make the final decision, so don't lose sleep over it.

  Does anyone have any nice queries or methods they can share to help
  prove server performance is fine in terms of computing resources? I
  can screenshot activity monitor but I'd like to provide management
  with something more concrete (perhaps over 24 hours) that it is not a
  lack of computing resources that is part of the perceived slowness.

See the list of tools above for this. Painting a long term picture is key here, which third party tools and/or pretty graphs will probably help with. Remember that no one cares if you have good performance for 23 hours out of the day if the Big Boss's report runs slow during the literal 11th hour. Clearly management has valid concerns things are not fine and you've said the same more or less - so don't fight them on that point.

You are disagreeing on what the root cause of the problems are and that is where you need to focus your efforts. Spend some time to agree on metrics that both you and management find to be important and start reporting on those. You can highlight you're meeting RTO and RPO SLAs, have good CPU usage, etc. but also remember they probably care about their reporting SLAs just as much (lets be honest, way more) than the boring technical details. Then, as long as you can show positive trends in making those metrics healthier, it should be easy to get them on your side of reasoning.

Let me also add that I have these and more tools/resources compiled on my site  here if you'd like to explore more free and community driven software/projects.


Can I edit queued MySQL binary logs for replication? I have a simple Master-Slave replication set up, but Slave keeps falling behind do to what appears to be a simple checkout system. Master can keep up because it runs multi-threaded, but since the checkout is on a single table on a single database, the slave cannot multi-thread to catch up.

Consequently my binary logs are full of queries like:

UPDATE db.foo SET process_id = 123 WHERE id = 456 AND process_id IS NULL;
UPDATE db.foo SET process_id = 234 WHERE id = 567 AND process_id IS NULL;

I've analyzed the general_log (turned on slave temporarily) to estimate 9/10 queries are a regex of the above query.

Can I prevent these queries from being logged? How?
Can I scrub them from log before it's relayed to slave? How?
Any other suggestion?

Plan A:

Can you avoid updating that table entirely?  If so, the Slave can be configured to have replicate-ignore-table on the Slave.  If it is an entire database (or could be moved to be such), then you can use the more efficient binlog-ignore-db on the Master.  Caveat -- If that table is needed on the Slave, well, it won't be there (or at least not up to date).

Plan B (close to what you are asking about):

SET SESSION sql_log_bin = 0;
UPDATE ...
SET SESSION sql_log_bin = 1;

Again, having the table inconsistent between Master and Slave leads to various Caveats.

Reference: https://dev.mysql.com/doc/refman/5.7/en/set-sql-log-bin.html


Merging into a self referential table with identity columns I have a number of records I need to add/update in a table that represents a hierarchical tree structure. The table has three columns

Id(PK, IDENTITY, int) | Description(varchar(50)) | ParentId(FK(Id), nullable, int)

A null value in the ParentId indicates the root node of a tree.
If possible, I'd like to insert/update these records in a single operation but since the identity isn't assigned until a record is inserted I'm having trouble figuring out how to capture this so child nodes' ParentId can be populated.

So given an existing table:

Id | Description | ParentId
1  | 'Foo'       | NULL
2  | 'Bar'       | 1
3  | 'Fizz'      | 1
4  | 'Buzz'      | 3

Is it even possible to insert a new root node and it's child nodes in a single operation?

I've attempted combining MERGE with UPDATE but it's either not possible to reference the columns of INSERTED in the same operation that populates them or else I just don't know how to do it.

This is not a single operation, but a loop that will run n number of times where n is the number of levels in your staging hierarchy.

This uses recursive common table expressions to build the hierarchy path for both a given target table (tree) and a given staging table (staging). The first example uses views containing the recursive code, and the second uses inline ctes if views are not an option.

This solution requires that the entire ancestor path be in the staging table (i.e. from the root node up), and that the combination of parentid,description is unique for each table.

The full code is available in the demos, but here is the recursive cte view for the staging table:

create view dbo.staging_path as
with cte as (
  select id, description, parentid
      , path = convert(varchar(512),description)
      , parent_path = convert(varchar(512),null)
  from dbo.staging t
  where t.ParentId is null
  union all
  select c.id, c.description, c.parentid
      , path = convert(varchar(512),p.path+'|'+c.description)
      , parent_path = convert(varchar(512),isnull(p.parent_path+'|','')+p.description)
  from dbo.staging c
    inner join cte p
      on c.parentid = p.id
  )
select id, description, parentid, path, parent_path
from cte;

which returns:

+----+-------------+----------+---------------+-------------+
| id | description | parentid |     path      | parent_path |
+----+-------------+----------+---------------+-------------+
|  1 | Foo         | null     | Foo           | null        |
|  2 | Bar         | 1        | Foo|Bar       | Foo         |
|  3 | Fizz        | 1        | Foo|Fizz      | Foo         |
|  4 | Buzz        | 3        | Foo|Fizz|Buzz | Foo|Fizz    |
+----+-------------+----------+---------------+-------------+

dbfiddle.uk with two recursive views

declare @rc int = 1;

while @rc > 0
begin;
  insert into tree (description, parentid)
    select sp.description, parentid=tp.id
    from dbo.staging_path sp
      left join dbo.tree_path tp
        on sp.parent_path = tp.path
    where not exists (
      select 1
      from dbo.tree_path i
      where sp.path = i.path
      )
      and (tp.id is not null or sp.parentid is null);

  set @rc = @@rowcount;
end;

If you can not create views to simplify the code as above, you can use inline recursive ctes like so:

dbfiddle.uk without views

declare @rc int = 1;

while @rc > 0
  begin;
    with tree_path as (
      select id, description, parentid
          , path = convert(varchar(512),description)
      from dbo.tree t
      where t.ParentId is null
      union all
      select c.id, c.description, c.parentid
          , path = convert(varchar(512),p.path+'|'+c.description)
      from dbo.tree c
        inner join tree_path p
          on c.parentid = p.id
    )
    , staging_path as (
      select id, description, parentid
          , path = convert(varchar(512),description)
          , parent_path = convert(varchar(512),null)
      from dbo.staging t
      where t.ParentId is null
      union all
      select c.id, c.description, c.parentid
          , path = convert(varchar(512),p.path+'|'+c.description)
          , parent_path = convert(varchar(512),isnull(p.parent_path+'|','')+p.description)
      from dbo.staging c
        inner join staging_path p
          on c.parentid = p.id
      )
    insert into tree (description, parentid)
    select sp.description, parentid=tp.id
    from staging_path sp
      left join tree_path tp
        on sp.parent_path = tp.path
    where not exists (
      select 1
      from tree_path i
      where sp.path = i.path
      )
      and (tp.id is not null or sp.parentid is null);

    set @rc = @@rowcount;
  end;

dbfiddle.uk with recursive view for target table, inline cte for staging

Each option results in tree having the following rows (identity was set to start at 41 for this table to differentiate the id from staging):

+----+-------------+----------+
| Id | Description | ParentId |
+----+-------------+----------+
| 41 | Foo         | null     |
| 42 | Bar         | 41       |
| 43 | Fizz        | 41       |
| 44 | Buzz        | 43       |
+----+-------------+----------+


What are the performance implications of running multiple smaller DBs instead of a single larger DB on a server? Our database architecture allows multiple 'customers' to exist in the same database, yet we split them into mulitple database for administrative reasons [patching, backup, etc.]
Question 1
What would the performance implications be if we consolidated the customers into a single DB?
Question 2
We current have multiple customer in each DB, but we may have 10 in each and say 5 database; so if we consolidated the DBs we'd just have one DB with 50 customers; would that make much difference to performance?

As others have indicated in the comments, it's difficult to form an answer to this question without an understanding of the application. It depends, it really really does.

The nature of the question (and answer) also changes on the basis of the physical environments i.e. multiple databases on a single server or spread across several? Is a customer “typical” or do some consume a disproportionate percentage of server resources? In 3 years, will there be 50 customers or 50000?

That said, let’s have a crack at it.

  What would the performance implications be if we consolidated the
  customers into a single DB?

Potential gains

Reduction in plan cache memory. If you have 5 of the same database you have 5 copies of every execution plan.
Improved buffer pool utilisation. Similar to above, any common data that you have in each database exists in the buffer pool for each database.
Improved cpu/memory utilisation. Consolidating multiple servers to a single server removes the overhead of the operating system on each host.
Possibly improved IO utilisation. Combining the smaller arrays allocated to each server might result in improved overall throughput by having higher capacity to deal with peaks.

Potential risks

$$$. Multiple low spec servers are typically cheaper than an all mighty powerhouse. Spreading the load across utility servers can be cheaper.
Expansion flexibility. When you have all customers on that all mighty powerhouse server and it runs out of steam, upgrading is difficult and complex.
Locking/blocking/deadlocks. Any deficiencies in the database and application design are likely to be magnified in a single host environment.

From your comments, it sounds like this is early days for your software and company. So, I’d be looking for ways to maximise flexibility and minimise capital expenditure.

Install an expandable iSCSI array. With separate storage, you can expand both raw GB capacity and IOPS in isolation from the servers.
Make your customers data portable. Might be difficult to retrofit but if you can migrate a customer’s data from one system to another, you can move them between systems to better balance workloads.
Tier your customers. Allocate each customer to a tier depending on their usage, perhaps 3 tiers initially. Analyse typical usage patterns for these tiers of customers and allocate them to server resources accordingly. It may be that you can accommodate 200 tier 3 customers on a single box, or 50 tier 2, or 10 tier 1.

Maybe virtualisation would be a better fit. Maybe cloud would work. Maybe hybrid cloud. Honestly, it really does depend. Call in an expert to help, they could save you a fortune.


NULL values in unique constraints I have been asked several times in interviews this question.

Why an unique key allows one null value?

I searched for the answers but I couldn't find one.
Please help me on this

Because Microsoft have implemented UNIQUE constraints to behave like this, apparently.  This is documented here (quote)

  ...UNIQUE constraints allow for the value NULL. However, as with any
  value participating in a UNIQUE constraint, only one null value is
  allowed per column ...

Apparently, this is not the behaviour as defined in the ANSI SQL standard documents (as Martin Smith's link suggests).

Other database servers behave differently in this respect.  E.g. PostgreSQL allows you to do this:

create table t ( id int unique ) ;

insert into t (id) values (1),(2),(3),(null),(null),(null) ;

-- 6 rows affected

See dbfiddle.

When working with Oracle, you can do this (or something similar), too -

create table t ( id int unique ) ;

begin
  insert into t (id) values (1);
  insert into t (id) values (2);
  insert into t (id) values (3);
  insert into t (id) values (null);
  insert into t (id) values (null);
  insert into t (id) values (null) ;
end;
/

select * from t;

ID
----
1
2
3
null
null
null

Dbfiddle here.


Why is this simple query taking more than 1 second? The table has around 17M rows and is MyISAM.

select DateModified,PELTM,PSLTM
from financial_key_ratios
where SecurityId = 4525
  and DateModified >= '2015-01-07';

This simple query seems to take more than 1 second a lot of times.

The indexes seem to be fine. Here is the output from explain:

 explain
 select DateModified,PELTM,PSLTM
 from financial_key_ratios
 where SecurityId = 4525
   and DateModified >= '2015-01-07';
+----+-------------+----------------------+-------+------------------------------------------------+------------+---------+------+------+-------------+
| id | select_type | table                | type  | possible_keys                                  | key        | key_len | ref  | rows | Extra       |
+----+-------------+----------------------+-------+------------------------------------------------+------------+---------+------+------+-------------+
|  1 | SIMPLE      | financial_key_ratios | range | SecurityId,secid,DateModified_index,secid_date | secid_date | 7       | NULL |  156 | Using where |
+----+-------------+----------------------+-------+------------------------------------------------+------------+---------+------+------+-------------+

What could we be doing wrong?

CREATE TABLE `financial_key_ratios` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `SecurityId` int(11) NOT NULL,
  `PELTM` decimal(12,4) NOT NULL,
  `PSLTM` decimal(12,4) NOT NULL,
  `POCFLTM` decimal(12,4) NOT NULL,
  `PEG` decimal(12,4) NOT NULL,
  `PBV` decimal(12,4) NOT NULL,
  `EVEBTIDA` decimal(12,4) NOT NULL,
  `Evbyrevenue` decimal(12,4) NOT NULL,
  `Fcf_yeild` decimal(12,4) NOT NULL,
  `Earnings_yeild` decimal(12,4) NOT NULL,
  `Pe_annualized` decimal(12,4) NOT NULL,
  `Pbys_annualized` decimal(12,4) NOT NULL,
  `Pbybv_annualized` decimal(12,4) NOT NULL,
  `Evbyebitda_annualized` decimal(12,4) NOT NULL,
  `Evbyrevenue_annualized` decimal(12,4) NOT NULL,
  `Pe_ltm_adj` decimal(12,4) NOT NULL,
  `Enterprise_value` decimal(12,4) NOT NULL,
  `Marketcap` decimal(12,4) NOT NULL,
  `DividendYield` decimal(12,4) NOT NULL,
  `DateModified` date NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `SecurityId` (`SecurityId`,`DateModified`),
  KEY `secid` (`SecurityId`),
  KEY `DateModified_index` (`DateModified`),
  KEY `secid_date` (`SecurityId`,`DateModified`)
) ENGINE=MyISAM AUTO_INCREMENT=65138826 DEFAULT CHARSET=latin1 |

Update:

I already have all the indexes in place. It is NOT always slow. Out of 14K requests, it is reported in slow query log around 100 times.

Here is the update from slow query log

Time=1.22s (68s)  Lock=0.00s (0s)  Rows=249.2 (13955),
select DateModified,PELTM,PSLTM from financial_key_ratios
where SecurityId = N and DateModified >= 'S'

I'm not an expert on MySQL, but it looks to me like it's doing a table scan by secid_date, which isn't even in the query at all. And scanning 17M rows is not what you want to be doing unless you really have to.

Check your indexes

mysql> SHOW INDEX FROM financial_key_ratios\G

I would make sure that you have something like

+-----------------------+------------+------------+--------------+---------------+ ...
| Table                 | Non_unique | Key_name   | Seq_in_index | Column_name   | ...
+-----------------------+------------+------------+--------------+---------------+ ...
| financial_key_ratios  |          1 | ...        |            1 | SecurityId    | ...

and if possible

| financial_key_ratios  |          1 | ...        |            2 | DateModified  | ...

If you aren't indexing on at least SecurityId, you can add it with

CREATE INDEX IdxSecurityId ON financial_key_ratios (SecurityId)

You can take it a step further and include DateModified

CREATE INDEX IdxSecurityId_DateModified ON financial_key_ratios (SecurityId, DateModified)


Assertion failure from InnoDB while restoring using Innobackupex I thought I would give back to all those people who help me when I have a problem. This might be something old, but I did not find it anywhere so here goes:

We recently upgraded our production DBs from percona 5.1 to 5.5. Parallel to this we have been using innobackupex for our backups, which has never let us down... Until now.

We were adding a node from a backup, when during the recovery phase we ran into this error:

InnoDB: Failing assertion: len % DATA_NEW_ORDER_NULL_TYPE_BUF_SIZE <= 1

Our backups were made with:

nice -n 19 innobackupex-1.5.1 --stream=tar ./ --user XXXX --password 'YYYYY' \
  --ibbackup xtrabackup_55 | \
  nice -n 19 /usr/bin/pigz - | \
  ssh -p 32231 ***@backup.****.com \
    'cat - > /var/backup/db/#{Time.now.strftime("%Y-%m-%d")}.tar.gz'

and recovered with:

innobackupex-1.5.1 --apply-log . --ibbackup xtrabackup

The problem was that our backups had been made with the switch --ibbackup xtrabackup. This was fine for mysql/percona 5.1 but does not work on mysql/percona 5.5 due to the fact that current transactions are not commited/rollbacked at the time of the backup.

Luckily there is a small fix for this if you do not have the luxury of stopping traffic on the DB. By using --ibbackup xtrabackup_55 the recovery recognizes the pending transactions and handles them accordingly.

Our script now looks like this:

nice -n 19 innobackupex-1.5.1 --stream=tar ./ --user XXXX --password 'YYYYY' \
  --ibbackup xtrabackup_55 | \
  nice -n 19 /usr/bin/pigz - | \
  ssh -p 32231 ***@backup.****.com \
    'cat - > /var/backup/db/#{Time.now.strftime("%Y-%m-%d")}.tar.gz'

and

innobackupex-1.5.1 --apply-log . --ibbackup xtrabackup_55

respectively.

Just thought I would share this tidbit, due to the fact that looking for a solution cost me approx. 3,5 hours.


JOIN to obtain all the rows from the first table along with NULLs from the second table I have two tables (tblReps and tblDailyWorkingTime). The tblReps table, i.e., the first one, returns 37 rows, but when I JOIN it with the second table I get 36 rows as the second table only has 36 rows that match the first table.

How do I go about getting the 37 rows back with a NULL result for the row that was not matched?

Here is the query that I have so far:

SELECT
    tblReps.[sName] AS 'RepName',
    tblReps.sNote AS 'Type',
    RIGHT(CAST(tblDaily.sDateAndTimeStart  AS smalldatetime), 8) AS 'DayStarted'

FROM
    [tblRepresentatives] AS tblReps

FULL OUTER JOIN
    tblDailyWorkingTime AS tblDaily ON tblDaily.sRepresentativeCode = tblReps.sCode

WHERE
    tblDaily.sDate = CAST(GETDATE() AS DATE)
    AND tblReps.[sActive] = 'True'

First of all, the correct type of join to use in this case is a left join:

...
FROM
    [tblRepresentatives] AS tblReps

LEFT OUTER JOIN
    tblDailyWorkingTime AS tblDaily ON tblDaily.sRepresentativeCode = tblReps.sCode
...

In your specific case, you could still use FULL because this condition in WHERE would turn it into a left join anyway:

tblReps.[sActive] = 'True'

But it is better to express the intent accurately.

The same effect that turns your full join into a left join is actually responsible for the missing 37th row that you expected. More specifically, this other WHERE condition:

tblDaily.sDate = CAST(GETDATE() AS DATE)

transforms your join further into an inner join. That is why the 37th row is missing from the output.

The reason for that happening is this. The FROM clause returns a null in tblDaily.sDate for the unmatched row. Because the WHERE clause logically executes after the FROM, the above-mentioned predicate excludes the unmatched row, since NULL = CAST(GETDATE() AS DATE) does not evaluate to True.

What you need to do instead is move the sDate condition to the ON subclause:

...
FROM
    [tblRepresentatives] AS tblReps

LEFT OUTER JOIN
    tblDailyWorkingTime AS tblDaily ON tblDaily.sRepresentativeCode = tblReps.sCode
                                   AND tblDaily.sDate = CAST(GETDATE() AS DATE)

WHERE
    tblReps.[sActive] = 'True'

That way the right-hand side of the join is filtered on tblDaily.sDate = CAST(GETDATE() AS DATE) before the join takes place. Consequently, the query will return the expected 37 rows from tblReps complemented either with matching data from tblDaily or with nulls.


Upgade from SQL Server 2012 cluster Evaluation to enterprise What I understand is that there is a restriction of upgrading a SQL Server 2012 cluster from evaluation edition to licensed standard edition.

Is there a similar limitation for upgrade from cluster evaluation edition to licensed enterprise edition. If no , can you please provide a link for the upgrade process

Supported Upgrade Path for SQL 2012 can be found here:

https://technet.microsoft.com/en-us/library/ms143393%28v=sql.110%29.aspx?f=255&MSPPError=-2147217396

Upgrade from SQL Server 2005 Evaluation, SQL Server 2008 Evaluation, and SQL Server 2008 R2 Evaluation to SQL Server 2012 is not supported.

So if you are just making a 2012 eval edition to a full enterprise edition it'll work.  You can do so by starting setup.exe to bring up the installation menu.
Go to maintenance, then 'edition upgrade'
See the pic:

Or you could run the command line to install SQL Server but pass in the Product ID, using your own path, /q command optional.

setup.exe /q /ACTION=editionupgrade /InstanceName=MSSQLSERVER /PID=<appropriatePid> /SkipRules= Engine_SqlEngineHealthCheck


Create view with column name aliases or hard change column names on a big database with a very low amount of changes? Background:
I have to work with an big enterprise geodatabase on an SQL Server Express 2012 server with around 170 tables where many of these tables have 10+ columns. Some column names are repetitive, e.g. FEAT_ID, GEOM, what means that very many tables have equally named column names.
The application program my company accesses these big databases with doesn't support column name length > 31. Only two of all 170 tables own one column with a column name which does not meet the requirements (so overall two column names). I have to shorten these two column names. I guess this could be done by introducing aliases. So it's a very minor change to a big database.
Below is the first problematic table. The problematic column name in this table is FT_ADDRESS_AREA_BOUNDARY_ELEMENT as its length is 32

USE [foo]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [foo].[baron](
[FEAT_ID] [uniqueidentifier] NOT NULL,
[FT_ROAD_ELEMENT] [bit] NOT NULL,
[FT_FERRY_ELEMENT] [bit] NOT NULL,
[FT_ADDRESS_AREA_BOUNDARY_ELEMENT] [bit] NOT NULL,
[FT_RAILWAY_ELEMENT] [bit] NOT NULL,
[COUNTRY_LEFT] [nvarchar](3) NULL,
[COUNTRY_RIGHT] [nvarchar](3) NULL,
[CENTIMETERS] [numeric](9, 0) NOT NULL,
[POSITIONAL_ACCURACY] [numeric](1, 0) NULL,
[ADA_COMPLIANT] [bit] NOT NULL,
[GEOM] [geometry] NOT NULL,
CONSTRAINT [PK_baron] PRIMARY KEY CLUSTERED
(
 [FEAT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Further below is the second problematic table with the too long column name MAIN_OF_DETAILED_POSTAL_POINT_ID:

USE [foo]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [foo].[baroness](
    [FEAT_ID] [uniqueidentifier] NOT NULL,
    [FEAT_TYPE] [numeric](5, 0) NOT NULL,
    [NAME] [nvarchar](254) NULL,
    [LANG_CODE] [nvarchar](3) NULL,
    [COUNTRY] [nvarchar](3) NULL,
    [POSTAL_CODE] [nvarchar](254) NULL,
    [NOTATION] [numeric](2, 0) NULL,
    [ISO_SCRIPT] [nvarchar](4) NULL,
    [MAIN_OF_DETAILED_POSTAL_POINT_ID] [uniqueidentifier] NULL,
    [POSTAL_DELIVERY_TYPE] [nvarchar](1) NULL,
    [POSTAL_POINT_TYPE] [numeric](1, 0) NULL,
    [NC_MAIN] [nvarchar](254) NULL,
    [NC_MAIN_OFFSET] [numeric](1, 0) NULL,
    [NC_SUB] [nvarchar](254) NULL,
    [NC_SUB_OFFSET] [numeric](3, 0) NULL,
    [GEOM] [geometry] NULL,
 CONSTRAINT [PK_baroness] PRIMARY KEY CLUSTERED
(
    [FEAT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [foo].[baroness]  WITH CHECK ADD  CONSTRAINT [FK_Postal_Point_MAIN_OF_DETAILED_POSTAL_POINT_ID_2_Postal_Point] FOREIGN  KEY([MAIN_OF_DETAILED_POSTAL_POINT_ID])
REFERENCES [foo].[baroness] ([FEAT_ID])
GO

ALTER TABLE [foo].[baroness] CHECK CONSTRAINT [FK_Postal_Point_MAIN_OF_DETAILED_POSTAL_POINT_ID_2_Postal_Point]
GO

Questions:
Is it worth creating a view (or multiple?) or should I just "hard change" these two columns and track it appropriately? I have no experience with views at all. When I tried to create an appropriate view with Microsoft SQL Server Management Studio, I received an error message that column names should be unique. Do I have to create aliases for every repetitive column name in the whole database when creating a view? Should I create a view with only the two changed tables and one with the rest of the database and merge them? I could also imagine that the performance would suffer if I create a view where I have to select the whole database at once.

Here would be two recommendation based on working in environments with lots of databases being used by lots of different apps.

Option 1:
Create a view with a similar name but reflecting the changes you want made. In your case, it's just one field renamed. You may have to create triggers on the view so the apps that perform DML can use it just like they use the original table.

Publicize the view name and encourage the app developers to change their code to use the view instead of the table. You want the only change needed to be the name of the table and any changes propagated through the view (the new name of the field). They can make the changes more or less "at their leisure" as the original table still works as before. You will, of course, give them a deadline to get this finished.

When all app changes have been made (and verified), you can really consider yourself finished. I encourage a system of exposing only views to apps instead of entity tables. This allows for much more freedom to make necessary changes to the tables and change views so everything looks the same to apps.

But if you want to, rename the fields in the tables and, after extensive testing of course, notify app developers to change the "table" names back to original.

Test and finished. Delete the views or keep them around if you prefer.

Option 2:
An alternative would be to rename the tables and create the views with the original table names. Then all apps would need to be changed at once to use the new field name(s). Thoroughly test after bringing the system back online.

Next, change the tables as needed and test. Then reverse the process -- rename (or drop) the views and rename the altered tables back to the original names. Test and (hopefully) finished.


Oracle Nested Query with Group By and Having Clauses So here's the question

Find the IDs of aircraft which have flown more distance for a destination than the average distance of all flights for that same destination

Here is the schema

Destinations(did: integer, dname: string, state: string)
Aircraft(aid: integer, aname: string, color: string)
Flights(aid: integer, did:integer, fdate:date, distance: real)

So far I have been able to find answers to two different parts of this query.
I have found the average distance flown to each destination and I have also found the total distance flown to each location by each aircraft.
The queries for the above are listed below:
SELECT D.DID, AVG(F.DIST) as AvgDist
from Destinations D
join Flights F ON F.DID = D.DID
GROUP BY D.DID;

SELECT F.AID,F.DID, SUM(F.DIST) as fDist
from Flights F
join Destinations D ON F.DID = D.DID
GROUP BY F.AID,F.DID;

My Problem is I do not see how to combine them to find the solution. I know all I need to do is figure out a way to check if the average distance of that destination is less than the distance flown to by that individual aircraft but i'm just not sure how to proceed. All my Having clauses seem to not work so i'm sort of stuck for now.

If I understand correctly:

select *
  from (SELECT D.DID, AVG(F.DIST) as AvgDist
        from Destinations D
        join Flights F ON F.DID = D.DID
        GROUP BY D.DID) avgdist
  join
       (SELECT F.AID,F.DID, SUM(F.DIST) as fDist
        from Flights F
        join Destinations D ON F.DID = D.DID
        GROUP BY F.AID,F.DID) sumdist
        on avgdist.did = sumdist.did
  where avgdist.avgdist < sumdist.fDist;


Calculate percent changes in contiguous ranges in PostgreSql I need to calculate price percent change in contiguous ranges. For example if price start moving up or down and I have sequence of decreasing or increasing values I need to grab first and last value of that sequence and calculate the change.

I'm using window lag function to calculate direction, my problem- I can't generate unique RANK for the sequences to calculate percent changes.
I tired combination of RANK, ROW_NUMBER, etc. with no luck.

Here's my query

WITH partitioned AS (
  SELECT
    *,
    lag(price, 1) over(ORDER BY time) AS lag_price

  FROM prices
),
sequenced AS (
  SELECT
    *,
    CASE
      WHEN price > lag_price THEN 'up'
      WHEN price < lag_price THEN 'down'
      ELSE 'equal'
    END
     AS direction
  FROM partitioned

),
ranked AS (
  SELECT
    *,
    -- Here's is the problem
    -- I need to calculate unique rnk value for specific sequence
    DENSE_RANK() OVER ( PARTITION BY direction ORDER BY time) + ROW_NUMBER() OVER ( ORDER BY time DESC) AS rnk
    -- DENSE_RANK() OVER ( PARTITION BY seq ORDER BY time),
    -- ROW_NUMBER() OVER ( ORDER BY seq, time DESC),
    -- ROW_NUMBER() OVER ( ORDER BY seq),
    -- RANK() OVER ( ORDER BY seq)

  FROM sequenced
),
changed AS (
  SELECT *,
    FIRST_VALUE(price) OVER(PARTITION BY rnk ) first_price,
    LAST_VALUE(price) OVER(PARTITION BY rnk ) last_price,
    (LAST_VALUE(price) OVER(PARTITION BY rnk ) / FIRST_VALUE(price) OVER(PARTITION BY rnk ) - 1) * 100 AS percent_change
    FROM ranked

)
SELECT
    *
FROM changed
ORDER BY time DESC;

and SQLFiddle with sample data


How do I prevent an ORA-08102 error I am getting an ORA error (08102 - Index Key Not Found) with several customers, and I have been unable to reproduce or explain it.  They have all been on the same index on the same table.

This error is tied to two tables - ALPHA and BRAVO.  ALPHA has a primary key on alpha_id. BRAVO links to ALPHA by a foreign key reference to ALPHA.alpha_id.  For these customers, all BRAVO rows point to a single APLHA row on the BRAVO table. We have an index on the BRAVO table whose only column is BRAVO.alpha_id.

That index, ALPHA_ID_FK, is generating the error when we go to delete rows. As I said, I have been unable to replicate this myself, but I'm getting reports of the same problem by several different customers.

We are sometimes able to rebuild the index to correct this issue, but even rebuilding the index is not working for all customers.

What is causing this error, and how can I better prevent it?

Disclaimer:
As @JustinCave wrote, you should contact Oracle support.
I give the following information without any warranty.
Source of Information:
I found the following metalink notes that analyze this problem:

ORA-08102: Rebuilding Index does not correct ORA-8102 (Doc ID 96118.1)
ORA-8102 CORRUPT INDEX, ORA-1499 EVEN AFTER DROPPING AND RECREATING INDEX (Doc ID 1081343.6)

Why does Rebuilding an Index not help?
Rebuilding the index may not help. 'REBUILD' uses the data of the existing index. If the data of the index is corrupt you will not get rid of this corruption. So a 'DROP INDEX ...' followed by a 'CREATE INDEX ...' is an option. But the create index may scan another index that contains the fields of the index to create. An if the index that is scanned is corrupt you still may create a corrupt index. Finally you may drop all indexes that my be used for creating your index and recreate them all. But it may also happen that your table is corrupt and then index recreation will generate again corrupt indexes. If this is the case you have to repair the corrupt table before you rebuild the indexes.
What is Corrupt?
To check what problems your table/indexes have you can issue the statement
ANALYZE TABLE <owner>.<table_name> VALIDATE STRUCTURE CASCADE;

It will generate a trace file with the appropriate errors, but maybe only an Oracle support engineer can read this trace output. The analyze statement may lock your table.
How to Repair a Table?
If rebuild or the recreation of one or more indexes does not help, because the table is corrupt then the table mus be rebuilt. This can be done by exporting the table with data pump (expdp) , dropping the table and importing the table with data pump (impdp) (or exp/imp for older Oracle versions). You may loosed data (of the corrupt rows) when doing this export import.


Hard to tell what you want (a precise question gets a precise answer, a vague question at best gets a lucky guess). In Firebird 2.5 an answer may be something like:

EXECUTE BLOCK RETURNS (ID INTEGER, ID2 INTEGER, FROM DATE, TO DATE,
                        COSTS DECIMAL(9,2), MySUM(DECIMAL(9,2))
AS
DECLARE VARIABLE FROMDATE DATE;
DECLARE VARIABLE TODATE DATE;
BEGIN
  FROMDATE = NULL;
  TODATE = NULL;
  MySUM = 0;
  FOR SELECT ID, ID2, FROM, TO, COSTS
      FROM MyTable
      INTO :ID, :ID2, :FROM, :TO, :COSTS DO
  BEGIN
    IF (FROMDATE <= FROM and TODATE >= TO) THEN
    BEGIN
      MySUM = MySUM + COSTS;
      SUSPEND;
    END
    ELSE
    BEGIN
      MySUM = COSTS;
      FROMDATE = FROM;
      TODATE = TO;
    END
    SUSPEND;
  END
END

By the way, I don't think the above would quite compile since 'FROM' and 'TO' are not names I would recommend as names of columns.

Still, HTH,
Set


How to use the covariance and variance in PostgreSQL I am going through the documentation:
https://www.postgresql.org/docs/9.5/static/functions-aggregate.html

for the aggregate functions: corr(Y, X), covar_pop(Y, X) but I cannot find any valid example.

Can you please show me how to use these two functions or pointing me to some extra doc/examples?

There is an answer on https://stackoverflow.com/questions/3015846/calculating-correlation-coefficient-using-postgresql that is relevant for you.

Corr(y,x) is pretty easy. Say you have a table of people with columns height and weight. You wanted to know if those were correlated.

select corr(height,weight) from people;


Displaying a value associated with a maximum value I have a PostgreSQL database for a supermarket (it's a toy problem) and I need to find which outlet has sold the most copies of each product and display it in a query which also shows the product name, description, copies in stock across all stores, copies purchased across all stores.

I think I have the first few columns queried appropriately, as below:

SELECT a.ProductName, a.ProductDescription, a.StockSum, b.PurchaseSum, c.MaxSales
FROM (SELECT Product.Name AS ProductName, Product.Description AS ProductDescription
           , SUM(Stock.copies) AS StockSum
FROM Product
INNER JOIN Stock
ON Stock.product_id = Product.product_id
GROUP BY Product.name, Product.description) AS a
FULL JOIN
(SELECT Product.name AS ProductName, Product.description AS ProductDescription
      , SUM(PurchaseItem.copies) AS PurchaseSum
FROM Product
INNER JOIN PurchaseItem
ON PurchaseItem.product_id = Product.product_id
GROUP BY Product.name, Product.description) AS b;

But I cannot for the life of me work out how to pull the appropriate Outlet.name associated with the MAX of the SUM of all PurchaseItem.copies corresponding to a particular product_id at a particular store. It seems like a very complex query, and it has flummoxed me!

The structure of the database is that Purchase references Outlet, while PurchaseItem references Purchase and a single Product (with PurchaseItem.copies recording the number of sales of that product in the purchase).

You could use a window function here, but I would think there's actually a better solution with DISTINCT ON.
First I simplified what you had so far:
SELECT p.name AS product_name, p.description AS product_description
     , a.stock_sum, b.purchase_sum
     , c.max_sales, o.outlet_name   -- still missing
FROM   Product p
LEFT   JOIN (
   SELECT product_id, SUM(copies) AS stock_sum
   FROM   Stock
   GROUP  BY 1
   ) a USING (product_id)
LEFT   JOIN (
   SELECT product_id, sum(copies) AS purchase_sum
   FROM   PurchaseItem
   GROUP  BY 1
   ) b USING (product_id)
-- c, o still  missing

It should be considerably faster to aggregate counts before joining:

Why does the following join increase the query time significantly?

Also, LEFT JOIN retains products in the result that don't have any purchases, yet, or aren't in stock any more.
Then add the missing parts:
LEFT   JOIN (
   SELECT DISTINCT ON (product_id)
          pi.product_id, pu.outlet_id, sum(copies) AS max_sales
   FROM   Purchase      pu
   JOIN   PurchaseItem  pi USING (purchase_id)
   GROUP  BY 1, 2
   ORDER  BY 1, sum(copies) DESC NULLS LAST
   ) c  USING (product_id)
LEFT   JOIN Outlet o USING (outlet_id);

About DISTINCT ON:

Select first row in each GROUP BY group?

You can run DISTINCT over the results of the aggregation. Consider the sequence of events in a query:

Best way to get result count before LIMIT was applied in PHP/PostgreSQL

Optimize performance
It's probably cheaper to scan PurchaseItem only once, using a CTE. But this also adds some overhead. You'll have to test which is faster:
WITH ct AS (
   SELECT pi.product_id, pu.outlet_id, sum(pi.copies) AS sales
   FROM   PurchaseItem  pi
   JOIN   Purchase      pu USING (purchase_id)
   GROUP  BY 1, 2
   )
SELECT p.name AS product_name, p.description AS product_description
     , a.stock_sum, b.purchase_sum
     , c.max_sales, o.outlet_name
FROM   Product p
LEFT   JOIN (
   SELECT product_id, SUM(copies) AS stock_sum
   FROM   Stock
   GROUP  BY 1
   ) a USING (product_id)
LEFT   JOIN (
   SELECT product_id, sum(sales) AS purchase_sum
   FROM   ct
   GROUP  BY 1
   ) b USING (product_id)
LEFT   JOIN (
   SELECT DISTINCT ON (product_id)
          product_id, outlet_id, sales AS max_sales
   FROM   ct
   ORDER  BY product_id, sales DESC
   ) c  USING (product_id)
LEFT   JOIN Outlet o USING (outlet_id);

Test performance with EXPLAIN ANALYZE (a couple of times to exclude caching effects).


pgbouncer 1.7 with TLS/SSL client and server connections With settings borrowed from a similar server running Postgres 9.4.1 & pgbouncer 1.6.1, I have multiple users connecting to the database through pgbouncer on port 6543. I also have a second server running PostgreSQL 9.4.5 where I have verified that all users are only able to connect directly to the databases (on port 5432) using TLS/SSL set to verify-full.

However, I need to create an environment combining these configurations: one where all users connect to the database via TLS/SSL with connection pooling through pgbouncer. That means I need to use the new TLS/SSL functionality in the recently released (as of Dec 18, 2015) pgbouncer 1.7, but aside from documentation of the new TLS parameters, I have not found any examples available demonstrating the new functionality nor had any success on my own establishing a valid connection through pgbouncer using TLS/SSL on my second server.

I have included pertinent excerpts from the postgresql.conf, pg_hba.conf, & pgbouncer.ini from my second server.

postgresql.conf:

ssl = on                                # (change requires restart)
ssl_cert_file = 'server.crt'            # (change requires restart)
ssl_key_file = 'server.key'             # (change requires restart)
ssl_ca_file = 'root.crt'                        # (change requires restart)

pg_hba.conf:

hostssl    all             all             10.10.5.0/24            cert

pgbouncer.ini:

;
; pgbouncer configuration
;
[databases]
mydatabase = host=localhost port=5432 dbname=mydatabase
;
[pgbouncer]
listen_port = 6543
listen_addr = *
admin_users = lalligood, postgres
logfile = /tmp/pgbouncer.log
pidfile = /tmp/pgbouncer.pid
ignore_startup_parameters = application_name
server_reset_query = DISCARD ALL;
pool_mode = session
max_client_conn = 1000
default_pool_size = 300
log_pooler_errors = 0
; Improve compatibility with Java/JDBC connections
ignore_startup_parameters = extra_float_digits
; USER AUTHENTICATION (old way commented out with new lines below)
;auth_type = md5
;auth_file = pgbouncer/users.txt
auth_type = hba
auth_hba_file = pg_hba.conf
; TLS SETTINGS (NEW STUFF!)
client_tls_sslmode = verify-full
client_tls_key_file = server.key
client_tls_cert_file = server.crt
client_tls_ca_file = root.crt
server_tls_sslmode = verify-full
server_tls_key_file = /tmp/pgb_user.key
server_tls_cert_file = /tmp/pgb_user.crt
server_tls_ca_file = root.crt

pgbouncer starts, however, when I attempt to connect as any user but let's say I want to be user 'lalligood', I get the following error:

ERROR: no such user: lalligood

pgbouncer.log contains the following line for each attempt:

2016-01-13 16:00:36.971 2144 LOG C-0xcad410:
(nodb)/(nouser)@10.10.5.194:54848 closing because: No such user:
lalligood (age=0)

I can provide more information if necessary. If anyone has any advice/suggestions on what I might be overlooking to get this working, I greatly appreciate the help!

I figured it out... I was (partially?) guilty of trying to use too many new features in pgbouncer 1.7.

There are the TLS/SSL settings & then there is the HBA access controls. (TLS/SSL doesn't need HBA access controls & vice-versa to work). Also, since pgbouncer & the database are on the same box, there is no need for the extra overhead of TLS/SSL between pgbouncer & the database.

Simplifying to just use more commonly used user authentication settings proved to be the fix.

First, postgresql.conf & pg_hba.conf were left untouched as show above.

pgbouncer.ini, however, is this:

;
; pgbouncer configuration
;
[databases]
mydatabase = host=localhost port=5432 dbname=mydatabase
;
[pgbouncer]
listen_port = 6543
listen_addr = *
admin_users = lalligood, postgres
auth_type = cert
auth_file = pgbouncer/users.txt
logfile = /var/lib/pgsql/pgbouncer.log
pidfile = /var/lib/pgsql/pgbouncer.pid
ignore_startup_parameters = application_name
server_reset_query = DISCARD ALL;
pool_mode = session
max_client_conn = 1000
default_pool_size = 300
log_pooler_errors = 0
; Improve compatibility with Java/JDBC connections
ignore_startup_parameters = extra_float_digits
; TLS settings
client_tls_sslmode = verify-full
client_tls_key_file = server.key
client_tls_cert_file = server.crt
client_tls_ca_file = root.crt

So the specific changes are auth_type = cert & auth_file = pgbouncer/users.txt (changing/removing the HBA references) & stripping out the 4 server_tls_... lines at the end.

The users authenticate to both pgbouncer and the postgres database using the SSL cert.

This also means that I have to put together a list of users that will be going through pgbouncer in ./pgbouncer/users.txt. The format should be just like this (for each user):

"lalligood" ""

since pgbouncer will not be verifying any connections based on a password.

So what all this means is that TLS/SSL authentication/connectivity through pgbouncer works. But it also leaves me with the feeling that auth_type = hba / auth_hba_file = pg_hba.conf is suspect at best; not working properly at worst.


SQL Server Configuration Manager returns remote procedure call failed I open the SQL Server Configuration Manager and it returns remote procedure call failed.

This is Windows 7 Ultimate version, SQL Server 2008 R2 Express version with advanced services.

Previously I installed SQL Server 2008 R2 Express version and since I needed the full text search I uninstalled SQL Server 2008 R2 Express and then installed SQL Server 2008 R2 Express with advanced services.

P.s my RPC background service is running.

I executed the following command and it returned an error:

 C:\Users\venkat>mofcomp.exe "C:\Program Files (x86)\Microsoft SQL    Server\100\Shared\sqlmgmproviderxpsp2up.mof"

 Microsoft (R) MOF Compiler Version 6.1.7600.16385
 Copyright (c) Microsoft Corp. 1997-2006. All rights reserved.
 Parsing MOF file: C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof
 MOF file has been successfully parsed
 Storing data in the repository...

Error:

  An error occurred while processing item 1 defined on lines 4 - 7 in file C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof:
  Error Number: 0x80041003, Facility: WMI
  Description: Access denied
  Compiler returned error 0x80041003

In my case there were two instances of the "SQL server Configuration Manager" in SysWOW64. Only the latest version worked. The other version produced the RPC error message.

C:\Windows\SysWOW64\mmc.exe /32 C:\WINDOWS\SysWOW64\SQLServerManager10.msc FAILED
C:\Windows\SysWOW64\mmc.exe /32 C:\WINDOWS\SysWOW64\SQLServerManager11.msc WORKED


Table Relationship - One to Few (Many) I know the title may seem confusing but I have yet to find a solid answer to this design pattern. Let's say I want to create an Employee table and have multiple addresses for each employee. So this would call for your standard one-to-many relationship. You obviously want and Address Type column to track what type of addresses you are storing for each employee.

Now for my issue, I want to only allow one address per address type for each employee. This breaks the one-to-many model because I want to force this restriction at the database level. I see a few ways of dealing with this issue but I am stuck on which one is best based on best practices and functionality.

Below are the same tables with the AddressId removed, for lack of usefulness, and instead a compound primary key of EmployeeId and AddressTypeId. This would enforce one address type per employee, allowing each employee to have multiple addresses but only one of each type.

For the purposes of enforcing key restraints on the address table, this would probably work fine. But this seems more like a one-to-one/none relationship which could cause me issues with ORM that don't work with this kind of table design. (Correct me if I am wrong please.)

Below is what I know to be the typical bridge table (many-to-many-to-many) relationship. It, like the above table, deals with the address type restriction I want to enforce while also keeping each table well structured and with its own singular primary key. This design will work fine with most ORMs and is probably the correct way to go but it seems like overkill and it seems to break the meaning of the many-to-many relationship as there will never be an address with multiple employees.

Is the bridge table relationship design the best to be using despite its complexity? Other than the issues that might arise with an ORM would the compound key relationship cause issues I am not seeing? Or am I totally insane and seeing this all the wrong way and there is actually a better way to be dealing with this scenario?

The compound PK of EmployeeId and AddressTypeId is exactly what you want, and you don't need anything else. The relationship is still a one-to-many relationship, as for one employee, you may have many address whether it is a home address or a work address.

The bridge table is interesting if you may have the same address for multiple employees AND that a same address may be of a different type for multiple employees. If it's not the case, this table is pretty overkill and may add more complexity for nothing.

TL:DR: First solution is probably the best.


How to upgrade a SQL 2000 Database to SQL 2005 - need confirmation? I need to confirm the process for upgrading a standard SQL 2000 database to SQL 2005. As I understand it, there are at least 2 methods for upgrading. Back\Restore and Detach\Attach DB.  In my case, I need to leave the original SQL 2000 Database online, so I believe it is best to do a Back\Restore.
Can someone confirm the process below and shed some light on my questions?

On the SQL 2000 Server

Take a Full Backup of the Database
Databases->[Database]->All Tasks->Backup Database..., selecting 'Full', and saving the file (.bak)
Move the backup file (.bak) to the new SQL 2005 server

On the New SQL 2005 Server

Copy the .MDF and .LDF files from the old SQL 2000 Server to the New SQL 2005 Server.
Rename files if desired to something more standard
Create a New SQL 2005 Database.
Perform a Restore of the backup file (.bak)
Select “Options” tab. This will have the mdf and ldf locations that were in the backup file. Change these to legitimate directories on your new Server.
Change the Compatibility to “SQL Server 2005 (90)”
Questions:

What performs the Upgrade?
Is the SQL 2000 Database automatically UPGRADED during the restore, by the SQL 2005 Engine?
Do I need to copy the .MDF and .LDF files from the old SQL 2000 Server to the New SQL 2005 Server?
Do I need to create a New SQL 2005 database?

Thanks in advance for all of your help.
Jorge

Since you are using the Backup/Restore method, you don't need to copy the database (mdf) and log file (ldf) to the new server, just the backup files. You do not need to recreate the database either. The database, database files and log file will all be created during the restore process.  The upgrade process is done by SQL Server when restoring your database backup.

Here is the method I use:

On SQL Server 2000

Perform a full backup of the database
Perform a log backup of the database
"captures records on the transaction log that were written since the last transaction log backup".  Consider using the WITH NORECOVERY option, if you don't want people accessing the database after you're done.
Copy the full database and log backup files to the server that has SQL Server 2005

On SQL Server 2005

Restore the full backup with NORECOVERY
Make sure you change the directories as mentionned in your post.
Restore the log backup with RECOVERY

Once the database is restored on the new server.  I run the following commands in a new query window

USE [mydb]
sp_updatestats -- updates statistics
This is because statistics are not automatically updated during the upgrade process.
DBCC CHECKDB WITH DATA_PURITY

Causes DBCC CHECKDB to check the database for column values that are not valid or out-of-range.  This is important when upgrading from SQL Server 2000 to 2005 or 2008.

Associating database users with Logins

Now that your database has been restored, you need to associate its orphaned users with an actual login.

Method A: Fixing orphaned users

EXEC sp_change_users_login 'Report' -- find orphaned users
if the login exists already, you can asssociate the orphaned user with the login. Otherwise, you can create the login

-- Login exists

EXEC sp_change_users_login 'Auto_Fix', 'user'

-- Login doesn't exist

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

Method B: sp_help_revlogin

You also have an alternative called sp_help_revlogin which can help you to restore not only the login but the original password that went with it.

http://support.microsoft.com/kb/246133/en-us

It essentially entails creating a stored procedure on your old server to generate a script that will output all of your existing logins.  You then copy the output of that stored procedure to your new server and restore the logins that are associated with the database you restored.


Maintaining Postgres copies of fdw tables from Oracle I have a production Oracle 11g server, from which I want to copy data regularly to PostgreSQL 10.14 database for DWH uses, so as not to overload the production with analytics queries.
I have succesfully mirrored the neccessary Oracle views with FDW wrapper, so relevant Oracle structures are accessible, and I have a couple of functions and queries I run regularly, to copy oracle_fdw view data to Postgres tables.
As Oracle is used for a business system, which is maintained by external support company, they are occasionally tweaking some of the views I copy, in order to enable new functionality in the business system. For example, they are adding columns to Oracle tables and views, and/or extending some column sizes.
When that happens, the Oracle fdw views in Postgres need to be reimported, or else they fail when trying to fetch a column, in which place there is another column now, or the column sizes don't match anymore, or there are more columns in the actual Oracle structs than in the fdw imported view.
Reimport, however, still breaks the queries and/or functions which use constructs like:
INSERT INTO my_pg_mirror_table_archive SELECT * FROM oracle_fdw_view;
because as most of Oracle structures I copy, are actually views, the new Oracle columns can and do sometimes appear in middle of others, instead of at end, like:
oracle_fdw_table: col1 new_col3 col2
which means even if I add column to my_pg_mirror_table_archive the above query will fail, as it will expect the column order col1 col2 new_col3.
I can circumvent that by manually specifying column order instead of SELECT * , however, the more tables the more possibility of mistakes and manual work.
In addition there are views on the pg_mirror_table_archive, which depend on it and need to be recreated after every such column addition/change.
Are there any tools or best practices that can automagically or semi-automatically maintain such relationship(s) and do the behind scenes work, so that my postgres tables always match the oracle ones?

I have a production Oracle 11g server, from which I want to copy data regularly to PostgreSQL 10.14 database for DWH uses ...

Why move the data from Oracle to Postgres at all?
Why not use a Data Guard based solution to populate your Data Warehouse?
Three possibilities:
Logical Standby:
Data Guard applies SQL on the Standby (instead of the more efficient, Redo vectors).
Logical Standbys can be used to load-balance near real-time, read-only activity away from the Primary.
The Logical Standby database can be structurally different (i.e. different indexes, etc.) from the Primary ...   and therein lies your problem if the vendor goes and changes stuff - this will not get carried through into the Logical Standby automatically.  You would have to find out the changes and re-make them in the Standby.
Alternatively, you could rebuild the Logical Standby database after each Product release or build a new Logical Standby, route Users to that one and throw the old one away).
Active Data Guard:
This is a "cost option", but it might be your simplest solution.
Using Active Data Guard, your Physical Standby database becomes readable and is [very] near real-time up-to-date with the Primary.
The Standby database is structurally identical to the Primary.
Snapshot Standby:
Spends some of its life as an inaccessible, Physical Standby database (unless you pay for Active Data Guard), but when you want to use it, you "unplug" it from the Data Guard configuration and open it as a database in its own right, with Data as of when it was "unplugged".  Each night, you "plug" it back into the Data Guard configuration and redo (held on the Standby's host) is re-applied, bringing the database back "up-to-date" with the Primary.
The Standby database is structurally identical to the Primary unless you choose to add extra indexes, etc., after the "unplugging" activity.

Reimport ... still breaks ... constructs like:
INSERT INTO my_pg_mirror_table_archive SELECT * FROM oracle_fdw_view;

And this is just one of the reasons why should you never use "select *" in Production Code.


SQL Server 2016 table corrupt I have an SQL Server 2016 v13.0.5101.9 hosting Sharepoint databases. The weekly DBCC CHECKDB job discovered database corruption (or inconsistency): Msg 8928,  and Msg 8965 popping up.

I narrowed it down to the Docstreams table which occupies 99% of the database space, so that doesn't help much: the table is around 100GB, I suspect mostly in the "Content" column of type  varbinary(max).

Within that table I narrowed down the corruption to 8 pages. The REPAIR ALLOW_DATA_LOSS option of DBCC CHECKTABLE which was suggested is not really appealing, considering the possible consequences in Sharepoint. So far, there are no complaints about the corruption from the users, but I suspect that is just a matter of time.

What I'm trying to do is to identify corrupt rows and have the rows (=sharepoint content) removed in a controlled way using sharepoint. So there I go using DBCC PAGE (parameters) which I found is supposed to have four main sections: Buffer, Page Header, Data, and Offset Table  ... but it show only the first 2 sections, the third is supposed to show the data I want to see.

So the question is: Is there a(nother) way to identify the rows that are affected by the corrupt pages that allows me to point to the sharepoint content?

Note 1: this is a new database of which the contents were migrated from a filesystem by an external vendor last week. There is no indication of any faulty disk, but somehow this inconsistency must have crept in.

Note 2: I have copied the database to an non-prod SQL Server in order to investigate the issue. I have already removed the unique constraint and thus related clustered index. The problem lies in the table itself. In other words: recreating the (clustered) index doesn't work.

Note 3: I was thinking about doing a select * into newtable, but as I'm writing this, it crashed with:

  Msg 824, Level 24, State 2, Line 67
  SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xd843cba2; actual: 0x908acba5). .... and actually pointing to the pages found in the DBCC.

To answer my own question for anyone in the same situation: Although the DBCC CHECKTABLE ... with ALLOW_DATA_LOSS option very nicely fixed the problem and removed the corrupt pages in a test-environment, it was decided to redo the migration. That was possible because the source-data turned out to be static: nothing had changed there...