Is it normal for Snapshot Replication job to be constantly running? Our SQL Server 2016 already has Merge replication running successfully for some tables in 2 databases. We then needed to restore Snapshot replication for another set of tables between same pair of databases. I have run a T-SQL script from old server to restore Snapshot publication/subscription. Everything went fine, publication and subscription were created. I have manually run the Snapshot Agent for the first time and then manually triggered synchronization via the View Synchronization Status dialog.

All articles in the publication had Drop and Re-Create setting for the destination tables. After initial synchronization finished I have compared source and destination tables and have confirmed number of rows to be equal. Just what we needed.

However, I am surprised that after delivering the initial snapshot the replication process didn't finish like it does for example for Merge replication. Instead, the View Synchronization Status dialog reports synchronization as running but with No replicated transactions are available message, see below:

I then went to check synchronization history for this subscription and found out that the actual synchronization has been running for already 20+ hours without actual rows/tables copied. Looks like it has been waiting for new changes/snapshots all this time, see below:

The above image correctly reports bulk copying of changes around 3 am because the REPL-Snapshot agent job has been put on schedule to run at 3 am each day. However, from the SQL Server Agent Job monitor I see that REPL-Distribution, REPL-LogReader and REPL-QueueReader jobs for my snapshot publication are constantly running.

The question is: is the above behavior a normal one? Shouldn't Snapshot replication job just process a new snapshot and then quit? Or are we missing schedules for Queue and Log reader jobs too? I worry for not being able to free up Transaction Log if replication never finishes.

In the T-SQL script for this publication/subscription we had these lines:

exec [DB1].sys.sp_addlogreader_agent @job_login = null, @job_password = null, @publisher_security_mode = 1
GO
exec [DB1].sys.sp_addqreader_agent @job_login = null, @job_password = null, @frompublisher = 1
GO

Not sure if they cause the synchronization to be always running. Any insight on what is going on here will be very much appreciated.

This is correct state of Distributor Agent. After delivering from snapshot (which was created at scheduled time by Snapshot Agent) it triggered action that has finished as "Delivered Snapshot from the (...)". Delivery is performed by a Distributor, which is always on, and looking for new actions (Snapshots).
So in reality there are two jobs for Snapshot replication: Snapshot Agent triggered by schedule and Distributor Agent waiting (active) for any new actions. "No replicated transactions are available" means here, that there are no new snapshots to distribute.

By design Distributor Agent needs to run constantly and listen for new snapshots which are scheduled in Snapshot Agent.


Select entries based on a combination of columns I have a table with a definition like this:

CREATE TABLE [dbo].[Contact]
(
    ClientNumber [numeric] (20, 0) NULL,
    ContactDate [Date] NULL,
    Name [text] NULL,
    AdditionalInformation [text] NULL
)

This table is filled with information for a single month. Each client may have multiple entries for a single day. Meaning there might be entries like this:

42  2015-12-09  Felix       called today
42  2015-12-09  Fexclicx    ...

My goal is to get all client results from the table with the condition that there should be only one entry a day per client number. I do not care which of the (here) two entries is displayed, so something like top 1.

So far I tried to achieve this with a distinct:

select distinct ClientNumber, ContactDate, * from Contact

Unfortunately this does not limit the results to show a client entry for a specific date only once. They still all do appear. Also the problem is that there are no id's in this table so I cannot work with that.

How could I achieve getting all entries but only one entry for one client per day?

To create sample schema and data

 IF OBJECT_ID('tempdb..#Contact', 'U') IS NOT NULL
        DROP TABLE #Contact ;

    CREATE
    TABLE   #Contact
            (
            ClientNumber [numeric] (20, 0) NULL,
            ContactDate [Date] NULL,
            Name [text] NULL,
            AdditionalInformation [text] NULL
            );

    INSERT
    INTO    #Contact
            (
            ClientNumber
            ,ContactDate
            ,Name
            ,AdditionalInformation
            )

    VALUES  (111,'2016-03-11','A','A'),
            (111,'2016-03-11','B','B'),
            (112,'2016-03-11','C','C'),
            (112,'2016-03-12','A','A'),
            (112,'2016-03-13','A','A'),
            (112,'2016-03-13','B','B'),
            (112,'2016-03-14','A','A'),
            (113,'2016-03-14','B','B'),
            (113,'2016-03-14','C','C');

Actual query

;WITH   CTERowNumber
AS      (
        SELECT  ROW_NUMBER() OVER (PARTITION BY ClientNumber, ContactDate ORDER BY ClientNumber DESC) AS RowNumber
               ,ClientNumber
               ,ContactDate
               ,Name
               ,AdditionalInformation
        FROM    #Contact
        )

SELECT  c.RowNumber
       ,c.ClientNumber
       ,c.ContactDate
       ,c.Name
       ,c.AdditionalInformation
FROM    CTERowNumber c
WHERE   c.RowNumber = 1 ;

To return

RowNumber   ClientNumber    ContactDate Name    AdditionalInformation
1           111             2016-03-11  A       A
1           112             2016-03-11  C       C
1           112             2016-03-12  A       A
1           112             2016-03-13  A       A
1           112             2016-03-14  A       A
1           113             2016-03-14  B       B


Why aren't my Postgres WAL segments (up to the point of turning on archiving) being archived? The pg_xlog directory for my master cluster looks like this:

-rw------- 1 postgres postgres  16M Jun 21 21:42 000000010000000000000001
-rw------- 1 postgres postgres  16M Jun 21 22:42 000000010000000000000002
-rw------- 1 postgres postgres  16M Jun 21 23:42 000000010000000000000003
-rw------- 1 postgres postgres  16M Jun 22 00:42 000000010000000000000004
-rw------- 1 postgres postgres  16M Jun 22 01:42 000000010000000000000005
-rw------- 1 postgres postgres  16M Jun 22 01:49 000000010000000000000006
-rw------- 1 postgres postgres  16M Jun 22 01:55 000000010000000000000007
-rw------- 1 postgres postgres  16M Jun 22 02:05 000000010000000000000008
-rw------- 1 postgres postgres  16M Jun 22 02:30 000000010000000000000009
-rw------- 1 postgres postgres  16M Jun 22 02:50 00000001000000000000000A

The pg_xlog/archive_status directory looks like this:

-rw------- 1 postgres postgres 0 Jun 22 01:49 000000010000000000000006.done
-rw------- 1 postgres postgres 0 Jun 22 01:55 000000010000000000000007.done
-rw------- 1 postgres postgres 0 Jun 22 02:05 000000010000000000000008.done
-rw------- 1 postgres postgres 0 Jun 22 02:30 000000010000000000000009.done

When I first turned on archiving 006 was the current segment, but why weren't 001-005 archived anyway? Last time I set up archiving on an existing cluster (which was yesterday - I've been experimenting), all log segments were archived when I turned archiving on, despite the pg_xlog directory containing more than 50 previous log segments.

Relevant settings:

wal_level = hot_standby
archive_mode = on
wal_keep_segments = 2048  # I know, right :)
archive_timeout = 3600
archive_command = 'rsync -aq --delay-updates --ignore-existing %p postgres@db-slave:/mnt/pgsql/'

Note: I did change all of these settings (including was_level, which was minimal previously) when I turned on archiving earlier. Could it be that my wal level wasn't sufficient to warrant archiving, or because there is a mismatch between the prior level and the current level?

This is most likely due to the fact that at least the 5th WAL segment was used for recovery to get the database into a consistent state after an immediate stop. I'm not sure why the WAL segments wouldn't still be useful (e.g., for point in time recovery).


Is it possible to break down query time? We have a setup of a few cloud servers that connect to a MySQL cluster on dedicated servers over Rackspace's Rack Connect network. We have noticed recently that the time for database queries (via PHP) have slowed down and as part of investigating we wanted to figure out whether it was an increase in the amount of time to process the query and generate results, or is it the time to transfer the data from the servers to database cluster that's increased?

Is there a way for me to figure this out? So far our timing is just a total overall for each query.

Not really.  But...

Use PHP's microtime(true) to give you precise times.  Then time trivial things such as SELECT 1.  This should give you a feel for the "network roundtrip time".  That is effectively added (once) to each SELECT that you do.

As for queries getting slower...  Look at whether the tables are getting bigger.  Look at the EXPLAIN plans to see what counts it estimates.  Look at the algorithms to see if they are O(1), O(N), or O(N*N), etc.

By "cluster" do you mean "NDB cluster"?  Or "Galera Cluster"?  Or something else?

Another tool is to do this:

FLUSH STATUS;
SELECT ...
SHOW SESSION STATUS LIKE 'Handler%';

This gives a precise count of the number of rows (index or data) that need to be read/written to perform the SELECT.  Track that over time to see if it is growing due to data size growth (as opposed to something else making things sluggish).


"mysqld" vs "server" in my.cnf I used to see server-related configuration in my.cnf located under [mysqld].
However, I now come across configurations that use [server] instead.
It looks like both of them are synonyms, at least on MySQL 8.0, I can see that options under both groups apply to the MySQL server.
Is this a new feature of MySQL 8.0? Is one of them preferred over the other? Is one of them deprecated?

According to the MySQL docs:

mysqld reads options from the [mysqld] and [server] groups.

I could trace this back to MySQL 5.0, so this has been there for quite some time.


Where does the logins users are stored in the SQL server “in which data base “ Where does the logins users are stored in the SQL server “in which data base “
In some softwares that requires the admin to create users/password for his users such as ArcGIS server the logins information are stored in a separate database so that the admin takes periodically backups just in case if the server was down to avoid losing the usernames and passwords. In the SQL server 2012 how can I manage to back up my logins since the moved the database form one machine to another preserves the users but the users cannot connect to the SQL server due to the loss of logins.

Where does the logins users are stored in the SQL server “in which data base “

First please note logins and users are both different thing. You need a valid login for a user to make changes into database. User is anyone authorized to perform ceratin actions on database while login is credentials they use to Log into the SQL Server.

  In the SQL server 2012 how can I manage to back up my logins since the moved the database form one machine to another

There are two authentications

Windows authetication

The credentials for which are not stored in SQl Server database and managed by windows/AD. There would be entry for windows autheticated logins in master database with respective SID but password would be with Active directory.

SQL Server authetication.

For 2nd we have password stored in hash format you can see it from sys.sql_logins. The information about SQl server logins are stored in master database and each login has SID repective to it. Only SA login has same SID no matter what server it is. That is why when you move database by backup restore mechanism  users are moved not logins and you finally have to create logins(if already not there) and map it to users. This is generally called as troubleshooting orpahned users

You can use sys.syslogins and sys.server_principal to gain more information about logins. Note that sys.syslogins is a legacy view.

If you also want to move your logins you have to take backup of master database and restore it on destination server. And this would be possible only if you have same version and SP on destination machine.


Any ideas what is wrong with this code, Error at or near Insert on a new function CREATE FUNCTION insert_m_a_k("odOrganizacijaId" uuid, "doOrganizacijaId" uuid) RETURNS boolean AS
$BODY$BEGIN
    INSERT INTO mapper_a_k (id, model_a_k_id, organizacija_id, k_a_k, k_a_k_ime, aktiven, deleted)
    SELECT  uuid_generate_v4(), model_a_k_id, doOrganizacijaId , k_a_k, k_a_k_ime, aktiven, deleted
    FROM mapper_a_k WHERE organizacija_id = odOrganizacijaId AND deleted = false;
END;$BODY$
LANGUAGE sql VOLATILE NOT LEAKPROOF;

PostgreSQL has several languages for functions, and it is easy to get confused.

PL/pgSQL uses BEGIN / END blocks.

But LANGUAGE sql functions consist only of SQL statements with no blocks ir other procedural elements.

So either change the language to plpgsql and add a semicolon or (better) remove the BEGIN / END block.


SSIS column cannot convert between unicode and non-unicode string data types I am transferring a table from SQL Server to PDA Server. This is my data flow:

ADO NET Source ---> OLE DB Destination

All of the columns in my source table are varchars and so when I created my destination table, I made sure that they had the same data types. So both tables have varchar data types for all columns.

However, I am still getting the error: Column "col1" cannot convert between unicode and non-unicode string data types. The weird thing is that if I replace my OLE DB Destination with a ADO Net Destination, it seems to work however, I want to use OLE DB so that I can specifying things such as rows per batch.

Can anyone help?

Double click the line between the source and destination and select the Metadata tab. That will list all of your columns and the data types SSIS has inferred.

I would compare that to your source and 100% ensure they align.

What can happen is that the first time you built your data flow, SSIS will set the metadata based on what the types are at that point. col1 is interpreted to be nvarchar so it'll set it as DT_WSTR of some length.

Time passes and you update the query and the source is now varchar, but still called col1. A varchar will always fit into an nvarchar so the shape of that column hasn't "changed" so SSIS doesn't need to redo it's internals.

If it is the case that SSIS is clinging to old metadata, my click path is to

Copy the existing query
Change my query to SELECT 1 as TotallyNewAndUniqueColumn
SSIS will then report that all my metadata is out of whack and would I like to autofix it, and I click Yes
Repaste the original query and lo and behold, the current metadata is captured.


Recommendation for RDBMS for large table I have been asked for recommendation on the optimal RDBMS for a large amount of data. The largest table would contain 2TB of data. My own largest table is only 400GB (mysql, performs very well).

The individual rows are going to be short, no blobs/etc, just a lookup table.

Is PostgreSQL nowadays performing better than MySQL? Can I ask DBAs with tables in this order of magnitude for their experience? Oracle might be considered as well. The available hardware is probably a standard linux box with about 64G ram.

The size of the tables really doesn't matter all that much. It's all about how well indexed the tables are and how many people are using the database at once.

In my case I've got clients with billions of rows in a single table where the table size is about 1 TB and another 2 billion row table which has blobs.  These tables crank without performance issues at all.

In this case the server has 1TB of RAM, 80 cores, and is running Microsoft SQL Server 2008 R2.  Granted not all that helpful if you want a *nix OS, but it gives you some ideas that most database platforms can handle just about everything.


SQL Server - How to prevent public connections? Today we found that two of our SQL Servers (both 2017 CU21) are accepting connections from any random domain account, even those that most definitely do not have a direct login or access via AD group membership.
On each server there is a user database that allows these random domain accounts to connect.
Based on the configuration we see, we would expect any random domain account (i.e. any that does not have a login on the instance) to get a login failure, and aside from that to also be blocked from connecting to the user database.
For these databases, the guest account is disabled, but for good measure we have executed:
REVOKE CONNECT FROM guest

This made no difference.
So two questions:

How can we turn off / prevent these public connections from succeeding?
What TSQL statement could show us the problematic configuration?

Things we have looked at so far:

The 'public' server role seems to have the default configuration, same as all our other servers where this is not an issue
The guest user definitely shows as disabled on all databases & servers
The 'public' database role has just the default permissions we would expect to see
It is only 1 user database (plus master & msdb & tempdb) on each server that is accessible to these random accounts -- all the other databases behave as normal, i.e. the account cannot connect to them.
The database Containment Type setting is 'None' is all cases.
There is no login present on either server that covers all domain users

Some context: the two servers are related -- they are the DEV & PROD servers for the same application. It is very likely this configuration was done deliberately for some historical reason -- but no-one working here now knows why, so we would like to disable this public access.

As per your description, it seems to me that CONNECT SQL (or perhaps CONNECT) permissions has been assigned to the public server role.


How can I backup & restore a single FILEGROUP in Sql Server 2008 Previously, on ServerFault I asked a question about backing up and restoring a Sql Server 2008 Filegroup.

Today, when I tried to RESTORE one of these FILEGROUP backups, I got the following error:-

Processed 1895080 pages for database 'XWing', file 'XWing' on file 1.
Processed 4 pages for database 'XWing', file 'XWing_log' on file 1.
The database cannot be recovered because the log was not restored.
The database cannot be recovered because the log was not restored.
The roll forward start point is now at log sequence number (LSN) 221218000000010400001. Additional roll forward past LSN 221218000000010400001 is required to complete the restore sequence.
This RESTORE statement successfully performed some actions, but the database could not be brought online because one or more RESTORE steps are needed. Previous messages indicate reasons why recovery cannot occur at this point.
RESTORE DATABASE ... FILE=<name> successfully processed 1895084 pages in 69.504 seconds (213.014 MB/sec).

I used the following Sql code...

alter Database [XWing] SET SINGLE_USER With ROLLBACK IMMEDIATE

restore database [XWing] filegroup = 'PRIMARY'
FROM  DISK = N'C:\Temp\XWing Manual Full Primary Filegroup.bak'
with
    MOVE N'XWing' TO N'D:\XWing.mdf',
    MOVE N'XWing_log' TO N'L:\XWing_Log.ldf',
    replace, recovery

So I'm assuming that the DB wasn't backed up correctly?

This is the script I sed to backup the PRIMARY FILEGROUP

BACKUP DATABASE [XWing] FILEGROUP = N'PRIMARY'
TO  DISK = N'F:\Sql DB Backups\XWing Manual Full Primary Filegroup.bak' WITH NOFORMAT, INIT,
NAME = N'XWing-Full Filegroup Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'XWing' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'XWing' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''XWing'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = N'F:\Sql DB Backups\XWing Manual Full Primary Filegroup.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO

Questions

Is that Restore syntax correct?
What about my backup syntax?

Cheers!

You need to restore the transaction logs so that you have a consistent database.  Currently the filegroup which you have restored is at a different point in time than the rest of the database.  Once the logs have been rolled forward and everything is consistent then you can bring the database online.

If you look at the data in the msdb database you'll be able to see which transaction log files you need to restore in order to bring the database online.

There's nothing wrong with your backup and recovery syntax.


Importing .dmp file into SQL developer: Do I need to import seperately for each table? I have a large .dmp file which I would like to import into SQL developer. This contains all the data I want to import across several Schemas.

Do I need to split this up into each separate table in order to import it?

I ask as in this guide it suggests importing on a table by table basis.

This import file is an export done with a tool such as exp73, exp or expdp.

The manner of importing data you are referring to when you have data in an excel sheet with coma separated value, SQL developer will create a large insert statement with the data for you and run it, which will populate a table.

What you want to do is to use oracle import tools such as impdp or imp.

Do you happen to have the export's log ? There might be a file with the same name that finishes with a .log

Here is an example of syntax on how to import specific tables of an Oracle export

impdp username/password@oracle_sid tables=table1,table2 directory=data_pump_dir dumpfile=dumpfilename.dmp logfile=import_exec_log.log

You will want to run the following query to find where your data pump dir location is and place the export in the corresponding folder :

 select * from dba_directories where directory_name ='DATA_PUMP_DIR';


Finding cause of high write IOPS on RDS Postgesql Our RDS Postgresql 9.6 instance has had an unusual instance of high write IOPS. Write iops went from an idle level of ~10 to over 4000 (our provisioned level) yesterday evening, and stayed at that level for 14 hours.  There was no matching spike of write IOPS on our read replicas, no rise in network traffic, and no read IOPS. I am looking for advice on diagnosing this, and preparing for future incidents.

This morning while IOPS were still high at 10am I looked for long running processes:

SELECT * FROM pg_stat_activity order by query_start asc;

I found two processes idle in transaction from the day before, and terminated them at 10:05. At that point there was no query_start older than 2 minutes, but the write iops remained at 4000. At 10:25 write iops returned to its normal level: I have no evidence that terminating the processes caused that.

Throughout the incident my logs (default AWS configuration) contain nothing but normal checkpoint logging:

2020-03-02 23:55:07 UTC::@:[6884]:LOG:  checkpoint starting: time
2020-03-02 23:55:31 UTC::@:[6884]:LOG:  checkpoint complete: wrote 85 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 1 recycled; write=22.426 s, sync=0.000 s, total=23.549 s; sync files=20, longest=0.000 s, average=0.000 s; distance=16419 kB, estimate=22527 kB

One cause I've considered is autovacuum freeze: cloudwatch shows maximum used transaction id reaching a peak of 199,999,946 then dropping. But:

I've tested with a manual vacuum freeze, and it causes near simultaneous write iops on source and replica.
The writes don't start until an hour after the drop in transaction id.
The data written is more than 2TB on an 80GB database.

This doesn't cause an outage since we're on provisioned iops, but I'd like to understand the cause.  Could this be autovacuum, and what logging options should I change from the RDS defaults to see what's going on?

To log autovacuum activity you can set log_autovacuum_min_duration parameter:

  Causes each action executed by autovacuum to be logged if it ran for
  at least the specified number of milliseconds. Setting this to zero
  logs all autovacuum actions. Minus-one (the default) disables logging
  autovacuum actions. For example, if you set this to 250ms then all
  automatic vacuums and analyzes that run 250ms or longer will be
  logged. In addition, when this parameter is set to any value other
  than -1, a message will be logged if an autovacuum action is skipped
  due to the existence of a conflicting lock. Enabling this parameter
  can be helpful in tracking autovacuum activity. This parameter can
  only be set in the postgresql.conf file or on the server command line;
  but the setting can be overridden for individual tables by changing
  table storage parameters.

One possible cause could also be temporary files used by some queries for sorting and/or joins: you can try to check in  pg_stat_database view columns temp_files and temp_bytes . You can also set log_temp_files parameter to 0 (instead of default -1) to log all temporary files creation:

  Controls logging of temporary file names and sizes. Temporary files
  can be created for sorts, hashes, and temporary query results. A log
  entry is made for each temporary file when it is deleted. A value of
  zero logs all temporary file information, while positive values log
  only files whose size is greater than or equal to the specified number
  of kilobytes. The default setting is -1, which disables such logging.
  Only superusers can change this setting.


Using Cursor to loop through a Table variable in SQL Server I have a parameter in the Stored Prod which gets some data in the format

1/1/2018-2/1/2018,2/1/2018-3/1/2018,3/1/2018-4/1/2018,4/1/2018-5/1/2018,5/1/2018-6/1/2018,6/1/2018-7/1/2018,7/1/2018-8/1/2018,8/1/2018-9/1/2018,9/1/2018-10/1/2018,
10/1/2018-11/1/2018,11/1/2018-12/1/2018,12/1/2018-12/31/2018

I have a function which splits the data based on , character and stores it in a table variable as shown below

declare @SPlitDates table(ItemNumber int,Item nvarchar(max))
insert into @SPlitDates
select * from dbo.SPlitFunction(@RequestData, ',')

After this I have to perform certain operations on the data range so I use cursors to loop through the temp table as shown below

DECLARE cur CURSOR FOR SELECT Item FROM @SPlitDates order by ItemNumber
OPEN cur
FETCH NEXT FROM cur INTO @monthStart
WHILE @@FETCH_STATUS = 0 BEGIN
--Some operation
End

The max data points that I will get in the temp table is the date range for 12 months.

My question is that could I be using something else apart from Cursors to improve performance or it doesn't matter when the dataset is really this small.

Thanks

If possible, just treat this as a set and consider if you can rewrite the --Some operation as a single batch.

It may require you to get smart with joins to other tables based on the date ranges - or perhaps you could split the date range further into StartDate and EndDate columns before doing your additional processing which might help with joins


Both READ_COMMITTED_SNAPSHOT ON and ALLOW_SNAPSHOT_ISOLATION ON I inherited a SQL Server 2012 instance that hosts Hyperion databases. I believe the Hyperion techs configured the system and they have both READ_COMMITTED_SNAPSHOT ON and ALLOW_SNAPSHOT_ISOLATION ON at the same time. This doesn't make sense to me. Does it make sense?

Also I am seeing 60-70% of the waits are Lock_M_X, which is much higher than i have ever seen. Wondering if this might be related to the above.

And I have been running 1 minute sp_whoisactive snapshots into a table and have not been able to capture a single X type lock. A reasonable number of S type locks show up.

Any suggestions?

From a previous answer to this:

  If you use ALLOW_SNAPSHOT_ISOLATION make sure you use SET
  TRANSACTION ISOLATION LEVEL SNAPSHOT in your code, otherwise you will
  not get any of the benefits.

  If you set SET READ_COMMITTED_SNAPSHOT ON, then there is no need to
  modify any code. MS SQL Server automatically applies snapshot
  isolation for that table.

Basically, with only ALLOW_SNAPSHOT_ISOLAION, you still have to specify the isolation level in the query/SP to make it use that isolation level.

With READ_COMMITTED_SNAPSHOT set to ON, the DB is automatically in that mode and there's no need to modify your queries.

ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT

In the answer I linked to above, there is a link to Kim Tripp's video on snapshot isolation.  It's pure gold.


How to find out the service accounts for each of my instances in the current server? I have the following query that gives me one line for each sql server instance installed in the current server:

--list the instances in the current server
--marcello miorelli
--16-sep-2016

            DECLARE @GetInstances TABLE
            ( Value nvarchar(100),
             InstanceNames nvarchar(100),
             Data nvarchar(100))

            Insert into @GetInstances
            EXECUTE xp_regread
              @rootkey = 'HKEY_LOCAL_MACHINE',
              @key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
              @value_name = 'InstalledInstances'

            --Select InstanceNames from @GetInstances
            Select *
            from @GetInstances

I have a server with 2 instances, when I run the above query there, I get the following result:

Now for EACH of those instances - or for a single instance, be it named or not, I would like to know at hand, information about the sql server services account similar to what is returned from the queries below:

select * from
sys.dm_server_services

SELECT  DSS.servicename,
        DSS.startup_type_desc,
        DSS.status_desc,
        DSS.last_startup_time,
        DSS.service_account,
        DSS.is_clustered,
        DSS.cluster_nodename,
        DSS.filename,
        DSS.startup_type,
        DSS.status,
        DSS.process_id
FROM    sys.dm_server_services AS DSS;

how can I achieve that?

DMV is specific to a particular instance, so no way to get info for all instances of sql server running on a given machine.

2 ways to do it -

Use your script to read registry and dump the info into a temp table and then join either  sys.dm_server_services and/or  sys.dm_server_registry depending on your needs. You can possibly join both the dmvs using the ImagePath
use powershell - dbatools - Find-DbaInstance and then pipe the discovered instances to any other service related commands for additional info. I have used Boe Prox's Get-SQLInstance to discover sql server instances on a given machine.


Is this a valid query that could allow to detect whether compression is supported or not? I am developing against a demo Oracle instance and have no access to compression. My code has to detect whether compression is available or not. My thought was to run this statement in a try/catch block and set the flag accordingly:
CREATE TABLE D COMPRESS FOR ARCHIVE AS SELECT * FROM dual;

Can anyone with access to an Enterprise instance tell me if this is the correct query? For me, it always throws:
Error: ORA-64307:  Exadata Hybrid Columnar Compression is not supported for tablespaces on this storage type

I am not sure if it correctly tells me that my instance is not Enterprise, or there is a problem with the query itself.

That syntax is for Hybrid Columnar Compression. HCC is a feature that requires special hardware, like Exadata, Pillar Axiom storage, or Oracle ZFS Storage Appliance.
You can not do this on any database and certainly not in most of them. Instead of the above you can use  this for basic or advanced compression:
CREATE TABLE D ROW STORE COMPRESS BASIC AS SELECT * FROM dual;
CREATE TABLE D ROW STORE COMPRESS ADVANCED AS SELECT * FROM dual;

If you want to check the availability of compression options, use this:
SQL> select parameter, value from v$option
     where parameter in ('Advanced Compression', 'Basic Compression');

PARAMETER            VALUE
-------------------- -----
Basic Compression    TRUE
Advanced Compression TRUE

The values will be FALSE in Standard Edition.
Keep in mind, Advanced Compression is an extra cost option and despite this, it still can not be properly disabled, so the above does not mean you are licensed to use it, it just means th option is available.


How can I monitor the call count per stored procedure per timespan? In order to diagnose some performance issues I would like to get a better understanding of the number of times certain procedures are called compared to the system performance. Is there a way to get the number of times each procedure has been called during a certain timespan?

You can get this (and more) from Dynamic Management Views (DMVs). To get statistics for a particular stored procedure, try the following query.

SELECT
    OBJECT_NAME(qt.objectid)
  , qs.execution_count AS [Execution Count]
  , qs.execution_count / DATEDIFF(Second, qs.creation_time, GETDATE()) AS [Calls/Second]
  , qs.total_worker_time / qs.execution_count AS [AvgWorkerTime]
  , qs.total_worker_time AS [TotalWorkerTime]
  , qs.total_elapsed_time / qs.execution_count AS [AvgElapsedTime]
  , qs.max_logical_reads
  , qs.max_logical_writes
  , qs.total_physical_reads
  , DATEDIFF(Minute, qs.creation_time, GETDATE()) AS [Age in Cache]
FROM
    sys.dm_exec_query_stats AS qs
CROSS APPLY
    sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
WHERE
    qt.[dbid] = DB_ID()
AND qt.objectid = OBJECT_ID('StoredProcedureName')
OPTION (RECOMPILE);

To look at the most frequently executed procedures:

SELECT
    OBJECT_NAME(qt.objectid)
  , qs.execution_count AS [Execution Count]
  , qs.execution_count / DATEDIFF(Second, qs.creation_time, GETDATE()) AS [Calls/Second]
  , qs.total_worker_time / qs.execution_count AS [AvgWorkerTime]
  , qs.total_worker_time AS [TotalWorkerTime]
  , qs.total_elapsed_time / qs.execution_count AS [AvgElapsedTime]
  , qs.max_logical_reads
  , qs.max_logical_writes
  , qs.total_physical_reads
  , DATEDIFF(Minute, qs.creation_time, GETDATE()) AS [Age in Cache]
FROM
    sys.dm_exec_query_stats AS qs
CROSS APPLY
    sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
WHERE
    qt.[dbid] = DB_ID()
ORDER BY
    qs.execution_count DESC
OPTION (RECOMPILE);

The values reported are cumulative since the last restart. If you want to measure over a fixed period, use the command below to reset wait stats.

DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);

If you wanted to measure fixed timespans across the day, you could feed the query output to a table via an agent job and either a) calculate the values between two runs or b) issue the wait stats reset as the last step in the agent job.

Alternatively, capture a profiler trace and run it through Clear Trace.


Connect to SQL Server on Linux over the network I have setup Microsoft SQL Server on an Ubuntu VM.

I know how to connect to SQL Server on Windows using SSMS -> but unfortunately I can not install SSMS on Ubuntu.

Which leads me to the question at hand of how would I do this from the terminal using sqlcommand?

1st you need to identify your VM IP address, supposing it is 10.1.1.10

sqlcmd -S 10.1.1.10 -U SA -P "YourPassword"

Either check connectivity between your host and your VM using

ping 10.1.1.10

and/or

telnet 10.1.1.10 1433

Read detailed instructions on Microsoft's link below:

sqlcmd on Linux

Regarding to web connection, there's no guarantee to be able to connect to the VM via web, that imply many aspects.


Insert from one row to another using cases My original table was(being used since 2005):

CREATE TABLE `request` (
    `msg` VARCHAR(150) NOT NULL,
    `id` VARCHAR(20) NOT NULL,
    `ctg` VARCHAR(10) NOT NULL DEFAULT 'misc',
    `date` VARCHAR(25) NULL DEFAULT NULL,
    `status` VARCHAR(10) NOT NULL DEFAULT 'empty',
    `fid` BIGINT(20) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`fid`),
    UNIQUE INDEX `msg_id` (`msg`, `id`),
    UNIQUE INDEX `msg_ctg` (`msg`, `ctg`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
CHECKSUM=1
AUTO_INCREMENT=18491;

Since, it was neither good management of table nor appreciable by me, I created a newer table:

CREATE TABLE `requests` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `ctg` VARCHAR(15) NOT NULL,
    `msg` VARCHAR(250) NOT NULL,
    `nick` VARCHAR(32) NOT NULL,
    `filled` ENUM('Y','N') NOT NULL DEFAULT 'N',
    `dated` DATETIME NOT NULL,
    `filldate` DATETIME NULL DEFAULT NULL,
    `filledby` VARCHAR(32) NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `nick_msg` (`nick`, `msg`),
    UNIQUE INDEX `ctg_msg` (`ctg`, `msg`)
)
COMMENT='Requests from users in any of the categories.'
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

What I want to do now is:

SELECT data from first table and insert as follows:

msg to msg, id to nick, ctg to ctg and date to dated are copied as is.
status in original table is either empty or filled. I want to use a CASE clause while insertion so that Y and N are inserted accordingly.

My query:

INSERT INTO `requests`( `id`, `ctg`, `msg`, `nick`, `dated`, `filled` )
SELECT `fid`,
    `ctg`,
    `msg`,
    `id`,
    `date`,
    CASE
        WHEN `status` = 'empty' THEN 'N'
        WHEN `status` = 'filled' THEN 'Y'
    END CASE
FROM `request`

My question is

  Is the query above perfect? Also, one of the sample string from request table with 'filled' status is like:

.hack//SIGN (filled by hjpotter92 in 02/08/12 09:47:07 )

the format is the same: filled by <<nick>> in <<<date>> in <<mm/dd/yy H:I:S>> for all filled entries. Can I extract the date from there and use it with STR_TO_DATE() to pass it in filldate field too? I know it'd need me to create a procedure/function.

For now, I am thinking of using PHP for the task but I wanted a clean MySQL solution if possible.

so it's work for me..

INSERT INTO `requests`( `id`, `ctg`, `msg`, `nick`, `dated`, `filled` )
SELECT `fid`,
    `ctg`,
    `msg`,
    `id`,
    `date`,
    CASE `status`
        WHEN 'empty' THEN 'N'
        WHEN 'filled' THEN 'Y'
    END
FROM `request`


How to Prevent an Ever-Growing index file (MyISAM engine) From Causing Disk Fragmentation in Windows (NTFS partition)? We have a keep-growing table which using MYISAM storage engine.

Almost every half year, this table will crash, and need to be repaired.

I checked the defragment result reported by windows defragment program, the most fragmental files are:

--------------------------------------------------------------------------------
碎片 文件大小 最零碎的文件
3,812 213 MB \MySQLData\data\cmszivr\calllog.MYI
24 541 MB \Program Files\Microsoft SQL Server\MSSQL\Data\tempdb.mdf
...

you can see, the index file caused 3812 fragment when it's 213 MB size. compared to SQL Server (24 fragment, 541 MB size), it's too bad.

I don't know if the fragmental issue caused mysql-server/mysql-table crashed, but it's must not be good for storage, and could be a potential reason to make server unstable.

So, is there a way to prevent it? (change config? change storage engine?)

When you perform any OS defragmentation or virus scan operations, please make sure mysql is shutdown. No utilities (mysql-based or otherwise) should be running against any table while the server is up. MySQL is not good to run on a PC that is not dedicated to MySQL DB only, especially in the presence of defragmentation or virus scan software running. Temp tables have also been known to get whacked by virus scan software.

At the very least, move the MyISAM table to a folder away from the standard datadir.

You should consider defragmenting the MyISAM table using:

OPTIMIZE TABLE calllog;

or you can do it in stages:

CREATE TABLE calllognew LIKE calllog;
ALTER TABLE calllognew DISABLE KEYS;
INSERT INTO calllognew SELECT * FROM calllog;
ALTER TABLE calllognew ENABLE KEYS;
ALTER TABLE calllog RENAME calllogold;
ALTER TABLE calllognew RENAME calllog;
DROP TABLE calllogold;

You may need to consider upgrading to MySQL 5.5 for the latest MyISAM storage engine code.

If you want to switch to InnoDB, you must configure it so that each table has its own tablespace.


Can't remote access MySQL server running on Mac OS X I have a MySQL server running on my work computer, which runs Mac OS X Maverick. I want to access it from home computer, which also has Mac OS Maverick.

From terminal, if in 1 tab, I ssh into my work computer and just let the ssh session sit there without being idle, then on another tab, I can access MySQL server.

However, without the ssh session running, I will get following error

  ERROR 2003 (HY000): Can't connect to MySQL server on 'SERVER_IP_ADDRESS' (60)

I've tried to modify my.cnf file on the server by commenting out bind-address or assigning it to 0.0.0.0 but it doesn't work.

Why is it like that? How can I configure the server so that it allows remote access without having to run ssh on the client? Can someone please help?

I have been banging my head against this error today on OSX Yosemite with MySQL 5.7 recently updated with Homebrew. Following suggestions on StackOverflow and elsewhere, I hunted around after my.cnf files all of which specified bind-address=0.0.0.0. I even removed and reinstalled MySQL following these instructions and then reinstalled using brew install mysql. Still no remote connections allowed.

It wasn't until I ran ps -ax | grep mysql and noticed that the bind address was being passed in the launch command (thus overriding any my.cnf files) that I dug some more and found out that Homebrew binds MySQL to 127.0.0.1 by default.

Editing ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist to change --bind-address=127.0.0.1 to --bind-address=0.0.0.0 solved my problem (the latter should be changed to a specific IP address if this isn't just a development machine).

I feel this is a vital piece of information that was lacking from most of the resources I consulted so hopefully posting this here will help someone else!

EDIT: As LeandroCR indicated in the comments, running brew services restart mysql will overwrite the plist file in LaunchAgents with the default one, leading to MySQL mysteriously refusing connections again. So better advice than what I originally wrote is the following:

Edit /usr/local/Cellar/mysql/<yourversion>/homebrew.mxcl.mysql.plist and replace --bind-address=127.0.0.1 with bind-address=* or --bind-address=0.0.0.0 (see MySQL documentation on bind-address)
Restart mysql using brew services restart mysql

Then MySQL should continue to accept non-local connections from then on - until you reinstall it, presumably.

Edit (Sep 2019)
Timothy Zorn points out that this problem no longer occurs for MySQL 8.x installed and run via Homebrew, so my answer above, written in 2016, may only be relevant to 5.x.


Job is Storing History But 'Include Step Output in History' is not Checked I have a SQL Server Agent Job. The 'Include step output in history' checkbox is unchecked. However, when I right-click oon the job and select 'View History', I can see that it is tracking step output.

There is just the one step on the job.

Why is this? Am I missing some setting?

That check box does not prevent job step history from being recorded/stored. SQL Server is going to do all this by default and that I know of you cannot turn it off.

The check box referenced is for telling SQL Agent to store additional or more detailed job step output. It would store this information to msdb.dbo.sysjobstepslogs. Which from my testing just checking that box alone does not do anything. You would still find the table empty if you queried it. Once I also checked Log to table, ran the job again, and then it showed a record for the job step.

This would enabled on a job that runs DBCC CHECKFILEGROUP command for a database:


Identify MySQL create version from data files Someone has given me a copy of a database full of FRM, MYD, and MYI files.  Trying to open it in my newer MySQL version gives an error that I need to run mysql_upgrade.  I don't want to change the files at all, so I want to install the same version of MySQL that was used to create the database.  Is there some way to identify the creation version from looking at these files?

Open the .frm file with a hex editor. Go to offset 0x33 and read four bytes. Convert to decimal, keeping your endianness in mind.

Examples:

c0c30000 --> c3c0 --> 50112 --> version 5.1.12
64c50000 --> c564 --> 50532 --> version 5.5.32
b2860100 --> 186b2 --> 100018 --> version 10.0.18 (MariaDB)
19870100 --> 18719 --> 100121 --> version 10.1.21 (MariaDB)

Sources:

MySQL Internals Manual
a percona.com blog entry


Is this a correct understanding of transactions? I have just noticed that one of my TRUNCATE queries takes 16 seconds to complete. I always assumed that TRUNCATE queries were basically-instant.
I currently do this:
TRUNCATE TABLE bigtable RESTART IDENTITY;
INSERT INTO bigtable (col) VALUES ($1); -- This one is run countless times.

Question 1: Since it takes 16 seconds for the TRUNCATE to do its thing, does that mean that for 16 seconds, my applications might be querying the bigtable and get nothing returned?
Question 2: If I do the following, will my applications continue to get the old values until the entire new dataset has been loaded in? Or will something else happen?
BEGIN TRANSACTION;
TRUNCATE TABLE bigtable RESTART IDENTITY;
INSERT INTO bigtable (col) VALUES ($1); -- This one is run countless times.
COMMIT TRANSACTION;

As I understand it, the BEGIN ... COMMIT "block" will basically not "go live" until the COMMIT, at which point both the TRUNCATE and all the INSERTS have taken place, so at that moment, the applications will get the new data, but until then, they will get the old data rather than NO or PARTIAL data?
Correct?

TRUNCATE is indeed very fast, but it has to take an ACCESS EXCLUSIVE lock on the table.
For this, the query has to wait until all concurrent transactions that use the table (even if only for a SELECT!) are done. Only then can it take the lock and execute the actual truncation.
To make it faster, see that concurrent transactions are short. 16 seconds is an awfully long time for a database transaction.
To your questions:

All the transactions that started before the TRUNCATE (and that block it) will see the complete, untruncated table.
All transactions that start after the TRUNCATE will be blocked until the TRUNCATE's transaction is done, even if they only SELECT from the table.

If you TRUNCATE and INSERT in the same transaction, the ACCESS EXCLUSIVE lock is held until the whole transaction is done.
So it will be like in the previous case, but the transactions that started later will see the table filled immediately.


"Insufficient privileges" while shutting down as sysoper I can't shutdown the database with a common user that is granted sysoper.

There is clearly a discrepancy between this and PDBs that I am unaware of (and that the documentation also doesn't clarify), since if I do the same operations for PDB users a sysoper can shutdown/startup the PDB with no problem. Other examples I've looked at also indicate that nothing should be wrong. What am I missing?

Exact error message is:

ORA-01031: insufficient privileges

Thanks for any insights.

It depends upon how you have granted the SYSOPER privilege. To shutdown the database instance user should have SYSOPER privilege on all containers.

The following demonstrate the scenario.

I have common user call C##_TEST.

[oracle@ora12c dbs]$ sqlplus sys@oracdb as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 2 08:47:05 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> select common from dba_users where username='C##_TEST';

COM
---
YES

I have granted SYSOPER to the user for the ORACDB(CDB) only.

SQL> grant sysoper to c##_test container=current;

Grant succeeded.

Then I have connected to container database using C##_TEST user as SYSOPER and tried to shut down the database and got the ORA-01031 error.

[oracle@ora12c dbs]$ sqlplus c##_test/test@oracdb as sysoper

SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 2 08:49:05 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> shut immediate;
ORA-01031: insufficient privileges
SQL>

Now I again granted the SYSOPER privilege with the option container=all. And this time the user can shut down the database.

[oracle@ora12c dbs]$ sqlplus sys@oracdb as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 2 08:49:36 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> grant sysoper to c##_test container=all;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

[oracle@ora12c dbs]$ sqlplus c##_test/test@oracdb as sysoper

SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 2 08:50:06 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


Is it possible to run VACUUM FULL in Postgres asynchronously? I am working on some unused data purging scripts on Postgres 9.1.  VACUUM FULL is necessary to reclaim physical space after purging.  However, with my data volume (5M+ rows), it takes several minutes to execute it.  Since the vacuum needs to be incorporated in the script, I was wondering if it is possible to kick it off asynchronously (or schedule it) so that my script can just finish without waiting?

Do you need VACUUM FULL?

  VACUUM FULL is necessary to reclaim physical space after purging

Yes, if you want to return that space to the OS or use it in other tables.

If you expect to just re-use the space for new rows in the same table, you're better off just running normal VACUUM (or letting autovaccum do its job), so the blocks containing deleted rows are marked as free for re-use. Shrinking and growing tables is a heavyweight operation that's best avoided if you're just going to re-use the space.

VACUUM FULL without locks

Pg doesn't provide a VACUUM FULL CONCURRENTLY.

There's a client-based effort to do the equivalent, called pg_repack. I can't speak for its safety/reliability.

In general we tend to avoid VACUUM FULL in favour of just letting normal VACUUM clear space for re-use within the table.


migrate db's - best way to mirror or replicate a full database I would like to mirror/replicate my databases to the cloud. The cloud is setup using VPN to my actual machine.
I am a bit confused by the options I have and would like to get some light into it.
My setup is a SQL Server 2012 (standard edition) Instance which should be mirrored/replicated to a SQL server 2014 instance. The instance contains 30 databases which are in full recovery mode.

One other option would be to take a full backup, restore in cloud and later restore a differential one. But this might take some time. I would love the minimize the downtime that's why I thought about the replica/mirror way.

What are my options and riks at this point?

It sounds like you're trying to setup a disaster recovery environment, so that if your local machine blows-up you can switch over to the Azure machine.

There are a number of options, each with their own pros and cons. These are well documented elsewhere (see this link for an overview from Brent Ozar: http://www.brentozar.com/archive/2011/12/sql-server-high-availability-disaster-recovery-basics-webcast/). That said, you might want to look at Log Shipping and Asynchronous Database Mirroring particularly.

Log shipping is an automated process for taking your log backups and restoring them on another server (e.g. Azure), you can take log backups every five minutes so that you're Azure server is always within about five minutes of your local machine. The upside is that it's easy to configure and can be setup through the SSMS GUI.

Database Mirroring takes this to the next level, constantly sending completed transactions to the other server (i.e. Azure) so that it's within a minute or so of your local machine (this depends on your network connection, of course).

There are other options but if you're new to disaster recovery but I'd recommend looking at these to start with.


Reading from subscriber during Transactional Replication When using Transaction Replication, are there any restrictions around selecting data from the subscriber whilst transactions are being applied to it?

i.e. can selects be made against tables to which updates are being applied?

Will performance of these selects suffer whilst transactions are being applied?

are there any restrictions around selecting data from the subscriber whilst transactions are being applied to it?

There are no restrictions for selecting data on subscriber side.

  Will performance of these selects suffer whilst transactions are being applied?

Yes, depending on the frequency of the log-reader agent there might be blocking when the replicated transactions are being applied to subscriber.

If your publication database is very active - generating lots of changes that needs to be applied to subscriber, I would suggest to change the database option to SET READ_COMMITTED_SNAPSHOT ON for publication and subscription databases. TEST IT before implementing in PROD !!

As a side note:

publish only subset of tables that are required rather than the entire database.
Use PULL subscription when there are large number of articles as the agent will run on subscriber side and this will alleviate load on distribution.

Refer to : Enhance General Replication Performance


Postgres- update a value in a array on particular index I have a column named ids that is of type bigint[]. ids cane be null or of any length

It has values based on certain hierarchy.

My use case is I want to update value that is at a particular index.
For example ids[2] = 50.

How can I achieve this?

I looked at this https://www.postgresql.org/docs/current/functions-array.html#ARRAY-FUNCTIONS-TABLE
but couldn't find anything that could help.

Thanks in advance.

Indeed there is no such function, but you can easily write your own:

create function array_set(p_input anyarray, p_index int, p_new_value anyelement)
  returns anyarray
as
$$
begin
  if p_input is not null then
    p_input[p_index] := p_new_value;
  end if;
  return p_input;
end;
$$
language plpgsql
immutable;

(If you never need it for anything different than bigint[] then you can replace the anyelement and anyarray with the equivalent types)

Then you can use it like this:

update the_table
  set ids = array_set(ids, 2, 50::bigint)
where ...;


Which one to consider for upgrade SQL Server 2008 vs SQL Server 2014 or Compatibility level 100 vs 110 I need to upgrade the SQL Server 2008 to SQL Server 2014 . SQL Server 2008 is currently running on compatibility level 100. I need to upgrade to SQL Server 2014 with compatibility level 100 this year and to compatibility level 110 next mid year.

If I deploy SQL Server 2014 with compatibility level 100 will I able to see the new features of SQL Server 2014 or I need to upgrade the compatibility level also in order to use new features of SQL 2012 and SQL 2014?

Compatibility levels are the way to tell SQL Server how to interpret the T-SQL and how the database will behave. Paul Randal wrote up a short comparison about the differences between version and compatibility levels about 9 years ago.

In a nutshell he says there is no relationship between compatibility versions and a database versions.  But you may have access to the new features in an older compatibility version of a database they just may not behave as expected.  One extra thing of note is that if you have multiple databases on the same server you can set each of them to their own compatibility level.


Extracting only numbers from data in mysql query In mysql table, the data has special characters (.) and (_) mainly next to numbers. I'd like to extract only the numbers stripping the characters in mysql query. My current query is like this:

 $query = "SELECT Date, Tenext FROM tbdata";

In this query, I need this only for the second field name (Tenext).
How can I do this in the mysql query? any help is appreciated. Thanks!

You can use the TRIM function, for example:

mysql> SELECT TRIM(BOTH '_' FROM '_21_' );
21

So in your case, to remove all _ characters, you can use something like:

 $query = "SELECT Date, TRIM(BOTH '_' FROM Tenext) as Tenext FROM tbdata";


Tkprof in critical environments Why tkprof utility is not adviced to be used in production setups? What concerns does it have?

I don't think that tkprof is the problem. It is a tool that summarizes information from trace file. Tracing is the problem. If you enable tracing of a process, this may slow down the process considerable. Besides that the trace files may fill up your disks. Finally you should be aware  that security relevant information is written to trace files.


How to convert varchar into decimal and its sum I have the following query:

SELECT SUM(cast(Amount as decimal(10,2))) as result
FROM tblserviceinvoice

and values of amount (which is varchar) are:

    2.00
  276,00
  528.00
  759.00
  759.00
  233.00
7,956.00
5,328.00

But as I run the said query, only 2569.00 is given as result. How can I fix it?

The issue in the above query is only due to these value (7,956.00 and 5,328.00 ) when casting in implemented on these values the value returned as 7.00 and 5.00 due to which the query gives the result as 2569.00.

Just remove these commas from these numbers and the query will execute properly

SELECT SUM(REPLACE(Amount, ',', '')) as result
FROM tblserviceinvoice


how to install a second server of mariadb I have a server where cpanel is installed (it includes mysql 5, php). What I need is to install a second database server mariadb 10.1.20. I tried through:

yum install MariaDB-10.1.20-centos6-x86_64-common.rpm

But it shows me conflicts like

Transaction Check Error:
  file /usr/share/mysql/charsets/Index.xml from install of MariaDB-common-10.1.20-1.el6.x86_64 conflicts with file from package MySQL56-server-5.6.38-1.cp1162.x86_64
  file /usr/share/mysql/charsets/README from install of MariaDB-common-10.1.20-1.el6.x86_64 conflicts with file from package MySQL56-server-5.6.38-1.cp1162.x86_64

What can I do for this case?

You will probably run into more conflicts than just what is listed in the question, e.g. TCP port, datadir, /etc/my.cnf, as well as maybe pid and socket. I think these configuration issues can be overcome by using different settings (MariaDB-specific sections in the .cnf files).

I'm not sure whether installing MariaDB from packages alongside a standard installation of MySQL (from packages) is feasible. rpm has an option --relocate OLDPATH=NEWPATH and --prefix NEWPATH which allows you to set a non-standard install path for rpms marked as relocatable. Not all rpms are reloatable.

However, see Installing MariaDB alongside MySQL (mariadb.com) which talks about installing the compiled binary distribution of MariaDB 5.5 from source code alongside an existing installation of MySQL. This is also the approach used by tools like dbdeployer.

See also Running Multiple MySQL Instances on One Machine (mysql.com) which talks about installing multiple MySQL instances on the same server. This may or may not be useful.

One relatively low-effort solution could be to install MariaDB in a Docker container. See e.g. MariaDB and Docker use cases, Part 1:

  Docker use case #1: Multi Tenancy

  A common use case for MariaDB and Docker is running several instances of MariaDB on the same physical hosts.


Combine the same column from multiple rows into one row I am joining two tables HR and UserDefined based on the Employee_ID:

HR Table

First Name  Last Name   Employee_ID
----------  ---------   -----------
Joe         Smith       456654

User Defined Table

Area    ud_1_text   ud_2_text   Employee_ID
----    ---------   ----------  -----------
1       ABCD1234    31/08/2018  456654
2       69090       23/05/2022  456654

Now I know that if Area equals 1 then it is a "Drivers Licence" and if Area equals 2 that it is a "First Aid Certification".

I would like to be able to return the following in one line per Employee_ID:

Employee_ID First Name  Last name   D/L Number  Expiry      First Aid Expiry
----------- ----------  ---------   ----------  ----------  --------- ----------
456654      Joe         Smith       ABCD1234    31/08/2018  69090     23/05/2022

Any ideas on how to do this?
Microsoft SQL Server query please.

Took the cte code from @Aduguid, another alternative is

;WITH
hr_table
AS
(
    SELECT tbl.* FROM (VALUES
      ( 'Joe', 'Smith', 456654)
    ) tbl ([First Name], [Last Name], [Employee_ID])
)
,
user_defined_table
AS
(
    SELECT tbl.* FROM (VALUES
      ( 1, 'ABCD1234', '31-Aug-2018', 456654)
    , ( 2, '69090', '23-May-2022', 456654)
    ) tbl ([Area], [ud_1_text], [ud_2_text], [Employee_ID])
)

SELECT h.Employee_ID,h.[First Name],h.[Last Name],
MAX(CASE WHEN u.Area=1 THEN u.ud_1_text ELSE '' END) AS [D/L Number],
MAX(CASE WHEN u.Area=1 THEN u.ud_2_text ELSE '' END) AS [expiry],
MAX(CASE WHEN u.Area=2 THEN u.ud_1_text ELSE '' END) AS [First Aid],
MAX(CASE WHEN u.Area=2 THEN u.ud_2_text ELSE '' END) AS [expiry]
 FROM hr_table h
INNER JOIN user_defined_table u
ON h.Employee_ID=u.Employee_ID
GROUP BY h.Employee_ID,h.[First Name],h.[Last Name]


Running a number of .sql files using SQLCMD I'm trying to put together a batch file to run a large number of .sql files without doing so individually.

So far I have:

@echo off
echo "Enter Server"
set /p SERVER=

echo "Enter Database"
set /p DATABASE=

del *.log

for /r %%i in (*.sql) do sqlcmd -E -S %SERVER% -d %DATABASE% -i"%%i" -m0 -o"%%i.log" -w500

pause

This works as far as it goes.

However what I really want to do is route any errors to a separate .err file, so that it is very clear at a glance which of the (100+) files failed.

I saw the :Error parameter, but I'm not sure how to apply it here.

Is there an easy way to acheive this?

for /r %%i in (*.sql) do sqlcmd -E -S %SERVER% -d %DATABASE% -i"%%i" -m0 -w500  -b -r  >%%i.log  2>%%i.err

Not perfect since it creates an empty error output file when there are no errors, but a .err file with > 0 bytes will have the errors.

for %%a in (*.err) do if %%~za==0 del "%%a"

..if that's a problem...


Advice with date column SQL Server 2005 I have two datetime columns in SQL Server 2005 that I need to query without the time portion of the datetime. Currently, my query looks something resembling this (just an example):

WITH Dates AS (
        SELECT [Date] = @StartDate
        UNION ALL SELECT [Date] = DATEADD(DAY, 1, [Date])
        FROM Dates WHERE [Date] < @EndDate
)
SELECT DISTINCT ID
FROM table t
CROSS APPLY DATES d
WHERE d.[Date] BETWEEN CONVERT(DATETIME, CONVERT(VARCHAR, t.StartDate, 103)) AND CONVERT(DATETIME, CONVERT(VARCHAR, t.EndDate, 103))

Now this results in a full Clustered Index Scan (surprise, surprise). I am trying to think of ways of making this faster (the actual query takes 3 mins :O). I have thought of doing the following but haven't tried any yet as I ran out of time earlier:

Use a computed column with just the date part
Index said computed column (Not sure if this is even possible?)
Use an Indexed view (Again, not sure if this is possible, will work?)

Easiest way would be to update the column and remove all time information, but I cannot do this :(

Any ideas?

Update

Thanks for all of the answers so far. I think the point of the question was missed because I was slightly unclear on what I wanted. My bad. I was only aiming to optimise the date conversion part of the query as the amount of data i'm dealing with is tiny in reality ( < 500,000 after the cross apply with a year long date range). Sorry for the confusion on this.

For those optimising the rest of the query for me, I can see what people are saying by using < > but consider the following:

The parameters that are passed in is a date range (eg. 1st to the end of this month)
The start date in the table can appear before or during the parameters date range (eg. only the end date is in the date range)
The end date in the table can appear during or after the parameters date range (eg. only the start date is in the date range)
Lastly, the start and end date in the table is in the parameters date range.

Personally, given the above, I could never get a < > solution to work. The only way I could get it to work properly and not miss anything is by using a CTE and saying where d.[Date] BETWEEN t.StartDate AND t.EndDate.

I hope this clears things up. Thanks again.

You can do steps 1 and 2: but use the DATEADD/DATEDIFF technique as per this: How to the get current date without the time part

You will most likely be unable to index the computed column because it won't be deterministic with the varchar method


Dead Raid Card SQL Server Running in Memory Wanted some opinions, on a predicament I’m currently facing. I just got thrown with this mess..

SQL Server database files saving to Raid volume, long story short raid card died so volume is not accessible. SQL service is still running and applications accessing the databases are still responding.

The backups aren’t upto date of course.

I have tried to export the databases out but error occurs because the files do not exist as the volume isn’t present.

However the ram is currently at 47GB.

My question is what does SQL Server store in memory, as my understanding is it stores everything in ram and then dumps to file?

Is the data recoverable by specialist if I do a raw ram dump?

Could I get backup of the database and remount another volume with the same drive letter and file structure and try exporting the database?

long story short raid card died so volume is not accessible.

Definitely not a good thing, but is it possible to buy a replacement? If it's on the motherboard directly, can the motherboard be replaced?

  SQL service is still running and applications accessing the databases are still responding.

This will work so long as the data in the files that are now not accessible are not asked for any data. If they are, it'll return an error to the session and write some information in the errorlog. This may currently be happening.

If, at some point, data from this volume was in memory and subsequently changed then you should see errors in the errorlog from checkpoints failing to write to the disk. This leads to a few conclusions but it's hard to say without looking at the database metadata. Generally speaking, if you aren't seeing any failures writing to disk in the eventlog, errorlog, or sessions (if you're looking at that) it's possible to conclude that some of the following may be true:

The database files aren't in use and aren't needed
The database files were in use but no data has changed
The database files aren't in use but will be used at a later point (month end, etc.)
Some data from the database files are in memory but not changed
No data from the database files are in memory
None of the files are log files or they are log files but no VLFs are in use

This list seems to be very contrary but multiples of them can be true at the same time. Again, without seeing the actual metadata it's hard to say.

  The backups aren’t upto date of course.

This is actually the root issue (I know you believe it's the RAID Card) because with backups it could have been restored to a different server, DNS updated, Logins created, and everyone go about their normal day. This isn't a put down, merely an example.

  My question is what does mssql store in memory, as my understanding is it stores everything in ram and then dumps to file?

You can think of it that way, yes. The very oversimplified version is that it's initially read into memory if it existed on disk, changed in memory, and some point later written back to disk if it's a disk based table.

  Is the data recoverable by specialist if I do a raw ram dump?

I'm not sure this would be a suitable way to go to be honest. If the files being offline aren't currently spewing tons of errors into the logs and clients are being returned tons of errors due to the inability to retrieve the data... you may not need (at this point in time) the data from those files. Additionally, swapping out cards should solve the immediate issue.

I believe the question is can you shutdown without losing any data? If so, and you aren't spewing tons of errors as listed above then it's more than likely you won't lose much if anything at all - that's not a guarantee
and YMMV but if you look at all the other logs it should give you an idea. Here is the main point, if it's in memory right now and has been changed then the log record exists whether or not it was successfully flushed to disk at some point later which means upon database startup it should redo the transactions.

Ask your clients to disconnect or stop using the services, stop the services or applications, then deal with the database. The longer this continues to be up and running the higher the risk of something much more catastrophic happening.


Can I permanently lock a MySQL table to prevent inserts I have a complicated situation where I would like to prevent inserts into a MySQL table, but keep it open for edits/updates to existing data. Can this be done? I've searched around here, but could not find a similar situation.

I would use a before insert trigger:

CREATE TRIGGER trigger_name
BEFORE INSERT
   ON table_name FOR EACH ROW
BEGIN
    SIGNAL SQLSTATE VALUE '99999'
      SET MESSAGE_TEXT = 'You cannot insert rows to this table.';
END;


MobiLink: How to add a new table to synchronisation using Sybase Central and mlagent How to add a new table to a synchronisation using mlagent configured from Sybase Central. This might be related to this or this question.

When doing a change on remote database as described on Sybase Tutorial it's not updating the consolidated database as well as the synchronisation model. When updating the model and add maybe a complete new table (or a new column) what is the correct way on doing this using mlagent and remote tasks configured from inside Sybase Central?

This seems¹ to work:

Create a new Remote Schema Name (e.g. v2.0)
Do changes on your consolidated database (e.g. CREATE TABLE-statement)
Update schema inside your synchronisation profile and maybe increase version string (on my current test it appears to doesn't matter)
Do a deployment of your update synchronisation profile. You will need update ml_columns and ml_tables as well as ml_table_scripts etc on your consolidated database fitting your update synchronisation profile. So apply the changes to it. Also you will need the SQL of remote database
Create an upgrade remote task including the remote SQL created with step 4 (EXECUTE SQL). This task should also change Remote Schema Name
Maybe iterate your sync- task and deploy both -- upgrade task and sync task
Should be done

The biggest issue is, that the script in default is droping the tables on remote side so a huge amount of data might be transferred after update.

¹ I was able to get this running on a local test scenario with minimum database.


List all columns for a specified table I'm looking for a precise piece of information in a database which I have no knowledge about. The database is on a separate machine, but I can log into it, and launch a psql command line, with administrator rights.
It's a third-party product, and they are slow to answer questions. I know the data is inside that database, so I want to do a little bit of reverse-engineering.
Given a table name, is it possible to get a list of the names of the columns in that table?
For example, in SQL Server, it's possible to dump a table into a reusable CREATE statement, which textually lists all the columns the table is composed of.

In addition to the command line \d+ <table_name> you already found, you could also use the Information Schema to look up the column data, using information_schema.columns:

SELECT *
  FROM information_schema.columns
 WHERE table_schema = 'your_schema'
   AND table_name   = 'your_table'
     ;

Note: As per the example above, make sure the values are enclosed within quotes.


MapReduce performance on a single PC I have heard that Hadoop has better performance than MySQL. Until now, I have used relational databases so this is really new technology for me. I have a 6 core PC. Suppose I have a table with 20 columns and 5million rows. Does Hadoop give better performance for operations like Select, Insert, and Update?

What is the equivalent command of Create Table, Select, Update, Insert, etc. in Hadoop 1.1?

Traditionally the use-case for Hadoop is when you need to split your data storage over dozens or more of machines, and you're not using a traditional RDBMS solution. When you only have one machine, you're likely to negate any potential gains the use of Hadoop would have provided.

Additionally, 20 columns * 5 million rows is considered by most DBA to be a small database, and aside from index lookups is not worth much in the way of optimization because most DBMS would handle this amount of information quite quickly.

Back to the topic of Hadoop, however, is this: Hadoop is a distributed file system, not an outright database. A potential use (and one I know fairly well) of Hadoop is when you have large sets of binary files, which have a common data format, and you need to run the same operations on each binary file, or you need to find those binary files quickly. In this case, Hadoop is effectively a massive lookup engine for all the files on the DFS. This way you can quickly find the files that you need to work with to run the parallel data analysis. One such group using Hadoop for such a goal is CERN.

I would not encourage you to consider transitioning your data to Hadoop when a traditional RDBMS would work well for your needs.


Number of TempDB files if we can't use parallelism Currently we have 4 cores and 4 tempdb files.  I just came on as the DBA about 2 months ago and, for some reason, 3 of the tempDB files are 2GB and 1 is 1GB.  Obviously, that is not best practice.

Being the OCD and proactive person that I am, I want to resize that file to match up with the other 3.

But wait, there's more.  It's used on a system that has an application that gains performance by NOT using any parallelism, so we have it disabled. MAXDOP = 1, cost threshold = 0.  It's the only application on the system and the SQL isntance is basically designed for this one application.

What are everyone's thoughts on this?  Will it help with any slowdowns that were seeing, given that our MAXDOP = 1?  Also, what implications/issues should I be on the lookout for when I change the file size?  I'll be doing it at night, off production hours.

Thanks for the help guys.

Individual queries don't use parallelism? So what? If you have two completely different users running different queries at the same time, they too can make use of different tempdb files.

Of course it doesn't help all that much if you have four tempdb files all on the same volume - the real gain is when you can spread it out into multiple I/O paths.

Hard to speculate on "any slowdowns you're seeing" without more details.

Shouldn't be any issues with changing the file size. Just make sure they are all exactly the same size, have the exact same autogrow settings, and check to see if you should run trace flags 1117 and 1118.


DENY access to EXEC for a role I have a read-only database user that I do not want to be able to run the EXEC command. I ran the following:

DENY EXECUTE to db_USERROLE_deny

That completed successfully; however, when I sign in as that user and run the following it runs and gives me output:

EXEC sp_columns table1

How can I deny this user the ability to run EXEC command on the database he connects to?

Since these are system stored procedures, you need to deny permissions in the master database using a new role in master, not the one you already have:

Add a new role DenyExecRole in master
Add the desired login to that role
Deny execute permissions:

USE master;
DENY EXECUTE TO DenyExecRole;


How can I simplify this query? I have written the following SQLite query:

SELECT sum(amount) / (julianday('now', '+1 day') - julianday('2012-08-11')) * 14 AS 'Cost Per Fortnight'
FROM
(
    SELECT "transaction".transamount AS amount
    FROM checkingaccount_v1 AS "transaction"
    JOIN Category_v1 AS category ON "transaction".CategID = category.CategID
    JOIN SubCategory_V1 AS subcategory ON "transaction".SubCategID = subcategory.SubCategID
    WHERE "transaction".transdate BETWEEN date('2012-08-11') AND date('now')
        AND category.categname = 'Food'
        AND subcategory.subcategname = 'Groceries'

    UNION ALL

    SELECT splittransaction.splittransamount AS amount
    FROM splittransactions_v1 AS splittransaction
    JOIN Category_v1 AS category ON splittransaction.CategID = category.CategID
    JOIN SubCategory_V1 AS subcategory ON splittransaction.SubCategID = subcategory.SubCategID
    JOIN checkingaccount_v1 AS "transaction" ON splittransaction.TransID = "transaction".TransID
    WHERE "transaction".transdate BETWEEN date('2012-08-11') AND date('now')
        AND category.categname = 'Food'
        AND subcategory.subcategname = 'Groceries'
) AS costs

I would like to know how I can simplify it. The two queries that are UNION ALLed are quite similar. Some values, such as the literal date, are also duplicated.

SQLite doesn't appear to support functions, stored procedures or variables. For this particular query, I require SQLite and can't use another programming language (like Perl) to execute it, so please don't suggest other tools/technologies.

If anyone is curious, the query is for a MoneyManagerEx report.

I've managed to simplify it a bit:

SELECT sum(amount) / (julianday('now', '+1 day') - julianday('2012-08-11')) * 14 AS 'Cost Per Fortnight'
FROM
(
    SELECT transdate, transamount AS amount, CategID, SubCategID
    FROM checkingaccount_v1 AS "transaction"

    UNION ALL

    SELECT transdate, splittransamount AS amount, splittransaction.CategID, splittransaction.SubCategID
    FROM splittransactions_v1 AS splittransaction
    JOIN checkingaccount_v1 AS "transaction" USING (TransID)
)
JOIN Category_v1 AS category USING (CategID)
JOIN SubCategory_V1 AS subcategory USING (SubCategID)
WHERE transdate BETWEEN date('2012-08-11') AND date('now')
    AND categname = 'Food'
    AND subcategname = 'Groceries'


Select into specific array positions with array_agg()? Is there a way to set values in specific positions inside an array, based on information from other columns? (Postgres 9.3 or later.)

For example, I would like to select an item and its stock information from the following tables:

Table item:

CREATE TABLE item (
  id integer NOT NULL
);

INSERT INTO item VALUES
 (1), (2), (3), (4);

Table item_stock (containing shop-specific information like stock and prices):

CREATE TABLE item_stock (
    item_id integer NOT NULL,
    shop_id integer NOT NULL,
    stock integer,
    cost numeric(19,3),
);

INSERT INTO items_stock VALUES
  (1, 1, 2, 10),
  (1, 2, 0, 9),
  (2, 2, 0, 9),
  (3, 1, 3, 22);

SQLFiddle

Looking for a query to produce the following results, where the array in the column stock contains stock info for specific shops. In the example, array position 1 is stock for shop_id=1 and array position 2 is stock for shop_id=2. 0 instead of NULL where no data is found:

id | stock
---+-------
1  | {2, 0}
2  | {0, 0}
3  | {3, 0}
4  | {0, 0}

This is the query I was able to come up (with some brute-force):

SELECT b.id, array_agg(b.stock) FROM (
  SELECT a.*, COALESCE(i_s.stock, 0) as stock FROM (
    SELECT id, generate_series(1, 2) as n FROM items
  ) as a
  LEFT OUTER JOIN item_stock i_s ON a.id = i_s.item_id AND a.n = i_s.shop_id
) as b GROUP by b.id;


Upgrade PostgreSQL 9.1 to 9.3 and server no longer starts Technically, I didn't upgrade from 9.1 to 9.3, I built a new server with Ubuntu 14.04 and used my old 9.1 config files. PostgreSQL wouldn't start:

* Starting PostgreSQL 9.3 database server
* The PostgreSQL server failed to start. Please check the log output.

but the logs were empty.

Eventually, after comparing the stock 9.3 postgresql.conf file and my 9.1 file I figured out that, at some point, the config variable unix_socket_directory had been changed to unix_socket_directories (not that easy to pick up in a diff with many changes and comments after each line).

Posted because I couldn't find anything on the net about this (until after I already knew what the issue was).

The config variable unix_socket_directory has been changed to unix_socket_directories in 9.3 - found in postgresql.conf


MySQL - Installing InnoDB Plugin I have a Master/Slave Replication Setup, both servers run MySQL 5.1.61.

I want to upgrade the master server to a stronger machine with the following:

2 processors - 6 cores each
48GB of RAM

To utilize both processors I need to install the InnoDB Plugin

I have a few questions:

Is it possible to install the InnoDB plugin just on the master and not on the slave?
Are there any other implications to installing the plugin?
What parameters do I need to change in my.cnf to better utilize the plugin and the multi core options?
Any recommended tutorial for installing the plugin?

Goto to the following folder

cd /usr/lib64/mysql/plugin
ls -l

If you see something like this

-rw-r--r-- 1 root root 11827298 Sep 13  2010 ha_innodb_plugin.a
-rwxr-xr-x 1 root root      962 Sep 13  2010 ha_innodb_plugin.la
lrwxrwxrwx 1 root root       25 Jun 23  2011 ha_innodb_plugin.so -> ha_innodb_plugin.so.0.0.0
lrwxrwxrwx 1 root root       25 Jun 23  2011 ha_innodb_plugin.so.0 -> ha_innodb_plugin.so.0.0.0
-rwxr-xr-x 1 root root  1204496 Sep 13  2010 ha_innodb_plugin.so.0.0.0

You have located the InnoDB Plugin files

Getting back to your question :

  1.Is it possible to install the InnoDB plugin just on the master and not on the slave?

Yes

  2.Are there any other implications to installing the plugin?

Once the plugin is installed and you configure my.cnf for the Master, do not use the options for InnoDB Plugin on another server where the plugin was not installed.

  3.What parameters do I need to change in my.cnf to better utilize the plugin and the multi core options ?

Please see my past posts on this

Possible to make MySQL use more than one core?
Multi cores and MySQL Performance

  4.Any recommended tutorial for installing the plugin?

Please follow the instructions from the MySQL Documentation on InnoDB Plugin Installation


Error 7399 when the Access file has a password I'm trying to create a linked server to access an old MS Access database (.mdb file) on SQL Server 2012, but I always get the error 7399 (authentication denied). So I tested with another mdb file that has no password and, in this case, the linked server worked perfectly. I've already given all permissions to the file's folder.

I tried to deny the inprocess in the ACE.OLEDB driver, but, if I do that, I got a new error

  7302 (SQL Server could not create a driver instance to this linked
  server).

I can't see what I'm doing wrong. I've searched about this a lot, and I still didn't find out a solution.

The problem was the file. I got a Windows XP virtual machine that has the MS Access 95. So I could open the file to delete the current password and change groups permissions. Now the linked server works.

It seems neither Microsoft.ACE.OLEDB.12.0 nor Microsoft.Jet.OLEDB.4.0 can deal with MS Access 95 security policy. But I really don't know why.


FCB::Open failed: Could not open file The machine was restarted while I was shrinking a database and since then the SQL Server (MSSQLSERVER) not starting. After reading the blogs I replaced the master.mdf and  mastlog.ldf files from the templates folder with the same file, assuming master database was corrupted as I was shrinking this.

However the problem remains and the error log file gives the following message,

Starting up database 'msdb'.
Starting up database 'mssqlsystemresource'.
Error: 17204, Severity: 16, State: 1.
FCB::Open failed: Could not open file E:\sql12_main_t.obj.x86Release\sql\mkmastr\databases\mkmastr.proj\MSDBData.mdf for file number 1.  OS error: 3(The system cannot find the path specified.).
Error: 5120, Severity: 16, State: 101.
Unable to open the physical file "E:\sql12_main_t.obj.x86Release\sql\mkmastr\databases\mkmastr.proj\MSDBData.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
Error: 17207, Severity: 16, State: 1.
FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'E:\sql12_main_t.obj.x86Release\sql\mkmastr\databases\mkmastr.proj\MSDBLog.ldf'. Diagnose and correct the operating system error, and retry the operation.
File activation failure. The physical file name "E:\sql12_main_t.obj.x86Release\sql\mkmastr\databases\mkmastr.proj\MSDBLog.ldf" may be incorrect.
The resource database build version is 12.00.2000. This is an informational message only. No user action is required.
Starting up database 'model'.

I can clearly say that there is no E:\ partition on my machine. Can you please help me to fix the path which I assume is the error now?

You replaced master with the one that comes from the SQL Server build process. This one is pointing to the release build mkmaster folders for msdb database. As msdb is no a system database, you should be able to start the engine, connect to it, detach msdb and re-attach the correctly located one. But this is not the correct process. The correct process is described in Rebuild System Databases, and involves running setup.exe:

Setup /QUIET /ACTION=REBUILDDATABASE ...


Does pt-table-checksum point out rows that are different between Master and Slave? Trying to verify replication integrity of my replica. And I found that pt-table-checksum is one of the best ways of doing it.

I have a simple Master->Slave set up. Have created a sample table with 10 rows on the Master, which has successfully replicated on the Slave. Intentionally on the slave I have changed a column in one of the rows of that table. Executed -

pt-table-checksum "h=host,u=user,p=password,P=3306" --function=MD5 --databases=test --tables=tentotal

And indeed in my checksums table that pt-table-checksum created, I could see different checksum for master and slave for that table. Excellent so far!

Output was -

TS                  ERRORS DIFFS    ROWS    CHUNKS  SKIPPED  TIME  TABLE
09-05T12:17:30      0      0        3       1       0        0.182 test.tentotal

Strangely the DIFF column says 0 which is incorrect. What am I missing here?

Percona tool kit version: 2.1.3

By default, pt-table-checksum displays as result all tables even if there is no error or diffs.

If you want to display just errors and diffs you can add the --quiet to the invocation of pt-table-checksum

  Print only the most important information (disables --progress). Specifying this option once causes the tool to print only errors, warnings, and tables that have checksum differences.
  Specifying this option twice causes the tool to print only errors. In this case, you can use the tool’s exit status to determine if there were any warnings or checksum differences.


Is there a way to grant a user access to only all non system views of database? In SQL Server, I have a user in a particular database and I've been asked to grant them access to all of the non-system views of the database only. I believe this can be done by editing securables of type view and granting select on each one, but there are many, many views. Is there a more efficient way to accomplish this?

There is no syntax such as

GRANT SELECT ON ALL::Views TO SomeUser

You can GRANT SELECT permissions on individual objects, schemas or the entire database but not filtered by object type to only include Views. For this sort of adhoc task I'd probably create a new role called ViewReader, add the user to that role then in SSMS run

SELECT 'GRANT SELECT ON ' +
          QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) +
      ' TO ViewReader;'
FROM sys.views
WHERE is_ms_shipped = 0

to generate a script to run to GRANT the required permissions.


Using a data warehouse staging database as a source of operational data Should you use the staging database within a data warehouse as a source of operational data?

ie, is it good practice for other operational (non BI) systems to source their data from this database? Or is the data warehouse there for information reporting/analysis and definitely not for information processing/feeding to other systems?

In general, is it good practice to source operational data from any part of a data warehouse system? Or should a data warehouse be a consumer of data only?

There is a forum discussion which discusses this briefly, but quite well IMO: Tek Tips - Using a data warehouse as a source system
Some points for consideration:
Duplication of effort / Single source of the truth
Will the operational system need to apply the same logic to the source data that the data warehouse is already performing?
Read-only source
Does the operational system expect to write changes/updates back to the warehouse?
Timely information
Is the operational system happy with the latency of the warehouse? (Generally T-1)
Service-level agreement
What is the impact to the operational system if there is an outage to the warehouse? In my experience warehouses, inherently, have a lower priority than transactional systems and may have, for example, up to 24 hours to become available (for querying) and up to 4 days for the ETL to be restored, running, and for the warehouse to be up to date.
If the operational system is internal and non-critical, this may be acceptable. If it is customer-facing, and retrieving FX rates from the warehouse for pricing, probably not.
I think the quote from that forum post that sums it up nicely is:

The business drives the technical solution with their requirements.

Your role would be to provide the facts to the business. If the business is willing to accept the risks and proceed with using the data warehouse as a source for an operational system, then I suggest you get that in writing and triplicate.
A "best-of-both-worlds" solution would be for the warehouse to publish the data once processed for the operational system to consume. The data could be extracted to a file or replicated to another/the-operational-system's database. This assumes that your warehouse is not "real-time".
I must admit that I get the heeby-jeebies whenever someone suggests connecting an operational system to our warehouse. Within our environment, we made the architectural decision that we would not control how users could consume the data, provided it does not unfairly impact our ETL processes or other users. An operational system becomes another "user query", and as such we provide the same level of service wrt to availability and accuracy as we do to Joe Bloggs, the junior analyst in Finance.
If a user requires a higher level of service, then we provide the data (via FTP'd files) rather than the user pulling the data (via queries/direct access). This assists in impact analysis for future changes because the extracts are visible within our ETL tool/suite.


postgresql revoke access to login role I executed this query that gave schema access to a login role called test

GRANT ALL ON SCHEMA open_waters TO test;

I want to now revoke the access to test

In general, a GRANT is revoked with its opposite command having REVOKE instead of GRANT and FROM instead of TO:

REVOKE ALL ON SCHEMA open_waters FROM test;


How to select the mail profile in a sql agent job I have several mail profiles configured on sql agent, how can I choose which of these profiles gets used for sending out notifications on a specific sql agent job ?

Database Mail profiles work a bit differently when it comes to SQL Agent jobs. In configuring SQL Agent itself through the properties window you will configure the Alert System.

The two arrows point to where a database mail profile can be selected. In these two instances the profile you select is basically telling SQL Agent this is your SMTP configuration and who you are going to send the emails "as", or the "FROM" field. Configuring the Alert System does require restarting the SQL Agent service.

Once that is done in order to receive notifications for SQL Agent jobs you will need to configure Operators.

You would setup the operator to note who you want the notification to be sent "TO". You can put more than one email address in this field but it does have a character limit. You can create multiple operators but within the notifications page of the job properties you will notice it only allows you to select one. This is just the limitation of SQL Agent.

The alternative to this would be using the sp_send_db_mail within the job step itself. In this you can use any mail profile you want, in the event you wanted it to go to someone other than any operator you may have configured.


Performance of a single table versus single table with jsonb versus multiple tables? This one is difficult to summarize in the title.

I have a set of data such that a single table can contain all information in a single row per item except for one column. That column will contain a URL or many URLs. So I have three ideas:

Add another table to store all URLs with a foreign key back to the main table for each
Use a single table and match the URLs with a regular expression
Use a single table and store the URLs in a jsonb column as an array

For case #2, I would be storing the regular expression in the column and matching values against it, e.g.:

select *
from foo
where 'http://someting.example.com/foo' ~ url

The url column has a row with the value: http(s)?://.+\.example\.com/foo.

Let's assume the row set for foo would number in the 100s (1000 would be a very large set), but most likely in the 10s. Let's further assume that the URL count could be double or triple in number.

Would the performance of each of these three possible designs be inline with each other? In other words, would the choice of design be a matter of preference? Or would designs #2 and #3 be so much worse than #1 that it would never be a good idea to choose them over #1?

I need to match rows by the URL. But I'd also like to keep maintenance to a minimum.

Options

Add another table to store all URLs with a foreign key back to the main table for each
Use a single table and match the URLs with a regular expression
Use a single table and store the URLs in a jsonb column as an array

Overview

Good idea. Totally ok.
If 2 means storing the URLs as a compound text field and processing with a regex, than that's utterly insane. Worst idea of the day insane. ;)
3 is also a very poor idea. Using JSONB to store an array is way overkill.

Conclusion

Store them in a separate table, or
Store them on the same table using a PostgreSQL Native Array Type

Querying

The 1nf-form method.

SELECT t1.*, t2.*
FROM t1
JOIN t2 USING (id)
WHERE t2.url = 'http://someting.example.com/foo';

This is how you query the table with an array type.

SELECT t1.*
FROM t1
WHERE urls @> ARRAY['http://someting.example.com/foo']

Performance implications

Separate-table (1nf) are that

adding URL's, and updating URLs are faster (you're writing less data).
joining slows things down a bit.

Performance implications for the column-array

Depending on the query, indexing can be more complex.
Adding or updating URLs requires writing the whole row.
Not having to join makes this potentially faster.
Arrays are less supported with ORMs and libraries.

Maintenance

  I need to match rows by the URL. But I'd also like to keep maintenance to a minimum.

Neither of these approaches is less-maintenance. However, for what it's worth, this is an ideal case for using an array. But make sure you normalize urls before you store them.