Unable to configure MongoDB replication without initial sync I have a big MongoDB database on my production server, which I want to set up as a replica set. I understand that if I enable the replica set option on both servers, it will automatically start to sync the data from scratch. But I don't want to sync from the beginning; instead I want to configure it like MySQL replication.

I would like to follow the following process:

Take dump from primary
Restore it on secondary
Enable replica set in config file, on both servers
Restart MongoDB.
Go to primary server and add the secondary node.

Is this possible; or is there any other alternate solution for this?

Why do two sets of heavy lifting ??? Let MongoDB do it.

For this example, suppose IP of SECONDARY is 10.30.50.70 with hostname myslave

STEP 01 : Enable Replica Set on the PRIMARY only

Make the following changes in  /etc/mongodb.conf on the PRIMARY

enable authorization in /etc/mongodb.conf

authorization: enabled under the security YAML tag (MongoDB 2.6+)
auth=true (if you are using MongoDB 2.4)

Enable replication (suppose your name the replica set myreplica)

Put replSetName: myreplica under replication YAML tag (MongoDB 2.6+)
Put replSet = myreplica (MongoDB 2.4)

STEP 02 : Restart MongoDB on the PRIMARY

service mongod restart

STEP 03 : Initiate Replica Set on the PRIMARY

rs.initiate();

NOTE: At this point your have a one-node Replica Set

STEP 04 : Perform Steps 01 and 02 on your SECONDARY

NOTE: At this point, the SECONDARY is replica set enabled but not replicating

STEP 05 : NOW, add the SECONDARY to the PRIMARY

Goto the PRIMARY and run

rs.add("10.30.50.70:27017")

or

rs.add("myslave:27017")

STEP 06 : Check status of Replica Set

Run this repeatedly

rs.status()

If this command ever freezes, don't worry. It rebuilding all the indexes.

You can open another ssh session on the SECONDARY, run tail -f on the mongodb.log file and see the index rebuild progression.

WHY DO IT THIS WAY ???

MongoDB will do essentially start replication, copy the data, and rebuild indexes in a single operation. As shown, this process is in no way automatic when building an initial replica set. Doing it this way, is less error-prone.


Return number of rows based on column value We are administering a sweepstakes for a client where we have a GUID tied to a contestant and the number of entries the contestant has acquired. I want to be able to draw winners based on the chance of winning corresponding to the number of entries.

GUID | Entries
--------------
1    | 1
2    | 5
3    | 2
4    | 7

So the results would be:

GUID
----
1
2
2
2
2
2
3
3
4
4
4
4
4
4
4

Basically I plan put the results in a spreadsheet and then use a random number generator to pick a row number between 1 and n, where n is the total number of entries. But of course if there is a way to easily select the winner programmatically, I'm all ears (or eyes as the case may be).

Thanks in advance.

For Postgres you can use:

select guid, g.i
from the_table
  cross join lateral generate_series(1, entries) as g(i);


"ORA-03113: end-of-file on communication channel" on startup I have been reading posts here, on Oracle support, and anywhere else I can find for the last three days and I've given up on this problem...

An Oracle database hung. Shutdown of the database sat for a few hours and then it quit. It wouldn't restart. The server was restarted. Oracle was restarted. Going step by step: startup nomount works, alter database mount works, alter database open returns ORA-03113. This is all on localhost - not over the network. The machine has no firewall of any kind running.

Any idea how to get past this ORA-03113 error? I've been on the phone with support in India for the last 4.5 hours and I haven't found anyone helpful yet.

After hours of misdirection from official Oracle support, I dove into this on my own and fixed it. I am documenting it here in case someone else has this problem.

To do any of this, you must be the oracle user:

$ su - oracle

Step 1: You need to look at the alert log. It isn't in /var/log as expected. You have to run an Oracle log reading program:

$ adrci
ADRCI: Release 11.2.0.1.0 - Production on Wed Sep 11 18:27:56 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
ADR base = "/u01/app/oracle"
adrci>

Notice the ADR base. That is not the install. You need to see the homes so you can connect to the one that you use.

adrci> show homes
ADR Homes:
diag/rdbms/cci/CCI
diag/tnslsnr/cci/listener
diag/tnslsnr/cci/start
diag/tnslsnr/cci/reload

CCI is the home. Set that.

adrci> set home diag/rdbms/cci/CCI
adrci>

Now, you can look at the alert logs. It would be very nice if they were in /var/log so you could easily parse the logs. Just stop wanting and deal with this interface. At least you can tail (and I hope you have a scrollback buffer):

adrci> show alert -tail 100

Scroll back until  you see errors. You want the FIRST error. Any errors after the first error are likely being caused by the first error. In my case, the first error was:

ORA-19815: WARNING: db_recovery_file_dest_size of 53687091200 bytes is 100.00% used, and has 0 remaining bytes available.

This is caused by transactions. Oracle is not designed to be used. If you do push a lot of data into it, it saves transaction logs. Those go into the recovery file area. Once that is full (50GB full in this case). Then, Oracle just dies. By design, if anything is messed up, Oracle will respond by shutting down.

There are two solutions, the proper one and the quick and dirty one. The quick and dirty one is to increase db_recovery_file_dest_size. First, exit adrci.

adrci> exit

Now, go into sqlplus without opening the database, just mounting it (you may be able to do this without mounting the database, but I mount it anyway).

$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 11 18:40:25 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> connect / as sysdba
Connected.
SQL> startup mount

Now, you can increase your current db_recovery_file_dest_size, increased to 75G in my case:

SQL> alter system set db_recovery_file_dest_size = 75G scope=both

Now, you can shutdown and startup again and that previous error should be gone.

The proper fix is to get rid of the recovery files. You do that using RMAN, not SQLPLUS or ADRCI.

$ rman
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Sep 11 18:45:11 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
RMAN> backup archivelog all delete input;

If you've got RMAN-06171: not connected to target database, than try to use rman target / instead of just rman

Wait a long time and your archivelog (that was using up all that space) will be gone. So, you can shutdown/startup your database and be back in business.


SQL Group by MAX then get the MIN of all the groups *edit: I'm using SQL Server 2012.

I have a table that looks like this:

id     LogOutTime
------------------------------
8563   2017-11-21 09:21:28.330
7961   2017-11-21 09:22:40.627
7961   2017-11-21 09:26:48.507
8563   2017-11-21 09:29:05.977

I want to get this the latest LogOutTime for each id. Then I want to get the oldest LogOutTime out of that group.

This is the record I want:

id     LogOutTime
------------------------------
7961   2017-11-21 09:26:48.507

This gives me the latest LogOutTime for each group:

SELECT MAX(LogOutTime) AS MaxLogOut,
       id
FROM table1
GROUP BY id

This gives me the date that I want:

SELECT MIN(table2.MaxLogout)
FROM (SELECT MAX(LogOutTime) AS MaxLogOut,
             id
      FROM table1
      GROUP BY id) AS table2

I need to get the ID and I'm not sure how to go about it and haven't found an example that is like mine.

Depending on the DBMS, you can use LIMIT 1 or TOP 1 or FETCH FIRST 1 ROWS ONLY - in combination with ORDER BY MaxLogOut:

-- standard SQL, Postgres, SQL Server 2012+
SELECT
    MAX(LogOutTime) AS MaxLogOut, id
FROM table1
GROUP BY id
ORDER BY MaxLogOut
    OFFSET 0 ROWS
    FETCH FIRST 1 ROWS ONLY ;

-- SQL Server
SELECT TOP (1)
    MAX(LogOutTime) AS MaxLogOut, id
FROM table1
GROUP BY id
ORDER BY MaxLogOut ;

-- MySQL
SELECT
    MAX(LogOutTime) AS MaxLogOut, id
FROM table1
GROUP BY id
ORDER BY MaxLogOut
    LIMIT 1 ;


Does CAST(DateTime AS DATE) cause the entire table to be CAST before comparison? This is a very common need in the WHERE clause of our SQL statements:

 CAST(Something.CreationDate AS DATE) BETWEEN CAST(@firstDate AS DATE)
 AND CAST(@lastDate AS DATE)

All of the fields are of type DateTime that are cast to DATE. Does this cause a conversion to run across the entire table first and then comparisons happens or is this very similar to a scan on that table?

Whipped up a simple example on this using a handy calendar table.

DECLARE @StartDate DATETIME = '2014-04-16 09:35:36.510'
DECLARE @EndDate DATETIME = '2014-05-16 09:35:36.510'

select
    *
from Calendar
where CAST(BaseDate AS date) > CAST(@StartDate AS date)
    AND CAST(BaseDate AS date) > CAST(@StartDate AS date)

When we look at the query plan for this we can see it's doing a simple index seek and all is well.


best mysql field type for temperatures I'm realising that this is the first time I've stored temperatures in a MySQL DB.

What do think the best mysql field type for temperatures (c) is?

Decimial (3,3) ?

You should use DECIMAL (3,1) for temperature in (Celsius)


What is the equivalent of SQL Server's db_datareader/db_datawriter in PostgreSQL? I'm porting an application from SQL Server to PostgreSQL. Most of this is simple as the application only really does simple selects, inserts and updates. However I need to create a user to access the database. The SQL Sever code creates its user like this:

USE [master] CREATE LOGIN [ExampleUserName] WITH PASSWORD = 'ExamplePassword', CHECK_EXPIRATION=OFF, CHECK_POLICY=ON;
USE [ExampleDB];
CREATE USER [ExampleUserName] FOR LOGIN [ExampleUserName];
ALTER ROLE [db_datawriter] ADD MEMBER [ExampleUserName];
ALTER ROLE [db_datareader] ADD MEMBER [ExampleUserName];

What is the equivalent of that in PostgreSQL?

There is no builtin role like that in PostgreSQL.  However, you can set up one:

CREATE ROLE db_datawriter;

After this, you have to assign write privileges (usually INSERT, UPDATE, DELETE, maybe TRUNCATE) to it.  To see how to do this for all present and future tables, read my older answer.

Normally, write access also implicates reads, therefore it make sense to add db_datawriter as a member of db_datareader (which was set up similarly, but with SELECT only):

GRANT db_datareader TO db_datawriter;

Then you can grant these two to different login users (or other roles) as necessary:

CREATE USER example_user IN ROLE db_datareader;

Note that CREATE USER implicates being able to log in, unlike CREATE ROLE.


Direct database interaction with memory - how it works? I am reading a book on databases and a chapter in it talks about buffer manager and it's replacement strategies.

I've noticed that the DBMS interacts directly with memory, as opposed to asking OS to interact with virtual memory. Is that correct? How exactly does this direct communication takes place? How much of memory is DBMS allowed to allocate? I've never been exposed to such low level function calls and I'd love to see some explanations and examples of it.

Getting this kind of info is kinda troublesome to find on google if you don't know the correct keywords to search for.

Let's talk about MySQL/MariaDB with ENGINE=InnoDB only.  (That is virtually the only Engine in use today.)

All InnoDB activity happens in the buffer_pool.
All data and each secondary index is composed of 16KB blocks.
Those blocks are brought into the buffer_pool as needed, and flushed out as needed.
It uses a LRU (Least Recently Used) algorithm for controlling which blocks to bump out.  (There are minor tweaks to LRU, so it is not exactly LRU.)

Hence, it is very important go give InnoDB most of RAM, then let it do its thing.  A simple rule of thumb:  innodb_buffer_pool_size should be set to about 70% of RAM.  For most applications, that is the most important setting and the only one that needs to be changed from the default.

Setting the buffer_pool too large could lead to swapping.  But, since InnoDB treats it as a big array, that could cause a terrible amount of swapping, hence slowing down MySQL/MariaDB terribly.

InnoDB has threads for reading writing blocks.  In particular, flushing to disk is somewhat done "in the background".  This allows an INSERT to appear to be faster than it really is.

I discuss memory allocation further in here

Using "virtual memory" for accessing a file is a lazy way to do things.  It works OK for simple cases.  However, a database engine needs to be efficient and fast.  When the code knows the access pattern, it is best to use an access method that is tuned for such.

For example, if you are going to read sequentially through a file, it is best (for memory management) to ping-ping between two 'small' buffers; one being read into and one being parsed.  (A single "circular" buffer is a good alternative.)

  How exactly does this direct communication takes place?

Because everything is in 16KB blocks, there is a number that uniquely identifies such.  It is rather large (20? bytes).  It includes at least the "tablespace" number, and block number within the table space.  This is taken modulo the number of buffer_pool_instances to decide which instance to look in.

Within a block, there are rows of the table or rows of a secondary index, or rows of a node in the B+Tree (see Wikipedia) that it represents.

If the block is not in the buffer_pool, it must wait while a read occurs.  But, before the read can occur, there might need to be a write to flush out some block.  Normally, a background thread(s) flushes out "dirty" blocks so that there is always some free blocks in the buffer_pool.  ("Free" or "easily removed" because they are not dirty.)


How to limit inserts to one row per user per month? I would like to make a monthly contest. I have Users and Products. Each user can vote for products he/she likes the most this month. My vote table looks like this:

+---------+-----------+------------+
| user    | product   | date       |
+---------+-----------+------------+
| 1       | 1         | 2018-09-03 |
| 1       | 2         | 2018-09-03 |
| 1       | 2         | 2018-08-28 |
+---------+-----------+------------+

A user can like the same product but not in the same month.

My problem is that I can't figure out a valid Primary Key to prevent duplicate votes.

If I use PK (user,product), a user cannot vote more than one time for the same product. If I use PK (user,product,date), a user can vote the same product once a day.

How can I prevent duplicate votes within the same month?

Note: date should be a valid DateTime column.

MySQL does not allow functions in keys, only column references. So if you want to restrict by a UNIQUE key, you need another column that would contain only the year and month for the event. The easiest and most straightforward approach is to reduce the date value to something like YYYY-MM-01. If you are using MySQL 5.7 or newer, you can declare that column as generated and define an expression converting the day part to the constant 01 value.

+---------+-----------+------------+------------+
| user    | product   | date       | rdate      |
+---------+-----------+------------+------------+
| 1       | 1         | 2018-09-03 | 2018-09-01 |
| 1       | 2         | 2018-09-03 | 2018-09-01 |
| 1       | 2         | 2018-08-28 | 2018-08-01 |
+---------+-----------+------------+------------+

And now you can create the UNIQUE INDEX permonth (user, product, rdate). This is the uniqueness established on the physical level.

On the logical level uniqueness can be achieved by (at least) two common ways.

First, you can define a BEFORE INSERT trigger that would perform extended comparison of the dates and decline to insert a duplicate. But triggers are not a very clean and easy tool because of their implicitness, and my advise is to avoid triggers if possible.

The more robust way is to wrap the insert statement into a stored routine and perform call SafeVoteInsert( userID, productID, date ) instead of plain INSERT vote VALUES ....


Migrating a tablespace to a different database on the same ASM How might one go about copying or moving tablespaces between databases that uses the same ASM instances?

Might it be possible to cut some corners in RMAN or have ASM remap files belonging to a transportable tablespace?

Transportable tablespaces and asmcmd cp command should do the trick.

Prepare transportable tablespaces metadata set.
Copy expdp file to location accessible by impdp from destination database.
asmcmd cp datafiles to new location.
Import TT metadata set.

If your ASM diskgroup uses some storage device with several disks then you should be able to improve performance by copying several datafiles in parallel.


Is DETERMINISTIC NO SQL function which returns the same value always a good alternative for using as a constant in MySQL? MySQL does not support declaring constants which can be used between many stored procedures.
For me, it seems that a deterministic no sql function can be used as an alternative for a constant.
Example:
I want to use a constant named "OPERATION_TYPE_ABORT" with the value "ABORT".
So what I think I can use as an alternative is defining a stored function like below.

DROP FUNCTION IF EXISTS OPERATION_TYPE_ABORT;
DELIMITER //
CREATE FUNCTION OPERATION_TYPE_ABORT() RETURNS VARCHAR(6)
DETERMINISTIC NO SQL
BEGIN
    RETURN ("ABORT");
END//
DELIMITER ;

Output:

mysql> select "test" FROM DUAL WHERE "ABORT" = OPERATION_TYPE_ABORT();
+------+
| test |
+------+
| test |
+------+
1 row in set (0.00 sec)

mysql> select "test" FROM DUAL WHERE "ABORT2" = OPERATION_TYPE_ABORT();
Empty set (0.00 sec)

Do you see any disadvantage or a issue in this approach? (for e.g. Significant performance issue etc).
P.S: The queries which use these constants are using small sized tables in the query. MySQL version 5.6

Thanks

I'd say yes, it is a good technique.

When there are no input arguments to a stored function declared as DETERMINISTIC and NO SQL, the optimizer does the right thing -- it executes the function once, and resolves the result from the function into an internal constant when the query is planned, so applicable indexes on the column being compared to the result of the function are used, as expected.

The behavior and performance using this technique should (for all practical purposes) be no different than if the query actually contained the literal constant.


Simple Car Rental database I am trying to learn SQL development using SQL Server 2008 R2 Database Management Studio.  I was trying to model a car rental company, where clients can rent cars, and I have to know which client has rented which car on which date, and also the date the car was returned.  I have tried to the following:

--create the database
create database RentACar;
use RentACar;

--create the tables
create table Clients(
Id int not null constraint pk_ClientID primary key,
Name nvarchar(255),
LastName nvarchar(255)
);

create table CarPool(
Id int not null constraint pk_CarPoolID primary key,
Brand varchar(40) not null,
Price decimal not null
);

create table DateOut(
Id int not null constraint pk_DateOutID primary key,
DateOut datetime not null
);

create table DateIn(
Id int not null constraint pk_DateInID primary key,
DateIn datetime not null
);

--fill the tables with some values
insert into Clients values (1, 'Richard', 'Castle');
insert into Clients values (2, 'Jane', 'Rizzolli');
insert into Clients values (3, 'Jack', 'Bauer');

insert into CarPool values (1, 'BMW', 125.99);
insert into CarPool values (2, 'Lexus', 105.99);
insert into CarPool values (3, 'Honda', 95.99);

insert into DateOut values (1, 2012-03-10);
insert into DateOut values (2, 2012-03-11);
insert into DateOut values (3, 2012-03-12);

insert into DateIn values (1, 2012-04-10);
insert into DateIn values (2, 2012-04-11);
insert into DateIn values (3, 2012-04-12);

--now try to connect the tables somehow...

I don't know how to connect the tables with primary and foreign keys, so as to be able to for example query a list of all people who did not return the car yet, and a field that calculates how long a car has been with a client.  I would also like to create a query that allows me to update the datetime for clients that have not returned their cars.

I am only familiar with the basic SQL commands like create, add, update, insert into, and i am at the end of my wits here.  Nothing I try works.  I always get errors no matter what i try. I am going slowly through MS Tranact-SQL documentation but I cannot seem to find what I am looking for.  I know the tables should be normalized and kept to a minimum, data should not be redundant etc... I just can't seem to find the right combination.  Can someone please point me in the right direction?

@BryceAtNetwork23
thanks for editing my post with code.

@Lumpy
thank you for your help. I didn't have time to check if someone had replied to my question here, and last night i was thinking how to finally solve this and came up with the same conclusion. I should have two tables, one for Clients and one for the Cars, and then one table to connect them, by simply creating a column with both dates, in and out, and then the key fact being the two columns that reference the IDs of the particular Client and a particular Car using foreign keys. Also the date(s) being in the same row with these Client ID and Car ID is the connection to the Client and Car tables.  Eureka! I felt so stupid, haha, it was right there in front of my nose.

Anyway, I think SQL is amazing, and I am just beginning to grasp the possibilities as I go deeper into things, and thank God for dba.stackexchange and folks like yourself, it makes learning  a whole lot easier.

Thank you

ok.  It looks like you have 4 tables here with nothing to join them together.  Perhaps the easiest solution at this point would be to create a mapping table called something like CarRental which would have the columns Carpoolid, cilentsid, dateinid, and dteoutid.

I think the way I would go would be to drop the datein and dateout table and put them on a table called car rental.

    CREATE TABLE [dbo].[CarRental](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CarpoolID] [int] NOT NULL,
    [ClientID] [int] NOT NULL,
    [DateIN] [datetime] NULL,
    [DateOut] [datetime] NOT NULL,
 CONSTRAINT [PK_CarRental] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[CarRental]  WITH CHECK ADD  CONSTRAINT [FK_CarRental_CarPool] FOREIGN KEY([CarpoolID])
REFERENCES [dbo].[CarPool] ([Id])
GO

ALTER TABLE [dbo].[CarRental] CHECK CONSTRAINT [FK_CarRental_CarPool]
GO

ALTER TABLE [dbo].[CarRental]  WITH CHECK ADD  CONSTRAINT [FK_CarRental_Clients] FOREIGN KEY([ClientID])
REFERENCES [dbo].[Clients] ([Id])
GO

ALTER TABLE [dbo].[CarRental] CHECK CONSTRAINT [FK_CarRental_Clients]
GO

Car rental now stores the relationship between the client and the car that they rented and for each record stores the datein and dateout.

If you dont want to script all of this out you can use the table designer to create the table by right clicking on tables and then choosing new table. You can use the database diagram to drag and drop columns in order to create your foreign key relationships by right clicking database diagrams and chosing New Database diagram.

If you use this structure then You would leave the DateIn field as NULL untill the car was returned.  Your query to find out which clients and cars have not been returned would be

SELECT carpool.brand, clients.name, clients.lastname
  from carrental
       join carpool on carpool.id = carrental.carpoolid
       join clients on clients.id = carretnal.cientid
where carretnal.datein IS NULL

In this case you dont need to update the records for people who havent returned a car.  You only update the record to contain the date when they have returned the car.

UPDATE CarRental
SET Datein = GETDATE()
WHERE Clientid = @CLientid
  AND Carpoolid = @Carpoolid


2 similar queries in large sql server table - 1 takes too long I have a huge table in my database that contains distances between cities.  This enables my application to find nearby cities around the world when a starting city is selected.  It contains 4 columns - ID, StartCityID, EndCityID and Distance and contains about 120 million rows.  I've got indexes set up on the startcityID, endcityID, another one for both, and another one each for startcity + distance, and endcity + distance.  (This is my first real dealings with indexes so not 100% sure if I'm doing it correctly).

Anyway - I do the following 2 queries:

Select distinct StartCityID
From Distances where EndCityID = 23485

and

Select distinct EndCityID
From Distances where StartCityID = 20045

They both return the same number of cityIDs, but the top one takes 35 seconds to do, and the bottom one returns results immediately.  When I look at the indexes, they seem to be set up to serve startCity and endCity in the same way.  Anyone know why they might be acting differently? I'm at a loss...

NB - this may offer more insight, but the one that takes 35 seconds - if I press execute again straight away with the same ID, it returns results immediately as well that time.  Unfortunately that isn't good enough for my website but it may be useful information.

Thanks

The explain plan (or execution plan) should tell you exactly where the difference lies, and which indexes are being used.

See here for more info on how to get an explain plan.


BAK files not created on Secondary or Primary SQL Server 2014 with most DB in AG. Backup preference set to prefer Secondary.

Plan 1 backup all user DBs (AG and non AG)
Plan 2 backup all user DB trans logs

Both .bak and .trn files were created with no issues. Received error email from the one that did not finish job due to AG settings. But .bak files were created by one or the other instance.

These setting WERE WORKING fine up until 2 months ago.

Now for some reason, no .bak files at all for ANY AG database are created.

BAK: bak files for nonAG DBS are created by both primary and secondary. NEITHER the Pri or Sec are creating bak files for any AG Db.

TRN: The trn are created fine on secondary for all AG and nonAG DBs. The primary only has trn backups for nonAG. SO TRN backups are working the way they should.

So what changed?

I know that there were VM upgrades done during this time and I also installed the latest SQL SP.

The whole point of selecting prefer Secondary option is so backups happen on Sec, both bak and trn. Selecting ignore priority option in plan setting is NOT a solution it is a band-aid.

The only thing close to what is happening I found here https://blogs.msdn.microsoft.com/alwaysonpro/2014/01/02/maintenance-plan-does-not-backup-database-or-log-of-database-defined-in-availability-group/
but that is also vague as it says

  the maintenance plan may cease to backup the database or log of that database

Seriously: may.

Prefer secondary means that backups should run on secondary, plain and simple but they aren't running on either anymore. Any ideas?

Our Senior DBA ended up opening a Microsoft ticket for this and so far the only solution that does not involve selecting ignore priority or copy-only is to change the Availability Groups backup settings to backup on Primary instead of Secondary. We have MANY databases and many of them are quite large and accessed throughout the day; we did not initially want to run on Primary due to the server load this could create.
So for anyone who is going through a similar situation, here are the tests I did and why we came to this conclusion. Again choosing Primary Backup for Availability Group always-on settings is not a fix to why Secondary option does not work the way it should, but it is the best solution if you want latest full database backup.

Prefer Secondary for AG backups
Created 2 plans one for DB backups (bak) and one for Trans Log (trn)

Note: When I first started testing I had one plan with both DB backup (bak) and transaction log backup (trn) but would fail and stop when bak were not created on secondary and no trn created and wanted to test each. separated bak and trn

Added both AG and nonAG Dbs to the plans

1. Test 1

NO options selected

Trans Log plans works as it should, Primary only had trn for nonAG DBs and Sec had trn for all Dbs, no errors on either

Bak files were created on both Pri and Sec but only for nonAG DBs

No errors from primary, not expecting there to be on primary as it is not set as AG backup.

Sec is the only one that triggered an error...

  "This BACKUP or RESTORE command is not supported on a database mirror
  or secondary replica. BACKUP DATABASE is terminating abnormally."

2. Test 2

Selected ignore replica on DB backups only.

Bak files were created on PRIMARY

NO bak files on Secondary

Secondary triggered same error above

3. Test 3

Unchecked ignore Priority and checked copy-only

Secondary created both AG and nonAG bak files

Primary only had nonAG bak files

4. Test 4 (NOT AN OPTION AT ALL)

Both Ignore and copy-only checked

ALL DB backed up on both Pri and Sec


Restricting Insert and Update to functions We have a function in postgresql that inserts some rows in a table and updates some other rows based on an input provided by the user. Is it possible to somehow execute the function without giving write access to the user for those tables which the functions inserts and updates?
So i basically want the user to be able to execute the function and for the changes to happen through the function, but the user should not be able to insert or update the table on his own outside of the function.

TIA.

Yes this is possible.

You (obviously) need two database users for this:

one who is allowed to call the function
one who ones the table and is allowed to directly modify it.

Then revoke the update, insert and delete privilege from the restricted user.

Then create the function with the privileged user as the owner and use the attribute SECURITY DEFINER when creating it.

Thus the restricted user cannot change the table, but when he calls the function, the function runs with the privileges of the other user and thus can modify the table.

Something like this:

Logged in as the privileged user:

create user restricted_user password 'hidden';
create table foo (id serial not null primary key, some_info text);
grant select on foo to restricted_user;
create or replace function public.insert_foo(p_info text)
  RETURNS void
  security definer  -- << this is important
  LANGUAGE sql
AS
$body$
   insert into public.foo (some_info) values (p_info);
$body$;

grant execute on function public.insert_foo(text) to restricted_user;

Now you log in as the restricted_user:

insert into foo (some_info) values ('foo');
==> ERROR: permission denied for relation foo

select insert_foo('foo');
==> one row inserted


How to maintain trees and their roots in SQLite I have a (SQLite) database with a table representing nodes in a forest (set of trees):

create table "Node" (
    "id" Text primary key,
    "parent" Text references "Node" ("id") -- parent node
        on update restrict on delete restrict,
    "root" Text references "Node" ("id") -- tree root node
        on update restrict on delete restrict
) without rowid;

It may happen that after inserting new nodes or updating existing ones, "root" attribute updates must be performed on the inserted nodes or nodes previously in the table, because the inserted nodes are connected to or existing trees.

Consider the following existing table:

"id" "parent" "root"
--------------------
'a'  null     'a'
'b'  'a'      'a'
'c'  'a'      'a'

Case 1: we want to insert/ignore the following nodes

"id" "parent" "root"
--------------------
'b'  null     'b'           -- ignore
'd'  'b'      'b'           -- insert

To end up with

"id" "parent" "root"
--------------------
'a'  null     'a'
'b'  'a'      'b'
'c'  'a'      'a'
'd'  'b'      'b'

Case 2: we want to insert/update (in the original table, without the case 1 changes)

"id" "parent" "root"
--------------------
'e'  null     'e'           -- insert
'a'  'e'      'e'           -- update

To end up with

"id" "parent" "root"
--------------------
'e'  null     'e'
'a'  'e'      'e'
'b'  'a'      'e'
'c'  'a'      'e'

Given these cases: what are insert/update statements (and perhaps triggers) that allow me to do obtain the correct resulting trees? One part is: when the node is already in the table, then I need to ‘merge’ it correctly with its version from the insert and then I need to propagate in some way the change of root.

One idea I had was to create a second table containing just a list of roots and then having "root" refer there, so that it could cascade on update. But then I don't know how to deal with updates that replace a root by an already existing one.

Finding all nodes in a subtree to update them requires a recursive common table expression:

-- update the root of all nodes in 'a's subtree to 'x'
WITH RECURSIVE subtree(id) AS (
    VALUES('a')
    UNION ALL
    SELECT Node.id
    FROM Node
    JOIN subtree ON Node.parent = subtree.id
)
UPDATE Node
SET root = 'x'
WHERE id IN subtree;

In SQLite, CTEs are not allowed in triggers.


test a SET data type field I have a SET type field in my table like:

car_features SET('Air Conditioning'
       ,'Power Windows'
       ,'Power Steering'
       ,'Moonroof'
       ,'Disk Brakes'
       ,'Power Seats'
       ,'Leather')

I'm trying to test this field in the order that the client gives to me in a input string,like:

                     "Power Windows,Moonroof,Leather,Disk Brakes"

then I want to test the field then give the result back to client like:

               +---------------+----------+---------+-------------+
               | Power Windows | Moonroof | Leather | Disk Brakes |
               +---------------+----------+---------+-------------+
               | 1             | 0        | 1       | 1           |
               +---------------+----------+---------+-------------+

according to whether the specific option is set in this field or not.

the question is,I can easily get single result like:

      select find_in_set(a_function_fetch_option_oneByone(theInputString),car_features)>0;

but I cannot combine every result into one just like I said...

somebody help me!!!

This may help you get closer to a solution:

You can & and | (that's 'and' and 'or') SET values.  Unfortunately you get an integer, the bits of which represent the result of the bit-wise operation.

The result of & will be non-zero if some fields match.

The result of & can be fed into BIT_COUNT() to see how many things match.

(a & b) != 0

should give you 1 for the item being in the set, zero if not.


How to Make a Copy of a database? I have an sql server database and I want to make a mirror copy of it on my local computer. I tried to use the copy database wizard but it fails. It seems me to look at the event log for the reason but all it says in the event log is

Package "CDW_4" failed.

so I have no clue why it is failing.

Is there another way to do this?

I am assuming that by mirror copy ==> you mean at the point when the database full backup was taken.

If that is not the case, then for near to realtime copy of your database, I would suggest transactional replication.

Also, if you are using enterprise edition, then you can leverage the benefits of database SNAPSHOTS.

Step 1: Make a FULL database backup

backup database database_name to disk = 'path to backup drive' with copy_only, stats =10
-- ,compression (sql server 2008 R2 and up, compression is available in standard edition as well).

  e.g. path to backup drive = D:\backups\db_name_FULL.bak

Step 2: Restore the backup taken in step 1 with move.

restore database database_name_mirror
from disk = 'path to backup drive'
with stats =10, recovery,
move 'Logical_Data_FileName' to 'Physical data file location.mdf',
move 'Logical_Log_FileName' to 'Physical log file location.ldf'

Logical file name can be found using sp_helpdb database_name or restore filelistonly from disk = D:\backups\db_name_FULL.bak

e.g.

restore database database_name_mirror
from disk='D:\backups\db_name_FULL.bak'
WITH move 'database_name_Data' to  'D:\Data\database_name_mirror_Data.mdf',
move 'database_name_log' to  'L:\logs\database_name_mirror_log.ldf';
GO


Reset SQL Server sa password I need to recover my SQL Server 2008 user/pwd.

The situation is: I don't know the SA password and Windows Authenticated user is no longer active user (disable user because the user has left job) so I can not log in using Windows authentication.

Is there anyway to reset the sa password, Windows authentication and to log in to the database?

Last option could be uninstall and install again. In this case data will be lost.

Thanks,

Follow the steps in Connect to SQL Server When System Administrators Are Locked Out:

start the server in maintenance mode with -m
connect as local administrator
add necessary accounts
restart the server in normal mode

Is all described, with detail step-by-step, in the linked article.


Start failed for Job in SQL Server Agent I am trying to schedule a job which executes stored procedure but it is failing.

DECLARE @return_value int
EXEC    @return_value = [dbo].[sp_WarehouseAttendance_New]
SELECT  'Return Value' = @return_value

If I try to run it manually,  I am getting this error.

At the moment I am trying to run EXECUTE storedproc but it takes long time to complete.

I checked Services.msc and it shows as below.

Kindly let me know how to avoid this.

Thanks,
Ar

Looks like the problem is exactly in the description - the job is already running.

Try stopping the job first, then re-running it. Either with the GUI, or

USE msdb
GO

EXEC dbo.sp_stop_job
    N'Job Name'
GO

Other things to check:
What is the job doing? Can you do that (run the script, for example) directly (not using Agent)?

Has the job got elements (e.g. a script) which can never actually finish, therefore the job is never going to stop (and therefore will not start again on schedule).

It's a bit hacky, but you can schedule a job-killing job to stop any jobs that are over-running (although it's usually better to try fix your jobs so they finish properly).

This script from SQL Server Central provides some handy info about currently running jobs:

SELECT
    ja.job_id,
    j.name AS job_name,
    ja.start_execution_date,
    ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,
    Js.step_name
FROM msdb.dbo.sysjobactivity ja
LEFT JOIN msdb.dbo.sysjobhistory jh
    ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j
ON ja.job_id = j.job_id
JOIN msdb.dbo.sysjobsteps js
    ON ja.job_id = js.job_id
    AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
AND start_execution_date is not null
AND stop_execution_date is null;

That should help you sort out your problem, if not, post some more details about what exactly the job is doing.

Edit-
Code to check SQL server agent is running (run from Management studio).
DECLARE @agent NVARCHAR(512);

SELECT @agent = COALESCE(N'SQLAgent$' + CONVERT(SYSNAME, SERVERPROPERTY('InstanceName')),
  N'SQLServerAgent');

EXEC master.dbo.xp_servicecontrol 'QueryState', @agent;

You can manage Agent from SQL Server Configuration Manager.

Ian


Insert rows that match a condition, and return rows that don't I am using Sql Server 2014

I have an expensive query. I want to insert the results of that query into a table.

However the results should match a certain condition, if they don't I don't want to insert them, but I do want to return them so that my application can warn/record these violations.

So essentially I want to run both of the following:

INSERT INTO MyTable
SELECT ExpensiveQuery
WHERE MyCondition;

SELECT ExpensiveQuery
WHERE Not MyCondition;

But I don't want to run the expensive query twice.

Is this possible?

You might store the records of the expensive query into a tempdb table and you can use that with your condition on the tempdb, this way you won't need to run your expensive query twice.

select * into tempdb..expensive_query from Userdb.dbo.expensive_query

Afterwards, you may use your conditional statement as below:

1st query will look like:

Insert into MyTable SELECT from tempdb..expensive_query
WHERE MyCondition;

2nd query will be:

SELECT from tempdb..expensive_query
WHERE Not MyCondition;

You may use # or ##table also instead of tempdb.dbo.table_name, this depends on whether you are doing that in the same session or multiple database sessions are required however tempdbb.dbo.table_name is safer.

You might use CTE also.

Hope above helps.


Is it a good practice to build in as many foreign keys as possible? For example, there are three tables: players, dungeons, and instances.

A player has many dungeons and each dungeon has many instances, like in a well-known game of World of Warcraft. Each player would have a dungeon_id as a foreign key and each dungeon would have an instance_id as a foreign key. That would be a sufficient minimalist approach. A player of each instance can be accessed through the dungeon table. This would result in 1 less column, but a bit longer query.

Another approach is to add player_id to instances table as a foreign key. Now players can queried directly from instances table. Shorter query, but one more column for a foreign key.

Which approach is better in the long run? In practice it seems like adding a foreign key everywhere where it is logically possible makes it easier. But then wouldn't it result in tighter coupling and make code harder to refactor?

Another approach is to add player_id to instances table as a foreign
  key. Now players can queried directly from instances table. Shorter
  query, but one more column for a foreign key.

IMO, this will be bad approach.This is kind of circular reference  .

Since you have one to many relation so mapping table will be separate,dungeon,instance and player each will have master table and mapping table defining their one-to-many relation.

    CREATE TABLE instances (
    instance_id INT PRIMARY KEY
    ,instance VARCHAR(100) NOT NULL
    )

CREATE TABLE dungeons (
    dungeon_id INT PRIMARY KEY
    ,dungeon VARCHAR(100) NOT NULL
    )

CREATE TABLE player (
    playerid INT PRIMARY KEY
    ,player VARCHAR(100) NOT NULL
    )

CREATE TABLE dungeon_instance (
    dungeon_instance_id INT identity(1, 1)
    ,dungeon_id INT NOT NULL
    ,instance_id INT NOT NULL CONSTRAINT FK_instances FOREIGN KEY (instance_id) REFERENCES instances(instance_id)
    ,CONSTRAINT FK_dungeon FOREIGN KEY (dungeon_id) REFERENCES dungeons(dungeon_id)
    ,PRIMARY KEY (
        dungeon_id
        ,instance_id
        )
    )

CREATE TABLE player_dungeon (
    player_dungeon_id INT NOT NULL
    ,playerid INT NOT NULL
    ,dungeon_id INT NOT NULL
    ,CONSTRAINT FK_player FOREIGN KEY (playerid) REFERENCES player(playerid)
    ,CONSTRAINT FK_dungeon FOREIGN KEY (dungeon_id) REFERENCES dungeons(dungeon_id)
    ,PRIMARY KEY (
        playerid
        ,dungeon_id
        )
    )

if you have very frequent query between player and instance where there is no dungeon table require in output and volumne of data is high then you can go for Denormalisation.

Whether to create player_instance mapping table or not depend upon above requirement.

But it desirable to achieve Denormalisation via index view


query to identify all data types used in postgresql database tables I don't want to know all data types, just all data types used in my database. Can this information be queried?

PostgreSQL 8.4 and 9.x versions

I currently need to know all data types for over 200 tables in public ( and other schemas )

select data_type, count(*)
from information_schema.columns
where table_schema = 'public'
group by data_type ;

For other schemas just add: or table_schema = 'your_schema_name'.
If this will not satisfy you, you should look in pg_... or information_schema tables.

If you are looking for table information:

select data_type, count(*)
from information_schema.columns
where table_schema = 'public' and table_name = 'your_table_name'
group by data_type ;


Behavior of Oracle During Very Large Transactions I understand that if a transaction is large enough to overflow all of the redo logs on an Oracle database, and the database is in ARCHIVELOG mode, the transaction will wait until the archive log file is written before it continues.

How does Oracle behave in situations where a transaction overflows the redo logs and the database is in NOARCHIVELOG mode?

Strictly speaking, you cannot overflow the redo logs, because they are endlessly reused in a circular manner. When in the ARCHIVELOG mode, however, a log file cannot be reused until it's archived, so there can be a situation where you would have to wait for archiving if you fill redo logs faster than they are archived.

In the NOARCHIVELOG mode there is no such restriction, so Oracle will just keep rewriting log files one after another in a circular manner.

In either case there is also a wait during log file switch because the database checkpoint must be performed before the next log is overwritten.

This is fully described in the administration manual, by the way.


Best way to automate the loading of data daily into google big query from SQL Server Best way to load/copy/insert data from a SQL Server database into a  table in Google Big Query on a daily schedule.

I.E data from a table in a SQL Server database to a table in Google Big Query

I was hoping to use SSIS but there are no native data sources/destinations only 3rd party offerings?

I was thinking of using SSIS to create a CSV file then using Google BQ commandline push the CSV file to big query?

Does anyone have any other better suggestions/methods?

See this Q&A at Stack Overflow: Migrate SQL Server database to BigQuery

SQLCMD is your friend. It dumps the results of a query into a CSV.


Get a Table from XML in postgresql Have a look at the following XML data:

<data>
    <test color="red">Red text</test>
    <test color="green">green</test>
</data>

Let's say I have several xml-documents with this structure in my database:

CREATE TABLE xmldata (
    id bigserial not null,
    documents xml)

Now I want to select all possible colors:

SELECT id, xpath('//test', xml) FROM xmldata;

But this returns a table with the id of each document and a text-array of the test-nodes. Furthermore, documents without any "test" node exist in the result as well - with an empty array {}

What I really want is a table like this:

| id | node                              |
|  1 | <test color="red">Red text</test> |
|  1 | <test color="green">green</test>  |

What is the syntax I have to use?

I heard that xpath_table may be the function to use - but this function is marked as deprecated...

(The returned table has to have one line for each occurence of the node I searched for. The node itself maybe an xml-snippet, text or something else - isn't really important)

There is nothing wrong with using xpath_table().

Even though some of the functions in the xml2 extension have been replaced with in-core functions, xpath_table is not one of them (I think this will happen for Postgres 10).

Until then, xpath_table() seems to be your only option:

SELECT *
FROM xpath_table('id',
            'documents',
            'xmldata',
            '//test',
            'true') AS t(doc_id integer, data text);

Returns:

doc_id | data
-------+---------
     1 | Red text
     1 | green

In order for this to work you first need to create the extension (as a superuser):

create extension xml2;

Otherwise the function isn't available.


How can I find out what my SqlServer 2005 CPU is mostly working on? I apologize if this is somewhat of a rookie question.

After looking online I found some articles that explain how to find the worker time per query using sys.dm_exec_query_stats. e.g.

SELECT TOP(10)
creation_time
, last_execution_time
, (total_worker_time+0.0)/1000 AS total_worker_time
, (total_worker_time+0.0)/(execution_count*1000) AS [AvgCPUTime] , execution_count
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
WHERE total_worker_time > 0
ORDER BY total_worker_time DESC

from here.

But according to Microsoft the total_work_time only tells us the amount of time spent after the plan has been generated, not for instance, the amount of time spent building the plan for that query.

Is there a way to determine how much CPU is being spent in building the plans, and better yet, a simple breakdown of percent CPU by task (e.g. building plans, executing queries, indexing).

Thank you in advance.

Ref: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-statistics-time-transact-sql

You can use SET STATISTICS TIME ON.

You can get a breakdown of:

SQL Server parse and compile time
SQL Server Execution Times

You can turn this on in Management studio for all queries or per session.


passing variable values to procedure in postgresql via psql basically I have written a code to pass variable values via psql to my procedure is it supported?
and if supported any pointers to get it done : my code looks like below :
do $$
<<main_block>>
declare
  err_msg text;
BEGIN
        insert into mig.qb_items (qb_id , sync_token , qb_updated_at , qb_created_at , qb_expired_at , name , sku , description , active , qb_parent_id , taxable , unit_price , transaction_type , category_type , qb_income_account_id  , qb_expense_account_id , purchase_description  , tax_included , purchase_cost , qb_asset_account_id , track_quantity , inventory_start_date  , quantity_on_hand , qb_sales_tax_id , qb_tax_code_id , abatement_rate , reverse_charge_rate , service_type , level, created_at , updated_at , expired_at , ancestor_id , signature , financial_signature , qb_object , print_grouped_items, company_id )
select qb_id , sync_token , qb_updated_at , qb_created_at , qb_expired_at , name , sku , description , active , qb_parent_id , taxable , unit_price , transaction_type , category_type , qb_income_account_id  , qb_expense_account_id , purchase_description  , tax_included , purchase_cost , qb_asset_account_id , track_quantity , inventory_start_date  , quantity_on_hand , qb_sales_tax_id , qb_tax_code_id , abatement_rate , reverse_charge_rate , service_type , level , created_at , updated_at , expired_at , ancestor_id , signature , financial_signature , qb_object , print_grouped_items, :tenant_id as company_id from :schema.qb_items;
update mig.migration_status set status='COMPLETED' WHERE schema=:tenant and table_name='qb_items';
EXCEPTION WHEN others THEN
            GET STACKED DIAGNOSTICS err_msg = MESSAGE_TEXT;
            update mig.migration_status set status='FAILED' WHERE schema=:tenant and table_name='qb_items';
raise notice 'EXCEPTION %', err_msg;
END main_block;
$$;

bt i am getting error as below :
psql:/home/ec2-user/scripts/qb_items.sql:14: ERROR:  syntax error at or near ":"
LINE 7: ...cial_signature , qb_object , print_grouped_items, :tenant_id...

That won't work, because the $$ that surround the body of your DO statement delimit a string literal, just like single quotes. And psql won't interpolate variables in a string literal.
You should write a PL/pgSQL function and pass the variable as argument in the function call.  If you need the function only temporarily, you can use the undocumented feature of a temporary function: create the function in schema pg_temp, and it will vanish automatically when your database session ends.


How to get desired result set? I have the following two tables
customers

| customer_id | firstname | lastname | email                |
|-------------|-----------|----------|----------------------|
| 1001        | John      | Doe      | johndoe@gmail.com    |
| 1002        | Lorem     | Ipsum    | loremipsum@gmail.com |
| 1003        | Alph      | Beta     | alphabeta@gmail.com  |

And orders

| order_id | customer_id | via | status     |
|----------|-------------|-----|------------|
| 100      | 1001        | WEB | pending    |
| 101      | 1001        |     | pending    |
| 102      | 1001        | POS | pending    |
| 103      | 1002        | WEB | processing |
| 104      | 1002        | APP | pending    |
| 105      | 1001        | APP | completed  |
| 106      | 1003        |     | pending    |

I want to get the following resultset:

All the customers who have ordered via WEB or (EMPTY) but not from POS
All the customers who have ordered only via APP

You can use an inner join between customers and orders where the via has the wanted values and a NOT EXISTS to check, that no unwanted via exists.

SELECT c1.*
       FROM customers c1
            INNER JOIN orders o1
                       ON o1.customer_id = c1.customer_id
       WHERE o1.via IN ('WEB', '')
             AND NOT EXISTS (SELECT *
                                    FROM orders o2
                                    WHERE o2.customer_id = c1.customer_id
                                          AND o2.via = 'POS');

(If the empty via is actually a NULL not an empty string, change o1.via IN ('WEB', '') to coalesce(o1.via, '') IN ('WEB', '') or to o1.via = 'WEB' OR o1.via IS NULL.)

Alternatively you can do it just with EXISTS and NOT EXISTS.

SELECT c1.*
       FROM customers c1
       WHERE EXISTS (SELECT *
                            FROM orders o1
                            WHERE o1.customer_id = c1.customer_id
                                  AND o1.via IN ('WEB', ''))
       AND NOT EXISTS (SELECT *
                              FROM orders o2
                              WHERE o2.customer_id = c1.customer_id
                                    AND o2.via = 'POS');

(Again, if the empty via is actually a NULL not an empty string, change o1.via IN ('WEB', '') to coalesce(o1.via, '') IN ('WEB', '') or to o1.via = 'WEB' OR o1.via IS NULL.)
Again you can use a join and NOT EXISTS.

SELECT c1.*
       FROM customers c1
            INNER JOIN orders o1
                       ON o1.customer_id = c1.customer_id
       WHERE o1.via = 'APP'
             AND NOT EXISTS (SELECT *
                                    FROM orders o2
                                    WHERE o2.customer_id = c1.customer_id
                                          AND o2.via <> 'APP');

Or just use EXISTS/NOT EXISTS.

SELECT c1.*
       FROM customers c1
       WHERE EXISTS (SELECT *
                            FROM orders o1
                            WHERE o1.customer_id = c1.customer_id
                                  AND o1.via = 'APP')
       AND NOT EXISTS (SELECT *
                              FROM orders o2
                              WHERE o2.customer_id = c1.customer_id
                                    AND o2.via <> 'APP');


Decrease Insert and Update performance on bigger database Have two partitioned tables (32 partitions / 16 files / 8 file groups). First table with 56 columns and Second table with 5 columns. One of requirment of application is to test procedures for future workload. I did it in Jmeter application. Put all procedures in one transaction controller:

GetSomeDataFromNonPartitioneTable1
GetSomeDataFromNonPartitioneTable2
InsertNewTransaction (insert new row in two partitioned tables)
GetOneRecordFromPartitionedTable
UpdatePartitionedTable

At the beggining of the test (0 rows in paritioned tables) got performance around 1300 operations per second ("one operation" means above set of procedures running one after the other). I tried same test (with same number of virtual users) when got around 4 000 000 of records in partitioned tables (4 000 000 per table). Get performance around 1050 operations per second. Going deeper I have got average response times:
1 test (0 rows in partitoned table)

GetSomeDataFromNonPartitioneTable1 - 22 ms
GetSomeDataFromNonPartitioneTable2 - 22 ms
InsertNewTransaction (insert new row in two partitioned tables) - 160 ms
GetOneRecordFromPartitionedTable - 22 ms
UpdatePartitionedTables- 136 ms

2 test (around 4 000 000 rows in partitoned tables)

GetSomeDataFromNonPartitioneTable1 - 25 ms
GetSomeDataFromNonPartitioneTable2 - 25 ms
InsertNewTransaction (insert new row in two partitioned tables) - 217 ms
GetOneRecordFromPartitionedTable - 23 ms
UpdatePartitionedTables - 177 ms

My questions:

Why bigger table decrease performance so much ?
What can I do to avoid it ? Bigger partitioned table got 2 non clustered indexes and cluster index, and smaller got only cluster index.

Assuming you're using Microsoft SQL Server (as your tag implies), if you're using JMeter, ensure your tests run directly on SQL Server and don't involve any middleman that can affect results. I'm not personally familiar with JMeter but the biggest difference you have between your two tests is about 57 milliseconds which is little enough to be affected by any possible variable in your process.
Although the difference between your two InsertNewTransaction tests is about a 35% difference, it's still only 57 milliseconds, which is very small. It can even be just the difference of a single run of one test vs it's second run, or more outside factors like temporary hardware blips. You'd have to run a large number of instances of each test to average and really compare when you're dealing with such granular time measurements.
That being said, some general advise is as follows:

INSERTS take longer when there are more indexes on a Table. So when applicable, reducing the number of low-use or unused indexes can help your INSERT performance.

Partitioning isn't meant to improve performance from a SELECT or INSERT perspective. Rather it helps with better granularity on maintaining the data, and can be used smartly to improve maintenance tasks performance (such as index maintenance).

If the table with 56 columns is being slow, then normalizing it, if possible, can help your INSERT performance (especially if you're not always inserting data for all 56 fields) and can help your SELECT performance.

If you need further performance critical changes, you can look into if Memory-Optimized Tables fit your use cases.


availability groups - checking for primary server - cannot call a procedure with parameters In my servers that are involved in alwayson availability groups if there are jobs running, inside the jobs I always test whether the current server is the primary in the availability group.

So the job only runs on the primary server of the AG.

something like this is working fine:

If sys.fn_hadr_is_primary_replica ('JUNOReporting') =1
BEGIN
        declare @reportYear int, @reportMonth int, @reportMonthPrevious int

        SELECT @reportYear  = CASE WHEN month(getdate()) = 1 THEN year(getdate()) - 1 ELSE year(getdate()) END,
               @reportMonth = CASE WHEN month(getdate()) = 1 THEN 12 ELSE month(getdate()) - 1 END

        SELECT @reportMonthPrevious  = @reportMonth - 1
        EXEC JUNOReporting.dbo.usp_some_other_procedure_without_parameteres
END

However, if instead of the procedure dbo.usp_some_other_procedure_without_parameteres that lives in the database called JunoReporting,
I want to call a different procedure that has parameters, then SQL Server does not like it.

I think he is putting the cart before the horses, but how can I tell him?

when I have the following code within a job, the job fails:

declare @reportYear int, @reportMonth int, @reportMonthPrevious int

SELECT @reportYear  = CASE WHEN month(getdate()) = 1 THEN year(getdate()) - 1 ELSE year(getdate()) END,
       @reportMonth = CASE WHEN month(getdate()) = 1 THEN 12 ELSE month(getdate()) - 1 END

SELECT @reportMonthPrevious  = @reportMonth - 1

If sys.fn_hadr_is_primary_replica ('JUNOReporting') =1
begin
    -- this server is the primary replica, do something here
    print 'yes, primary'

            IF @reportMonthPrevious >= 1
            BEGIN
                EXEC JUNOReporting.dbo.usp_ins_feesDuePaid_ForMonth @reportYear, @reportMonthPrevious

            END
            EXEC JUNOReporting.dbo.usp_ins_feesDuePaid_ForMonth @reportYear, @reportMonth
END

with the boring and obvious error message - that's exactly why I was testing if the current server is in the primary role anyway:

  Msg 976, Level 14, State 1, Line 22

  The target database, 'JUNOReporting', is participating in an availability group and is currently not accessible for queries. Either
  data movement is suspended or the availability replica is not enabled
  for read access. To allow read-only access to this and other databases
  in the availability group, enable read access to one or more secondary
  availability replicas in the group.  For more information, see the
  ALTER AVAILABILITY GROUP statement in SQL Server Books Online.

Any ideas how to work around this one?
I prefer not to add that piece of code inside the procedure, so avoiding the parameters issue sql server seems to have on the occasion.

Just to clear any misunderstanding:

On the seconday server  of my Availability Group (consists of 2 servers only)
the databases are not accessible as you can see on the picture below.

This happens because the procedure has parameters and the procedure is also called from a job step.

When you execute the query outside of the job context (ssms), it should execute just fine. The exact reason why this happens is still unclear to me, I tried getting the commands through the profiler and recreate it in my query window, but no dice there.

However, I do know that when the agent executes job steps, NON-Default behaviour can happen.
An example of this is the fact that quoted_identifier gets turned off inside the job step context:

From Microsoft:

  SET QUOTED_IDENTIFIER is ON (default)

Source

Anyhow, the workaround I used for your problem is putting it in dynamic SQL.

declare @reportYear int, @reportMonth int, @reportMonthPrevious int,@sql varchar(max)

SELECT @reportYear  = CASE WHEN month(getdate()) = 1 THEN year(getdate()) - 1 ELSE year(getdate()) END,
       @reportMonth = CASE WHEN month(getdate()) = 1 THEN 12 ELSE month(getdate()) - 1 END

SELECT @reportMonthPrevious  = @reportMonth - 1

If sys.fn_hadr_is_primary_replica ('JUNOReporting') =1
begin
    -- this server is the primary replica, do something here
    print 'yes, primary'

            IF @reportMonthPrevious >= 1
            BEGIN
               set @sql = 'EXEC JUNOReporting.dbo.usp_ins_feesDuePaid_ForMonth '+ cast(@reportYear as varchar(20)) +', '+ cast(@reportMonthPrevious as varchar(20))
               exec(@sql)
            END
                set @sql = 'EXEC JUNOReporting.dbo.usp_ins_feesDuePaid_ForMonth '+ cast(@reportYear as varchar(20)) +', '+ cast(@reportMonth as varchar(20))
                exec(@sql)
END


Finding tables & columns at risk of hitting max value of int I just took over a medium-sized postgres database and the team has been surprised with many tables hitting their auto-incrementing id int max limits which triggered unscheduled downtime to fix.
Does anyone know of a simple tool to help check all tables/columns and easily find which are at risk of hitting the int max limits? I'm sure we can come up with a script to check, but I remember reading somewhere of a tool to help me identify potential issues exhaustively.

As you mention auto-incrementing, it sounds like you want to check for sequence exhaustion - check_postgres has a sequence check which will warn about this.
Sequence metadata is stored separately from the associated tables, so is relatively easy to access without having to check each table individually. If you're using PostgreSQL 10 or later, the pg_sequences view provides a useful overview of the current state of all sequences. The default sequence naming makes it obvious which table/column the sequence is associated with (though it's also possible to create a query which returns the actual table/column name as well).


Oracle 11g RAC - PSU Simple question, when patching two nodes of 11g RDBMS, do I need to patch GRID as well, or only RDMBS is enough?

Thank you in advance

https://mikedietrichde.com/2017/04/26/keep-your-patch-versions-between-grid-infrastructure-and-databases-homes-in-synch/

  Can you have different PSU/BP versions between Database and GI homes?
  Yes, you can have a higher version PSU or BP in the Database home than
  in the Grid Infrastructure home managing the resources. This is
  implicitly documented in MOS Note 337737.1 – Oracle Clusterware
  (CRS/GI) – ASM – Database Version Compatibility where it says:

  “The Oracle Grid Infrastructure (GI) /Clusterware (CRS) version must
  be of the highest version down to the 4th digit in the possible
  combinations at all times.“

  Therefore you can deviate that this rule does not apply for the 5th
  digit anymore.


T SQL Table Valued Function to Split varbinary(max) into 16byte GUIDs I am trying to write a function which will split a varbinary(max) field into a table with 16 byte GUIDs, but am struggling. I haven't been able to find any examples or walkthroughs on how to do this using varbinaries - so any help appreciated. An example of the data in the varbinary field I am trying to split is:

0x09B57E7B1E19CD448698ECDA170B080F7C98FD411E5D264EB6D83AD6D4067C0D336E73299D2408438B625B09BED408D2D8E6C6DE4310194ABD90CF90F1558838731B2B740B508E44843FA98125ADF599669FC433BD4C074294C64E13D880A3ED78D25157C1E7A946B5B9C78E8DA0B5A8ECA73BB5CE59E6439586C445F25F2CDCA2B69483A7AE184A922DBC895C2EEB47910B88E915B7B94FB0EFC84568E2C83F0EF3168F62B6CB4EBA849486164F6DA7805C85383FBF694DB606904E0BA1364DC2DECDC1D00DED4BA28D0F1AA3DD9D1E1AA98697B914924995A26512C7EBD7C1

Thanks

This is easy if you have a numbers table. The following example is cut and pasted from Erland Sommarskog's site:

CREATE FUNCTION fixbinary_single(@str varbinary(MAX))
RETURNS TABLE AS
RETURN(SELECT listpos = n.Number,
              n = convert(int, substring(@str, 4 * (n.Number - 1) + 1, 4))
       FROM   Numbers n
       WHERE  n.Number <= datalength(@str) / 4 )


Find the disk size of specific rows I am trying to figure out the disk size a number of rows in various tables take up.

I can define this rows by a query. How can I find out the actual disk size that corresponds to the result set in SQL Server?

The reason why I want to know this, is that I am trying to analyse a multi-tenant system.
I am trying to get the total size each tenant takes up on the system.  So I need the size of all rows pertaining to each tenant and the size each tenant is using in index tables, etc.

The tenants all share the same schema, so it is not easy to do.

One way is to look at the sys.dm_db_index_physical_stats output. The min_record_size_in_bytes, max_record_size_in_bytes and avg_record_size_in_bytes will give you the sizes you want.

If you want to see the size of a particular record, another way is to find the physical row location, see SQL Server 2008: New (undocumented) physical row locator function. You can then dump the row physical structure, see Anatomy of a Record.

Finally you can use the product documentation to estimate the size:

Estimate the Size of a Clustered Index
Estimate the Size of a Nonclustered Index
Estimate the Size of a Heap


SQL Server: Database integration and synchronization I have n SQL Server databases with the same data schema.

On the other hand i have a web application that needs to retrieve, for some entities, the union of the information stored into the n databases.

A possible solution is to apply the ETL operations (Extract, transform and load) using SSIS (SQL Server Integration Services) and BIDS (Business Intelligence Development Studio).

With the application of the ETL operations I obtain a database (warehouse) that can be used from my web application. See the picture below for a basic sketch.

The problem is that the web application not only access the warehouse for read, but also for write and update information.

If an entity is updating into the warehouse, the same update need to be applied into the original source.

NB. the information into the source and warehouse can be updating frequently.

Can replication be a good approach to sync the update into warehouse to the right source or there is another tools that I can use?

Maybe there is another strategy instead of ETL and warehouse that I can consider?

If your update/insert always happens on a single one of the n databases AND you read a low number of rows on each request, you can use Linked Servers and Distributed Partitioned Views (DPV) to create the illusion of a single database: http://technet.microsoft.com/en-us/library/ms188299(v=sql.105).aspx. This can be done without introducing the complexity of replication.

DPV have several limitations and may give you poor performance if you are not careful. Without knowing more details of the queries you want to run - I can only advise that you try it and see if solves your problem.


Help optimising delete statement Using SQL Server 2012 Standard - I'm running a delete on a table based on the contents of another table. It's taking rather a long time (5 hours) and doesn't seem to be optimal to me, would appreciate some input optimising the statement:

delete from [dbo].[tbl1]
where exists (
    select *
    from [dbo].[tbl2] t
    where [dbo].[tbl1].[col1] = t.[col1]
    and [dbo].[tbl1].[col2] = t.[col2]
    and [dbo].[tbl1].[col3] = t.[col3]
)

The columns are as follows:

tbl1.col1 varchar(10)
tbl1.col2 datetime
tbl1.col3 varchar(60)
tbl2.col1 varchar(10)
tbl2.col2 datetime
tbl2.col3 varchar(30)

I realise that the datatype on col3 differs, I know this is bad, but would this mean the index cannot be used?

There is a non-unique clustered index on each table (not covered by this query) and a non-clustered index on both, covering all three columns included in the where clause.

tbl1 contains ~1.2 billion rows, tbl2 contains ~30 million rows. I'm expecting around 30 million rows to be deleted from tbl1.

Any help appreciated!

EDIT: FYI, tbl1 and tbl2 are on differing filegroups, but on the same disk (SAN). Also, here is the execution plan:

I've run into this kind of performance issue before, and breaking the delete up into discrete steps of a fixed size is what ended up doing the trick.  It allows SQL Server to commit changes more frequently, which is generally easier on the transaction log.

SELECT 'Begin Delete'; --gives @@ROWCOUNT a value
WHILE @@ROWCOUNT <> 0
    delete top (100000) from [dbo].[tbl1]
    where exists (
        select *
        from [dbo].[tbl2] t
        where [dbo].[tbl1].[col1] = t.[col1]
        and [dbo].[tbl1].[col2] = t.[col2]
        and [dbo].[tbl1].[col3] = t.[col3]
    );

The optimal batch size just depends on your server and what it can handle.  I've found 100,000 to be a safe size for me, and we've not got anything particularly powerful, so you may be able to get away with more.


Tools for looking at transactions inside of transaction log backup file? Are there any tools (commercial or open source) for looking at the contents of a transaction log backup file?  Occasionally, get a large transaction log backup file and would like to see the contents / transactions inside the log backup to know what is backing up log shipping.

If you are interested in yourself, you can check with sys.fn_dump_dblog the whole log file or log backup. Otherwise, there is a tool that I know but haven't tried Apex Log Reader.


Can you gain the performance benefits of partition elimination without changing SQL module or table definitions? I work for a company that has a multi-tenant database model that doesn't currently use database partitioning. The ideal end-state would be adding a new TenantID column to every existing table, including this new column as the first column in every primary key, and rewriting all existing SQL definitions to filter and equi-join on TenantID.

However, with thousands of existing SQL modules, this is a resource intensive solution, outside of a complete rewrite of the entire database. So my new plan is to make this update in phases.

Phase I will be keeping the existing database schema exactly as is, and creating aligned database partitions that use the existing SupplierID column (which is in nearly every table) as a proxy for TenantID. SupplierID already lines up well to what I want to do with TenantID. Existing suppliers are clustered into supplier groups, so a group of suppliers can be thought of as a single tenant.

So my question is. If I do this, create aligned database partitions on the existing tables without changing any table schema or the definitions of any existing SQL modules, will I see any performance benefits?

You can ask for additional information in the comments, and I will update the question to reflect them. But for starters there are many existing queries that filter on SupplierID, but few that equi-join on it.

Possibly, but in my experience you don't partition data for performance reasons. Yes, partition elimination can reduce the IO that some queries consume but there are complications that partitioning will introduce, see here for my favourite.

What partitioning does help with is maintenance. You can rebuild indexes, and stats (on SQL Server 2014+) at the partition level. This can be a huge win when you struggle to keep maintenance operations inside your maintenance window.

Partitioning is also great when it comes to loading and archiving data. The SWITCH statement is your friend here.

Lastly partitioning can also be used to optimise columnstore indexes by introducing a level of ordering to the data. This helps with dictionary pressure. Each partition acts as it's own columnstore index which means you get more global dictionaries as well as delta stores and delta bitmaps. Niko's blog has much much more info here


Patching SQL Server 2008 and upgrading to 2014 The environment which I am working right now is on SQL Server 2008, 2008 R2. Do you think its a good idea to first patch it with the latest service pack and then upgrade to SQL Server 2014?

Thanks

The SQL Server 2014 Upgrade Techincal Guide states that  you need the following as the minimal version for an in-place upgrade

SQL Server 2005: SP4 is required.
SQL Server 2008: SP2 is required.
SQL Server 2008 R2: SP1 is required.
SQL Server 2012: SP1 is required

So if you are not on the stated service pack level you will need to upgrade to at least that.

It is not neccessary, but start by running the upgrade advisor.
Thomas LaRock has a checklist on his page both for the tasks needed before an inplace upgrade and what to do afterwards.


Why do SQL Server uniqueidentifier values look different when converted to Db2? I am currently trying to convert a SQL Server database to DB2. I am using the Data Conversion Workbench (DCW) for IBM Data Studio. The issue I have is that DCW can not seem to correctly extract data from SQL Server to flat files. All the columns in my database (in SQL Server) that have the data type of uniqueidentifier are not extracted correctly by DCW. Any data that is of the uniqueidentifier type is written as random symbols and characters. What could be the reason?

I feel your pain. I've worked with both SQL Server and DB2. I will admit that IMHO, the data extraction and importing tools are not as simple or straightforward to use in SQL Server as they are in DB2.

If you wish to stick to the database tools, here is what I recommend: Create queries to extract your data. Use CONCAT to encase each field with a character (one that won't conflict with your data). Hopefully you don't have millions of rows. Then run the query and copy and paste results into something like Notepad++ and save it as a CSV file. You can now use db2import to pull that data in much cleaner and easier. Clunky, yes. But its the best I've found. I've yet to find an easy way to use bcp or SSIS to do what db2import, db2load, and db2export do....

Anyway, if that doesn't suit your fancy, or if the data sets are too large for that, you may wish to consider a different tool. There are ETL tools (perhaps you have one in shop?) to move the data across. Microsoft's SQL Server Integration Services (SSIS) or IBM's InfoSphere DataStage are just two examples of this. (I've not really worked with SSIS, and what little I tried, it wasn't simple or intuitive to me compared with db2export. But perhaps that is just me and I need further exposure/training.)

Another tool (and one I can happen to leverage as I am not only a DBA, but also a Java developer) is to leverage an existing API such as pure JDBC in tandem with Apache POI or Apache Commons-CSV to get the data extracted in simple format. POI lets you extract to Excel format (both .xls and .xlsx) and Commons-CSV lets you obviously extract to CSV format. And once again, when you have the data in CSV format, it is easy thing to import/load into DB2.

This will help with some of the data issues. You still may need to spot check the data to make sure you don't have any codepage conversions going on. If so, you may need to deal with that in your extraction process.

I realize others will mention bcp or SSIS. I can only speak to what I have experienced. But hopefully I've given you some other options to consider.


SQL - Return most recent batch of sequential rows Version:
Microsoft SQL Server 2014 (SP3-CU4) (KB4500181)

What i'm trying to accomplish here is pull in all the rows where there is a break of less than lets say 40 days between the next row.

So in the first example there is an uninterrupted break every month (roughly). So I would like the query to pull in all records here.

Example 1

CREATE table #Temp1
(
id Varchar(50),
DT8 int,
series int
)

INSERT INTO #Temp1
VALUES
('147501',20181022,1),
('147502',20181115,1),
('147503',20181211,1),
('147504',20190110,1),
('147506',20190306,1),
('147505',20190205,1),
('147507',20190402,1),
('147508',20190429,1),
('147509',20190529,1),
('147510',20190603,1),
('147511',20190703,1),
('147512',20190802,1),
('177901',20190905,1),
('177902',20191001,1),
('177903',20191108,1),
('177904',20191205,1),
('190401',20200103,1),
('190402',20200205,1)

In example two there is a gap greater than 40 days between Sep 25 2019 and January 29 2020.  So I would like the query to just pull in the most recent subsequent block.  In this case it would just be the top record.

Example 2

INSERT INTO #Temp1
VALUES
('164701',20190605,2),
('164702',20190703,2),
('164703',20190802,2),
('164704',20190904,2),
('164705',20190925,2),
('189101',20200129,2)

I have started down this road, and was looking at using LEAD to calculate the number of days between the current and previous rows.  I realize I probably need to break the years out to account for the case when moving to a new year or convert it to a real date so that I can use some sql functions to calculate the difference in days for me.

After that I wasn't sure how to go about only returning the most recent consecutive block.  Thought I would ask here to see if anyone had any insight on how to accomplish this.

WITH
cte1 AS
(
SELECT series,
       id,
       `date`,
       DATEDIFF(LEAD(STR_TO_DATE(`date`, '%Y%m%d')) OVER (PARTITION BY series
                                                          ORDER BY `date`),
                STR_TO_DATE(`date`, '%Y%m%d')) diff
FROM test
),
cte2 AS
(
SELECT series,
       id,
       `date`,
       COALESCE(SUM(diff > 40) OVER (PARTITION BY series
                                     ORDER BY `date` DESC), 0) grp
FROM cte1
)
SELECT series,
       id,
       `date`
FROM cte2
WHERE grp = 0
ORDER BY 1,3

fiddle


relationship between transaction chaining and autocommit I am trying to understand the relationship between the concepts of transaction chaining and autocommit. Autocommit is usually exposed in a client-side programmatic interface. E.g. in JDBC with the Connection#setAutoCommit method.

Namely I would like to know whether:

these concepts are simply aliases of one another (and what is the correspondence)

or

these concepts are orthogonal and all 4 combinations make sense and yield different results (and what these results are)

There's a number of resources that suggest the case is 1. The same resources seem to suggest that unchained corresponds to autocommit on. E.g.
this post says that:

  set chained off (default in Sybase, = set autocommit on)

This more extensive post similarly says:

  Some database programming interfaces have an autocommit mode, also called unchained mode. In this mode, each statement is a transaction, and is committed after execution. If you wish to use transactions in your applications, you need to be using manual commit mode, or chained mode.

This has been my own understanding so far. For example, whenever I try to execute (using JDBC) a stored procedure and I run into the following message:

  java.sql.SQLException: Stored procedure 'whatever' may be run only in unchained transaction mode.

… then setting in my code:

conn.setAutoCommit(true);

… fixes the problem which seems to confirm that indeed unchained is the same as autocommit.

However looking at the Sybase ASE documentation one reads:

  The default mode, called unchained mode or Transact-SQL mode, requires explicit begin transaction statements paired with commit transaction or rollback transaction statements to complete the transaction.

This seems to be the exact opposite of autocommit - unless the above text is understood as having the following meaning:

  The default mode, called unchained mode or Transact-SQL mode, requires
  explicit begin transaction statements paired with commit transaction
  or rollback transaction statements to complete the transaction when one wants to place more than a single statement in a transaction.

Finally, I've come across this post which suggests that chaining and autocommit are mostly orthogonal.

AutoCommit true is identical to chained off

AutoCommit false is identical to chained on

In chained mode your first statement will start an implicit transaction, which will stay active till you manually commit or rollback.

Even though the manual says the default mode is unchained this is not true for client libraries. The JDBC and Python drivers use a default mode of chained unless you overwrite that.


Should SSIS packages and SQL database be on same server? I'm a bit new to SSIS. I have a lot of 'dev', building, query, tuning performance on SQL Server but am not the master of admin/ overall performance modeling.

So here's my question --- Should SSIS packages be run on the same server as the data warehouse (SQL server databases?)

For one, I already get a cryptic error message that certain 'bulk insert' tasks in SSIS can only be run against local databases. Maybe this can be worked around. Maybe it cannot and is much faster, hence put the ETL on the same box as the db.

Second --- even if that can be worked around, wouldn't it be faster anyway to have the ETL on the same server as the database in any case? Or barring that, as close as geographically as possible? (ie not different continents).

In particular, the ETL ones run overnight, and the DB is not worked on overnight (possibly backup processes, however) -- but I don't foresee much tripping on each other.

If bulk insert tasks can only be done on local DBs, wouldn't the test/ dev environment need to be setup on the DB server as well? Just wondering what is sensible. It's a smaller shop here but we stretch things.

Oh god no, do not use the SQL Server Destination.

At this point, the negligible performance gains are not worth the random error it may throw while loading data. OLE DB Destination is rock solid, provides excellent performance and doesn't suffer from the infernal errors you can run into with the SQL Server Destination. Both components can store data into SQL Server and as a bonus, the OLE DB Destination allows you to run your SSIS package from any server.

SQL Server Destination works by some very clever voodoo, as I understand it. Basically, the SSIS engine lays the data as it would load into SQL Server and whoosh it gets sucked in with a minimal amount of checking on the database side since SSIS super promises, double pinky swears, that the data is good.

The issue that plagued many users in the 2005/2008 days was that something would happen between thinking about inserting the data and actually inserting it wherein the only reported error would be something exceptionally insightful like "The destination component reported an error." However, if you immediately restarted the package - no changes to the source or destination database, the data would load just fine.

As for the architect level questions, if you design your packages using the OLE DB Destination, you can be infinitely more agile than using the SQL Server Destination. What is the one thing SQL Server wants more than anything else for good performance? Memory. The more information it can keep in RAM, the better performance you will have. How long does SQL Server keep that memory? Until it reboots or until things get really bad and it is forced to give back lest the server topple over.

SQL Server Integration Services is a high performance ETL engine. It is able to transform the data very quickly by keeping it all in memory instead of writing it to temporary files or tables. The more rows of data it can load into memory, the more concurrent changes it can process and your throughput is increased. However, the general pattern for SSIS resource consumption is that it's bursty. Sure, it might look like hungry hungry hippos as it starts up consuming everything it sees but it runs - maybe for minutes, maybe hours but as soon as it's done, all the resources are freed and available for general processing.

Since SSIS is an in-memory ETL tool and SQL Server really likes memory, they're gonna fight. Maybe not today. The server has 256 GB of memory, SQL Server is only using 180 so there's a good 30% left for OS level processes and SSIS plays nicely in that space.

Time passes and Oh, the business is growing and we have new web thingers beating on the database all day long and there are now all these analysts are going against the server with Access and pulling all the data just to filter it for "today" once it gets to their desktop. Now SQL Server is asking for more and more memory. You can change the max server memory setting in SQL Server to give a little more to the database engine but at a cost for other OS tasks, like SSIS (it doesn't run in SQL Server's memory space).

At some point, you might run into a situation where you've maxed the server's physical memory and trimmed down everything running on that box that isn't SQL Server and the only thing left is SSIS. So now what?

In an OLE DB Destination world, you stand up another instance (or use an existing, less taxed box), deploy/move your packages from server 1 to 2. As long as permissions and any file system dependencies exist on the new box, you're done.

In a SQL Server Destination world, you have to edit every package and change the destination component, test, go through any change management review process and then deploy.

Something is going to be your limiting factor for performance with SSIS: Memory, Network, Storage, CPU. If you have packages pulling terabytes of data across the wire, only to write kilobytes of data, yeah that subset of packages might need to run as close to the source as possible.

Novice SSIS developers can write some really memory intensive packages. For example, they need to bring together customers from server1.database1 and their orders, also in server1.database1. If they just use the tools, they might have two OLE DB Source components in there that bring all the customers and all the orders into memory. To join the data, they would then need to use a merge component. Oh, but that has a requirement for sorted data. No worries, there's a handy dandy Sort component right there in the toolbox so we add one of those to each of our sources and then wire up the join. At this point, we just pulled all that data into memory, sorted it all in memory and then halved our available memory into the "before the asynchronous/blocking component" and "after the blocking component" Categorizations here

No problem, they rewrite and realize they can add an explicit ORDER BY in the source, and mark it as such, and eliminate two SORTs but they still have to pay the penalty for the join.

Or, someone who actually writes queries sees what they're doing and says "let's use a single query to get this data. Let the database engine use its indexes to make this efficient"


All Data Transformations via SSIS Some folks here at my current job have made the decision that all data transformations that go into our database must be done via SSIS. This means that any old code such as views, stored procedures, functions, etc. that are used to transform data will no longer be used. The transformations they currently perform will be done via SSIS only. I am trying to come up with a list of potential issues we may face and I don't know as much about SSIS as I do about SQL Server being that I am a DBA here. One of the issues I see is attempting to look at performance issues in SSIS vs sql objects. What are the other things I am missing? We are using Microsoft SQL Server 2016 SP2 and SSIS 2016.

I have seen this done.  And while it's not something I would ever recommend, it does have some advantages from a process and skillset point-of-view.  The main reason you would try this is to have a consistent drag-and-drop process for building, and importantly, changing ETL processes.

Issues:

1) Performance.  Performing transformations in SSIS data flows is just slower than using TSQL.

2) Productivity.  Using TSQL is, perhaps, a more difficult skill, but for someone who can do both, it's massively more productive to build and test using TSQL than SSIS data flows.


What is the purpose of adding new file to the database? I'm fairly new at Microsoft SQL. I've seen many tutorials online on how to add a file to the database. What I still don't get is why?

You could

put huge archive data onto a big but slow drive
put structural information ("meta data") into its own file
put indexes etc. on a fast drive
put large readonly data (e.g. old legacy data) onto a SSD
set up a more intelligent backup strategy

I'm sure there's still more to add :-)


Query Performance and Single Quotes We have a table as below

CREATE TABLE "ABC"(
"ID" NUMBER (18, 0) NOT NULL,
"ID2" VARCHAR2(15 char) NULL,
"VERSION" VARCHAR2(30 char) NULL,
"TIMESTAMP" DATE  NULL,
 CONSTRAINT "PK_ABC" PRIMARY KEY ("ID")
);

ALTER TABLE ABC ADD  CONSTRAINT CK_ABC_ID2_VERSION UNIQUE(ID2, VERSION);

CREATE UNIQUE INDEX IDX1_ABC ON ABC
(
    ID2,
    VERSION
);

CREATE INDEX "IDX2_ABC" ON ABC
(
                VERSION ASC,
                ID2 ASC,
                TIMESTAMP ASC
);

say there is a high volume of data on the same table. Would you be able to help conclude which Query from below would have better performance and why

First query:

SELECT ID, ID2, VERSION, TIMESTAMP
FROM ABC WHERE ID2=123
  AND VERSION =54321

Second query:

SELECT ID, ID2, VERSION, TIMESTAMP
FROM ABC
WHERE ID2='123'
  AND VERSION ='54321'


Fiscal Year dynamic function (Oracle SQL) My Fiscal Year starts on 1st October (2017) and ends 30th September (2018)

I want to write a function that will dynamically calculate first day of October.

Currently I have:

select Add_Months(Trunc(Add_Months(sysdate,3),'YYYY'), -3) from dual

create or replace function fiscal_year_start_date(p_date date) return date
as
  pragma udf;
begin
  return add_months(trunc(add_months(p_date,3),'YYYY'), -3);
end;
/

alter session set nls_date_format='YYYY-MM-DD';

select fiscal_year_start_date(date'2016-01-01') from dual;

FISCAL_YEA
----------
2015-10-01

pragma_udf is optional, it is a 12c feature that boosts performance of such user-defined functions a bit when used in plain SQL (but may degrade in PL/SQL).


Recalculate row size in SQL Server 2008? It is possible to recalculate row size in SQL Server 2008? Take this example:

declare @counter int
declare @statement nvarchar(max)
set @counter=0
drop table kua2
create table kua2(id int)
while @counter<307
begin
    set @statement = N'alter table kua2 add id'+CAST(@counter as nvarchar(max))+N' nvarchar(max)'
    exec (@statement)
    set @statement = N'alter table kua2 drop column id'+CAST(@counter as nvarchar(max))
    exec (@statement)
    set @counter=@counter+1
end

alter index all on kua2 rebuild

dbcc cleantable (0,'kua2',0)

alter table kua2 add id0 int
alter table kua2 add id1 int
alter table kua2 add id2 int
alter table kua2 add id3 int

When I add id3 column, I got warning about 8060 bytes, but the table has only 5 int columns, it still count the dropped nvarchar(max) columns in row size.

The only thing that helps is recreate the table. But for several reasons I don't want to do that. Is there any way to tell the SQL Server to recalculate the row size somehow?

This is a bug (albeit a fairly minor one) that still exists in SQL Server 2012 RC0. Report it at connect.microsoft.com

(Thanks Mr Denny).


Print the execution time on messages Pane? How I can print the execution time of each query on messages Pane? For example the Select * from tbl1 runs for 10 mins then Select * from tbl2 runs for 15mins and so on..

Select * from tbl1
Go
Select * from tbl2
Go
Select * from tbl3
Go

DATEDIFF will be useful

DECLARE @StartTime1 datetime

SET @StartTime1 = GETDATE()

select query 1

SELECT ExecutionTimeInMS = DATEDIFF(millisecond, @StartTime1, getdate())

GO

similarly do for other queries. @starttime2, 3


Fix ShrinkFile / ShrinkDb database my DBA obviously thinks that it is best practice to use shrinkdb / shrinkfile all the time some bigger table is truncated / deleted just a little bit.

I tried to talk sense to him and explain him how it works but he won't listen.
So I end up with a database / database files with messed up pages.

Is there any way that i can fix that for certain tables. Does an Index and Table Rebuild also reorders / reallocate the data or does it just logically rebuild it?

In the end i want new fresh data pages because the old ones might be copied to the front of the data file due to shrink usage.

Does your DBA get paid by how little disk space is used or something?
This is not only useless, but is an actively harmful practice.
Make one final attempt to convince your DBA (or their manager) with the following article, written by someone with unimpeachable credentials on this topic (Paul Randal, who was, for a while, in charge of development on the SQL database engine at Microsoft):
Why you should not shrink your data files:

A data file shrink operation works on a single file at a time, and uses the GAM bitmaps to find the highest page allocated in the file. It then moves it as far towards the front of the file as it can, and so on, and so on. In the case above, it completely reversed the order of the clustered index, taking it from perfectly defragmented to perfectly fragmented.
The same code is used for DBCC SHRINKFILE, DBCC SHRINKDATABASE, and auto-shrink – they’re equally as bad. As well as introducing index fragmentation, data file shrink also generates a lot of I/O, uses a lot of CPU, and generates loads of transaction log – as everything it does is fully logged.
Data file shrink should never be part of regular maintenance, and you should NEVER, NEVER have auto-shrink enabled.

(emphasis mine)
In my experience, 15-20% internal free space within databases is normal and expected. Either way it is going to be free space, there is literally no good reason to have that disk space sitting outside the file as opposed to inside the file.
If your DBA can't be educated (or replaced, and you can't appeal over their head), the only thing I can possibly suggest is to see how much you can reduce  table fragmentation, so your DBA hardly ever finds anything to shrink:

First, make sure that primary keys are set so that newly inserted rows always appear at the end in your current data order, so tables always grow in a contiguous manner.
Make sure your tables and indexes have a smaller "fill factor" (which leaves more free space within each data page), so that updates/inserts are less likely to create page splits and fragmentation.
Finally, rebuild your tables and indexes into a brand new filegroup using CREATE INDEX … WITH (DROP_EXISTING = ON) ON, as discussed near the end of Paul's article. This will make them contiguous from the very beginning of the file.
When small fragmentation does starts to occur, defragment the file, which slowly moves pages in-place, instead of rebuilding, which more often than not tries to move the table or index to a different area.

This won't keep them contiguous forever, especially if someone deletes a big range from the beginning or middle of a table, but it might help.