texts: Sql Server 2017 Developers Edition install failure during configuration

texts:
When installing SQL Server 2017 Developers Addition, the install would get to the point where it needed to connect to the service to configure the users (and whatever else it does), and it would fail to connect.

This is my development machine, and I had uninstalled other versions of SQL Server (2016, 2014, 2012, 2008 R2) previously and rebooted.  I tried removing C++ 5 and 7 shared component.  I uninstalled SSMS.  I completely deleted Sql Server in Program Files and Program Files (x86). I could not figure it out.


texts: select rows where values equal the result of count(*) group by?

texts:
I have a table like this:

Each keyword should have 4 pieces of data.

I want to select the keywords that have less than 4 pieces of data (less than 4 rows).

I used select count(*) from mytable group by keyword to achieve something like this:

how can I select the keywords based on the count(*) result?

Here I want the keyword b from the result grid.


texts: Using count() and sum() in a calculation in a select

texts:
I am trying to do the following in a single select. We have a SAAS that has Company records and User records. A User is a member of a Company (Company.CompanyId = User.CompanyId). In the User record we track bandwidth usage for that user (for the month).

I need to do a select that gives me all companies that have used over 1GB of bandwidth per user. I can get this company by company using:

select count(*) as numUsers,
  sum(BandwidthThisMonth) as totalBandwidth
from [User]
where [User].CompanyId = @CompanyId

The problem with that approach is I have to first get the CompanyId of every company, and then one by one pull this data. And I only need to find the cases where the usage is over 1GB/user, which is rare.

Is there a way to do something like this:

select
  CompanyId,
  count(*) as numUsers,
  sum(BandwidthThisMonth) as totalBandwidth
from Company, [User]
where totalBandwidth - (numUsers*1000000000))/1000000000 > 0

Where the count() & sum() are against just the User records where User.CompanyId = Company.CompanyId?


texts: Selecting the average of the top percent of duration, grouped by minute

texts:
I am attempting to find the average of (the top 5% of duration/ per minute).

I wrote this query:

select AVG(Q.duration) Avg5Perc, Q.time FROM
(

          select top 5 PERCENT duration, time from table (nolock)
          WHERE time between '2015-06-5 13:00' and '2015-06-5 13:59.99'
) Q
group by datepart(minute, Q.time), Q.table
ORDER BY Q.table desc;

Basically, in the sub-select I am acquiring the top 5 percent of values, and in the outer select I am averaging them. However, when I run the query, I do not actually have it grouped by minute.

Any idea? Thanks


texts: Database replication from local Postgres to live

texts:
I have a software whose data from its local Postgres server has to be synced to live database, so that I can query data from that live Postgres server via api. I am new to Postgres. So after a small research I came across Database replication concept such as hot standby, streamed replication, wal files, background processes etc.
My question is whether it is possible to replicate my local Postgres database to live Postgres database and also access or query the live Postgres database for CRUD operations and also I want both the databases to be in sync irrespective of the database on which the CRUD operations are being performed.
Found similar questions already answered.

How to PUSH data between a Local DB Server to Cloud DB Server?
Streaming replication Postgresql 9.3 using two different servers (can we write on the copy database?)


texts: Order of DROP_EXISTING and ONLINE for CREATE INDEX

texts:
If I call a CREATE NONCLUSTERED INDEX using DROP_EXISTING = ON and ONLINE = ON what order are things done it?

What I want is:

New index is made in the TempDB
Existing Index is dropped
New index is put in its place.

But I worry that it is like this:

Existing Index is dropped
New index is made in the TempDB
New index is put in its place.

Basically I am wondering if the old index is gone while the new index is being made?  (I want it to be there to limit the time when I am running without any index.)

If it is a long time frame then I will make a new index with a different name then drop the existing one.


texts: BI-DIRECTIONAL Oracle Golden Gate for Oracle 12c and SQL Server 2014

texts:
I have two servers (both servers are on Windows OS):

Server_A running Oracle 12c
Server_B running SQL Server 2014

I want to implement data synchronization between them with Oracle Golden Gate technology.
I've downloaded these software from Oracle's website:

  Oracle GoldenGate V12.1.2.1.0 for SQL Server on Windows (64 bit) (71 MB)
  Oracle GoldenGate V12.1.2.1.0 for Oracle on Windows (64-bit) (239 MB)

Which one of these two software I have to install in each servers?
I want it to be BI-DIRECTIONAL.
How configure each servers to achieve this goal?


texts: What features of Oracle make it an attractive choice for small projects?

texts:
Given Oracle's licensing handling[a] (and, to a lesser extent, cost) I have always been wondering what the deciding factors would be for choosing Oracle over PostgreSQL or MySQL.

My company is nearly always choosing Oracle (XE where possible), even for small projects where there is just a single simple Windows server box running the database without any dedicated DB administration. (Note that small does not mean the data will always fit the rather small size constraints of Oracle XE.)

I have always questioned this choice, but it has the benefit that we at least are exposed to only one database product.

Still, given a new project, where you need a RDBMS, but the project and scope of the database is pretty small, based on which unique features of Oracle running on simple Windows server boxes (without too much dedicated administration) would you choose Oracle over another RDBMS?

Additional Context: A lot of our database deployments run at customer sites in a, lets call it, "low-administration" mode. That is, the database is set up once. There is some initial testing on its correct behavior and performance on site. After this, the database just run's on its on. No regular administration done. Only if somethings broken will a technician (not a dedicated DBA) check the database, trying to figure out what's up. Backup is mostly done as offline backup. In some projects, the customers don't even care there's a RDBMS involved. They just see their app as a black box that works (or not).

[a] : Where I work, it took several project managers repeatedly months to get proper licensing for small projects as the local Oracle representatives are simply not very interested in selling their product if the revenue is smallish.


texts: Quiet installation of MS SQL 2012 RC0 from command line using config file

texts:
My problem is that I recently deployed Microsoft SQL server 2008 R2 Express to create a database for a locally hosted application to access.  To save myself time I used the /CONFIGURATIONFILE option to set it all up to run from the command line so I could stript the installation from soup to nuts.

I now am trying to use the same ini file with Micro SQL Server 2012 RC0 and the thing won't install quietly.  I continually get the Window to configure installation even if I use /Q="true", /QS="true", and I even tried /QUIET="true" but the window continues to open.

Is there a way to force MS SQL Server 2012 RC0 to install quietly or is that functionality that is not in this release?


texts: Unique key in SQL Power Architect? Forward Engineering

texts:
I want to know if there is a way to create unique keys (not PKs) in SQL Power Architect using forward engineering.

I've searched google, saw the tutorials by the software vendor, and checked out in youtube, but I didn't came across with a solution.

Can anybody here help me out?

What I want to do is to define an unique (or alternate) key (UK/AK) for some attributes in one row.

Thanks a lot! :)


texts: Create new Read Only user on DB2 on the whole database

texts:
I am an oracle/sql server DBA having limited knowledge in IBM DB2. We have DB2 9.7 fp4 running on AIX 6. I would like to create a new user and grant him READ only privilege on a particular database. Please help!

Thanks & Regards
Mohammed


texts: Clustered Index Scan on partitioned table even when there is covering index

texts:
I have a partitioned table which is partitioned based on col1 int.  I also have a covering index for the query that I am trying to troubleshoot.

https://www.brentozar.com/pastetheplan/?id=BkNrNdgHm

Above is the plan

Left to its wishes SQL Server decides to do a clustered index scan of the entire table which is obviously slow.  If I force the index (like in the plan above) the query runs quickly.

What magic logic does SQL Server use to decide that the covered index is not useful?  I am not sure if top/orderby and rowgoal has anything to do with it.

My table structure is

Create table object2(col1 int, col3 datetime, col4 int, col5, col6 etc) clusterd on col1

nonclustered non aligned index is on col3,col4 (col1 is clustered so its included in nonclust)

SELECT top(?) Object1.Column1
    FROM Object2 Object1 WITH (NOLOCK,index(Column2))
    WHERE  Object1.Column3 >= ?
    AND Object1.Column4 IN (?)
    ORDER BY Object1.Column1

Edit added Repo

    CREATE PARTITION FUNCTION [PFtest](int) AS RANGE RIGHT FOR VALUES (100000, 200000, 300000, 400000, 500000, 600000, 700000, 800000, 900000, 1000000)
    GO
    CREATE PARTITION SCHEME [PStest] AS PARTITION [PFtest] all TO ([PRIMARY]);
    GO

    create table test([ID] [int] IDENTITY(1,1) NOT NULL primary key clustered,[Created] [datetime] NULL,[Type] [int] NULL,text1 varchar(10),text2 varchar(20))
    on pstest(id)
    set nocount on

    declare @a int =1
    declare @type int
    while 1=1
    begin
    if @a%30 =0
    insert into test (Created, Type, text1, text2) select getdate(),4,'four','four'
    else
    insert into test (Created, Type, text1, text2) select getdate(),1,'one','one'
    set @a=@a+1
    end
    create nonclustered index ncl1 on test(created, type)

select min(created),max(created) from test
--2018-08-02 22:46:40.187   2018-08-02 22:49:01.577
SELECT top(10) ID
    FROM test
    WHERE  Created >= '2018-08-02 22:49:01'
    AND Type IN (1, 4)
    ORDER BY ID -- clustered index scan

SELECT top(10) ID
    FROM test
    WHERE  Created >= '2018-08-02 22:49:01.577'
    AND Type IN (1, 4)
    ORDER BY ID-- index seek of ncl1


texts:
I have a posts collection in the following format:

{
    reqID: Int32
    chatID: Int64
    messageID: Int32
    visits: Int32
}

I also have a user collection which has an array field named visits:

visits: [
{
    chatID: Int64
    messageID: Int32
    lastVisit: Int64
}
]

I want to select every post from the first collection where the desired user has not visited it in last 24 hrs. Well this is an outer join, but MongoDB has limited support for outer joins only using $lookup which is appropriate only for simple foreign key lookup. Are there any tricks which can help me to find what I want internally in DMBS?


texts: I know there are plenty of posts here regarding SQL Server and SSL encrypted connections, however I didn't find any that touched on this particular subject. In this article, it mentions that performance is hindered with enabled SSL connections due to the extra processing required. In a shared database server, where database-A will have encrypted connection, but database-B will not, will database-B's performance be affected somehow due to the encrypted connections being processed on database-A?


texts: How to use full text search in Persian using Postgresql?

texts:
I'm using Postgresql 9.6 and I'd like to run a query like this:

select post_id
from comments
where to_tsvector('pg_catalog.persian', comments.body) @@ to_tsquery('pg_catalog.persian', 'کتاب') ;

But Persian is not among the languages supported in postgresql dictionary:

postgres=# \dF
               List of text search configurations
   Schema   |    Name    |              Description
------------+------------+---------------------------------------
 pg_catalog | danish     | configuration for danish language
 pg_catalog | dutch      | configuration for dutch language
 pg_catalog | english    | configuration for english language
 pg_catalog | finnish    | configuration for finnish language
 pg_catalog | french     | configuration for french language
 pg_catalog | german     | configuration for german language
 pg_catalog | hungarian  | configuration for hungarian language
 pg_catalog | italian    | configuration for italian language
 pg_catalog | norwegian  | configuration for norwegian language
 pg_catalog | portuguese | configuration for portuguese language
 pg_catalog | romanian   | configuration for romanian language
 pg_catalog | russian    | configuration for russian language
 pg_catalog | simple     | simple configuration
 pg_catalog | spanish    | configuration for spanish language
 pg_catalog | swedish    | configuration for swedish language
 pg_catalog | turkish    | configuration for turkish language
(16 rows)

So I get error:

  ERROR:  text search configuration "pg_catalog.persian" does not exist

My default DB config is for English:

postgres=# show default_text_search_config;
 default_text_search_config
----------------------------
 pg_catalog.english
(1 row)

I've tried pg_catalog.simple but it returns anything, while there are  results for کتاب.

So I'm wondering how can I make a Full text Search in this situation to get relevant Persian results?


texts: Which is the best way for selecting dimension attributes while creating a dimension?

texts:
I'm a newbie to SSAS OLAP so please apologize me if it was a silly question but surely I need suggestions. I would like to make clear some concepts which was confusing for me.

Let us consider five tables:

1. FileImport

2. DepositLog

3. Divisions

4. CardDetail

5. Date

Here, the process is, I'm importing a deposit file to database so the file details like name, date and time will be imported to FileImport table.

The data inside of the file will be imported to DepositLog.

The Divisions table will have the clients detail and the CardDetail has the card types and card holder data details.

So, DepositLog will have primary-foreign key relationships to FileImport, DivisionID, Date and CardDetail.

For example,

  FileImport has columns as shown below

 1. FilePkId
 2. FileName

  Divisions has columns as shown below

  1. DivisionPkId
  2. DivisionName

  CardDetail has columns as shown below

1. CardPkID
2. CardNumber
3. CardExpDate

  Date:

1. DatePkId --> has date as key value
2. Monthname
3. Yearname

  DepositLog table has columns with primary-foreign key relationship to
  all those above four tables:

   1. Id
   2. FileFkId
   3. DivisionFkId
   4. CardFkId
   5. DepositSubmitDate --> has foreign key relationship with (DatePkId from Date table)
   6. DepositUnit --> numeric value column

  My Doubts:

When adding the table to DSV, do I need to specify the same relationship between those tables as specified above?
When creating a dimension for DepositLog table, what are the dimension attributes we have to select into it?
How to define the attribute relationship for those attributes in DepositLog dimension?
Is that necessary to create or set attribute relationship even if the dimension has no hierarchies in it?
How does the tables are joined in SSAS and whether in DSV or dimension or based on Dimension Usage that we have specified ?
Please suggest me some links which may help me to gain insight into SSAS.

Please guide me through the following and thanks in advance.


texts: Create a check constraint across multiple schemas in PostGIS

texts:
I'm a little new to this so apologies up front if my vocabulary is incorrect.

I'm trying to create a check constraint that determines whether a new point is located within an existing polygon. The issue is that the new point is created in one schema, and the existing polygon geometry is located in another schema.

I believe I need to create a function using
ST_Within(NEW.geom, existing.geom) and then call that function as a constraint. However, I don't know how to deal with the syntax when using schema-qualified tables, and also be able to use NEW.geom in the function.

Any help is greatly apreciated!


texts: How can I disallow restore from backup on SQL Server?

texts:
I want to disallow restore from backup. How can I do this? One Of my friends said to me

  "Take a certificate for your database and anyone cant restore from your backup"

I searched about certificate but I didn't find anything. I want to be sure that nobody copy my database from backup or the other ways.

Is there anyway about this problem?


texts: What are the major differences between mysql5.6 and mysql 5.7

texts: Can anyone tell me the difference between mysql5.6 and mysql5.7


texts:
Which port number to be used in forms 11G Preference-->Runtime--> Application ServerURL Section?   I have:

1521-Given at the time of database creation
7003 and 7004-Given at the time of WLS Domain creation

Is it necessary to save the Forms before compiling or running the forms in browser?  If yes, then, is there any fixed location were I have to save it or I can save it anywhere and run it from that location?


texts: Preparing a cluster installation fails at Setup Support Rules Step (Not Clustered or the cluster service is up and online)

texts:
Today I was trying to install a SQL Server 2008 R2 Cluster environment on Windows Server 2012 R2 servers and after doing all the necessary steps to prepare the cluster (like Creating an iSCSI Virtual Disk for Windows Cluster, configuring both node members, installing .net Framework 3.5, Adding the Windows Cluster Failover Feature, configuring the cluster, etc...) I continued by running the "Advanced Cluster Preparation" installation in SQL Server 2008 R2.

During one of the initial steps (the one checking the Rules) I find that the "Not clustered or the cluster service is up and online" step fails...

Upon inspection of the details I see that the "machine is clustered, but the cluster is not online..." message

... not sure the reason why, since when I inspect the cluster using Failover Cluster Manager everything seems all right

I can even do a fail-back on the drives and a fail-over with no problem. I also ran the Validation report and everything came back green...

Any ideas of what could I be missing?

More investigation

This is what I found when looking in the SystemConfigurationCheck_Report.htm (on the bootstrap log)... apparently it is more or less the same message. All the other checks are "green" (OK).

Also, on further bootstrap log investigation, I found this section (in the detail.txt for one of my executions) that appears to shed some more light...

What "the given key was not present in the dictionary" means... I can't say. I'm not an expert in advanced cluster troubleshooting so I am only speculating here... I have updated the contents of my bootstrap log here if anybody thinks they can find some more "clues" that I have not been able to find...

More findings (24 hours later...)
So, I have not been able to find the root cause of the problem, even following suggestions in this page and this KB, nothing seems to help me with that Support Rule!

Today, out of desperation, I attempted to run the "Advanced Cluster Preparation" using the installation image for SQL Server 2017 Enterprise and that one indeed worked with no problems!

So the issue seems to be limited to SQL Server 2008 R2 cluster binaries (something that sometime after 2008 R2 was addressed). This is not a solution for me (as I need to make this work in 2008 R2 for various reasons) but at least sheds some more light into the problem (hopefully) and tells me there is nothing wrong with the Failover Cluster install and configure per se, but more of how SQL Server 2008 R2 interprets that I have a fully-functional cluster.


texts: How to block dedicated server requests in Oracle Database?

texts:
I have gone through some docs but no luck. Oracle database is always enabled to allow dedicated server processes. You can't control tnsnames.ora but you need to disallow clients to connect the database service in dedicated server mode.
Is it possible to disable dedicated server connection in server side or to block out dedicated server request from some clients in a technological way?


texts: How do I find out who is the resource hog in SQL Server 2008?

texts:
We have a SQL Server 2008 installation on a machine with 24+GB of RAM and 16 cores.  Plus fast drives, etc.  A large machine by our standards.

Anyway, that machine is 100% devoted to SQL Server and there are TWO production databases on it.  Each DB is from two different vendors.

This has been running fine for 2 years.

Now, within the last 2 WEEKS, Vendor B is saying that the SQL Server can't handle their 8 GB database in performance.  Nothing has changed on our end in months.  Our DB server hasn't been touched.

Vendor A is working just fine.

So it sounds like Vendor A is taking more resources and slowing down Vendor B.  But I don't suspect Vendor A is to blame because I am familiar with the their DB and its users.

How can I find out how much resources each DB is using?  How I can say Vendor A gets 50% and Vendor B gets 50% of the server?


texts: Simple XML .query SELECT statement times out on 1 SQL SERVER instance

texts:
I dropped and recreated a stored procedure on a DB on a SQL Server 2012 instance (SERVER1/INSTANCE1), after which the sproc started hanging on a line.   Using profiler I have reduced a recreateable scenario on this SQL Server instance to the following code:

DECLARE @RequestXML xml = '<Foo>
    <Node1>Hello</Node1>
    <Node2>World</Node2>
    <Node3>Today</Node3>
</Foo>'

SELECT
    @RequestXML.query('/Foo/Node1[1]')
    ,@RequestXML.query('/Foo/Node2[1]')
    ,@RequestXML.query('/Foo/Node3[1]') -- comment out any of these lines so only one or two items are returned and the query works fine!

This statement hangs.   If you comment out one of the returned columns in the SELECT statement, then the statement runs fine.  So one of two returned columns is fine, but anything over is bad.  If you take out the first node selector (i.e. the [1]) it also runs fine.

Here's the rub though:

the statement fails on any DB in this SQL Server 2012 instance (SERVER1/INSTANCE1).
the statement succeeds on any other SQL Server instance I have tried, including  other instances running from the same SQL Server machine as the failing instance (e.g. SERVER1/INSTANCE2, SERVER1/INSTANCE3 etc.) - all of which are 2012 instances.

It seems to me that SERVER1/INSTANCE1 has borked the way it runs this statement.  I have heard that SQL Server does things with optimising execution of statements, but my knowledge stops there.  I'm sure there must be some way to get it to behave again, but how?

UPDATE

The following adjustment (getting the singleton of the Xpath result-set rather than potentially multiple first elements) solves the issue above for the SERVER1/INSTANCE1 issue.  I would put this as the answer, except that I don't believe this is identifying the underyling problem (please correct me if I'm wrong!).  Given that our codebase deployed on other servers implements the above statement (or derivatives of it) I don't want to update our entire shredding XML approach without good reason (or at least I would have to justify it to my peers).

DECLARE @RequestXML xml = '<Foo>
    <Node1>Hello</Node1>
    <Node2>World</Node2>
    <Node3>Today</Node3>
</Foo>'

SELECT
    @RequestXML.query('(/Foo/Node1)[1]')
    ,@RequestXML.query('(/Foo/Node2)[1]')
    ,@RequestXML.query('(/Foo/Node3)[1]')

Any help gratefully received.

Thanks,

Ali


texts:
I need to do A LOT (millions) of lookups and I need them be as efficient as possible.

I'm using the ip4r extension for PostgreSQL:
https://github.com/RhodiumToad/ip4r/blob/master/README.ip4r

Running PostgreSQL 9.6.1

My tables:

CREATE TABLE test_cidr_ipv6
(
    id SERIAL PRIMARY KEY,
    cidr ip6r NOT NULL,
    domain INT NOT NULL,
    start TIMESTAMP NOT NULL,
    end TIMESTAMP,
    data JSON NOT NULL
);
CREATE TABLE test_ipv6
(
    id SERIAL PRIMARY KEY,
    ip ip6 NOT NULL,
    domain INT NOT NULL,
    timestamp_start TIMESTAMP NOT NULL,
    timestamp_end TIMESTAMP,
    data JSON NOT NULL
);

Two Indices:

CREATE INDEX test_ipv6_idx ON test_ipv6 (ip);
CREATE INDEX test_cidr_ipv6_idx ON test_cidr_ipv6 USING GIST (cidr);

Current query:

(SELECT
   test_ipv6.ip,
   test_ipv6.data,
   test_cidr_ipv6.cidr,
   test_cidr_ipv6.data,
   test_ipv6.start,
   test_ipv6.end,
   test_cidr_ipv6.start,
   test_cidr_ipv6.end
 FROM test_ipv6, test_cidr_ipv6
 WHERE
   test_ipv6.ip = ip6('2001:DB8::1')
   AND ip6('2001:DB8::1') <<= test_cidr_ipv6.cidr
   AND '2017-01-01 00:00:00.100000' BETWEEN test_ipv6.start AND test_ipv6.end
   AND '2017-01-01 00:00:00.100000' BETWEEN test_cidr_ipv6.start AND test_cidr_ipv6.end)
UNION ALL
(SELECT ... )  # same as the query above, but with a different ip.
UNION ALL
...            # repeated 1000+ times

Question #1-
Is there a better way to repeat the same query like I'm doing in a single "batch" without UNION ALL?

Analyze on a single query:

db=# explain analyze SELECT
db-#    test_ipv6.ip,
db-#    test_ipv6.data,
db-#    test_cidr_ipv6.cidr,
db-#    test_cidr_ipv6.data,
db-#    test_ipv6.start,
db-#    test_ipv6.end,
db-#    test_cidr_ipv6.start,
db-#    test_cidr_ipv6.end
db-#  FROM test_ipv6, test_cidr_ipv6
db-#  WHERE
db-#    test_ipv6.ip = ip6('2001:DB8::1')
db-#    AND ip6('2001:DB8::1') <<= cidr
db-#    AND '2017-01-01 00:00:00.100000' BETWEEN test_ipv6.start AND test_ipv6.end
db-#    AND '2017-01-01 00:00:00.100000' BETWEEN test_cidr_ipv6.start AND test_cidr_ipv6.end;
                                                                               QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.29..16.34 rows=1 width=127) (actual time=0.023..0.026 rows=2 loops=1)
   ->  Index Scan using test_ipv6_idx on test_ipv6  (cost=0.14..8.17 rows=1 width=71) (actual time=0.015..0.015 rows=1 loops=1)
         Index Cond: (ip = '2001:DB8::1'::ip6)
         Filter: (('2017-01-01 00:00:00.1'::timestamp without time zone >= start) AND ('2017-01-01 00:00:00.1'::timestamp without time zone <= end))
   ->  Index Scan using test_cidr_ipv6_idx on test_cidr_ipv6  (cost=0.14..8.17 rows=1 width=56) (actual time=0.005..0.008 rows=2 loops=1)
         Index Cond: ('2001:DB8::1'::ip6r <<= cidr)
         Filter: (('2017-01-01 00:00:00.1'::timestamp without time zone >= start) AND ('2017-01-01 00:00:00.1'::timestamp without time zone <= end))
 Planning time: 0.154 ms
 Execution time: 0.066 ms
(9 rows)

Question #2-
Is the "Nested Loop" concerning?
Is there a better way to structure this type of query to get the data I'm after?

When I do the "bulk" query by doing a UNION ALL a bunch of times with this same query block, the execution time is ~4sec for 5k IPs at a time. Ideally, I would like to get 5k of IPs down to 500ms.

If anyone has any pointers for how to make this type of query more efficient I would be forever thankful.


texts: When creating new maintenance jobs for an alwayson setup with new service accounts, should we ensure that the sid's on both the nodes should be the same? I created a new login with the new domain service accounts and scheduled Olla Hallengren's scripts. I just wanted to check the sid of the service account  on both the nodes. They are different. Is this something which i should worry about?Thanks!


texts: Is it possible to prevent an insert without rolling it back?

texts:
In MySQL 5.5, I know I can prevent an insert by using SIGNAL, which rolls back the current transaction of my INSERT.

The fact is I want to use a trigger to insert a row in another table,
but I'd like that to replace the original insertion. If I use SIGNAL in the trigger the new insert statement is also cancelled.

Example:

If I do

INSERT INTO table_a VALUES('a','b');

I want to have a new row in a table_b but none in the table_a.

(I want to replace the original insertion only in a few cases.)

Is it possible in MySQL?
If yes, how can I do this?


texts: Can we migrate Azure SQL Database's objects to In-Memory OLTP? IF yes then how to perform this step by step?


texts: Is it better to omit associative tables in displaying many to many relationship

texts:
Let's say I have 2 entities Student and Class with many to many relationships. Usually, in the textbook, it is recommended that we create another associative table (maybe called Enrollment) to convert many-to-many relationships becoming 2 one-to-many relationships.

So this design above is correct and I have no problem with it.
However, I also thinking of a more simple design like this
Student
id name class_id
1  Jake 1
2  Jake 2
3  John 1

Class
id name
1  Math
2  English
3  Physics

And I think it can also work fine without even creating the third tables. (and only Student table has the foreign key)
So my question is what are the pros and cons of the second method ( without associate tables). Is there any particular case that making the text-book method (1st solution) better than the 2nd solution and vice versa. Thanks


texts: Columnstore Index maintenance and finding fragmentation

texts:
We are using clustered columnstore indexes and partitions in SQL Server 2014. Some tables have a lot of updates and we find ourselves with a lot of deleted rows for some partitions. In our tests, a rebuild reduces the table size to a third and improves 10% performance.

Besides checking sys.column_store_row_groups for deleted_rows, is there any other indicator for columnstore indexes fragmentation we can use to identify which partitions/tables should be rebuilt?


texts: MariaDB ALTER TABLE produced a separate .ibd file, how can I re-integrate the data into the shared table space?

texts:
We have a multi-GB InnoDB MariaDB (recently switched from MySQL). For some reason, the value of innodb_file_per_table changed to ON (we wanted only one innodb data file explicitely). So when I ran an ALTER TABLE statement on one of the big tables, it created a separate .ibd file which now occupies 35GB even though I had takes measures to make ~100GB space free in the shared tablespace (ibdata1).

How can I re-integrate the data from this file back into the shared tablespace? I thought maybe DISCARD TABLESPACE would do that, but this just deletes the file and the data in it (I tested it with a sample table of course).


texts:
We just added a node to our replica set. After the node's state switched to SECONDARY, I do
use obs
db.stats()

on both the PRIMARY node and the new SECONDARY.
               PRIMARY    SECONDARY
"collections" :  19          18
"views" :        0            0
"objects" :    70926        70409

Why are numbers of objects and collections different?


texts: SQL Log Shipping - What is the date time format used by transaction log file name?

texts:
Looking at my SQL log shipping file names (following is an example)
Q:\Backups\LogShipping\MyAudit\MyAudit_20201027165400.trn
What date time format is the transaction log expressed in (local military time, UTC time)?


texts: Correct way to restore dbs to new server

texts:
We are migrating from sql server 2008 to 2017 and will do the following

install sql server 2017 on the new machine
make backups of all dbs
on old server

RESTORE DATABASE DbName FROM DISK = 'C:\temp\Live\dbname.bak'
WITH
   MOVE 'DbName' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\DbName.mdf',
   MOVE 'DbName_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\DbName_log.ldf'

I would like to avoid the WITH Part if possible but that requires me to create a blank db first and set its version to 2008 before i can properly restore.

I guess what i am asking is, what is the best way to take a .bak file and restore it as db on a server that doesnt have any databases.

Prefer to do this without attach/detach

I have a large number of databases so I will be creating a script to do all of them at once.


texts: An alternative to querying with multiple JOIN over the same table?

texts:
I have a Postgresql 11 database.
Let's say I have a table called houses. It should have hundreds of thousands of records.

CREATE TABLE houses (
  pkid serial primary key,
  address varchar(255) NOT NULL,
  rent float NOT NULL
);

Now, my houses have features I want to register in the database. As the list of possible features will be quite long (several dozens) and will evolve over time, as I don't want add  a long list of columns to the table houses and change the table constantly with 'ALTER TABLE', I thought of having a separate table for these features :

CREATE TABLE house_features (
   pkid serial primary key,
   house_pkid integer NOT NULL,
   feature_name varchar(255) NOT NULL,
   feature_value varchar(255)
);
CREATE INDEX ON house_features (feature_name, feature_value);
ALTER TABLE house_features ADD CONSTRAINT features_fk FOREIGN KEY (house_pkid) REFERENCES houses (pkid) ON DELETE CASCADE;

In average, each house record will have 10-20 records in the house_features table.

So far, this seems a simple efficient model : I can add as many different features, controlling the possible values of feature_name and feature_value in the upper layers (the applicative layer and/or the GUI). I don't have to alter the database each time the application evolves and I need a new type of feature.

For the example, let's say I have the following features :

feature_name : 'rooftype' with possible feature_value : 'flat' or 'inclined'
feature_name : 'wallcolors' with possible feature_value : 'white', 'beige', 'blue', 'green', etc.. (15 different possible values)
feature_name : 'has_basement' with possible feature_value : 'True' or 'False'.
feature_name : 'number_of_doors' with possible feature_value any integer coded as a string (so '0', '1', '2', ...).
feature_name : 'floor_surface' with possible feature_value any given float coded as a string (e.g.: '155.2')

Obviously, storing booleans, integers and floats as strings is not very efficient and this is also something I will need to take care of. I was thinking of having a separate house_features_XXX table for each XXX type (string, boolean, float, integer).

But that is not even my problem.

My problem is : how do I search for houses that have certain features ?

For the example, let's say I want to search the houses with a basement, white walls and an inclined rooftype.
I could dynamically create in the application layer a query like :

SELECT sq1.* FROM
( SELECT house_pkid FROM house_features WHERE feature_name = 'has_basement' AND feature_value = 'True' ) AS sq1
JOIN
( SELECT house_pkid FROM house_features WHERE feature_name = 'wallcolors' AND feature_value = 'white' ) AS sq2
ON sq1.house_pkid = sq2.house_pkid
JOIN
( SELECT house_pkid FROM house_features WHERE feature_name = 'rooftype' AND feature_value = 'inclined' ) AS sq3
ON sq1.house_pkid = sq3.house_pkid
;

But that seems not so efficient, especially considering that there may be several dozens of conditions on house_features.

Is there a better way to do this ?


texts: Execute Job With Stored Proc Signed with Certificate

texts:
I'm trying to grant permissions to a user to run one job.  I created a certificate, assigned it to the stored proc, and granted the cert role membership to SQLAgentOperatorRole, but when executing the proc the user still gets the error that the job is not found.  I can execute the proc with no errors and the job is started.  What did I miss?

CREATE PROCEDURE spExecuteRefreshJob
AS
BEGIN

EXEC msdb.dbo.sp_start_job @job_name = 'Refresh Job'

END

CREATE CERTIFICATE ExecuteRestoreJobsCert
   ENCRYPTION BY PASSWORD = 'password'
      WITH SUBJECT = 'Cert for Jobs';
GO

ADD SIGNATURE TO dbo.spExecuteRefreshJob
   BY CERTIFICATE ExecuteRestoreJobsCert
    WITH PASSWORD = 'password';
GO

CREATE USER ExecuteRestoreJobsUser
   FROM CERTIFICATE ExecuteRestoreJobsCert;
GO

use msdb
EXECUTE sp_addrolemember
@rolename = 'SQLAgentOperatorRole',
@membername = 'ExecuteRestoreJobsUser'


texts:
I have a table '[Employee]' , and I want to allow access  to certain people only through [View] , using a grant.I am trying first to do it with a single user [User]  and then do it for a collection of users through a role.  I know I can use :

GRANT SELECT ON OBJECT : : [VIEW] TO [User] ;

And I can also create a role [Role] , and use:

GRANT SELECT ON OBJECT : : [VIEW] TO [Role]

My question is whether this permission somehow implicitly excludes all others,
i.e., do the two grants above prevent any of the [Users][Roles] from having any other type of access to '[Employee]' ( Read, Select, Update ), or do I have to expressly prohibit said users, roles from having additional access?

I assume this has to see with membership of [User] and the people in [Role] in other logins or existing user types which may have been granted said permissions either explicitly or as part of role membership? Thanks.


texts: Influxdb and Grafana combine multiple SELECT

texts:
I have some network counters in Influx that I graph with Grafana.

I'm trying to combine stats from a couple ethernet interfaces and combine them to show aggregate bandwidth.

These two separate queries work on their own, but I can't figure out how to write it to combine these two.

SELECT 8 * non_negative_derivative(mean("value"), 1s )
FROM "inoctets"
WHERE "host" = 'myhost1' AND $timeFilter
GROUP BY time(1s) fill(null)

SELECT 8 * non_negative_derivative(mean("value"), 1s )
FROM "inoctets"
WHERE "host" = 'myhost4' AND $timeFilter
GROUP BY time(1s) fill(null)

Edit:  FWIW I'm running Influx 1.1.1 and Grafana 4.1.1


texts: Why can't SQL Server handle into's in 2 parts of a script?

texts:
I noticed some behavior I found weird while editing a co-workers stored procedure:

    create procedure weird_behavior (@switch bit)

    as

    if (@switch = 0)
    begin
        select *
        into #tables
        from sys.tables st
        where left(st.name, 1) in ('a','b','c')
    end

    else if (@switch = 1)
    begin
        select *
        into #tables
        from sys.tables st
        where left(st.name, 1) in ('d','e','f')
    end

    select * from #tables

    --drop procedure weird_behavior

you get the error:

  There is already an object named '#tables' in the database.

This seems odd to me, since only one of the select * into will ever run. I'm forced to rewrite it like so:

    create procedure weird_behavior (@switch bit)

    as

    if (@switch = 0)
    begin
        select *
        into #tables
        from sys.tables st
        where left(st.name, 1) in ('a','b','c')
    end

    else if (@switch = 1)
    begin
        select *
        into #tables01
        from sys.tables st
        where left(st.name, 1) in ('d','e','f')
    end

    select * from #tables

    --drop procedure weird_behavior

This would cause headaches because sometimes the output is in #tables, and sometimes it is in #tables01.

Question: why won't SQL Server let me write this code?


texts:
We need to create a oracle database instance similar to customer environment. What are all the parameters needs to be considered other than similar version of software.

Like Character set, Multibyte Or Single Byte and other Database, Instance, Session level parameters which are to be considered.

What happen if a Dump Successfully imported into a Database instance with different NLS parameters, Will it be having any Side effects?
Is there a standard way of creating a replica of customer instance in our local?


texts: SQL Server Deadlock Graph - Table, Page or Row Lock?

texts:
Is there any way to decipher if a lock in a deadlock graph is Table, Page or Row level? I have all the information I need from the graph, including the Isolation Level, (2) but I really want to know this, too.

Thanks to anyone who can help!


texts:
I'm a developer with apps backed with SQL databases. I want to give my CEO access through BI self-serving tools such as PowerBI or Tableau.

My issue is that the affordable tools all seem to require for someone to define the model in an interactive application leaving no means of automization.

In practice, that means that every time I add some column somewhere I need to fire up some sluggish desktop application, manually add the column in the model there too and then trigger some deployment process. I'm a developer, I need to automate such things in order to not be overwhelmed with complexity.

I've looked at PowerBI's file format, but it's binary. I've looked at both theses product's REST APIs, but all they allow is upload that binary and undocumented file format.

I'm looking for

a way to create a model programmatically with these tools or
affordable alternatives that allow that.

I'm otherwise satisfied with both these tools.

I also had a look at Looker, but that already costs thousands of dollars per month.


texts:
I'm in an unpleasant situation where I have a requirement to audit any queries executed by users via SSMS, SQLCMD, etc. on a SQL Server 2005 database.

The situation is made worse by the inability to upgrade the version of SQL Server, change server permissions, etc.

I'm currently auditting user logons under priveleged accounts (previous question: Audit 'sa' login in SQL Server 2005).

I'm concerned that now I have a requirement to audit what queries are being run that I will need to set up server-side traces to a table, which will introduce a higher performance cost.

I've hit up Google for some recommendations but can't seem to find anything other than 'run server-side traces / profiler'.

What other options (if any) do I have here? Will I have to bite the bullet and set up highly filtered server side traces to capture this data?


texts:
This is a bit of a newbie question I think, but all the answers I've found online are for other questions, so I ask here:

We have a server that we set up on a virtual machine to handle the passing of data from a VPN connection to the main database that end users will access.  This "Processing" server has a few databases for handling deltas and hosting sprocs.  I have recently tried to create a view in one of those databases and found that it cannot query data in any other database, which is not the normal experience that I have had.  On the other server all the databases can see eachother.  I've looked at all of the databases and they only have visibility limited to themselves.

I suppose there is a setting at the server level that isolates the databases, but I don't have the expertise to know what that is.  Can somebody help me?  I'm at an impasse until this is solved.

Thanks,

n8

--edit--
After extended testing I have found that the databases can write to each other and to the other server using 4-part naming, I just cannot execute queries or views that span the same databases that the sprocs are reading from and writing to, even using 4-part naming.  This is quite odd.


texts: Multi-master (NoSQL) database solutions with cloud and no-premise option

texts:
We are currently investigating database options for distributed database (possibly globally distributed) where the data in general is stored in "cloud" (this would not be a special requirement). The special requirement is that a parts of the database must be also available in local branches.
Data added in local branches should be replicated (delay is not a problem) to the cloud and changes made in the cloud should be replicated back to the related database in branches. So a multi-master setup.

Focus in on partition tolerance. It is possible that the local branches have from time to time no network connectivity or a slow network. So the database in the branches should be able to work by themselves and sync data and changes back.

We are long to solve this problem on the database layer. One database for cloud and local branches.

The only database that I came across to solve this problem is CouchDB with providing the "AP" of the CAP theorem.

Are there any database available (I checked options like Elasticsearch, Crate, ArangoDB etc.) that play in the same area ("AP")?

Requirement is that we must be able to install the database on-premise in local branches. For the "cloud" part I could image using a SaaS or do the hosting ourselves.

Options like Postgres or MySQL with related replication options are not of interest here.


texts:
I have an instance which having 25 large databases.

I have set a rebuild_all_indexes job to rebuild all indexes on every database.

Due to database large size and OLTP environment this job takes about more than 3,4 days to its completion.

Can any one help me out to handle this situation?

As job step I used some store procedures calling like this:

exec database1.dbo.SP_JOBS_REBUILD_DB_INDEXES
GO
exec database2.dbo.SP_JOBS_REBUILD_DB_INDEXES
GO
exec database3.dbo.SP_JOBS_REBUILD_DB_INDEXES
GO
exec database4.dbo.SP_JOBS_REBUILD_DB_INDEXES
GO
exec database5.dbo.SP_JOBS_REBUILD_DB_INDEXES
GO
exec database6.dbo.SP_JOBS_REBUILD_DB_INDEXES
GO
exec database7.dbo.SP_JOBS_REBUILD_DB_INDEXES
GO
exec database8.dbo.SP_JOBS_REBUILD_DB_INDEXES
go

...

exec database25.dbo.sp_jobs_rebuild_db_indexes
go


texts: Where do custom Solr schemas go in Riak?

texts:
I have checked these two pages in the Riak docs http://docs.basho.com/riak/kv/2.2.0/developing/usage/search/ http://docs.basho.com/riak/kv/2.2.0/developing/usage/search-schemas/
and they tell how to make a custom schema and how to attach it to an index, but they seem to expect the schema to be in a certain folder to do so. Whenever I try adding the schema through curl like this:

curl -XPUT http://localhost:8098/search/schema/users \
    -H 'Content-Type:application/xml' \
    --data-binary @user_schema.xml

it throws an error saying it isn't found. I've tried inserting the path too. I've found solutions online saying that it's somewhere in the data folder, but those were back in 2015 and I'm using version 2.2 of Riak now. Has anyone else recently had success with adding a custom solr schema to Riak?

PS - I'm using ubuntu/trusty64 in a vm


texts:
I have to forward my logs to rsyslog via UDP, but due to the volatile nature of UDP and rsyslog I would like to also log to a file. However, AFAICT MongoDB only allows one target for its logs:

systemLog:
  destination: file
  logAppend: true
  path: /var/log/mongodb/mongod.log
  #destination: syslog

I'm using the latest mongodb 4.

Unless I'm mistaken it doesn't even support remote syslog: https://docs.mongodb.com/manual/reference/configuration-options/#systemLog.destination

How can I log to something file-alike and a remote syslog using mongodb?


texts: Lost postgreSQL password in Windows installation

texts:
I have postgresql 9.2 installed on a windows XP machine. I have lost the postgres password.

I looked at answers given to a similar question asked 6 months ago. The answer given there required the editing of a file pg_hba.conf however I cannot find that file anywhere. Windows search doesn't find it.

I have tried uninstalling the postgreSQL installation but that seems to leave behind the password and security settings as on installing again the step to set the postgres password is missed out.

Help please?


texts:
I've got the following table

And I have to change the order of the rows in an unpredictable way for example:

Set the position of the row with id = 1 after the row with id = 3 and preserve the ordering of the other rows.
Add new row at position 3 right after row with id = 2.
Delete row from
position 4

What is the best solution for this problem?


texts: how to get avg() size by day using two decimal places

texts:
does this simple query result in grouping the attachments by day and giving me the total number of daily attachments as well as the average size?

the only table i am querying is attachmentdetail (email attachments)

[attachmentdetail]
    AttachmentId     int
    Name             varchar
    Size             int
    IsInline     bit
    InsertedDatetime datetime

select
    count(*) as [Tally],
    avg( cast(size as decimal)/1024/1024) as avgSize,
    DateAdd(dd, DateDiff(dd, 0, InsertedDatetime), 0) As [date_only]
from attachmentdetail
where isinline <>1
group by DateAdd(dd, DateDiff(dd, 0, InsertedDatetime), 0)
order by [date_only] desc

also how to i keep the values i get from my avg() to 2 decimal places?


texts: MongoDB replication: going into maintenance mode with 10333 other maintenance mode tasks in progress

texts:
I have a MongoDB instance where resync is required.

2016-11-07T11:59:23.330+0000 I REPL     [ReplicationExecutor] syncing from: x.x.x.x:27017
2016-11-07T11:59:23.354+0000 W REPL     [rsBackgroundSync] we are too stale to use x.x.x.x:27017 as a sync source
2016-11-07T11:59:23.354+0000 I REPL     [ReplicationExecutor] could not find member to sync from
2016-11-07T11:59:23.354+0000 E REPL     [rsBackgroundSync] too stale to catch up -- entering maintenance mode
2016-11-07T11:59:23.354+0000 I REPL     [rsBackgroundSync] our last optime : (term: 20, timestamp: Oct  4 07:41:29:1)
2016-11-07T11:59:23.354+0000 I REPL     [rsBackgroundSync] oldest available is (term: 20, timestamp: Oct 17 02:13:33:5)
2016-11-07T11:59:23.354+0000 I REPL     [rsBackgroundSync] See http://dochub.mongodb.org/core/resyncingaverystalereplicasetmember
2016-11-07T11:59:23.355+0000 I REPL     [ReplicationExecutor] going into maintenance mode with 10333 other maintenance mode tasks in progress

what does this line mean?

[ReplicationExecutor] going into maintenance mode with 10333 other maintenance mode tasks in progress

what are maintenance mode tasks? No docs from MongoDB. Why there are 10333 queued? Howto see them (list)? With a search engine I found also log entries with with 0 other maintenance mode tasks in progress


texts: Converting outer apply with Correlated subquery in SQL Server to SQLite

texts:
I need to convert a query in SQL Server to SQlite and I have replaced the outer apply to left outer join, but how to limit the subquery to return only 1 in this situation?  And how to pass the reference value from outer table into sub query?

I have highlight the things that i am not able to convert it into Sqlite, can anybody please give some advise ?

SQL Server:

SELECT  a.id,
        a.CodeValue ,
        a.TotalAmount ,
        InvoiceRevised.RevisedInvoiceId,
        InvoiceRevised.BillNumber AS RevisedInvoice,
        patientlookup.RegistrationNumber AS RegistrationNumber,
        patientlookup.PatientName AS PatientName,
FROM dbo.Invoices a
OUTER APPLY (
SELECT TOP 1 dbo.Invoices.Id AS RevisedInvoiceId,CodeValue AS InvoiceNumber,
    dbo.SCSInvoiceRels.CreatedOn AS InvoiceTime,
    dbo.Invoices.CreatedBy AS InvoiceCreatedBy,
    dbo.Invoices.TotalAmount,dbo.Invoices.Discount,
    dbo.Invoices.RoundAmount
    FROM Invoices
    INNER JOIN dbo.SCSInvoiceRels ON dbo.Invoices.Id = dbo.SCSInvoiceRels.InvoiceId
        AND dbo.SCSInvoiceRels.RecordStatus = 1 AND dbo.Invoices.RecordStatus = 1 AND  dbo.SCSInvoiceRels.SCDetailId IN (
            SELECT SCDetailId FROM dbo.SCSInvoiceRels WHERE InvoiceId = a.id)
) InvoiceRevised
OUTER APPLY (
SELECT TOP 1 pr.CodeValue AS RegistrationNumber,pa.Name AS PatientName FROM dbo.PatientRegs pr
INNER JOIN dbo.Patients pa ON pa.id = pr.PatientId
INNER JOIN dbo.SCDetails SC ON pr.id = SC.RegId
INNER JOIN dbo.SCSInvoiceRels SCb ON SC.id = SCb.SCDetailId
WHERE SCb.InvoiceId = a.Id
) patientlookup
WHERE RecordStatus = 0 AND  UpdateOn IS NOT NULL

SQLite:

SELECT  a.id,
        a.CodeValue ,
        a.TotalAmount ,
        InvoiceRevised.RevisedInvoiceId,
        InvoiceRevised.BillNumber AS RevisedInvoice,
        patientlookup.RegistrationNumber AS RegistrationNumber,
        patientlookup.PatientName AS PatientName,
FROM dbo.Invoices a
Left outer join (
SELECT dbo.Invoices.Id AS RevisedInvoiceId,CodeValue AS InvoiceNumber,
    dbo.SCSInvoiceRels.CreatedOn AS InvoiceTime,
    dbo.Invoices.CreatedBy AS InvoiceCreatedBy,
    dbo.Invoices.TotalAmount,dbo.Invoices.Discount,
    dbo.Invoices.RoundAmount
    FROM Invoices
    INNER JOIN dbo.SCSInvoiceRels ON dbo.Invoices.Id = dbo.SCSInvoiceRels.InvoiceId
        AND dbo.SCSInvoiceRels.RecordStatus = 1 AND dbo.Invoices.RecordStatus = 1 AND  dbo.SCSInvoiceRels.SCDetailId IN (
            SELECT SCDetailId FROM dbo.SCSInvoiceRels WHERE InvoiceId = a.id)
) InvoiceRevised
Left outer join(
SELECT pr.CodeValue AS RegistrationNumber,
       pa.Name AS PatientName,
       SCb.InvoiceId as InvoiceId
FROM dbo.PatientRegs pr
INNER JOIN dbo.Patients pa ON pa.id = pr.PatientId
INNER JOIN dbo.SCDetails SC ON pr.id = SC.RegId
INNER JOIN dbo.SCSInvoiceRels SCb ON SC.id = SCb.SCDetailId
) patientlookup on patientlookup.InvoiceId = a.Id
WHERE RecordStatus = 0 AND  UpdateOn IS NOT NULL


texts: Designing a PostgreSQL database for high-volume updates

texts:
I am designing a database for a system that has to handle lots of writes operations (99%+ are updates, sometimes inserts) but at all times it has to provide fast reads. At the moment selects take a lot of time to complete.

There are around 20 processes (each on its own server) that communicate with the database server at the same time. Each process has to update rows and in order to avoid multiple processes updating the same row in parallel, we use a particular column queued which indicates if a row has already been queued by one process. In more detail:

A process requests data from the database with a query like this:

WITH selected AS
    (SELECT username FROM profiles
        WHERE queued = false FOR UPDATE SKIP LOCKED LIMIT %(n)s)
UPDATE profiles
    SET
        queued = true
    WHERE username IN (SELECT username FROM selected)
RETURNING username;

After it has finishing updating each profile we run the following query

INSERT INTO profiles (
    ...
) values (
    ...
)
ON CONFLICT (username) DO UPDATE
    SET
        ...
;

Presently we have around 1.7-1.9M updates per day. As mentioned, selects are really slow when all these processes are updating rows. The database server has 4 cores and 25GB of RAM and runs PostgreSQL 9.6 with the following parameters:

max_connections = 100
shared_buffers = 2560MB
effective_cache_size = 7680MB
maintenance_work_mem = 640MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 13107kB
min_wal_size = 2GB
max_wal_size = 4GB
max_worker_processes = 4
max_parallel_workers_per_gather = 2

With all these updates I guess I'll have to increase the autovacuum frequency, is that right? I am ok penalizing a bit the updates if in turn that guarantees fast reads.


texts: Is it safe to disable sync_binlogs, innodb_flush_log_at_trx_commit and innodb_doublewrite on a site-redundant galera cluster?

texts:
On a site-redundant galera cluster, a commit should only return after a quorum of database nodes have accepted the transaction.  If one database node goes down, all commits will be preserved by the rest of the cluster, and the downed database node will get in sync with the rest of the cluster when starting up the database again.  If the database should have become corrupt, one can always nuke the mysql data dir and start with an empty database, it will eventually catch up with the rest of the cluster.  Hence it sounds like we could tweak performance by turning off options for ensuring local ACID-compliance.

So the question here is really ... "What could possibly go wrong?"

:-)

Based on the comments, I'll give some information about our particular setup:

The cluster consists of three nodes.  Two of them are actively used in production, one of them more heavily used than the other.  The third node is only used for quorum and backup purposes.
Site-redundant means that the nodes are in different server centers.  I find it hard to think of anything that would cause two nodes to go down simultaneously - except a serious mysql bug, how likely is that?  Admittedly, two of the nodes are situated less than 10 km apart (backup/quorum node is separated by several hundred km plus a national border).  A medium-sized nuclear bomb could potentially take out two of the nodes simultaneously ... then again, "we have problems with the database" is probably the least of our concerns in such a scenario.  Could a solar storm possibly take out both or all of the servers simultaneously?
Our performance problem is primary due to the write cache on our SAN going full from time to time.  We're working on mitigating this issue, but we can never guarantee that it won't happen again.  Every now and then we experience "hicups" where we have transactions waiting for some 10-30 seconds.
In this particular setup, a 30s delay may actually be a matter of life and death.  Well, most likely not, but if the customer perceives it that way, then it's bad enough.  Reasonably low latency and quick recovery should the whole cluster fall down is the most pressing priority.  Losing some few transactions may be bad enough, but it's not a matter of life and death.
The performance problem we're seeing is write-transactions being stuck in "wsrep in pre-commit"-state.  This is not a flow-control issue, it's only one node having the problems.  I did a bit of research on it, apparently it's so that all local write queries are waiting for locks while the node is writing remote changesets to the database.  This problem should be fixed in galera 4, but upgrading is currently not an option.
Our performance problem is only on one node, so it's only on one node I'm considering to turn off those things.  I would add to our documentation that whatever happens, said node should never be used for bootstrapping the cluster.
We're not doing sharding, and we do not intend to do any sharding.  Except for those hic-ups we don't have any performance problems.


texts: pt-table-checksum says there is a difference but pt-table-sync with --print says nothing

texts:
I am using pt-table-checksum to ensure master-slave is synchronized.

When I run pt-table-checksum, I see some differences on employee_profile.trigger_log but when I use pt-table-sync with --print, I see nothing.

I resolved some differences on some tables with pt-table-sync but when I re-test with pt-table-checksum, this tool still says there is a difference.

So what is the problem with pt-table-checksum ? Is is accuracy ?

[UPDATE]

I use

pt-table-checksum -h <masterip> -u <the_user> -p '<passwd of the user>' --recursion-method dsn=h=<slaveip>,P=3306,u=<the_userr>,p=<passwd of the user>,D=check_db,t=dsns --nocheck-binlog-format --set-vars innodb_lock_wait_timeout=50 > ~/stats_check_db_<slaveip>.txt

After that, I run command to filter:

cat ~/stats_check_db_<slaveip>.txt | awk '{print "*"$3"*" " -- " $8}' | grep -Fv  "*0*"

This is output:

*DIFFS* -- TABLE
*1* -- mysql.user
*1* -- employee_profile.entry
*1* -- employee_profile.ip
*1* -- employee_profile.thread

When I run pt-table-sync:

pt-table-sync  --print h=<slaveip>,P=3306,u=<the user>,p=<passwd of the user> --sync-to-master --databases employee_profile --tables entry

I see nothing. After a while, when I re-check with pt-table-checksum, I don't see difference on employee_profile.entry

For a resolved table, I still see some differences on next re-check. These are disappeared from result of pt-table-checksum on the third checksum.

[UPDATE 2]

I am using percona toolkit version 2.2.13 - lastest version on http://www.percona.com/

I reviewed all implementations of db server that I did checksum. These are old implementation of mysql or mariadb that someone built them.

I see the problem just appear when the implementation between master and slave is different such as master is MariaDB 5.5.34 and slave is MariaDB 5.5.37 (I see false positive error: Cannot nibble table because MySQL chose no index instead of the PRIMARY index - just ignore it and then execute pt-table-sync) or master is mysql 5.5.35 and slave is mysql 5.5.37 (I see pt-table-checksum says there is a difference but pt-table-sync --print says nothing - just re-test for sure).

I think it is a reason for false positive errors.

Thank you so much !


texts:
I am tasked with making large PostgreSQL imports to master instance, and then replicate it across the nodes as read-only data.

Still, the CMS system has to be able to write data and replicate across all of them.

I can use the latest version of PostgreSQL server. There are just few tables in single database.

I havent been working with PostgreSQL for 5 years, so I was wondering if this is doable the same way as with MySQL, that I have the DM and so on?

The thing is, that I need to run it in two data centres, so ideally it would be close to:

2 master PostgreSQL instances, which are doing writes from CMS and imports
10 slave PostgreSQL instances doing only reads.

Is there any preferred approach in doing this for PostgreSQL?


texts: I have a column with a mixture of letters and numbers, and I need to round only the cells that contain numbers using SQL. I currently have my data saved as strings only, and when I try to use a round function, my data containing no numbers gets lost. Is there any way around this issue?


texts:
My SQL Server 2008 is located on a remote computer so I am trying to create an ODBC connection using SQL Server Authentication.

My problem is that the way i normally connect to my SQL Server is by doing a remote desktop connection and then I just connect using windows authentication so I don't know what my SQL Server Username and Password are.

Is there anyway I can find out what my Sql Server Authentication username is so I can maybe reset my password?

All I am trying to do is to create an ODBC connection usnig SQL Server Authentication since I can't use Windows Authentication since the SQL Server is on a remote Windows Server computer (or can I?)


texts: Store row count or use query to calculate on Primary Key

texts:
I am curious to know if I should store the row count for an instance number of friends of a user into a separate table or if I should use a query like

SELECT COUNT(user_id) FROM user_friend WHERE user_id = 20;

where user_id is AUTO_INCREMENTED PRIMARY_KEY

Is querying the COUNT each time slower than storing the row account in a separate table?

In my opinion, I shouldn't store the row counts separately as this will lead to Denormalization and also some other factors like having to update the row count again and again and fetching the records by joining the tables unnecessarily.


texts:
I am trying to create a SQL Server linked server from on premises to SQL Server instance on Windows in Amazon EC2 (2 different domains) and getting:

  Login timeout expired.

I am using sa account and password. It is the same on both instances.
I was wondering if it's even possible do to so? If yes, what could be the issue here?

Trying to find docs online but not able to do so.
Also, can the linked server be created from AWS EC2 to on premises and vice versa?


texts: How to check which table is called which trigger in postgresql?

texts:
I have ten tables and five triggers in my database.
I don't know the trigger name, but I need to edit the trigger.
So I need to know which table is called to which trigger?
Using,

SELECT tgname
FROM pg_trigger;

we can print all the trigger name. But it did not show which table is called to which trigger.
Can anyone explain to me?


texts: MySQL - Delete with IN Clause taking more time

texts:
In MySQL 5.6 I have around 60 million rows. I wanted to remove rows based on Id. Here Id is the primary key.

One strange behavior I had today.

delete from tbl where id=1;

Its executed in milliseconds.

Then I did this.

CREATE TEMPORARY TABLE DELETE_ID (ID int);
Insert into DELETE_ID select id from (subery1(subquery2(subquery3)));

The DELETE_ID has around 150 ID.

Delete from tbl where id in (select id from DELETE_ID);

Its taking more than 30mins, but deleting a single row.

There were no locks or deadlock.

In innodb_trx the status was sometimes fetching rows sometimes unlocking rows.

Can anyone help me to understand why this is happening?


texts: Mongodb - Security weakness while using --authenticationDatabase admin?

texts:
I was trying to create a new Mongodb instance based on my old Mongo Database. In my current DB I have an user and password configured. So, I can access my database using:

$ mongo --host $MONGO_HOST -u $MONGO_USER -p $MONGO_PASSWORD --authenticationDatabase admin

This works perfectly. But accidentally, I typed:

$ mongo --host $MONGO_HOST --authenticationDatabase admin

And this is when the problem came in! The MongoDatabase authorized my connection for the database!  The security.authorization is set as true in my mongod.conf. Is this a weakness in MongoDB? Is there any method to always force the authentication?

EDIT 1: Current configuration for mongo:

{
    "config" : "/etc/mongod.conf",
    "net" : {
        "bindIp" : "0.0.0.0",
        "port" : 27017
    },
    "security" : {
        "authorization" : "enabled"
    },
    "storage" : {
        "dbPath" : "/var/lib/mongodb",
        "journal" : {
            "enabled" : true
        }
    },
    "systemLog" : {
        "destination" : "file",
        "logAppend" : true,
        "path" : "/var/log/mongodb/mongod.log",
        "quiet" : true
    }
}


texts: Index Optimization Error in SQL Server 2012

texts:
I have an Index optimization job set on the database SWHSJ having two data files with unrestricted growth in the J drive with 174GB free, as you can see below:

╔═══════════╦════════╦══════════════════════════╦═══════════╦══════════════╦═══════════════╦═══════════╦═════════════╗
║   name    ║ fileid ║         filename         ║ filegroup ║     size     ║    maxsize    ║  growth   ║   usage     ║
╠═══════════╬════════╬══════════════════════════╬═══════════╬══════════════╬═══════════════╬═══════════╬═════════════╣
║ SWHSJ     ║      1 ║ E:\sql_dat\SWHSJ.mdf     ║ PRIMARY   ║ 2490368 KB   ║ Unlimited     ║ 0 KB      ║ data only   ║
║ SWHSJ_log ║      2 ║ D:\sql_log\SWHSJ_log.LDF ║ NULL      ║ 266816 KB    ║ 2147483648 KB ║ 10240 KB  ║ log only    ║
║ SWHSJ_2   ║      3 ║ G:\sql_dat\SWHSJ_2.ndf   ║ PRIMARY   ║ 24117248 KB  ║ Unlimited     ║ 0 KB      ║ data only   ║
║ SWHSJ_3   ║      4 ║ H:\sql_dat\SWHSJ_3.ndf   ║ PRIMARY   ║ 59940864 KB  ║ Unlimited     ║ 0 KB      ║ data only   ║
║ SWHSJ_4   ║      5 ║ J:\sql_dat\SWHSJ_4.ndf   ║ PRIMARY   ║ 104857600 KB ║ Unlimited     ║ 262144 KB ║ data only   ║
║ SWHSJ_5   ║      6 ║ J:\sql_dat\SWHSJ_5.ndf   ║ PRIMARY   ║ 10485760 KB  ║ Unlimited     ║ 10%       ║ data only   ║
╚═══════════╩════════╩══════════════════════════╩═══════════╩══════════════╩═══════════════╩═══════════╩═════════════╝

Even though data files have enough space to grow, still my Index optimisation job keeps on failing with the below error:

  Command: ALTER INDEX [IX_JLog_MessageType] ON [SWHSJ].[dbo].[JlLog]
  REORGANIZE WITH (LOB_COMPACTION = ON) Comment: ObjectType: Table,
  IndexType: NonClustered, ImageText: N/A, NewLOB: No, FileStream: N/A,
  ColumnStore: No, AllowPageLocks: Yes, PageCount: 706445,
  Fragmentation: 5.42321 Msg 50000, Level 16, State 1, Server
  ServerName, Procedure CommandExecute, Line 152 Msg 1105, Could not
  allocate space for object 'dbo.JLog'.'IX_JLog_MessageType' in database
  'SWHSJ' because the 'PRIMARY' filegroup is full.  Create disk space by
  deleting unneeded files, dropping objects in the filegroup, adding
  additional files to the filegroup, or setting autogrowth on for
  existing files in the filegroup. Outcome: Failed

This job was working fine till 11th march, 2015. Please suggest.


texts: Measure the size of a PostgreSQL table row

texts:
I have a PostgreSQL table. select * is very slow whereas select id is nice and quick. I think it may be that the size of the row is very large and it's taking a while to transport, or it may be some other factor.
I need all of the fields (or nearly all of them), so selecting just a subset isn't a quick fix. Selecting the fields that I want is still slow.
Here's my table schema minus the names:
integer                  | not null default nextval('core_page_id_seq'::regclass)
character varying(255)   | not null
character varying(64)    | not null
text                     | default '{}'::text
character varying(255)   |
integer                  | not null default 0
text                     | default '{}'::text
text                     |
timestamp with time zone |
integer                  |
timestamp with time zone |
integer                  |

The size of the text field may be any size. But still, no more than a few kilobytes in the worst case.
Questions

Is there anything about this that screams 'crazy inefficient'?
Is there a way to measure page size at the Postgres command-line to help me debug this?


texts: Differences Between Two Different Create Index Commands

texts:
Are there differences between these two scripts?  Or would all of the extra tokens/attributes (ie: NONCLUSTERED, WITH..., etc...) for the 1st script be defaults in SQL Server 2008 for the 2nd script?

1st script:

CREATE UNIQUE NONCLUSTERED INDEX [DEID_MAP_IDX1] ON [dbo].[DEID_MAP]
(
    [VISIT_NUM] ASC
) WITH
  (PAD_INDEX  = OFF,
   STATISTICS_NORECOMPUTE  = OFF,
   IGNORE_DUP_KEY  = OFF,
   ALLOW_ROW_LOCKS = ON,
   ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

2nd script:

CREATE UNIQUE INDEX [DEID_MAP_IDX1] ON [DEID_MAP]
(
    [VISIT_NUM] ASC
);

FYI: there is ETL code that drops the index with this script before doing a bulk data load, and then finally applying re-creating the index with the 2nd script above.

DROP INDEX [deid_map_idx1] ON [deid_map] WITH ( ONLINE = OFF );

EDIT:

After applying the simple index above (2nd script), I got this:

SQL Server Management Studio > expanded table > expanded folder "Indexes" > right click index > selected "Script Index as.." > selected "CREATE TO" > selected "New Query Editor Window" > got the following.

CREATE UNIQUE NONCLUSTERED INDEX [DEID_MAP_IDX1] ON [dbo].[DEID_MAP]
(
    [VISIT_NUM] ASC
) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

So it appears that these are added in addition by running the simple statement:

SORT_IN_TEMPDB = OFF

DROP_EXISTING = OFF

ONLINE = OFF


texts: How to get all messages raised when a statement fails

texts:
Executing the below query will generate two error messages

ALTER TABLE [MFClassProperty] DROP CONSTRAINT [FK_MFClassProperty_MFProperty]

Error messages are:

  Msg 3728, Level 16, State 1, Line 3 'FK_MFClassProperty_MFProperty' is
  not a constraint. Msg 3727, Level 16, State 0, Line 3 Could not drop
  constraint. See previous errors.

If I use a try..catch block, then it shows only the last message.

Why isn't it moving to the catch block on the first error?
Is there any way to get all error messages?

This is sample situation.


texts: How to SELECT cells from mysql for a HTML table?

texts:
I have data for individual cells of a HTML (or excel) table as

CREATE TABLE table_cols
(
column_id smallint(5) unsigned NOT NULL AUTO_INCREMENT,
column_number tinyint(3) unsigned,
title varchar(255),
PRIMARY KEY(column_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

CREATE TABLE table_rows
(
row_id smallint(5) unsigned NOT NULL AUTO_INCREMENT,
row_number tinyint(3) unsigned,
title varchar(255),
PRIMARY KEY(row_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

CREATE TABLE table_cells
(
row_id smallint(5) unsigned,
column_id smallint(5) unsigned,
content varchar(255),
PRIMARY KEY(row_id,column_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

What is the best SELECT query to catch data to be displayed in a HTML table as

 columns titles

row title	 cells

I don't expert to make the entire table by one query. I get the results by  PHP.


texts: Optimizing and maintaining test databases for a web development team

texts: Right now when we test, we mysqldump from the production database, load it into the test database server, and then use scripts to scrub sensitive data. As the database is getting bigger, this process is taking longer, there becomes a risk where as the database is still being scrubbed, developers can connect to the test database still and look at sensitive data. Is there a way to scrub and then load without adding too many steps? It already takes about half hour to reload the test database. The objective here is the make sure the sensitive data is safe at all times and somehow increase the speed of reloading the test environment's database.


texts: SQL Server : not allocating all memory that it has on disposal

texts:
Yesterday, we moved from dedicated server to amazon AWS. And since then we see strange thing. Our SQL Server is not using more than 2.8GB of RAM, which is strange because on old server it always load everything to RAM. Our database is 289GB large, we have so many indexes etc. We are running on Microsoft SQL Server Standard edition, which should give us access to 128GB RAM for database usage.
What could it be the reason? Anyone experienced similar issue? Beside this, database seems to be working fine at the moment, although it had issue 1 time during 24 hours where it became completely unresponsive, so we restarted the SQL Server at that time. But something is strange, something is not right, because that ram should be filled to maximum as it always does.


texts:
I have 2 SQL server instances, on different servers.
One is Production instance and gets lots of reads and writes daily.
Another one is for external applications to use a copy of few of the tables from production sql instance.
At the moment we have a SSIS job which does compare and update once in a day from production sql to the external sql server.
We want to increase the frequency of the data being replicated, so that it is more or less synchronous on both servers.
What would be a better approach? increasing the frequency of the SSIS package or creating replication or using log shipping?
Any answer is really appreciated.
Thanks.


texts:
in this table Customer is actually storing CUSID (i have pleased the caption customer and made it display the name to which the CUSID corresponds to)
now in the CUSnum field i want it to automatically pic the number CUSID of which was entered into the previous field (if he customer doesnt exist it should all to add the number of unregistered customer)

i came up with the following SQL
SELECT [Customer].[CUSContact] FROM Customer WHERE [Sale].[CUSID] = [Customer].[CUSID];

but for some reason it is not automatically catching the CUSID from the sale table bu asking for it to be manually inputted ! please help !


texts: What are valid usage scenarios for HEAP tables?

texts:
I am currently doing some data imports into a legacy system and discovered that this system does not use a single clustered index. A quick Google search introduced me to the concept of HEAP tables and now I am curious in what usage scenarios a HEAP table should be preferred over a clustered table?

As far as I understood a HEAP table would only be useful for audit tables and/or where inserts happen far more often than selects. It would save disk space and disk I/O since there is no clustered index to maintain and the additional fragmentation wouldn’t be a problem because of the very rare reads.


texts: design database for electrical meter data?

texts:
Hi just wondering how to I have .csv "Time 'datetime' Voltage 'double'"file that record Voltage usage from electrical meter each every minutes and database to record the data just want to normalize the data  should  be in database ? should I have Meters table,Times table, usage or meters table  Voltages table (double, time stamp)

csv file data

 meterID  timeDate         Voltages
    M1    1/12/2014 5:58:00  117.5
    M1    1/12/2014 5:59:00  117.6
    M1    1/12/2014 6:00:00  117.5
    M1    1/12/2014 6:01:00  117.3


texts:
I have a Sql Job on my server that is triggered nightly from a remote server (once it has completed its backups).

I have successfully configured the job to notify me when it fails, but I would like to set up an alert to notify me when a job has not been triggered. i.e. when the remote server fails to trigger the job on my end.

Is this possible? I've had a look at the settings, and all the alerts, etc, are all concerned with when the job fails/succeeds.

I could set the job to alert on completion (regardless of status), and I was doing that to start with (and I did that to start with), but I would rather not get continuous emails (after all, I'd probably just start ignoring them).


texts: How to troubleshoot "client driver has sent multiple requests while the session is in single-user mode"

texts:
I'm working with a developer who is struggling to stop the following error:

  The server will drop the connection, because the client driver has sent multiple requests while the session is in single-user mode. This error occurs when a client sends a request to reset the connection while there are batches still running in the session, or when the client sends a request while the session is resetting a connection. Please contact the client driver vendor.

When this error occurs, a typical outcome is a session that is holding locks, but has no SQL running (no row in sys.dm_exec_requests for the session_id), and causes blocking until it is killed. The cause of the blocking is straightforward, but how to stop the error from occurring is not.

Some facts about the problem:

The client is using Entity Framework (yes, I know...), with MARS enabled (yes, I know...)
SQL Server version is 10.00.5844 (SQL Server 2008 SP3 CU12). They probably won't upgrade major versions without extensive testing, but applying a newer CU is viable.
The client program shows up in sys.dm_exec_connections as .Net SqlClient Data Provider
The open connection appears to definitely have MARS enabled, as net_transport in sys.dm_exec_connections shows Session.
I'm told the application's connection string has asynchronous processing enabled, per Matt Neerincx's comment in this thread (I don't have access to the application servers to confirm this myself):

  If you set Async=true on the connection string it should prevent this problem.   If you are using MARS and using the same connection on multiple concurrent threads what can happen is the call to reset the connection can get slightly delayed and trigger this error.  If you set Async=true we perform additional locking in the client driver to prevent this from happening.

Further information about this error is scarce. It appears to suggest that the client's program is calling sp_reset_connection while a batch is still running. I could set up a trace to confirm this, but I'd have to record quite a lot of unrelated activity, and the problem is only happening once every few days. Further, I'm not sure that the developer would be able to take advantage of this evidence in order to solve the problem.

Is there any other technique I can use as a DBA or sysadmin to troubleshoot this further, or anything I can suggest to the developer that might provide him with useful information or make the problem less likely to happen?


texts: Dealing with varying fill factors in a SQL Server 2014 environment

texts:
I've recently become a DBA of sorts for our company's ERP system. I've been making small changes to the server & improvements have been steady. One area I'm concerned with is the index fill factor. I found a YouTube video Brent Ozar did on defragmenting & fill factor of indexes which was most insightful. All my fill factors are currently 100%, but I know that this isn't good for all my indexes. I found this article/script about determining a better fill factor for certain indexes. If this article is still applicable, this brings me to my question:

I'm using the Ola Hallengren scripts to help keep my indexes tidy, but I don't see a way to leave the current fill factor alone. It will rebuild/etc with either the system setting or the specified one but not the current one if it is different.

Does anyone have suggestions/practices they use if an index needs to be rebuild with a specific/different fill factor?


texts: Event 17058 - initerrlog: Could not open error log file (SQL Server)

texts:
I'm new to SQL Server and encountering an error when running sqlservr.exe
(Microsoft® SQL Server® 2008 R2 SP2 - Express Edition)

  Error 17058, Severity: 16, State: 1.
  initerrlog: Could not open error log file ''.
  Operating System Error = 3 (The system cannot find the path specified.).

Here are my startup parameters:

-dc:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf;
-ec:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG;
-lc:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

I've also made sure that all users have full control access and removed read-only permission from folders

Thanks!


texts: Dump PostgreSQL schema with foreign keys and referenced data

texts:
The official PostgreSQL documentation states that pg_dump can use the --schema=SCHEMA option to selectively dump a given schema.

However according to the same documentation (read Note on --schema=SCHEMA) foreign-keys to objects outside the selected schema will be included in the dump, but not the tables which they refer to.

With that said, I would like to know how to dump a given schema in a way that it includes all the needed tables regardless of the schema they are in and that this is done in a way that is safe/easy to restore the dump.

Additionally, are there any tools available that do it already?

Thanks


texts: Unable to connect on premise sql server from Sql Azure db

texts:
I have two SQL Server databases:

On-Premise : See the image below.

We have a static IP associated with this server in order to access remotely from other development machines. Machine is deployed in Location A and is accessible from Location B or C which are geographically separated.

To connect using SSMS, from Location B or C we are using IP Address/UserName/Password. Neither instance name is required nor FQDN. Only IP.
On Azure : Another database is on Azure. Hosted as a Service there. No VM. Its migrated from one of our On-Premise database, which was earlier accessible from #1. We are planned to move our all database to azure , but as of now, we need a cross communication for few months, until we migrate completely.

Problem

We are using a linked server in our queries, and they are failing as Azure does not support linked servers. So in order to access on premise from Azure db we are using External/Table and data-sources but still its giving me connectivity issues.

Error

  Msg 46823, Level 16, State 1, Line 1
  Error retrieving data from XXX.XXX.157.236.TableBackup.
  The underlying error message received was: 'A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.
  (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)'.

Kindly help me fixing this issue.


texts: Issue with composite type in an UPSERT function

texts:
I have a function in PostgreSQL 9.1 called fun_test. It has a composite type as input parameter and I keep getting a casting error when I call it.

CREATE OR REPLACE FUNCTION netcen.fun_test(myobj netcen.testobj)
  RETURNS boolean AS
$BODY$
DECLARE
tmp_code smallint;
cur_member refcursor;
BEGIN
-- Check if the member exists first
OPEN cur_member FOR
EXECUTE 'SELECT testkey FROM netcen.test WHERE testkey=' || myobj.testkey ;
FETCH cur_member INTO tmp_code;
CLOSE cur_member;
CASE tmp_code
    WHEN COALESCE(tmp_code,0)=0 THEN
    -- Record not found INSERT a new record
    -- will skip user defined validation for now
    insert into netcen.test values(myobj.testkey,
    myobj.tes,
    myobj.testname);

    ELSE
    -- Record found UPDATE the record
    update netcen.test set
    test=myobj.test,
    testname=myobj.testname  WHERE testkey=myobj.testkey;

END CASE;
END;$BODY$
  LANGUAGE plpgsql;

Below is the type testobj

CREATE TYPE netcen.testobj AS
   (testkey smallint,
    tes text,
    testname text);

When I call the function:

SELECT netcen.fun_test('(3,khaendra@me.com,khaendra)':: netcen.testobj);

.. I get the following error message:

ERROR:  operator does not exist: smallint = boolean
LINE 1: SELECT "__Case__Variable_8__" IN (COALESCE(tmp_code,0)=0)
                                      ^
HINT:  No operator matches the given name and argument type(s).
       You might need to add explicit type casts.
QUERY:  SELECT "__Case__Variable_8__" IN (COALESCE(tmp_code,0)=0)
CONTEXT:  PL/pgSQL function "fun_test" line 11 at CASE

Where should I cast?
Definition of the table netcen.test:

CREATE TABLE netcen.test    (
  testkey smallint NOT NULL DEFAULT 0,
  tes netcen.dom_email_validation,
  testname text,
  CONSTRAINT key PRIMARY KEY (testkey)
)

@ Erwin, thanks for the links. I read and have modified my function to this, please go through it and tell me if it can work well with several clients calling the same function concurrently?

CREATE OR REPLACE FUNCTION netcen.fun_test_modified(myobj netcen.test)
  RETURNS boolean AS
$BODY$
DECLARE
myoutput boolean :=false;
BEGIN
    update netcen.test set
    tes=myobj.tes,
    testname=myobj.testname  WHERE testkey=myobj.testkey;
IF FOUND THEN
        myoutput:= TRUE;
        RETURN myoutput;
    END IF;
    BEGIN
        INSERT INTO netcen.test values(myobj.testkey,
    myobj.tes,
    myobj.testname);
    myoutput:= TRUE;
    EXCEPTION WHEN OTHERS THEN
        update netcen.test set
    tes=myobj.tes,
    testname=myobj.testname  WHERE testkey=myobj.testkey;
    myoutput:= TRUE;
    END;
    RETURN myoutput;
END;
$BODY$ LANGUAGE plpgsql;

I have done away with the type test and just used the table test! I didn't know that could work!


texts:
I'm not very experienced in database design so bear with me.

I've taken over a ruby/rails app. with a connected MySQL/innodb database. As I'm updating the server to the latest ruby/rails version I also wanted to look at the DB structure. My main concern is a specific database design decision which makes it very hacky to use with rails and I wanted to get some external input on this.

We are tracking spatial data for various systems from customers every x minutes which results in a lot of data. We don't have that data in one big "positions" table but split into a table for each system and each year. So e.g. for a system with id=1 and in the current year we have a "positions_1_2015" table.

So for, like, 1,000 systems over 3 years we already have 3,000 tables which seems a lot to me. When I want to change a column I have to change each single table instead of just one. I also have to do some hacking for this mechanism to work with rails activerecord logic.

Right now, summed up, we have about 300,000,000 positions from various customers. They are write once/read often and by multiple concurrent users.

So my question: is this table splitting a sensible design?

The argument from my clients is that they can more easily backup/archive all data and a lot of small tables are more manageable than one single huge table. But above all each client has access to their specific tables so that performance should be better when 100+ customers want to access their spatial data at the same time.

So is this true? Will performance be worse if Ihave 100+ concurrent users on a single big 300M row table?

Thanks for your help!

Edit: SHOW CREATE for one position table

CREATE TABLE `positions_1000_2015` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `world_x` double DEFAULT NULL,
  `world_y` double DEFAULT NULL,
  `timestamp` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `status` int(11) DEFAULT NULL,
  `coworker_id` int(11) DEFAULT NULL,
  `raw_data` varchar(255) CHARACTER SET latin1 COLLATE latin1_german1_ci    DEFAULT NULL,
  `user_data` varchar(1024) DEFAULT NULL,
  `map_text` varchar(512) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `drive_number` int(11) DEFAULT NULL,
  `course` int(11) DEFAULT NULL,
  `speed` int(11) DEFAULT NULL,
  `max_speed` int(11) DEFAULT NULL,
  `distance` double DEFAULT NULL,
  `gsm_quality` int(11) DEFAULT NULL,
  `sat_count` int(11) DEFAULT NULL,
  `private` int(11) DEFAULT NULL,
  `user_created` tinyint(1) NOT NULL DEFAULT '0',
  `io_01` int(11) DEFAULT NULL,
  `io_02` int(11) DEFAULT NULL,
  `io_03` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `timestamp` (`timestamp`),
  KEY `world_x` (`world_x`),
  KEY `world_y` (`world_y`),
  KEY `status` (`status`),
  KEY `timestamp_status` (`timestamp`,`status`),
  KEY `status_timestamp` (`status`,`timestamp`),
  KEY `idx_raw_data` (`raw_data`),
  KEY `drive_number` (`drive_number`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=1412811 DEFAULT CHARSET=utf8


texts: How exactly does the one-byte "char" type work in PostgreSQL?

texts:
I often see people talking about "char". I've never used it. It's defined in the docs as,

  The type "char" (note the quotes) is different from char(1) in that it only uses one byte of storage. It is internally used in the system catalogs as a simplistic enumeration type.

And further,

"char"  1 byte  single-byte internal type

So, if it's one byte, what is the domain and how would you make use of it? Is it signed or unsigned? In this post by @Erwin Brandstetter he lays it out, but I'm still confused. He's using ascii() and chr(), and provides this

SELECT i
     , chr(i)::"char"        AS i_encoded
     , ascii(chr(i)::"char") AS i_decoded
FROM   generate_series(1,256) i;

That's doing something really weird between 10 and 11.

  i  | i_encoded | i_decoded
-----+-----------+-----------
...
   8 | \x08      |         8
   9 |           |         9
  10 |          +|        10
     |           |           -- WTF is going on here.
  11 | \x0B      |        11
  12 | \x0C      |        12
...

It also gets really weird here:

 126 | ~         |       126
 127 | \x7F      |       127
 128 |           |       128
 129 |           |       128
 130 |           |       128
 131 |           |       128

Why is everything north of 128 being decoded as 128? But to take the bizzare up a bit, after 192 there is a switch and they get decoded as 192..

 190 |           |       128
 191 |           |       128
 192 |           |       192
 193 |           |       192
 194 |           |       192
 195 |           |       192
 196 |           |       192
 197 |           |       192

Erwin says

  There are several characters not meant for display. So encode before you store and decode before you display ...

I'm not sure why we should encode at all though if we're doing exactly what that questions asks

CREATE TABLE foo AS
SELECT i::"char"
FROM   generate_series(-128,127) i;

That works fine. We can get the ints back out using

SELECT i::int FROM foo;

So in short,

What is Erwin's code doing between 10-11 where the i goes null?
Why is 128 repeated so many times?
Why is 192 repeated so many times?
How do I trigger the inability to store 0, when Erwin says you cannot encode 0 this way (null character not permitted)

CREATE TABLE foo AS SELECT 0::int::"char" AS x;
SELECT x::int FROM foo;
 x
---
0


texts: I am debugging poor query performance for my application and recently came across the SQLT tool. The XTRXEC method combines XTRACT and XECUTE and it seems like this provides more info then XTRACT alone. However, one of the statements that I want to investigate is an Insert. Is it safe to run XTRXEC in a live production environment or will the XECUTE try to actually do the insert? If that's the case is it safe to do the XTRACT in production?


texts: The installation puts character-set-server=utf8 in my.ini by default. Is it better to use utf8mb4 instead of clean utf8 and if Yes, why utf8mb4 is not set by default?


texts:
I've just recently started working with Extended Events and am loving it.  However, I ran into a small annoyance and am wondering if there's a way around it.

I had a very simple ExEvent session that was named "DatabaseFooActivity".  Once I got comfortable with that, I expanded it so it was looking at more than just Database "Foo", meaning that the session name is no longer accurate.  Now, when I right-click the session, there's no rename option and when I go into it's properties, the Session Name field is grayed out.

In short, can you change the name of an Extended Event session after it's been created, without dropping and recreating it?


texts: How to install keyring_aws plugin in MySQL?

texts:
I am looking for a way to install keyring_aws plugin in mysql community edition.
Is it possible?

I gone through couple of docs,

  https://dev.mysql.com/doc/refman/5.7/en/keyring-aws-plugin.html
  https://dev.mysql.com/doc/refman/5.7/en/keyring-installation.html

And also tried to findout keyring_aws.so file, but couldn't find it anywhere.

I checked in mysql dir also.

root@server:~# ls -l /usr/lib/mysql/plugin/
total 644
-rw-r--r-- 1 root root  21224 Jan 22 17:26 adt_null.so
-rw-r--r-- 1 root root   6288 Jan 22 17:26 auth_socket.so
-rw-r--r-- 1 root root  44144 Jan 22 17:26 connection_control.so
-rw-r--r-- 1 root root 108696 Jan 22 17:26 innodb_engine.so
-rw-r--r-- 1 root root  88608 Jan 22 17:26 keyring_file.so
-rw-r--r-- 1 root root 154592 Jan 22 17:26 libmemcached.so
-rw-r--r-- 1 root root   9848 Jan 22 17:26 locking_service.so
-rw-r--r-- 1 root root  10840 Jan 22 17:26 mypluglib.so
-rw-r--r-- 1 root root   6288 Jan 22 17:26 mysql_no_login.so
-rw-r--r-- 1 root root  56064 Jan 22 17:26 rewriter.so
-rw-r--r-- 1 root root  56936 Jan 22 17:26 semisync_master.so
-rw-r--r-- 1 root root  14768 Jan 22 17:26 semisync_slave.so
-rw-r--r-- 1 root root  27568 Jan 22 17:26 validate_password.so
-rw-r--r-- 1 root root  31296 Jan 22 17:26 version_token.so

Can we build this .so file from scratch? If yes, where can I find the source?


texts:
What is the value returned by Page Life Expectancy, precisely?

The use of the word "will" in this documentation

  Indicates the number of seconds a page will stay in the buffer pool without references.

suggests that the PLE value is a prediction of some kind. Is the prediction based on the actual computed average of the length of time pages now in the buffer pool have been in the buffer pool?

Is the algorithm used for computing PLE available?


texts:
How can I convert this SQL statement to DAX?

SQL query and tables:

select SUM(Kiekis )
from Info
left outer join Prekes on Info.PrekeID = Prekes.PrekeID
left outer join PrekiuGrupes on Prekes.PrekiuGrupeID = PrekiuGrupes.PrekiuGrupeID
group by PrekiuGrupesID


texts:
Im looking for documentation on best practices for the following scenario.

A hosted application contains some "global" data and some "Per-tenant" data.  A "Tenant" should have no access to another tenant's tables, and I'd like this to be enforced at DBMS level (in addition to being enforced to Application level).

So as an Example the application may offer to Video rental shops a management system.

The Shop Owners and staff and also information pertaining to people who rent movies are global.  Once a customer signed up they would not need to do so again to rent from another shop.

The Shops (tenants) have their own databases (list of customers, inventory, currently booked-out movies, etc).  So when a shop-owner logs in the application presents only his own data, which I don't have a problem with.

Should the system create a new set of tables for each Tenant/Shop?

Or Should the system use DBMS security / authorizations... in other words in the back end the inventory of Shop 1 is in the same table as that of Shop 2, but through DBMS security the views only allow selecting rows for that Shop for who the user is signed into the application.

I'm not sure if my question is clear enough?


texts: What is the difference between a union and an "isa" disjoint relationship?

texts: To me, it seems that a union and an "isa" disjoint relationship are the same. What is the difference between the two?


texts:
I have a 4.4TB database that I've been transactionally replicating to 2 different subscribers just fine.
Yesterday I added a 3rd subscriber that is all flash drives to test how much better it will be with all flash drives.
The latency on the original 2 subscribers is just fine/normal/sub5seconds.
However latency on the flashonly subscriber is growing and growing and growing.

Any idea what a "duh" check would be?

Thanks,
Daniel


texts: SQL Server Express Edition Primary - Secondary Model

texts:
From Replication Considerations (SQL Server Express) in Books Online, SQL Server Express Edition can only act as subscriber, and only by using Windows Sync Manager.
Can anyone suggest any other methods or third party freeware tools available to produce a Primary - Secondary model like transactional replication between two Express Edition instances?

It must be free.
It must not use triggers.
I cannot build a separate application for this.

I'm searching for freeware or a tool like SymmetricDS, but unfortunately SymmetricDS adds too many triggers. For 20 tables, I had more than 100 triggers in my database. Moreover, those triggers will be considered as malware in concern with security. It does performance degradation too. Still, when there are triggers that track down what are we doing, it will be a security concern, because we can't provide 100% assurance that tracked data will be secured as it's the business logic of SymmetricDS. And moreover, I am not going to use  that product, so talking about it over and over won't solve my problem :(