Restore from local disk vs SAN on physical machine (SQL Server Linux) I am new to Linux and our client decided to use SQL Server on Linux. As soon as we started testing, we ran into performance issues.
The server in question is a physical machine configured with a SSD local disk and a "natively" attached Storage. I don't know what a quoted "natively" actually mean here. Linux guy's exact words below

the storage is "natively" attached to the server

The storage area network is also built on top of SSD and I copied a 4.7 GB backup file from there over to local SSD within a second.
cp \gsfs\sql\backups\testdb01.bak \tmp\backups\testdb01.bak

But when I try to restore from

SAN

RESTORE DATABASE successfully processed 612466 pages in 369.425 seconds (12.952 MB/sec).

Local Disk

RESTORE DATABASE successfully processed 612466 pages in 26.248 seconds (182.295 MB/sec).

The database files are also stored on the SAN and while SAN to physical machine has no connectivity issues, I am certain, the way MS SQL reads the files on the SAN has something to do with the slowness.
The database size is not big enough and the database is usually idle based on wait statistics

I want to understand why SQL Server is restoring from SAN slower than local file system. I would understand if the difference was little, but currently, the difference is dramatic. Since our data files and log files are also on SAN, we need to ensure disk is read/written on as fast as possible.

Most of settings are set as default
network packet size is 4096
No antivirus installed

I quit the job, so I don't know what they did about it. Before I left, it was decided that a SAN admin will investigate if their storage was slow or if they were facing other challenges.
Closing this question as there is no way for me to track the issue any further.


How to make conditional ordering for two or more columns In MS SQL Server 2005 I am writing one query with conditional sort and my problem is that I do not know how can I sort conditional using two columns?

If I wrote code like this it is working normaly

select
    *
from
    table
order by
    case @pkr
           when 'kol' then kol
           when 'nci' then nci
    end

I do not know how to make conditional ordering for two or more columns

select
    *
from
    table
order by
    case @pkr
        when 'KOL-NCI' then kol,nci
        when 'kol-MPCI' then kol,mpci
    end

There is an idea to make dynamic TSQL and use sp_executesql but I am still looking for a better idea?

I'll admit I've never had to do this before so there was a bit of head scratching involved. Simple example table to demonstrate:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]') AND type in (N'U'))
    DROP TABLE [dbo].[MyTable]
GO

CREATE TABLE dbo.MyTable
(
    col1 INT
    , col2 CHAR(1)
)
GO

INSERT dbo.MyTable (col1, col2) VALUES (1, 'A')
INSERT dbo.MyTable (col1, col2) VALUES (1, 'B')
INSERT dbo.MyTable (col1, col2) VALUES (1, 'C')
INSERT dbo.MyTable (col1, col2) VALUES (2, 'A')
INSERT dbo.MyTable (col1, col2) VALUES (2, 'B')
INSERT dbo.MyTable (col1, col2) VALUES (2, 'C')
INSERT dbo.MyTable (col1, col2) VALUES (3, 'A')
INSERT dbo.MyTable (col1, col2) VALUES (3, 'B')
INSERT dbo.MyTable (col1, col2) VALUES (3, 'C')

Using an @SortStyle parameter to differentiate between sort orders, @SortStyle =1 will sort by col1 ASC, col2 DESC and @SortStyle=2 sort by col2 DESC, col1 ASC.

DECLARE @SortStyle INT
SET @SortStyle = 1

SELECT
    col1
    , col2
FROM
    dbo.MyTable
ORDER BY
    CASE
        WHEN @SortStyle = 1 THEN col1
    END ASC,
    CASE
        WHEN @SortStyle = 1 THEN col2
    END DESC,
    CASE
        WHEN @SortStyle = 2 THEN col2
    END DESC,
    CASE
        WHEN @SortStyle = 2 THEN col1
    END ASC

SET @SortStyle = 2

SELECT
    col1
    , col2
FROM
    dbo.MyTable
ORDER BY
    CASE
        WHEN @SortStyle = 1 THEN col1
    END ASC,
    CASE
        WHEN @SortStyle = 1 THEN col2
    END DESC,
    CASE
        WHEN @SortStyle = 2 THEN col2
    END DESC,
    CASE
        WHEN @SortStyle = 2 THEN col1
    END ASC

How do you ORDER BY a parameter covers the simpler case of sorting by just 1 column.


MySQL: Prevent DROP TABLE and TRUNCATE TABLE on Slaves Can someone tell me how to prevent the following commands

 DROP TABLE <tbl-name>;
 TRUNCATE TABLE <tbl-name>;

from being replicated to slaves?

Disable binary logging in your session, drop the table, and enable binary logging.

SET sql_log_bin = 0;
DROP TABLE IF EXISTS ... ;
SET sql_log_bin = 1;

This works because if the Slaves do not see the DROP TABLE command registered in the Master's binary logs, it cannot run the DROP TABLE.

I have recommended using SET sql_log_bin = 0; before in my earlier posts

Nov 12, 2012 : Mysql replication and ignore tables
Feb 03, 2012 : Scheduled optimization of tables in MySQL InnoDB
Jul 11, 2011 : ALTER TABLE on a large table with an indexed column (This is where I use SET sql_log_bin = 0; to directly prevent data from being written to Slaves)

Give it a Try !!!


Creating a Windows database backup service I am creating a Windows service in C# that will run on our system to run a backup between 3am and 5am on our databases.

I have started by creating a method that will basically run the backup when SQL statements are passed into a backup method as below.

 private void BackupData()
        {
            try
            {
 RunBackupSQL("INSERT IGNORE INTO new table_backup SELECT * FROM current table",
"Backup current table data");

However this is doing it 1 table at a time. I need to do this for 5 databases with over 56 different tables.

I was wondering if there is a way in which I can create a SQL command to take all tables in each database and do the backup so there would only be 5 SQL statements instead of 56?

The company I work for wants it done in this way based on the fact it was so difficult with their current backup system as it encrypted everything and it was difficult to extract the data again. I need to create a backup onto a different database completely so in effect snapshots of the current.

It needs to run once a day between 3am and 5am so in effect any changes made outside of these time frames will be picked up when the timer does start and it will overwrite the other database. I have 5 databases within the server I need to back up, so really there would be 5 databases I need to copy to. So if anything does go wrong with the current databases we have an exact replica that we can bring back across. It will create new dbs and then every time the current ones are changed it would then run the timer which will send across the changes to the new dbs and constantly do that.

It is MySQL. FYI I'm an apprentice at the moment, so it could be they want me to do it as a project form in C# using Visual Studio to get me into creating Windows services.

It seems you are trying to roll you own incremental backup script. The short answer is:

Don't.

There are so many things to consider when doing backups and even more for incremental backups that it's very difficult to be done right.
Using a simple INSERT IGNORE (which ignores many errors, not only duplicate key errors) is not going to work and give you incremental backups.

Also note that a database has a few more things besides tables. There are view definitions and events that you may want to backup as well. And that tables are created and deleted and changed (columns added, dropped, modified, triggers added or dropped, etc).

Plus a backup has to be a consistent snapshot of the database, so whatever a script does has to be either a single transaction or while the database is not modified by any other session.

I suggest you start by reading the MySQL documentation on:

Backup and Recovery

It explains the various backup types and methods that can be used. There is no solution that covers all situations so the answer for your specific case depends on what exactly is your objective (normal backups, incremental backups) and many other details like the version, edition, the tables' engine (MyISAM, InnoDB, NBD, etc.), whether the database will be accessed during the backups or not, etc.

You might find that a simple mysqldump of the 5 databases is all you need.


PostgresSQL default statement for join I have a transactions table, each transaction has an address field which references a row in an address table, each address in the address table has a coinID.

I want to get a sum of all transactions for EVERY coin for a specific user. I don't care about ownership, I want to display every coin in the table regardless if users own it or not.

The problem I have is that if a user has 0 transactions or addresses that belong to a specific coin it is completely missing from the result. I need all coins in a coin table that have 0 transactions or addresses to return with a sum of 0.

SELECT
        CASE WHEN SUM(transactions.amount) IS NULL THEN 0 ELSE SUM(transactions.amount) END AS balance
FROM coins
Left outer  JOIN addresses ON addresses.coin_id = coins.id
Left outer  JOIN transactions ON transactions.address = addresses.address
Left outer JOIN users ON transactions.user_id = users.id
WHERE users.email = 'matt@test.net'

I have tried quite a few combinations of queries but no matter what I do I get 1 of two options

rows are only returned for coins that the user has a transaction/address for
rows are returned but contain the balance of all transactions in the database not just the ones belonging to that user.

I think the problem is the WHERE. If no user was joined, no e-mail address can match and you get the empty set. Try to CROSS JOIN the user in question and then LEFT JOIN the addresses and transactions.

SELECT c.id,
       coalesce(sum(t.amount), 0)
       FROM coins c
            CROSS JOIN (SELECT u.id
                               FROM users u
                               WHERE u.email = 'matt@test.net') u
            LEFT JOIN addresses a
                      ON a.coin_id = c.id
            LEFT JOIN transactions t
                      ON t.address = a.address
                         AND t.user_id = u.id
            GROUP BY c.id;


Permission denied when creating a database Currently, my local instance looks like this

I want to restore my user database
I have tried restoring my database my rightclicking on Databases and also tried right clicking and doing it on system databases but I get this error

I also have the same permission error when trying to create a new empty database.

If you are a Windows admin, you may create a sysadmin login for yourself following the procedure documented here: Connect to SQL Server when system administrators are locked out
Here it is in batch file form to run (from an elevated command prompt), for a default instance:
net stop mssqlserver
net start mssqlserver /mSQLCMD
sqlcmd -Q "if not exists(select * from sys.server_principals where name='BUILTIN\administrators') CREATE LOGIN [BUILTIN\administrators] FROM WINDOWS;EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\administrators', @rolename = N'sysadmin'"
net stop mssqlserver
net start mssqlserver
sqlcmd -Q "if exists( select * from fn_my_permissions(NULL, 'SERVER') where permission_name = 'CONTROL SERVER') print 'You are a sysadmin.'"

or for a named instance:
net stop mssql$sqlexpress
net start mssql$sqlexpress /mSQLCMD
sqlcmd -S (local)\sqlexpress -Q "if not exists(select * from sys.server_principals where name='BUILTIN\administrators') CREATE LOGIN [BUILTIN\administrators] FROM WINDOWS;EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\administrators', @rolename = N'sysadmin'"
net stop mssql$sqlexpress
net start mssql$sqlexpress
sqlcmd -S (local)\sqlexpress -Q "if exists( select * from fn_my_permissions(NULL, 'SERVER') where permission_name = 'CONTROL SERVER') print 'You are a sysadmin.'"

Note that if you create the login for BUILTIN\administrators and you've got User Account Control enabled then you'll have to elevate to connect.  If you create a login for your individual account you won't need to elevate.


Best way to merge tightly coupled SQL Server Instances I´m looking for some recommendations or best practice for the following scenario:

I need to merge several SQL Server 2014 instances into one (we are switching from many instances to a single clustered instance).
The scope of this merge is of about 10 servers, and 400 databases.
The source instances are very tightly coupled. There are many linked servers, and a lot of stored procedures, views, and functions that use them.
Some SPs also perform operations in the file systems of other servers in the scope of this migration.
As far as I know, we may be forced to change some DB names because we would have name collisions between databases on the destination instance.

I have scripted all DB objects so I can do some text search to find all occurrences of queries that use a given linked server.

So far I believe my options are:

1- The long one. Change the queries that use linked servers so it will query to the same instance (just to another DB in the instance).

2- A short one. We believe we can create DNS aliases that would make the old machine names point to the new clustered instance.

Then we would create several linked servers in the clustered instance pointing to the same instance. The linked servers would have the same names as in the source servers. This should make the old SPs, functions, and views work without any change (except when we need to change a DB name).

Does anyone know a better option or have any recommendations?

I faced an analogous problem where we had different environments using their own linked servers so we could never guarantee that dev was like integration was like test was like production.

The "doh" moment was when we worked out that you can name a linked server whatever you want irrespective of its physical name.

I've not got access to a SQL Server instance to test whether you can create a linked server to itself but if possible then entire redirection would be self contained within your consolidated instance until such time as you were able to weed out the procs that rely on linked servers.


Does an Oracle nvarchar2 take twice as much space as varchar2? Regarding point 4 of this question (which is in reference to MS SQL Server):

  VARCHAR does not store Unicode characters.
  NVARCHAR does store Unicode characters.
  Today's applications should always be Unicode compatible.
  NVARCHAR takes twice the amount of space to store it.
  Point 4 doesn't matter because storage space is extremely
  inexpensive.

Is the same true for Oracle database as well?

Not exactly. Oracle accommodates both byte semantics and character semantics for VARCHAR. The default for character types is byte semantics; a column defined as VARCHAR2(10) holds 10 bytes, which might not be the same as 10 characters. The default for new columns is controlled by the parameter NLS_LENGTH_SEMANTICS.

But in a UTF-8 database, you can define a column as VARCHAR2(10 CHAR), which will hold 10 characters. Even if five of them are single-byte characters and five of them are multi-byte characters, all 10 will fit.

NCHAR and NVARCHAR2 always use character semantics. A column of NVARCHAR(20) will hold 20 characters, but it might take 60 bytes to hold all of them.

NVARCHAR2 is limited to 4000 bytes, which is 4000 single-byte characters, but only a third that many multi-byte characters. (Assuming a maximum of three bytes per character.)

Disk space is much less expensive than it used to be. But you still have to get data off the disk for it to be useful. More bytes, more time; more time, more expense. IME, cost of disk space doesn't matter only to people who don't have to pay for it.

More details in Oracle Database Concepts.


How to capture all user's query on Oracle database I have an Oracle Database installed over Unix system. On this single instance database I have a lot of sessions opened from an application pool (application is installed on another server). Every sessions are opened with the same user name. As sessions can be closed by the pool (really, it can be) I can't capture session id and - for each - enable query trace functionality.
The application is a closed source code (and proprietary), so I can't (and don't want to) reverse engineering it.

Now, I need to capture every query that this user execute. Ideally I want to get one file by user session (but it will be perfect if I get one file with all queries). My real goal is to get queries but session_id and/or query's timestamp could be welcome.

Important note: as my database is a Standard Edition I have no access to any Oracle advanced pack as "Oracle Tuning Pack", "Oracle Diagnostic Pack" or so on ... I only have access to the StatPack and regular SQL query over all database dictionary objects (as a sysdba could be).

Platform information:

Unix RedHat 6.x
Oracle Database 11g Standard edition
only one database instance
number of sessions opened between 150 and 250

Thanks for your ideas

Thomas

As per my knowledge, there is no straightforward way to achieve this goal. However, I have tried to compose the following steps to fulfill the requirements.

You can enable trace for all sessions by using the following statement.

alter system set events '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4';

To disable this tracing, use the following statement.

alter system set events '10046 trace name context off';

Steps:

First, enable 10046 event trace.

SQL> conn / as sysdba

SQL> alter system set events '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4';

To get the names of these trace file use the following query where username should be your username used by the application.

SQL> SELECT LISTAGG(tf, ' ') WITHIN GROUP (ORDER BY tf) "Trace_List"
FROM (
      SELECT sys_context('userenv','instance_name') || '_ora_'|| p.spid || '.trc' as tf FROM
                                                                                v$process p join v$session s
                                                                                ON (s.paddr=p.addr)
                                                                                WHERE s.username='JAY'
);

Trace_List
--------------------------------------------------------------------------------
orcl_ora_3443.trc orcl_ora_3458.trc orcl_ora_3473.trc

 After that, go to USER_DUMP_DEST to find trace files.

SQL> show parameter user_dump_dest

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest               string  /u01/app/oracle/diag/rdbms/orcl/orcl/trace

Now, you have got multiple trace files scattered on your user dump dest. In order to combine and create a single trace file, you can use trcsess utility, which allows trace information from multiple trace files to be identified and consolidated into a single trace file. You can consolidate these trace files based on following criteria. Choose the which is common for these all sessions.

Session Id

Client Id

Service name

Action name

Module name

In this case, I am going to use service name.

SQL> select service_name from v$session where username='JAY';

SERVICE_NAME
----------------------------------------------------------------
SYS$USERS
SYS$USERS
SYS$USERS

Let's use the trcsess utility.

[server1@oracle ]$ trcsess output=/home/oracle/Desktop/main_trace_file.trc service='SYS$USERS' orcl_ora_3443.trc orcl_ora_3458.trc orcl_ora_3473.trc

We have created a single trace file and we can use tkprof utility to create formatted output from this trace file.

[server1@oracle ]$ tkprof main_trace_file.trc main_formatted_trace.txt

Note: If you were using Enterprise Edition you could use fine-grained auditing which is not available in Standard Edition

References:
TRCSESS
TKPROF


Join static file xml with ID field with Database table ID field I am using php to display a table with different columns. The table is build from a static xml file with several hundreds of entries. Each entry has also an ID field.
I have to look up that ID field with some other table's ID field inside a Mysql Database..
What would be the most performant way to do this? Because as I see it, I have to iterate through each of the array entries id field and join that with the table id field... which would result in extremely much queries...
Is there another approach how to do it?
Maybe do one query for each of the complete array's entries, and then get back an array from the mysql table query with the corresponding joined id result (with the same matching indexes as the array's entries) in a list?

What would be easiest probably is to build a string list of the IDs that you need to filter on, in PHP first, and pass that in as a parameter to a procedure that uses that parameter with the FIND_IN_SET() function like so:
SELECT *
FROM SomeOtherTable
WHERE FIND_IN_SET(IdField, IdListParameter)


SQL Server Enforce Password Policy In SQL Server, I understand when the "Enforce password policy" is checked for accounts with SQL authentication, it will pull from the O/S.

If "enforce password policy" is checked and "enforce password expiration" is not checked, will the SQL account's password inherit the expiration of 90 days? Or do both have to be checked?

Does "enforce password policy" override the "enforce password expiration" whether it's checked or not?

The password policy applies to restrictions around Windows password policy, including things like:

An uppercase character (A-Z).
A lowercase character (a-z).
A digit(0-9).
One of the non-alphanumeric characters, such as a    space, _, @,
*, ^, %, !, $, #, or &.

It does not handle expiration checks, you need to have the check expiration set for that to happen.

Reference MSDN Create Login


How To Set Up Web Synchronisation With IIS 7.5 I set up merge replication web sync using a pull subscription.

When I try to start the merge agent at the client side I get this error,

The Merge Agent could not connect to the URL
'http://address.com/MergeReplication/replisapi.dll' during Web synchronisation.
Please verify that the URL, Internet login credentials and proxy server settings are
correct and that the Web server is reachable.

When I browse to the address http://address.com/MergeReplication/replisapi.dll?diag it works fine when I specify the same login credentials as I did for the subscription.

If we have a think about some of the possible causes, proxy server, we're not using that. URL is fine (as far as I can tell). Login credentials are OK as far as I can tell too.

The other thing I noticed is that I can see the https traffic in fiddler when I put the ?diag address in my browser, but when I start the merge agent I don't see the traffic in fiddler.

UPDATE:

I used wireshark to look at the traffic going between my subscription PC, and the IIS server. When I run the subscription agent I get this traffic,

TLSv1 Client Hello
TLSv1 Server Hello
TLSv1 Client Key Exchange
TLSv1 Change Cipher Spec, Encrypted Handshake Message
TCP https [FIN, ACK]
TCP https [RST, ACK]

The last item there RST is hilighted as red, and does not appear when I browse to the ?diag page.

Perhaps there is a problem with the certificate I have. I created a self signed security certificate and installed it on my PC. When I browse to the ?diag page I don't get a security certificate error.

The answer at this point appears to be quite simple. I was using a different domain account to run the subscription agent. I had to log into my machine with that account and install the security certificate.


How to insert values to one table from the table stored in another database in postgresql? Here is the structure of table:

`* Table 1 of database A contains mobile number and userId and both field cannot be blank
I want to fetch data from table 1 of database A and insert that with some other data in the table 1 of database B, Following are some clause that I have to follow during insertion and selection of data:
1. Check whether userid exists of a particular phone number from table 1 of Database A If user id exists the  Insert the row to Table B.1 of database B without phone number
2. If userId does not exists in the table A.1 of database A then Insert the row into Table B.1 of database B with null userId and with phone number (i.e. row 3rd of Table B.1)

So far what I have tried is:

WITH useridTab AS
(SELECT *
 FROM dblink('postgres://postgres:password@localhost/databaseA?sslmode=disable',
              E'SELECT mobilenum,game_user_id FROM table1 where mobilenum=\'1234\' UNION ALL
  SELECT \'1234\', null
limit 1') as data(mobilenum text,userid text))
insert into table1 (userid,phonenumber,email,address) select userid ,case when userid is null then mobilenum else 'No One' end,'ABC@mail.com','AbcAddress' from useridtab;

I used DB link to fetch data from table stored in some other database, created CTE of it then used that to insert values to table1 of database B.
Just wanted to whether this approach is correct or some more optimization can be done.

Using a Foreign Data Wrapper is generally preferable to using dblink.  Once you set up the link and import the table definition, then you can write the query just as if the table were local.

Your English description of the problem doesn't make any sense to me, so I can't tell you if the specific query you wrote does what you want.


Splitting a column on underscores What SQL syntax would split this column into three new columns (split would be on the underscore)?

There are other columns in the table, but I would like to split these into three (underscore based).

i usually say to people don't save data with any delimiter

CREATE TABLE [test] (JOBNUMBR VARCHAR(15))
GO

INSERT INTO test SELECT '57299_87961_003' UNION SELECT '66515_88567_OOO' UNION SELECT '58OO7_88695_OO1'
GO

SELECT JOBNUMBR
,Reverse(ParseName(Replace(Reverse(JOBNUMBR), '_', '.'), 1)) As [no1]
,Reverse(ParseName(Replace(Reverse(JOBNUMBR), '_', '.'), 2)) As [no2]
,Reverse(ParseName(Replace(Reverse(JOBNUMBR), '_', '.'), 3)) As [no3]
FROM (Select newid() AS [ID], JOBNUMBR from test) As [t]
GO

JOBNUMBR        | no1   | no2   | no3
:-------------- | :---- | :---- | :--
57299_87961_003 | 57299 | 87961 | 003
58OO7_88695_OO1 | 58OO7 | 88695 | OO1
66515_88567_OOO | 66515 | 88567 | OOO

db<>fiddle here


Ops Manager backup process Few questions on ops-manager backup process

While backup ops manager creates a head db which is copy of the monogdb deployment
I want to know the reason behind this. Why create another copy and increase space consumed for backup
Ops manager takes snapshot of the head db which got created where Backup Daemon is running
What is the type of snapshot?
How ops manager maintains consistency in snapshots for whole cluster?

Whole process is explained here

Backup is created "elsewhere" (than originating data; of course) and that backup is kept "up-to-date". Time to time snapshots (moments in time) is created (and stored) from that data. In the "side" system keeps copy of opLog too, so it is possible to "roll-forward" from snapshot to specific moment in time (between snapshots).


Get data between date range I have a range of tabular data with two columns for dates Start Date and End Date and I want to pick up everything either Starts/Ends on coming Monday 03/05/21 or Starts/Ends on Sunday 09/05/21 and everything in between so typically a week of workload.
e.g.
Derived Monday Start Date:  03/05/2021
Derived Sunday End Date:    09/05/2021
Derived Monday End Date:    10/05/2021
I initially wrote below syntax to work around to choose every Monday since Report will run on every Thursday but the query not producing correct results, I understand it might be due to excessive use of ANDs & ORs -- I am pretty much new in SQL world so any help on this would be really appreciated.
WHERE
   StartDate >= CAST(GETDATE()+4 AS date) AND EndDate <= CAST(GETDATE()+14 AS date)
OR StartDate <= CAST(GETDATE()+4 AS date) AND EndDate >= CAST(GETDATE()+4 AS date)
OR StartDate >= CAST(GETDATE()+14 AS date) AND EndDate <= CAST(GETDATE()+14 AS date)

I also tried below but this formula doesn't up pick on-going work:
StartDate BETWEEN CAST(GETDATE()+4 AS Date) AND DATEADD(week, 2, CAST(GETDATE() AS Date))

ID  Start Date  End Date
1   20/04/2021  07/05/2021
2   05/06/2021  06/06/2021
3   04/05/2021  30/06/2021
4   20/05/2021  30/05/2021
5   05/04/2019  06/06/2021
6   24/06/2018  31/10/2023
7   05/05/2021  06/05/2021
8   07/05/2021  08/05/2021
9   27/11/2020  05/05/2021
10  09/05/2021  10/05/2021

I want to pick everything that is either starts or End on Monday OR  starts or End on Sunday and everything in between a week of range for example.
I want data for next week between 03/05/21 - 10/05/21 so the query should pick ID: 1, 3, 7, 8, 9, 10. I sadly can't use any fixed date as the data I want, is between Every Monday and Sunday.


grant select on all databases except one mysql I have user called test_user created under MySQL 5.5.17 under MS Windows 2008R2, I want to grant this user select privileges on all databases except MySQL database, note I have around 200 database inside this instance.

EDIT:

EDIT2:

Run the output of the following query:

SELECT CONCAT("GRANT SELECT ON ",SCHEMA_NAME,".* TO 'test_user'@'localhost';")
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME NOT LIKE 'mysql';


Can't login to sql server custom db I have wamp installed in my system and sql server 2014, and configure so i can access sql server db through php.. with this code:

<?php
$serverName = "MY-PC\sqlexpress";

$connectionInfo = array("Database"=>"master");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
 echo "Connection established.
";
}else{
 echo "Connection could not be established.
";
 die( print_r( sqlsrv_errors(), true));
}
?>

that actually works. But when i change "master" with some "customdb" its giving me this error.

  Error: 18456, Severity: 14, State: 38. Login failed for user 'NT
  AUTHORITY\SYSTEM'.  Reason: Failed to open the explicitly specified
  database 'customdb'. [CLIENT: ]

It seems you haven't added to service account as a database user to customdb:

USE customdb;
CREATE USER [NT AUTHORITY\SYSTEM];

You will also need to grant the needed permissions on database objects to allow a minimally privileged user to access data.  I suggest you do this via role membership to facilitate manageability:

USE customdb;
CREATE ROLE YourRole;
ALTER ROLE YourRole ADD MEMBER [NT AUTHORITY\SYSTEM];
GRANT SELECT ON YourTable TO YourRole;

These symptoms may also be due to the account name and SID beging different, perhaps because the database was restored/attached. Try dropping and recreating the login and user.


Searching Oracle tables for particular values I want to search all tables, all rows in a particular schema for a particular value. This is so I try to understand where a value is getting persisted.

How do I do this in Oracle?

This script constructs one select statement per table for all CHAR and VARCHAR2 columns.
It will give error ORA-01489 if some of the tables have many columns with long names.
It uses LIKE and equality search, in hope of an index.
The result is the table name and exact row, where the first occurrence of string has happened. Run resulted statement to quickly get this row.
If you don't have access to DBA_ views, and want to search in schema you are connected to, then change DBA_ to USER_ and remove rows 11 and 12.
For large schemas (many gigabytes or terabytes) it is slow to do such search.
HR@mydb> declare
  2   l_owner varchar2(30):= 'HR';
  3   l_search_string varchar2(200):= 'King';
  4   l_sql varchar2(30000);
  5   l_rowid rowid;
  6  begin
  7    l_search_string := upper(l_search_string);
  8    for c in (select t.table_name, listagg ('upper('||column_name||')',',') within group ( order by column_id )col
  9                from DBA_tables t, DBA_tab_columns c
 10               where t.table_name = c.table_name
 11                 and t.owner = c.owner
 12                 and t.owner = L_OWNER
 13                 and c.data_type in ('VARCHAR2','CHAR')
 14               group by t.table_name
 15               order by t.table_name )
 16    loop
 17      begin
 18        dbms_application_info.set_action('table: '||c.table_name);
 19        l_sql := 'select rowid from '|| l_owner||'.' ||c.table_name||
 20          ' where (' || replace(c.col,',',' like ''%'||L_SEARCH_STRING||'%'' or ' )|| '='''||L_SEARCH_STRING||'''' ||
 21          ') and rownum = 1';
 22        --DBMS_OUTPUT.PUT_LINE ( 'l_sql = ' || l_sql );
 23        --
 24        execute immediate l_sql  into l_rowid;
 25        --
 26        DBMS_OUTPUT.PUT_LINE ( 'select * from  '|| l_owner||'.' || c.table_name||' where rowid = '''||l_rowid|| ''';'  );
 27      exception when no_data_found then null;
 28      end;
 29    end loop;
 30    dbms_application_info.set_action(null);
 31  end;
 32  /
select * from  HR.EMP where rowid = 'AAAW1OAAGAAACErAAI';
select * from  HR.EMPLOYEES where rowid = 'AAAVPuAAFAAAADPAAA';
select * from  HR.NEW_EMPS where rowid = 'AAAW1XAAGAAACE7AAK';
select * from  HR.T where rowid = 'AAAXE9AAHAAAIACAAT';
select * from  HR.TT where rowid = 'AAAWTDAAIAAAA2CAFk';

From another session, you can monitor on what table the select is working at that moment.
select sid, action, username,  program
  from v$session
 where action like 'table: %'
/


calculate percent change between two entire rows i have a table of currency data, it's have multiple records in each rows.

| Date           | ETH  | XRP | LTC | USDT | BTC  | etc... |
|----------------|------|-----|-----|------|------|--------|
| Today          | 3111 | 0.3 | 100 | 1    | 3500 |        |
| Tomorrow       | 3211 | 0.6 | 90  | 1.14 | 3540 |        |
| After Tomorrow | 3215 | 0.8 | 122 |   1  |      |        |

Date column is timestamp.

the output sample data is:

| Date             | ETH  | XRP | LTC | USDT | BTC  | etc... |
|------------------|------|-----|-----|------|------|--------|
| Past Day ->Today | 10%  | 1%  |-10% | 0    | 2%   |        |

I want to know how to find the percent change from day to day for all column that have price(ETH,XRP,LTC,USD,...) without execute multi query request like this :

SELECT t.*,
 BTC - (SELECT BTC FROM PRICE prev WHERE prev.date < t.date ORDER BY date DESC LIMIT 1) AS percent


Filtering characters to just return numeric characters in a string? I want to filter an nvarchar field to return just the numeric values.

I have some SQL to do this but it seems way more complicated than it needs to be. I am interested in finding out if anyone has a better way to filter out any non-numeric characters in a string?

IF OBJECT_ID('tempdb..#MOB') IS NOT NULL
BEGIN
    DROP Table #MOB
END

SELECT [mob]
INTO #MOB
FROM (
SELECT '(00) 1234 5678' AS [mob]
UNION
SELECT '1234 5678' AS [mob]
UNION
SELECT '+61 012 345 678' AS [mob]
) AS temp

;WITH [fill] ([Num], [Index], [MOBILEPHONE])
AS
(
    SELECT
    CASE
        WHEN [MOBILEPHONE] IS NOT NULL
        THEN SUBSTRING([MOBILEPHONE], 1, 1)
        ELSE NULL
    END AS [Num]
    , 1 AS [INDEX], [MOBILEPHONE]
    FROM (
        SELECT DISTINCT [mob] AS [MOBILEPHONE]
        FROM #MOB as t
    ) AS temp
    UNION ALL
    SELECT
    SUBSTRING([F].[MOBILEPHONE], [F].[Index] + 1, 1) AS [Num]
    ,[F].[Index] + 1 AS [Index]
    , [MOBILEPHONE]
    FROM [fill] AS [F]
    WHERE ([F].[Index] + 1) < LEN([F].[MOBILEPHONE]) + 1
)

SELECT [E].[MOBILEPHONE] AS [old_MOBILEPHONE],
    STUFF((SELECT N'' + [F].[Num]
    FROM [fill] AS [F]
    WHERE (PATINDEX('%[^0-9]%', [F].[Num]) = 0 OR PATINDEX('%[^0-9]%', [F].[Num]) IS NULL) AND
    ([F].[MOBILEPHONE] = [E].[MOBILEPHONE])
    ORDER BY [F].[MOBILEPHONE], [F].[Index]
    FOR XML PATH('')), 1, 0, '')
    AS [MOBILEPHONE]
FROM (
        SELECT DISTINCT [t].[MOBILEPHONE]
        FROM (SELECT [mob] AS [MOBILEPHONE] FROM #MOB) as t
    ) AS [E]

Output

╔═════════════════╦═════════════╗
║ old_MOBILEPHONE ║ MOBILEPHONE ║
╠═════════════════╬═════════════╣
║ (00) 1234 5678  ║  0012345678 ║
║ +61 012 345 678 ║ 61012345678 ║
║ 1234 5678       ║    12345678 ║
╚═════════════════╩═════════════╝

I have seen the Q & A T-SQL select query to remove non-numeric characters on Stack Overflow, but that answer has a similar solution to what I have found, using a CTE table and recursion. I am searching for something simpler. Hoping there is something like a custom collation that I can create or a regex filter I can apply?

To use regular expressions, you would need to use a SQLCLR function. Solomon Rutzky has created a library of useful CLR functions called SQLsharp. The free edition includes several regular expression functions including RegEx_Replace4k as used below:

SELECT
    M.mob,
    numeric_only =
        SQL#.RegEx_Replace4k
        (
            M.mob,  -- Source
            N'\D',  -- Regular expression
            N'',     -- Replace matches with empty string
            -1,     -- Unlimited replacements
            1,      -- Start at character position
            NULL    -- Options (see documentation)
        )
FROM #MOB AS M;

This produces the output shown below:

╔═════════════════╦══════════════╗
║       mob       ║ numeric_only ║
╠═════════════════╬══════════════╣
║ (00) 1234 5678  ║   0012345678 ║
║ +61 012 345 678 ║  61012345678 ║
║ 1234 5678       ║     12345678 ║
╚═════════════════╩══════════════╝

Regular expressions are a bit overkill for this simple requirement, so writing your own CLR implementation to just remove non-numerics would likely be even faster. Nevertheless, I have found the library function above to be as fast, if not faster, than the best T-SQL implementations (T-SQL string manipulation is rather slow).

For a T-SQL implementation, see Splitting Strings Based on Patterns by Dwain Camps.


Formulating a Join Query for PostqreSQL I have a quick question, if someone can help, I would be grateful

I have the following tables
User, Video, User_video

  user has userdetails (user_id, name, email)
  video has videoDetails (video_id, video_url, video_price)
  user_video is the mapping table which has (user_id,video_id,price,dttm)

currently, if I do a select from video table, it gives me all available videos which is fine. The current return format is as follows:

123  |  Video 1  | desc of video1 | /abvu274vasag.3gp | 5.00
124  |  video 2  | desc of video2 | /dh63ddh3dag.3gp  | 6.00

So far, so good, but now, I need to get the details of the logged in user, as in which videos he has purchased and which ones he has not, so in the above response, next to each video, I need a "Y" or "N" flag (by referencing the user_video) table.

I need the output as follows:

123  |  Video 1  | desc of video1 | /abvu274vasag.3gp | 5.00 | Y
124  |  video 2  | desc of video2 | /dh63ddh3dag.3gp  | 6.00 | N

It is a PostgreSQL database. And I am not an expert on databases, I am familiar with only the basics.

If someone can guide me in this regards, it will be most helpful.

Assuming (video_id, user_id) is unique in user_video, a plain LEFT JOIN would do the job:

SELECT v.*, uv.video_id IS NOT NULL AS has_bought
FROM   video v
LEFT   JOIN user_video uv ON uv.video_id = v.video_id
                         AND uv.user_id = $current_user_id;

$current_user_id being the ID of the current user.

If (video_id, user_id) is not unique, you could add GROUP BY

SELECT v.*, count(uv.video_id) > 0 AS has_bought
FROM   video v
LEFT   JOIN user_video uv ON uv.video_id = v.video_id
                         AND uv.user_id = $current_user_id
GROUP  BY v.video_id;

count() doesn't count NULL values.
Or use an EXISTS semi-join to avoid duplicates:

SELECT v.*, EXISTS (SELECT 1 FROM user_video uv
                    WHERE  uv.video_id = v.video_id
                    AND    uv.user_id = $current_user_id) AS has_bought
FROM   video v;

Or use the PostgreSQL-specific DISTINCT ON, that's particularly handy if you need the result in a certain sort order anyway.

SELECT DISTINCT ON (v.video_id)
       v.*, uv.video_id IS NOT NULL AS has_bought
FROM   video v
LEFT   JOIN user_video uv ON uv.video_id = v.video_id
                         AND uv.user_id = $current_user_id
ORDER  BY v.video_id, uv.video_id;  -- NULL sorts last


Detect changes in SQL Server database I need to know if a SQL Server 2000 database has been changed. The changes are not important, just to know if any change has been made. Maybe SQL Server keeps a timestamp or an incremental field that provide that information for internal uses. Has any one idea about it? Thanks...

Just put a delete/insert/update trigger on every table and log every operation to a central location. Something like this (untested):

CREATE TRIGGER dbo.auditTableName
ON dbo.TableName
FOR INSERT, UPDATE, DELETE
AS
BEGIN
  SET NOCOUNT ON;
  IF EXISTS (SELECT 1 FROM inserted UNION ALL SELECT 1 FROM deleted)
    INSERT CentralDB.dbo.AuditTable(TableName, EventTime, Action)
      SELECT N'dbo.TableName', GETDATE(), CASE
        WHEN EXISTS (SELECT 1 FROM inserted) THEN
          WHEN EXISTS (SELECT 1 FROM deleted) THEN
            'Update' ELSE 'Insert' END ELSE 'Delete' END;
END
GO

You can generate this code easily, like this, assuming all objects are under dbo:

SELECT 'CREATE TRIGGER dbo.audit' + name + '
 ON dbo.' + name + '
 FOR INSERT, UPDATE, DELETE
AS
BEGIN
  SET NOCOUNT ON;
  IF EXISTS (SELECT 1 FROM inserted UNION ALL SELECT 1 FROM deleted)
    INSERT CentralDB.dbo.AuditTable(TableName, EventTime, Action)
      SELECT N''dbo.' + name + ''', GETDATE(), CASE
        WHEN EXISTS (SELECT 1 FROM inserted) THEN
          WHEN EXISTS (SELECT 1 FROM deleted) THEN
            ''Update'' ELSE ''Insert'' END ELSE ''Delete'' END;
END
GO' FROM sysobjects WHERE type = 'U';

Again, that's just a general idea, untested as I don't have SQL Server 2000 handy. Use results to text and you should get the appropriate trigger for each table.


Postgres doesn't use expression index for group by I'm doing a simple group by on a single column with a matching index and it works fine:

create index on contacts (home_city);
select home_city, count(*)
from contacts
group by home_city
limit 50;

Takes 150 ms on an 8 million row table. That's plenty fast enough. EXPLAIN ANALYZE output:

"Limit  (cost=1000.46..9256.24 rows=50 width=17) (actual time=14.201..76.630 rows=50 loops=1)"
"  ->  Finalize GroupAggregate  (cost=1000.46..207395.17 rows=1250 width=17) (actual time=14.200..62.196 rows=50 loops=1)"
"        Group Key: home_city"
"        ->  Gather Merge  (cost=1000.46..207370.17 rows=2500 width=17) (actual time=14.100..76.557 rows=97 loops=1)"
"              Workers Planned: 2"
"              Workers Launched: 2"
"              ->  Partial GroupAggregate  (cost=0.43..206081.59 rows=1250 width=17) (actual time=0.897..56.260 rows=41 loops=3)"
"                    Group Key: home_city"
"                    ->  Parallel Index Only Scan using contacts_6_home_city_idx on contacts_6  (cost=0.43..188620.82 rows=3489654 width=9) (actual time=0.026..28.866 rows=105725 loops=3)"
"                          Heap Fetches: 0"
"Planning Time: 0.099 ms"
"Execution Time: 76.680 ms"

Now I want to do it with a case-insensitive index:

create index on contacts (lower(home_city));
select lower(home_city), count(*)
from contacts
group by lower(home_city)
limit 50;

Takes 3-4 seconds. It appears to be using the wrong index:

"Limit  (cost=215869.40..215882.20 rows=50 width=40) (actual time=4253.233..4253.846 rows=50 loops=1)"
"  ->  Finalize GroupAggregate  (cost=215869.40..216175.66 rows=1197 width=40) (actual time=4253.232..4253.335 rows=50 loops=1)"
"        Group Key: (lower((home_city)::text))"
"        ->  Gather Merge  (cost=215869.40..216148.72 rows=2394 width=40) (actual time=4253.219..4253.796 rows=102 loops=1)"
"              Workers Planned: 2"
"              Workers Launched: 2"
"              ->  Sort  (cost=214869.38..214872.37 rows=1197 width=40) (actual time=4244.566..4244.630 rows=710 loops=3)"
"                    Sort Key: (lower((home_city)::text))"
"                    Sort Method: quicksort  Memory: 88kB"
"                    Worker 0:  Sort Method: quicksort  Memory: 89kB"
"                    Worker 1:  Sort Method: quicksort  Memory: 120kB"
"                    ->  Partial HashAggregate  (cost=214793.22..214808.18 rows=1197 width=40) (actual time=4241.397..4241.658 rows=1021 loops=3)"
"                          Group Key: lower((home_city)::text)"
"                          ->  Parallel Index Only Scan using contacts_6_home_city_idx on contacts_6  (cost=0.43..197344.95 rows=3489654 width=32) (actual time=0.059..2918.355 rows=2778720 loops=3)"
"                                Heap Fetches: 0"
"Planning Time: 0.118 ms"
"Execution Time: 4253.906 ms"

When I delete the index on plain home_city, it does a full table scan and takes 6 seconds.

How do I get it to use the right index? What am I missing here?

(I'm using Postgres 11.5).

There is this code in check_index_only in src/backend/optimizer/path/indxpath.c:

/*
 * check_index_only
 *      Determine whether an index-only scan is possible for this index.
 */
static bool
check_index_only(RelOptInfo *rel, IndexOptInfo *index)
{

[...]

    /*
     * Construct a bitmapset of columns that the index can return back in an
     * index-only scan.  If there are multiple index columns containing the
     * same attribute, all of them must be capable of returning the value,
     * since we might recheck operators on any of them.  (Potentially we could
     * be smarter about that, but it's such a weird situation that it doesn't
     * seem worth spending a lot of sweat on.)
     */
    for (i = 0; i < index->ncolumns; i++)
    {
        int         attno = index->indexkeys[i];

        /*
         * For the moment, we just ignore index expressions.  It might be nice
         * to do something with them, later.
         */
        if (attno == 0)
            continue;

So if I read that right, expressions are ignored when an index only scan is considered.

I don't think that's a matter of principle, it is just not implemented.

From PostgreSQL v12 on, you could use a case-insensitive ICU collation to get what you want:

CREATE COLLATION und_ci (
   LOCALE = 'und-u-ks-level2',
   PROVIDER = icu,
   DETERMINISTIC = FALSE
);

Then you'd have to change the column to use that collation:

ALTER TABLE contacts ALTER home_city TYPE text COLLATE und_ci;

This index can be used for case insensitive searches, and you can use it for an index only scan too.


delete WSFC 2012 cluster role/group fails PS C:\Windows\system32> get-cluster

Name
----
sql2012poc_cl1

PS C:\Windows\system32> get-clustergroup

Name                                    OwnerNode                               State
----                                    ---------                               -----
AG1                                     srvgnpw049                              Failed
Available Storage                       srvgnpw049                              Offline
Cluster Group                           srvgnpw049                              Online

PS C:\Windows\system32> remove-clustergroup AG1
remove-clustergroup : The object has been deleted from the cluster.
At line:1 char:1
+ remove-clustergroup AG1
+ ~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (:) [Remove-ClusterGroup], ClusterCmdletException
    + FullyQualifiedErrorId : ClusterObjectNotFound,Microsoft.FailoverClusters.PowerShell.RemoveClusterGroupCommand
PS C:\Windows\system32> get-clusterresource
get-clusterresource : The object has been deleted from the cluster.
At line:1 char:1
+ get-clusterresource
+ ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (:) [Get-ClusterResource], ClusterCmdletException
    + FullyQualifiedErrorId : ClusterObjectNotFound,Microsoft.FailoverClusters.PowerShell.GetResourceCommand
PS C:\Windows\system32>

I am trying to delete Cluster Role from Windows 2012 Failover Cluster but get above error in both WFC Manager GUI and PS.
AG1 cluster role is still visible in GUI and Cluster Core Resources is empty in GUI.

Please advise how AG1 role can be removed and cluster core resources be visible again.

As a quick workaround ended up deleting whole cluster:

PS C:\Windows\system32> Import-Module FailoverClusters
PS C:\Windows\system32> clear-clusternode
Clear-ClusterNode
Are you sure you want to forcefully clean up cluster node SRVGNPW049?
[Y] Yes  [N] No  [S] Suspend  [?] Help (default is "Y"): Y
PS C:\Windows\system32>

And then recreating a new one.


MySQL Grant Permission I want to Grant a user only INSERT permission.

When I try to insert data, it throws the error "Access Denied".

What permission do I give a user to use/select database in mysql and how?

I have given only GRANT INSERT ON database.* To user@'localhost';

'insert' permission should be all you need to do an insert:

create database stack;
create user dummy;
create table stack.t(v integer primary key);
grant insert on stack.* to dummy;

as dummy:

insert into stack.t values(1);
Query OK, 1 row affected (0.00 sec)

select * from stack.t;
ERROR 1142 (42000): SELECT command denied to user 'dummy'@'localhost' for table 't'

and the grant like this will even apply to tables created after the grant was made - it gives a database privilege

As you also want to select you will also need to grantselecton stack.* to dummy


Specifying the failover partner when using Availability Groups We have a situation where we are using replicated databases in an environment that's making use of Availability Groups.  As we wanted no manual intervention when failing over, we decided to set up subscriptions to write replicated data to both of our replica servers.  As these databases will only ever be read from, this architecture should be ok.

To further configure this and after reading this article by Brent Ozar, we set the connection strings in our apps that used said databases with a failover partner such that if we lost our primary replica then our app would still work.

After a recent test, failover and fail back, we have now started receiving some intermittent errors from our clients along the lines of:

System.InvalidOperationException:
Server xxx, database xxx is not configured for database mirroring.

At present I am confused as to why this error is appearing. There has been some suggestions of adding a connection timeout to the connection string, however that doesn't make sense to me as if the primary timed out then reading from the secondary should work, and in our case is valid as it's an exact copy.

If anyone has any ideas I would be grateful.

EDIT:  Further to this I have now removed the failover partner from the connection string and left it pointing at the DB on the replica, however we get errors of:

System.Data.SqlClient.SqlException: 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: TCP Provider, error: 0 - Only one usage of each socket address (protocol/network address/port) is normally permitted.)

Its like the client has cached the secondary server somewhere?

Edit: in response to Brent's answer

Hi Brent thanks for that, however I wasn't clear in my initial description.  The replicated databases are not part of an availability group but instead I have a subscription on each node because, as I understand it, replicated DBs are not well supported in AGs.

I was therefore trying to come up with a way to avoid manual intervention for these DBs should a failure of the replica occur and the other databases which are part of an AG failover.

I was thinking that while Node A was up all connections would go to that server, but if Node A failed then connections would go to Node B when using Failover Partner.  However this does not seem to be the case and actually according to this article:

http://msdn.microsoft.com/en-gb/library/system.data.sqlclient.sqlconnection.connectionstring.aspx

If you specify a failover partner but the failover partner server is not configured for database mirroring and the primary server (specified with the Server keyword) is not available, then the connection will fail.

This Failover Partner configuration seems not to be supported for how I am attempting to use it, but leaves me with a situation where manual intervention is required for any failover :(  unless I am missing something?

There's two parts to this question:

First, can you use the Failover Partner connection string tip with AlwaysOn Availability Groups?  No.  AlwaysOn AG's "Listener" technology is the replacement.  Have your connection strings point to the listener name and they'll always get the primary replica.  (For the next part of this answer, I'm assuming you're using the listener name - if you're not, start, heh.)

Second, why do some queries fail to connect to the listener?  This has to do with the number of DNS entries for the listener.  All possible subnets for the listener will be in DNS at all times.  If you've got a listener in 192.168.1.X and another in 192.168.100.x, both listeners will always be in DNS.  By default, your clients will try connecting to each of the DNS entries serially, and not always in numeric order.  If you've got a 30-second connection timeout, it's possible that your app will only try one of the IPs and then fail before it has the time to try the second one.

If you want to try connecting to all possible IPs simultaneously, check out the MultiSubnetFailover = True options for the SQL Server client as described here: http://msdn.microsoft.com/en-us/library/hh205662.aspx

Otherwise, you'll need to increase your connection timeout to account for the multiple IPs.

Update Feb 27: the question added, "I was therefore trying to come up with a way to avoid manual intervention for these (replicated) DBs should a failure of the replica occur and the other databases which are part of an AG failover."

Ooo, unfortunately, no, if the databases aren't part of the Availability Group, you're going to be doing manual work in order to fail over. One popular option is to use a DNS record, and just repoint the DNS record at whatever server is currently hosting the primary copy of the databases.


SQL Server Setup: Report Server and RAM Considerations DISCLAIMER

I am a software developer by trade, but sometimes have to put on my 'dba' hat in the absence of a true dba.

SETUP

I am working on a project where:

SQL Version is SQL Server 2008 R2 (no SP installed).
They have 32GB of RAM, but 28,672MB is the max server memory allowed.
The primary usage of the database is hosting the db for an account / business management system called 'Aderant'.
I have added SQL Reporting Services
I built a few Reports with Report Builder to report on AR and open invoices,
I am experiencing performance issues for larger data sets including CPU spikes to 100%, some reports do not return at all.

QUESTION

Do I need to request more RAM on this server for the additional overhead of running Reporting Services?

Couldn't find anything specific to this on the MS Docs site, searching for "SQL Server Handware and Software Requirements".

Thanks!

Report Server executes the query and stores a copy of it within its cache. This is outside of the SQL Server process and so you will need to account for it when architecting your installation. Generally, the biggest winner I have noticed when I have done installations myself is the number of CPU's. RAM helps of course, but I have found additional CPU's to have the biggest gains - no point having 1TB of RAM if your request is stuck in a queue on a 2 core machine!

There is a very good (but lengthy) white-paper on MS-Docs called Planning for Scalability and Performance with Reporting Services which outlines the difference between different hardware setups, scaling and provides some very good advice on how to architect a SSRS solution. I would highly recommend that you check it out.


Is it safe to risk multiple Firebird nbackup -L? I'm using Firebird 3.0 nbackup to lock + copy + unlock each night.

If some steps fail or are skipped e.g. due to system restart (updates), it may happen that the master is not unlocked (-N). Next night, it will already be in locked state, but the scheduled backup task will lock it again (-L).

Is this safe?

Will the second -L have any effect, or is it a no-op, so a single unlock (-N) will restore it to normal state? Or do I have to safeguard against a second lock (-L) if it's already in locked state?

Executing nbackup -L when a database is already locked will produce an error (and the executable will return exit code 1 instead of 0):

[
PROBLEM ON "begin backup".
unsuccessful metadata update
-ALTER DATABASE failed
-Database is already in the physical backup mode
SQLCODE:-607
]

In that case, Firebird (or nbackup) won't change anything in the state of the database. So the locked database will remain locked.

Continuing would be fine in theory (as the database is locked), but I don't think it is a good idea to just ignore errors and continue, because it could be a different error than this. So, for proper error checking, you would need to check if the error is Database is already in the physical backup mode and not some other error.

As an aside, you can achieve the same effect with ALTER DATABASE BEGIN BACKUP and ALTER DATABASE END BACKUP, which will also work with SuperServer mode (as the nbackup tool needs direct file access, while SuperServer uses an exclusive lock on the file).


MySQL transaction size - how big is too big? I have an import process that runs every so often and I want it to be an 'all or nothing' kind of deal, aka: a transaction.

There are many aspects, and the imports may yield anywhere between 100k-1mil+ records.
This equates to a payload ranging from several MB to a few hundred MB of data.

I know temp tables are another option - but this method seems so handy.

Are there any caveats to be aware of regarding this kind of practice with a large amount of data manipulation between commits? (Outside of the typical write/indexing load burst once committed)

One bottleneck to be aware of is the InnoDB Log Buffer. The size is set by innodb_log_buffer_size. Here is what the MySQL Documentation says about it:

  The size in bytes of the buffer that InnoDB uses to write to the log files on disk. The default value is 8MB. A large log buffer enables large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have big transactions, making the log buffer larger saves disk I/O.

The InnoDB Log Buffer should not be be confused with the InnoDB Buffer Pool. The major difference between them is their purpose. The InnoDB Log Buffer will basically record short-term changes that get written to the redo logs (ib_logfile0, ib_logfile1). The InnoDB Buffer Pool (sized by innodb_buffer_pool_size) caches data and index pages that are to be committed (if the pages are dirty) and eventually written) to disk. Once committed, the changes pages remain in RAM until removed via LRU rules.

Big transactions must funnel through the Log Buffer. As mentioned, a larger log buffer will reduce disk I/O. Only a large commit would present a bottleneck.

You may want to look into other InnoDB options to configure.

innodb_flush_method : This dictates how flush is handled and whether or not to allow the OS to cache writes to InnoDB (You must experiment between O_DSYNC and O_DIRECT)
innodb_read_io_threads (Use the Max : 64)
innodb_write_io_threads (Use the Max : 64)
innodb_io_capacity (use higher values like 5000 or 10000, but the hardware will top it out for you, In other words, let the hardware decide the IO capacity once you assign a huge number)
innodb_thread_concurrency : Default value is 0. I learned firsthand from Ronald Bradford at Percona NYC 2011 to leave this number at 0. It lets InnoDB decide the best number of concurrency tickets it could handle)

I have other posts about optimizing InnoDB for further research

Optimizing InnoDB default settings
How do you tune MySQL for a heavy InnoDB workload?
Insert-heavy InnoDB table won't use all my CPU
How to improve InnoDB DELETE performance?
MySql - Clean ibdata1


The transaction log for the database fills up overnight everyday Lately my server is overloaded because of daily running SQL jobs, my transaction log grows very large throughout the night and I run out of disc space.  The short term solution was to shrink the log every hour, because even at every 4 hours it seemed to still fill up the whole server (as the shrink job might get blocked from running by other scripts).  Is there any long term solution to this so I can go back to backing up/shrinking the transaction log once per day?

I think your problem could actually be that you only want to backup and shrink your transaction log once per day.
If you don't need Transaction Log Backups to restore to any point in time, then switch your recovery model to "Simple" and stop doing log backups altogether.
If you do need Transaction Log Backups, then you usually have a need to take more than 1 in a day. SQL Server will track every change in the Transaction Log until the Transaction Log is backed up, so maybe try scheduling these to run more often.
Before you backup and shrink the file, or whatever you're doing each day, you can run the following command to tell you what it's waiting for:
SELECT name, log_reuse_wait, log_reuse_wait_desc FROM sys.databases;
Given that backing up the transaction log fixes your problem, that's where I'd put my money. But other things that can cause the Transaction Log to continue to grow are Mirroring/AlwaysOn replicas falling behind, long running transactions (which will affect Simple recovery model as well). The above query will help you find the problem.
Finally, having a large transaction log is not actually a problem. You shouldn't shrink the file every night, it's just going to grow again tomorrow. If log_reuse_wait = 0 then just let it be. If log_reuse_wait is not 0 then check before you clear the log what it's waiting on. Microsoft Docs has the full list of codes.


Is using the MongoDB _id field index as a time range query possible? We are storing lots of data in our Mongo DB collection. We do not customize the _id field value. I have an index on a timeStamp field in this collection. This field itself records the insertion time of the record only. Given that the ObjectID in the _id field contains a timestamp can I use MongoDB _id field default index for the same queries?

Would using the _id field be as effective as the separate timestamp index?

I am trying to improve my insertion performance as this collection takes heavy write load.

In addition to the insertion load, we run an aggregation on this collection every 5 minutes based on time range.

Yes, of course you can use _id to query created time. You just need to query "range" where lower limit is wanted time (with needed resolution, msec, sec, min...) where rest of bytes (_id) are zero and upper limit with rest of bytes set to FF.

I'm not sure is it most effective way or is saved db space worth of it...


Can I reorganise index on a sql server 2005 database without performance hit on production server I would have preferred to run online index rebuild instead but our production sql server (2005) is standard edition. Hence I think I am left with reorganising index and update statistics after that. Please correct me if I am wrong. But I am wondering if I can do this without any performance hit or not.

Correct: online index rebuilds are only supported in the Enterprise edition engine.

ALTER INDEX ... REORGANIZE is always an online operation, regardless of your edition of SQL Server, even on 2005.

Doing this while the system is in operation will certainly affect performance as far as physical disk workload, but it should not affect read or modification operations in terms of locking. See the link above for more details.

Since doing a REORGANIZE is online, you can choose to schedule it more frequently, perhaps even taking advantage of periods of slower activity. The key idea would be to do it more frequently, but do less work each time, as compared to, say, doing it once/day or once/week.


MySQL group replication - how to find queries causing replication lag? How can we determine which queries are causing a set of MySQL servers running Group Replication to have replication lag?
We have a MySQL Group Replication cluster with 9 servers, and we are currently experiencing replication lag (i.e high values in performance_schema.replication_group_member_stats.COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE for several servers). We're using MySQL Community version 8.0.19.
We noticed that neither slow queries nor frequent queries are necessarily correlated with lag across servers. We have also noticed that some specific joins we had in our application were causing lag spikes and, once we removed them, the lag reduced. This is, however, not the case for all joins.
Is there a tool or a methodology we can use to find the queries causing the lag?

Turn on the slowlog on both machines.  What version of MySQL are you using?  Here are good settings to have for a relatively new version:
log_output = FILE
slow_query_log = ON
slow_query_log_file = (fullpath to some file)
long_query_time = 1
log_slow_admin_statements = ON
log_queries_not_using_indexes = OFF
log_slow_slave_statements
log_slow_extra = ON

More:  http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog
A possible cause of what you are seeing:  An ALTER that takes some time and hits tableA is replicated.  Meanwhile, other things are happening (read or write) to tableA.  The above settings will probably catch the ALTER.
Another thing to look for (but hard to see) in the slowlog:  A bunch of queries ending at about the same second.  This usually means that one of them took a long time and was blocking the others (either directly or indirectly).
In any case, use pt-query-digest; its default sort is to put the "worst" queries first.  Then...

The blocker may be among the first few.
The first few are likely to be queries that you should work on improving -- better indexing, reformulation, etc.
Sometimes a "worst" query is a very fast query that was blocked by the blockage; that is it was a "victim", not a "villain".  Example:  UPDATE ... WHERE primary_key = 123 taking second(s) or even minute(s), not milliseconds.


Good Natural Key For a Physical Mailing Address I'm trying to figure out what a good natural key for a Physical Mailing (PO Box) address would be. I haven't designed the fields for the table yet, and initially I was going to go with a surrogate key and a text field for the address, allowing free input. But the other dev I'm working with is interested in having a natural key (just for this one table).
However, it's not quite making sense to me, because I'm not sure how to ensure, with an address, properly input data (when keyed in either on create or to do some kind of manual lookup). I know that you can often format the same address in more than one way.
For example:
Suite vs Ste.
Apt. 36 vs #36 vs Unit 36
1110 West 300 South vs 1110 W 300 South vs 1110 W. 300 S. vs....

All valid, but all different. Granted PO Boxes are more standardized, but I feel like it may still suffer from some of the same issues.
Any thoughts on the matter would be greatly appreciated!

As mentioned in the comments, free text fields make it tough to create a primary key on. And for something with as much variability as an address field, it's just not reasonably possible by itself.
This is the type of problem where if you must create a primary key for it, then you will need to use strong validation and/or an address standardization API like this one from the USPS, in your application layer before it hits the database. Even doing that, I don't think you'll have a 100% perfect solution, but it does make it more reasonably possible to then create a primary key on an address field.


How to create shared folder between my on-premise Windows Server and Azure VM Server? I'm trying to migrate my on-premise SQL Server to Azure VM using 'Microsoft Data Migration Assistant'. But it is asking for shared folder to be accessed by both source and target. How to achieve this?

If you are new to Azure I would suggest ticking the "Copy the database backups to a different location that the target server can read and restore from" and manually copy the backup files to the target.

I tried setting up an SMB share in Azure to use as the backup location as per this article. The issue I ran into was that Azure does not allow anonymous access to files share (the request for the feature has been under review for more than 2 years)and you also can't enter the credentials in the Microsoft Data Migration Assistant, so this route doesn't seem like a solution at the moment.

The only other way I can think of is creating a permanent link with either ExpressRoute or VPN and then creating a share that can be accessed by both subnets.


Performance of SQL Server indexing I want to see the performance differences between an indexed vs non-indexed column when running the same query.

The table I've created (FileCollection) is very simple and has the following 3 columns

Id (int) - Primary key which auto generates the number.
FileName (nvarchar(MAX)) - Contains a file name.
Description (nvarchar(MAX)) - Contains a description of the file.

The query I'm using is:

SELECT * FROM FileCollection WHERE FileName='readme.txt'

I've populated the database with more than 100k records. Using the SQL Server Profiler tool, I initially ran the query with no index on the FileName column and again when adding the index to FileName column. However, I cannot see any difference in performance.

Both tests average around the same stats:
CPU: 500
Reads: 13,000
Writes: 0
Duration: 200

Can anyone help me figure out why? I would assume 100k records is enough to show some difference but do I need more records?

TIA

Edit:
I've now changed the FileName column to nvarchar(255) but still the problem exists. BTW, I'm using SQL Server 2008 R2.

I'm pretty sure you're leaving something out:

(n)varchar(max) fields CANNOT BE INDEXED

From MSDN:

  Columns that are of the large object (LOB) data types ntext, text,
  varchar(max), nvarchar(max), varbinary(max), xml, or image cannot be
  specified as key columns for an index. Also, a view definition cannot
  include ntext, text, or image columns, even if they are not referenced
  in the CREATE INDEX statement.

Did you get an error when you tried to create the index?

Both your queries are the same because they are the same - since you can't index that field, they are both creating table scans.


Redirecting Report Server connections using host file I am connected remotely to a server called New_Server and trying to connect to the Report Server which is installed on it by referring to another server:

I've changed the host file on NEW_SERVER to POINT to ITSELF when referring to another server: if I put in Old_Server name, it SHOULD point to itself; NEW_Server

So I put in this link: http://New_Server/ReportServer/ - it works
But then when I put in the link: http://OLD_SERVER/ReportServer/
then it asks me to RE-AUTHENTICATE, which I do, but it doens't seem to respond at all. It just seem to get my input and do nothing with it (since I am reprompted again and again for the username/password).

Issues:

Why should it even ask me to re-authenticate; should it NOT just replace "Old_Server" with "New_Server"? So shouldn't it just connect to
http://New_Server/ReportServer/ ??
Why does it ask for, then ignore authentication?

It's worth mentioning that I've blocked the OLD_SERVER using firewall. But again, this should not matter since I changed the host to refer to itself when it's trying to access OLD_Server

My research:

I've read this blog about changing URLROOT, and again this is not
    something I want to do or am trying to achieve. I am trying to fool
    the server to think it's connecting to OLD_SERVER but then
    referring it to NEW_SERVER
Using CNAME/DNS Alias is not an option
https://stackoverflow.com/questions/19447582/windows-8-ie-cannot-reach-sites-listed-in-hosts-file - I've done the troubleshooting steps from that question.
https://support.solarwinds.com/Success_Center/Network_Performance_Monitor_(NPM)/Knowledgebase_Articles/Edit_the_host_file_to_add_static_entry_for_hosts_that_are_not_resolving_by_DNS

For anyone who's facing the same issue, following the steps in this article fixed my issue. For some reason, it's required to make an adjustment in registry to allow for this use. Also, remember to add report server site as INTRANET (in Internet Option/security).


Start MongoDB With Authentication Enabled Well I am new in MongoDB. Just started using 2 tutorials from tutorialspoint and w3resource. I am really enjoying learning it. I think it is really the future of database management. However I have faced some problems and I want to clear my conceptions on those issues. Here is the first question of the stack of noted questions. Any help will be highly appreciated.

I use Ubuntu 14.04 LTS (32bit machine and O/S). My MongoDB version is 2.6.11. I use terminal to toy with MongoDB. According to MongoDB website I start the server mongod using this command...

$sudo service mongod start

Now to start the server with authentication enabled what should be the command? I tried using

$sudo service mongod --auth start

but it doesn't work.

Regarding the issue described in above mentioned post I suggest you to check  authentication options into mongodb configuration file.

# Turn on/off security.  Off is currently the default
#noauth = true
auth = true

And after it initialize mongod service using auth mode.

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

Auth configuration option should be set to true and accordingly mongo service will start in authentication mode.


How to create several builds of PostgreSQL? I'm building PostgreSQL(version 9.3.1) from source code in Ubuntu. I'd like to create several builds of Postgres, like pg1, pg2, etc. How can I do it? Thanks in advance.

If you use the --prefix=PREFIX option while building from source, won't that help? You would also need to look at other options such as --with-pgport=NUMBER to ensure that you don't end up forgetting to change the port in postgresql.conf. Please have a look at http://www.postgresql.org/docs/current/static/install-procedure.html for other options you might want to change.


Replace all Numeric Values with Single Character I have a value in a SQL table like the below:

80400365_SwapOpenTrd_20180831.csv

I want to replace the numeric values so it is returned as this:

%_SwapOpenTrd_%.csv

I tried the below code but there are groups of % characters returned...

%%%%%%%%_SwapOpenTrd_%%%%%%%%%csv

What do I need to change here to get desired result?

http://sqlfiddle.com/#!18/9eecb/33261

DECLARE @Temp nvarchar(150) = '80400365_SwapOpenTrd_20180831.csv'

Declare @KeepValues as varchar(50)
Set @KeepValues = '%[^a-z_% ]%' -- keep underscore and space
While PatIndex(@KeepValues, @Temp) > 0
    Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '%')

select @Temp

Based on your latest comment, I made few changes to your original example.  The following logic prevents repetitive % from being stuffed.

See if this works for you.

DECLARE @Temp NVARCHAR(150) = 'Numara_03_09_2018_07_41_04_OP_NIP_CSA_5739038.XLS'
DECLARE @KeepValues AS VARCHAR(50)

SET @KeepValues = '%[^a-z_%. ]%' -- keep underscore and space

WHILE PatIndex(@KeepValues, @Temp) > 0
BEGIN
    SET @Temp = CASE
            WHEN substring(@temp, PatIndex(@KeepValues, @Temp) - 1, 1) = '%'
                THEN Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')
            ELSE Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '%')
            END
END

SELECT @Temp

Result

Numara_%_%_%_%_%_%_OP_NIP_CSA_%.XLS


Convert all the data of table (which have both encoding data) into single encoding either utf8 or latin1 We have table which consists of two different encoding data (utf8 and latin1) which has been inserted from two different use cases of application. We are getting broken strings issue for other languages text if we get the data in one encoding.We need to convert total table data into single encoding.

Eg: Table X
id name data encoded
1 ébarber - utf8
2 à gogo - latin1

if we use "latin1" connection charset, we get issue with "ébarber"(broken strings). if we use "utf8" connection charset, we get issue with "à gogo"(broken strings).

How can we convert this table data into single encoding either utf8 or latin1?
Please share your thoughts to fix this issue.

Depending on what RDBMS you are using:

You can try altering the Latin1-encoded column to use an encoding of UTF-8, if the RDBMS supports such an operation.

If you are using MySQL, you should look at this related Question on S.O.:
Converting mysql tables from latin1 to utf8
You can try:

Adding a new column with an encoding of UTF-8
UPDATE new_column = Latin1_column  (I am assuming that the RDBMS would be able to implicitly convert since it knows both encodings)
DROP Latin1_column
rename new_column to be former Latin1_column's name


Proper Database Partitioning I had asked a previous question "Proper technique for storing users event data" and the correct answer in my opinion was to create a database partition. Now from what I have read on it there are different ways to partition, but for this question we are going to assume we are doing a horizontal key partition using a date field on a RDBMS such as MySQL... (if you have objection or argument to this, by all means contribute).

The base question is how do you know how many partitions to create?

I understand this is a pretty open question being that it would also heavily rely on what hardware you are running this on, yet either way there should be some guidelines that would point to where the better performance would be, or a proper way of doing so, or even how would you judge something like this? Most all of the documentation I have found use terms such as "large", "big", "a lot"... what do these terms mean when it comes to access speed, or row count, efficiency vs storage or required hardware. Is it something where from trial and error or observed performance where if things start getting a little rough you would just add a partition or two?

I'm very interested in opinions and contradictions to this seemingly common hurdle in large DB schemes.

Thanks

I will tell you my experience in defining the terms of "large", "big", "a lot":

a lot is a database that takes around 400 GB for the data of a full month (custom logging information from all our web apps)
big is a table in this database that contains much of this space :-).. about half of this size (200 GB a table, from what I kinda remember)
large is the data in a full day (about 12-15 GB) - which means a partition of that magic table

This is not a rule or a best practice.. but when you feel that your current indexing strategy is a bit lost and nothing seems to make your queries faster, I believe that it's time to take into account the partitioning.


How to best store a timestamp in PostgreSQL? I'm working on a PostgreSQL DB design and I am wondering how best to store timestamps.
Assumptions
Users in different timezones will use the database for all CRUD functions.
I have looked at 2 options:

timestamp NOT NULL DEFAULT (now() AT TIME ZONE 'UTC')

bigint NOT NULL DEFAULT

For timestamp I would send a string that would represent the exact (UTC) timestamp for the INSERT moment.
For bigint I would store the exact same thing, but in a number format. (time zone issues are handled before millis is handed over to the server, so always millis in UTC.)
One main advantage with storing a bigint could be that it would be easier to store and to retrieve, as passing a correctly formatted timestamp is more complex than a simple number (millis since Unix Epoc).
My question is which one would allow for the most flexible design and what could be the pitfalls of each approach.

Store timestamps as timestamp, or rather timestamptz (timestamp with time zone) since you are dealing with multiple time zones. That enforces valid data and is typically most efficient. Be sure to understand the data type, there are some misconceptions floating around:

Time zone storage in PostgreSQL timestamps
Ignoring timezones altogether in Rails and PostgreSQL

To address your concern:

passing a correctly formatted timestamp is more complex than a simple number

You can pass and retrieve a UNIX epoch either way if you prefer:
SELECT to_timestamp(1437346800)
     , extract(epoch FROM timestamptz '2015-07-20 01:00+02');

Related:

Truncate timestamp to arbitrary intervals
Aggregating (x,y) coordinate point clouds in PostgreSQL

If you want to store the current timestamp with writes to the DB, use a timestamptz column with default value now(). The system time on the DB server is typically much more reliable and consistent than multiple clients handing in their respective notion of what time it is.
For INSERT it can be as simple as:
CREATE TABLE foo (
  ... -- other columns
, created_at timestamptz NOT NULL DEFAULT now()
);

And just don't write to that column. It's filled in automatically.


simple question about group by and custom groups Imagine a MySQL table that only has 2 columns: an id and a name of a color.

With this query, I know how many id's I have for each color:

SELECT color_name, count(id) FROM color_table GROUP BY (color_name)

red:    10
blue:   5
yellow: 3
green:  1

My question is, is there a way I can specify to the "group by" some custom groups?? I mean, is there a query that results in this:

red: 10
colors different than red: 9

Directly using GROUP BY, no. You can still get the results you want, though, and there are many ways to accomplish it. Such as UNION:

SELECT 'red', COUNT(*) FROM color_table WHERE color_name='red'
UNION
SELECT 'colors different than red', COUNT(*) FROM color_table WHERE color_name <> 'red'


Oracle shared memory exception ORA-04031 I'm trying to establish an Oracle database connection. It throws the following exception:

ORA-04031: unable to allocate 4048 bytes of shared memory
  ("shared pool","unknown object","sga heap(1,0)","kglsim heap")

I have tried connecting the database through JDBC as well as  SQL Developer, however it throws the exception in both case.

How can I fix this?

Oracle 10g or earlier? Probably you already restarted the database. Then you can check SQL Area:
    SELECT sql_text, parsing_schema_name, executions FROM v$sqlarea ORDER BY executions desc;
If there are tens of thousands SQLs with 1 execution - you are having shared pool fragmentation. To avoid future exceptions either rewrite SQLs (usually not using binding variables causes SQL area bloat) or upgrade to 11g. It better handles shared pool fragmentation.


How to avoid certain characters in MySQL full text search? I have a column named parcel_number of varchar type. It contains values like ab-cd-ef and uvwxyz.  A search for abcdef or uv-wx-yz does not return those records.

How should I write the query to meet this requirement?

This situation seems to be the result of a bad design decision.

As this column is not storing human readable content, but is to be used as an identifier, the content of this column should have a uniform structure, either with or without the dashes, but not mixing them! You'd also have a hard time having a UNIQUE constraint on this column...

I'd recommend stripping the dashes from the table:

UPDATE table SET parcel_number = replace(parcel_number,'-', '');

MySQL replace reference

Then you only have to strip the dashes from the the search string too. I assume ab-cd-ef is the same as abcdef. Do note however that doing so means a-bcd-ef and ab-cd-ef will be treated as being the same.


SSMS 2016 Query Store: Missing Index Details error: DocumentFrame (SQLEditors) Each time I try and view missing index details in Query store I get the following error:

  DocumentFrame (SQLEditors)

  Program Location:    at
  Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ShowPlan.ShowPlanControl.OnMissingIndexDetails(Object
  sender, EventArgs a)    at
  System.ComponentModel.Design.MenuCommand.Invoke()    at
  Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ShowPlan.ShowPlanControl.Microsoft.VisualStudio.OLE.Interop.IOleCommandTarget.Exec(Guid&
  guidGroup, UInt32 nCmdId, UInt32 nCmdExcept, IntPtr vIn, IntPtr vOut)

Is anybody else experiencing this? Is this something I can fix or is it a bug?

SSMS version 13.0.16106.4

It's a bit frustrating as I can't view the end of the create index recommendation.

It is happening to me from Query Store query plan screens on SSMS 13.0.16106.4 as well.

Workaround that I found, until they fix it:

Press "New Query" button for a blank new tab
Switch back to the Query Store tab and right-click, select "Missing Index Details" again
It'll open up the missing index details in a third tab

When you look at another, you may have to repeat the steps, even if you still have the blank tab open.


DB2 - How to get value for: Transation Per Minute parameter for DB? Hi is there some easy way to find out what is transaction per minute value for the database?
Should I use db2 get snapshot?
We are using DB2 9.7 ESE on Linux

Thank you in advance

There is no "per minute" metric available, you will need to query the monitor repeatedly and divide the delta of the metric you're after by the time interval since the previous query. The following two queries will return the total number of application units of work and the total number of completed units of work, respectively.

SELECT
   TOTAL_APP_COMMITS + TOTAL_APP_ROLLBACKS
FROM SYSIBMADM.MON_DB_SUMMARY

SELECT
   SUM(TOTAL_APP_COMMITS) +
   SUM(INT_COMMITS) +
   SUM(TOTAL_APP_ROLLBACKS) +
   SUM(INT_ROLLBACKS)
FROM TABLE(MON_GET_WORKLOAD('',-2))

There are other monitor views that you may find useful: http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.rtn.doc/doc/r0023485.html#r0023485__mon_23485

You can obtain the same information using the GET SNAPSHOT command, but the use of old-style snapshot monitor is not recommended. For example, you can try this:

db2 get snapshot for database on sample | grep -Ei "commit|rollback"


ALTER SEQUENCE to use schema? Consider tables cats and dogs in ani animals schema.
During sequences creation, sequence for cats.id used schema, whereas sequence for dogs.id didn't use schema:
create sequence ani.cats_id_seq
create sequence dogs_id_seq

How can I modify the dogs_id_seq so it also uses schema?
For example: ALTER SEQUENCE dogs_id_seq RENAME TO ani.dogs_id_seq this won't work.

See the documentation:
ALTER SEQUENCE dogs_id_seq SET SCHEMA ani;


Adding users to a list and sort by category? I'm using SQL-Server 2012 and I would like to know how to solve this program.

So:

I want to create a page that allows admins to manage a project, and part of it, would be to add members/employees to the project (Project Table).

Now, I have another table, users, containing all the users on the system.

Each project has a different department (departments can be added - for example, hardware, software, accounting etc).

What I would like to solve is.. how would I do so as to create a new department and add employees to the project i.e. how will have the tables be structured, and what tables are to be created?

Steps

Add user to a project
Assign user role
Assign user department
Create department (will this have to include creation of another table?)
Add users to that department (will this have to include creation of another table?)

How can can I solve this in terms of database design?

The process of data modeling is complex enough that you will need to go read some books about it to learn the details.  The subject goes way beyond what can be covered here.  However, to get you started, you can follow these (drastically oversimplified) steps:

Step 1: Make an Entity-Type for Each Tangible Thing

Start with making a logical data model.  For each thing your system cares about (people, places, things, events) make an entity-type. For your system it might look like this:

Step 2: Identify Your Relationships

Look at your logical entity-types and determine how they relate to each other.  Remember to focus on the relationships that matter to your system.  Don't get bogged down looking at relationships that are possible or that are real, but which are not pertinent to your system.

Step 3: Transform Your Logical Model Into a Physical Model

Once you have a logical model that covers the objects and properties that are of interest to your system, you need to figure out how the logical model can be implemented using physical tables in your database.

This involves at least two very important steps:

Resolving many-to-many relationships into intersection tables
Normalizing your tables

In the case of your system, there are some many-to-many relationships that can be resolved.  Normalization involves looking at table fields.  Since we don't know what your fields are from your question, we can't really get into that topic here.  Normalization is very important, however, and you should always make sure that you start from properly normalized tables and de-normalize only when you have very good, well considered, reasons to do so.

Your model with m:n relationships resolved would look like this:

Note that there isn't enough information in your question to be able to say for sure that this model is the start of what you need.  I've had to make a number of assumptions about what your entities are and how they're related.  This site isn't meant to be used for collaborative design in any case (see the FAQ) - but I've tried to give you a quick outline of the process so you have someplace to start.  If you get stuck with a specific design issue along the way, then you can definitely come back to ask for advice about it here on dba.se.


add RECOMPILE for specific query server wide I've SQL 2017 and i have a query performance problem. i did try to play with all those trace flag (4199,4136,4137,9481)server wide and play with the indexes and stats and the query is still running slow. the only thing that resolve the issue is if i add the OPTION  (RECOMPILE) to the query. my problem is that i can't add it to query itself that comes from the ERP program.can i use it server wide for specific query?

You can use a plan guide. What type of plan guide to use depends on whether the query is in a proc (OBJECT) or not (SQL). Example:
sp_create_plan_guide
@name = N'myGuide',
@stmt = N'SELECT col1, col2 FROM MyTable',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (RECOMPILE)'

Documentation for plan guides here: https://docs.microsoft.com/en-us/sql/relational-databases/performance/plan-guides


MySQL slow queries - Fast using PHPMyAdmin , low CPU and RAM usage I have a site running on Amazon EC2 , which runs fast and contains some reasonably complex MySQL queries.

Part of that site is an API which serves out data to another site. These queries are very simple (one is literally returning one row from a table containing only that row).

Making these API calls from the external domain results in a 10-20 second delay. The data always returns fine, no errors are thrown. Nothing is shown in the logs.

Running any of these queries locally in PHPMyAdmin , they complete in around 0.007 seconds .

CPU load is 20%-30%.
RAM usage is about 60%.

My problem is similar to:

This

I've added 'skip_name_resolve' to my my.cnf. This made no difference.

There is a lot of talk about MySQL and DNS , but the docs around that didn't really help me.

This seems like a problem with a simple solution , but I cannot work out what it is.

EDIT1.

To clarify , my MySQL queries are very very basic. One of them is literally just

SELECT * FROM aboutPage

Where aboutPage contains 1 row and 5 columns of tiny data. This runs lightning quick as expected in PHPMyAdmin locally , but has a slow load when accessed remotely.

The main app that runs the API also runs fast . Its literally just when trying to access data from another IP/DNS that its slow.

You need to identify which queries are taking some time to execute and optimize accordingly. By default mysql does not log slow queries, so you have to add the following to your mysql config file -

log-slow-queries
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 10     // in seconds - you can replace this with another number.

For any mysql performance issues, it is a good idea to use mysqltuner


Difference between FIND_IN_SET and LIKE Is there a difference in behavior or performance in choosing FIND_IN_SET over LIKE for a operation that looks like:

SELECT * FROM my_table WHERE column_1 LIKE '%string%';

vs

SELECT * FROM my_table WHERE FIND_IN_SET('string', column_1) > 0;

This is done on Percona Server.

I would use LIKE for one very good reason. From the documentation here - "This function (FIND_IN_SET) does not work properly if the first argument contains a comma (“,”) character".

You could always use an EXPLAIN  to check on performance - but simply for the reason above, I would not use it.

[EDIT]

You could always use LOCATE(). I strongly recommend that you don't use MySQL's SET datatype. It's non-standard and illogical to say the least.


Get Stored Procedure Result Column Types I'm trying to get whatever a stored procedure returns, with the column names and their types. I can do this with tables but I couldn't figure it out for stored procedures. I tried the sp_columns but only managed to make it work for tables.

I have also tried something like this but I'm not sure what I am supposed to match with what.

SELECT *
FROM SYS.PROCEDURES (NOLOCK) AS AA
INNER JOIN SYS.SCHEMAS (NOLOCK) AS BB ON (AA.schema_id = BB.schema_id)
INNER JOIN SYS.COLUMNS (NOLOCK) AS CC ON (AA.object_id = CC.object_id)

For example:

USER_ID VARCHAR(200)

I only need the names of the column names a stored procedure returns and their data types. SQL Server version is 2014, if it matters.

Any ideas? Thanks.

You should be able to use the new system stored procedure sp_describe_first_result_set for that - see the MSDN docs for details:

EXEC sp_describe_first_result_set N'YourStoredProcedureNameHere'


Is it possible to JOIN to a Primary database from a Replication instance? I have a database on a Replication server that doesn't exist on the Primary server.

There are stored procedures on the Replication server's DB that join to tables in other databases on the Replication server. These should be joining to tables on the Primary server.

Is this possible? If so, how?

This is an example of what it's currently doing:

UPDATE  c
SET     CRP_Id      = be.EventId
FROM    #TempEvent be
JOIN        CRP.dbo.Payments c -- *should* be joining to CRP on Primary, not Replication
    ON  c.CRP_TransactionId     = be.SourceTransactionId
WHERE   be.EventTypeId  in  (
                                @MyEventTypeId
                            );

You should use linked server and use four part naming linkedservername.dbname.schema.object

Also refer my answer on efficient way of pulling data when using linked server


Database structure, relational or data warehouse? Good morning,

Im having some issues with how my database should be laid out and would appreciate some guidance. I have several tables (rainfall data), each containing  the following columns:

Date (DateTime), Value_of_rainfall (Float)

Each of these tables are for a specific location.

It has been suggested i create another table with the following columns:

LocationID (tinyInt), LocationName(char(6))

and insert into the first table a new column called LocationID(tinyInt).

Now the confusion i have is regarding that the data i have stored in the rainfall data datatables. Its been suggested that all the data from each location be contained in one data table. The statistical analysis im looking to carry out on the info is very (as far as i have envisioned thus far) location specific and wont require querying multiple locations at once. A couple months worth of data for one location = nearly 3 million rows and im looking to set up long running calculations on the data. therefore would a data warehouse be more appropriate? If so, could someone give me some pointers on how i should lay it out?

Thanks for your time.

Note: Im using Sql Server 2008

It seems you want to aggregate location based statistics over time for rainfall.  A database structure like the one below would let you do that.  The 'data source' could be just a filename, or some indication as to where it came from.

create table DimDataSource (
       DataSourceID      int identity (1,1) not null
       DataSourceDesc    nvarchar (100)  -- May need unicode for file names
)
go

alter table DimDataSource
  add constraint PK_DataSource
      primary key clustered (DataSourceID)
go

create table DimLocation (
       LocationID        int identity (1,1) not null
       LocationDesc      varchar (50)
)
go

alter table DimLocation
  add constraint PK_Location
      primary key clusterd (LocationID)
go

create table DimDate (
       DateID           smalldatetime not null  -- 'Date' is a reserved word
      ,MonthID          int not null
      ,MonthDesc        varchar (15)
      ,QuarterID        int not null
      ,QuarterDesc      varchar (15)
      ,YearID
)
go

alter table DimDate
  add constraint PK_Date
      primary key clustered (DateID)
go

create table DimTime (
       TimeID           time not null  -- 'Time' is a reserved word
      ,Hour             int not null
)
go

alter table DimTime
  add constraint PK_Time
      primary key clustered (TimeID)
go

-- If the table is <50GB, don't bother with partitioning, but put a clustered
-- index on DateID or LocationID and DateID, depending on how you normally expect
-- to query the data.

create table FactRainfall (
       RainfallID        int identity (1,1) not null -- May need a wider type if >4B rows.
                                                     -- SSAS likes an identity column for
                                                     -- incremental loads
      ,DataSourceID      int not null
      ,LocationID        int not null
      ,DateID            smalldatetime not null
      ,TimeID            time not null
      ,Rainfall          float
)
go

-- Add foreign keys as necessary

Populate the dimensions with the appropriate list of locations, date ranges, time of day to the right grain and one data source record per file.  This table will also allow you to put a cube over the top, or can be flattened with a view, which will help people using tools like Excel or stats packages to get and use the data.