column alias in dynamic pivot I need help with a dynamic pivot query. I would like to modify the column name (alias) but I can't get it done in the pivot. Please help. The query itself works perfectly but the username contains the domain\username format. I am trying to drop the "domain\" portion. I cannot control what is in or inserted in the database nor can I make any structure changes. I have tried "replace(x,'domain\','') but again, I can't get it to work in the pivot.

Here is what I have
data table:

requestID dateClosed              closedby
--------- ----------------------- -----------
1         2016-06-12 10:34:09.000 DOMAIN\Joe
2         2016-06-12 23:35:09.000 DOMAIN\Bill
3         2016-06-12 08:02:09.000 DOMAIN\Matt
4         2016-06-13 21:46:09.000 DOMAIN\Joe
5         2016-06-13 17:34:09.000 DOMAIN\Bill
6         2016-06-14 16:39:09.000 DOMAIN\Matt
7         2016-06-14 12:38:09.000 DOMAIN\Joe
8         2016-06-14 12:37:09.000 DOMAIN\Bill
9         2016-06-15 11:36:09.000 DOMAIN\Matt
10        2016-06-16 10:35:09.000 DOMAIN\Joe
11        2016-06-13 10:35:09.000 DOMAIN\Bill
12        2016-06-15 21:46:09.000 DOMAIN\Joe
13        2016-06-15 21:47:09.000 DOMAIN\Joe
14        2016-06-15 21:48:09.000 DOMAIN\Joe

Here is my ouput after PIVOT

date                    DOMAIN\Bill DOMAIN\Joe  DOMAIN\Matt
----------------------- ----------- ----------- -----------
2016-06-12 00:00:00.000 1           1           1
2016-06-13 00:00:00.000 2           1           0
2016-06-14 00:00:00.000 1           1           1
2016-06-15 00:00:00.000 0           3           1
2016-06-16 00:00:00.000 0           1           0

I need to remove the "Domain\" portion from the column alias. Replace() didn't work for me. SQl v2012

Here is my query

--dynamic pivot
declare @cols as nvarchar(max),
    @query as nvarchar(max)

set @colS = STUFF((SELECT DISTINCT ',' + QUOTENAME(r.closedby)
    from MYRequests r
    where r.dateclosed > getdate()-30
    for XML path(''), type
    ).value('.','NVARCHAR(MAX)'),1,1,'')

SET @QUERY = 'select * from (
    select
    DateAdd(Day, DateDiff(Day, 0, dateclosed), 0) as [date],
    closedby
    from MyRequests
    where DateAdd(Day, DateDiff(Day, 0, dateclosed), 0) > getdate()-30
    )
    as s
PIVOT
    (count(closedby) for [closedby] in
    ('+ @cols + '))
    as PVT '

execute(@query)

Here is sample filler data:

create table MYRequests
    ([requestID] smallint,
    [dateClosed] datetime,
    [closedby] varchar(50));

Insert into MyRequests
    ([requestID], [dateclosed], [closedby])
values
    (1,'20160612 10:34:09 AM','DOMAIN\Joe'),
    (2,'20160612 11:35:09 PM','DOMAIN\Bill'),
    (3,'20160612 08:02:09 AM','DOMAIN\Matt'),
    (4,'20160613 09:46:09 PM','DOMAIN\Joe'),
    (5,'20160613 05:34:09 PM','DOMAIN\Bill'),
    (6,'20160614 04:39:09 PM','DOMAIN\Matt'),
    (7,'20160614 12:38:09 PM','DOMAIN\Joe'),
    (8,'20160614 12:37:09 PM','DOMAIN\Bill'),
    (9,'20160615 11:36:09 AM','DOMAIN\Matt'),
    (10,'20160616 10:35:09 AM','DOMAIN\Joe'),
    (11,'20160613 10:35:09 AM','DOMAIN\Bill'),
    (12,'20160615 09:46:09 PM','DOMAIN\Joe'),
    (13,'20160615 09:47:09 PM','DOMAIN\Joe'),
    (14,'20160615 09:48:09 PM','DOMAIN\Joe')

    select * from myrequests

Please help.

Here you go, using a CTE to do the replace.  I used a global temp table instead, but the sample works.

CREATE TABLE ##MYRequests
    (
      [requestID] SMALLINT
    , [dateClosed] DATETIME
    , [closedby] VARCHAR(50)
    );

INSERT  INTO ##MYRequests
        ( [requestID], [dateClosed], [closedby] )
VALUES  ( 1, '20160612 10:34:09 AM', 'DOMAIN\Joe' ),
        ( 2, '20160612 11:35:09 PM', 'DOMAIN\Bill' ),
        ( 3, '20160612 08:02:09 AM', 'DOMAIN\Matt' ),
        ( 4, '20160613 09:46:09 PM', 'DOMAIN\Joe' ),
        ( 5, '20160613 05:34:09 PM', 'DOMAIN\Bill' ),
        ( 6, '20160614 04:39:09 PM', 'DOMAIN\Matt' ),
        ( 7, '20160614 12:38:09 PM', 'DOMAIN\Joe' ),
        ( 8, '20160614 12:37:09 PM', 'DOMAIN\Bill' ),
        ( 9, '20160615 11:36:09 AM', 'DOMAIN\Matt' ),
        ( 10, '20160616 10:35:09 AM', 'DOMAIN\Joe' ),
        ( 11, '20160613 10:35:09 AM', 'DOMAIN\Bill' ),
        ( 12, '20160615 09:46:09 PM', 'DOMAIN\Joe' ),
        ( 13, '20160615 09:47:09 PM', 'DOMAIN\Joe' ),
        ( 14, '20160615 09:48:09 PM', 'DOMAIN\Joe' );

--dynamic pivot
DECLARE @cols AS NVARCHAR(MAX)
  , @query AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT DISTINCT
                            ',' + QUOTENAME(REPLACE(r.closedby, 'DOMAIN\', ''))
                   FROM     ##MYRequests r
                   WHERE    r.dateClosed > GETDATE() - 30
    FOR           XML PATH('')
                    , TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

SET @query = ';WITH CTE_Requests AS
(SELECT M.requestID, M.dateclosed, REPLACE(M.closedby, ' + QUOTENAME('DOMAIN\',
                                                              '''') + ', '
    + '''' + '''' + ') AS closedby
FROM ##MYRequests M)
SELECT * from (
    select
    DateAdd(Day, DateDiff(Day, 0, dateclosed), 0) as [date],
    closedby
    from CTE_Requests
    where DateAdd(Day, DateDiff(Day, 0, dateclosed), 0) > getdate()-30
    )
    as s
PIVOT
    (count(closedby) for [closedby] in
    (' + @cols + '))
    as PVT ';

execute(@query);


Is the hierarchyid CLR open-source? The built-in hierarchyid is a CLR that stores paths in an efficient binary form, and provides other useful functionality.

Unfortunately, there is limit to how deep the represented paths can be, and it's ~1427 for a binary tree. I would like to increase that limit for a complex existing application that is bound to hit this limit. I don't wish to change the interface of the type. I am not confident that I could pull off changing the interface of the type without introducing subtle bugs into all the code that would have to be changed as a result.

I could (in theory) create a "binhierarchyid" CLR UDT that implements the same interface as hierarchyid, but only supports binary trees. That should get me a depth of ~7000 while still remaining inside the 900 byte limit. Not sure how big of an undertaking that would be.

Is the source of this hierarchyid CLR available somewhere, so that I could create my own based on it that supports deeper structures?

A lot of the .NET source code is available, and much of it is Open Source, though under various licenses. Still other code is available, but only for reference purposes and cannot be used in your code.

Given that the stated goal is:

  I would like to increase that limit for a complex existing application that is bound to hit this limit. I don't wish to change the interface of the type. I am not confident that I could pull off changing the interface of the type without introducing subtle bugs into all the code that would have to be changed as a result.

if the intention is to update the current Hierarchy type, then whether or not the code is Open Source is irrelevant because even if it is, you would never be able to introduce any changes into the overall system: even if you could replace the Assembly in SQL Server, you wouldn't be able to sign it with the same Certificate / Private Key and hence it wouldn't be used by the system.

However, you could build your own SQLCLR User-Defined Type (UDT) to provide the desired functionality.

Still, as it relates to gaining a better understanding of the .NET Framework:

You can view the source code of most stuff here:  https://github.com/Microsoft/referencesource

and even more stuff here: https://github.com/dotnet/corefx

Both of the repositories noted above are Open Source and governed mostly under the MIT license, though you need to review the licensing info in each location, and sometimes in various folders, to be certain.

A lot of the remaining code can be found here: http://referencesource.microsoft.com/

The code found at ReferenceSource.Microsoft.com (the link directly above) that is not also found at the first two links (which are Open Source) is not Open Source and is instead governed by the MICROSOFT REFERENCE SOURCE LICENSE (MS-RSL), the relevant portion being (lightly edited to put the definition of "reference use" just following the usage of that phrase):

  the Licensor grants you a non-transferable, non-exclusive, worldwide, royalty-free copyright license to reproduce the software for reference use: use of the software within your company as a reference, in read only form, for the sole purposes of debugging your products, maintaining your products, or enhancing the interoperability of your products with the software, and specifically excludes the right to distribute the software outside of your company.

However, it appears that the Microsoft.SqlServer.* namespace is not available in any of those locations. Which means that the simple answer to the question of: "is HierarchyID Open Source?" is: No.

If the desire is to see the source code, then you would need to open the DLL -- Microsoft.SqlServer.Types.dll -- with a decompiler. BUT, there are potential legal and/or ethical issues to deal with. This is proprietary code so there are two main points to consider:

as stated in the SQL Server End-User License Agreement (EULA) (and as quoted in @TomV's answer), you can only "reverse engineer, decompile or disassemble" non-provided code "only to the extent that applicable law expressly permits". Meaning that for some folks, it is perfectly legal to do this, but for other folks, not so much. So you might want to consult a local intellectual property lawyer.
if it is legal for you to decompile, the intent of the legal language, plus the fact that some source code is provided (as linked at the top) but this particular code is not, certainly implies that the desire is for the general public to not see or have this code. So being legal to decompile shouldn't be taken as it also being legal to duplicate, but more so that it can be used as a reference (i.e. for debugging, etc).


Restoring .trn files on a regular basis to a backed-up database We have been given a .bak file of a database from our vendor on a completely different network. They are placing their .trn files every 15 minutes on a sftp for us to pickup and write to the .bak file they sent us. This way we will have data up-to-date every 15 minutes or so.

What I have done is restored their backup WITH NORECOVERY. Then I restored all 800 .trn files WITH NORECOVERY, setting the last restore to WITH RECOVERY. Now that it is out of NO RECOVERY we can read from the DB.

Now that we are up-to-date, what is the process for restoring the new .trn files that they send us?

I apologize for the ignorance, I am new to the DBA game.

You should only issue WITH RECOVERY when it's the last backup to restore or when you are sure that is the point where you want to go. After that there's no way to restore any more backups.

Now you will need to restore everything again to keep the same log chain.

If you need to "open" the db without break the log chain, you must use WITH STANDBY = 'path/file'. This will open the DB in READ ONLY mode and when you need to restore more files, SQL Server will use the 'path/file' you create to allow the DB to continue restore the log chain.

You may take a look to this option here:
https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-arguments-transact-sql?view=sql-server-2017


Join Tables without losing Data I am in a situation where I need to query a database for data, but am struggling on how to properly form the query.  Essentially I need to get data from the following tables:

Table: Contact
    contact_id INT [Primary Key]
    first_name VARCHAR(50)
    last_name  VARCHAR(50)

Table: Contact_Phone
    contact_id   INT [Foreign Key/Composite Primary Key]
    phone_number VARCHAR(15) [Composite Primary Key]
    extension    VARCHAR(10), NULL

Table: Contact_Fax
    contact_id   INT [Foreign Key/Composite Primary Key]
    fax_number   VARCHAR(15) [Composite Primary Key]

Table: Contact_Email
    contact_id   INT [Foreign Key/Composite Primary Key]
    email        VARCHAR(100) [Composite Primary Key]

Now not every single contact in my Contacts table has a Phone, Fax, or Email.  Some just have a phone or fax.  Others just an email.  There is one contact I know only has a phone number.

Now my issue is, if I try and make a simple query joining all of these tables look for specific contact information, if for any one of them there is no entry (for example, my one contact which doesn't have a fax or email) then I get no data being returned at all.

So the question is, how do I put this query together so that if I am trying to look up a specific contact's information, I will still be able to see all of the data (Phone/Fax/Email) even if one of them happens to be blank?

Use an outer join:

SELECT c.first_name, c.last_name, p.phone_number, p.extension, f.fax_number, e.email
FROM Contact c
LEFT OUTER JOIN Contact_Phone p ON c.ContactID = p.ContactID
LEFT OUTER JOIN Contact_Fax   f ON c.ContactID = f.ContactID
LEFT OUTER JOIN Contact_Email e ON c.ContactID = e.ContactID

Any missing information will show up as NULL.

This assumes that all contacts will at the very least have a master record in Contact.


High availability SharePoint farm with restricted conditions Currently, our SharePoint farm has restricted conditions as below

Shared folder is not allowed
FTP/SFTP is not allowed
SQL FILESTREAM is not allowed
SMB protocol is not allowed
Using Windows Server Failover Clustering (Not SQL AlwaysOn FCI) for SharePoint database servers

I want to setup a DR farm for the existing production.
First, we tried with log shipping but it turned out that log shipping does not co-exist with Failover Clustering, as stated here
https://msdn.microsoft.com/en-us/library/bb500117(v=sql.105).aspx

Then, I came up with idea of using transactional database replication, but still, have considerations that couldn't find elsewhere

Does Transactional Replication require shared folder? (For example, to copy log file)?
Can Transactional Replication co-exist with Failover Clustering?

Please propose if there's any possible Disaster Recovery solution for this requirements

We don't use Failover Cluster at instance level but server level

Note: This part of the answer corrects the misconception about Log Shipping not being compatible with Failover Clustering.

As @sp_BlitzErik implies in the comments, Log Shipping is very much compatible with Failover Clustering.  I've seen others fall prey to this implied lack of interoperability as it seems the MSDN article you link to is a direct reference of this MS Blog post from 2013.  It seems even @Shanky commented about this discrepancy directly on that article as well.

Long story short, that article is incomplete.  It should also reference Failover Clustering under Log Shipping along with a handful of other DR technologies.

Final note on using log shipping with Failover Clusters; the main caveat is to ensure your TLogs are being backed up to a clustered share that can failover along with the instance and other clustered services.

Answer: Now to address the rest of the answer.

You've got a few options.  Really what you're able to do is often limited by your budget (e.g. licensing, hardware, etc.), so any option proposed will need to fall within your budgetary constraints.  That being said, here some options I can think of, but by no means do I consider this a complete list of your available options:

Log Shipping: For the sake of completeness, you CAN configure log shipping on a failover cluster.  As I say above, be sure to configure the backup location as a shared folder that fails over along with the other clustered resources (e.g. SQL Server Service, SQL Agent, etc.).
Multi-Subnet Failover Cluster: This is adding onto what it sounds like you already have, but you can include a different site into your failover cluster.  Proper care will need to be taken so that automatic failover to your DR site is only done when necessary.  Additional constraints are replicating the data to the DR site.  Configuration of this approach is not simple and will depend a great deal on your Windows Version(s), SQL Server version, budget, etc.
SAN-to-SAN Replication: This is generally a hardware based approach and will depend upon your SAN vendor.  Most Enterprise-level SAN solutions support SAN-to-SAN replication and in conjunction with virtualization/etc. you can run across some elegant and expedient DR solutions.

Disaster Recovery is a complex topic and providing anything resembling a complete answer can't be provided on any forum answer.  If you're new to this or feel overwhelmed, I heavily suggest reaching out to MS, a MS Partner, a Consulting Firm/Consultant, etc. and ask for help.


Database schema - performance v correctness information I have a database that stores information about the users of my website (username, password, etc). I also store information about their activity on the site.

For example, I have a large number of questions on my site. I have a few topics, and each topic has a number of questions.

Whenever a user answers a question, I store the result in a table containing the tuples: (user_id, question_id, timesAnswered, timesCorrect)

I also have a table called topics_and_questions containing (question_id(PK), topic_id(PK)) which can be used to determine which topic(s) the question belongs to.

Based on the above, it is possible to determine how many questions a user has answered on a particular topic. However, this would involve searching through the first table for all question_ids answered by the user, then searching the topics_and_questions table. (The first table could get rather large)

Does it make sense to instead create another table users_and_topics which contains (user_ID, topic_id, questionsAnswered) and update this whenever a question is answered? It would seem as though this would be bad database practise as data would essentially be replicated (this table can be derived from current tables). However, it could provide performance benefits as it would mean less work for the server. I would expect around 1 request for this information every 5 minutes per user of my website.

I'm using innoDB, MySQL database if it matters

This is a "materialized view" and triggers are used often to implement them. It is duplication of data, and an opportunity for data inconsistency, but if needed and well implemented, they can improve performance a lot.

But I don't see what are you asking.


Count items on condition In an application where a user can have copies of a mobile application installed on multiple devices I'd like to know how many iphone and how many android apps each user has.

I tried a query such as

gv2=> SELECT userid,
       COUNT(ALL version_identifier LIKE '%ios%'),
       COUNT(ALL version_identifier LIKE '%android%')
FROM gl.user_device
GROUP BY userid;
                userid                | count | count
--------------------------------------+-------+-------
 46d0f5b7-42b0-4aad-9162-1390c32cb06e |     7 |     7
 5d519794-abfe-4863-82d4-6da33db7637b |     7 |     7
 a81cff6b-30f2-4b6e-a5bf-b1a933904473 |     1 |     1
 b65f0708-0cd1-11e7-878b-06fa189da46b |     4 |     4
 94b91b02-ff43-4a9a-b317-037fa2a347d3 |     1 |     1
 a4cacd98-1216-4801-b058-b28b8fa632a9 |     8 |     8
(6 rows)

Which is clearly wrong.

For example

gv2=> SELECT userid FROM gl.user_device WHERE version_identifier LIKE '%ios%';
                userid
--------------------------------------
 5d519794-abfe-4863-82d4-6da33db7637b
(1 row)

In case it matters I need to support Postgres 10.1 but preferably also 9.5.10 as I have one old Production DB still on that version.

Use the modern aggregate FILTER syntax in Postgres 9.4 or later:

SELECT userid,
       COUNT(*) FILTER (WHERE version_identifier LIKE '%ios%') AS nr_ios,
       COUNT(*) FILTER (WHERE version_identifier LIKE '%android%') AS nr_android
FROM   gl.user_device
GROUP  BY userid;

It's shorter, clearer and a bit faster. See:

For absolute performance, is SUM faster or COUNT?

But it still results in a sequential scan on the whole table.

If ...

the table is big and performance is important,
a substantial percentage of rows does not contribute to either count,
and you have a matching index - a trigram index on version_identifier in your case, see:

Full Text Search With PostgreSQL

... then run two separate queries instead to tap the full potential of the index. Example with two subqueries in a FULL OUTER JOIN to make it a 100% equivalent drop-in replacement:

SELECT userid
     , COALESCE(nr_ios    , 0) AS nr_ios
     , COALESCE(nr_android, 0) AS nr_android
FROM  (
   SELECT userid, COUNT(*) AS nr_ios
   FROM   gl.user_device
   WHERE  version_identifier LIKE '%ios%'
   GROUP  BY 1
   ) i
FULL JOIN (
   SELECT userid, COUNT(*) AS nr_android
   FROM   gl.user_device
   WHERE  version_identifier LIKE '%android%'
   GROUP  BY 1
   ) a USING (userid);

Related:

PostgreSQL merge two queries with COUNT and GROUP BY in each


What are the design alternatives when having multiple "type" tables I am working on a database design and see myself with lots of *_type tables (e.g. user_type, product_type, etc.) where the structure of these tables is the same, basically:

user_type (
    id int pk
    label char
)

I could simplify this by doing something like:

labels (
    id int pk
    label char
    context blah
)

but is this an appropriate way to do this?

Please don't combine the various _type tables.  Your future self (and anyone that ends up writing queries against the tables) will thank you.

If you combine the tables, you give up the ability to have referential integrity constraints ensure that your tables have valid data.  Inevitably, someone will inadvertently insert a row that doesn't reference a valid row in your lookup table.  Then, when you to run a report, you'll find that you have customers in all 61 states with folks in interesting states like 'NA' and 'X'.
If you combine the tables, you make it much harder for future developers to write queries.

I would much rather write something like

SELECT p.product_name,
       pt.product_type_name,
       ct.color_name
  FROM product p
       JOIN product_type pt ON (p.product_id = pt.product_id)
       JOIN color_type   ct ON (p.color_id   = ct.color_id)

than

SELECT p.product_name,
       pt.label product_type_name,
       ct.label color_name
  FROM product p
       JOIN (SELECT *
               FROM labels
              WHERE context = 'PRODUCT_TYPE') pt ON (p.product_id = pt.id)
       JOIN (SELECT *
               FROM labels
              WHERE context = 'COLOR_TYPE') ct ON (p.color_id = ct.id)

If you have separate tables, the optimizer is much more likely to be able to make good decisions.  You'll inevitably have some lookup tables that have half a dozen rows and others that have hundreds of rows.  If you have separate tables, it's relatively easy for the optimizer to figure out which lookups are more restrictive and which are less restrictive.  If you throw all the _type tables together, the database will tend to have much less information to work with so it will be much less likely to pick the most efficient plans.

Beyond that, I completely second @BillThor's suggestions for how to create the various lookup tables.


SQL Database structure to store calculations I am in the process of designing a database structure to store some calculation information.

Information about the problem:

Each "calculation" is a collection of scripts allowing to calculate value, target, low limit and high limit. All of those are optional.
A calculation script is a string, that will be parsed by the client app. Example: 50 + {OutsideTemperature}

I have tried to follow the principles of normalization, as I understood those, and I came up with:

CREATE TABLE CalculationScript(
ScriptId int NOT NULL PRIMARY KEY,
Script VARCHAR(8000) NOT NULL
);

CREATE TABLE Calculation(
CalculationId int NOT NULL PRIMARY KEY,
Name nvarchar(100) NOT NULL
);

CREATE TABLE ValueScript(
CalculationId int FOREIGN KEY REFERENCES Calculation(CalculationId),
ScriptId int FOREIGN KEY REFERENCES CalculationScript(ScriptId)
);

CREATE TABLE TargetScript(
CalculationId int FOREIGN KEY REFERENCES Calculation(CalculationId),
ScriptId int FOREIGN KEY REFERENCES CalculationScript(ScriptId)
);

CREATE TABLE LowLimitScript(
CalculationId int FOREIGN KEY REFERENCES Calculation(CalculationId),
ScriptId int FOREIGN KEY REFERENCES CalculationScript(ScriptId)
);

CREATE TABLE HighLimitScript(
CalculationId int FOREIGN KEY REFERENCES Calculation(CalculationId),
ScriptId int FOREIGN KEY REFERENCES CalculationScript(ScriptId)
);

The table will be used as a configuration table. The client app will query the calculation, parse the scripts and run the calculation. For example, the calculation:

Name: testCalc
Value: 50 + {OutsideTemperature}/2
LowLimit: 10 - 5^({OutsideTemperature})

Will yield, at runtime, if it is 0°C outside

Name: testCalc
Value: 50
LowLimit: 9

And if it is 5°C outside:

Name: testCalc
Value: 52.5
LowLimit: -3115

Before moving forward with it, I would like some feedback on this structure, and in particular, is it overkill to create so many tables for such a simple problem?

As there is 1-1 relations all across the chain, having one table would obviously be easier, but I am worried about having a mostly empty table full of varchar(8000) fields.

I would just have a single table to store the relationships, just add a ScriptType and then you have the classic Entity Attribute Value table.  Properly indexed (an example is below) the performance should be pretty good.  Then adding additional types is straightforward.  Not sure if it makes more sense for you to have the ScriptType attribute as a component of the CalculationScript table or not, but I would definitely go with a single table.

CREATE TABLE CalculationScript(
ScriptId int NOT NULL PRIMARY KEY,
Script VARCHAR(8000) NOT NULL
);

CREATE TABLE Calculation(
CalculationId int NOT NULL PRIMARY KEY,
Name nvarchar(100) NOT NULL
);

CREATE TABLE ScriptType(
ScriptTypeID INT NOT NULL PRIMARY KEY,
[Name] NVARCHAR(100) NOT NULL
);

CREATE TABLE Script_EAV(
Script_EAVID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
CalculationId int FOREIGN KEY REFERENCES Calculation(CalculationId),
ScriptTypeID INT FOREIGN KEY REFERENCES ScriptType(ScriptTypeID),
ScriptId int FOREIGN KEY REFERENCES CalculationScript(ScriptId)
)

CREATE NONCLUSTERED INDEX IDX_Script_EAV_CalclationID_ScriptTypeID ON dbo.Script_EAV
    (CalculationID, ScriptTypeID)
    WITH (FILLFACTOR=70, SORT_IN_TEMPDB=ON)


How do I compare 1000 items every minute over 1 million records in mongodb without pulling all the data? I am developing a real time stock price alert system that gets new prices every 1 min. There are 1000 tradable assets from one exchange currently and I plan to add other exchanges soon and 1 million records are stored in the database currently. A user is able to add alerts on any of the assets and is to be notified in real time (every minute)

The user would add alerts of type "Alert me if XYZ reaches 100$" I am using redis to store the current prices and mongodb to store the alerts.If the current price of the asset is 90$, this alert is considered to be a "greater than" alert else it is a "less than" alert. This attribute is represented by direction in the database. Here's what a single alert in the database looks like...

{
    "_id" : //alert id,
    "2" : //user id,
    "3" : e1:XYZ:1, //exchange: asset: direction
    "4" : 100,
    "5" : 0
}

I am indexing this database over the field "3". After 1 min, when I fetch all the stock prices, I want to be able to find all the "greater than" alerts that are below the current price indicating that the price has risen and all the "lesser" alerts that are above the current price indicating that the price has fallen.

Approach 1

I dynamically build a query in mongodb that is of the form

db.getCollections("alerts").find({
$or: [
{3: "e1:XYZ:0", 4: {$gt: 100}},
{3: "e1:XYZ:1", 4: {$lt: 100}},
...2000 more assets here
]
})

The process above is limited by the 16 MB document size restriction applicable while querying Mongodb

Approach 2

I found the above approach was very slow and therefore decided to get all the alerts stored in the database and do the comparisons on my node.js server which takes rougly 2.5 seconds per round to fetch data currently

Right way to do 1000 comparisons like this (server side or mongodb side)?
Can I further reduce the amount of data being sent from the database
to server in any way?

Thanks

If you add "_id" to your index {"3":1, "_id":1 } and then projection of {"_id":1, "3":1} to query, you get result directly from index in the memory (if you have enough memory to hold index in the memory).

Adition, you could use in-memory database engine.


Query performance low for aggregate function I perform a  SUM query on a MySQL table of mine which takes around 1.5 seconds to complete. The table will have around 200000 rows at any time. I have to run this query every 5 seconds from my web app and because of that my DB CPU usage is shooting up to around 50%.

This is my query

select ((SUM(active_energy) *0.00277)/1000) as kwh
from energy_readings
WHERE  unitid = 2000

I am using an Amazon RDS instance (db.m4.large). Is there an alternate way of writing this query with low server load?

My table structure is like this

Columns:

Id bigint(20) AI PK
unitid varchar(16)
date_time timestamp
active_energy float
power_factor float

CREATE TABLE energy_readings (
 Id bigint(20) NOT NULL AUTO_INCREMENT,
 unitid varchar(16) DEFAULT NULL,
 date_time timestamp NULL DEFAULT NULL,
 active_energy float DEFAULT NULL,
 power_factor float DEFAULT NULL,
 PRIMARY KEY (Id)
) ENGINE=InnoDB AUTO_INCREMENT=6114324 DEFAULT CHARSET=latin1

I use this code to fetch the values. This works with string unitids as well; Should I be putting quotes around it?

$stmt = $db->prepare("
    select ((SUM(active_energy) *0.00277)/1000) as kwh
    from energy_readings
    WHERE unitid=?
");
$stmt->execute([$_SESSION["plug_id"]]);

Let's see the entire SHOW CREATE TABLE; we need to see that you are missing INDEX(unitid).

Then, when it still does not run any faster, we need to discuss whether unitid is really a string, in which case you should quote '2000' or is really a number, in which case VARCHAR is the wrong declaration.


postgresql: hindi encode and decode error select encode('नमस्त', 'base64');

        encode
──────────────────────
 4KSo4KSu4KS44KWN4KSk

select decode('4KSo4KSu4KS44KWN4KSk','base64');

              decode
──────────────────────────────────
 \xe0a4a8e0a4aee0a4b8e0a58de0a4a4

Why does not this return the Hindi text?

collate: en_US.UTF-8
Ctype: en_US.UTF-8

The documentation tells you what exactly decode() returns:

Function                          Return Type   Description
-------------------------------------------------------------------------------
decode(string text, format text)  bytea         Decode binary data from textual
                                                representation in string.

'\xe0a4a8e0a4aee0a4b8e0a58de0a4a4' is the bytea output.  You need an additional call of convert_from() to turn this back to text:

SELECT convert_from(decode(encode('नमस्त', 'base64'), 'base64'), 'utf8');
 convert_from
──────────────
 नमस्त


Display line number where error occurred in DB2 stored procedure I have added an EXIT handler in my procedure that captures the SQLSTATE and SQLCODE, and even found a way to get the procedure name, but I also need to know where the error occurred.  Suggestions greatly appreciated.
declare EXIT handler for SQLEXCEPTION
begin
    select sysibm.routine_specific_name, SQLSTATE, SQLCODE
    into v_sp_name, v_sqlstate, v_sqlcode
    from sysibm.sysdummy1;

    call dbms_output.put_line('Error in '||v_sp_name ' ['||v_sqlstate, v_sqlcode||']');
end;

If you are on a currently supported version of Db2 for LUW (11.1 or 11.5) you can use either DBMS_UTILITY.FORMAT_CALL_STACK()  or DBMS_UTILITY.FORMAT_ERROR_BACKTRACE() in your signal handler to log the troubleshooting information.


Understanding block sizes My question targets Postgres, but answers might just be good enough coming from any database background.

Are my assumptions correct:

Disks have a fixed block size?
RAID controller can have a differnt block size? Does one RAID block get split onto multiple real disk blocks?
The filesystem also has an independant block size which again gets split onto the RAID block size?
Postgres works with fixed 8k blocks. How does the mapping to the filesystem block size happen here? Are Postgres 8k blocks batched together by the filesystem?

When setting up a system is it best to have all blocks at 8k? Or do the settings not real matter? I was also wondering if some "wrong" block size settings could endanger data integrity in case of a crash? Maybe if a Postgres 8k block has to be split onto multiple disk blocks?

Or does nothing get batched together, and therefore I loose disk space with every mismatch between defined block sizes?

Disk Sectors

A disk has a fixed sector size, normally 512 bytes or 4096 bytes on some modern disks; these disks will also have a mode where they emulate 512 byte sectors.  The disk will have tracks with varying numbers of sectors; tracks closer to the outside of the disk have more sectors as they have more room for a given bit density.  This allows more efficient usage of the disk space; typically a track will have something like 1,000 512 byte sectors on a modern disk.

Some formatting structures can also include error correcting information in the secotrs, which manifests itself in the disks being low-level formatted with 520 or 528 byte sectors.  In this case the sector still has 512 bytes of user data.  Neither Windows nor Linux support this directly, although i5OS (IBM iSeries) and various SAN controllers do.

Normally the sector/head/track is translated into a logical block address; due to historical issues with backward compatibility the geometry (heads x sectors x tracks) seen by the operating system (particularly on IDE and SATA disks) normally has little to do with its physical structure.

RAID stripe Size

A RAID controller can have a stripe size for an array using striping (e.g. RAID-5 or RAID-10).  If the array has (for exmaple) a 128k stripe, each disk has 128k of contiguous data, and then the next set of data is on the next disk.  Normally you can expect to get approximately one stripe per revolution of the disk, so the stripe size may affect performance on certain workloads.

Partition Alignment

A disk partition may or may not align exactly with a RAID stripe, and can cause performance degradation due to split reads if it is not aligned.  Some systems (e.g. Windows 2008 server) will automatically configure partitions to align with disk volume stripe sizes.  Some (e.g. Windows 2003 server) will not, and you have to use a partition utility that does support stripe alignment to ensure they do.

File System Block Size

The file system will allocate blocks of storage in chunks of a certain size.  Generally this is configurable - for example NTFS will support allocation units from (IIRC) 4K to 64K.  Misalignment of partitions and file system blocks to RAID stripes can cause a single filesystem block read to generate multiple disk accesses where only one would be necessary if the file system blocks aligned correctly with the RAID stripes.

Database Block Size

The database will allocate space in a table or index in some given block size.  In the case of SQL Server this is 8K, and 8K is the default on many systems.  On some systems such as Oracle, this is configurable, and on PostgreSQL it is a build-time option.  On most systems space allocation to tables is normally done in larger chunks, with blocks allocated within those chunks.

Misalignment of filesystem and data allocation blocks can generate multiple I/Os for a single block write, which can drive a performance penalty.

I/O Chunking

Normally a DBMS will actually do its I/O in chunks of more than one block.  For example, on SQL Server, all I/O is done in chunks of 8 blocks, 64k in total).  On Oracle this is configurable.  Casual inspection of the PostgreSQL docs doesn't reveal a specific description of whether PostgreSQL does this, so I'm not sure how it works on this platform.

When the I/O chunk larger than the file system block size or is misaligned with RAID stripe boundaries a disk write from the DB can cause multiple disk writes, which generates a performance penalty.

Disk space usage

No disk space is wasted - the database I/O will use one or more physical I/O operations on the disk to complete - but incorrectly tuned I/O can generate inefficiencies which will slow down the database.  The main things that have to be in alignment are:

RAID stripes and partitions - the partition should begin on a RAID stripe boundary.
Filesystem I/O allocation and raid stripe/partition boundaries - a RAID stripe boundary must align with a filesystem allocation unit, and should be a multiple of the filesystem allocation unit size.
Disk write size and filesystem allocation unit size.  There should be a 1:1 relationship between database I/O operations and filesystem I/O operations.

Misalignment does not create a greater data integrity problem than would otherwise be present.  The database and file system have mechanisms in place to ensure file system opearations are atomic.  Generally a disk crash will result in data loss but not data integrity issues.


Insert New Foreign Key Row for new Strings Lets say I have a table new_data like this:

ndx    | someval | somemachine
serial | integer | integer
----------------------------

And let's say I have a reference table new_data_someval_ref:

someval | someval_str
integer | varchar
---------------------

(Where, obviously, new_data_someval_ref.someval is foreign key on new_data.someval)

And I want do inserts like so:

INSERT INTO new_data
  (someval, machine)
VALUES (
  (SELECT someval
   FROM new_data_someval_ref
   WHERE someval_str = ?),
  ?)

BUT, if there is no match in the reference table, I would like to add a row containing the new string and an incremented someval, then do the insert above.

So my question is there clean, possibly even constraint-based way to do this?

(I use both Postgres and SQL Server regularly, so an answer addressing both would be ideal, but not necessary.)

Assuming someval is actually an auto-generated column like a serial.

In Postgres this data-modifying CTE (writeable CTE) would do the job:

WITH sel AS (
   SELECT r.someval, i.someval_str, i.machine
   FROM  (SELECT ?, ?) AS i(someval_str, machine)
   LEFT   JOIN new_data_someval_ref r USING (someval_str)
   )
, ins AS (
   INSERT INTO new_data_someval_ref (someval_str)
   SELECT DISTINCT someval_str FROM sel WHERE someval IS NULL
   RETURNING someval, someval_str
   )
INSERT INTO new_data (someval, machine)
SELECT COALESCE(sel.someval, ins.someval), sel.machine
FROM   sel
LEFT   JOIN ins USING (someval_str);

Everything standard SQL, not sure whether it's all implemented in SQL Server identically.

For a query with multiple input rows and ample explanation:

How do I insert a row which contains a foreign key?

There is a tiny chance for a race condition under heavy concurrent load. (Doing everything in a single statement minimizes the time window.) If that's the case:

Is SELECT or INSERT in a function prone to race conditions?


Tab color for registered servers in Management Studio SSMS allows us to use custom color for each connection.

Does anyone know where this information is saved? I'd like to keep the color setup but I keep losing it.

edit

When I connect from object explorer, I can set the color. However, this seems to be different from the colors that I set in registered servers, and this I keep losing. (Indeed, I am on a roaming profile.)

I use registered servers panel only when I do multi server operation and try to be much more careful when I have that panel open, so that's why I also want to be able to use colors associated with the object explorer.

Thanks

After you have set up all of your registered servers the way you want them, you should be able to export the whole set under any server group by right-clicking the Local Server Groups node in View > Registered Servers and selecting Tasks > Export...

You can save individual servers or the whole lot. When you lose them, you can then import the file you backed up to reset them. I did not test (sorry, on the move) but this should save off all your servers, their credentials (which should be encrypted if present), and any personalized settings including custom colors.

(I suspect you are losing them because you are using a roaming profile or have some other AD/Group Policy perhaps resetting your profile.)

These settings are also stored in RegSrv.xml, which is going to be in a path something like:

  C:\Users\<you>\AppData\Roaming\Microsoft\Microsoft SQL Server\<version>\Tools\Shell\

You will see lines like this, which you can change on the fly. I believe they will require a restart of Management Studio though, and you'll have to map your desired RGB color with the int equivalent (there are many calculators available online). Carriage returns added by me:

<RegisteredServers:UseCustomConnectionColor type="boolean">
  true
</RegisteredServers:UseCustomConnectionColor>
<RegisteredServers:CustomConnectionColorArgb type="int">
  -16777216
</RegisteredServers:CustomConnectionColorArgb>

The point is more that you can back up that RegSvr.xml file to save all your settings somewhere, and restore them whenever this happens. But you should also look into why this file is getting reset or why your profile gets re-created (that may be a question your system administrator can answer even if you can't).

The custom connection settings you set in the Connect to Server dialog are stored separately; these are in a file called SqlStudio.bin, in the following path:

  C:\Users\<you>\AppData\Roaming\Microsoft\SQL Server Management Studio\<ver>

However, knowing where the file is will not really help you - the information about servers is largely not stored in a human readable format, and after connecting to a server with a custom color set, the change I saw when I performed a diff against the before and after files yielded nothing intelligible (and in fact I can't even confirm that that color change is saved there, except that when I open SSMS again it remembers it, like all the server credentials I've ever used). So:

You can't very easily hand-modify the SqlStudio.bin file to put your chosen colors back in there.
While it's possible you could back up your SqlStudio.bin file at a time when you've connected to your servers and SSMS currently remembers all of the settings, it would be missing anything else you've changed since you backed it up, so you might lose other things.

Therefore, I propose that it would just be much easier to just use registered servers, and stop manually setting a color when you connect, because with a moving profile you're constantly going to get SqlStudio.bin blown away (other things you can easily save off and import when that happens, e.g. fonts and colors).

With registered servers, you can keep a separate server group for the important servers where you want to be careful, and keep the other ones in a different group. And you can back them all up so it is easy to restore them on a different machine, when they get blown away, when your profile gets nuked again (or fixed), etc.


How to migrate data from Google AppEngine Datastore How to migrate data from Google AppEngine's Datastore to other database (not Datastore)?

There is appcfg.py download_data but it produces sqlite3 file with empty bulkloader_database_signature and useless result tables.

I think approcket can help you. It syncs data from GAE datastore to mysql. Is this what you ask for?


How do I hide sensitive information like plaintext passwords from the logs? I do not have access to a Postgres installation, so I cannot check.

I am a security guy, and I'm seeing plaintext passwords in the logs:

create user user1 with password 'PLAINTEXT PASSWORD'

How can the DBAs change or create their passwords without the password in the clear in the logs?

I've seen this, which states you can use an md5 hash of the password, but then the hash is also in the clear. Is there a better way?

It sounds like log_statement is set to all.

If you wish to prevent passwords from appearing in the logs while log_statement is set to a value that captures ALTER USER / ALTER ROLE then you'll want to override that when changing passwords. e.g.

BEGIN;
SET LOCAL log_statement = 'none';
ALTER USER ... SET PASSWORD ...;
COMMIT;

You must be a superuser to do this. Normal users cannot override logging rules.

It would be nice if PostgreSQL supported flagging some parameters to statements (or even functions) as security-sensitive and allowed users to request that they be masked in logs, pg_stat_statements, pg_stat_activity, etc. There is not currently any such feature - but hey, patches are welcome. If you're genuinely interested, post on pgsql-hackers before writing any actual code so you can get advice and comments, though. Alternately, speak to someone who does contract development.

In general PostgreSQL expects you to treat the logs as sensitive.

There are other areas where logging is a serious security concern. For example some of the pgcrypto functions take crypto keys as parameters.


Benchmarks for custom database I am looking for an open source benchmark tool that can easily be extended to custom databases. The reason is I have created a new NoSQL DBMS (called Borrego) that support both JSON queries like MongoDB and DB2 JSON, and generic SQL statements via a JDBC driver. I would like to run some performance tests that I can compare with other existing databases.
Any pointers would be helpful.

Thanks

I found YCSB, the Yahoo Cloud System Benchmark:
https://github.com/brianfrankcooper/YCSB

It has bindings for many DBMSs, including jdbc and rest interfaces, so it seems to have exactly what I needed.

Thanks!


How to change SQL Server collation How could I change the SQL Server 2008 R2 Express Default Collation for the whole server and a particular database?

Is there a way to do it using visual interface of SQL Server Management Studio? In the Server Properties window (and in the corresponding Database Properties window), this property is not available for editing.

Yes.
You can change the default collation of SQL Server 2008 R2 express instance and individual databases, but it is a complex task.
Sadly, there is no visual option to do it via SSMS.

SQL Server 2008 supports setting collations at the following levels:

Server

Database

Column

Expression

The default installation settings are
determined by the Windows system
locale. The server-level collation can
either be changed during setup, or by
changing the Windows system locale
before installation. more...

Setting and Changing the Server Collation - SQL Server 2008

Make sure you have all the information or scripts needed to re-create your user databases and all the objects in them.

Export all your data using a tool such as the bcp Utility. For more information, see Importing and Exporting Bulk Data.

Drop all the user databases.

Rebuild the master database specifying the new collation in the SQLCOLLATION property of the setup command

Create all the databases and all the objects in them.

Import all your data.

Setting and Changing the Database Collation - SQL Server 2008

Set the COLLATION option in the CREATE DATABASE statement while creating a new database.

Similarly, set the COLLATION options in the ALTER DATABASE statement to change the collation of an existing database.
ALTER DATABASE [database_name] COLLATE SQL_Latin1_General_CP1_CI_AS;

Setting and Changing the Column Collation

Some of the column collations will remain the same even after you alter the database collation. In that case, you have to modify the collation of the individual columns.


MySQL : Conditional ORDER BY to only one column Hello and thanks for taking time to read this question.

I am using MySQL, and I want to sort results using ORDER BY to one specific column, but the results must be ordered according an specific criteria to this column. For example, to the following table, I want to ORDER BY 'group', showing first the 9,7,6 'group' items and, in the end 10,8,5 'group' items:

names     group
--------- ------
susanita  10
miguelito 5
mafalda   7
manolito  8
libertad  6
felipe    9
guille    8

thanks in advance.

SELECT* FROM mytable ORDER BY
LOCATE(CONCAT('.',`group`,'.'),'.9.7.6.10.8.5.');

I took your sample data, loaded it into a table called mytable and ran it.

Here are the results:

mysql> use test
Database changed
mysql> drop table if exists mytable;
Query OK, 0 rows affected (0.04 sec)

mysql> create table mytable
    -> (
    ->    names varchar(10),
    ->    `group` int
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> insert into mytable values
    -> ('susanita',  10),
    -> ('miguelito', 5),
    -> ('mafalda',   7),
    -> ('manolito',  8),
    -> ('libertad',  6),
    -> ('felipe',    9),
    -> ('guille',    8);
Query OK, 7 rows affected (0.09 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM mytable;
+-----------+-------+
| names     | group |
+-----------+-------+
| susanita  |    10 |
| miguelito |     5 |
| mafalda   |     7 |
| manolito  |     8 |
| libertad  |     6 |
| felipe    |     9 |
| guille    |     8 |
+-----------+-------+
7 rows in set (0.00 sec)

mysql> SELECT * FROM mytable ORDER BY
    -> LOCATE(CONCAT('.',`group`,'.'),'.9.7.6.10.8.5.');
+-----------+-------+
| names     | group |
+-----------+-------+
| felipe    |     9 |
| mafalda   |     7 |
| libertad  |     6 |
| susanita  |    10 |
| manolito  |     8 |
| guille    |     8 |
| miguelito |     5 |
+-----------+-------+
7 rows in set (0.01 sec)

mysql>

Give it a Try !!!

UPDATE 2011-09-06 12:33 EDT

Here is another approach:

SELECT* FROM mytable ORDER BY
IF(FIELD(`group`,9,7,6,10,8,5)=0,99999,FIELD(`group`,9,7,6,10,8,5));

This will force any groups other than 9,7,6,10,8,5 to appear at the very bottom of the query.

UPDATE 2011-09-06 14:39 EDT

mysql> SELECT names, `group`
    -> FROM mytable
    -> WHERE `group` IN (9,7,6,10,8,5)
    -> ORDER BY find_in_set(`group`,'9,7,6,10,8,5');
+-----------+-------+
| names     | group |
+-----------+-------+
| felipe    |     9 |
| mafalda   |     7 |
| libertad  |     6 |
| susanita  |    10 |
| manolito  |     8 |
| guille    |     8 |
| miguelito |     5 |
+-----------+-------+
7 rows in set (0.00 sec)

Hey @Nick, yours works as well against my sample data !!!


How often should MySQL (stock, Percona, etc.) be upgraded? My organization runs several data-driven web applications backed by various flavors of MySQL on RHEL (i.e. we're just like everyone else!)  Some of our web apps use the standard build of MySQL, and we use yum update periodically to update all yum-managed software on those systems.  Other webapps on other hosts use the Percona build, which we installed manually.  We use yum update periodically on those systems, too, but as our installation of Percona on those builds is not managed by yum, it isn't upgraded by that process.

Is there a rule of thumb for when to upgrade a component like MySQL (e.g. monthly, quarterly, or after each release)?  We want to find the right balance between staying up-to-date and spending too much time on administration.  What factors affect the decision?  Regarding security, we use hardware firewalls and iptables to restrict access, so MySQL/Percona is not exposed to the outermost attack surface.  The systems in question are dedicated, single-tenant hosts (i.e. there are no other users on the host).

Personally, I cringe when I hear about upgrades gone wrong due to human error. Most have success stories when upgrading MySQL in succession (5.0, 5.1, 5.5, 5.6) Some create their own disaster movies by moving datadir, uninstalling old MySQL, installing new MySQL, putting back the datadir and think everything's OK ... NOT !!!

MySQL Upgrade

MySQL created mysql_upgrade for an inventoried check of everything that could be different from the currently installed MySQL Binaries. When you used responsibly, this is the all-out solution. It is the only to go with very large database installations. There are certain rules you must follow. Although quite rare, even running mysql_upgrade right, strange things still can happen : See In place upgrade from MySQL 5.5 to 5.6.11 removes all users from user table and my answer to it.

Alternative

For smaller database installations, I have a much more simplistic approach: mysqldump (but with caveats). The most overlooked database in a MySQL Instance is always the mysql schema. People use mysqldump --all-databases and try to load the mysqldump from an older version into a newer version thinking that's all to it. Not so. Here is why:

With each major release of MySQL, table mysql.user has a different number of columns.

MySQL 5.6 has 43 columns
MySQL 5.5 has 42 columns
MySQL 5.1 has 39 columns
MySQL 5.0 has 37 columns
MySQL 4.x has 31 columns
See the actual columns in my post : Cannot GRANT privileges as root
See my most recent post in ServerFault about this : MySQL upgrade from 5.0 to 5.5

The script mysql_upgrade attempts to fix those columns among the other compatibility issues to adjust. My approach total removes concerns about compatibility issues.

My Approach

STEP 01: mysqldump all databases EXCEPT the mysql schema to a text file (/root/MySQLData.sql)

MYSQL_CONN="-uroot -ppassword"
SQLSTMT="SELECT GROUP_CONCAT(schema_name SEPARATOR ' ')"
SQLSTMT="${SQLSTMT} FROM information_schema.schemata WHERE schema_name"
SQLSTMT="${SQLSTMT} NOT IN ('information_schema','mysql')"
DBLIST=`mysql ${MYSQL_CONN} -ANe"${SQLSTMT}"`
MYSQLDUMP_OPTIONS="--single-transaction --routines --triggers --databases ${DBLIST}"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} > /root/MySQLData.sql

STEP 02: Dump the MySQL Grants as an SQL file

There are two techniques to do that

TECHNIQUE #1: Use mk-show-grants

This dumps out all the MySQL Grants as SQL statements, which is completely portable to any version MySQL/Percona 5.x

TECHNIQUE #2: Emulate mk-show-grants

cd
MYSQL_CONN="-uroot -ppassword"
SQLSTMT="SELECT CONCAT('SHOW GRANTS FOR ',"
SQLSTMT="${SQLSTMT} QUOTE(user),'@',QUOTE(host),';') "
SQLSTMT="${SQLSTMT} FROM mysql.user WHERE user<>''"
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" > GetGrants.sql
echo "SET sql_log_bin = 0;" > MySQLUserGrants.sql
mysql ${MYSQL_CONN} -AN < GetGrants.sql | sed 's/$/;/g' >> MySQLUserGrants.sql
rm -f GetGrants.sql

I have recommended these techniques before

Jul 26, 2011 : Restoring an old backup to latest MySQL release
Mar 24, 2013 : MySQL export user with semi-colon ";" on the end

STEP 03: Shutdown mysql

service mysql stop

STEP 04 : Get old datadir and my.cnf out of the way

mv /var/lib/mysql /var/lib/mysql_old
mv /etc/my.cnf /etc/my.cnf_old

STEP 05 : Uninstall Old MySQL/Percona Server

STEP 06 : Install New MySQL/Percona Server

STEP 07 : Login to MySQL

STEP 08 : Run the two scripts

mysql> source /root/MySQLData.sql
mysql> source /root/MySQLUserGrants.sql

That's it !!!

Don't worry, I have done this before : MySQL upgrade 5.0.88 to latest

I trust my alternative approach because a mysqldump is a logical representation of the data that can be moved from installation to installation with the except of storage engine specifics. If such is the case, I would mysqldump the data without the schema in one file and mysqldump the schema only in another file. Then, I would edit the specifics in the schema-only file.

I'll leave the gory details of software installation in your capable hands...

Give it a Try !!!

EPILOGUE

There are many old sayings

If it ain't broke, don't fix it.
Fortune Favors the Bold (From an Episode of Star Trek DS9)

In light of these saying, use common sense. If you are satisfied with MySQL's performance heretodate, upgrading would just be needless downtime. Stick with what you have. Don't just upgrade because you can. I have personally seen an upgrade to MySQL 5.5 which was slower than the original MySQL 5.1 database. here are my past answers on this phenomenon:

Nov 24, 2011 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)
Oct 05, 2011 : Query runs a long time in some newer MySQL versions
Jun 19, 2011 : How do I properly perform a MySQL bake-off?

If you want MySQL's new features, such as

InnoDB Multiple Core Engagement (MySQL 5.1's InnoDB Plugin, MySQL 5.5/5.6)
Semisynchronous Replication (MySQL 5.5)
Parallel Slave Threads (MySQL 5.6)

you will have to boldly go where no man has gone before.

Learn the technology you want in your database before engaging in upgrades.


Select all data from two tables with partial common data I have two tables which have 3 same column names and approx half of the records matching.

I want the output table to combine all the rows from both tables whether the rows match or not.

I Tried to run the Union in PL SQL as

SELECT a.First_Name,a.Last_Name, a.State, a.DOB, a.Age, b.Gender
From table1 a, table 2 b
where
a.First_Name || a.Last_Name || a.State = b.First_Name || b.Last_Name || b.State

UNION

SELECT a.First_Name,a.Last_Name, a.State, a.DOB, a.Age, 'NA' Gender
From table1 a
where
a.First_Name || a.Last_Name || a.State != b.First_Name || b.Last_Name || b.State

UNION

SELECT a.First_Name,a.Last_Name, a.State, 'NA' DOB, 'NA' Age, b.Gender
From table2 b
where
a.First_Name || a.Last_Name || a.State != b.First_Name || b.Last_Name || b.State

However, this returns redundant rows and not the expected data.
Could anyone help me with this.

Check out this db<>fiddle for an example. When running your example SELECT statement, it returned an error because the second and third SELECT in the UNIONs reference aliases from the other SELECT statements.

I'm not sure if this is is something you can do in PL/SQL (I have an MSSQL background), but the code in the fiddle returns the expected result set.


How do I copy a materialized view to a table? I have a materialized view. I want to create a copy of it so I can modify some of the data to test a few theories about an issue and its possible solutions.

How do I make a copy of this MV as a table? The copy will reside in the same schema but will have a different name. the copy should be an identical copy (structure and data) of the MV, it's just going to be a table so I edit the data.

It sounds like you just want a CREATE TABLE AS SELECT

CREATE TABLE your_new_table_name
AS
SELECT *
  FROM your_materialized_view

If there are indexes on your materialized view that you want to create on the table, you'd need to create those separately.


Is it possible: MySQL semisync and async replication on same master-slave pair? I want to use master-slave MySQL (or MariaDB) replication to remote server as data protection from hardware failures.
I have two goals: to keep low response time and don't lose important data.
Good solution would be an asynchronous replications for most of data to keep responsiveness of the system and semisynchronous replication for important tables (payments).

In best case, tables with sync/async replication should be in same database or on same mysql server.
But as far as I understand mysql will use semisynchronous replication for all data, if master and slave has semisynchronous replication enabled.

Not so good way is to have two mysql instances as masters (one with semisync enabled and one with disabled). Then I'll need either two slaves or multisource replication. Multisource replication is still in dev version of MySQL and doesn't support semisync in MariaDB.

The only probably working way I found for now based on https://dba.stackexchange.com/a/41491/55235
M1

DB1 (with async data)

M2

DB1 (BLACKHOLE, async slave of M1)
DB2 (with semisync data)

S1 (semisync slave of M2)

DB1
DB2

Is it possible to have semisync+async replication with one master-slave pair? If no,  is described above scheme a viable solution?

Semisync Replication will switch to async on the fly if needed.

I mentioned this before in my old post MySQL Replication : 1 Slave / Multiple Masters

In that post I said

  Having this setup gives you block-level replication within both data centers. If you are using MySQL 5.5, you can have Semisynchronous Replication send SQL to the other DataCenter without waiting for the SQL to be executed, only acknowledged. This keeps any intermittency that standard MySQL Asynchronous Replication would normally cause a MySQL Master to a minimum. In the event of any sudden network latency, MySQL Replication between DataCenters would switch from Semisync to Async. Once conditions improve, then MySQL Replication would it switch back to Semisynch.

In another old post of mine, With MySQL Replication, what level of resilience is possible?, I mentioned the option that shows that semisynch can switch to asynch and back:

  rpl_semi_sync_master_wait_no_slave : With semisynchronous replication, for each transaction, the master waits until timeout for acknowledgment of receipt from some semisynchronous slave. If no response occurs during this period, the master reverts to normal replication. This variable controls whether the master waits for the timeout to expire before reverting to normal replication even if the slave count drops to zero during the timeout period.

Therefore, you cannot setup semisync and async replication on the same instance of MySQL. Then again, you don't need to since it is designed to toggle between them depending on response time or lack of response time.

BTW to setup Semisync Replication, the semisync plugin activated on Master and Slave. See my post Is MySQL Replication Affected by a High-Latency Interconnect? on how to install Semisync Replication.


How to get the registers in the highest time of period I have 2 tables all with relations and I want to get the contracts in the highest time of period in order to do a historical report whether if the time of period is closed or active.
This is the code that I have or that I've been trying
select
  a.id
 ,a.ordinalperiod
 ,b.name
 ,a.fecin
 ,a.fecend
 ,b.dtend
 ,a.contrato
From
  historiccontrat a
Left join
  periodo b
    On a.id = b.id
      And a.ordinalperiod = b.ordinalperiod
Where
  a.fecend in
    (
      select
        max(b.dtend)
      where
        a.id = b.id
          and a.ordinalperiod = b.ordinalperiod
          and b.dtend <> '4000-01-01'
    )

The thing is that the employee have 5 period of times that works in the company like this
Id  |   período     |   dt_start    |   dt_end
000 |   1           |   2012-01-01  |   2012-04-01
000 |   2           |   2013-05-01  |   2016-04-03

And in each of these periods of time have different type of contracts.
What I want is that the just the contracts in the last period.
But the result with my query is the row with the data of the period 2 but not the contracts.
Thank for your help.

You are doing a left join that then includes a where clause that is filtering against the b table in the where clause.  I'm guessing you want to say OR b.id IS NULL or something to that affect.
To confirm, comment out the where clause.


mysql Quiz leaderboard filter by points, time taken I have a quiz report table which shows a report for every quiz a user takes. I need to create a leaderboard from this, which shows the top users best score, filtering by points and then time taken.

here is a link to a sql fiddle http://sqlfiddle.com/#!2/65fbf0/1

I am really struggling as i need to filter the results by two columns for one user, my ideal result would be

Results for Quiz id 1
---------------------------------------------------------------
| user_id  |  points  |  time_spend  |  start_dt  |  quiz_id  |
| 1        |  3       | 0.5          | May,15 2015|  1        |
| 2        |  3       | 0.8          | May,15 2015|  1        |
| 3        |  2       | 0.5          | May,15 2015|  1        |

Then a separate query for all quiz's showing the results from the last week.

Results from all Quizzs
---------------------------------------------------------------
| user_id  |  points  |  time_spend  |  start_dt  |  quiz_id  |
| 1        |  3       | 0.5          | May,15 2015|  1        |
| 2        |  3       | 0.8          | May,13 2015|  3        |
| 3        |  2       | 0.5          | May,12 2015|  2        |

Solution to the query:

select *
from (
  select *
  from ProQzQuizReport
  order by points desc, time_spend asc) a
group by user_id;

http://sqlfiddle.com/#!2/65fbf0/9


Why adding FOREIGN KEY constraint does not lock the table A "large" table :

mysql> select count(*) from MyTable;
+----------+
| count(*) |
+----------+
|  6594951 |
+----------+

Adding a FOREIGN KEY disallow to insert into the table

mysql> ALTER TABLE MyTable ADD CONSTRAINT FK_DC7F6DF281F7EA87 FOREIGN KEY (key_id) REFERENCES Key (id);

Take almost 20 minutes during it is impossible to add a new row.

My question: but why mysql does not consider the table as a locked table?

mysql> SHOW OPEN TABLES WHERE `Table` LIKE '%MyTable%' AND `Database` LIKE 'db';
+----------+-----------------------+--------+-------------+
| Database | Table                 | In_use | Name_locked |
+----------+-----------------------+--------+-------------+
| db       | MyTable               |      0 |           0 |
+----------+-----------------------+--------+-------------+
1 row in set (0.02 sec)

Thank you

A guess:

Even though the table is effectively locked, SHOW OPEN TABLES is oblivious to it.

LOCK TABLES is a very old DDL statement in MySQL.  It was probably a kludge to let users manually simulate transactions.  At about the same time (version 3?) SHOW OPEN TABLES was added.

Once InnoDB was added, it became clear that LOCK TABLES was no longer needed; InnoDB does row-level locking for DML and transactions.

There are a few DDL statements that effectively "lock" a table even in InnoDB, but they probably come through a different part of the code.  That is (remember, I am guessing), LOCK TABLES and SHOW OPEN TABLE know about each other, but ALTER runs in a different circle.

Feel free to complain at bugs.mysql.com .

I checked 122 systems; according to Com_show_open_tables, only 11 used SHOW OPEN TABLES since startup.


Permission Denied for a User that is part of AD I have a group created in Active Directory. This Group contains 3 users. For some strange reason one of the user within the group can't select  from specific type of SCHEMA whilst the 2 other users can select from the same schema
I ran some test EXECUTE AS USER for the other 2 users and everything seems fine except for that one user and i get the following error:

  Msg 229, Level 14, State 5, Line 4 The SELECT permission was denied on
  the object '', database 'Somethng_Database', schema 'sch'.

As the comment by Randi Vertongen suggested, more than likely that user belongs to more than one group, one of which has explicit deny permissions on the object in question.

This query will show you which group(s) that user belongs to.

EXEC xp_logininfo 'DomainName\AD_User', 'all'

You can then look at the output for all of the results (making sure to also check for that specific user) to see if they have any deny permissions.


SELECT xyz INTO #Temp from SSMS is 5x longer in one environment than another First time posting, I hope I'm doing it right.

I'm a "dba by accident" who is learning a lot but admittedly still has a lot more to learn. Here is my brain dump:

Prod server has 5 million rows. I copied those rows to a Test server.
Using SNAPSHOT or READ UNCOMMITTED produce different elapsed times, but the ratio between Prod and Test is always ~5:1 (14 minutes:3 minutes with SNAPSHOT, 4 minutes:sub-1 with READ UNCOMMITTED)
Using STATISTICS IO ON reveals that most of the reads on Prod are logical (~90%) while none of the reads on Test are logical. I think that should mean Prod would be faster if anything.
Most of the time appears to be going to waits of type "PAGEIOLATCH_SH."
I don't think different statistics can matter, since no index should be used on an unfiltered, unjoined, unsorted query.
SELECTing the top n rows on both servers reveals a very linear scale... the ratio is always ~5:1.
The comparison is not apples-to-apples, but I think the only relevant differences are activity and storage.
I have performed the comparison when activity was next to nothing, and saw the same results.
I discovered that autogrowth was set to 1MB (ugh), which I have since changed but I wonder if this has led to fragmented storage at the file system level.
The database is on a separate volume from the Windows installation, but it does share a volume with tempdb and with a FILESTREAM store, which I assume could also lead to fragmented storage.
The "optimize drives" windows utility says the volume is "OK (98% space efficiency)" which seems to suggest that maybe file system fragmentation isn't a problem.

Questions:

Are my thoughts and assumptions above correct?
Is there anything I'm blatantly overlooking in my thought process?
What are good candidates for the next steps in this troubleshooting process?
What other information can/should I have provided?

Thank you for your attention and help!

EDIT: Added rest of time information in 2nd statement.

For me it sounds like issue with tempdb.. What you could do- check how many files you have on each server- you can see recommendations here.

Are they big enough and not growing when you doing inserts? What about log file of tempdb?
if they are growing- best would be to expand them before (and keep that way).

One more thing you can check- if they are growing, you can probably make it faster by using instant file initialization.


sqlfiddle: leading blanks are gone I am not sure if this is on topic here.

When selecting VARCHAR2 columns that contain values with leading blanks this leading blanks will not be displayed in the output of sqlfiddle. so

select '     x' from dual

gives some html equivalent to

+-------------+
|"X"          |
+-------------+
|x            |
+-------------+

(without preceding blanks).

Here is the ouput in sqlfiddle.

A workaround is

SELECT translate('     x',' ',chr(128)) FROM dual
/

copy and paste the output to a spreadsheet
and then substitute this character by a blank.

Is there a better way get tproper output?

Response from admin@sqlfiddle.com (originally provided by miracle173 in a comment):

If you change the output format to "plaintext" rather than "tabular" (the default) you'll see the whitespace there like you expect. You can change the format by clicking the down arrow next to "Run SQL".
The reason you aren't seeing it in tabular mode is because by default html ignores more than one whitespace character, and I'm not bothering to do any work to override this default behavior for tabular formatting.


Is it better to install a very large Postgres database (billions of rows) in a real or a virtual machine? From my common sense, my physical machine hosting a very large Postgres database (for big data) should be real rather than virtual or container.

Some people say it is better to install on a virtual machine or a container.  I would like to get opinion from those who works with database every day. I am looking for performance over everything else.

Another question is about the storage: external or internal? I would think real internal disks with the RAIDs. Not sure external storage with logical volumes are better or not.

Your decision on VM or not shouldn't be so much determined by the number of rows but rather the availability, scaling, technical expertise, company culture, and performance based on your hardware availability.

You can run billions of rows in VMs.  Obviously you'd want to find the database engines best practices doc for your hypervisor and follow them carefully.  You should conduct plenty of testing to ensure it meets your needs and also test your HA solutions if needed for your SLAs.

External and internal at the end of the day come down to availability and IOPS or MBps.  If you have the expertise to run a fast external source reliability, great!  If you don't, then it would be safer to go internal.  Internal will be faster anyways by default without any special configuration or external hardware anyways and you should be testing your perf. reqs. but with SSDs and the latest nand solutions you're a lot less likely to under perform.


Recommendations on Partitioning Multi-Tenant Data in SQL-Server I would appreciate input on design considerations for partitioning (or not) a multi-tenant business application.  All data tables use a composite key that looks something like:
CREATE TABLE SomeTable(
  TenantId SMALLINT NOT NULL,
  ID INT NOT NULL,
  SomeData ...,
  CONSTRAINT [PK_SomeTable] PRIMARY KEY CLUSTERED (TenantID, ID)
)

Almost all reads are keyed to the specific tenantid.  Writes come in randomly by tenantID but any data in place for 3-5 days tends to never change after that.
Questions

If I don't partition, is (tenantID, id) or (id, tenantid) a better choice.  Assume reads are all by tenantid, writes are random by tenantid, and a there is enough downtime nightly to run a maintenance plan.
What are the pros and cons of creating a separate partition for each tenantID?
Will partitions reduce page fragmentation if the tenantID is the first key?
If the partitions are in the primary file, does that alter the arguments?
If there are 10 or 1000 partitions, does it alter the arguments?
Does the answer depend on whether I use SQL 2016 or SQL Azure?

If I don't partition, is (tenantID, id) or (id, tenantid) a better
  choice. Assume reads are all by tenantid, writes are random by
  tenantid, and a there is enough downtime nightly to run a maintenance
  plan.

(tenantID,id) is the better ordering, as it creates locality for each tenant's data.

  What are the pros and cons of creating a separate partition for each
  tenantID?

Pro: partition operations can be used to operate on single tenants.  Eg SWITCH, TRUNCATE, REBUILD.

Con: Maintence, and possibly cost of partition splitting to add new tenants.

But see:

  Will partitions reduce page fragmentation if the tenantID is the first
  key?

No.  Each tenant's rows will be logically and mostly physically contiguous in the index.  There will basically be one or a few fragments for each tenant.  So while the partitioning will reduce fragmentation, it shouldn't be a problem without partitioning.

  If the partitions are in the primary file, does that alter the
  arguments?

No.  That is separate.  All partitions should go to a single filegroup, unless there's some good reason to separate them.  You could potentially have a separate filegroup for some special tenant, but I wouldn't recommend that.

  If there are 10 or 1000 partitions, does it alter the arguments?

No, except that it exacerbates the maintence issue with the partitions.

  Does the answer depend on whether I use SQL 2016 or SQL Azure?

No.

But stepping back, you should plan to have multiple federated databases holding your tenant data.  You can still go with a multi-tenant database design, but in the end state you will have a mix of multi-tenant and single-tenant databases.  There are many important advantages to isolating a tenant in a database.

Tenant data is isolated.  This is important in selling to your customers, and it makes it very easy to make sure that tenants can never see each others' data.
You can upgrade and patch tenants separately.
You get the ability to move, backup, restore, export a tenant-at-a-time for free.  Critically this gives you point-in-time restore for single tenants.
Databases are a real security boundary, and you can enable users to see only a single tenant's data.
You don't have to write tenant export, and tenant import processes to move a tenant from one database to another, which you otherwise would have to write.
You can scale tenants separately, giving large or premier tenants differentiated access to server resources, or even giving them their own dedicated resources.  This makes your solution easier to manage, mitigates risk, and creates pricing opportunities for you.
Each tenant gets their own query plan cache, so you don't have query plans optimized for a small tenant used for queries for the data of a large tenant or vice versa.  And you have visibility into the performance metrics for each tenant.


Decomposition and lossless joins If someone walk me though the following questions I'd be very grateful.

  Consider a relation R(A, B, C, D, E) with the following functional dependencies:

A, B → C
D, E → C
B → D

  i) decompose R so that it is in 2nd normal form.

  Second part of the question: ii) suppose R is populated with the following data:

| A  | B  | C  | D  | E  |
| a1 | b1 | c1 | d1 | e1 |
| a2 | b1 | c2 | d1 | e2 |
| a2 | b2 | c1 | d3 | e2 |

  Show that your 2NF decomposition is lossless with respect to joins. To do this , produce a join expression amongst the decomposed tables, and show that its execution results in the original table above.

Thanks.

I figured it out and got it confirmed by the lecture, hopefully this will help someone else:

  i)

  The key is {A,B,E}.

  This means that FD1 and FD3 are both partial — but note that FD2 is
  not, because {D,E} is not a partial key. The decomposition creates one
  table for each of the FDs, and one for the PK FD:

  R1(A,B,E) R2(A,B,C) R3(B,D)

-

  ii) Using the above we can create the following tables:

        `R1                R2              R3`
| A  | B  | E  |   | A  | B  | C  |    | B  | D  |
| a1 | b1 | e1 |   | a1 | b1 | c1 |    | b1 | d1 |
| a2 | b1 | e2 |   | a2 | b1 | c2 |    | b2 | d3 |
| a2 | b2 | e2 |   | a2 | b2 | c1 |

  Joining R1 and R2 gives:

        R1R2                  R3
| A  | B  | E  | C  |    | B  | D  |
| a1 | b1 | e1 | c1 |    | b1 | d1 |
| a2 | b1 | e2 | c2 |    | b2 | d3 |
| a2 | b2 | e2 | c1 |

  Joining R1R2 with R3:

| A  | B  | E  | C  | D  |
| a1 | b1 | e1 | c1 | d1 |
| a2 | b1 | e2 | c2 | d1 |
| a2 | b2 | e2 | c1 | d3 |

  which is equal to:

| A  | B  | C  | D  | E  |
| a1 | b1 | c1 | d1 | e1 |
| a2 | b1 | c2 | d1 | e2 |
| a2 | b2 | c1 | d3 | e2 |

  Hence decomposition is lossles with respect to joins.


Inserting to a table when everything is parameterized I need to grab data stored on a remote AS/400 file system to which I connect using a linked server connection, so I can send OPENQUERY select statements and receive the data on SQL Server.

I don't know how many files/tables I'm going to end up with yet, but at this point I know enough to see that I'm going to need a flexible and scalable solution.... that ideally performs well enough.

So I have created a table in my Metadata database that looks something like this:

Id    Filename      FilterColumn    FilterValue    LastTransferred
 1    STDLIB.FOOBAR      TRXDATE           NULL               NULL
 2    STDLIB.SOMETH         NULL           NULL               NULL
...

My control flow looks like this:

   Collect Metadata
         ↓
[ Foreach Loop Start ]

   Execute DeleteStmt
         ↓
   Execute OPENQUERY
         ↓
 Write to destination

[ Foreach Loop End   ]

In the Collect Metadata step I have a data flow task that reads from the above metadata table and a script component that uses it to generate T-SQL statements - one to truncate/delete the existing local tables, and another to select the remote data; then I send everything into a Recordset Destination.

So far, so good.

The Foreach Loop Container iterates the recordset and can execute the generated truncate/delete commands, then executes the OPENQUERY select command, and dumps the result set into a dedicated User::RemoteData object variable.

And this is where I'm stumped: in the "Write to destination" step, I have another script component (configured as a "source") that takes this variable and uses an OleDbDataAdapter to fill up a DataTable, from which I'm able to pick up the column names and actual row data (every iteration of the Foreach loop container has a different table in User::RemoteData).

So I started implementing the CreateNewOutputRows override like this:

public override void CreateNewOutputRows()
{
    using (var records = new DataTable())
    using (var adapter = new OleDbDataAdapter())
    {
        adapter.Fill(records, Variables.RemoteData);
        var builder = new StringBuilder();
        builder.Append("INSERT INTO dbo.");
        builder.Append(Variables.StagingTableName);

        var columnNames = new string[records.Columns.Count];
        for (var index = 0; index < records.Columns.Count; index++)
        {
            columnNames[index] = records.Columns[index].ColumnName;
        }

        builder.Append("(");
        builder.Append(string.Join(",", columnNames);
        builder.Append("););

        builder.Append(" VALUES (");
        foreach (DataColumn column in records.Columns)
        {
            // ok, stop, think. this can't be right.
        }
    }
}

So I stopped and started thinking... and I can't think of a nice, neat way to do this.

Am I going to have to script these INSERT commands and check column types to properly single-quote-delimit string values and whatnot?

Isn't this going to perform like a snail? I'm not liking that the Foreach container is going to process each table sequentially, and I can already see the above solution completing in hours... when it's only the "transfer data over from remote system" part (I have many more ETL steps coming up, that will need to consume this data).

What would be a better way? Do I have to drop the idea of sourcing my data flow from my Metadata database, and have n "statically-defined" workflows, one for each table?

// ok, stop, think. this can't be right.

Indeed, there's a better, cleaner, safer, faster way.

You have a DataTable in memory; you can "dump" it as-is to the destination table using a SqlBulkCopy object:

public override void CreateNewOutputRows()
{
    using (var records = new DataTable())
    using (var adapter = new OleDbDataAdapter())
    {
        adapter.Fill(records, Variables.RemoteData);

        var bulk = new SqlBulkCopy(_connection, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.UseInternalTransaction, null);
        bulk.BulkCopyTimeout = 0;
        bulk.DestinationTableName = Variables.StagingTableName;
        bulk.WriteToServer(records);

        _transferredRows = records.Rows.Count;
        Output0Buffer.SetEndOfRowset();
    }
}

That fixes the first problem - you indeed don't need to be bothered with individual rows and/or column names and types, even less to build/concatenate an individual INSERT statement for every single row in the data source.

The other problem to solve is to get each iteration to run concurrently. I believe you could extract the body of the Foreach loop container into its own parameterized SSIS package, and replace it with an out-of-process Execute Package Task.


Best SQL datatype for JSON In SQL Server 2016, I believe the best datatype for handling JSON is nvarchar.
Most of our JSON strings will not use more than 100 characters.
Should I use nvarchar(100) or nvarchar(max)?

We prefer nvarchar(100). I just want to make sure the new JSON functions do not have a preference for using the max type. Max is stored outside of row and slightly less efficient performance-wise.

The documentation you linked to links to the JSON page, which says:

  JSON is a textual format so the JSON documents are can stored in NVARCHAR columns in SQL Database.

Wait a second...I think this documentation needs a pull request :)

Anyways...As always with MAX, if you don't need it, don't use it! You're correct that it will cause a (slight) performance hit and SQL Server in no way prefers NVARCHAR(MAX) for JSON storage. Consider yourself lucky you're storing such small JSON.


SQL Server 2005: Job scheduled every 4 hrs, but sp_help_jobactivity shows unexpected value for next_scheduled_run_date I have done a lot with sql and jobs, and this is throwing me off.

My job is scheduled as shown below.

I can run the job interactively from SSMS by starting the job, it runs, all is well.

It is currently Jan 27 at 11 AM local time.
But sp_help_jobactivity returns a next_scheduled_run_date of

next_scheduled_run_date
2012-01-27 12:01:00.000

I expect it to be four hours from the last time I ran it, or from when I created the job and schedule (which I created at the same time).

Observed Problem: While the SSMS GUI displays "12:01 AM" the data in next run shows "12:01:00" which is "12:01 PM"

What am I missing?
When will the job next run?

Thanks!

The job runs every 4 hours starting with the start time in the schedule. If you run it manually that has no effect on the next scheduled startstart time.


AlwaysOn Disaster Recovery Clarifications Whenever I Perform a Forced Manual Failover of an Availability Group from PRODUCTION Servers(Sync) to DR/BCP (Asynchronous) node and test the connectivity and if everything is okay. I have two questions.

1)How to revert back the AOAGs to original Synchronous mode -- Should I Perform a Forced Manual Failover of an Availability Group to PRODUCTION Servers(Sync) from DR/BCP (Asynchronous) node ?

2)We have one extra BCP node MSDTC and  AOAGs Listener. Should I turn then online before I Perform a Forced Manual Failover of an Availability Group from PRODUCTION Servers(Sync) to DR/BCP (Asynchronous) node ?

Please correct me. I am asking these questions for BCP site Testing plan view.

1)How to revert back the AOAGs to original Synchronous mode -- Should I Perform a Forced Manual Failover of an Availability Group to PRODUCTION Servers(Sync) from DR/BCP (Asynchronous) node ?

You've failed over to asynchronous nodes. This means all of the database flows are paused and there is no current way (assuming it's a true disaster) how far behind your secondary replicas were. Now that they've come back up, we know that it isn't going to be 100% the same data (they are asynchronous). This was not mentioned in the question but I'm going to add it to the answer as it's extremely important as it's part of your SLA.

Take a database backup of the original primary databases. To do this you will need to kick them out of the AG. Alternatively you could take a database snapshot (inside of SQL, not SAN).
Resume data movement between the replicas at the database level.
Set the local primary replica to synchronous and a remote replica to synchronous.
Wait for them both to say 'Synchronized'. They will start out as 'Synchronizing'. This could take quite a while depending on multiple factors such as downtime, data generated, IO, Networking, etc.
Once Synchronized, find a good time to take a minute or less outage. Use that time to do a planned manual failover.
Set the local servers to be synchronous to each other.
Remove synchronous from the remote replica and set it back to async.

  2)We have one extra BCP node MSDTC and AOAGs Listener. Should I turn then online before I Perform a Forced Manual Failover of an Availability Group from PRODUCTION Servers(Sync) to DR/BCP (Asynchronous) node ?

How do you have an "extra AOAG listener" just chilling out? I don't understand that part of your question.

MSDTC is for distributed transactions, which aren't supported in 2012/2014 and only supported with certain restrictions (as of this moment) in 2016. Thus this is not required in a supported scenario. If you're going unsupported then this is still a moot point as the local MSDTC will be used.

Unless I'm missing something (completely possible) or not understanding, this is not needed.


MySQL: 3 servers on-site architecture I have 3 servers with RHEL 6 OS and Percona server running in one of the servers in the same data centre, while the other 2 servers lie idle.

Now I need to implement some kind of on-site redundancy (high-availability) for my databases.

What is the best option we got here? I want to go for Percona cluster but I don't want to since there aren't people who cannot manage the same.

Any other options?

Master and two slaves?
Two Masters (Active-Passive) and One slave?

Need inputs here.

These are your options:

1 master with 2 slaves, standard replication. If you have long running or massive transactions you may want to stick with this.
1 master with 2 slaves + MHA to handle failover
2 masters (active-passive) with 1 multi-source replicating slave. Just to emphasize active-passive masters
3 node Percona XtraDB Cluster/MariaDB Galera Cluster, avoid these if you have long running or big transactions.
3 node PRM cluster

Others might have a better answer but IMHO these are good options.


SQL command to remove prefix from a set of tables I am no DBA. I have a situation with a MySQL database (5.0.51a) where I want to remove a 'dr_' prefix to all tables having this prefix (about 110 tables). I could rename them one by one of course, but I was wondering whether there was an SQL command to perform this in one shoot?

To make it clear, a table called dr_hjkd would have to be renamed hjkd. A table called rfefd would keep the same name. Thanks.

Wow I answered a similar question a year ago.

Nevertheless, your question is unique.

Here it goes:

If you are renaming all tables in the database mydb, here is the query you need:

SELECT
CONCAT('ALTER TABLE ',db,'.',old_tblname,' RENAME ',db,'.',new_tblname,';')
FROM
(
    SELECT
        table_schema db,
        table_name old_tblname,
        substr(table_name,4) new_tblname
    FROM
        information_schema.tables
    WHERE
        SUBSTR(table_name,1,3)='dr_'
        AND table_schema = 'mydb'
) A;

Run this in the OS and capture it to a SQL file. Then, execute the SQL file.

mysql -u... -p... -AN -e"SELECT CONCAT('ALTER TABLE ',db,'.',old_tblname,' RENAME ',db,'.',new_tblname,';') FROM (SELECT table_schema db,table_name old_tblname,substr(table_name,4) FROM information_schema.tables WHERE SUBSTR(table_name,1,3)='dr_' AND table_schema = 'mydb') A" > BigRename.sql
mysql -u... -p... < BigRename.sql

If you are renaming all tables in all databases, here is the query you need:

SELECT
CONCAT('ALTER TABLE ',db,'.',old_tblname,' RENAME ',db,'.',new_tblname,';')
FROM
(
    SELECT
        table_schema db,
        table_name old_tblname,
        substr(table_name,4) new_tblname
    FROM
        information_schema.tables
    WHERE
        SUBSTR(table_name,1,3)='dr_'
) A;

Run this in the OS and capture it to a SQL file. Then, execute the SQL file.

mysql -u... -p... -AN -e"SELECT CONCAT('ALTER TABLE ',db,'.',old_tblname,' RENAME ',db,'.',new_tblname,';') FROM (SELECT table_schema db,table_name old_tblname,substr(table_name,4) FROM information_schema.tables WHERE SUBSTR(table_name,1,3)='dr_') A" > BigRename.sql
mysql -u... -p... < BigRename.sql


free database modelling software What is available in the realms of database modelling software that is free.  I am developing an SQLExpress database.

CA ERWin - community version
  although is free, I can't see an option to allow me to create a database, either using a direct connect or via creating an SQL script file.

Embarcardo ER/Studio - 14 day trial
  Can I reinstall this every 14 days?

What others are there, that will allow me to model my database and create a "create" script or "update" sql script?

Thanks in advance.

There are several choices:

SQL Power Architect (download without registration from Google Code). This is open source, but it seems there is no further development.
Oracle's Data Modeler (but it only supports Oracle, SQL Server and DB2. PostgreSQL for example is not supported)
A free version of TOAD Data Modeler - although that has some serious limitations on the size of the model
Open ModelSphere a general purpose modeler (including UML) but can also be used for ER modelling


Why is a Login losing a User mapping? Is there any way to determine why is a Login is losing a User mapping?

I've created them with the following SQL ...

CREATE LOGIN [qauser] WITH PASSWORD = 'xxxx', DEFAULT_DATABASE = [app_tst],
                           CHECK_EXPIRATION=OFF, CHECK_POLICY = OFF

CREATE USER [qauser] FOR LOGIN [qauser] WITH DEFAULT_SCHEMA = [devdba]

and then successfully used the matching login on a linked server with synonyms to tables in the original.

Some time later, the synonyms fail and checking the LOGIN properties, the User Mapping have gone for the database / user. I have to re-run the CREATE LOGIN / USER script to get them to work ... but using the main devdba login, the synonyms continues to work

What could be causing this loss ?

From the post here (user mapping disappearing):

"The only time I have seen something like this happen, was when the application DBAs where restoring test scenario DB backups over the existing databases, without telling us SQL Server SysAdmins what they were doing. This would of course wipe out all of our later user mappings to those databases. So, you might want to check for something like that... "

And in the same thread, a pointer to a script for knowing when the database was last restored.