texts: Mongodb find() operation sometimes extremely slow

texts:
I have a collection in mongodb (latest 3.0.4, not wiredtiger) with about 8 million documents. For the most part it's very fast but sometimes all reads to the database start stacking up and the culprit is the following query:

> db.currentOp();
{
    "inprog" : [
        {
            "desc" : "conn619",
            "threadId" : "0x858e8a0",
            "connectionId" : 619,
            "opid" : 4190651,
            "active" : true,
            "secs_running" : 171,
            "microsecs_running" : NumberLong(171584220),
            "op" : "query",
            "ns" : "default.products",
            "query" : {
                "$query" : {
                    "ps" : {
                        "$gt" : 0
                    },
                    "price" : {
                        "$type" : 1
                    },
                    "ships_to_australia" : true,
                    "category_memberships.node" : "2344577"
                },
                "orderby" : {
                    "salesRate" : -1
                }
            },
            "planSummary" : "IXSCAN { salesRate: -1.0 }",
            "client" : "142.0.36.114:29880",
            "numYields" : 135044,
            "locks" : {

            },
            "waitingForLock" : false,
            "lockStats" : {
                "Global" : {
                    "acquireCount" : {
                        "r" : NumberLong(270088)
                    }
                },
                "MMAPV1Journal" : {
                    "acquireCount" : {
                        "r" : NumberLong(154166)
                    },
                    "acquireWaitCount" : {
                        "r" : NumberLong(26)
                    },
                    "timeAcquiringMicros" : {
                        "r" : NumberLong(101532)
                    }
                },
                "Database" : {
                    "acquireCount" : {
                        "r" : NumberLong(135044)
                    }
                },
                "Collection" : {
                    "acquireCount" : {
                        "R" : NumberLong(135044)
                    },
                    "acquireWaitCount" : {
                        "R" : NumberLong(19122)
                    },
                    "timeAcquiringMicros" : {
                        "R" : NumberLong(14462598)
                    }
                }
            }
        }
    ]
}

The strange thing is the identical query is fast most of the time just sometimes it's incredibly slow like this and seems to lock any further read operations to the database completely which I thought shouldn't happen in mongo.

The server is dual CPU with 48GB of ram so the issue shouldn't be caused by hardware.


texts: Avoid already existing objects when importing into Oracle

texts:
I need help with impdp. I have taken a backup with:

expdp system/pw@orcl full=y directory=abc dumpfile=full.dmp

Now I need to import the dump into another instance, which has recently been created. I was going to import the full database dump, but it raises an error when an object already exists.

However, these objects belong to Oracle's default schemas, which I can not remove. How do I avoid these import errors?


texts:
I need to set up a SQL Server for a development environment for a client. First I installed SQL Server 2008, I was able to connect to it from SQL Server Management Studio fine but when I tried to restore the .bak it said

  the database was backed up on a server running version 10.50.2789.
  That version is incompatible with this server, which is running
  version 10.00.5500.

After googleing I found I needed SQL Server 2008 R2 to match that version number, so I downloaded that from the Microsoft website and ran it. But I keep trying to set up a new server instance so I can restore the .bak but it's still creating another instance in SQL Server 2008 when I install it with version number 10.0.1600.

Can anyone suggest how I can set up a server running version 10.50.2789?

Thanks


texts:
I have changed my MyISAM tables to InnoDB and from that day, iostat is increasing continuously. My findings on the server say that mysqld is the one who is increasing iostat.

But how can it increase continuously?

write is heavy. And server has 8GB.am pasting my.cnf

innodb_data_file_path = ibdata1:10M;ibdata2:20M:autoextend
innodb_autoextend_increment=5
innodb_file_per_table
innodb_log_group_home_dir = /var/lib/log/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 2048M
innodb_additional_mem_pool_size = 2M
#innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
#innodb_log_files_in_group=2
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_log_file_size = 40M
innodb_log_files_in_group = 2

innodb_thread_concurrency=16
innodb_thread_sleep_delay=1000

innodb_max_dirty_pages_pct = 80

query_cache_size=512M
query_cache_limit= 1M

max_heap_table_size = 32M
tmp_table_size = 64M

read_buffer_size = 512K

innodb_log_buffer_size=8M
thread_cache_size = 70

table_cache=2048

This iostat started increasing when I changed engine of my tables from MyISAM to InnoDB.

Please help now.


texts: Problem with MySQL Master/Slave reboot when in replication

texts:
There is a master and number of slaves connected to it.

Due to some patches, we have to Reboot the Master and Slave.

Afterwards, we have to make sure replication is running fine.

While doing this process, what precautions should I take so that I don't get stuck anywhere? After rebooting, I also have to validate that all the slaves are working fine.


texts: Why insert in with is returning unexpected results?

texts:
Context

Schema setup

create table users( id serial primary key );

Query 1

with t1 as (
    insert into users default values
    returning id
), t2 as (
    insert into users default values
    returning id
)
select id from t1;

Expectations

1

Result

1

Query 2

with t1 as (
    insert into users default values
    returning id
), t2 as (
    insert into users default values
    returning id
)
select id from t2;

Expectations

2

Result

1

Query 3

with t1 as (
    insert into users default values
    returning id
), t2 as (
    insert into users default values
    returning id
)
select id from t1
union all
select id from t2;

Expectations (taking in account results of Query 2)

1
1

Result

1
2

Question

What's wrong with my expectations and how to get the expected value from Query 2?


texts:
I have two, almost identical, databases and I am trying to figure out what is causing queries to perform that much slower in one of them. While investigating using the information_schema.statistics table I noticed that the cardinality column for one of the indexed fields was triple in size compared to the same column in the other database. e.g.
For the first database the cardinality is

+------------------------+-------------+
| column_name            | cardinality |
+------------------------+-------------+
| date_time              |      46202 |
+------------------------+-------------+

and for the second database:

+------------------------+-------------+
| column_name            | cardinality |
+------------------------+-------------+
| date_time              |      185052 |
+------------------------+-------------+

Since the cardinality is

  An estimate of the number of unique values in the index.

I got wondering about the unique date_time  for this columns. Turns out that they are the same (select distinct date_time from table = 54187).

So if the unique values are exactly the same, how come the cardinality in the second database for the column date_time is almost four times the one on the first database?


texts: Efficient way to perform approximated search?

texts:
I have to perform a join between table_a and table_b, using three fields as key being one of them the date of the event, say, both tables have id1, id2 and evnt_time for eache record.

As it happens though, the evnt_time can be displaced in a few seconds between these tables. Thus the join has to behave sort of table_a.id1 = table_b.id1 and table_a.id2 = table_b.id2 and table_a.evnt_time ~ table_b.evnt_time, where:

~ means approximately, given + or - seconds
There must be a way to give ~ parameters of how precise should be. e.g.: table_a.evnt_time ~(2) table_b.evnt_time will join table_a.evnt_time with table_b.evnt_time - 2s, table_b.evnt_time - 1s, table_b.evnt_time, table_b.evnt_time + 1s and table_b.evnt_time + 2s.

To tackle the situation, there are a few possibilities I am experimenting, but my doubt is on which architecture should I use to perform a very efficient "approximated join" - these tables are partitioned, sub-partitioned and each sub part may contain billions of records... (although I have a reasonable amount of resources).

For once, I thought of storing the unique sencond of the event (i.e. second it happened since julian calendar) on both sides so the real join (give a "~(2)") could simply look like:

select *
from
    table_a,
    table_b
where
    table_a.id1 = table_b.id1
    and table_a.id2 = table_b.id2
    and (table_a.evnt_sec = table_b.evnt_sec
        or table_a.evnt_sec = table_b.evnt_sec + 1
        or table_a.evnt_sec = table_b.evnt_sec + 2
        or table_a.evnt_sec = table_b.evnt_sec - 1
        or table_a.evnt_sec = table_b.evnt_sec - 2)

But I am unsure of how efficiently would that perform with the indexes and scans..

This is just an example, I am not stuck to any sort of architecture.
Also, I am using Oracle 11gR2.


texts: How can a field with multiple values be used with SQL Server?

texts:
For example, I have a field in MongoDB like this:

"SignedDocs" : [

    {
     "_id" : "blablabla"
     "title": " bla bla bla"
     },

    {
     "_id" : "blablabla2"
     "title": " bla bla bla2"
     },

    {
     "_id" : "blablabla3"
     "title": " bla bla bla3"
     }
]

For you to understand, inside this field there are 3 values. What would be the best way to achieve this with SQL Server? What is the best practice to save 3 values inside a column?

I don't want to do something like this:

NAME    | DOCSSIGNED
abduh   |      1
abduh   |      2
abduh   |      3

Having lots of rows with the same values only changing this DOCSSIGNED field as a FK pointing to the DOCS table.

EDIT: Ohh maybe a 3rd table between these 2 linking them?


texts: There is insufficient system memory in resource pool default to run this query

texts:
I'm using SQL Server 2014 - 12.0.2269.0 (X64) on Windows Server 2012 and I'm experiencing some memory issues. When I run a stored procedure that is doing a quite "heavy" calculation, I get an error after something like 10 minutes:

  There is insufficient system memory in resource pool 'default' to run this query.

My SQL Server has multiple databases (like 15, but they are not always used at the same time). I looked to the SQL Server log file (after I got the error), and I saw a lot of lines like this:

  2015-12-17 12:00:37.57 spid19s     Disallowing page allocations for database 'Database_Name' due to insufficient memory in the resource pool 'default'. See 'http://go.microsoft.com/fwlink/?LinkId=330673' for more information.

A report is generated in the log with the memory used by each component (I think). If I correctly interpret the report, we can see that there's a lot of memory consumed by MEMORYCLERK_SQLBUFFERPOOL. You can find the report here: http://pastebin.com/kgmk9dPH

I also generated a report with a graph that shows the same "conclusion":

Here's maybe another useful reports:

Note that I've also seen this error in the log:

  2015-12-17 12:04:52.37 spid70       Failed allocate page due to
  database memory pressure: FAIL_PAGE_ALLOCATION 8

Here's some information regarding the server's memory:

Total memory on the server: 16 Gb
Memory allocated to SQL server: 12288 Mb
Physical Memory In Use (from sys.dm_os_process_memory): 9287 Mb

If it can help, the server doesn't host any SharePoint database.


texts: How to setup filtered transactional replication

texts:
Configuration/Setup

PUBLISHER: SQL Server at branch premises having only specific branch data (ex: branch ID 135)

SUBSCRIBER: SQL Server (live at data center) used by application having data for all the branches.

The publisher needs to send the data changes (only for branch ID 135) from local branch to the subscriber (the data center SQL Server). The subscriber must store the replicated data from branch ID 135 without affecting any other branch data from the live tables.

I have configured Transactional Replication to replicate the changes performed on the Transaction tables on the publisher, which stores data only for 1 branch with Branch_ID 135.

The distributor runs on the publisher. I have filtered the publication tables/articles on Branch_ID=135.

Issue/Problem

Subscriber data gets fully refreshed and all the branch id data gets deleted. The Subscriber is left with data only for Branch ID 135, which is replicated from the Publisher.

I have a test server to perform this configuration/testing.

Can you please help me out where am I missing my Replication configuration?


texts: Is it possible to find when the last insert took place?

texts: I have several tables that I'm not sure whether they are still in use by some old tools. Not all of them have columns with timestamps so I was wondering if there is some secret internal sql-server table where I could see when the last insert (or any other operation) took place for certain tables?


texts: Is Information in sys.dm_db_index_usage_stats reliable

texts:
I'm archiving data from an old system that has no documentation. Lucky me...

I'd like to find out when the tables were created, last accessed etc. Can I trust that this query will give me the correct answer or is there some parameters that I first need to check? SQL Server 2008 R2:

 SELECT t.Name AS Tabelname, p.rows AS NoOfRows, MAX(us.last_user_lookup) AS LastUsed, t.create_date AS CreatedDate
FROM sys.tables t
INNER JOIN
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
LEFT JOIN --A lot of the tables did not have any records in this table
    sys.dm_db_index_usage_stats as us ON t.OBJECT_ID = us.OBJECT_ID
GROUP BY t.Name, p.rows, create_date
ORDER BY MAX(us.last_user_lookup) DESC


texts: Replication Monitor reports little to no latency, but my tracer token is taking hours to go from Distributor to Subscriber

texts:
I sent a tracer token through a replication job that was reporting about 2 seconds of latency about two and a half hours ago.  I sent the token through because I noticed a row that was inserted on a published table had not been sent to the replicated table.

I'm not sure what this indicates and would appreciate any help in troubleshooting it.  Thank you.

UPDATE:
My replication publication hasabout 30 tables. Most of these tables are being replicated as expected. When I run a trace I can see their sp_MSupd_dboXXXXX procedures running, I can verify that the data is in sync.

However, for several tables, this is not happening. When I looked in the database the table was being replicated to, it did not even have its own sp_MSins, upd, and del procedures.

When I run sp_browsereplcmds and provide its publication_database_id and article_id, I can see that the command is waiting to be run.

For example, it might say {CALL sp_MSins_dboTableName (fields here)}. It will sit there for a while, then disappear.

My first assumption was that it wasn't being applied because the procedure didn't exist. So I wrote my own sp_MSins_ proc to confirm this (@c1 datatype, @c2 datatype etc...) and tested it to be sure it worked.

But even then, when I do an insert, the command sits in the repl_commands table for a while, then disappears, and no row ever hits my table.

I'm getting pretty lost here. Can anyone help? Thanks!

Some more detail:

The distribution DB lives on the subscriber. There are several publications from the same database on the publisher to the same database lon the subscriber (three to be exact, two work fine, one does not).

On two of the publications, a tracer token is processed within about 4 seconds. On the one I'm having trouble with, the tracer token hangs in limbo indefinitely.


texts:
I have a fairly small database ~200MB which when running in the web edition of SQL azure is lightning fast. I've switched it to the basic edition and all of a sudden a group of queries which took less than 1 second to execute is now taking ~25 Seconds (running in basic mode)
Switched it to a D1 and the halved the speed to 12.5 seconds.

Running on premise with an average server and the query took ~1 second.

In both tests I was the only one using the database.
I'm shocked that it's running so slowly. I know the new databases work slightly differently but was really shocked at the performance difference.

Does anyone have any good suggestions on how to optimise your database design to improve  performance when running in SQL Azure.

If anyone has any suggestions for me to check I'd love to hear it. I'm figuring that it's the amount of queries which it's executing in one hit.

I'm about to create a view to do all the querying in one hit. Something that I've already run on the database and on the basic edition it's instant.

This is only a little test app so it's easy for me to alter but i'm concerned about our productions apps which may suffer from the same issue.


texts: How to TRIGGER/RETURNING this generator function in PostgreSQL?

texts:
I am providing a key table to my users in various categories so I am thinking how to TRIGGER/RETURNING the function with each SELECT.
Functions to generate shorthand unique IDs

The thread answer Generating human-readable/usable, short but unique IDs but no salt so no.
http://hashids.org/ can be a valid option as proposed by the answer here
Any default PostgreSQL options?

Example of key table where I need the keys

Module 1
1. <key1>
2. <key2>
3. ...

Module 2
1. <key1>
2. <key2>

Keys are bound to the SERIAL primary keys in the table event_log.
I am thinking if the database can do the generation task for you i.e. how to bind your primary key to any algorithm that can generate such shorthand forms.
I have currently the data as the following in the table where SERIAL PRIMARY is not so clear

CREATE TABLE event_log (
    data_id SERIAL PRIMARY KEY NOT NULL,
    name VARCHAR(10) NOT NULL,
    module INTEGER NOT NULL, - - 1 or 2 or 3 as value
    time TIMESTAMP NOT NULL
);

Idea: Bind a TRIGGER and/or RETURNING nice_id to SELECT * FROM event_log WHERE module=1 queries

OS: Debian Linux 8.7
SQL: PostgreSQL 9.4 and/or R sqldf


texts:
In postgresql 9.5.1, I created a user called GuardDog_02. I gave it a password. And I gave it permissions to create db and user:

CREATE USER GuardDog_02 WITH PASSWORD 'apple100' CREATEDB CREATEUSER;

Then under the GuardDog_02 system user (Mac OSX):

$ psql -d template1
Password:
psql: FATAL:  password authentication failed for user "GuardDog_02"

I updated password just to make sure I typed it right:

template1=# ALTER USER GuardDog_02 WITH PASSWORD 'apple100';

Still it says password authentication fails. I checked the pg_hba.conf file too:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                md5
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5

What am I doing wrong?


texts: In Redshift how can I create an user which which has only user creation privileges?

texts: The primary requirement is to automatically create or alter users based on user information stored in a table. I am planning to write a stored procedure / use Databricks notebook to do the automation. The Redshift user which I use for creating other users in this automation should only user create/alter permissions. It is the only which should be performed from that user. I read that the option CREATEUSER option apparently gives super user like privileges to the user. How can i restrict/revoke other superuser privileges?


texts: Error publishing SQLCLR C# Function using Visual Studio 2017

texts:
I'm trying to (learn) publish a SQLCLR function using Visual Studio 2017. (It's a simple function that sends an email.)

As a reference I used this articles on CodeProject and MSSQLTips:

Create, Run, Debug and Deploy SQL CLR Function with Visual Studio 2013 Database Project

Send Email from SQL Server Express Using a CLR Stored Procedure

On project properties->SQLCLR I've set:

Permission level : UNSAFE

And according to the article I've updated target databases:

sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

And after I've got the error I've followed this advice on MSSQLTips article:

  If you get error messages when trying to compile the code you may need to alter the database using the following command and then try again to create the assembly and the stored procedure.

ALTER DATABASE msdb SET trustworthy ON

I've tried using two different target databases:

SQL-Server 2017 LocalDB
SQL-Server 2017 Express

I can build the project without errors, but when I publish the project I receive one error when it executes the next command:

CREATE ASSEMBLY [dbSysmac]
    AUTHORIZATION [dbo]
    FROM 0x5F8A900003000000...
    WITH PERMISSION_SET = UNSAFE;

  (47,1): SQL72014: .Net SqlClient Data Provider: Msg 10327, Level 14, State 1, Line 1 CREATE ASSEMBLY for assembly 'dbSysmac' failed because assembly 'dbSysmac' is not trusted. The assembly is trusted when either of the following is true: the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission, or the assembly is trusted using sp_add_trusted_assembly.

What I'm doing wrong?


texts: PostgreSQL Partitioning - Constraint exclusion not working

texts:
I have a table with about 9 million rows where i'm keeping customer's id, a timestamp, and a cost value for the particular date-time.

It looks like this:

customer|t|consumption

I need to split my dataset into smaller tables which will inherit from the table master.

Master table:

CREATE TABLE my_table_master (
 customer TEXT NOT NULL,
 t TIMESTAMP NOT NULL,
 consumption INTEGER NOT NULL
);

Child tables:

CREATE TABLE my_table_2 (
CHECK ( t='2013-07-01 01:00:00')
) INHERITS (my_table_master);

CREATE TABLE my_table_3 (
CHECK ( my_extract(t)=0 )
) INHERITS (my_table_master);

CREATE TABLE my_table_4 (
CHECK ( t <> '2013-07-01 01:00:00' AND my_extract(t) <> 0 )
) INHERITS (my_table_master);

The problem

Constraint exclusion is not working here. Note that i have already execute the command:

SET constraint_exclusion = on;

If for example i run the following query, the planner includes my_table_2 and my_table_3 instead of just my_table_2.

SELECT * FROM my_table_master WHERE t='2013-07-01 01:00:00';

Query Plan:

Append  (cost=0.00..24601.55 rows=967 width=24) (actual time=164.514..164.693 rows=993 loops=1)
->  Seq Scan on public.my_table_master  (cost=0.00..0.00 rows=1 width=44) (actual time=0.010..0.010 rows=0 loops=1)
 Output: my_table_master.customer, my_table_master.t, my_table_master.consumption
 Filter: (my_table_master.t = '2013-07-01 01:00:00'::timestamp without time zone)
 ->  Seq Scan on public.my_table_3  (cost=0.00..24577.43 rows=960 width=24) (actual time=164.472..164.472 rows=0 loops=1)
 Output: my_table_3.customer, my_table_3.t, my_table_3.consumption
 Filter: (my_table_3.t = '2013-07-01 01:00:00'::timestamp without time zone)
 Rows Removed by Filter: 1237954
 ->  Seq Scan on public.my_table_2  (cost=0.00..24.13 rows=6 width=44) (actual time=0.029..0.165 rows=993 loops=1)
 Output: my_table_2.customer, my_table_2.t, my_table_2.consumption
 Filter: (my_table_2.t = '2013-07-01 01:00:00'::timestamp without time zone)
 Planning time: 0.298 ms
 Execution time: 164.731 ms

EDIT:

If i'm only asking two specific queries on the DB that include the following WHERE clauses respectively:

WHERE t='2013-07-01 01:00:00'
WHERE my_extract(t)=0

do you think i should possibly split my dataset in a different way?
If yes, please let me know.


texts: SELECT * FROM small_table executes quickly, returns very slowly in PostgreSQL 11

texts:
I have a small table (~2500 rows, 37 col) being served by Postgrest API. After months of <1sec response times, it's now up to over 20seconds despite the table not being much larger.
EXPLAIN ANALYSE VERBOSE
SELECT * FROM companies

Seq Scan on companies  (cost=0.00..436.93 rows=2393 width=1530) (actual time=0.004..0.860 rows=2393 loops=1)
  Output: <37 column names>
Planning Time: 0.065 ms
Execution Time: 1.311 ms

Also
CREATE TABLE companies_test as SELECT * FROM companies

OK, Time: 0.076000s

For what it's worth, I have also run VACUUM FULL ANALYZE on the table and it completes in <0.5 seconds
The slow response is seen by the API response time as well as in my Navicat client. According to those two queries above, the query executes as quickly as we'd expect it to, but then delivers the data out of the DB very slowly? I've never encountered this before and am curious if anyone has any ideas of where I could look to resolve this.


texts: SQL Server Nested views, how deep is too much?

texts:
I stumbled on a problem using views. The content management system my program interfaces with, defines a view which calls another view on the system. That is 2 levels to begin with. Before I stumble on views, I thought nested views were casual, but after my code blew up and i googled around, i begin to pick up words like "sin" and "vulgar" and "bad" which are associated with nested views.
Well I have two-level nested views to begin with. In my application level, I define my 3rd level and that's when it gives me time out error. Is 3 level too much? The tables are not terribly big -- between 5,000 and 100,000 records, while my view itself only produces 2,000 record. Is this suppose to break the camel's back?


texts: SUPER privilege not defined for master user in Amazon MySQL RDS

texts:
I have created one medium instance on amazon rds in asia pecific (singapore) region. i have created my master user with master password. and it is working/connecting fine with workbench installed on my local PC. When, I am going to create function on that instance, it show me following error

  ERROR 1418: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)

At my instance, my variable (log_bin_trust_function_creators) shows OFF. now when I go to change with variable using

SET GLOBAL log_bin_trust_function_creators = 1;

it gives me another error

  Error Code: 1227. Access denied; you need (at least one of) the SUPER privilege(s) for this operation

I don't know how to solve this error.

Can anybody help???


texts: The value for the parameter @MirrorDirectory is not supported error with Ola Hallengren Backup script

texts:
I am using Ola Hallengren's Maintenance Solution, and it's been working great for our environment.

Quick question regarding the User Database backups s proc. Currently, when I'm taking backups, the backup files are getting saved to one of the servers local drive (Z: drive). But if I wanted to also get another copy of those same backup sets to a different drive or folder, I was thinking the @MirrorDirectory is where we can enter a different drive path for the backup files. (Please correct me if I'm wrong).

I wanted to test this, so I have duplicated the original the User Backup Database sqlserver job for my main backup job and renamed this new sqlserver job a "DatabaseBackup - USER_DATABASES - FULL_COPYONLY". Here's what I have on the first and only job step:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d LSDBA_DB -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'Z:\COPYONLY_BACKUPS',@MirrorDirectory =N'C:\MIRRORBAKS', @CopyOnly = 'Y', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 192, @MirrorCleanupTime= 24, @CheckSum = 'Y', @LogToTable = 'Y'" -b

I'm currently testing this in the hope that it will place the main backup files on "Z:\COPYONLY_BACKUPS" folder, and place another backup set on "C:\MIRRORBAKS" foder. (I'm place them in C drive just for testing).

When I run the job, it is failing and the message is:
 Msg 50000, Level 16, State 1, Server (Instance Name here), Procedure DatabaseBackup, Line 436  The value for the parameter @MirrorDirectory is not supported.     Msg 50000, Level 16, State 1, Server (Instance Name here), Procedure DatabaseBackup, Line 740  The documentation is available at https://ola.hallengren.com/sql-server-backup.html.     Date and time: 2018-11-19 11:05:58.  Process Exit Code 1.  The step failed.

Could anyone shed some light on why it is saying the parameter for @MirrorDirectory is not supported? Thank you very much.


texts: SSIS Package Validation Error when Executed From Windows Task Scheduler

texts:
I have deployed my SSIS package using the project deployment model to the SSIS catalog and experience a strange validation error when I try to execute the package. I believe this is due to a privileges issue or I may have configured our data import process wrong.

Let me give you an overview on the process:

Windows Task Scheduler is being used
Scheduled Task executes a long-running file download process (using Powershell)
Scheduled Task triggers execution of the SSIS package (using Powershell and SMO)
Scheduled Task is configured to run as a domain account (see privileges below)

Note: all these steps, including account creation, are automated using guidance from here.

Scheduled Task Account Privileges:

Windows: Users Group
SQL Catalog: Read on Catalog Folder, Read on Environment, Read and Execute on Project
Destination Data Database: db_datareader, db_datawriter
Source Data Database: db_datareader, db_datawriter

Steps To Reproduce The Error:

Right-click scheduled task, choose 'Run'
Package execution is triggered successfully
Open SSMS, view Execution logs. Package execution immediately failed with a validation error "Cannot create task from XML for task "[taskname]", type "SSIS.ReplacementTask" due to error 0x80070057 "The parameter is incorrect."

While diagnosing this issue, I wanted to rule out privileges and ensure the account could see the SSIS project in the catalog, so I executed SQL Server Management Studio as my Scheduled Task account. The Package validated and ran to completion successfully.

Here's the bit that is very confusing.! After performing the above step (login to SSMS as Task Scheduler account),  I could successfully trigger, validate and execute the scheduled task from the Task Scheduler!

I am now able to reliably replicate these steps:

Execution Fails
Login to SSMS As Task Scheduler Account
Execution Succeeds

Theory: the 'first time configuration' that occurs when launching SSMS as the Task Scheduler account is configuring user specific registry keys. I have confirmed that when I do not log in as my custom account, when the package tried to execute it complains that user specific registry keys are not found via Process Monitor.

My Questions:

Should we be using the Task Scheduler instead of SQL Server Agent? I orignally chose Task Scheduler because the powershell download task was long running and needs access to network shares
Can I trigger a package to execute from a Scheduled Task, but have it execute as a different account?

Thanks for any help you can offer - I'm quite confused!


texts:
I have a view that has serious performance issues, because I am linking it to a CTE that uses another view, with the same table references.
WITH
  splits(ID, [NEW TEU])
  AS
    (SELECT s.[DTL ID] AS ID, s.[NEW TEU]
     FROM   [POTS].[vwSPLITCARGO] AS s)

SELECT *
FROM   [MISNT].[CARGO_MANIFEST_DETAIL_VIEW] AS cmd
       LEFT JOIN splits AS s ON s.ID = cmd.ID

The vwSPLITCARGO view, references the same CARGO_MANIFEST_DETAIL_VIEW table to get it's calculations.
I know why it's slow. It's because the vwSPLITCARGO view is reading all the data from the same CARGO_MANIFEST_DETAIL_VIEW view for every record already in the CTE. In fact, the vwSPLITCARGO is always returning the same small set of records (approx 4 or 6 records). I don't need it to get those records all the time. Once would be great!
Is there a way to make the CTE behave like a temporary table, in that it retrieves the records once, and not be processed over and over?
Something to tell the CTE: CASE WHEN RunOnce = 0 THEN "do it" ELSE "don't do it" END


texts:
To set the scene, I maintain 4 local SQL Server 2008 R2 instances.
At some point in the near future, 3 of these will be upgraded to SQL Server 2017 and migrated to azure.

I am currently using SSMS and SSIS 10.5 to manage these servers and develop ETLs.

Can SSMS 10.5 and 17.7 be run side-by-side, and can I do this before we've even upgraded any of the instances to 2017? Is there any benefit of doing so?
I'm considering forking out for ApexSQL but some of the tools aren't compatible with 10.5.

Can SSDT also be installed side-by-side with SSIS 10.5?

Thanks.


texts: usage percentage of Mongodb internal cache

texts:
I've ran two performance test on a MongoDB server with identical environment/settings AFAIK.
I found that the throughput was 10% apart.

When I inspected the mongostat logs, I found that Mongodb in the first test with faster throughput used 95% of its cache_size. In the second test, it stayed at 80%.

I rebooted the server system between the runs, but didn't change any of the settings of the server or the client. mongodb log reports the exact same setting in both tests:

First test:
2019-08-14T00:42:32.588-0400 I STORAGE  [initandlisten] wiredtiger_open config: create,cache_size=96122M,cache_overflow=(file_max=0M),session_max=20000,eviction=(threads_min=4,threads_max=4),config_base=false,statistics=(fast),log=(enabled=true,archive=true,path=journal,compressor=snappy),file_manager=(close_idle_time=100000),statistics_log=(wait=0),verbose=(recovery_progress),

Second test (after reboot):
2019-08-14T01:11:15.722-0400 I STORAGE  [initandlisten] wiredtiger_open config: create,cache_size=96122M,cache_overflow=(file_max=0M),session_max=20000,eviction=(threads_min=4,threads_max=4),config_base=false,statistics=(fast),log=(enabled=true,archive=true,path=journal,compressor=snappy),file_manager=(close_idle_time=100000),statistics_log=(wait=0),verbose=(recovery_progress),

Here is a sample from mongostat of the first test:

insert  query update delete getmore command dirty  used flushes vsize   res   qrw   arw net_in net_out conn                time
    *0 104209     *0     *0       0     9|0  0.0% 94.9%       0 96.7G 94.7G 107|0 128|0  27.0m   40.2m  272 Aug 14 00:51:13.759
    *0 102610     *0     *0       0     0|0  0.0% 94.9%       1 96.7G 94.6G 109|0 128|0  26.6m   39.6m  272 Aug 14 00:51:44.092
    *0 104724     *0     *0       0     9|0  0.0% 94.9%       0 96.7G 94.6G  87|0 128|0  27.1m   40.4m  272 Aug 14 00:52:13.767

And a sample from the second test:

insert  query update delete getmore command dirty  used flushes vsize   res   qrw   arw net_in net_out conn                time
    *0  94009     *0     *0       0     9|0  0.0% 80.9%       1 83.1G 80.9G 127|0  81|0  24.4m   36.3m  272 Aug 14 01:17:29.289
    *0  93680     *0     *0       0     0|0  0.0% 80.3%       0 83.1G 81.0G   0|0 107|0  24.3m   36.2m  272 Aug 14 01:17:59.119
    *0  93068     *0     *0       0     9|0  0.0% 80.9%       1 83.1G 81.0G 130|0 128|0  24.1m   35.9m  272 Aug 14 01:18:29.123
    *0  92599     *0     *0       0     0|0  0.0% 80.0%       0 83.1G 80.9G 137|0 128|0  24.0m   35.8m  272 Aug 14 01:18:59.186

What could have caused the difference in the cache percentage usage and throughput?
is it safe to change explicitly this settings so Mongodb uses more of its cache?
Can I do this setting change from the command line ? (I'm starting mongodb from command line and not as a service)


texts: Why does InnoDB allow a 772 byte unique constraint?

texts:
I've defined a unique constraint for two columns: A bigint and a VARCHAR(256) COLLATE utf8mb4_unicode_ci

Then this error occured (which I'm already familiar with):

Specified key was too long; max key length is 767 bytes

I then tried to set the length of the VARCHAR column as high as possible without getting an error.

What suprised me was that MySQL allowed me to change the length to 191 but that would mean that the index is 8+191*4=772 bytes long but the error told that only 767 bytes are allowed.

This is the table:

CREATE TABLE `file_content`
(
  `file_content_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `local_filename` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `directory_id` bigint(20) NOT NULL,
  ...
  PRIMARY KEY (`file_content_id`),
  UNIQUE KEY `fc_dir_name` (`directory_id`,`local_filename`),
  ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I'm using MySQL 5.5.27


texts:
I'm in debate with my database admin. He claims that my queries I'm sending through PHP are the problem and he is stating several things I believe are completely false.

To begin I have a site receiving 64,000 daily views. My server monitors queries over 5 seconds. Multiple times during the day I see 3-6 "SELECT" queries all taking more than 10 seconds, sometimes even up to 200 seconds. This is on an InnoDB table on MySQL 5.0. Also PHP is calculating the speed of the query using microtime. Ironically enough I try the same queries in PHPMyAdmin and they take 0.0031 seconds.

My Admin says I shouldn't use an asterisk in my code, but nowhere online goes against that. He says I should convert all my tables from MyISAM to InnoDB. I have already done that but still the same things happen. He doesn't seem to be budging and even worse once in a while my server is down for 1-2 minutes with a MySQL error of "Too Many Connections" or "MySQL Server has gone away"

The Admin isn't listening to reason and these problems continue. I'm on a version of mysql with profiling disabled so I can't really see query times of just mysql. Can anyone suggest anything I can do or try to diagnose this? I'm only the coder but I have experience in mysql (probably more than the admin).


texts: Syntax for restoring SQL Server backup from non-disk source

texts:
I have backed up and restored many a SQL Server database using T-SQL script, but always FROM DISK or TO DISK. I am not a provisioning-concerned database administrator, though, so don't have as much experience with the full range of available backup types.

Now I'm working with a database new to me where the physical_device_name from table msdb.dbo.backupmediafamily for the full backups is a GUID followed by a number, e.g. {F1554525-23BC-48A2-89E0-4069C2D3ACF3}1. How do I use this? Is it tape? Or a physical device name? I have searched online for references to using a GUID in restores or to restoring FROM TAPE and can't seem to find any examples.

Unfortunately at this moment I don't have on-demand access to the server where the backup is and the server doesn't have enough disk space for an extra copy of the database, anyway. But, when I DO get in this situation again, I'd like to know the correct syntax.

RESTORE DATABASE DBName FROM {F1554525-23BC-48A2-89E0-4069C2D3ACF3}1; doesn't look like good syntax to me.

RESTORE DATABASE DBName FROM TAPE = '{F1554525-23BC-48A2-89E0-4069C2D3ACF3}1'; looks more likely. Is it a tape device?

Or is there some additional set of values I need from the backupmediafamily table that will help me know how to write the restore statement (and a RESTORE FILELISTONLY statement, too)?

Note: the log backups look like normal filesystem files. So no issue there.

Additional Note: the problem with the suggested duplicate is that the answers there are less than confident. It could be DPM/VSS or tape. So... some definitive answer, here or there, would be appreciated.


texts: How to access a database hosted in a normal pc from the cloud?

texts:
[ Customer PC - MYSQL DB ]  <--cloud---  [ Server - Web App ]
[ Customer PC - MYSQL DB (master) ]  <--cloud---  [ Server - MYSQL DB (slave) | Web App ]

I have a mysql database that resides in a normal pc (windows 7), it's a database from an old application and I can't move it.

I need to communicate a web app (that resides in the cloud) with the database inside the "normal windows 7"that resides in a local lan with a private IP.

I was trying to do a MYSQL replication between the local database and a slave that resides in the cloud, I do my test using virtual machine and it was working.

But when I try to replicate it, I realize that I can't do NAT between a normal windows and the public IP of my client and therefore I can't get the connection between the local MYSQL (master) and the Slave (Cloud) to get the replication.

Maybe there is something that I'm missing?


texts: Could not fail over Always on AG to DR ones Prod was down - Help!

texts:
Set-up: Prod-HA-DR - SQL 2012 SP2
Prod-HA Synch, DR Asynch

Databases are distributed on Prod and HA equally like Active-Active Instances.

PROD-HA in Datacenter A
DR - In Datacenter B

Scenario:
This Saturday we broke the VPN between Prod-HA and DR datacenters to create the REAL DR

Scenario.

Action performed ones there was no connectivity between Prod and DR:

Logged-in to DR SQL Instance
Stopped the Clustered Service
Start the Cluster Service with Forced Quorum
Set the DR Node Weight to 1

These 4 steps completed successfully and DR node weight was showing 1 and PROD-HA node weight also 1 in clus admin on DR.

But AG Force fail over with Data loss command

 ALTER AVAILABILITY GROUP [' +  @availabilityGroup + ']   FORCE_FAILOVER_ALLOW_DATA_LOSS;

gave below error:

ERROR Message:

  The availability replica for availability group 'AG_XXXX' on this
  instance of SQL Server cannot become the primary replica. One or more
  databases are not synchronized or have not joined the availability
  group, or the WSFC cluster was started in Force Quorum mode. If the
  cluster was started in Force Quorum mode or the availability replica
  uses the asynchronous-commit mode, consider performing a forced manual
  failover (with possible data loss). Otherwise, once all local
  secondary databases are joined and synchronized, you can perform a
  planned manual failover to this secondary replica (without data loss).
  For more information, see SQL Server Books Online.

DR Database showed status Not Synchrinizing/Recovery Pending.
Another aspect was that many SQL instances with AGs just worked fine with exactly same steps VS many instances gave this error and we could not failover to DR.

It important to have a successful DR test with AGs for us.Appreciate a quick response.


texts:
From MySQL 5.1, the data doesn't need to be entirely in memory anymore.

I've read that the indexed columns (I think the entire index structure) must still be in memory (MySQL High Availability, 2010, pg. 533, "MySQL Cluster keeps all indexed columns in main memory").

In light of this, what happens if there's NOT enough memory (ie, a huge database (> 100GB, or 1TB), running on servers with low memory configs (2 data nodes, each with 1GB of RAM, for instance))?


texts: eliminate extra spaces in VARCHAR columns

texts:
In a table, I have an ID column defined as BIGINT(8) and one VOUCHER column that is VARCHAR(100). No matter the actual length of the value in that field, the SQL always returns a full length value, which can be cumbersome in CLI, specially when selecting more fields.

Here is a sample of what I'm getting

ID                   VOUCHER
-------------------- ----------------------------------------------------------------------------------------------------
               26462 7083d8374831148f9e2271741529ee37
               26624 9170220e6971c2414613d0a69b420156
               26625 b75d12847a24981d36f7bfcd58e8a230

I tried TRIM / LTRIM / RTRIM functions, but without success.

What can I use to get something along these lines, at least for display purposes:

ID     VOUCHER
------ --------------------------------
26462  7083d8374831148f9e2271741529ee37
26464  9170220e6971c2414613d0a69b420156
26465  b75d12847a24981d36f7bfcd58e8a230


texts: I see that I can view the latest deadlock in mysql/innodb, but is there a way to view past deadlocks? We have two deadlock issues, one being important and the other not. The less important deadlock happens several times a day so it becomes the "latest" deadlock.


texts: Service Broker: Queue Monitor is dropped after poison message

texts:
I have searched everywhere and there isn't an answer online. There is one similar question on stackoverflow but it's not exactly the same and it has no accepted answer.

When I setup event notification on a queue, it creates a queue monitor and if receiving a message from that queue causes a poison message the queue monitor disappears. It's not in "RECEIVE" or "INACTIVE" it's just drops. And after enabling the queue with ALTER QUEUE somequeue WITH STATUS = ON; the queue monitor does not re-appear and and I need to drop and recreate event notification in order to have event notification working again.

So the question is, is that by design or not? And after I re-enable the queue after poison message is it a common practice to recreate event notifications.

Thanks!


texts: When changing the size of a nvarchar column, do I need to drop the unique index? And will the table get locked when recreating the index?

texts:
In our database a big table exists that more or less looks like this:

CREATE TABLE dbo.production_data
(
    pd_id BIGINT PRIMARY KEY,
    serial NVARCHAR(16) NOT NULL UNIQUE,
    ...
);

but now the size of the serial field has become to low, so I want to change it to 32.
The Visual Studio schema compare tool suggests doing this by:

DROP INDEX ux_production_data_serial ON dbo.production_data;
GO
ALTER TABLE dbo.production_data ALTER COLUMN serial NVARCHAR(32) NOT NULL;
GO
CREATE INDEX ux_production_data_serial ON dbo.production_data(serial ASC);

Is this really needed? Or more like a ultra save way of doing this?

Also when recreating the unique index, will my table get locked? Because this would be a big problem (as the table has 30 million rows and i guess recreating the index will take quite some time), because the next maintenance window is a few month in the future.
What are my alternatives?


texts:
Is it possible to create a foreign key that relates to information_schema?

For an application implementation, I would like to store extra column information in another table, but I want to ensure that it is not describing columns that do not exist.

Is it possible to have a foreign key that relates to information_schema?  If so, how?


texts: Best way to speed up requests using date columns

texts:
Our database is receiving the following requests:

select * from [schema].[table] (nolock) where (Date_A > Date_B and Archive = 0)

This is a normal request that is made by our application. On average, the request is taking between 200ms - 400ms to complete, with about 100ms - 150ms in CPU. Reads normally come in at about 48k. This is at least one example of how this script performs.

What would be the best way (if there is a way) to speed up this type of request?

The table has 240,932 rows and the queries return 0 records.


texts: Restore log backups to non-readable async AlwaysOn replica

texts:
We have 3 SQL 2014 instances in a AlwaysOn Availability Group. Our 2 primary servers are onsite at our primary data center in synchronous replication. A 3rd replica is at our DR site in Async Mode (no automatic failover)

We run transaction log backups every minute on our primary instance (don't offload backups). Our DR site usually runs < 5 seconds behind the primary site, and the business is ok to lose up to 1 minute of data in the event of an unplanned outage at our primary site (planned outages we switch to sync mode and deal with the latency issues).

Sometimes though, our DR site can be out of sync by 20-30 minutes, particularly during index maintenance and end of month operations.

My question is, in the event that both servers in the primary site go offline and our DR site is behind, can we use our log backups to bring the DR server up to date? What are the steps involved?

Related question: asks the same question but for SQL Mirroring instead of AAGs.

Due to licensing the DR site is non-readable (I don't think this will make a difference but just in case it does).


texts: How should I handle constraining options in a database?

texts:
For sake of simplicity,
I have a Gender table with only 2 values:
Male, Female

I have another table called Activity with a field called restrict_to_gender. The values of this field can be any one of the following
Male, Female, DoNotLimit

I know I can simply create another table called ActivityGenderLimit to host these values, but it seems like I am having redundant values, since the Gender table already has the genders.
How do database people handle situations like this?
[Note: i am willing to change my question title, but I didnt know a better one to describe it]


texts: migrating from a local PostgreSQL database to Amazon Aurora

texts: I have a local PostgreSQL database and I want to migrate it to Amazon Aurora, how do I do that? I found a documentation on how to migrate from RDS PostgreSQL database, however, mine is local and I couldn't find a documentation on how to migrate it.


texts: What is a 'Target Server' in a SQL Server Agent Job?

texts: Can someone explain what a 'Target Server' is in this instance.  Also, what it means to "Target Multiple Servers"?


texts: Master slave replication - DATETIME columns not replicating the time

texts:
I have master which is mariadb 5.5 and slave mariadb 10.3, the replication works fine except for the DATETIME columns which are syncing the date part but not the time.

On master: LastLogin 2020-01-15 15:22:11

On slave: LastLogin 2020-01-15 00:00:00

Running show create table on both servers returns identical results

CREATE TABLE User (
    ID int(10) unsigned NOT NULL AUTO_INCREMENT,
    LastLogin datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    PRIMARY KEY (ID)
) ENGINE=MyISAM AUTO_INCREMENT=71041 DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci

SHOW SLAVE STATUS\G results on slave

*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 185.244.166.75
                   Master_User: replication_user
                   Master_Port: 3306
                 Connect_Retry: 10
               Master_Log_File: master1-bin.000008
           Read_Master_Log_Pos: 4275814
                Relay_Log_File: mysqld-relay-bin.000013
                 Relay_Log_Pos: 4276111
         Relay_Master_Log_File: master1-bin.000008
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
               Replicate_Do_DB: live_wp,live_ms
           Replicate_Ignore_DB:
            Replicate_Do_Table:
        Replicate_Ignore_Table:
       Replicate_Wild_Do_Table:
   Replicate_Wild_Ignore_Table:
                    Last_Errno: 0
                    Last_Error:
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 4275814
               Relay_Log_Space: 4276462
               Until_Condition: None
                Until_Log_File:
                 Until_Log_Pos: 0
            Master_SSL_Allowed: No
            Master_SSL_CA_File:
            Master_SSL_CA_Path:
               Master_SSL_Cert:
             Master_SSL_Cipher:
                Master_SSL_Key:
         Seconds_Behind_Master: 0
 Master_SSL_Verify_Server_Cert: No
                 Last_IO_Errno: 0
                 Last_IO_Error:
                Last_SQL_Errno: 0
                Last_SQL_Error:
   Replicate_Ignore_Server_Ids:
              Master_Server_Id: 1
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: No
                   Gtid_IO_Pos:
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0

What could be the issue and how this could be debugged/fixed?


texts: How can I improve this slow query in my wordpress site?


texts: Does it make sense to consolidate Oracle databases?

texts:
I am more experienced with SQL Server and Sybase than Oracle, and understand those products well. I've been asked to look for ways to reduce the server estate running Oracle. I understand that an instance in Oracle maps to a database hosting many tablespaces. I have a fairly good grasp of the fundamentals, however if I wanted to consolidate SERVER1,..,SERVER4 running Oracle database into one server what would be the best way to do it physically? I am considering Virtual as well using a DBaaS (Database as a Service) model, but am curious if it can/should be done physically.

Is it possible to have four separate instances point to four separate databases on one machine? Or would I have to merge the four databases into one database on the consolidated server and manage the schemas to ensure there are no name conflicts? If I did that would I have one instance or four?

I have read the documentation but I'm still not 100% sure about this area.


texts: Designing An ACL Based Permission System

texts:
I am trying to create a permissions system where everything is going to be stored in MySQL (or some database) and pulled using PHP for a project management system I am building.  I am right now trying to do it is an ACL kind of way.  There are a number key features I want to be able to support:

1.  Being able to assign permissions without being tied to a specific object.

The reason for this is that I want to be able to selectively show/hide elements of the UI based on permissions at a point where I am not directly looking at a domain object instance.  For instance, a button to create a new project should only be shown to users that have the pm.project.create permission but obviously you can assign a create permission to an domain object instance (as it is already created).

2.  Not have to assign permissions for every single object.

Obviously creating permissions entries for every single object (projects, tickets, comments, etc…) would become a nightmare to maintain so I want to have some level of permission inheritance.

3.  Be able to filter queries based on permissions.

This would be a really nice to have but I am not sure if it is possible.  Here's an example: I have a page that lists all projects.  I want the query that pulls all projects to incorporate the ACL so that it would not show projects that the current user does not have pm.project.read access to.  This would have to be incorporated into the main query as if it is a process that is done after that main query (which I know I could do) certain features like pagination become much more difficult.

Right now this is my basic design for the tables:

AclEntities

id - the primary key
key - the unique identifier for the domain object (usually the primary key of that object)
parentId - the parent of the domain object (like the project object if this was a ticket object)
aclDomainObjectId - metadata about the domain object

AclDomainObjects

id - primary key
title - simple string to unique identify the domain object(ie. project, ticket, comment, etc…)
fullyQualifiedClassName - the fully qualified class name for use in code (I am using namespaces)

There would also be tables mapping AclEntities to Users and UserGroups.
I also have this interface that all acl entity based object have to implement:

IAclEntity

getAclKey() - to the the unique key for this specific instance of the acl domain object (generally return the primary key or a concatenated string of a composite primary key)
getAclTitle() - to get the unique title for the domain object (generally just returning a static string)
getAclDisplayString() - get the string that represents this entity (generally one or more field on the object)
getAclParentEntity() - get the parent acl entity object (or null if no parent)
getAclEntity() - get the acl enitty object for this instance of the domain object (or null if one has not been created yet)
hasPermission($permissionString, $user = null) - whether or not the user has the permission for this instance of the domain object
static getFromAclEntityId($aclEntityId) - get a specific instance of the domain object from an acl entity id.

Do any of these features I am looking for seems hard to support or are just way off base?
Am I missing or not taking in account anything in my implementation?
Is performance something I should keep in mind?


texts: Can not install a database, can I still learn though?

texts:
I am given access to a domain where I can put my code in and it will go public, like in this example. In order to store the data right now, I am using PHP and I write the data to a file. When the page is loaded, I will read from file and display it in the page.

However, for teaching purposes, I would like to use a database, but I have no privileges, which would allow me to install a database. So, can I do something, that would still allow me to learn how to use a database && apply it to my page?

A negative answer would also be accepted.

I thought of SQLite in-memory, but it says that the database will delete itself, once the connection to the database is closed. What does that mean exactly? It means that it will delete itself when the user exits the page from his browser? This question gives me this feeling.


texts: Why can i not call COUNT on a varray column in a select statement using oracle

texts:
I have a simple array type definition like this

  CREATE OR REPLACE TYPE "LIST_OF_NAMES_T" IS  VARRAY(10) OF VARCHAR2 (100);

Now I have a column items of type LIST_OF_NAMES_T in my table TABLE1

Why am I getting an error when selecting the count of the array in a select statement like this:

SELECT items.COUNT FROM TABLE1;

Oracle tells me

ORA-00904: "items"."COUNT ": invalid identifier


texts: Keep Mongo DB set up up and running with two data center always

texts:
There is a scenario where we have 5 member replica set across two data centers.
Data center 1 : 3 Members
Data center 2 : 2 Members
Situation is if DC 1 goes down DC 2 becomes read only but  we need to keep it ready to accept writes also.
Is there any possibility for same using one extra member like hidden node or two arbiter.
We do not have third data center.

Thanks
Virendra Agarwal


texts:
When a Proxy Authentication is used, there is the impersonated user (called the client) and the impersonating user (called the proxy).  So if Bob wants all the privileges of App1 and has been granted the privilege to connect as App1, then the client is App1 and the proxy is Bob.  I would like a query of sessions that provides both the client and the proxy.

v$session shows schemaname as the client user.  It has osuser and machine columns from which the proxy may be inferred, but it would be fragile at best.

v$session_connect_info changes the column authentication_type to proxy when proxy authentication is in use, but that only indicates that proxying is in effect, not who the proxy is.

The proxy_users table gives all the potential proxy/client combinations, which would work if there were only one match for the client, but wouldn't otherwise.

As the proxy user a selection could be done of SYS_CONTEXT with a parameter of PROXY_USER to get it, but that does not allow a session to get the proxy for other sessions.

Update:

MOS Doc ID 782078.1 shows how this can be done by joining dba_audit_trail with itself if auditing is turned on specifically and the user performs an audited action.  It seems like a simpler solution should exist.


texts: Correlated Subqueries: resolving ambiguous names

texts:
Suppose I have 2 tables which share some column names. In this case the primary key is both is called id.

CREATE TABLE artists(
    id int primary key,
    name text,
--  …,

);
CREATE TABLE paintings(
    id int primary key,
    artistid references artists(id),
    title text,
--  …,
);

Note: I know there are arguments against calling your primary key a generic name like id, but let’s suppose, for argument’s sake that it’s out of my control. In any case, it could have been any other column for the purpose of this question.

Suppose I now have a carelessly written SELECT statement which seeks to extract data from the referenced table using a correlated subquery:

SELECT
    id, title,
    (SELECT name FROM artists WHERE artistid=id) as artist
FROM paintings;

Clearly the inner WHERE clause could have been better written as WHERE paintings.artistid=artists.id. However I have got away with it and it works. It even works if I write the WHERE clause as id=artistid.

I know it’s not the best way to go about it, but I’m more surprised that it has understood my intention, which is not what we have come to expect from SQL.

The question is: How does SQL interpret ambiguous columns in a Correlated Subquery?


texts: Where to start learning to understand SQL Server architecture and internals?

texts: I have a basic knowledge of T-SQL and SQL Server components. My goal is to master my skills and learn everything about SQL Server to eventually become DBA in the future. I would like to understand deep SQL Server internals, how exactly everything works, when and why. Could you please suggest me a good place to start? IMHO it's just not possible by doing the programming work.


texts: Does increasing the tempdb file size require a restart

texts:
I want to alter tempdb as such (i am increasing the size)

USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp2', SIZE = 10240KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp3', SIZE = 10240KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp4', SIZE = 10240KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 10240KB )
GO

Will this require me to restart SQL server?


texts: SQL Azure Indexes need Rebuilding for Foreign Keys to work

texts:
We have a product that runs off SQL databases.

Until recently, each of our clients had their own fileserver running SQL Server, however we have now started hosting on Azure for some existing and some new clients.

Attempting to roll out the latest version of the software to our clients, we've run into an issue with some (but not all!) of the Azure-hosted databases.

The update required adding some new tables, which had Foreign Keys to some existing tables. This worked fine on all the SQL Server hosted databases, but on some of the Azure hosted databases we got the following error:-

There are no primary or candidate keys in the referenced table 'TableName' that match the referencing column list in the foreign key 'FK_Name'

But there were definitely valid PKs, all the databases had the same schema, and only some had issues. Those that had issues also reported the issue against different tables.

The solution turned out to be that the indexes needed rebuilding on some of the tables:-

ALTER INDEX ALL ON TableName REBUILD;

In some cases, more than one index needed to be rebuild.

Once that was done, the scripts to create the new tables ran without issue.

All of the Azure databases are quite new, and most of them barely have any data in them. Half a dozen records in the tables affected, in some cases, so not a case of indexes getting too big, or too fragmented.

The tables themselves worked fine, before and after the indexes were rebuilt, and the software didn't throw any errors.

The only obvious issue related to this is being unable to reference the PKs with FKs.

However, we've obviously concerned that there is some underlying issue with Azure and indexes, and that we could face worse problems down the line.

Has anyone else experienced this issue?

Is there something we need to be doing on Azure to stop indexes breaking?

I ran overnight jobs on all the Azure databases to rebuild all indexes, so hopefully we're now good, but I'd still like to know what happened, and why, and how to stop it happening again.

TL:DR;

Different indexes, on different tables, on different Azure databases, have broken somehow in the last few weeks (newest DB) to months (oldest DB), and we'd kinda like to know why!

Edit : Answering Questions

1) Could the table have had a disabled index?

It wasn't disabled as far as I could see, and certainly wasn't done deliberately.

Also, was on different tables on different databases, so seems unlikely.

I'll read through the link you provided though, just in case, thanks!

2) Could that happen with automatic index tuning?

Automatic index tuning is turned off, it was one of the things I checked, sorry I should have mentioned that.

I believe that it will also only drop indexes that it created itself, not ones that already existed. I did play around with it on a test database on a different SQL Server, and that did seem to be the case,


texts: Sql server database synchronization on 2 different server

texts:
I need advice on setting up synchronization between 2 SQL Server 2016 databases on standalone servers in different sites. What techniques should I best use and how should I go about setting up synchronization?

Currently I have a SQL Server at one site and a second server at a different site.

The requirement is to have database replication from the first site to the second site only.


texts:
Is there a way to get a notification when a row in a table is created or updated? Much like RethinkDB's change feeds.

Anything would work really - a message on a WS channel, or a HTTP request with the data that was created/updated...

Cannot use trigger to copy to temp table and poll, this is for a real time currency fx feed.

I've also been looking at reading from binlogs but it's too complicated, adds overhead and multiple possible points of failure.

Later edit: also tried lib_mysqludf_sys to sys_exec() a PHP script that connects to the WS server to send the update, it takes between .25 and .50 seconds, which is very very slow considering the single table I am interested in gets updated several thousand times per minute.


texts: how to disable delete in replication Cassandra datastax

texts:
my previous scenario i  used Sql server  one instance in amazon for live and the second
in local network for archiving by disable delete in replication can i do this in Cassandra or what best practices for this scenario?
 im using datastax  4.8.2


texts: Update the current session's default parameter value to reflect a new default

texts:
Say I ALTER the database default search_path to include a new schema:

ALTER DATABASE my_db SET search_path TO "$user",public,other_schema;

Then I also update the current search_path to match:

SET search_path "$user",public,other_schema;

This is great. Now I can use objects from that schema in my current session without including the schema.

...unless someone RESETs the parameter:

RESET search_path;

This doesn't reset it back to the new default. It resets it back to the original default before the session started. Even COMMITing the current transaction doesn't change this.

Trying to UPDATE pg_settings doesn't work:

UPDATE pg_settings SET reset_val = '"$user",public,other_schema' WHERE name = 'search_path';
SELECT * FROM pg_settings WHERE name = 'search_path';

This shows no change to the view and no difference in the behavior of RESET.

I could see use cases for this, but in my use case, I need this to not happen. How can I get PostgreSQL to update the current session default alongside my other changes, so that RESET will set it back to that value? Especially following a COMMIT?


texts:
I have 3 tasks in a SSIS package. If the final task fails I only want that task to rollback.

Do I just set that task to REQUIRED? Or leave it as SUPPORTED?

Thanks!


texts:
In our current prod environment, when the ETL launches, it first checks an environmental variable at the OS level which tells it where the "config" database and table is for it to get the necessary values/parameters for running.

In our test environment though we will have test and QA on the same physical box, but using separate instances of SQL Server.  Feedback I am getting is that since they are on the same physical box, that means one env variable, so we would have to change the env variable depending on whether ETL was running in test or QA.  This is less than desirable.

I understand not wanting to hard code anything into the packages but there has to be a way to accomplish such a thing with multiple environments on the same physical box.  I can't justify splitting out onto separate physical hardware for each env.

What have others done.  Thoughts?  What has worked and what has not?


texts: Logins mapped to dropped database sql server

texts:
I was told to delete the logins that are mapped to a database ABC but unfortunately the database ABC has been dropped and no backups exist.

Is it possible to find the logins that WERE mapped to ABC?


texts: two column compare ,get count(*) of two column A value same cloumn B value

texts:
I have a Mysql table as

---------------------
-- page   -  user  --
---------------------
-- page a - user 1 --
-- page a - user 2 --
-- page a - user 3 --
-- page b - user 1 --
-- page b - user 3 --
-- page c - user 3 --
-- page d - user 2 --
---------------------

I need to get the result as

---------------------------------
-- page_a  -  page_b  -  cnt   --
---------------------------------
-- page a  - page  b  -    2   --
-- page a  - page  c  -    1   --
-- page a  - page  d  -    1   --
-- page b  - page  c  -    1   --
-- page b  - page  d  -    0   --
-- page c  - page  d  -    0   --
---------------------------------

Since this table is really big (200K rows) I tried PHP solution by compare in pair (with 1000pages). Since it will resulted in almost 500K answers, it takes more than one day to finish (about 5 each second).

------------------------------------------------
MYSQL: script
-----------------------------------------------
select count(user) as cnt
FROM (select user
FROM table where page='".$a."') as a
WHERE a.user in (select user FROM table where page='".$b."')
-----------------------------------------------

to get the count then insert into table by php statement.

I like to try mysql approach to see if pure sql can be faster
Any Help ?


texts: Mysql error 28 no space left on the device

texts:
I keep running in to the same problem with mysql with error code 28 which means there is not enough space left on the device.

Now the weird part is that there is enough space, here are some tips i found on google to find out if i have enough space. (please keep in mind that i'm newbie when it comes to linux systems)

du -sh /var/log/mysql

36K     /var/log/mysql

df -h

Filesystem            Size  Used Avail Use% Mounted on
udev                  1.5G  4.0K  1.5G   1% /dev
tmpfs                 604M  224K  604M   1% /run
/dev/disk/by-uuid/7a06472d-4cc9-4383-93b0-4ecee9b21c04
                       24G   15G  8.4G  64% /
none                  5.0M     0  5.0M   0% /run/lock
none                  1.5G     0  1.5G   0% /run/shm
/dev/vda1             461M   36M  402M   9% /boot

I have been trying to solve this for so long now, but nothing helps, any kind of advice would help.

EDIT

The most weird part is that if i delete big file 200-800mb this error goes away for a very short time and then it returns again. I have actually removed around 10GB files in hopes of fixing this but it didnt help.


texts: SELECT UNION from multiple tables with same schema in MySQL

texts:
I want to SELECT (combine) records from multiple tables with the same schema. This is the query that I use:

SELECT visitor_name, in_time, out_time, blacklist FROM visitor_archive_2012 UNION
SELECT visitor_name, in_time, out_time, blacklist FROM visitor_archive_2013 UNION
SELECT visitor_name, in_time, out_time, blacklist FROM visitor_archive_2014 UNION
SELECT visitor_name, in_time, out_time, blacklist FROM visitor_archive_2015

I am quite certain there is a less redundant and verbose way to write this query. The schema for each of the table is exactly the same. This is in MySQL 5.6


texts: Oracle schema import is not importing all the tables present in the schema dump file

texts:
I have exported an existing oracle schema from another machine and then imported it in my local machine. Import was successful, but some tables which are present in the export dump file are not imported.

Here are the export and import commands i have used.

Export Command:
---------------
exp sreeni/sreeni@agentrics1:1524/ezmodc full=n file=SreeniTrunkDump.dmp log=SreeniTrunkDump_Export.log

Import Command:
---------------
imp badri/badri@localhost:1521/xe file=SreeniTrunkDump.dmp log=BadriSchemaImport_Import.log full=y

The Oracle we are using is 10g EE.

What could be going wrong ? Can you please suggest a solution to this issue.


texts: How do you resize tables in an erd diagram in DBeaver?

texts: When I click on a given table in an erd diagram in DBeaver I simply cant resize it.  The table names get cut off. What am I missing ?


texts: Is it possible to create a horizontally partitioned table that is located on two separate sites in Oracle?

texts:
I know that it is not possible to use @dblink syntax to assign tablespaces.

For example, the following would be illegal.

CREATE TABLE hr.admin_emp (
    empno      NUMBER(5) PRIMARY KEY
  )
  partition by range (empno)
  partition p1 values less than (100) tablespace tbspace1,
  partition p5 values less than (MAXVALUE) tablespace tbspace2@dbserver2;

Is there any alternative way to achieve a similar thing with Oracle12?


texts:
Followed the official repmgr document, I set these items to the configuration file:
  shared_preload_libraries = 'repmgr'
  max_wal_senders = 10
  max_replication_slots = 10
  wal_level = 'hot_standby'
  hot_standby = on
  archive_mode = on

However, in the /var/lib/pgsql/10/data/postgresql.conf file, something default as
#wal_level = replica                    # minimal, replica, or logical
                                        # (change requires restart)

Doesn't have hot_standby option.
If I use the config as repmgr document, when I want to create a database or user, alwasy hanging.
createuser -s myuser
createdb -E UNICODE -l en_US.UTF-8 -T template0 mydb -O myuser

But use the default postgresql configuration will work well.
So how to use repmgr with PostgreSQL 10 correctly?


texts: MS SQL Linked Server Security failing using current security context

texts:
I have 2 instances of SQL 2008 64 bit (ver 10.0.4000) running on a windows 2008 64 bit server db03 and db03\ins2. The ins2 has a linked server connecting to db03 that was setup by replication. I have code that needs to connect to db03 from ins2 to kick off a proc, so am trying to use same linked server connection. It is failing with error:

  Msg 18483, Level 14, State 1, Line 1  Could not connect to server db03
  because '' is not defined as a remote login at the server.

Here is what I have done:

Verified kerberos is setup correctly and validated I am connecting with kerberos. http://msdn.microsoft.com/en-us/library/ms189580%28v=SQL.105%29.aspx
Changed linked server property to allow data access (turned off b/c replication created it).
Ran this (I think redundant from my GUI change) EXEC sp_addlinkedsrvlogin 'DB03', 'true'
Changed to using sql authentication account to connect on linked server properties and tested that it works.
Both servers are using same domain service account.

What I would like to happen is to use the "Be made using the login's current security context."

EXEC master.dbo.sp_addlinkedserver @server = N'DB03', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DB03',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

EXEC master.dbo.sp_serveroption @server=N'DB03', @optname=N'collation compatible', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'DB03', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'DB03', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'DB03', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'DB03', @optname=N'rpc', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'DB03', @optname=N'rpc out', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'DB03', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'DB03', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'DB03', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'DB03', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'DB03', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'DB03', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'DB03', @optname=N'remote proc transaction promotion', @optvalue=N'true'

Thanks, Chuck.


texts: Can't enable binary logging ("index not found")

texts:
I have a MySQL 5.5.41 server running on Ubuntu 14.04 that already contains several databases of data, and I would like to enable binary logging.  I want the binary logs to be placed in their own directory, /var/log/mysqld, which did not exist prior.  I created the directory, set its permissions to mysql:root 0755, and added a file to /etc/mysql/conf.d with the following contents:

[mysqld]
log_bin=/var/log/mysqld/mysql-bin.log
expire_logs_days=1
binlog_format=ROW

Unfortunately, running service mysql restart then failed to bring MySQL back up, and the MySQL error logs contain the message:

/usr/sbin/mysqld: File '/var/log/mysqld/mysql-bin.index' not found (Errcode: 2)

What do I have to do to get this to work?


texts:
I have a table, which was loaded dynamically from an external job. This table has a changing number of coulmns from time to time.
I want to unnest all the columns with the coulmn name as 'Key' and the value as 'Value':

SELECT
   unnest(array[(select array_agg( column_name::text )from information_schema.columns where table_name = 'tableName')]) AS "Keys",
   unnest(array[tableName.col1, tablename.col2, ...]) AS "Values"
FROM tableName ;

The problem that I'm facing is that I need something to replace the second unnest Statement, because I don't know the names of the columns.

I thought I just use unnest(array[tableName.*]) as "Values"but that does not work.

Any ideas welcome.

Example:

create table mytest ( col_1 varchar(30), col_2 varchar(30));
insert into mytest values ('a1','b1');
insert into mytest values ('a2','b2');
insert into mytest values ('a3','b3');
SELECT
   unnest(array[(select array_agg( column_name::text )from information_schema.columns where table_name = 'mytest')]) AS "Key",
   unnest(array[mytest.col_1, mytest.col_2]) AS "Value"
FROM mytest ;

Results in:

"Key"   "Value"
col_1   a1
col_2   b1
col_1   a2
col_2   b2
col_1   a3
col_2   b3

which is fine.
But when I get a table of unknown numbers of columns I need something to put into the second unnest statement (unnest(array[tableName.col1, tablename.col2, ...])) so I thought somethng like tablename.* instead of tableName.col1, tablename.col2.


texts: Is it possible to automate the creation of multiple FEDERATED tables in MySQL?

texts:
From the MySQL documentation on creating tables for to access data on a remote MySQL server using the FEDERATED storage engine:

  The basic structure of this table should match that of the remote
  table, except that the ENGINE table option should be FEDERATED and the
  CONNECTION table option is a connection string that indicates to the
  FEDERATED engine how to connect to the remote server.

I have a lot of existing tables that I want to federate against another server.  Is there a way to make this process quicker? Or I must go, table-by-table, copying and editing the definitions and creating the federated version of each table locally?

I found the command to extract the existing table definition from the remote server:

mysql> SHOW CREATE TABLE wp_options;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| wp_options | CREATE TABLE `wp_options` (
  `option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `option_name` varchar(64) NOT NULL DEFAULT '',
  `option_value` longtext NOT NULL,
  `autoload` varchar(20) NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`option_id`),
  UNIQUE KEY `option_name` (`option_name`)
) ENGINE=InnoDB AUTO_INCREMENT=999648 DEFAULT CHARSET=utf8 |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

So it seems like all I have to do is modify the ENGINE and add a CONNECTION definition... still, having to create federated tables manually for each of the existing tables is a time-consuming task.

Does MySQL have a way to do this automatically for a group of tables, or all tables?


texts: Execution plan showing missing index but query is fast

texts:
While looking at an actual execution plan, it's showing missing indexes even though the query is taking less than 1 second.

SELECT
    Account.AccountID,
    Account.Name
FROM
    account
    LEFT OUTER JOIN accountfeaturesetting afs
ON afs.accountid = account.accountid
and afs.featureid = 'Schedules'
 and
afs.settingid = 'EditReasons'
WHERE
    ISNULL(afs.Value, '0') = '0'
    AND EXISTS
(SELECT 1 FROM program WHERE program.AccountID = account.AccountID
 AND program.Active = 1
AND (program.ScheduleEditReasonFlags <> 0
OR program.ScheduleEditReasonFields <> 0))
    AND account.IsMaster = 0
    AND account.BeginDate IS NOT NULL

The execution plan is showing:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Account] ([IsMaster],[BeginDate])
INCLUDE ([AccountID],[Name])

Do we need to create the index even though query is only taking 1 second? On what basis should an index be created?

I am going to run this query as a daily job.


texts: DR - Moving databases from Active/Active cluster to Standalone server with LUN sync

texts:
We have an active/active cluster on LUNs (with one named instance on each node and a total of an 100 DBs)

For DR purpose, LUNs will be replicated (with same drive letters) and attached to a standalone server with same configuration of SQL Server but not same Windows Server editions (enterprise vs standard, but i don't think it may be an issue), and the two same named instances pre-installed.

I proposed the Attach / Detach solution, with a script that auto-generate the attach / detach to simplify the tasks, and other scripts to copy Logins, jobs and so on.

Other DBA said a better option will be to rename the MASTER and MSDB databases from the pre-installed named instances on standalone server with _old, and to attach the MASTER / MSDB databases from Cluster node instance to both corresponding named instance in standalone instance.

He said it works and had tested it.

However, I've never heard of such a solution nor can i find any documentation of it.

I would like to hear some other opinions.

Thank you,
Kukah


texts: Cluster migration to new hardware, same OS, but SQL Server 2012 to 2016

texts:
I have a 3 node cluster running on Windows Server 2012 R2. Two nodes for the databases, the 3rd node is an Always On db used for reporting. I have new hardware built running the same OS, but will need to upgrade the SQL Server version from 2012 to 2016. All servers will utilize the same SAN.

Should I stand up a new cluster running SQL Server 2016, restore the database to it, going through all the renaming and such? Or can I do a type of rolling upgrade where I join the new servers to the existing cluster (I guess I would need to install 2012 on them first and then kick off the upgrade to 2016 on them) once I fail over to the new servers, I would evict the old nodes without upgrading them?

This seems like it would have the least amount of downtime. But I'm not sure  if I can easily rejoin the old servers to the cluster and rollback to the 2012 instance if there are problems with the 2016 instance. Most whitepapers I see revolve around OS changes, not same OS, but new SQL versions.


texts:
I want to build a function which will insert an email if the email value doesn't exist in the table and return the email_id of the row. How can I do this?
Also how can I return the id if the email was not inserted and it already exist in the DB? Do I need to perform another SELECT?

BEGIN;
  LOCK TABLE mailing_list IN SHARE ROW EXCLUSIVE MODE;
  INSERT INTO mailing_list (email)
  SELECT 'email'
   WHERE NOT EXISTS (
     SELECT * FROM mailing_list WHERE email='email'
   );
COMMIT;

I have tried adding the returning id but it doesn't work. I got:

  query has no destination for result data

Sqlfiddle


texts: When I have 6 cores and they do a table scan and merge afterwards, does every core scan the whole table?

texts:
I remember that I once had a missing index on a table and one query was taking ages. In the execution plan I saw that a table scan and a merge was done. Statistics from IO showed me that every core did a table scan, or better that I got 6 table scans.
Now I wonder if every core does a full scan, or does every core roughly do 1/6 table scan?
I know for sure that if I had a proper index, I either just get a seek or it would be split for every core.

I hope you understand what I ment here, unfortunately I can't provide any information, since the question just came up and my problem was solved years ago.


texts: Sql Server 2008 R2 Memory Caching , why doesn't it release the memory?

texts:
i am working on SQL server 2008 R2. It has been allowed to take a maximum memory which is around 45 GB. In couple of days it caches everything and the limit is reached. We have other processing as well on the server. Why doesn't it release all the memory after it has finished the processing?

How can i know the reason of the issue which process is doing this caching? I could find out some of the query plans cached but 40 GB of caching after that other processes from the SQL server it takes too much around 47/48 GB which has been an headache.


texts: Trigger to update a date field on one table when another table is updated

texts:
I want to be able to update a date filed in one table when another table is updated.
I have the following two tables: Transdetails, Customers.

In the Customers table I have a field called ZLastDateOfSale. I want this field updated with the current date when Transdetails.TradingDate is updated per Customer.

Can you please help me create this Trigger?

My Example is below:

CREATE TRIGGER trg_LastSaleDate ON dbo.Transheaders
    AFTER UPDATE
AS
    IF UPDATE(TradingDate)
    BEGIN
        update dbo.Customers
            set dbo.Customers.ZLastSale = dbo.Transheaders.TradingDate
        from dbo.Customers
    END

Updated Example 11/05/2014:

CREATE TRIGGER trg_LastSaleDate ON dbo.Transheaders
    AFTER UPDATE
AS
    IF UPDATE(TradingDate)
    BEGIN
        UPDATE c
            SET ZLastSales = i.TradingDate
        FROM dbo.Customers AS c
          JOIN inserted AS i
            ON i.UniqueID = c.UniqueID     -- use the appropriate column for joining
          JOIN deleted AS d
            ON  i.AccountID = d.AccountID
            AND ( i.TradingDate <> d.TradingDate
               OR d.TradingDate IS NULL
                ) ;
    END ;

Hello,

Thank you so much for your input and response.
I changed your provided example, ran the script and it was successful, however, what it did was to update the ZLastSale date on all Customers with todays date. This field should be blank and only update moving forward from now when the Trigger fires. This field should only be updated upon a sale from a Customer.

Also, when I tested entering a new transaction with a date of 30/05/2014 it didn’t update the required ZLastSale field. It is displayed as 11/05/2014 so something is not working right somewhere.

My ID’s for each table are:

Customers.UniqueID and Transheaders.AccountID

Thanks again for your help.


texts: Is there a better ticketing system to check PostgreSQL issues?

texts:
I want to keep track of an issue in PostgreSQL. This mailing list tool that they have is hard.

Is there an easier way to track their issues, open one and track it?

Like a mirror system they have in Github.


texts: Representing an IS A relationship in a SQL database

texts:
Let's say I want to store information about several persons in an database. Every person can have no, one or more roles. Each role has some attributes like a name, number and so on (the roles have different attributes).

In my opinion this relationship between a Person and a Role can be described as "IS A". Am I right?


texts: How to find tempdb reside in drive location with drive size and tempdb usage details in single query in sql server?

texts: How to find tempdb reside in drive location with drive size and tempdb used details  in single query in sql server?


texts: Modelling constraints on subset aggregates?

texts:
I am using PostgreSQL but I figure most of the top-end db's must have some similar capabilities, and moreover, that solutions for them may inspire solutions for me, so don't consider this PostgreSQL-specific.

I know I am not the first to try to solve this problem so I figure it is worth asking here but I am trying to evaluate the costs of modelling accounting data such that every transaction is fundamentally balanced. The accounting data is append-only. The overall constraint (written in pseudo-code) here might look roughly like:

CREATE TABLE journal_entry (
    id bigserial not null unique, --artificial candidate key
    journal_type_id int references  journal_type(id),
    reference text, -- source document identifier, unique per journal
    date_posted date not null,
    PRIMARY KEY (journal_type_id, reference)
);

CREATE TABLE journal_line (
    entry_id bigint references journal_entry(id),
    account_id int not null references account(id),
    amount numeric not null,
    line_id bigserial not null unique,
    CHECK ((sum(amount) over (partition by entry_id) = 0) -- this won't work
);

Obviously such a check constraint will never work. It operates per row and might check over the entire db. So it will always fail and be slow doing it.

So my question is what is the best way to model this constraint? I have basically looked at two ideas so far. Wondering if these are the only ones, or if someone has a better way (other than leave it to the app level or a stored proc).

I could borrow a page from the accounting world's concept of the difference between a book of original entry and a book of final entry (general journal vs general ledger).  In this regard I could model this as an array of journal lines attached to the journal entry, enforce the constraint on the array (in PostgreSQL terms, select sum(amount) = 0 from unnest(je.line_items).  A trigger could expand and save these to a line items table, where individual column constraints could more easily be enforced, and where indexes etc could be more useful.  This is the direction I am leaning.
I could try to code a constraint trigger that would enforce this per transaction with the idea that the sum of a series of 0's will always be 0.

I am weighing these against the current approach of enforcing the logic in a stored procedure.  The complexity cost is being weighed against the idea that mathematical proof of constraints are superior to unit tests.  The major drawback of #1 above is that types as tuples is one of those areas in PostgreSQL where one runs into inconsistent behavior and changes in assumptions regularly and so I would even hope that behavior in this area might change over time.  Designing a future safe version is not so easy.

Are there other ways to solve this problem that will scale up to millions of records in each table? Am I missing something?  Is there a tradeoff I have missed?

In response to Craig's point below about versions, at a minimum, this will have to run on PostgreSQL 9.2 and higher (maybe 9.1 and higher, but probably we can go with straight 9.2).


texts: SQL Prepared statement reuse in plan cache

texts:
I'm working on minimising our plan cache usage, and I've read a lot around Adhoc queries and how we can parameterize and reuse rather than having many single use plans.

I'm not seeing as much information out there about the prepared plan reuse.  We have around 100gb of RAM dedicated to our production box, and 90% of our plan cache is divided into ~10% Single Use Adhoc, ~35% Plan stubs Adhoc and 36% Single Use Prepared.

So, I am basically trying to understand what prepared really means.

I believe the plans exist only for the duration of the connection, but I can see that many of the plans are duplicates similar to this:

(@bitValid BIT OUTPUT)IF(SELECT COUNT(pkItemID) FROM vwValidateDetail WITH (NOLOCK) WHERE fkInvoiceID = 43630155) > 0 BEGIN SET @bitValid = 1 END

Similarly, there are UPDATE statements where there are many plans for the same type - where one part is parameterized and the rest of the query isn't.

(@ElementValue nvarchar(4000))UPDATE ImportInterfaceSub Set [sub1] = @ElementValue WHERE [ID] = 18926954

Even if these queries are short lived, is there a better way I can parameterize the queries to re-use for memory?

The sql in question lives in a stored procedure which looks parameterized already IE:

UPDATE ImportInterfaceSub Set [sub1] = NULL WHERE [ID] = @id

Is this just a factor of how the engine operates, or is there a gain to be re-writing prepared queries as well as the single-use adhoc queries?


texts:
I have seen these 2 extensions used for datafiles, .dat and .dbf while creating and/or altering a tablespace. I'm not sure what the difference between the 2 extensions is, or if .dat is incorrect.

Here are 2 examples from Oracle Database SQL Reference 10g Release 2 (10.2)

.dat

CREATE TABLESPACE tbs_01
   DATAFILE 'tbs_f2.dat' SIZE 40M
   ONLINE;

.dbf

CREATE TABLESPACE tbs_03
   DATAFILE 'tbs_f03.dbf' SIZE 20M
   LOGGING;


texts: How to loop through all table rows and perform subquery using values of same row?

texts:
I have a table structured as follows:

| reference | valA | valB |    xml      |
     1        NULL   NULL   <xml stuff>
     3        NULL   NULL   <xml stuff>
     4        NULL   NULL   <xml stuff>
     6        NULL   NULL   <xml stuff>

My objective is to parse the xml column and insert the values into columns valA and valB. I have a query which does this but I only know how to do this by specifying the reference number of the row:

UPDATE table
   SET  valA = (SELECT xml.value(_parsing queryA_) FROM table WHERE reference = N),
        valB = (SELECT xml.value(_parsing queryB_) FROM table WHERE reference = N)
   WHERE reference = N;

I need to be able to loop through each row in the table and perform the same query in each row based on values in that row. So the pseudo code for this would be:

UPDATE table
   SET  valA = (SELECT xml.value(_parsing queryA_) FROM current row),
        valB = (SELECT xml.value(_parsing queryB_) FROM current row)
   in current row;

I am currently doing this in an awkward way by implementing this loop in my main program (C++) by first reading in each reference in an array and then looping through the array for the reference number and setting it in the query in place of N in the example query above.

I am very new to SQL so I don't know if I am making this more complicated than it is and if there's an easy way to do it.

I have been running this operation for many hours (about 164,000 rows in table) so I have a partially updated table.


texts: Correcting a database that has had indexes and statistics created on it willy-nilly

texts:
I've inherited a DB and I believe it was the victim of DBAs who encountered random slow operations, ran the tuning adviser for individual queries and blindly created the recommend indexes and statistics for said individual cases with no concern for the impact on the DB overall.
How do I remedy this situation?

I myself am not a DBA but am interested in doing this properly -

Should I drop all indexes and statistics and start over?
Is there a tool out there that can look at the entirety of SQL operations and recommend which indexes and stats to delete, update or create.
Any recommended standard procedures for this situation, I can't imagine I am unique in encountering this scenario

I'm on SQL Server 2012


texts:
I've seen a lot of hate directed to pgAdmin4, but honestly I can't see why. It worked out of the box from a docker image, connected right away to the database and perfectly managed every maintenance and query I've made.

Is there a good reason to keep using pgAdmin3?


texts:
I am updating a database to use multiple filegroups. As it stands, it uses just the PRIMARY filegroup.

I have read that it is recommended to create a second Filegroup (called 'Data') and mark that one as the default. Will all database objects (tables, indexes, etc) automatically switch to this new one?

Secondly, does it matter how many files are created for each filegroup? Should I create more than a single file for each filegroup or will this make any difference performance wise?

Thanks!