How to update group no incremental by 1 based on every group from 1 to 3? I work on SQL server 2012 I face issue i can't update group no to every steps from 1 to 3
meaning i need every group from 1 to 3 take identity number incremental by 1 as 1,2,3 etc ..
create table #Replacement
       (
       PartIDC  INT,
       PartIDX  INT,
       FlagStatus nvarchar(50),
       HasReplacement nvarchar(50),
       groupId int,
       step tinyint
       )
    insert into #Replacement (PartIDC,PartIDX,FlagStatus,HasReplacement,groupId,step)
    values

 (1222, 3421, 'Invalid', 'Critical', NULL, 1),
 (3421, 6421, 'Valid' ,    'Active' ,   NULL, 2),
 (1222, 6421, 'Valid' ,    'Chain',     NULL, 3),
 (5643, 2243, 'Invalid', 'Critical', NULL, 1),
 (2243, 3491, 'Valid' ,    'Active',   NULL, 2),
 (5643, 3491, 'Valid' ,    'Chain',     NULL, 3)

select * from #Replacement
Expected result
   PartIDC PartIDX FlagStatus HasReplacement groupId step
    1222 3421 Invalid           Critical      1      1
    3421 6421 Valid              Active       1      2
    1222 6421 Valid           Chain           1      3
    5643 2243 Invalid           Critical      2      1
    2243 3491 Valid           Active          2      2
    5643 3491 Valid           Chain           2      3

always step 1 and step 3 are both equal on PartID C
always step 1 and step 2 are both equal on PartID x from step 1 equal to PartID C from step 2 .
so How to do Expected result above by update statement to group Id ?

Check if it works for you. Please, notice this code is meant to work with groups containing no more than 3 steps each.
;WITH CTE_Replacement
AS
(
    --Creates the groups by selecting only the rows with step 1
    SELECT PartIDC,
        PartIDX,
        FlagStatus,
        HasReplacement,
        ROW_NUMBER() OVER(ORDER BY PartIDC) AS groupId,
        step
    FROM #Replacement
    WHERE step = 1

    UNION ALL

    --Gets steps 2 that are lelated to the groups with step 1 from the query above
    SELECT R.PartIDC,
        R.PartIDX,
        R.FlagStatus,
        R.HasReplacement,
        CTE.groupId, --Uses the group created before
        R.step
    FROM #Replacement R
        INNER JOIN CTE_Replacement CTE ON R.PartIDC = CTE.PartIDX
    WHERE R.step = 2
)
--Adds the last step of each goup
SELECT R.PartIDC,
        R.PartIDX,
        R.FlagStatus,
        R.HasReplacement,
        CTE.groupId,
        R.step
FROM #Replacement R
    LEFT JOIN CTE_Replacement CTE ON R.PartIDX = CTE.PartIDX
ORDER BY CTE.groupId, CTE.step;


Transactional replication with updates on the subscriber? Summary

We need a "two-way" (bi-directional) replication topology which should be easy to setup and administer, with minimal downtime when changes are required.

Current solution

We have two SQL Server Instances 2008 R2 Standard, on Servers SQL1 and SQL2.
Server SQL1 has a database db1 with tables a,b,c,d,e,f,g,h,i,j,k.
Server SQL2 has a database db2 with tables a,b,c,d,e,f,g,h,i,    x,y,z

db1 serves as the primary database of the application app1, where all transactions are executed (mainly OLTP workload).
db2 serves as the replica database of db1 where all the reports and DW tasks are executed (mainly DW workload).
db2 has almost all data of db1 (tables a-i) and some extra tables (x,y,z).

We have setup a Transactional Replication (db1_to_db2) with the following (important) options
for the publication:
- @repl_freq = N'continuous'
- @immediate_sync = N'true'
- @replicate_ddl = 1

for the published articles:
- @schema_option = 0x000001410DC37EDF

As there is little maintenance window available for both databases, with the described setup we can:
1. replicate schema changes to the subscriber
2. add new tables to the publication and use a partial snapshot rather than having to reinitialize the entire subscription

This is working like a charm.
But now, a new requirement has come up.

The Problem

Some changes on specific replicated tables, that are updated on db2 directly, should be transferred back to db1.
Let's say that tables b on db2 (that references table a and is referenced by table c through FKs), accepts updates on its data that should travel to table b on db1 and not get replicated back to db2. Only Updates are permitted on table b on db2. Not Inserts or Deletes.

Thoughts

We have tried every possible (simple and easily adopted) solution we could think of:

Setup merge replication instead of transactional.

It does not accept partial snapshots for newly added articles.
It adds an extra column which means major changes to the application app1.

FAIL
Transactional replication with updatable subscriber

It does not accept partial snapshots for newly added articles.
It adds an extra column which means major changes to the application app1.

FAIL
Update db1.b through a (ON INSERT) trigger on db2.b:

This is a 2Phase-commit transaction that could fail due to network connectivity issues (which otherwise do not bother us)
There is no easy way to explude these Updates from being replicated back to db2.b through the replication.

FAIL
Setup a transactional replication from db2 to db1 with table b as the only article.

Again there is no way to exclude these transactions from being replicated back to db2. It would be nice to have something like a 'NOT FOR REPLICATION' option for these transactions...

FAIL

This is as far as we have gone in search of a solution.

Please help

Please state any idea you might have, taking into account our specific needs.
Forgive my being too analytic but I wanted to give every piece of required information you might need.

After all it seems that the answer to my question was #4 (Setup a transactional replication from db2 to db1 with table b as the only article)

There is a not-so-advertised option for the subscriptions (at least not to me) called @loopback_detection, which makes sure that transaction are not replicated back to their originator!
This means that you can set up a two-way transactional replication, plain and simple, and accomplish what I was asking for: a subscriber that can update specific tables, so that these changes are replicated to the publisher (and never come back to the subscriber).

You have to pay attention to some options however, to avoid messing things up when setting up a two-way transactional replication:
- When adding articles to the "reverse" publication:
@pre_creation_cmd = N'none' -- so that the original tables are not dropped/recreated
- When setting up the "reverse" subscription you should set:
@sync_type = N'replication support only' -- so that no snapshot is required to initiate the subascriber
@loopback_detection = 'true' (which is set by default)

These are the steps that need special attention. Other than that, it is a straight forward process.

When you have everything set up, you can test the reverse replication by executing an update on the replica db, while you have the "View synchronization status" windows for both replications open.
You will notice that only the reverse replication will log a "1 transaction(s) with 1 command(s) were delivered." message.

PS. I will provide a detailed description of the setup process, shortly.


Retrieving row ranking in a table I'm aware there are already many questions alike however none of those answers helped me achieve row rankings in a table.

I have a table set up as so:

+----------------------------------+---------------+--------+
| HEX(UUID)                        | Username      | Rating |
+----------------------------------+---------------+--------+
| 0000F659C5854C9DB8BE8984FEC8CFD9 | Aidan8or_     |   1600 |
| 00022E3531F44C36AE058B3CF063C02F | rob160502     |   1600 |
| 000399B665D14979B3C0AF309112625F | HauntedCorpse |   1536 |
| 0003BAC2C0764C0C9FE20C4A3C701D7E | Glitchx0R     |   1234 |
| 000597056E564BF587DAA1D31E51E29F | jkz101022     |   455  |
+----------------------------------+---------------+--------+

What I would like to do here is select a single row with a ranking appended to it (1, 2, 3, 10, 100, ...). If you haven't already guessed it, the rating is an ELO Rating therefore many scores are tied and I need to accommodate for that when creating leaderboards.

I've taken a look at this: Get the rank of a user in a score table and it had some really good answers but I just couldn't get any of them working correctly.

I've tried all those solutions and got close with this:

SELECT HEX(UUID) as UUID, Username, Rating, FIND_IN_SET( Rating, (
  SELECT GROUP_CONCAT( Rating
  ORDER BY Rating DESC )
  FROM table_name )
  ) AS rank
  FROM table_name
WHERE Username = 'jkz101022'

however returns the ranking as 0.

You must evaluate everybody's rank and select the Username last

SET @rnk=0; SET @rank=0; SET @curscore=0;
SELECT score,Username,rank FROM
(
    SELECT AA.*,BB.Username,
    (@rnk:=@rnk+1) rnk,
    (@rank:=IF(@curscore=score,@rank,@rnk)) rank,
    (@curscore:=score) newscore
    FROM
    (
        SELECT * FROM
        (
            SELECT COUNT(1) scorecount,rating score
            FROM mytable GROUP BY rating
        ) AAA
        ORDER BY score DESC
    ) AA LEFT JOIN scores BB USING (score)
) A WHERE Username='jkz101022';

If you want the original row, then do this

SET @rnk=0; SET @rank=0; SET @curscore=0;
SELECT HEX(B.UUID) as UUID,B.Username,B.Rating,A.rank FROM
(
    SELECT AA.*,BB.Username,
    (@rnk:=@rnk+1) rnk,
    (@rank:=IF(@curscore=score,@rank,@rnk)) rank,
    (@curscore:=score) newscore
    FROM
    (
        SELECT * FROM
        (
            SELECT COUNT(1) scorecount,rating score
            FROM mytable GROUP BY rating
        ) AAA
        ORDER BY score DESC
    ) AA LEFT JOIN scores BB USING (score)
) A INNER JOIN mytable B USING (Username)
WHERE A.Username='jkz101022';

UPDATE 2014-08-25 16:57 EDT

I Fixed My Code

SET @rnk=0; SET @rank=0; SET @curscore=0;
SELECT HEX(B.UUID) as UUID,B.Username,B.Rating,A.rank FROM
(
    SELECT AA.*,BB.Username,
    (@rnk:=@rnk+1) rnk,
    (@rank:=IF(@curscore=rating,@rank,@rnk)) rank,
    (@curscore:=rating) newscore
    FROM
    (
        SELECT * FROM
        (
            SELECT DISTINCT Rating score FROM survivalgamesstats
        ) AAA
        ORDER BY score DESC
    ) AA LEFT JOIN survivalgamesstats BB ON AA.score=BB.Rating
) A INNER JOIN survivalgamesstats B USING (Username)
WHERE A.Username='jkz101022';

I got this outout

mysql> SET @rnk=0; SET @rank=0; SET @curscore=0;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT HEX(B.UUID) as UUID,B.Username,B.Rating,A.rank FROM
    -> (
    ->     SELECT AA.*,BB.Username,
    ->     (@rnk:=@rnk+1) rnk,
    ->     (@rank:=IF(@curscore=rating,@rank,@rnk)) rank,
    ->     (@curscore:=rating) newscore
    ->     FROM
    ->     (
    ->         SELECT * FROM
    ->         (
    ->             SELECT DISTINCT Rating score FROM survivalgamesstats
    ->         ) AAA
    ->         ORDER BY score DESC
    ->     ) AA LEFT JOIN survivalgamesstats BB ON AA.score=BB.Rating
    -> ) A INNER JOIN survivalgamesstats B USING (Username)
    -> WHERE A.Username='jkz101022';
+----------------------------------+-----------+--------+------+
| UUID                             | Username  | Rating | rank |
+----------------------------------+-----------+--------+------+
| 000597056E564BF587DAA1D31E51E29F | jkz101022 |   1600 |    2 |
+----------------------------------+-----------+--------+------+
1 row in set (0.00 sec)

mysql>

UPDATE 2014-08-25 17:15 EDT

I tried it with rob160502 and got this:

mysql> SET @rnk=0; SET @rank=0; SET @curscore=0;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT HEX(B.UUID) as UUID,B.Username,B.Rating,A.rank FROM
    -> (
    ->     SELECT AA.*,BB.Username,
    ->     (@rnk:=@rnk+1) rnk,
    ->     (@rank:=IF(@curscore=rating,@rank,@rnk)) rank,
    ->     (@curscore:=rating) newscore
    ->     FROM
    ->     (
    ->         SELECT * FROM
    ->         (
    ->             SELECT DISTINCT Rating score FROM survivalgamesstats
    ->         ) AAA
    ->         ORDER BY score DESC
    ->     ) AA LEFT JOIN survivalgamesstats BB ON AA.score=BB.Rating
    -> ) A INNER JOIN survivalgamesstats B USING (Username)
    -> WHERE A.Username='rob160502';
+----------------------------------+-----------+--------+------+
| UUID                             | Username  | Rating | rank |
+----------------------------------+-----------+--------+------+
| 00022E3531F44C36AE058B3CF063C02F | rob160502 |   1650 |    1 |
+----------------------------------+-----------+--------+------+
1 row in set (0.00 sec)

mysql>

UPDATE 2014-08-25 17:38 EDT

I even tried HauntedCorpse and got this:

mysql> SET @rnk=0; SET @rank=0; SET @curscore=0;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT HEX(B.UUID) as UUID,B.Username,B.Rating,A.rank FROM
    -> (
    ->     SELECT AA.*,BB.Username,
    ->     (@rnk:=@rnk+1) rnk,
    ->     (@rank:=IF(@curscore=rating,@rank,@rnk)) rank,
    ->     (@curscore:=rating) newscore
    ->     FROM
    ->     (
    ->         SELECT * FROM
    ->         (
    ->             SELECT DISTINCT Rating score FROM survivalgamesstats
    ->         ) AAA
    ->         ORDER BY score DESC
    ->     ) AA LEFT JOIN survivalgamesstats BB ON AA.score=BB.Rating
    -> ) A INNER JOIN survivalgamesstats B USING (Username)
    -> WHERE A.Username='HauntedCorpse';
+----------------------------------+---------------+--------+------+
| UUID                             | Username      | Rating | rank |
+----------------------------------+---------------+--------+------+
| 000399B665D14979B3C0AF309112625F | HauntedCorpse |   1536 |    5 |
+----------------------------------+---------------+--------+------+
1 row in set (0.00 sec)

mysql>

Give it a Try !!!

CAVEAT : Please note that the results come from your actual data in SQLFiddle. I simply loaded the data into a test database and ran my code against it.


Always On Primary Replica On Prem, Secondary Replica On Azure We want to create an Always On Availability group where the primary replica will be On-premises and the secondary replica will be on an Azure VM. I have researched and I know it's possible to do this, but in all the examples I find, they always have a secondary replica on-prem as well and then the 3rd VM is the one that goes to azure. We only want to have 2 replicas, one on-prem and one on azure. Is this possible?

I have read the documentation to use the "Add Azure Replica Wizard" and one of the prerequisites is: Your availability group must contain on-premise availability replicas. I'm not sure if that means that I have to have at least one secondary replica on-prem or if it only means that I have to create the availability group first on-prem and then only add the secondary replica.

Thanks for your help and guidance!

We only want to have 2 replicas, one on-prem and one on azure. Is this possible?

Yes, it's possible. There are extra items you'll want to think about such as having a domain controller (if ad-attached clustering/cluster at all is used) and what type of pipe you'll need, region and latency, load balancer setup, etc., but it's not a hard requirements to have more than two replicas. Most have two replicas locally for HA and the Azure one for DR.

  have read the documentation to use the "Add Azure Replica Wizard" [...]

That wizard no longer works as it was based on the classic Azure model and is no longer available or viable. Disregard using the wizard. You'll want to deploy this via a written script or by hand (Azure web gui) after all of the required infrastructure pieces are decided upon and understood. Obviously setting up a test environment to see how things behave would behoove you.


Bringing web service data into SQL server I need to retrieve a data set daily from a hosted Web service. I can retrieve the XML "column names" in an SSIS package. But I need to retrieve all the data in the data set in csv format or bring directly into SQL Server.  (Rows and columns, header data and dataset).

Is this feasible on SSIS? Or is there a better approach? Any input or advice is greatly appreciated.

Does this need to be done using SSIS? If so, you can do pretty much whatever you like in a Script Task (C# or VB.NET). That way you can bring the data directly into SSIS variables and use throughout the package.

Or, you can do this via SQLCLR, in which case you can call it directly from T-SQL. This lets you integrate it rather easily into Stored Procedures, SQL Agent Jobs, etc. You can even call it in a SQL Task in SSIS. BUT, you would need to be a bit more careful using SQLCLR as opposed to using a Script Task in SSIS. I have documented several things to consider across a few answers on Stack Overflow:

How to invoke webservice from SQL Server stored procedure
SQL CLR Web Service Call: Limiting Overhead
Call web service from SQL CLR?

The most important points from those answers are:

Look at the following MSDN pages:

HttpWebRequest
HttpWebResponse
XmlDocument

To escape URLs / Query Strings:

If you are using SQL Server 2005, 2008, or 2008 R2, then use Uri.EscapeDataString as it was available prior to .NET Framework v4.5
If you are using SQL Server 2012, 2014, or newer, then you can use either Uri.EscapeDataString or, if the server has been updated to at least .NET Framework v4.5, then you can alternatively use WebUtility.UrlEncode

If not already done, you will have to enable (one time) "CLR Integration" at the server level: Enabling CLR Integration
SQLCLR is "shared" code. There is a single App Domain for each DB + Owner combination. So all Sessions calling this code will be operating in the same App Domain. If multiple simultaneous requests are made to the same URI, all but the first 2 might be "on hold" since you are bound by the concurrent connection limit imposed by ServicePointManager ( ServicePointManager.DefaultConnectionLimit ). And the default limit is a whopping 2! Meaning, all additional requests to that URI, while there are already 2 active/open HttpWebRequests, will wait inline, patiently. You can increase this by setting the .ServicePoint.ConnectionLimit property of the HttpWebRequest object.
You will need to mark your Assembly as WITH PERMISSION_SET = EXTERNAL_ACCESS. Please don't be lazy and turn TRUSTWORTHY ON. That is an unnecessary security risk. The proper way to make this secure is to do the following:

Sign your Assembly
In the [master] database, create an Asymmetric Key from the DLL of your Assembly.
Also, in [master], create a Login from that Asymmetric Key
Grant your new Login the EXTERNAL ACCESS ASSEMBLY permission
Create your Assembly with a PERMISSION_SET of EXTERNAL_ACCESS, not UNSAFE

If you want to go down the SQLCLR path but don't want to mess with the coding, etc, then one option is to get a pre-built function. The SQL# SQLCLR library (that I wrote) has a function called INET_GetWebPages (not available in the Free version) that calls a URL (can pass in either GET or POST data) and passes back the response (and the response headers as a separate XML field).


Using source columns in OUTPUT INTO clause of an INSERT statement (SQL Server) I am writing a batch processing insert statement and would like to use a temp table to keep track of inserted ID's instead of looping through the items myself and calling SCOPE_IDENTITY() for each inserted row.

The data that needs to be inserted has (temporary) ID's linking it to other data that also should be inserted into another table, so I need a cross reference of the actual Id and the temporary Id.

This is an example of what I have so far:

-- The existing table
DECLARE @MyTable TABLE (ID INT IDENTITY(1,1), [Name] NVARCHAR(MAX));

-- My data I want to insert
DECLARE @MyInsertData TABLE (ID INT, [Name] NVARCHAR(MAX));
INSERT INTO @MyInsertData ( ID,Name)
VALUES ( -1 , 'bla'),(-2,'test'),(-3,'last');

DECLARE @MyCrossRef TABLE ([NewId] INT, OldId INT);

INSERT INTO @MyTable ( [Name] )
   OUTPUT Inserted.ID, INS.ID INTO @MyCrossRef
   SELECT [NAME] FROM @MyInsertData INS

-- Check the result
SELECT * FROM @MyCrossRef

The problem is that I cannot get the OUTPUT INTO clause to accept the ID, I've tried @MyInsertData.ID and other tricks joining the table to itself, but nothing seems to work.

Actually, you can achieve the same thing by changing your INSERT to a MERGE. While the MERGE statement is actually a pretty neat way to do "upserts" in SQL Server, there's nothing to stop you from using it just for the purpose of inserting:

-- The existing table
DECLARE @MyTable TABLE (ID INT IDENTITY(1,1), [Name] NVARCHAR(MAX));

-- My data I want to insert
DECLARE @MyInsertData TABLE (ID INT, [Name] NVARCHAR(MAX));
INSERT INTO @MyInsertData ( ID,Name)
VALUES ( -1 , 'bla'),(-2,'test'),(-3,'last');

DECLARE @MyCrossRef TABLE ([NewId] INT, OldId INT);

MERGE INTO @MyTable AS dest
USING @MyInsertData AS ins ON 1=0   -- always false

WHEN NOT MATCHED BY TARGET          -- happens for every row, because 1 is never 0
    THEN INSERT ([Name])
         VALUES (ins.[NAME])

OUTPUT inserted.ID, ins.ID
INTO @MyCrossRef (NewId, OldId);

-- Check the result
SELECT * FROM @MyCrossRef

One of the nice things about MERGE is that it allows you to access the source columns as well as the built-in inserted and deleted tables in the OUTPUT clause.

My code may contain errors, as I haven't actually tested it. My blog post from a few years ago goes into a little more detail, including on query performance.


Aggregation vs Cursor For Reshaping MongoDB Documents I'm looking to reshape the Documents in one of my collections, and have found two ways to do it, but need guidance.  For simplicity, say I have a collection, "myColl", and I need to reshape Documents that look like this:

{
   x:"foo",
   y:"bar"
}

To:

{
   nest: {
       x: "foo",
       y: "bar"
   }
}

This can be accomplished by using the aggregation framework to reshape the documents, and then rewrite the entire collection. When run against a test collection of about 150K records, the following takes roughly 5 seconds:

db.myColl.aggregate([{$project: {_id:"$id", nest: {x:$x, y:$y}}}, {$out:"myColl"}]);

If I try to do this using a cursor, it takes about a 1.5 minutes:

db.myColl.find().snapshot().forEach(
        function(elem) {
            db.myColl.update(
                {_id: elem._id},
                {$set: {nest: {x: elem.x, y: elem.y}}}
            );
        }
);

I'm leaning towards the aggregation approach for performance reasons; however,  someone mentioned here that it is creating a "new" collection with somewhat of a negative connotation, but it's not entirely apparent as to why.  Are there causes of concern that I should be aware of other than the Type safety mentioned in that comment?

Also, if the cursor approach is better, then how might I speed up the execution?  Setting the "w" param of WriteConcern to 0 doesn't do anything in my test because everything is hosted on the same box so skipping the acknowledgement doesn't save me any time, and is orthogonal to the fact that aggregation is executing order of magnitudes faster.

Thanks for the input!

Aggregation vs Cursor

Let's first start from Aggregation. As per MongoDB BOL Here Aggregation operations process data records and return computed results. Aggregation operations group values from multiple documents together, and can perform a variety of operations on the grouped data to return a single result.
The aggregation pipeline can use indexes to improve its performance during some of its stages. In addition, the aggregation pipeline has an internal optimization phase.

The most basic pipeline stages provide filters that operate like queries and document transformations that modify the form of the output document.

  The pipeline provides efficient data aggregation using native
  operations within MongoDB, and is the preferred method for data
  aggregation in MongoDB.

For example here i want to show the aggregation where MongoDB provides three ways to perform aggregation: the aggregation pipeline, the map-reduce function, and single purpose aggregation methods.

Let's here i am going to create orders collection of MongoDB with 4 documents.

> db.orders.insertMany([
... {cust_id: "A123",
... amount: 500,
... status: "A"
... },
... {cust_id: "A123",
... amount: 250,
... status: "A"
... },
... {cust_id: "B212",
... amount: 200,
... status: "A"
... },
... {cust_id: "A123",
... amount: 300,
... status: "D"
... }
... ]
... )
{
        "acknowledged" : true,
        "insertedIds" : [
                ObjectId("5a44c1479adf6e5fc5cea525"),
                ObjectId("5a44c1479adf6e5fc5cea526"),
                ObjectId("5a44c1479adf6e5fc5cea527"),
                ObjectId("5a44c1479adf6e5fc5cea528")
        ]
}

To verify the inserted documents from MongoDB

> db.orders.find().pretty()
{
        "_id" : ObjectId("5a44c1479adf6e5fc5cea525"),
        "cust_id" : "A123",
        "amount" : 500,
        "status" : "A"
}
{
        "_id" : ObjectId("5a44c1479adf6e5fc5cea526"),
        "cust_id" : "A123",
        "amount" : 250,
        "status" : "A"
}
{
        "_id" : ObjectId("5a44c1479adf6e5fc5cea527"),
        "cust_id" : "B212",
        "amount" : 200,
        "status" : "A"
}
{
        "_id" : ObjectId("5a44c1479adf6e5fc5cea528"),
        "cust_id" : "A123",
        "amount" : 300,
        "status" : "D"
}

Aggregation Pipeline

MongoDB’s  aggregation framework is modeled on the concept of data processing pipelines. Documents enter a multi-stage pipeline that transforms the documents into an aggregated result.

> db.orders.aggregate([{$match: {status: "A"}},
... {$group: {_id: "$cust_id",total:{$sum: "$amount"}}}])
{ "_id" : "B212", "total" : 200 }
{ "_id" : "A123", "total" : 750 }
>

Map-Reduce

MongoDB also provides map-reduce operations to perform aggregation. In general, map-reduce operations have two phases: a map stage that processes each document and emits one or more objects for each input document, and reduce phase that combines the output of the map operation. Optionally, map-reduce can have a finalize stage to make final modifications to the result. Like other aggregation operations, map-reduce can specify a query condition to select the input documents as well as sort and limit the results.

> db.orders.mapReduce(
... function() {emit (this.cust_id,this.amount);},
... function(key,values){return Array.sum(values)},
... {
... query:{status: "A"},
... out: "order_total"
... }
... )
{
        "result" : "order_total",
        "timeMillis" : 1178,
        "counts" : {
                "input" : 3,
                "emit" : 3,
                "reduce" : 1,
                "output" : 2
        },
        "ok" : 1
}
>

Note: Starting in MongoDB 2.4, certain mongo shell functions and properties are inaccessible in map-reduce operations. MongoDB 2.4 also provides support for multiple JavaScript operations to run at the same time. Before MongoDB 2.4, JavaScript code executed in a single thread, raising concurrency issues for map-reduce.

Single Purpose Aggregation Operations

MongoDB also provides db.collection.count() and db.collection.distinct().

All of these operations aggregate documents from a single collection. While these operations provide simple access to common aggregation processes, they lack the flexibility and capabilities of the aggregation pipeline and map-reduce.

> db.orders.distinct("cust_id")
[ "A123", "B212" ]

Cursor

As MongoDB BOL Iterate a Cursor in the mongo Shell The db.collection.find() method returns a cursor. To access the documents, you need to iterate the cursor. However, in the mongo shell, if the returned cursor is not assigned to a variable using the var keyword, then the cursor is automatically iterated up to 20 times to print up to the first 20 documents in the results.

The following examples describe ways to manually iterate the cursor to access the documents or to use the iterator index.

Manually Iterate the Cursor

var myCursor = db.orders.find( { Cust_id: "A123" } );

myCursor

You can use the cursor method forEach() to iterate the cursor and access the documents, as in the following example:

var myCursor =  db.orders.find( { Cust_id: "A123" } );

myCursor.forEach(printjson);

  Note : You can use the DBQuery.shellBatchSize to change the number of iteration from the default value 20.

Iterator Index

In the mongo shell, you can use the toArray() method to iterate the cursor and return the documents in an array, as in the following:

var myCursor = db.orders.find( { Cust_id: "A123" } );
var documentArray = myCursor.toArray();
var myDocument = documentArray[3];

The toArray() method loads into RAM all documents returned by the cursor; the toArray() method exhausts the cursor.

Cursor Behaviors

Closure of Inactive Cursors by default, the server will automatically close the cursor after 10 minutes of inactivity, or if client has exhausted the cursor. To override this behavior in the mongo shell, you can use the cursor.noCursorTimeout() method:

var myCursor = db.orders.find().noCursorTimeout();

After setting the noCursorTimeout option, you must either close the cursor manually with cursor.close() or by exhausting the cursor’s results.

To know Cursor Information from MongoDB Server

The db.serverStatus() method returns a document that includes a metrics field.

db.serverStatus().metrics.cursor

The result is the following document:

{
   "timedOut" : <number>
   "open" : {
      "noTimeout" : <number>,
      "pinned" : <number>,
      "total" : <number>
   }
}

As finally, In Aggregation operations group values from multiple documents together, and can perform a variety of operations on the grouped data to return a single result.
The pipeline provides efficient data aggregation using native operations within MongoDB, and is the preferred method for data aggregation in MongoDB.

where in the mongo shell, if the returned cursor is not assigned to a variable using the var keyword, then the cursor is automatically iterated up to 20 times to print up to the first 20 documents in the results.
The toArray() method loads into RAM all documents returned by the cursor; the toArray() method exhausts the cursor.


restore could not start database Restore failed for Server 'servername'.  (Microsoft.SqlServer.SmoExtended)
  System.Data.SqlClient.SqlError: RESTORE could not start database 'ECP'. (Microsoft.SqlServer.Smo)

The database is created, but is not accessible using Object Explorer:

  The database ECP is not accessible. (ObjectExplorer)

Thanks for any advice on how I can make the database work.

EDIT: This query

select state_desc from sys.databases where name = 'ECP';

returns RECOVERY_PENDING.

I deleted the database again and had a new try with

restore verifyonly from disk = 'path\file';

This returns

  Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.
  The path specified by "F:\ECPDATA1\ECPDATA1.mdf" is not in a valid directory.
  Directory lookup for the file "F:\ECPDATA2\ECPDATA2.ndf" failed with the operating system error 3(failed to retrieve text for this error. Reason: 15100).
  Directory lookup for the file "F:\ECPDATA3\ECPDATA3.ndf" failed with the operating system error 3(failed to retrieve text for this error. Reason: 15105).
  Directory lookup for the file "F:\ECPDATA4\ECPDATA4.ndf" failed with the operating system error 3(failed to retrieve text for this error. Reason: 15105).
  Directory lookup for the file "E:\ECPLOG1\ECPLOG1.ldf" failed with the operating system error 21(failed to retrieve text for this error. Reason: 15105).
  The backup set on file 1 is valid.

Since it says, that the backup set is valid, I just have to get the files specified, to make it work, right? But on this server I don't have a drive E: or F:. How can I adjust this in the backup file? Or is it possible (I'm not that familiar with windows-server-2008-r2) to have some kind of symlinks like on linux?

Your restore is failing because there is no E:\ or F:\ on your machine.

Perform a RESTORE FILELISTONLY FROM DISK = 'path\file'; - this will tell you the logical names of the data and log files.
Construct a RESTORE DATABASE command based on this. For example (assuming the data files are named ECP_Data,ECP_Data_2, etc.):

RESTORE DATABASE ECO FROM DISK = 'path\file'
  WITH REPLACE, RECOVERY,
  MOVE 'ECP_Data'   TO 'C:\...path to MSSQL\Data\ECP_Data.mdf',
  MOVE 'ECP_Data_2' TO 'C:\...path to MSSQL\Data\ECP_Data2.ndf',
  MOVE 'ECP_Data_3' TO 'C:\...path to MSSQL\Data\ECP_Data3.ndf',
  MOVE 'ECP_Data_4' TO 'C:\...path to MSSQL\Data\ECP_Data4.ndf',
  MOVE 'ECP_Log'    TO 'C:\...path to MSSQL\Data\ECP_Log.ldf';

In addition to correcting the logical file names, replace C:\...path to MSSQL\Data\ with whatever path(s) you do want to use to store the data/log files. You don't need an individual folder per file, but you may still want to separate data and log files onto different disks. Note that it still might fail if whatever drive(s) you choose lack sufficient space to perform the restore.


Creating a FIFO List in MySQL I work in Transportation/Freight and we have these things called "PRO" numbers or freight bill numbers, which you can think about as and Invoice and Tracking number for a shipment.

Our carriers send us blocks of 2,000 sequential PRO #s at a time. Previously, we'd just work from Excel and mail-merge basically just printing off the barcode and changing the cell's color to keep track of what's been used.

We want to automate this process now in the system that creates the shipping documents. Creating the list of PRO #s in MySQL is easy. The problem is that each PRO # can only be used once, but we have multiple people generating shipping documents at any given time.

How can I retrieve a single PRO # from the database and be certain I've only ever used it once?

I'm open to other solutions that are not SELECT based. Maybe I can game the AUTO_INCREMENT system MySQL uses and use an INSERT based system? That would possibly be easiest to implement but I think it could possibly cause problems and could possibly get quite messy and tedious to maintain.

EDIT:
This is a Multi-Tenant system and transactional as well.

From here, you can do something like this

mysql> CREATE TABLE autoinc (id MEDIUMINT NOT NULL AUTO_INCREMENT, sample INT, PRIMARY KEY(id));
Query OK, 0 rows affected (0.34 sec)

mysql> INSERT INTO autoinc VALUES(NULL, 5);
Query OK, 1 row affected (0.05 sec)

mysql> SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+
1 row in set (0.01 sec)

mysql>

[EDIT - in response to OP's comments]

"To start with an AUTO_INCREMENT value other than 1, set that value with

CREATE TABLE or ALTER TABLE, like this:
mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;"

Also, this might help


MySQL Database Design Help? I am going through my database and what I want to do is re-design it because I feel like it's very messy and that I am doing it wrong. This is just for practice; I've never had anyone teach me so I would love to learn new ways and how to approach this.

What I have is a users table with the following fields:

id, admin, active, username, password, business_name, address, city, state, zip, phone, email, hours, hours2, credit_cards, logo_ext, package, theme, header_image, facebook_link, twitter_link, analytics, analytics_id, website_url, banner_image, member_forgot, home_page, about_page, contact_page

Wow, pretty messy right?  Here are a few things I see:

admin and active is just a bunch of 0's and 1's so can I do something like this? Since there will be 99% active and only a couple that will be inactive.

admin  - id, user_id

inactive - id, user_id

social - id, user_id, facebook_link, twitter_link

member_forgot - id, user_id, forgot_code

I'm not too sure about the rest.   Just to explain a few of the fields:

Hours = Monday through Friday hours
Hours2 = Saturday hours
Credit_Cards = 1110 format (visa, mastercard, discover, amex)
logo_ext = jpg, png - stores it when you upload logo
package = 1, 2, or 3. Different level. Each level has its own template.
theme = what stylesheet to use
header_image = which image to use on the header for that account.
analytics_id = I use piwik, so piwik stores a unique id that I need to store
banner_image = 1-30 of different images there are to choose from
pages = stores the text in the database, which they can change via wysiwyg editor.

I really want to practice splitting it up in different tables even if it is not a huge database, or is that a bad idea? So if I delete a user from the users table, it will delete all of the other records in the other tables associated with that user_id?

Any suggestions / help is appreciated.  I love to learn new things and am very interested in making this database more efficient and flexible.

Thanks so much.

admin and active is just a bunch of 0's and 1's so can I do something
  like this? Since there will be 99% active and only a couple that will
  be inactive.

  admin - id, user_id
  inactive - id, user_id
  social - id, user_id
  facebook_link, twitter_link
  member_forgot - id, user_id, forgot_code

Actually no, I wouldn't do this and keep it in the same table. The flag should be enough to distinguish between these two. The only difference in your queries is a active = true. Personally, I wouldn't consider this a bad design choice (altough your idea is not wrong, I would not use it in this case with - I'm guessing - a small set of data). Since only on set of data belongs to a single user, JOINs would make not that much sense.

  I really want to practice splitting it up in different tables even if
  it is not a huge database, or is that a bad idea? So if I delete a
  user from the users table, it will delete all of the other records in
  the other tables associated with that user_id?

Again, I wouldn't do that personally(!). I don't see a benefit of seperating user centric data from the user. If you fear losing the data on deletion, introduce a deleted flag. This way, you will never lose data and your application just respects the deleted flag in your queries (this would also apply if you split the data accross tables).

  Credit_Cards = 1110 format (visa, mastercard, discover, amex)

Storing credit cards is always very sensitive, according to PCI (as far as i remember) you are not allowed to store the CVV code (hope someone can correct me on that). But since this seems to be a fictional application, that should not be a problem.


Select column from current table to a related table I have this schema:
CREATE TABLE `players` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `email` varchar(255) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `email` (`email`)
);

CREATE TABLE `teams` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT ,
    `player_id` int(10) unsigned NOT NULL,
    `message` text NOT NULL,
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
);

I need to query teams table, where player_id has email barcelonafc@outlook.com, and also sorting it by the last 30 days of creation in the database.
Right now I have this query:
SELECT *
FROM teams
WHERE created_at BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND NOW();

This query is fine for selecting from teams and for filtering by the last 30 days of creation.
But, I can't figure out how to actually access players from it, and then filter by email='barcelonafc@outlook.com' as well.
I know it might be a dumb question, but, any ideas?
Should I change the schema for this?
It is a MySQL database.

Let’s join on player_id
SELECT *
  FROM teams t INNER JOIN players p ON t.player_id = p.id
 WHERE t.created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
   and p.email = 'barcelonafc@outlook.com'

Be sure to verify if the results should be filtered on players added within the last 30 days or teams.


Best way to encrypt some selected rows I know how to create a new column based on an old one , encrypt it after creating a master key/certificate and etc.
I would like to know what would be the best approach to encrypt ceartin columns without the need to create a new one.
I mean, is there a way to encrypt a column, and during a select, we see encrypt data or something like J&Y@HS@H&HS and only some users can see it?
I'm not talking about TDE in the entire database or backup encryption.
Thanks.

Dynamic Data Masking (DDM) can fulfil the requirement as long as the Least Privilege Principle followed, since by default members of sysadmin server role and members of db_owner database role can always see UNMASKED (Including Encrypted columns).
If the requirement as such that even sysadmin role members must only see the encrypted data then the better fit will be Always Encrypted feature...
Or, if the goal is just to protect some sensitive data within database when it restored into DEV/UAT environments, better look for reliable third-party solution, or you can also develop you're own stored procedure/script that can be executed (manually) in DEV DB every time a restoration happened.


How to represent foreign key in an ER diagram? Suppose I have a 'Transactions' table which has a column 'Customer ID' (Foreign Key) and a Customer Table having 'ID' (Primary key). How do I show the relation between the two tables and showing that the 'Customer ID' is the foreign key of 'Transactions' Table which is the primary key in the 'Customer' table?

I googled this question and also checked this forum for my query but couldn't find an exact example with a diagram addressing my question.

Please explain me, if possible, with a diagram.

ER Diagrams were originally used only to represent the ER model.  The ER model does not use foreign keys to represent relationships.  It uses lines between boxes.  The lines have some kind of indicator for cardinality at either end or both ends.  Sometimes, a relationship will be indicated separately by a diamond.

Today, more than half of the ER diagrams floating around are really diagrams of a relational model, and not of an ER model.  A relational model has the foreign keys included in the tables, and these serve to implement the relationships which the ER model identifies.  And a relational model will have an extra table, often called a "junction table" between two entity tables that are linked by a many-to-many relationship.  This junction table contains two or more foreign keys.

There are many ways to represent a relational model. Perhaps the simplest is the "Relationship Diagram" that MS Access can produce from a completed database.  This will be fairly complete, if the database builder has identified the foreign keys.

There are many tools that are more sophisticated than MS Access for making diagrams on a larger scale.  Some of these are used before building the database.  Some are used after.


How to determine which statement is blocking an update in SQL Server We have an UPDATE statement running in a large J2EE application that is getting blocked by some other statement. The update is using the primary key, so it should affect only one row. The isolation level being used in the application should be READ UNCOMMITTED, so it should only be another modification that is causing the block, but we can't see where.

We have obtained a report of All Blocking Transactions and this indeed shows a session blocking an update. Using the DBCC INPUTBUFFER command shows the last statement executed by the blocking session, but this is an SELECT on another table than the UPDATE, so I think it can't be the cause. I presume it must be a statement earlier in the transaction.

At this point, my SQL Server knowledge runs dry, and I also have problems reproducing the issue; it occurs intermittently.

What steps should I know take to find the blocking statement?

Before you run your update:

SELECT @@SPID;

Now, run the update, if it is not completing quick enough, then in another window run:

SELECT status, command, wait_type, last_wait_type, blocking_session_id
  FROM sys.dm_exec_requests
  WHERE session_id = <@@SPID from above>;

If you get a value in blocking_session_id, then you can run the same query for that SPID. You can also say:

DBCC INPUTBUFFER(<blocking spid>);

(Well, as long as you're not on SQL Azure.)

If this doesn't yield a clue, then it's possible the blocking statement was actually at a different point in a larger transaction. You'll have to track that down by figuring out where that spid is executing from and whether it's a batch of ad hoc T-SQL, a stored procedure, etc. There's not really any magic "look here!" we can provide you.

If it's not getting blocked, you may find relevant info in the other columns that can help determine why it's slow (e.g. perhaps it is waiting on a ginormous log autogrow event).


Does using the in operator affect the use of indexes? Does using the in operator affect the use of indexes? For example, is it possible for WHERE id IN (10) to cause the optimizer to ignore an index?

Specifically, I'm trying to understand this answer from StackOverflow. The question asks why an index is not being utilized (in MySQL), and the answer suggests that it could be due to the use of in with only one value. Eg WHERE id IN (10) might get the index ignored, while WHERE id=10 would be fine.

I've briefly looked around on Google, and skimmed through some MySQL docs, but I can't find any reference to in affecting the optimizer's decision to use an index. Neither with a single value, nor multiple values.

Assuming that the IN values are the same datatype as the column they are comparing, can they affect the index usage?

The question that I linked to was for MySQL, but I work in other DBs, so I'd be interested in knowing if this is a universal thing to note about indexes, or if it's a quirk with MySQL.

If that happens in MySQL then it's a quirk to MySQL.  Since you asked about other databases as well I decided to test it out on my postgres database.  On a table with a little over 70 million rows it produces exactly the same explain for the following two queries (with the index):

explain select * from myschema.my_list where my_column in (232);
explain select * from myschema.my_list where my_column = 232;

which is:

'Bitmap Heap Scan on my_list  (cost=68093.11..4109456.90 rows=1656844 width=994)'
'  Recheck Cond: (my_column = 232)'
'  ->  Bitmap Index Scan on my_list_2  (cost=0.00..67678.90 rows=1656844 width=0)'
'        Index Cond: (my_column = 232)'

It doesn't diverge until I add in multiple elements in the list:

 explain select * from myschema.my_list where my_column in (232,79);
 explain select * from myschema.my_list where my_column = 232 or my_column = 79;

which is (respectively):

'Bitmap Heap Scan on my_list  (cost=172208.28..7050980.15 rows=4290987 width=994)'
'  Recheck Cond: (my_column = ANY ('{232,79}'::integer[]))'
'  ->  Bitmap Index Scan on my_list_2  (cost=0.00..171135.53 rows=4290987 width=0)'
'        Index Cond: (my_column = ANY ('{232,79}'::integer[]))'

'Bitmap Heap Scan on my_list  (cost=177390.73..7066890.07 rows=4230402 width=994)'
'  Recheck Cond: ((my_column = 232) OR (my_column = 79))'
'  ->  BitmapOr  (cost=177390.73..177390.73 rows=4290987 width=0)'
'        ->  Bitmap Index Scan on my_list_2  (cost=0.00..67678.90 rows=1656844 width=0)'
'              Index Cond: (my_column = 232)'
'        ->  Bitmap Index Scan on my_list_2  (cost=0.00..107596.63 rows=2634142 width=0)'
'              Index Cond: (my_column = 79)'

I would take his answer with a grain of salt.


Only Return Rows Where Row Count >= 2 Table has duplicate data in it.  I am in need of identifying the data that is duplicated in a structure like such - identifying each storeID that is duplicated by Hambasa

ABC BLUE 2
ABC Green 2
ABC Orange 2
BET Blue 2
BET Green 2
BET Orange 2

This is sample DDL and the query using Row_Number() I attempted.  What would be the proper way to get the result I am after?  I am actually wanting to delete the duplicates, so that each storeID only has 1 entry for each Hambasa.

    Declare @TMI Table (HAMBASA varchar(10), storeid varchar(10))
Insert Into @TMI (storeid, hambasa) VALUES
('ABC', 'Blue'), ('ABC', 'Green'), ('ABC', 'Orange'),
('ABC', 'Blue'), ('ABC', 'Green'), ('ABC','Orange'),
('NYC', 'Blue'), ('NYC', 'Green'), ('NYC', 'Orange'),
('BET', 'Blue'), ('BET', 'Green'), ('BET', 'Orange'),
('BET', 'Blue'), ('BET', 'Green'), ('BET', 'Orange')

;With pan As
(
    Select HAMBASA, storeid
    ,RN = Row_Number() Over (Partition By storeid Order By HAMBASA ASC)
    FROM @TMI
)

Where's the primary key?

DELETE records FROM (
    SELECT
        ROW_NUMBER() OVER (PARTITION BY HAMBASA, storeid ORDER BY HAMBASA) AS HambasaCount
    FROM @TMI) records
WHERE records.HambasaCount > 1


Where I can find the tnsnames.ora file for my Oracle Windows client? I installed Oracle client for my Windows 7 machine. I can connect to a remote Oracle database using Toad for Oracle using the direct method (giving host, port and service name for the connection).

Now I tried to find the tnsnames.ora file from the Oracle client oracle home, but could not find it. I could not find a NETWORK\ADMIN folder in ORACLE_HOME (C:\app\myaccount\product\11.2.0\client_1).

Where is this file located?

At least for older releases, I'd generally just create my own. It's a plain text file but you probably need to start with an example.


Mirroring and log shipping together We are using SQL Mirroring as an HA solution and Log Shipping as a DR solution.

Recently, the mirror database has been configured as a log shipping primary (the mirror/primary database) to allow log shipping to continue after database mirroring fails over, using the same configuration we used for the primary on the principal database.

Now the problem is every so often the restore job fails due to:

  Exclusive access could not be obtained because the database is in use.

This is because two restore jobs (one from principal/Primary, and one from the mirror/primary) start at the same time. I have changed the job schedules to be run at different times, however.

For example, the restore job from the principal/Primary is scheduled for every 13 minutes and the restore job from mirror/Primary is scheduled for every 10 minutes. But there are still some times that they start at the same time.

Is there any way to prevent this?

Conditionally restore based on which job is running on the principle

If I understand you correctly, I would think that the primary mirror should be the one doing the restoring. If this is the case I would modify your job(s) like so:

if ((select mirroring_role from sys.database_mirroring where db_name(<database id>) = '<database name>') = 1)
    Restore Database XYZ from disk = 'C:\XYZ.bak'

Not knowing your full setup, I "would think" both servers could run this job and it wouldn't matter which job got there first or the timing of the jobs.

Note:

A mirroring_role of 1 signifies the principle whereas a mirroring role of 2 signifies the secondary role.

Edit based on your comment:

If you'd rather keep your jobs "as is", even though one of them sounds redundant, just stack them together in one job with two job steps. If the first step fails, execute the second step. But if the first job step succeeds, don't run the second job step.

I still think conditionally restoring is your best bet, but I'd have to see your job code on both servers to know exactly what your doing. Having both jobs compete to restore the same database doesn't sound like the very best of plans to me.


SQL Query to get last day of every week Using MySQL, I need to query a table that has a datetime field. I need to count all the records for a particular week. Using date_format(colname, "%Y %V") returns the week #, but I need the last day (day of month 01-28|29|30|31) for that week.

Output E.g.

    COUNT(*) | TheDate
    11       | 2012-01-07
    22       | 2012-01-14
    123      | 2012-01-21

Help?

Note: Last day of the week in my case is Sunday.

You need to use the DAYOFWEEK function

If the End of the Week in Saturday, any date can be morphed into a Saturday.

For example, this computes the upcoming Saturday

SELECT DATE(NOW() + INTERVAL (7 - DAYOFWEEK(NOW())) DAY);

This computes the upcoming Sunday (you must use WEEKDAY function instead)

SELECT DATE(NOW() + INTERVAL (6 - WEEKDAY(NOW())) DAY);

For a table called mytable with a Date Column called theDate, your summation would be

Saturday

SELECT COUNT(1) DateCount,EndOfWeekDate FROM
(
    SELECT DATE(theDate + INTERVAL (7 - DAYOFWEEK(theDate)) DAY) EndOfWeekDate
    FROM mytable
) A GROUP BY EndOfWeekDate;

Sunday

SELECT COUNT(1) DateCount,EndOfWeekDate FROM
(
    SELECT DATE(theDate + INTERVAL (6 - WEEKDAY(theDate)) DAY) EndOfWeekDate
    FROM mytable
) A GROUP BY EndOfWeekDate;

Give it a Try !!!

CAVEAT if you want any other end of week other than Saturday and Sunday, I wrote an insane algorithm back in Sep 22, 2011 for computing any week starting and ending whatever day.


SQL Replication - Distribution database backlog of commands for one table Our infrastructure is as follows:

Publisher on Live db server, Distibutor and subscriber on our mirror server.

Basically yesterday we were doing archiving on a huge table on our live server, (tblEventlog), and I was deleting rows month by time, millions of records. This caused the transaction log to fill up completely...so when we restarted server and got the space back, we now have 144225820 commands in our distribution database (on mirror)...and estimated time of 27 hours to apply and catch up.

Basically I am looking for the quickest fix to this - I know the offending table is tblEventLog. Can we drop this from the mirror and then export a copy (with the deletes applied) from Live...would this have any effect on the transactions waiting in the distribution db? Would the distributor still try and apply them even though the rows are now gone, or would it just skip them?

If so, whats the best way out of this...would rather not reinitialise as its a huge db and stresses live server.

Publisher is SQL Server 2008, Subscriber server is SQL Server 2012.

When it comes to replication you have options on how you want to initialize transactional replication. You can do that with a snapshot or from a backup. Only focusing on these two methods as they are the most common.

You may want to consider initializing from a backup to help save time, and to allow your distributor to only have to keep records of what was changed for the time it takes to restore the database.

Microsoft Article that explains the concept

MSSQL Tips article specific for SQL 2008


Import Tables without Constraints from Schema Export I am trying to do an import using data pump, but I am struggling to come up with the right options.  The export was taken as a schema export.  I am trying to import just the tables, with no other database objects.
I am currently using
INCLUDE=TABLE
TABLE_EXISTS_ACTION=REPLACE
REMAP_SCHEMA=
REMAP_TABLESPACE=

This is correctly importing the tables, but I am a little surprised to see that the import is also creating the constraints and triggers on the tables, or trying to at least.  The triggers are failing because they include a schema name that does not exist in the database.
I thought that by using INCLUDE=TABLE only the tables would be included.  Apparently that is not the case.  I am unable to use EXCLUDE=CONSTRAINT or EXCLUDE=TRIGGER as I am already using INCLUDE to limit the import to just tables.
Any ideas on how I could structure the import to only import tables?

You are right when stating I thought that by using INCLUDE=TABLE only the tables would be included, but it includes all related objects too. This means that constraints and triggers will be created for you when you use INCLUDE=TABLE when importing.

Possible Solution
If you have the possibility to re-create the dump using the expdp command then you might want to just export the tables of the required schema and use the INCLUDE parameter.

INCLUDE
Purpose:
Enables you to filter the metadata that is exported by specifying objects and object types for the current export mode. The specified objects and all their dependent objects are exported. Grants on these objects are also exported.

exp_include.par
This is basically the expdp parameter file.
FULL=N
...
SCHEMAS=<your_schema(s)>
INCLUDE=TABLE
...

This will create a dump of the required schema and contain only tables and dependant objects.

After you have generated the required *.dmp file, import using the EXCLUDE=... option.

EXCLUDE
Purpose:
Enables you to filter the metadata that is imported by specifying objects and object types to exclude from the import job.

imp_exclude.par
This is the parameter file for the impdp.
FULL=Y
...
EXCLUDE=STATISTIC
EXCLUDE=CONSTRAINT
EXCLUDE=TRIGGER
...

This should result in the tables being imported correctly.
Reference Material

2 Data Pump Export (Oracle Docs)
3 Data Pump Import (Oracle Docs)


SQL Server 2017 Enterprise vs Standard - Online Indexing and Online Schema Change I read through Microsoft's "Editions and supported features of SQL Server 2017" doc to compare the feature differences between Enterprise and Standard edition.

Two things not available in Standard that caught my eye were:

Online indexing
Online schema change

Does this literally mean you can't create and modify indexes or tables (and other objects?) without taking the database offline first, or does Microsoft mean there are only specific operations you can't do and if so where can I learn more about which operations require taking the database offline?

The confusion seems to be with the verbiage - online/offline is sort of confusing in this context.

It refers to whether the operations being performed (a reindex or a table alter) are performed online or not, and not whether the state of the object is online or offline. Online means that the object does not have an exclusive lock on it, and is possibly accessible to other queries/operations as a result. Offline means it is locked by the operation and can't be touched by other queries no matter what.

The benefit for Enterprise is that I can do my schema changes or index maintenance and not (potentially) adversely impact other queries running. That doesn't mean that other, less restrictive, locks won't be taken (row level, page level), but it does mean that I'm not totally boxed out of the object. It does not guarantee things will keep running the same, though, as many other factors play into this. Check out the individual commands you're interested in running to see the myriad options that may effect locking behaviors.

For example, I've seen an online index reorg end up effectively locking the table from all other SELECT statements until I tuned the MAXDOP it was using, even though it wasn't actually taking a lock on the whole table.

An object can't be taken online or offline, although a database can (not relevant to this topic though).

Resources:

How Online Indexing Works
Online ALTER COLUMN Operation


How to change mysql password I am on OS X 10.9.5 and I did a reinstall of mysql2 and I provided a password during installation but it is not working when I run the command mysql -uroot -p. The result I get is: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Does someone know how I can reset the mysql password? I have been searching posts all day on how to fix my issues but nothing has helped.

Here are the exact steps I had to run to get it working:

sudo /usr/local/mysql/support-files/mysql.server stop

sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables

In new terminal:

mysql -u root

UPDATE mysql.user SET Password=PASSWORD('my-new-password') WHERE User='root';
FLUSH PRIVILEGES;

\q

sudo /usr/local/mysql/support-files/mysql.server start


See X-Request-ID in error messages of PostgreSQL In my environment most PostgreSQL queries are generated from http requests.

In my case every http request has a unique header: X-Request-ID

If there is a error or warning in the PostgreSQL logs, I would like to see the related X-Request-ID to see where the sql query came from.

How to add an extra message like X-Request-ID to log messages of PostgreSQL?

Related: https://serverfault.com/a/797611/90324

Postgres knows nothing about how your application generates the SQL requests that are sent to the database engine. So it also has no idea that there is a HTTP request behind it, let alone the header of such a request.

The information that can be put into a log line is fixed and documented in the manual

The only "log variable" that you can dynamically change, is the application name. However that requires that your application sets that each time it processes a request.

Before running a SQL query, your application needs to run something like this:

set application_name = 'request-42';

where 'request-42' is the request ID you extracted from the HTTP request.

Once you have that, you can add the %a to log_line_prefix and it will be shown in the log file.


Run Mongodb Server as Service and mongod instance I am using ubuntu and can run mongodb server by the following command

systemctl start mongodb

But, if i want to run Mongodb by mongod

mongod --dbpath /var/lib/mongodb

it gives me Permission Denied error.
Adding sudo before the mongod command fix the error and the Server starts.
But now, earlier command

systemctl start mongodb

won't work and says too many files open

So, I change the owner of the folder /var/log/mongodb and /var/lib/mongodb by the following commands.

sudo chown -R  mongodb:mongodb /var/log/mongodb
sudo chown -R  mongodb:mongodb /var/lib/mongodb

Now, MongoDb server started by systemctl command but again mongod command gives me error.
So, Is there any way to run Mongodb Server by both ways i.e, by using systemctl and mongod command.

Thanks

The following command has solved my problem

sudo -u mongodb mongod --dbpath /var/lib/mongodb

The -u mongodb after sudo runs the mongod with mongodb user. So,I don't have to change owner of folder /var/lib/mongodb to mongodb.

Now, I am able to start Mongodb Server as Service and also by mongod.


DB Optimization with 2 to the power of N - Approach clarification This is a request for analysis and better performance approaches, one of my colleague has come up with an approach in optimizing the DB and it looks very impressive too. Can someone please let us know whether this a better approach for the explained scenario in the link below,

DB Optimization Blog Link

If there are another better option kindly advice.

So that solution is:

Creating a Bit Array in the user table to register a user to a health plan.
Then mapping the binary digit position to the health plan table.

Benefits:

Very little storage required, You might save $20 over the lifetime of the project.
Faster updates/deletes albeit perhaps not measurable.
Faster queries when not selecting by Health Plan

Problems:

The maximum number of distinct plans has to stay small. (32 or 64 depending on field size)
Nowhere to store plan start date, plan end date or other relevant data that you will probably need.
You need to decode the plan every time you use or apply it using bit operations.
There is no quick and easy way to do generic type queries that link the tables quickly.
Slower queries for health plan driven queries (i.e. all 'H10' Plans) without special indexes and query tools.

Issues with the original premise:

When subscribing to a new plan / leaving a plan you can just insert or delete the 'changed' records.

  Insert will add a new record ('U1',3).

  Delete will only need to delete the relevant record User_Health_Plan_Mapping(U1,10)

You seem to assume that db optimization is about minimizing the storage, it is more about the following.

Placing data so that it is easy to understand.
Storing it long term (think 5 generations of the code that talks to it)
Making the data extensible, supportable, searchable, reliable, secure, robust, backed up.
Optimizing the whole ecosystem (time to market, development time, operational time, hardware cost, business process, call center hours)

Optimizing the tables / indexes for the specific use-cases that is required of your application. Which is not about processor power, but indexes, direct access to hard disk and cache optimization (unless you have a trivial sized database that can stay in memory).


How to suppress an informational raiserror message I have a setup script that calls sp_configure a few times to set up some system variables, it works fine but I have a few of the following statement

  Configuration option '%ls' changed from %ld to %ld. Run the RECONFIGURE statement to install.

with the variables filled in accordingly. Internally I can see sp_configure is calling

raiserror(15457,-1,-1, @configname, @prevvalue, @configvalue) with log

Is there anything I can do to prevent those informational messages (which are useless to the script, I call RECONFIGURE from inside the script) from displaying in the messages window in MSSMS when a user runs the script?

No. Informational messages cannot be suppressed.


Remove field from composite key and collate duplicate data I'm looking for the best way to go about changing the primary key on a table in my DB without having to completely reinsert data from files again.  The modification I'm going to make is going to remove part of the primary key, which will result in tens of thousands of rows which need to be collated.

The part of the key being removed is a date field, which originally was needed to help uniquely identify incoming data.  However, the way this field is assigned at the source (outwith my control) has since changed and we now want to only keep the earliest occurrence of a particular record and discard the later occurrences.

The rest of the fields in the key (4 FK's) will produce unique records.

I'm assuming I'll need to transfer the data to another table, make the change and then re-insert?  Or is there a better way?

If the four records dictate a unique and you want the first one by date, then why not insert into another table, truncate, then insert back once you finish the table reconfigure (or create a new table with the right columns, insert into, then rename after dropping?) I don't think this can be improved upon, unless you want to delete from where not equal to, but the insert into should be faster (needs to be tested to prove for sure)


Several Questions about SGAM & GAM & IAM & PFS in SQL Server Have several questions about internal structure of SQL Server.

Lets assume that we have a table in a database.

SGAM & GAM pages keep track of shared & uniform extents within a single GAM interval(~4GB), and help us find appropriate type of extent when allocating pages/extents(initially first 8 pages are allocated from mixed extents & positions of those pages are recorder to IAM pages, and after that from GAM extents(uniform extents) and this information is stored in next chained IAM page).

Let's say, pages for the table were allocated, and table size is more than 8 pages, and sometime later, rows were deleted, leaving some re-usable space. How SQL Server knows that extents have free space ?

Pages have information about the size of free space, but it is too time consuming to check every page. Bits in SGAM & GAM pages don't tell us anything beside that extend is allocated or has some free pages(entire page is free, not partially).
File header & SGAM & GAM & IAM pages are among the first pages within a file. What data-structure actually points to them ?

How SQL Server knows that extents have free space ?

SQL Server tracks free space on the page, in heap tables as well as in LOB pages using PSF page internally.
Also, you can use sys.dm_db_file_space_usage DMV to find out total_page_count, allocated_extent_page_count as well as unallocated_extent_page_count within a database.

Page Free Space (PFS) pages record the allocation status of each page, whether an individual page has been allocated, and the amount of free space on each page. The PFS has one byte for each page, recording whether the page is allocated, and if so, whether it is empty, 1 to 50 percent full, 51 to 80 percent full, 81 to 95 percent full, or 96 to 100 percent full.
A PFS page is the first page after the file header page in a data file (page number 1). This is followed by a GAM page (page number 2), and then an SGAM page (page 3). There is a PFS page approximately 8,000 pages in size after the first PFS page. There is another GAM page 64,000 extents after the first GAM page on page 2, and another SGAM page 64,000 extents after the first SGAM page on page 3. The following illustration shows the sequence of pages used by the Database Engine to allocate and manage extents.

Click here to enlarge - output using DBCC PAGE

Refer to : Inside The Storage Engine: GAM, SGAM, PFS and other allocation maps

File header & SGAM & GAM & IAM pages are among the first pages within a file. What data-structure actually points to them ?

I do not get this question. The smallest unit of storage is a page. Eight 8K Pages form an extent. Below is what comprises of a data page:

I would highly recommend to read SQLSkills.com blog wherein Paul and his team blogs a lot about SQL Server internals.


SQL Server TRUNCATE TABLE permissions Is it possible to configure SQL Server user so that he doesn't have access to DDL statements but has access to run TRUNCATE TABLE commands. I know that behind the scenes it's a DDL.

I wasn't able to find this on search engines.

You could use this technique by Jonathan Kehayias to create a stored procedure that will allow the user to TRUNCATE a table.

As Johathan says:

  "You can't grant truncate because for whatever reason, that permission
  doesn't exist.  What you can do is use a stored procedure and use
  EXECUTE AS OWNER to get around it."

Here is the sample code from the above post.

CREATE DATABASE foo
GO

CREATE LOGIN foobar
    WITH password = 'Pa$$w0rd';
GO

USE foo
GO

CREATE user foobar
FROM LOGIN foobar;
GO

CREATE TABLE test (rowid INT identity)
GO

INSERT INTO test DEFAULT
VALUES;
GO

SELECT *
FROM test
GO

CREATE PROCEDURE dbo.truncate_test
    WITH EXECUTE AS OWNER
AS
TRUNCATE TABLE test
GO

GRANT EXECUTE
    ON dbo.truncate_test
    TO foobar
GO

EXECUTE AS LOGIN = 'foobar'

EXECUTE dbo.truncate_test

REVERT
GO

SELECT *
FROM test
GO

USE master
GO

DROP DATABASE foo

DROP LOGIN foobar


Difficulty Understanding Oracle DQL Procedures and Functions Coming from T-SQL I am much more familiar with SQL Server, but I am doing a project now in Oracle. There is a concept from SQL Server that I am not sure how to accomplish in Oracle.

In SQL Server if I create a procedure

CREATE PROCEDURE usp_GetSomeStuff
(
    @start_date DATE
    @end_date DATE
)
AS
BEGIN
SELECT
  Customer
, SUM( WidgetsPurchased ) AS WidgetsPurchased
FROM WidgetSales
WHERE TransactionDate >= @start_date
  AND TransactionDate <= @end_date
GROUP BY
  Customer
END

(Imagine a more complex query is involved). This gives application code a clean interface for 'selecting' this data without getting involved in a bunch of SQL. The application can just run usp_GetSomeStuff '2018-01-01', 2018-12-31' and get the results.

Oracle, on the other hand has a separation between the procedural language and the database language, and this is quite foreign to me.

When you are inside of a PL/SQL block you cannot perform DQL (unless assigning to cursors or variable).

For example, this is not legal:

create or replace procedure get_some_stuff
(
      in_start_date date
    , in_end_date date
)
is
begin
    select
      customer
    , sum( widgets_purchased ) as widgets_purchased
    from widgetsales
    where transactiondate >= in_start_date
      and transactiondate <= in_end_date
    group by
      customer
    ;
end;
/

PLS-00428: an INTO clause is expected in this SELECT statement

What is the 'Oracle Way' way of 'wrapping' a complex query for a caller that wants the results as if the caller had executed the select statement itself?

Note that we could also do this in a table valued function in SQL Server, which is even more convenient sometimes because we can call it as

select
 only
,these
,columns
from GetSomeStuff('2018-01-01', '2018-12-31')
where xyz = 123

With Oracle I just don't 'get it'. What's the pattern for doing this kind of thing in Oracle?

I am using Oracle 12c.

Oracle supports "table valued functions" as well. However they need a lot more boiler plate code than one would like. The main (annoying) "problem" is, that you need to explicitly define a type that can be returned:

create type stuff_type as object
(
  customer varchar(50),
  widgets_purchased number
)
/

create stuff_list as table of stuff_type
/

create function get_stuff(p_start_date date, p_end_date date)
   return stuff_list
   pipelined
as
begin
  for rec in (select customer, sum(widgets_purchased) as widgets_purchased
              from widgetsales
              where transactiondate >= p_start_date
                and transactiondate <= p_end_date
              group by customer)
  loop
    pipe row stuff_type(rec.customer, rec.widgets_purchased);
  end loop;
end;
/

The usage is also a bit different:

select *
from table(get_stuff(current_date - 7, current_date));

Some more examples and insights can be found here: https://oracle-base.com/articles/misc/pipelined-table-functions

Starting with Oracle 12.1 you can also use implicit results:

create or replace procedure get_stuff(p_start_date date, p_end_date date)
as
  c1 SYS_REFCURSOR;
BEGIN
  OPEN c1 FOR
    select customer, sum(widgets_purchased) as widgets_purchased
    from widgetsales
    where transactiondate >= p_start_date
      and transactiondate <= p_end_date
    group by customer;

  DBMS_SQL.RETURN_RESULT(c1);
END;
/

Some more details and examples for implicit results: https://oracle-base.com/articles/12c/implicit-statement-results-12cr1


No properties option in Index context menu I am attempting to analyze the indexes of several of my tables according to a Microsoft article that describes Reorganizing and Rebuilding Indexes. The article says to right-click on the index within SSMS and then select properties. However, I don't see any option for properties on the right-click menu. I am using SSMS v17.1 with an Azure SQL Server v12.0.2000.8 (which I believe is SQL Server 2014).

This functionality is not available for Azure yet.
You have to use INDEXPROPERTY.  Explained here with example for Azure.

On a side note Azure SQL Server v12.0.2000.8 is not SQL Server 2014. You cannot compare versions between Azure and On-premises.  Rather you should compare features if you have to.


Order by after concatenating columns After concatenating column how do i order by using one of the columns? Below is select statement i was trying to come up with.

SELECT * FROM (with tbl_demo AS
    (SELECT eod_date,
    branch_code branch_code
    , eoc_batch
    ,eoc_stage
    , start_time
   ,  end_time
     from aetb_eoc_programs_history where eoc_ref_no in
     ( select eoc_ref_no from aetb_eoc_runchart_history where eod_date = (select prev_working_day from sttm_dates where branch_code = '000'))
order by eod_date)
SELECT a.bank_code||'|'||param_val||'|'|| eod_date||'|'||
branch_code ||'|'||
       eoc_batch||'|'||
        eoc_stage ||'|'||
      to_char(start_time,'dd-mm-yyyy hh24:mi:ss')
     ||'|'|| to_char(end_time,'dd-mm-yyyy hh24:mi:ss')
        ||'|'|| numtodsinterval((end_time - start_time),'day')
from tbl_demo , sttm_bank  a,cstb_param  b
where end_time IS NOT NULL
and b.param_name = 'RELEASE'
order by  numtodsinterval((end_time - start_time),'day') desc)
where rownum <=10;

It is possible to ORDER BY using the ordinal value of selected fields (even a calculated value) instead of using the field name. It is hard to tell from the query above, but a generic example:

SELECT field1,field2,function(field1) FROM table ORDER BY 3;


PHP-MYSQL messaging system performance problems I want to create a messaging module for the social network web-site project (PHP & MySQL) I've been working on. What substructures do well-known sites use for modules like this?

I think, generating a new record on MySQL for each of new messages will slow down the system and cause problems after a while. What would you suggest me for saving the datas? SQLLite, file system or a high-performance dbms ? (exp: postgresql)

Thanks

You can use a database such as mySQL just fine. Especially if the table structure is simple and indexed well.  I would presume a record for each message would create a need for inserts, but not updates, which would keep index rebuilding needs low too.

If you wanted to scale, just create a read-replica for the querying of the messages and send the write queries to the master server.

Want to scale even more, I suppose you could implement some sort of memcached intermediate level, but this would help more on expanding performance on the read side, not the write side particularly (since you'd have to write them eventually, and you could lose them in memcached in the interim potentially).

In short - mySQL is fine.  Other options include cloud options from amazon like DynamoDB and/or nosql options, but not essential.


In Postgres 10, how do the choice of SERIAL or UUID type as primary key affect replication, if at all? A popular answer on SO states that using UUIDs as primary keys has the following benefit:

  Makes replication trivial (as opposed to int's, which makes it REALLY
  hard)

I have been unable to find any evidence in PG's documentation (or anywhere else) to substantiate this claim.  The PG docs mention nothing about this having any bearing on replication.

For me, if a replica is an exact copy of the master, the type of key used is irrelevant.  So, is there something that escapes me?  How would integer primary keys, compared to UUIDs, make harder to replicate your postgres db?

A related question is whether using UUIDs as primary keys slow down join operations (some people claim they do).  Are there any benchmarks on this?  Can anybody share experiences?

that claim seems spurious.

I do integer IDs with multi-master replication, it's not hard.

I set the step of the sequences to such that two servers cannot use the same id number.

alter sequence foo_id_seq set increment_by=10;
select setval('foo_id_seq',2);  -- or 3 or 4 etc...

but if you can do it natural keys are even easier to replicate.


SQL Server total memory less than target memory, lot of extra memory unallocated The situation is the following:
We have a SQL2008r2 server (64bit on 64bit Windows) with SSRS installed on it. The Max Memory is set to 6GB, the server has 12GB total memory.

The target server memory is always more than the total server memory (for instance total: 2758MB, target: 6144MB = 6GB, which is the max memory).

My problem is that the server has 6GB free memory, but the SQL Server is not using the available space, nor SSRS, or other process. Can anyone tell me is this normal? I've found nothing regarding this topic online.

This is normal.

Max server memory means that your server cannot use more than that amount, not that it must use that amount.  Min server memory doesn't mean it must use that amount, either;  it means that once the server uses that much it doesn't surrender memory to below that amount.  Basically, SQL Server uses the amount of memory it needs until it reaches the upper limit you set, and if it stops using it it might surrender part of it back unless you've set a min memory.  (However, in my experience, it's pretty memory-greedy.)

In short:  This means that you're currently not using 6GB of memory on your SQL Server.


ORA-12528 TNS Listner When I try to connect the database using sql developer it gives the error below:

ORA-12505 TNS:listner does not currently know of SID given in connect descriptor

I have tried all the suggestions that I found on this website, but in vain, they are not working.

I checked the instance and database status, they are started and active. But why I still can not connect to that?

When I try the oracle - default database, that is ok, but I want to connect to my own database, So can you help me to solve that problem?

This means the that the listener is up and running, but the SID or SERVICE_NAME in the CONNECT_DATA section of your net service connect descriptor does not specify a SID or SERVICE_NAME that the listener is aware of.  The listener does not know where to send your connection.

See the full text of the error message:

$oerr ora 12505
12505, 00000, "TNS:listener does not currently know of SID given in connect descriptor"
// *Cause:  The listener received a request to establish a connection to a
// database or other service. The connect descriptor received by the listener
// specified a SID for an instance (usually a database instance) that either
// has not yet dynamically registered with the listener or has not been
// statically configured for the listener. This may be a temporary condition
// such as after the listener has started, but before the database instance
// has registered with the listener.
// *Action:
//  - Wait a moment and try to connect a second time.
//  - Check which instances are currently known by the listener by executing:
//    lsnrctl services <listener name>
//  - Check that the SID parameter in the connect descriptor specifies
//    an instance known by the listener.
//  - Check for an event in the listener.log file.

Hope that helps.


SQL: revoking permissions from public in master By default SQL grants execute permission on stored to public and also select to some table and views in Master to public...I am not 100% sure about this... What is the implication if we have to remove the select privileges on those objects from Public on Master?

Thanks...

As long as you have given any account that needs explicit permissions to those objects (views/procedures) you should see no issues. The issue I usually see after altering PUBLIC role permissions is applications that assume the permissions are there instead of just giving explicit permissions to them.

You will find that some Microsoft documentation states to not alter the PUBLIC  role permissions. I work with the IASE database checklist and the permissions given to the PUBLIC role is one of the first things we have to remove/revoke. I always keep the script handy to restore the permissions if needed.

EDIT
Query to find public permissions that I use sometimes:

SELECT o.name AS [Object], p.permission_name AS [Type]
FROM sys.all_objects o
INNER JOIN sys.database_permissions p ON o.object_id = p.major_id
INNER JOIN sys.database_principals u ON u.principal_ID = p.grantee_principal_id
WHERE u.name = 'public'


Automate the patching of SQL Severs on Windows We are getting approximately 50-100 lower environments SQL Servers newly build.

We are assigned some basic task of SQL DBA which includes one as patching SQL Servers.

How can this be achieved, specially when we have servers, where only Windows authentication can be used whose password needs to be generated every 24 hours. That makes this look complex or may be something I am expecting too.

Please share your inputs how can we achieve this either via powershell, cmd or any best known approach?

Edit: These boxes are all lower env like DEV/SAT/QA. Some of them have log shipping configured but that too for side reporting , even though not that critical. These are not 365*7 and are generally used during the business EST hours.

Also, we have multiple logins each assigned to a DBA who has sys admin rights on these boxes but that login needs to have a password generated which expires every 24 hours.

While the rotating passwords are a hindrance, they don't negate any approaches available.  Kendra Little does a great job of explaining a general approach to patching servers in an automated fashion in this blog post, as well as some of the risks about when server patching goes bad.
Because your servers are not production, these would fall under her "cattle" classification which means that if you can tap into an existing automated toolset such as SCCM/WSUS (as denoted by Alen prior), you should do so.  Patches that don't go through may be annoying, but they hopefully won't cause outages that wake you in the night.  The rotating password complexity may require you customize this to a degree, and really the best approach there is to automate the process as best you can using PowerShell scripts.
Adam Bertram probably has the most robust toolset out there now that I'm aware of in this domain.  I would suggest you check it out and see if you can augment it to your needs.  Here's a direct link to his GitHub repository for the same toolset.


Apply SP3 on SQL Server 2012 AlwaysOn AAGs If Always-On is configured, while applying Service Pack, should I:

Directly move SQL Server Instance (Secondary Replica) to make it passive and apply Service Pack
Or First move Availability Group and then move SQL Server Instance (Secondary Replica) and finally apply patch and restart the Server.

Can you please let me know?

Before an upgrade, you should:

Perform a full backup of every database
Try a manual failover. Make sure the AG fails over a Synchronous replicas and that the synchronization state of the replicas is SYNCHRONIZED
Disable automatic backups on Secondary while they are being upgraded
Disable automatic backups on Primary before the upgrad of Primary
Do not upgrade Primary before any or all of the Secondary or .

Then here is the Rolling Upgrade Process:

Remove automatic failover on all synchronous-commit replicas
Upgrade all remote secondary replica instances running asynchronous-commit secondary replicas
Upgrade the all local replica secondary instances that are not currently running the primary replica
Manually fail over the availability group to a local synchronous-commit secondary replica
Upgrade or update the local replica instance that formerly hosted the primary replica
Configure automatic failover partners as desired


SQL Server Instance Version I'm working with a SQL Server with several instances. Four of my instances are at 10.50.2811.0 and the remaining two are at 10.50.1765.0

I've tried running SQL Server 2008 R2 SP1 and CU 6 to get it updated but the installer wont run. I receive the error:

  To continue, select a feature to install. If all features are already selected, these features are installed.

Basically, you have to install SP1 and then CU6 (as mentioned in my comment)

10.50.2811 ==> 2679367 Cumulative update package 6 (CU6) for SQL Server 2008 R2 Service Pack 1
10.50.1765 ==> 2489376 Cumulative update package 6 (CU6) for SQL Server 2008 R2 note this is for RTM


Improve Performance on GROUP BY - large table PostgreSQL I have a Postgres table containing the following three columns:

origin (INT, b tree index)
destination (INT, b tree index)
minutes (SMALLINT, b tree index)

The table has ~ 1.4 billion rows. Each row contains the information about the travel time (minutes) from one place (origin) to another (destination).

The table is read-only - we might need to change it, but that will happen once a year or so...

I need to do queries like:

SELECT min(minutes)
FROM table
WHERE origin IN([long list])
AND destination IN([long list])
GROUP BY origin

The performance is really bad. Depending on the number of origins and destinations, the query takes more than 10 minutes. What we need is < 3 minutes, ideally < 10 sec. DBMS is postgreSQL 9.4, 16 G RAM.

Is there a way to improve it? Partitioning the table, optimizing indexes etc.? Or another DBMS?

Edited 2018-11-28:

Added Index on (origin, destination)

EXPLAIN ANALYSE SELECT min(minutes),origin FROM table WHERE destination = ANY('{100225471, 100236548, 10263073, 10827564, 7435519, 100272388, 10688802, 10823750, 10853634, 10681223, 100213867, 100234761, 100113775, 100229234, 100234067, 100235418, 100229220, 1000053957, 1000059198, 1000028857, 1000057809, 1000058848, 1000059188, 1000057802}') GROUP BY(origin)

"HashAggregate  (cost=228043.99..228044.04 rows=5 width=6) (actual time=10118.146..10126.308 rows=50022 loops=1)"
"  Group Key: origin"
"  ->  Index Scan using idx_destination on table  (cost=0.60..226716.69 rows=265460 width=6) (actual time=0.022..10021.269 rows=208174 loops=1)"
"        Index Cond: (destination = ANY ('{100225471,100236548,10263073,10827564,7435519,100272388,10688802,10823750,10853634,10681223,100213867,100234761,100113775,100229234,100234067,100235418,100229220,1000053957,1000059198,1000028857,1000057809,1000058848,1000 (...)"
"Planning time: 0.306 ms"
"Execution time: 10127.949 ms"

IN can take a list of values (like you have it) or a set. Long lists don't scale well. See:

Optimizing a Postgres query with a large IN
How to use ANY instead of IN in a WHERE clause with Rails?

Try:

SELECT origin, min(minutes) -- you'll also want to show origin
FROM   tbl
JOIN   unnest($origin_array) o(origin)      USING (origin)
JOIN   unnest($dest_array)   d(destination) USING (destination)
GROUP  BY origin;

Where $origin_array can be an array of the appropriate type int[] or an  array literal like '{100225471,100236548,10263073}'::int[]

The optimal index for this query should be:

CREATE INDEX ON tbl (origin, destination, minutes);

Appending minutes only makes sense if you get index-only scans out of it (introduced with pg 9.2, improved in later versions). But since your table is mostly read-only, it seem like the perfect use case. See:

Can Postgres use an index-only scan for this query with joined tables?

You later commented:

  I want to get the closest (min(minutes)) facility (destination) for each place (origin).

The above query does not achieve that. Only returns the shortest time for any qualifying destination. Use instead:

SELECT DISTINCT ON (origin)
       origin, minutes, destination
FROM   tbl
JOIN   unnest($origin_array) o(origin)      USING (origin)
JOIN   unnest($dest_array)   d(destination) USING (destination)
ORDER  BY origin, minutes;

Depending on cardinalities and value frequencies, this alternative may be faster:

SELECT *
FROM   unnest($origin_array) o(origin)
LEFT   JOIN LATERAL (
   SELECT minutes, destination
   FROM   tbl
   WHERE  origin = o.origin
   AND    destination = ANY ($dest_array)  -- equivalent to IN([long list])
   ORDER  BY minutes
   LIMIT  1
   ) t ON true;

One subtle difference: the last query returns origins from the input array with no match in tbl at all. May or may not be desirable - or even necessary. The last query is my favorite.

Further reading on how to optimize queries of this kind:

Select first row in each GROUP BY group?
Optimize GROUP BY query to retrieve latest record per user

Plus, urgently consider upgrading to a current version of Postgres, like a_horse already suggested. Performance for big data has been improved  substantially since Postgres 9.4.

You mentioned lists of up to 2000 items. Combining two long list like that is problematic since it results in a Cartesian product of 2000 x 2000 = 4000000 possible combinations. If that can happen - sure you need to consider that many combinations? That won't be fast.


How to format sql-plus-spool-file to *.csv with all columns in one line and row-content with linebreaks as one field? I am not a professional, sorry. But perhaps you can help me anyway ...

I have a table with 27 columns and within the rows data with linebreaks. When I try to export selected rows with sql-plus spool, I have several problems. I tried the advices given here: How to make sqlplus output appear in one line?, but they don't help me. I'll explain below.

First the configuration of the SQL plus query:

set term off
set echo off
set underline off
set colsep ';'
set linesize 32767
set pages 10000
set trimspool on
set trimout on
set feedback off
set heading on
set newpage 0
set headsep off
;set wrap off
set termout off
set long 20000

spool D:\tmp_Datenaustausch\Export-141204.csv

SELECT MEDIENNR, ISBN, ISBN2, ISBNSUCH, KATKARTE1, BUCHKARTE1, SIGNATUR1, KATKARTE2,
   BUCHKARTE2, SIGNATUR2, KATKARTE3, BUCHKARTE3, SIGNATUR3, MAB2, FEHLERCODE, FARBE,
   ANZSYKKARTEN, KKSTATUS, BKSTATUS, MABSTATUS, SESTATUS, FARBSTATUS, ASSTATUS,
   REZENSION, REZENSENT, STICHWORTE, UPDDATE FROM Bekartungsdaten WHERE upddate like
   TO_DATE('2014-12-01', 'yyyy-mm-dd');

spool off;
exit;
/

My problems:

Headings are not in one line, "set wrap off" does not help, because it truncates data.
Some fields contain text with linebreaks, sql+ puts the lines of these fields in seperate fields in the csv. I need them in one field with linebreaks like stored in the database.
How do I get quotationmarks at the beginning and end of each field/cell in the csv?

I would be very glad, if some could help me!

1) If you want the headings in one line, you have to use a UNION with a select from dual. Example:

select '"'||'col1'||'","'||'"col2"'||'","'||'"col3"'
  from dual
union
select '"'||col1||'","'||col2||'","'||col3||'"'
  from table;

2) This one is tricky. It helps to know what ascii character your line breaks actually are. Depending on how they were written, generally (but not always) they will be chr(10) or a combination of chr(10)chr(13). You can use the REPLACE function in your query to replace those with '', which should get rid of them.

3) If you want quotes, see my example #1 above.

HTH.


Should I create seperate referenced table or go with serialized data in a column? I have a table with name app_client with the below fields

  id, user_id, name, age, phone, gender, related_entities_json (long text datatype),
  created_at, updated_at

I have 200k+ records in this table and I found that the size is mainly increased (7 GB ) due to related_entities_json field.

So I want to know that should I move related_entities_json to a seperate table? And then access it's data when needed!

I don't want to open another question for this so i would also like to know that Will this increase performance during fetching/inserting data?

Entry in related_entities_json from a row

https://pastebin.com/y4XDCK4Y

No.  InnoDB does something like that for you.  If a table has any bulky TEXT or BLOB columns, those columns are stored "off-record".  This means that they go in a separate block(s) on disk.  Meanwhile, if you read only other columns, you are not 'wasting' time fetching the bulky columns.

Corollary:  Do not use SELECT * ...; instead specify the columns you really need.

(History:  Back when ENGINE=MyISAM was the typical table type, your idea was a good one.)

A tip:  Consider COMPRESSing and UNCOMPRESSing the JSON column in the client.  This may shrink your 7GB to 2-3GB, and speed up queries that do need to look at the JSON (because I/O costs more that CPU).


SQL Server shows database in recovery Today, after a power failure, one database (with Recovery: full) shows "In Recovery" in SSMS. So:

  myDatabase (In recovery) (database status: recovery, Shutdown)

After finish, the "recovery process" the database shows the name myDatabase without "(In recovery)". I thought that the problem was solved, but it was not.

When I started the application that uses that database, the extra text "(In recovery)" appears again next to the name of my database.

I waited until the "recovery process" finished and then I took the database offline and brought it back online.

I restarted the server, restarted the computer and when my application was running the extra text appears again. In the SQL Server logs the message "Starting up database 'myDatabase'" appears few times. It seems that the database is working because I can insert data, but the state is showing that something it happens.

The server log is not showing anything interesting. The only abnormal thing is that I have 30 entries of "Starting up database 'myDatabase'".

I know that when the server starts every database goes through recovery before it is ready for use. But in my case, the database comes online then shows "myDatabase (In recovery)". If I close the application, the database goes to Status: Normal. This is driving me crazy.

I've even installed a new instance of SQL Server, and put the old database "myDatabase" on it. The problem still happens.

When I run this query:

SELECT databasepropertyex('nyDatabase', 'STATUS')

It shows recovering, online, suspect and the back to online and then recovering and so on.

I am not sure if this would help solve the issue, but you can give it a shot.

Run:

RESTORE DATABASE YourDatabase WITH RECOVERY

See if the above brings the database out of recovering mode. If it doesn't then the problem may be something else.

Can you try the below command and see if the database is corrupt?

DBCC CHECKDB ('YourDBname') WITH NO_INFOMSGS, ALL_ERRORMSGS

If it detects corruption, then you might want to repair the database using DBCC CHECKDB


Tmpfs for mysql tmp dir questions I have a 768M tmpfs dir that I use for mysql temp directory. It occasional fills up when running reports on large date ranges. When this happen the report fails and I get an error 28.

This actually seems acceptable to me because it prevents a query from running too long and put load on the server. (I think)

The advantage to using tmpfs is queries that use temporary tables run 50% faster.

My question is: is the tmpfs fills up while an insert or replace into is running will this fail?

Also it seems that when mysql fills the directory up the files are then deleted when the query fails. Will there ever be a case where it doesn't?

Do you think I could cause any issues using /tmp for MySQL temp dir? I have about 50 concurrent connections but not all of them are running reports at once.

What are the pros and cons of not using tmpfs?

If your 50 connections all want to do SELECTs that need tmp tables, even small ones, they could overflow tmpfs and fail right and left.

I prefer to let tmp tables be in the same filesystem where everything else is.  That way, it is not arbitrarily causing trouble when there is a spike in traffic.

Meanwhile, I keep track of the slowlog and aggressively tackle any slow queries.  Note that queries using big tmp table tend to be slow, so the slowlog generally catches them.

Generally, optimizing a query will provide much more than a 50% improvement.

Optimizing queries decreases the "load".

50 concurrent connections actually active simultaneously running (not just "Sleep" in SHOW PROCESSLIST) is unacceptable in my book.

When a query dies, it should remove the tmp table.  If the server crashes, there is a chance that the tmp table will remain.  This often happens with ALTER.

When a write operation dies in the middle, and the table is Engine=MyISAM, the write will be partially finished, and there is no way to know how much was done or not done.  Examples:  INSERT ... SELECT ... and DELETE ... WHERE ... SELECT ...