location of DTSX package I need to know the location of a DTSX package which is stored in "file system",

It is visible under Stored pacakges-->filesystem-->Package1 in Integration services .

I tried to do a windows search using "Package1.dtsx" string, But the results lists 5 packages with the same name, How do i know which is the package listed in integration services?. Is there any table i can query to get this info?

On your server, there is a file named MsDtsSrvr.ini.xml which is located at C:\Program Files\Microsoft SQL Server\120\DTS\Binn (for sql server 2014). When I examined this file it stated "..\packages" for the location, so I went to the DTS folder then into the packages folder and the files were there.

Depending on your version, architecture, and installation path) of SQL Server, you might find these files at

C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml (2005)
C:\Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini.xml (2008/2008R2)
C:\Program Files\Microsoft SQL Server\110\DTS\Binn\MsDtsSrvr.ini.xml (2012)
C:\Program Files\Microsoft SQL Server\120\DTS\Binn\MsDtsSrvr.ini.xml (2014)
C:\Program Files\Microsoft SQL Server\130\DTS\Binn\MsDtsSrvr.ini.xml (2016)


Average of last 4 runs of a product on SQL Server 2008 I'm looking to do the exact same moving average from this post: Average of last 4 runs of a product

but now on SQL Server 2008, which doesn't support the extended window aggregate functions.

Is there an alternative I can look into using to create the same solution in SQL Server 2008?

with cte as
( select ID, cost, ddate
       , row_number() over (partition by ID order by ddate) as rn
    from table
)
select cte1.ID, cte1.ddate, avg(cte2.cost) as avg, count(cte2.cost) as count
from cte as cte1
join cte as cte2
      on cte2.ID  = cte1.ID
     and cte2.rn >= cte1.rn
     and cte2.rn <= cte1.rn + 3
group by cte1.ID, cte1.ddate
order by cte1.ID, cte1.ddate


SQL Server SSL Encryption - SelfSign Cert working - why? I realize this is strange behaviour and I wonder if it is supposed to be working that way when using SQL Server generated self-signed certificate.

Client (Management Studio) with "encrypt connection" option set, client will verify self-signed cert on SQL Server side and fail because the self-sign cert is not trusted

Client (Management Studio) without 'encrypt connection' option set but "Force Encryption" is turned on at SQL Server side using SQL Server Configuration Manager. Client will not verify the self-signed cert and will connect successfully with the connection encrypted.

Questions

Why doesn't the client attempt to verify the SQL Server self-signed cert when encryption is enforced on Server Side? Why does the client only verify the self-signed cert when encryption is enforced on the client side?

If cert authentication is not done on client side for server-side encryption, what's the point of using a CA signed cert?

To be more precise: Why doesn't the client (Mgmt Studio) attempt to verify/authenticate the cert (be it self-signed/CA signed) when only server-side encryption is turn on ? Why does the client only authenticate/verify the cert given by the server when client-side encryption ( "encrypt connection" ) is turned on?
I added a signed cert (signed by an internal CA) to be used for server-side encryption. This requires the internal CA cert to be installed (into trusted root CA) on the client machine, but I did not do that. However, the client (mgmt studio) still connects successfully when server-side encryption is turned on.

Traffic will be encrypted regardless of the client encryption specification when you turn on the Force Encryption option on the server side (SSCM-->SQL Server Network Configuration-->Protocols for MSSQLSERVER-->Properties-->Flags-->Force Encryption). If you don't install a certificate from a trusted CA, SQL Server will use a self-signed certificate.

Clients cannot validate self-signed certificates because no trusted third party exists. Consequently, a client that requires encryption (Encrypt=true) must also explicitly specify that the certificate is to be trusted for the for the connection to succeed with a self-signed cert. TrustServerCertificate=true is  basically saying "yes, I understand the risks but connect anyway" and the certificate chain isn't validated.

A CA-issued certificate adds a trusted third party into the mix so the cert chain can be validated by the client with TrustServerCertificate=false. Although you could connect with TrustServerCertificate=true and a CA-issued cert (which I think is your second question), this is less secure because a self-signed cert could be used unwittingly by the client.

  Why doesn't the client (Mgmt Studio) attempt to verify/authenticate
  the Cert (be it self-signed/CA signed) when only ServerSide encryption
  is turn on ?

  Why does the client only authenticate/verify the Cert given by the Server when > client-side encryption ( "encrypt connection" ) is turn on ?

The server can force encrypted traffic for all clients but the onus is always on the client to verify the certificate. Consider the default connection setting is Encrypt=false in SSMS and other clients. This is saying "I don't need encrypted traffic so connect regardless of server settings or certificate." The ForceEncryption=true server setting strengthens this default by encrypting traffic regardless of the client's requested encryption level. This behavior aligns with this excerpt from the connection string syntax documentation for ADO.NET providers:

  Application settings never reduce the level of security set by SQL
  Server Client Configuration Manager, but may strengthen it.

Since the client didn't request encryption in the first place, certificate verification isn't performed but security is still strengthened by encrypting traffic with whatever certificate is provisioned on the SQL Server. The client can specify Encrypt=true and TrustServerCertificate=false to strengthen security further. This not only verifies a trusted certificate exists but also encrypts traffic even if ForceEncryption=false on the server.

Clients have to opt-in to certificate verification.  This is done to enable you to force encryption from the server without getting a trusted cert, installing a cert on the client or changing the client connection strings. See Using Encryption Without Validation in the product documentation.


Mysql query to retrieve the latest updates based on DATETIME for each parameter I have a station_data table where sensors from different weather stations report their data.

Sample data:
id(AI),station_code,parameter_code,value,date_time
1,1,1,1.234,2015-01-01 00:00
2,2,4,2.345,2015-01-02 00:00
3,1,8,3.456,2015-01-03 00:00
4,1,9,4.567,2015-01-04 00:00
5,3,3,5.678,2015-01-05 00:00
6,4,2,6.789,2015-01-06 00:00

I am trying to get to this result:

id(AI),station_code,parameter_code,value,date_time
9000,1,1,1.234,2015-08-07 09:00
9001,1,2,2.345,2015-08-07 10:00
9002,1,3,3.456,2015-08-07 09:00
9003,1,4,4.567,2015-08-07 10:00
9004,1,5,5.678,2015-08-07 10:00
9005,1,6,6.789,2015-08-07 09:00
.
.
.
9998,10,1,1234.56,2015-08-07 09:00
9999,10,2,2345.67,2015-08-07 09:00

The result should contain every pair station_code / parameter code once and the corresponding latest update time.

At the moment I am running this query:

SELECT DISTINCT station_code,parameter_code
FROM station_data
ORDER BY station_code,parameter_code ASC;

which results to a list of the station and parameter codes being monitored and then I chain query the database (in a for loop) to select the latest update time from the above list.

SELECT * FROM station_data
WHERE parameter_code=X AND station_code=X
ORDER BY date_time DESC LIMIT 1;

But this way of getting the data is nowhere next to optimum. Chain querying the database overloads the mysql server for a few minutes.

Is there any way to get the same result set by using a more efficient single query ?

I think you may be looking for something like this:

SELECT s.id,s.station_code,s.parameter_code,s.value,s.date_time
  FROM station_data s
 WHERE s.date_time = (SELECT MAX(date_time) FROM station_data WHERE station_code = s.station_code AND paramter_code = s.parameter_code)

Add whichever parameters are important for uniqueness in your sub-query.

I would also recommend for this to run optimally, you create an index that indexes the three columns in that index, namely date_time,station_code and parameter_code)


Last user update for a table Since my server was started one month back, I am getting 2015-07-31 as the value of last_user_update for a table.

Does that mean my table’s data hasn't been modified (inserted, updated, deleted) since 2015-07-31?

Can I 100% believe the results returned by last_user_update in sys.dm_db_index_usage_stats?

I am using SQL Server 2012. I have checked through sys.dm_os_sys_info that my server was started last on 22nd April-2015.

I am only worried about modifications to the table after 31st July. The reason why I want to be sure is because my database has been used by another party after 31st July and the data is huge so I can't confirm now whether any updates have been done or not.

Unless and until you try out some more observations and results of same, nothing can assure you 100 %. Although can assure you 99 % but rest you have to test.

Below query can give you what you said for dmv sys.dm_db_index_usage_stats as last modified date since restart on the usage of table.

SELECT last_user_update
FROM   sys.dm_db_index_usage_stats us
       JOIN sys.tables t
         ON t.object_id = us.object_id
WHERE  database_id = db_id()
       AND t.object_id = object_id('dbo.YourTable')

Also, below can give you an idea on same.
Result might be same on last usage date, but depends what you are looking for.
You need to understand the nature of that table as well depending upon the data out there.

SELECT
        [name]
       ,create_date
       ,modify_date
FROM
        sys.views
WHERE
        modify_date > DATEADD(day,-30,GETDATE())

In addition you will need trigger to observer what and when was it changed to analyse further.

Furthermore, if you have the default trace luckily out there , you might get some help that might get you close to what you are aiming for:
Read Collecting the Information in the Default Trace


Match similar special character in filters I have to perform a search on a table with Turkish city names.

The users would enter the first 4 characters of a city and I need to fetch the results from SQL.

For example, user wants to find the entries for Istanbul, so he would enter ista or Ista or ISTA

Problem is that Istanbul is written with special character. It is Ìstanbùl. So my query

where UPPER(city) like 'UPPER(<entered value>)%'

will not find it. It only finds it when the user enters Ìsta  (with the special character).

Is there a way of converting similar special character to 1 character. So for example ìíî will be converted to i. This way a user can find Ìnstanbùl with whatever variant of i they enter.

The replace function will find a character in a string and replace it.
Use:
REPLACE(str, find_string, replace_with)

in your case:
where UPPER(REPLACE(city, 'Ì', 'I') like 'UPPER()%'

Hope this helps

http://www.w3resource.com/mysql/string-functions/mysql-replace-function.php


Issue with a where clause by using Oracle I'm currently facing a problem while I'm adding a trigger to the database that I use at home.

I'm using : Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

The problem that occurs is that, when I add my trigger it compiles, but impossible to access to the content of a VARCHAR2 variable, even if it contains something.

    typeEngine engine.type%TYPE = '74E';--engine.type is a VARCHAR2(4)

SELECT engine.capacity INTO capacityMax
FROM engine
WHERE engine.type = typeEngine;

That returns me a NO DATA FOUND Exception, but the data is in the database.

Thanking you all in advance if someone has the solution to solve that issue.

It may probably no longer be a problem, but in the code it seems you forgot that the assignment operator is :=, not =, so typeEngine would be null and the query returns no rows.


Failover in Streaming Replication I have one doubt on master to slave replication in streaming replication in postgresql. I confgured ONE Master and One Slave with streaming replication in my system If Master fails slave act as a master then it is fine.

My Question:
1) who will be the master and who will be the slave when master fails? 2) when slave becomes as a master how much time it will take to get it back fail-over matster as slave?

can please give me the correct scenario for this?

Lets call them A (the master) and B (the slave).

  1) who will be the master and who will be the slave when master fails?

When master (A) fails, you'll have only a slave (B) working, trying to connect to its master - which will not be responding. The slave will still be able to accept read but not write requests.

You need to "promote" the slave (B) from its "slave" status to "master", in order to have a database that accepts write requests.

  1b) who will be the master and who will be the slave when master fails - and I promote the slave?

You will have a single master (B), no slave.

  2) when slave becomes as a master how much time it will take to get it back fail-over master as slave?

You will need to setup a new slave (C) or use the old master (A) as a slave of the new master (B). Use pg_rewind, rsync or pg_basebackup if you want to reuse the old master as a new slave. With pg_rewind or rsync you can be back up and running with a new slave very quickly (seconds possibly, if automated), or within minutes if done manually.


Local Database for Importing/manipulating ~200gb of data So I've recently come into a huge data set that i'm looking to work with - About 100gb in size across 80 flat files all of which I need to store in a relational way in order to interrogate, work with and actually perform data transformations and statistical analysis on.

I'm by no means a database administrator - I'm more in the statistical analysis space, but have learnt SQL and regularly work with local instances of SQL Server 2012 Express and R Studio. I use SQL Server to perform the manipulations, filtering and data clean up and R Studio to perform the statistical analysis; however historically I've only worked with data up to 10gbs.

During the SQL Express data import process I see that the maximum write speed is about 4mb/s (with my SSD I presumed it would be higher) and it has the storage limitation of ~10gb. Given I'm set up with an SSD, i7 processor and 16gb of ram (which i can upgrade to 64gb of ram if needed), I expected this to be faster. I'm thinking SQL Express is no longer be the ideal platform, but i'm not sure where to next. I'm open to paying for the versions of SQL Server under $1000, but I just don't know if that's what I need.

My question is, given the scale of this data and that I really need to be able to filter, combine and transform huge scales of it, what would be the ideal database I should be using? My preference is that there are suggestions for a windows platform, but I'm open to Linux databases too.

If you are the ONLY user connecting and manipulating data, I would suggest to download SQL Server 2016 DEVELOPER Edition (FREE) since it has tight integration with R.


Oracleasm deletedisk failed Unable to clear disk I have dropped the disk from ASM diskgroup:

> ALTER DISKGROUP DATA01 DROP DISK DISK1;

Oracle did a rebalance operation and then dropped the disk.

Now, I want to delete the disk from ASM library and return the disk to the system:

# oracleasm deletedisk -v DISK1
  Clearing disk header: oracleasm-write-label: Unable to open device "/dev/oracleasm/disks/DISK1":
  Device or resource busy failed
  Unable to clear disk "DISK1"

The system details:
11.2.0.1 2-node RAC

Why does ASM not clearing the disk header? Where to see the detailed log?

------------------------------------Edit1--------------------------------------------

# fuser /dev/oracleasm/disks/DISK1

returns nothing on both nodes

First of all, I checked that there were no extents on DISK1.

$ . oraenv
ORACLE_SID = [+ASM1] ?

SQL> select PXN_KFFXP,  -- physical extent number
           XNUM_KFFXP, -- virtual extent number
           DISK_KFFXP, -- disk number
           AU_KFFXP    -- allocation unit number
    from X$KFFXP
    where DISK_KFFXP=1;

no rows selected

I cleared the disk header by dd:

# dd if=/dev/zero of=/dev/oracleasm/disks/DISK1 bs=1024 count=100
100+0 records in
100+0 records out
102400 bytes (102 kB) copied, 0.000156 seconds, 656 MB/s

Note: My allocation unit is 1MB that is why I am using bs=1024.

Then deleted disk by oracleasm:

# oracleasm deletedisk DISK1
# oracleasm scandisks    <--Do this step on all nodes

To tell the truth it is not a good way to solve the problem, because when I run the following command on the second node:

/sbin/blkid | grep oracleasm

/dev/sdg1: LABEL="DISK6" TYPE="oracleasm"
/dev/sdb1: LABEL="DISK1" TYPE="oracleasm"
/dev/sdh2: LABEL="DISK8" TYPE="oracleasm"
/dev/sdh3: LABEL="DISK9" TYPE="oracleasm"
/dev/sdh4: LABEL="DISK10" TYPE="oracleasm"
/dev/sdh1: LABEL="DISK7" TYPE="oracleasm"
/dev/sdi1: LABEL="DISK11" TYPE="oracleasm"
/dev/sdi2: LABEL="DISK12" TYPE="oracleasm"

It shows me the label DISK1.

So clearing the ASM disk header using dd left some junks.


What is an "Obsolete" Database? I am trying to have a better understanding of the term: "Obsolete Database"

Are there elements that defines an obsolete database?

Depends on who you ask.

Is it in terms of new features, etc.? For example, a 32-bit RDBMS system could be considered obsolete nowadays, because 64-bit is common, and most engines run safely on a 64-bit platform, with access to more memory.

Is it in terms of scaling? For example, an old design that doesn't scale well, might be considered obsolete.

A database with queries that use old-style joins, could be considered obsolete, and in this case would prevent upgrades to newer technology without changing the code.

Some wonks like to say that SQL itself is obsolete, that NoSQL technology is better suited to certain problem areas like unstructured data. I personally wouldn't say this is the right use of "obsolete", but that could be an example too.


How do I store spatial coordinates in SQL Server 2005 I'm looking to store GPS co-ordinates in an SQL Server 2005 for location matching of users with GPS-enabled mobile phones. I've read about STDistance on MSDN, but it only appears to exist in SQL Server 2008 (& later) along with the geometry and geography field types. I'll need to support older versions of SQL Server as well (at least 2005).

What's the best way of storing GPS co-ordinates and calculating distances between 2 GPS locations in SQL Server 2005?

GPS co-ordinates are just latitude and longitude - if you have to support SQL 2005, then store them as numbers to your required precision.

To calculate the distance, you can implement the Haversine formula


Best way to divide CPU along all sql instances on 1 server I have a physical server with 40 CPU's (hyperthreaded) with 2 NUMA nodes. On this server I have 20 SQL Server instances installed and I need to make sure each instance has some dedicated CPU assigned into.

The problem is that sometimes one or more of the instances are using all of the CPUs and then about 4 or 5 instances are out of CPU resources and failing.

What is the best way to spread CPU among all of the instances so each instance has some at the minimum?

You can configure the processor affinity used by an instance of SQL Server by right clicking the instance and choosing Properties -> Processors:

Processor Affinity
Assigns processors to specific threads to eliminating processor reloads and reduce thread migration across processors. For more information, see affinity mask Server Configuration Option.

This will enable you to assign specific processors per instance. Getting this right though requires constant attention and tuning so generally I don't advise it unless you really know what you are doing.
If you do decide to proceed, then I highly recommend reading about the affinity mask Server Configuration Option before you attempt it and also you can read a quick tutorial on TechNet and some basic guidelines can be found on Microsoft Support.


Loading data in mysql using LOAD DATA INFILE, replication safe? I am trying to load data into mysql database form CSV file. I found that we could use LOAD DATA INFILE command to do it. But as per the mysql documentation it is not replication safe. (See here)

Is there a better way to do it rather than to do it via application?

Another answer has already been accepted, but I disagree with that answer, because your question first requires an understanding of the meaning of the term "unsafe" and the phrase "considered unsafe" when it is used related to MySQL replication.

  When speaking of the “safeness” of a statement in MySQL Replication, we are referring to whether a statement and its effects can be replicated correctly using statement-based format.
  http://dev.mysql.com/doc/refman/5.5/en/replication-rbr-safe-unsafe.html (emphasis added)

There is no such thing as an "unsafe" statement when you are using MIXED or ROW-based replication, because the entire premise of "unsafe" is related to how statement-based logging and replication work.

Something "considered unsafe" doesn't mean it shouldn't be done -- it means the optimizer will consider it unsafe to log in statement-based mode and therefore that it will be automatically replicated using the row-based replication logging format if row-based logging is available -- that is, if you are not using binlog_format = STATEMENT -- which you should not be doing unless you have a specific reason to use it.  If you are not aware of a specific reason to use it, then you most likely do not have one, and you should used MIXED logging, so that the optimizer can choose how to replicate each statement.

http://dev.mysql.com/doc/refman/5.5/en/replication-formats.html


How to have one Auto Incremented id for two separate tables in mysql? I have 2 different tables, each having its own id column.

I need that if one table is using a specific id, the other table will not use it. This is needed because the object in both tables will later be handled together by another component in the application, and the other component must have them uniquely identified by the id.

I thought to have a table counter with next_id column, and have a before insert trigger to each of these tables, that will take the current id from counter, use it for the new row, and increase it.

My question is, is this synchronized? i.e. what will happen if 2 different applications, or threads in a single application, insert columns to any of these tables?

Do I have to lock the counter table during the trigger? If yes, will it be locked until the end of the entire transaction, or just until the trigger ends?

Is there a better solution for this problem?

If the two tables share ID space, surely then they should have a common parent table?

I would create a parent table and make the two other tables children of it with foreign keys. The parent has the autoincrement field, and you have to insert there first before inserting the child. Add a "type" column to the parent table so you know in which table to look for the child with the given ID.

The biggest advantage to this approach is that you don't need any triggers. No performance or synchronicity problems to worry about.

In PostgreSQL you would implement this using inheritance, but I don't think MySQL has this feature.


Required values for the performance counters That will sound ludicrously, by the accident I became a 'DBA' and have to handle one big database around 2TB data. I would like to know what are optimal values for the following performance counters:

Average Disk sec/Transfer
Current Queue Length D
Disk Bytes/sec
Transfer/sec
PLE

In case of PLE I have read that in old times it was 300 sec per 4GB of RAM. We have 128GB of RAM, and PLE fluctuates from 0 to 15k. Anyway, I don't think it is possible to have high PLE value when everyday around 50-70GB of new data is coming in, same amount is being deleted and we store data from last 30 days, so users can read and manipulate it

After searching for "Accidental DBA" as suggested by LowlyDBA you will see that a lot of us got the job because we happened to stand next to the database server as it started going up in flames.

So I want to congratulate you on the promotion and suggest starting here: Accidental DBA Series - SQLskills.com

Regarding your actual question. There is no optimal value for most of the performance counters (besides maybe stuff like 100% CPU all the time might be not good). You need to find out what is normal for YOUR server. This baseline will help you to know where to look when it is not performing as expected (see the "Baseline" section in the "Accidental DBA Series").

That being said I would suggest stepping away from perfmon for a moment and have a look at your servers wait stats first. These stats will tell you if and where it hurts. If your server is not waiting on disk resources it does not matter if some disk specific perfmon counters are "too high". Wait stats analysis is explained in the "Wait Statistics Analysis" section of the sqlskills article.

Again, a baseline will help a lot. Have a look at Brent's solution for monitoring wait stats. It is lightweight, extremely helpful and free.


Attach Database is disabled in Microsoft SQL Server 2012 Management Studio I have buy from Godadday hosting and domain, after that I have create database in godaddy and the connect to this db using my authentication.

I have .mdf, .ldf that need to attach to the connected server, I am going to Object Explorer -> Database -> Attach -> Attach database screen is opened but everything inside is disabled.

What can I do to enable attaching my local DB to the server DB?

Thank you so much for helping.

I am using SQL Server 2012 Management Studio

If you need help attaching via T-SQL, if you see the SCRIPT button on the image you attached, you will be able to output the SCRIPT syntax to attach via 'T-SQL'. Just fill it in with the required information like target path to MDF file etc.. Give that a shot and see what you can come up with.

Also make sure you are on the same version of SSMS as the database to help prevent issues.

Also, when you connect to the DB instance, make sure you are using a Administrative account, it might be different than your current log-in or Windows Auth account.


Why is the CTE much worse than inline subqueries Im trying to better understand how the query planner work in postgresql.

I have this query:

select id from users
    where id <> 2
    and gender = (select gender from users where id = 2)
    order by latest_location::geometry <-> (select latest_location from users where id = 2) ASC
    limit 50

It runs in less than 10ms on my database with around 500k entries in the users table.

Then I thought that to avoid the duplicate subselects I could rewrite the query as a CTE, like this:

with me as (
    select * from users where id = 2
)
select u.id, u.popularity from users u, me
    where u.gender = me.gender
    order by  u.latest_location::geometry <-> me.latest_location::geometry ASC
    limit 50;

However, this rewritten query runs in around 1 sec! Why does this happen? I see in the explains that it doesnt use the geometry index, but can anything be done for that? Thanks!

Another way to write the query is:

select u.id, u.popularity from users u, (select gender, latest_location from users where id = 2) as me
    where u.gender = me.gender
    order by  u.latest_location::geometry <-> me.latest_location::geometry ASC
    limit 50;

However, this will also be as slow as the CTE.

If on the other hand I extract out the me parameters and statically insert them the query is quick again:

select u.id, u.popularity from users u
    where u.gender = 'male'
    order by  u.latest_location::geometry <-> '0101000000A49DE61DA71C5A403D0AD7A370F54340'::geometry ASC
    limit 50;

Explain of the first (fast) query

 Limit  (cost=5.69..20.11 rows=50 width=36) (actual time=0.512..8.114 rows=50 loops=1)
   InitPlan 1 (returns $0)
     ->  Index Scan using users_pkey on users users_1  (cost=0.42..2.64 rows=1 width=32) (actual time=0.032..0.033 rows=1 loops=1)
           Index Cond: (id = 2)
   InitPlan 2 (returns $1)
     ->  Index Scan using users_pkey on users users_2  (cost=0.42..2.64 rows=1 width=4) (actual time=0.009..0.010 rows=1 loops=1)
           Index Cond: (id = 2)
   ->  Index Scan using users_latest_location_gix on users  (cost=0.41..70796.51 rows=245470 width=36) (actual time=0.509..8.100 rows=50 loops=1)
         Order By: (latest_location <-> $0)
         Filter: (gender = $1)
         Rows Removed by Filter: 20
 Total runtime: 8.211 ms
(12 rows)

Explain of the second (slow) query

Limit  (cost=62419.82..62419.95 rows=50 width=76) (actual time=1024.963..1024.970 rows=50 loops=1)
   CTE me
     ->  Index Scan using users_pkey on users  (cost=0.42..2.64 rows=1 width=221) (actual time=0.037..0.038 rows=1 loops=1)
           Index Cond: (id = 2)
   ->  Sort  (cost=62417.18..63030.86 rows=245470 width=76) (actual time=1024.959..1024.963 rows=50 loops=1)
         Sort Key: ((u.latest_location <-> me.latest_location))
         Sort Method: top-N heapsort  Memory: 28kB
         ->  Hash Join  (cost=0.03..54262.85 rows=245470 width=76) (actual time=0.122..938.131 rows=288646 loops=1)
               Hash Cond: (u.gender = me.gender)
               ->  Seq Scan on users u  (cost=0.00..49353.41 rows=490941 width=48) (actual time=0.021..465.025 rows=490994 loops=1)
               ->  Hash  (cost=0.02..0.02 rows=1 width=36) (actual time=0.054..0.054 rows=1 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 1kB
                     ->  CTE Scan on me  (cost=0.00..0.02 rows=1 width=36) (actual time=0.047..0.049 rows=1 loops=1)
 Total runtime: 1025.096 ms

Try this:

with me as (
    select * from users where id = 2
)
select u.id, u.popularity from users u, me
    where u.gender = (select gender from me)
    order by  u.latest_location::geometry <-> (select latest_location from me)::geometry ASC
    limit 50;

When I look at the fast plan here's what jumps out at me (bolded):

 Limit  (cost=5.69..20.11 rows=50 width=36) (actual time=0.512..8.114 rows=50 loops=1)
   InitPlan 1 (returns $0)
     ->  Index Scan using users_pkey on users users_1  (cost=0.42..2.64 rows=1 width=32) (actual time=0.032..0.033 rows=1 loops=1)
           Index Cond: (id = 2)
   InitPlan 2 (returns $1)
     ->  Index Scan using users_pkey on users users_2  (cost=0.42..2.64 rows=1 width=4) (actual time=0.009..0.010 rows=1 loops=1)
           Index Cond: (id = 2)
   ->  Index Scan using users_latest_location_gix on users  (cost=0.41..70796.51 rows=245470 width=36) (actual time=0.509..8.100 rows=50 loops=1)
         Order By: (latest_location  $0)
         Filter: (gender = $1)
         Rows Removed by Filter: 20
 Total runtime: 8.211 ms
(12 rows)

In the slow version, the query planner is evaluating the equality operator on gender and the geometry operator on latest_location in the context of a join, where the value from me could vary with each row (even though it has correctly estimated only 1 row).  In the fast version the values of gender and latest_location are treated as scalars because they are emitted by inline subqueries, which tells the query planner it only has one value of each to deal with.  This is the same reason why you get the fast plan when you paste the literal values.


How can I get MySQL to use the value returned by a function as a default? I have a pre-existing table that has a column to that can hold a UUID.

I'd like to be able to define a default such that new rows coming in (from legacy systems) will still get a UUID defined, via the UUID() function -- is it possible to configure the default of a column to be a value returned by UUID (in a similar manner to how you can for the UNIX_TIMESTAMP() in phpMyAdmin)

Using a trigger

You can't use functions as defaults in MySQL, except for CURRENT_TIMESTAMP for TIMESTAMP columns


What is really wrong with this query please? SELECT CONCAT_WS(parent_title, parent_fname, parent_sname) AS 'Parent'
CONCAT ('£1.50', sum(activity_price) AS Amount, COUNT(*) AS 'N.Activities'
FROM Activities A, Parent P, Child C, Register R
WHERE C.parent_id=P.parent_id AND C.child_id=R.child_id AND A.activity_id=R.activity_id
GROUP BY P.parent_id;

The Query browser says error is near the 'CONCAT(£1.50', sum(activity_price)....... FROM at line 2.

I'm not a MySQL expert, but it looks like:

SELECT CONCAT_WS(parent_title, parent_fname, parent_sname) AS 'Parent'
    CONCAT ('£1.50', sum(activity_price) AS Amount

should be:

SELECT CONCAT_WS(parent_title, parent_fname, parent_sname) AS 'Parent',
    CONCAT ('£1.50', sum(activity_price)) AS Amount

I added a comma after the first result column, and a trailing bracket to the CONCAT function.

While I have no idea if the latter concatenation is a valid operation, what I've suggested should fix the basic syntax errors.


Will transaction log grow event trigger time out event? i have a .NET program request the MSSQL database, the request contains large amount of deletion and insertion.

In such case, there is a potential that the log would grow as the transaction log space is not enough.

And it is found out that the event of growing transaction log take long time to do.

I want to know if the time required for this event will trigger the database time out or not?

Or the auto grow event will not affect the regular time out setup of the database?

Growing the logfile (as well as database files) freezes the database operation for a short time. It is especially bad if it is needed repeatingly.

A good initial size and a big grow factor can reduce the number of resizes. What you also should avoid is shrinking the log because it will just grow again. The initial size of the log should cover all changes likely to happen within the backup period.

Timeout per se is not expected to happen by this, but of course if an application sets very low timeouts it will experience them. However if you work with small timeouts you should also implement proper retries since you are dealing with a enterprise software stack which has certainly no RT guarantees (and therefore timeouts will happen even without a log resize)

For database files (but not for logfiles) IFI (instant data file initialisation) can reduce the time to extend a data file. It requires SQL Server to be started with special volume management privileges.


Memory usage grows on SQL Server Analysis Services 2012 until cubes fail to process We have a 64-bit SQL Server 2012 SP4 Enterprise Edition instance with Analysis Services (Analysis Services is running on the same VM as the SQL Server database engine) where the memory usage of the Analysis Services process continues to grow until the point where cube processing begins to fail with a report of "The operation has been cancelled because there is not enough memory available for the application". Once this happens our only option appears to be to restart the Analysis Services process and begin the cycle again.

I have experimented with changing the low/total/hard memory limits in Analysis Services, but so far have not found anything that works for more than about 5-6 days.

What I have found is that the performance counter for "Cleaner Memory nonshinkable KB" continues to grow day to day, which is not something that I would expect.

Would anyone have any insights or suggestions to help diagnose this issue?

Some addition information:

The VM has 20 GB of RAM allocated to it.
The SQL Server is currently
set to limit memory usage to 12 GB.
The Analysis Services
low/total/hard memory limits are currently set to 40/50/70 (percent).
The SQL Server has about 20 databases of moderate size totaling no
more than 100 GB.
There are five Analysis Services databases / cubes.
We use multidimensional models.
The cubes are fully processed overnight on a daily basis.
Proactive caching is used to keep the cubes updated during the day.
Web sites access the cubes via the OLAP "pump" (msmdpump.dll).
This VM was recently set up to replace a physical server that had a very similar
configuration except for having a few more GB of RAM and a less
recent service pack of SQL Server. That physical server had been
running for years and we have never experienced this issue with it.
Unfortunately, that server failed and is no longer bootable (disk
issues) and the person who originally configured Analysis Services on it so far
has not been able to  remember any details about the setup that has been useful.

From a comment by Robin Blondon:

I tested with with SP1 through SP4 and it looks like SP4 is the only one that has this issue. I submitted a bug report On Azure Feedback.

The suggested advice would be to upgrade to a newer version of SQL Server Analysis Services, since SQL Server 2012 is out of mainstream support:


SSIS Error: To run a SSIS package outside of SQL Server Data Tools you must install Standard Edition (64-bit) of Integration Services or higher SQL Server 2016 SP1 Enterprise Edition Data Engine (with SQL Agent) installed on an FCI called inst1.
SSIS 2016 SP1 Enterprise Edition installed on comp2. (comp2 is not in the inst1 cluster)
Filesystem SSIS packages on comp2.
SQL Agent job using the SSIS step type on inst1 calls the package store on comp2.

When I run the job on inst1, I get this error:

  To run a SSIS package outside of SQL Server Data Tools you must install Standard Edition (64-bit) of Integration Services or higher.

I have run the edition upgrade wizard, just in case the wrong edition was installed. The error persists.

Any ideas?

Turns out I didn't have SSIS installed on the inst1 server(s) and that is where the dtexec.exe process was being kicked off. It was not an edition problem, just a missing service problem.


Selecting Without Repititions I have table with observations of objects moving along edges in a graph, this table has the following form:

PK | TIMESTAMP | object_id | from_id | to_id

where object_id is the id of some object and from_id and to_id are vertices.

Since the movements are observed at a high frequency the tuple

(object_id, from_id, to_id)

is repeated often for different PK and TIMESTAMPS. I'm interested in all the separate edge traversals, so if an object with id 1 moves from vertex 1 to 2, from 2 to 1 and from 1 to 2 I want to have a result:

object_id | from_id | to_id
        1 |       1 |     2
        1 |       2 |     1
        1 |       1 |     2

My question: how to write this query?

The tuples would have to be ordered by from_id, to_id in such a way that when from_id or to_id changes, it would fall under a different grouping.
I was thinking of writing this as a Stored Procedure, but I thought of something much more intriguing (I just got the idea from my answering this question Update ranking on table about 3 hours ago)

I'll do it in stages

Stage 1 : Sample Data

DROP DATABASE IF EXISTS bootvis;
CREATE DATABASE bootvis;
USE bootvis
CREATE TABLE graph_edges
(
    pk int not null auto_increment,
    object_id int default null,
    tm timestamp not null default current_timestamp,
    from_id int,
    to_id int,
    primary key (pk)
);
INSERT INTO graph_edges (from_id,to_id) VALUES (1,2),(1,2),(1,2),(1,2);
SELECT SLEEP(2);
INSERT INTO graph_edges (from_id,to_id) VALUES (2,1),(2,1);
SELECT SLEEP(1);
INSERT INTO graph_edges (from_id,to_id) VALUES (1,2),(1,2);
INSERT INTO graph_edges (from_id,to_id) VALUES (2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1);
INSERT INTO graph_edges (from_id,to_id) VALUES (1,2),(1,2),(1,2),(1,2);
INSERT INTO graph_edges (from_id,to_id) VALUES (2,1),(2,1);
UPDATE graph_edges SET object_id = 1 WHERE object_id IS NULL;
INSERT INTO graph_edges (from_id,to_id) VALUES (1,2),(1,2);
INSERT INTO graph_edges (from_id,to_id) VALUES (2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1);
SELECT SLEEP(2);
INSERT INTO graph_edges (from_id,to_id) VALUES (1,2),(1,2),(1,2),(1,2);
INSERT INTO graph_edges (from_id,to_id) VALUES (2,1),(2,1);
INSERT INTO graph_edges (from_id,to_id) VALUES (1,2),(1,2),(1,2),(1,2);
INSERT INTO graph_edges (from_id,to_id) VALUES (2,1),(2,1);
INSERT INTO graph_edges (from_id,to_id) VALUES (1,2),(1,2),(1,2),(1,2);
INSERT INTO graph_edges (from_id,to_id) VALUES (2,1),(2,1);
UPDATE graph_edges SET object_id = 2 WHERE object_id IS NULL;
SELECT * FROM graph_edges;

Stage 2 : Sample Data Loaded

mysql> DROP DATABASE IF EXISTS bootvis;
Query OK, 1 row affected (0.03 sec)

mysql> CREATE DATABASE bootvis;
Query OK, 1 row affected (0.00 sec)

mysql> USE bootvis
Database changed
mysql> CREATE TABLE graph_edges
    -> (
    ->     pk int not null auto_increment,
    ->     object_id int default null,
    ->     tm timestamp not null default current_timestamp,
    ->     from_id int,
    ->     to_id int,
    ->     primary key (pk)
    -> );
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO graph_edges (from_id,to_id) VALUES (1,2),(1,2),(1,2),(1,2);
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT SLEEP(2);
+----------+
| SLEEP(2) |
+----------+
|        0 |
+----------+
1 row in set (2.00 sec)

mysql> INSERT INTO graph_edges (from_id,to_id) VALUES (2,1),(2,1);
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT SLEEP(1);
+----------+
| SLEEP(1) |
+----------+
|        0 |
+----------+
1 row in set (1.00 sec)

mysql> INSERT INTO graph_edges (from_id,to_id) VALUES (1,2),(1,2);
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO graph_edges (from_id,to_id) VALUES (2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1);
Query OK, 8 rows affected (0.08 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> INSERT INTO graph_edges (from_id,to_id) VALUES (1,2),(1,2),(1,2),(1,2);
Query OK, 4 rows affected (0.13 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO graph_edges (from_id,to_id) VALUES (2,1),(2,1);
Query OK, 2 rows affected (0.10 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> UPDATE graph_edges SET object_id = 1 WHERE object_id IS NULL;
Query OK, 22 rows affected (0.06 sec)
Rows matched: 22  Changed: 22  Warnings: 0

mysql> INSERT INTO graph_edges (from_id,to_id) VALUES (1,2),(1,2);
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO graph_edges (from_id,to_id) VALUES (2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1),(2,1);
Query OK, 8 rows affected (0.05 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> SELECT SLEEP(2);
+----------+
| SLEEP(2) |
+----------+
|        0 |
+----------+
1 row in set (2.00 sec)

mysql> INSERT INTO graph_edges (from_id,to_id) VALUES (1,2),(1,2),(1,2),(1,2);
Query OK, 4 rows affected (0.08 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO graph_edges (from_id,to_id) VALUES (2,1),(2,1);
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO graph_edges (from_id,to_id) VALUES (1,2),(1,2),(1,2),(1,2);
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO graph_edges (from_id,to_id) VALUES (2,1),(2,1);
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO graph_edges (from_id,to_id) VALUES (1,2),(1,2),(1,2),(1,2);
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO graph_edges (from_id,to_id) VALUES (2,1),(2,1);
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> UPDATE graph_edges SET object_id = 2 WHERE object_id IS NULL;
Query OK, 28 rows affected (0.06 sec)
Rows matched: 28  Changed: 28  Warnings: 0

mysql> SELECT * FROM graph_edges;
+----+-----------+---------------------+---------+-------+
| pk | object_id | tm                  | from_id | to_id |
+----+-----------+---------------------+---------+-------+
|  1 |         1 | 2012-11-20 17:29:13 |       1 |     2 |
|  2 |         1 | 2012-11-20 17:29:13 |       1 |     2 |
|  3 |         1 | 2012-11-20 17:29:13 |       1 |     2 |
|  4 |         1 | 2012-11-20 17:29:13 |       1 |     2 |
|  5 |         1 | 2012-11-20 17:29:15 |       2 |     1 |
|  6 |         1 | 2012-11-20 17:29:15 |       2 |     1 |
|  7 |         1 | 2012-11-20 17:29:16 |       1 |     2 |
|  8 |         1 | 2012-11-20 17:29:16 |       1 |     2 |
|  9 |         1 | 2012-11-20 17:29:16 |       2 |     1 |
| 10 |         1 | 2012-11-20 17:29:16 |       2 |     1 |
| 11 |         1 | 2012-11-20 17:29:16 |       2 |     1 |
| 12 |         1 | 2012-11-20 17:29:16 |       2 |     1 |
| 13 |         1 | 2012-11-20 17:29:16 |       2 |     1 |
| 14 |         1 | 2012-11-20 17:29:16 |       2 |     1 |
| 15 |         1 | 2012-11-20 17:29:16 |       2 |     1 |
| 16 |         1 | 2012-11-20 17:29:16 |       2 |     1 |
| 17 |         1 | 2012-11-20 17:29:16 |       1 |     2 |
| 18 |         1 | 2012-11-20 17:29:16 |       1 |     2 |
| 19 |         1 | 2012-11-20 17:29:16 |       1 |     2 |
| 20 |         1 | 2012-11-20 17:29:16 |       1 |     2 |
| 21 |         1 | 2012-11-20 17:29:17 |       2 |     1 |
| 22 |         1 | 2012-11-20 17:29:17 |       2 |     1 |
| 23 |         2 | 2012-11-20 17:29:17 |       1 |     2 |
| 24 |         2 | 2012-11-20 17:29:17 |       1 |     2 |
| 25 |         2 | 2012-11-20 17:29:17 |       2 |     1 |
| 26 |         2 | 2012-11-20 17:29:17 |       2 |     1 |
| 27 |         2 | 2012-11-20 17:29:17 |       2 |     1 |
| 28 |         2 | 2012-11-20 17:29:17 |       2 |     1 |
| 29 |         2 | 2012-11-20 17:29:17 |       2 |     1 |
| 30 |         2 | 2012-11-20 17:29:17 |       2 |     1 |
| 31 |         2 | 2012-11-20 17:29:17 |       2 |     1 |
| 32 |         2 | 2012-11-20 17:29:17 |       2 |     1 |
| 33 |         2 | 2012-11-20 17:29:19 |       1 |     2 |
| 34 |         2 | 2012-11-20 17:29:19 |       1 |     2 |
| 35 |         2 | 2012-11-20 17:29:19 |       1 |     2 |
| 36 |         2 | 2012-11-20 17:29:19 |       1 |     2 |
| 37 |         2 | 2012-11-20 17:29:19 |       2 |     1 |
| 38 |         2 | 2012-11-20 17:29:19 |       2 |     1 |
| 39 |         2 | 2012-11-20 17:29:19 |       1 |     2 |
| 40 |         2 | 2012-11-20 17:29:19 |       1 |     2 |
| 41 |         2 | 2012-11-20 17:29:19 |       1 |     2 |
| 42 |         2 | 2012-11-20 17:29:19 |       1 |     2 |
| 43 |         2 | 2012-11-20 17:29:19 |       2 |     1 |
| 44 |         2 | 2012-11-20 17:29:19 |       2 |     1 |
| 45 |         2 | 2012-11-20 17:29:19 |       1 |     2 |
| 46 |         2 | 2012-11-20 17:29:19 |       1 |     2 |
| 47 |         2 | 2012-11-20 17:29:19 |       1 |     2 |
| 48 |         2 | 2012-11-20 17:29:19 |       1 |     2 |
| 49 |         2 | 2012-11-20 17:29:19 |       2 |     1 |
| 50 |         2 | 2012-11-20 17:29:19 |       2 |     1 |
+----+-----------+---------------------+---------+-------+
50 rows in set (0.00 sec)

mysql>

Stage 3 : Create Query with Running Counters That Changes When Tuple Changes (call it the Tuple Change Query)

SET @r=0;
SET @old_from=-1;
SET @old_to=-1;
SELECT from_id,to_id,
@inc:=((@old_from<>from_id)||(@old_to<>to_id)),
@old_from:=from_id,@old_to:=to_id,
@r:=(@r+@inc) as group_number
FROM graph_edges;

Stage 4 : Run the Tuple Change Query

mysql> SET @r=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @old_from=-1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @old_to=-1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT from_id,to_id,
    -> @inc:=((@old_from<>from_id)||(@old_to<>to_id)),
    -> @old_from:=from_id,@old_to:=to_id,
    -> @r:=(@r+@inc) as group_number
    -> FROM graph_edges;
+---------+-------+------------------------------------------------+--------------------+----------------+--------------+
| from_id | to_id | @inc:=((@old_from<>from_id)||(@old_to<>to_id)) | @old_from:=from_id | @old_to:=to_id | group_number |
+---------+-------+------------------------------------------------+--------------------+----------------+--------------+
|       1 |     2 |                                              1 |                  1 |              2 |            1 |
|       1 |     2 |                                              0 |                  1 |              2 |            1 |
|       1 |     2 |                                              0 |                  1 |              2 |            1 |
|       1 |     2 |                                              0 |                  1 |              2 |            1 |
|       2 |     1 |                                              1 |                  2 |              1 |            2 |
|       2 |     1 |                                              0 |                  2 |              1 |            2 |
|       1 |     2 |                                              1 |                  1 |              2 |            3 |
|       1 |     2 |                                              0 |                  1 |              2 |            3 |
|       2 |     1 |                                              1 |                  2 |              1 |            4 |
|       2 |     1 |                                              0 |                  2 |              1 |            4 |
|       2 |     1 |                                              0 |                  2 |              1 |            4 |
|       2 |     1 |                                              0 |                  2 |              1 |            4 |
|       2 |     1 |                                              0 |                  2 |              1 |            4 |
|       2 |     1 |                                              0 |                  2 |              1 |            4 |
|       2 |     1 |                                              0 |                  2 |              1 |            4 |
|       2 |     1 |                                              0 |                  2 |              1 |            4 |
|       1 |     2 |                                              1 |                  1 |              2 |            5 |
|       1 |     2 |                                              0 |                  1 |              2 |            5 |
|       1 |     2 |                                              0 |                  1 |              2 |            5 |
|       1 |     2 |                                              0 |                  1 |              2 |            5 |
|       2 |     1 |                                              1 |                  2 |              1 |            6 |
|       2 |     1 |                                              0 |                  2 |              1 |            6 |
|       1 |     2 |                                              1 |                  1 |              2 |            7 |
|       1 |     2 |                                              0 |                  1 |              2 |            7 |
|       2 |     1 |                                              1 |                  2 |              1 |            8 |
|       2 |     1 |                                              0 |                  2 |              1 |            8 |
|       2 |     1 |                                              0 |                  2 |              1 |            8 |
|       2 |     1 |                                              0 |                  2 |              1 |            8 |
|       2 |     1 |                                              0 |                  2 |              1 |            8 |
|       2 |     1 |                                              0 |                  2 |              1 |            8 |
|       2 |     1 |                                              0 |                  2 |              1 |            8 |
|       2 |     1 |                                              0 |                  2 |              1 |            8 |
|       1 |     2 |                                              1 |                  1 |              2 |            9 |
|       1 |     2 |                                              0 |                  1 |              2 |            9 |
|       1 |     2 |                                              0 |                  1 |              2 |            9 |
|       1 |     2 |                                              0 |                  1 |              2 |            9 |
|       2 |     1 |                                              1 |                  2 |              1 |           10 |
|       2 |     1 |                                              0 |                  2 |              1 |           10 |
|       1 |     2 |                                              1 |                  1 |              2 |           11 |
|       1 |     2 |                                              0 |                  1 |              2 |           11 |
|       1 |     2 |                                              0 |                  1 |              2 |           11 |
|       1 |     2 |                                              0 |                  1 |              2 |           11 |
|       2 |     1 |                                              1 |                  2 |              1 |           12 |
|       2 |     1 |                                              0 |                  2 |              1 |           12 |
|       1 |     2 |                                              1 |                  1 |              2 |           13 |
|       1 |     2 |                                              0 |                  1 |              2 |           13 |
|       1 |     2 |                                              0 |                  1 |              2 |           13 |
|       1 |     2 |                                              0 |                  1 |              2 |           13 |
|       2 |     1 |                                              1 |                  2 |              1 |           14 |
|       2 |     1 |                                              0 |                  2 |              1 |           14 |
+---------+-------+------------------------------------------------+--------------------+----------------+--------------+
50 rows in set (0.00 sec)

mysql>

Please notice that @inc changes only when the tuple changes !!!

Stage 5 : Put Tuple Change Query in a Subquery, extract Needed Data From Tuple Change Query, Run GROUP BY group_number (call it Duplicate Extraction Query)

SET @r=0;
SET @old_from=-1;
SET @old_to=-1;
SELECT group_number,from_id,to_id FROM
(
    SELECT from_id,to_id,
    @inc:=((@old_from<>from_id)||(@old_to<>to_id)),
    @old_from:=from_id,@old_to:=to_id,
    @r:=(@r+@inc) as group_number
    FROM graph_edges
) g GROUP BY group_number;

Stage 6 : Run Duplicate Extraction Query

mysql> SET @r=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @old_from=-1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @old_to=-1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT group_number,from_id,to_id FROM
    -> (
    ->     SELECT from_id,to_id,
    ->     @inc:=((@old_from<>from_id)||(@old_to<>to_id)),
    ->     @old_from:=from_id,@old_to:=to_id,
    ->     @r:=(@r+@inc) as group_number
    ->     FROM graph_edges
    -> ) g GROUP BY group_number;
+--------------+---------+-------+
| group_number | from_id | to_id |
+--------------+---------+-------+
|            1 |       1 |     2 |
|            2 |       2 |     1 |
|            3 |       1 |     2 |
|            4 |       2 |     1 |
|            5 |       1 |     2 |
|            6 |       2 |     1 |
|            7 |       1 |     2 |
|            8 |       2 |     1 |
|            9 |       1 |     2 |
|           10 |       2 |     1 |
|           11 |       1 |     2 |
|           12 |       2 |     1 |
|           13 |       1 |     2 |
|           14 |       2 |     1 |
+--------------+---------+-------+
14 rows in set (0.00 sec)

mysql>

Stage 7 : (OPTIONAL) Show Count for Each group_number

SET @r=0;
SET @old_from=-1;
SET @old_to=-1;
SELECT group_number,from_id,to_id,count(1) group_count FROM
(
    SELECT from_id,to_id,
    @inc:=((@old_from<>from_id)||(@old_to<>to_id)),
    @old_from:=from_id,@old_to:=to_id,
    @r:=(@r+@inc) as group_number
    FROM graph_edges
) g GROUP BY group_number;

Stage 8 : (OPTIONAL) Run the Show Count Query  for Each group_number

mysql> SET @r=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @old_from=-1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @old_to=-1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT group_number,from_id,to_id,count(1) group_count FROM
    -> (
    ->     SELECT from_id,to_id,
    ->     @inc:=((@old_from<>from_id)||(@old_to<>to_id)),
    ->     @old_from:=from_id,@old_to:=to_id,
    ->     @r:=(@r+@inc) as group_number
    ->     FROM graph_edges
    -> ) g GROUP BY group_number;
+--------------+---------+-------+-------------+
| group_number | from_id | to_id | group_count |
+--------------+---------+-------+-------------+
|            1 |       1 |     2 |           4 |
|            2 |       2 |     1 |           2 |
|            3 |       1 |     2 |           2 |
|            4 |       2 |     1 |           8 |
|            5 |       1 |     2 |           4 |
|            6 |       2 |     1 |           2 |
|            7 |       1 |     2 |           2 |
|            8 |       2 |     1 |           8 |
|            9 |       1 |     2 |           4 |
|           10 |       2 |     1 |           2 |
|           11 |       1 |     2 |           4 |
|           12 |       2 |     1 |           2 |
|           13 |       1 |     2 |           4 |
|           14 |       2 |     1 |           2 |
+--------------+---------+-------+-------------+
14 rows in set (0.00 sec)

mysql>

Stage 9 : Give it a Try !!!


64-Bit OLEDB Provider for ODBC (MSDASQL) issues I need to create a Linked Server from SQL 2008 SP1 64 bit instance located on a Windows 64 bit 2003 SP2 server towards a Mysql Server. As many of you know MSDASQL provider for 64 bit systems was put available by Microsoft later in a hot fix. This is what I have done so far:

Install MySQL connector.
Install MSDASQL provider.
Create ODBC connection with correct credentials. Test is succesfull.

When I try to create a Linked Server specifying the new DSN created I get the following error:

  Cannot initialize the data source
  object of OLE DB provider "MSDASQL"
  for linked server "MYSQL". OLE DB
  provider "MSDASQL" for linked server
  "MYSQL" returned message "Specified
  driver could not be loaded due to
  system error  998 (MySQL ODBC 5.1
  Driver).". (Microsoft SQL Server,
  Error: 7303)

I mention that I didn't reboot the server after installing the hotfix containing the OLEDB provider since it is a Production server and I must have a very good reason for doing this. Is the restart required?

I installed process monitor and I saw that before throwing the error SQL tries to get access to two paths:

Path: C:\Program Files\MySQL\Connector ODBC 5.1\myodbc5.dll.2.Config
Result: NAME NOT FOUND

Path: C:\Program Files\MySQL\MySQL Server 5.1\share\charsets\
Result: NAME NOT FOUND

I gave full rights on C:\Program Files\MySQL\Connector ODBC 5.1 to user which tries to get the access.

Does anybody know how I can fix this?

Thanks,

Ok, just read this on a blog. YMMV

http://www.ryanjadams.com/2011/03/dsn-windows-64bit/


DELETE IGNORE on Tables with Foreign Keys Can Break Replication I am using replication in a MYSQL database. I have read some where DELETE IGNORE command on Tables with Foreign Keys can break Replication. Is it true? If it is true then how can workaround this?

I have read it here: https://www.percona.com/blog/2012/02/02/stop-delete-ignore-on-tables-with-foreign-keys-can-break-replication/

The details of how DELETE IGNORE can be unsafe for replication is shown in that blog.

The solution to work around the issues to do either one of:

Don't use DELETE IGNORE
Use Row-Based Replication by setting binlog-format=ROW


How do I get a list of all the partitioned tables in my database? How do I get a list of all the partitioned tables in my database?

Which system tables/DMVs should I be looking at?

Methinks a better query is as follows:

select object_schema_name(i.object_id) as [schema],
    object_name(i.object_id) as [object],
    i.name as [index],
    s.name as [partition_scheme]
    from sys.indexes i
    join sys.partition_schemes s on i.data_space_id = s.data_space_id

This looks at the 'proper' place to identify the partition scheme: sys.partition_schemes, it has the right cardinality (no need for distinct), it shows only partitioned object (no need for a filtering where clause), it projects the schema name and partition scheme name. Note also how this query highlights a flaw on the original question: it is not tables that are partitioned, but indexes (and this includes index 0 and 1, aka. heap and clustered index). A table can have multiple indexes, some partitioned some not.


Why Clustered Index property is enabled on few columns and not on others? I heard that there can be only one Clustered Index? I may be wrong, but I am sure I read somewhere.

In one of my SQL Server 2005 table, I have following columns:

SerialNum ------- BigInt -------- Primary Key

District -------- nvarchar(50)

SchemeType ------ nvarchar(50)

When I open the table in design mode using SQL Server Management Studio, I notice that Clustered Index property is enabled for District, whereas it is disabled for SchemeType.

If there can be only one Clustered Index, then why District has this property enabled when SerialNum is already defined as Primary Key?

If there can be more Clustered Indexes, then how to enable this property for SchemeType column?

Primary key and clustered index are two different things, though they are often used in conjunction. A couple of potential reasons your table ended up this way:

(a) it could have been created with a non-clustered primary key using PRIMARY KEY NONCLUSTERED, then the clustered index was added.

(b) the clustered index could have been added, then the primary key added (in which case it can't be clustered since there is already a clustered index).

Either of those could have been intentional design decisions, accidents, changes in afterthought, etc.

Ignore the property in Management Studio's design view. The clustered index is the table, but it is defined by the column(s) specified. The other columns are still technically part of the clustered index, they're just not key columns in the index. Calling that "enabled/disabled" is misleading.


Why Can't I find my databases from Mysql on linux? I'm new to linux. I cannot get a list of my databases by typing SHOW DATABASES on the MySQL shell. Though this MySQL program is aware of the MySQL datadir because it is listed when I type SHOW VARIABLES LIKE "%dir", as datadir =  /var/lib/mysql. I am logged in as root.

Normally on windows, I would type SHOW DATABASES on the MySQL command console that comes with WAMPSERVER and I'd get a list of all available databases on the localhost.

Your problem has nothing to do with missing databases. You real problem stems directly from the way you logged in to MySQL. First, look at your comment
SHOW DATABASES; => information_schema, test.
SELECT USER(); => root@localhost
SELECT CURRENT_USER(); => @localhost
SHOW GRANTS; => GRANT USAGE ON *.* TO ''@'localhost'

Note the difference between USER() and CURRENT_USER()

USER() reports how you attempted to authenticate in MySQL ('root'@'localhost')
CURRENT_USER() reports how you were allowed to authenticate in MySQL (''@'localhost')

Bottom Line : You have no rights to see anything. From the the output of USER() and CURRENT_USER(), there is no root@localhost defined in the table mysql.user.
The further proof of this is the fact that you cannot see the mysql and performance_schema databases. A fresh install of MySQL would still have these databases fully visible if you had all rights enabled.
You can verify this by going into the OS and running the following:
cd /var/lib/mysql
ls -l

You should see the mysql and performance_schema databases as Linux folders. Thus, they are physically there. The problem is just the rights you have after user authentication.
The only way to fix it is to create the root@localhost user with all the rights needed. I am going to show you how to create the MySQL user root@localhost in the Linux server
Step 01) Create a script to create user
If root had a password (such as mys3cr3t), run this
SQLSTMT="GRANT ALL PRIVILEGES ON *.* to root@localhost"
SQLSTMT="${SQLSTMT} IDENTIFIED BY 'mys3cr3t' WITH GRANT OPTION;"
echo ${SQLSTMT} > /var/lib/mysql/init.sql
chown mysql:mysql /var/lib/mysql/init.sql

Step 02) Restart mysql using the init.sql file
service mysql restart --init-file=/var/lib/mysql/init.sql

Step 03) Remove /var/lib/mysql/init.sql
rm -f /var/lib/mysql/init.sql

Now, you have root@localhost with the password mys3cr3t
Give it a Try !!!


Zero Maintenance self-hosted off-line databases? I'm looking for a "Zero Maintenance" database engine that can be installed on Windows. We have a lot of customers that want our software installed on their local computers. Although we have a cloud offering, many of our customers do not use it, for various reasons.

By "Zero Maintenance", I mean:

Auto-tuning - the db doesn't lose performance over time
Auto-compacting - the database doesn't bloat over time
Resistant to corruption - in many crash scenarios, should automatically recover to a logically consistent state without human intervention
Auto-patching - nice, but not essential.

We want this because our customers don't have the expertise or budget to maintain something like SQL Server on their own systems. And we don't have the staff to do it for them. So the idea of having a database that is stable for long periods of time is very attractive.

I have found three potential candidates:

Mimer - Claims "Zero Maintenance", and is a full SQL relational db
Amazon Aurora - excluded from my consideration because it is Cloud only
InterBase - Claims "Admin free" "Rapid crash recovery" -- they also say they are fully-featured SQL database.

Our largest customer has under 2GB of data. Rather than write an abstraction layer, I would prefer coding to a single db platform that would have very low maintenance requirements on a local machine, and then scale well to the cloud. Postgres certainly works well at scale, but I'd have to research the local story more. Mimer claims to do this.

Have any of you heard of others? Any experience with any of these packages? I welcome any feedback.

Disclaimer
  I am in no way affiliated with Gupta, Gupta Technologies, OpenText or any of their subsidiaries. The statements made are my own personal experience or are statements taken directly from the "OpenText Gupta Development Tools and Databases" site.

Intro

This is my personal opinion based on a CRM system we were using back in the 90's which replicated data between salesperson's laptops and satellite and/or central database servers running the same RDBMS.

OpenText Gupta Development Tools and Databases

I'm still in possession of binder containing the following books:

SQLBase - Database Administrators Guide
SQL Console Guide
SQLBase - SQL Talk Command Reference

Managing Database

The database was very easy to maintain once you had the knack of running everything via SQL Talk, a simple SQL Tool with an an input and an output window.

There was a SQL Console back then, which gave you a bit more leverage on the system, but it wasn't really required.

In my personal experience the database (laptop and/or server database) performed at or near peak level for nearly a year before you had to set off a simple:

REORGANIZE;

Another statement to verify that everything was OK, was the simple:

CHECK DATABASE;

Backups are similar to handel (if required), by issuing one or two of the following commands:

BACKUP DATABASE FROM <database name> TO <directory name> ON CLIENT;
BACKUP LOGS FROM <database name> TO <directory name> ON CLIENT;
BACKUP SNAPSHOT FROM <database name> TO <directory name> ON SERVER;

However they did recommend to:

  BACKUP SNAPSHOT is the recommend way to backup your database and log files because it is easy and provides you with a backup from which you can recover the database in one step.

Performance

The database (used to) perform(s) very well up to a size of about 2 GB, after which the database would have to have been split into multiple database files.

Comparing Size of Databases

When we had to switch from Gupta SQL Base to Microsoft SQL Server for licensing reasons, the main database of one of our "Technical Applications" which used to be 2 GB in size grew to 8 GB in size just from the conversion. No additional features added. No new indexes. Just a conversion of the DDL, content and indexes from Gupta SQL Base to Microsoft SQL Server.

Company Statements

I can fully support some of the statements made on the homepage for the RDBMS OpenText Gupta SQL Base product:

  OpenText™ Gupta SQLBase is a fully relational, high performance, embedded database that allows organizations to manage data closer to the customer, where capturing and organizing information is critical. SQLBase provides a self-recovering, highly automated embedded database architecture that enables users from corporate IT to ISVs to deploy SQLBase embedded in software solutions having automated deployment and configuration and automated maintenance. With its small footprint and low TCO, SQLBase is the embedded database of choice for ISVs and organizations around the world.

  SQLBase integrates very well with many development tools, is highly secure and available for latest operating systems. The integrated tools allow testing for developers and to setup and configure automated maintenance with ease.

...including two of their statements made on their "Benefits" summary:

  Automate Installation, Configuration and Maintenance

  SQLBase provides automated deployment and configuration. Automated maintenance further reduces total cost of ownership of database solutions that include SQLBase.

  Support for TD Mobile Mobile Workforce Apps

  SQLBase is a great central database for TD Mobile mobile workforce apps. Very easy integration into the development system and easy application deployment make SQLBase a very good choice for these types of apps. Flexible licensing options allow for easy centralized deployment.

History

The Gupta Database which was initially owned by Gupta Technologies, has been handed around quite a few times (Centurasoft, Unify, ...) and is now associated with OpenText.


Left join without duplicate rows I have two tables called record and record_history. For each record, there could be more than one history. They can be joined by id and record_id. I want to get all the record's entries with recent record_history data. I have created the query like,

SELECT rec.id, rec.name, rech1.data AS last_history_data
FROM record rec
LEFT OUTER JOIN record_history rech1 ON (rec.id = rech1.record_id)
LEFT OUTER JOIN record_history rech2 ON (rec.id = rech2.record_id AND rech2.ts > rech1.ts)
WHERE rech2.id IS NULL
ORDER BY rec.id DESC

Here, I am getting the latest one by ts. This works as long as there are no duplicate ts entries. If the recent timestamp is repeated in record_history, this query returns more than one row for a record. How can we apply the limit here on the left join to restrict duplicate rows?

Unless you are in a very old version of Postgres, you don't need the double join. You can get the same result by using a LATERAL join.
The duplicate results can be avoided in your method by adding a second condition besides the rec.id = rech2.record_id. With the LATERAL join method, the use of LIMIT  is avoiding it anyway. There can be only 1 row returned from the lateral subquery. We can add a second condition so the choice is deterministic (from the two or more rows with same timestamp):
SELECT rec.id, rec.name, rech.data AS last_history_data
FROM record AS rec
     LEFT OUTER JOIN LATERAL
     ( SELECT rech.data
       FROM record_history AS rech
       WHERE rec.id = rech.record_id
       ORDER BY rech.ts DESC
                -- ,rech.id DESC               -- optional
       LIMIT 1
     ) AS rech
     ON TRUE
ORDER BY rec.id DESC ;

Regarding how to do this with the original method (2 joins and IS NULL check), you could change the ON condition - assuming there is an id column in history table so that (id) or at least (ts, id) is unique:
LEFT OUTER JOIN record_history rech2
ON rec.id = rech2.record_id
   AND (rech2.ts > rech1.ts OR rech2.ts = rech1.ts AND rech2.id > rech1.id)

By the way, you could replace that second LEFT join and IS NULL check with a NOT EXISTS subquery with same results and possibly similar efficiency (or even with a NOT IN subquery although that needs extra care for nullable columns, not recommended).


Full backup on failure I have created a maintenance plan to take a full backup of all my databases every Sundays and a differential backup every day. Also, a log backup is triggered every hour.

However, there is an issue with this schema. If I add a new database in the middle of the week, the differential and log backup jobs will fail because they are lacking a full backup image.

I therefore need to be able to take a full backup anytime either the differential or log backup job fails.

I could for example add a new step to take a full backup in case the previous one fails. However, this full backup job will run on all my databases but I would like to only take the one that have failed.

Any way to ask the SQL server to take a full backup of new databases? It can right after the database creation or in case a of failure of a differential or log backup job.

Unfortunately the maintenance plans in SQL Server are quite limited in functionality and the option that you are looking for doesn't exist as far as I know.

As Shanky has mentioned, you would need to look at some other tool for this such as the excellent open source backup and maintenance solution from (Ola Hallengren).

This has the specific backup parameter that you are looking for @ChangeBackupType = 'Y'.  From the documentation

  DatabaseBackup checks differential_base_lsn in sys.master_files to determine whether a differential backup can be performed. If a differential backup is not possible, then the database is skipped by default. Alternatively, you can set ChangeBackupType to Y to have a full backup performed instead.


SQL Server 2008 - Cannot create index on Indexed View I am attempting to create an index on a view. The view is contained in a different schema than the underlying table it's based on. When I attempt to create the view, I get the following error:

Msg 1938, Level 16, State 1, Line 1
Index cannot be created on view 'vwCdSimThk' because the underlying object 'CDSIM_Thk' has a different owner.

What I find confusing about this is that I have created this exact same view and index on at least 4 different SQL Server servers (each containing the same database schema) without getting an error like this. But on this one particular SQL Sever, I'm getting the error. Just to be clear, on all the other servers where this works, the view and underlying table it's based on are in different schema's.

Can anyone shed some light on this? Is there a server setting that needs to be changed to allow this?

Well apparently the owner is different.
You might have done that without even realizing it.

Try changing the owner:

ALTER AUTHORIZATION on CDSIM_Thk TO <whoever, dbo for instance>


Repair MySQL table without downtime We have a corrupt MyISAM table in our production MySQL database.  Is there a method for repairing this table without locking it?

We were considering using a method similar to Facebook's Online Schema Change or Percona's pt-online-schema-change.  The idea would be to: (1) copy the table, (2) monitor changes to the original using triggers, (3) repair the copy, and (4) finally, swap the repaired table and the original by renaming them.

Will something like this work or is there another recommended technique for repairing a corrupt table in a production database without downtime?

Realistically, there is no online method for table repait.
There are two techniques to repair mydb.mytable
TECHNIQUE #1 : Repair Online
REPAIR TABLE mydb.mytable;

This will perform the table repair with mysql runnng. This will perform a full table lock so no one can access the table.
TECHNIQUE #2 : Repair Offline
To repair a table offline, move the files making up the table to another folder and perform the repair there. For example, to repair mydb.mytable using the folder /var/lib/mysql
REPAIR_OPTION="-r"
DB_NAME=mydb
TABLE_NAME=mytable
FRM=${TABLE_NAME}.frm
MYD=${TABLE_NAME}.MYD
MYI=${TABLE_NAME}.MYI
cd /var/lib/mysql/${DB_NAME}
mv ${FRM} ..
mv ${MYD} ..
mv ${MYI} ..
cd ..
myisamchk -${REPAIR_OPTION} ${MYD}
myisamchk -${REPAIR_OPTION} ${MYI}
mv ${FRM} /var/lib/mysql/${DB_NAME}
mv ${MYD} /var/lib/mysql/${DB_NAME}
mv ${MYI} /var/lib/mysql/${DB_NAME}

If -r does not work, rerun these lines using REPAIR_OPTION="-o"
EPILOGUE
Neither of these techniques will allow REPAIR TABLE operations while the file is live.


Ola Hallengren's SQL Server Maintenance Solution & CMD Syntax I'm using the free version of SQL Server Management Studio so I have to use CMD to call the SQL scripts.  The official page gives an example of the CMD syntax used to call the "DatabaseBackup" script, as follows:

Create cmd files to execute the stored procedures; for example: sqlcmd
-E -S .\SQLEXPRESS -d master -Q "EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory = N'C:\Backup', @BackupType = 'FULL'" -b -o C:\Log\DatabaseBackup.txt

I got that to work, but I can't get it to work for the "DatabaseIntegrityCheck" or the "IndexOptimize" scripts.
I guess I don't know the syntax.  I tried using the example code and just replacing the script name, but that didn't work.
Can anyone help me?

How to automate SQL Server Express database maintenance using Windows Task Scheduler and Ola Hallengren’s maintenance solution has numerous examples of using SQLCMD to run Ola's scripts.
For example:
Database integrity check
sqlcmd -E -S .\SQLEXPRESS -d Tools -Q "EXECUTE dbo.DatabaseIntegrityCheck @Databases = 'USER_DATABASES', @LogToTable = 'Y';" -b -o D:\OLA\Logs\DatabaseIntegrityCheckUserDatabases.txt

Index optimize
sqlcmd -E -S .\SQLEXPRESS -d Tools -Q "EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES', @LogToTable = 'Y';" -b -o D:\OLA\Logs\IndexOptimizeUserDatabases.txt


What is the best way to create tables for email campaigns? I have table email_sent with fields:

id
user_id
template_name
sent_at
opened clicks

And now I need to save data which was send for each email. I have several campaigns with differnt data. For example, campaign wich recommend call to english teacher, if user call to teacher until expire date service will return 10% of charge. So for each email I need to save teacher_id and expire date. I have serveral ways.

First to create table call_discount with fields: email_sent_id, teacher_id, expire.

Second to create table email_sent_data with fields: id, fieldname, value and table email_sent_to_data with fields: email_sent_data_id, email_sent_data. But I will have huge amount of data and it will be hard to make right indexies.

Third to create table email_sent_data with fields: id, teacher_id, expire. Add column email_sent_data_id to table email_sent. And if I have new campaing I will add new fields to email_sent_data.

I have postgresql 9.1. So I can't work with JSON.

True, Postgres 9.1 does not have json / jsonb data types. But there is the additional module hstore providing the data type of the same name.

hstore has been in the core release since Postgres 8.3 (it's very mature). For just text data and no nesting it's just as good as json, if not better. (jsonb introduced some new candy.)

If you only have a few dozen of sparsely populated columns I would also consider wide rows with many columns just being NULL. NULL storage is very cheap in Postgres.

Many columns vs few tables - performance wise

You could also go with an EAV (Entity-attribute-value) design. Related answer discussing pitfalls:

Is there a name for this database structure?

But consider upgrading to a current version of Postgres first. Postgres 9.1 reaches EOL in Sept 2016.


SQL Server Many-to-One replication I have 8 individual SQL Server 2008 R2 machines, each hosting 1 database. Each database has an identical table structure and schema, and entirely unique data.

I would like to establish a reporting server (may be 2008 or 2012), that consolidates the rows  from selected tables across the 8 source servers into a single instance of those tables on the reporting server. This is one-way replication (no changes will be made to the reporting server). I will need to replicate changes from the source databases with relatively low latency (say 20-30 seconds).

Furthermore, I'd like to find methods to achieve this with as little impact to the source servers as is practical.  3rd-party agents, triggers, or schema mods to those servers are difficult in my environment.

My questions:

What are promising architectures and technologies for  achieving this goal?
I've looked at SQL Server Merge Replication, but I am concerned about latency. Is this an appropriate technology for this goal?
Are there many-to-one architectures for transactional replication?
Should I be looking at 1-to-1 replication into 8 databases on my reporting server, followed by some custom merge function (a 2-step replication)?

Thanks,
John

I have 8 individual SQL Server 2008 R2 machines, each hosting 1 database. Each database has an identical table structure and schema, and entirely unique data.
  I would like to establish a reporting server (may be 2008 or 2012), that consolidates the rows from selected tables across the 8 source servers into a single instance of those tables on the reporting server. This is one-way replication (no changes will be made to the reporting server). I will need to replicate changes from the source databases with relatively low latency (say 20-30 seconds).

You can achieve this with Transactional replication. Below is how you can do it.

Note : You have to change slightly your table schema to achieve this as you have to uniquely identify that rows when you are replicating to the subscriber. As a prerequisite of T-Rep you need to have tables with PK defined.

Below is your sample table on Publisher servers that is on all your 8 servers that you want to consolidate rows on the reporting server :

CREATE TABLE Products
(
ProductID INT not null,
ProductName VARCHAR(25),
ServerName sysname default @@servername not null -- this is to identify which row is from which server ; probably add this using Alter column
)
GO
ALTER TABLE Products
ADD CONSTRAINT pk_Product_ID_ServerName PRIMARY KEY (ProductID)

On the subscriber server, you need to create the same table but with different PK to uniquely identify the rows at subscriber (not doing so, T-Rep is going to fail with PK violation - I am assuming that you cannot modify the PK structure on live PRODUCTION rather its better to modify at the subscriber)

CREATE TABLE Products
(
ProductID INT not null,
ProductName VARCHAR(25),
ServerName sysname default @@servername not null
);
GO

ALTER TABLE Products
ADD CONSTRAINT pk_Product_ID_ServerName PRIMARY KEY (ProductID,ServerName)

Below script will help you setting up T-Rep, just change the databasename, destination server name along with object name.

-- Enabling the replication database
use master
exec sp_replicationdboption @dbname = N'repl1', @optname = N'publish', @value = N'true'
GO

exec [repl1].sys.sp_addlogreader_agent @job_login = null, @job_password = null, @publisher_security_mode = 1
GO
exec [repl1].sys.sp_addqreader_agent @job_login = null, @job_password = null, @frompublisher = 1
GO
-- Adding the transactional publication
use [repl1]
exec sp_addpublication @publication = N'repl1_2005', @description = N'Transactional publication of database ''repl1'' from Publisher ''server_name\SQL2005''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'false', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO

exec sp_addpublication_snapshot @publication = N'repl1_2005', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1
exec sp_grant_publication_access @publication = N'repl1_2005', @login = N'sa'
GO
exec sp_grant_publication_access @publication = N'repl1_2005', @login = N'NT AUTHORITY\SYSTEM'
GO
exec sp_grant_publication_access @publication = N'repl1_2005', @login = N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @publication = N'repl1_2005', @login = N'server_name\SQLServer2005SQLAgentUser$server_name$SQL2005'
GO
exec sp_grant_publication_access @publication = N'repl1_2005', @login = N'server_name\SQLServer2005MSSQLUser$server_name$SQL2005'
GO
exec sp_grant_publication_access @publication = N'repl1_2005', @login = N'distributor_admin'
GO

-- Adding the transactional articles
use [repl1]
exec sp_addarticle @publication = N'repl1_2005', @article = N'Products', @source_owner = N'dbo', @source_object = N'Products', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'none', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'none', @destination_table = N'Products', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dboProducts]', @del_cmd = N'CALL [sp_MSdel_dboProducts]', @upd_cmd = N'SCALL [sp_MSupd_dboProducts]'
GO

-- Adding the transactional subscriptions
use [repl1]
exec sp_addsubscription @publication = N'repl1_2005', @subscriber = N'server_name\SQL2008R2', @destination_db = N'repl123', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'repl1_2005', @subscriber = N'server_name\SQL2008R2', @subscriber_db = N'repl123', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @dts_package_location = N'Distributor'
GO

Couple of points to note :

In sp_addsubscription make sure that  @sync_type = N'automatic'

And the article properties should be set to :

So finally, you can have rows consolidated from all (in my case 3 servers) as below :

So in summary,

Use T-Rep.
Add an additional column to the existing Publisher databases e.g. serverName to uniquely identify the rows at the subscriber.
Create  table on Subscriber having PK included as ServerName.
Create replication of the tables with @sync_type = N'automatic' and Article property set to "Keep existing object unchanged".
Run snapshot agent.
Check the consolidated data on the subscriber.


creating new table partition with values already existing in the default partition I have the following table:

CREATE TABLE orders
(
  info_date  date,
  country_code  VARCHAR,
  order_total   int,
  CONSTRAINT orders_pk PRIMARY KEY (info_date, country_code)
) PARTITION BY LIST (country_code);

CREATE TABLE orders_def PARTITION OF orders DEFAULT;

I insert some rows  with country_code 'foo' and they end up in the default partition.

After some time i decide that 'foo' deserves it's own partition, how would i do it?

As according to the doc:

  If a DEFAULT partition exists, a new partition cannot be added if
  there are any rows in DEFAULT partition such that they would otherwise
  fit in the new partition being added.

this my attempt:

begin;
CREATE TEMP TABLE temp_table ON COMMIT DROP AS
SELECT * FROM orders where country_code = 'foo';
DELETE FROM orders where country_code = 'foo';
CREATE TABLE orders_foo PARTITION OF orders FOR VALUES IN ('foo');
INSERT INTO orders_foo select * from temp_table;
commit;

however it seems like a hack.

The non-hack would be to not get into this situation in the first place, by not using a default partition when you are not really sure that that is permanently what you want.  When we fail to correctly foresee the future, sometimes we have to resort to hacks.

You could make it slightly less hack by populating a permanent table, then attaching it as the new partition, rather than juggling the data out and in with a temp table.

begin;
CREATE TABLE orders_foo (like orders);
INSERT into orders_foo SELECT * FROM orders where country_code = 'foo';
DELETE FROM orders where country_code = 'foo';
ALTER TABLE orders ATTACH PARTITION orders_foo FOR VALUES IN ('foo');
commit;

You could also combine the INSERT and DELETE into a single statement, but I don't know that that really gets you anything worthwhile, other than avoiding one opportunity to misspell 'foo'.

with t as (delete from orders where country_code='foo' returning *)
   insert into  orders_bar select * from t;


Write output from dynamic queries in PL/pgSQL to CSV file PG_CMD="psql -d portal -U portal -c "
PG_CMD_TP="psql -d portal -U portal -t -P format=unaligned -c "
abc()
{
 $PG_CMD "
        DO \$$
        DECLARE
           srowdata record;
           customer_list varchar[];
           customer_schema varchar(100);
           portal_row a.portal%ROWTYPE;
           var1 varchar(100);
           temp varchar(100);
        BEGIN
           customer_list=ARRAY(select cname from customer);

            FOREACH customer_schema IN ARRAY customer_list LOOP

               EXECUTE format('select %s.portal.*,%s.p_fb_config.*,%s.p_gplus_config.*,%s.p_linkd_config.*,%s.p_localum_config.*,
                   %s.p_sms_config.*,%s.p_twt_config.*,%s.p_webform_config.*,%s.p_wechat_config.*,%s.p_clickthrough_config.*,%s.splash.*
               from %s.portal left outer join %s.p_fb_config on %s.portal.pid = %s.p_fb_config.pid left outer join %s.p_gplus_config
               on %s.portal.pid = %s.p_gplus_config.pid left outer join %s.p_linkd_config on %s.portal.pid = %s.p_linkd_config.pid left
               outer join %s.p_localum_config on %s.portal.pid = %s.p_localum_config.pid left outer join %s.p_sms_config on
               %s.portal.pid = %s.p_sms_config.pid  left outer join %s.p_twt_config on %s.portal.pid = %s.p_twt_config.pid left outer join
               %s.p_webform_config on %s.portal.pid = %s.p_webform_config.pid left outer join %s.p_wechat_config on
               %s.portal.pid = %s.p_wechat_config.pid  left outer join %s.p_clickthrough_config on
               %s.portal.pid = %s.p_clickthrough_config.pid left outer join %s.splash on %s.portal.pid=%s.splash.pid;', customer_schema,
                   customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,
                   customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,
                   customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,
                   customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,
                   customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,
                   customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema) INTO srowdata;

                   raise notice ' %: %', customer_schema,srowdata;
            END LOOP;
        END;
        \$$";
}

abc

I have written anonymous block in plpgsql. Now, instead of using raise notice I want to write this output in CSV format. I tried COPY but it accepts argument as a query.

There is a massively simpler solution.
SQL code
After removing much of the cruft, it boils down to this:
DO
$do$
DECLARE
   customer_schema text;
BEGIN
   FOR customer_schema IN
      SELECT cname FROM customer
   LOOP
      EXECUTE format('COPY (
         SELECT p.*, t1.*, t2.* -- etc. Or just: *
         FROM        %1$I.portal         p
         LEFT   JOIN %1$I.p_fb_config    t1 USING (pid)
         LEFT   JOIN %1$I.p_gplus_config t2 USING (pid)
         -- etc.
         ) TO $$/absolute/path/to/my/%1$I_file.csv$$;'
       , customer_schema);
   END LOOP;
 END
$do$

Simplify the format() call. Reuse the same parameter many times. Read the documentation here.

No need to stuff the cnames from your customer table into an array for processing. Just use a FOR loop.

Standard techniques like table aliases and the USING clause help to simplify SQL code.

Shell function
Adding that back into your shell function:
PG_CMD_TP="psql -d portal -U portal -t -P format=unaligned -c "
abc()
{
 $PG_CMD_TP '
DO
$do$
DECLARE
   customer_schema text;
BEGIN
   FOR customer_schema IN
      SELECT cname FROM customer
   LOOP
      EXECUTE format($f$COPY (
         SELECT *
         FROM        %1$I.portal         p
         LEFT   JOIN %1$I.p_fb_config    t1 USING (pid)
         LEFT   JOIN %1$I.p_gplus_config t2 USING (pid)
         ) TO $p$/absolute/path/to/my/%1$s_file.csv$p$;$f$
       , customer_schema);
   END LOOP;
 END
$do$';
}

abc

Voilá.

Enclose the whole query in single quotes for the scope of the shell. Unlike with double quotes the shell does not try any substitutions and the string is passed as is (special characters like $ lose their special meaning, too).

Switch to dollar-quoting in the plpgsql code to go along with the outer single quotes in the shell (or escape all single quotes properly).

For a quick solution I named the output files after customer_schema in the example. You may need to sanitize it to be a legal for filename syntax.


exec master..xp_cmdshell Can anyone help me resolve the syntax in the following script? Here @Name and @DBname are declared above.

exec master..xp_cmdshell 'echo 'alter login QUOTENAME(select @Name) with default_database =  QUOTENAME(select @DBname) >> C:\test\test_script.txt''

Thanks

Try something like this:

Declare @Name varchar(30) = 'Bob'
Declare @DBname sysname = 'TestDb'
Declare @Cmd varchar(4000)
set @Cmd = 'echo ' + 'alter login ' + QUOTENAME(@Name) + ' with default_database = ' + QUOTENAME(@DBname) + ' >> C:\test\test_script.txt'
print @Cmd
exec master..xp_cmdshell @cmd


Sync while Restore Operation I am trying to restore the Latest content to one of my Primary Replica Database which is part of AOAG.

My question is during the Restore operation,

1) Will the synchronization still happens between Application and DB server ?

2) During the DB restoration will the DB which is part of AOAG is locked out so that the synchronization with Secondary AOAG replica not happens ?

1) Will the synchronization still happens between Application and DB server ?

No synchronization will not happen during restore operation

  During the DB restoration will the DB which is part of AOAG is locked out so that the synchronization with Secondary AOAG replica not happens ?

Please note that you cannot just go ahead and restore database which is part of availability group, you would have to evict database from AOAG, restore it and then add it back to AOAG see This Link. The query would look like below(shamelessly copied from This SE thread)

--on primary

ALTER AVAILABILITY GROUP MyAG REMOVE DATABASE AdventureWorks2012;

--on primary

RESTORE DATABASE AdventureWorks2012
   FROM AdventureWorksBackups
   WITH NORECOVERY,
      MOVE 'AdventureWorks2012_Data' TO
'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\NewAdvWorks.mdf',
      MOVE 'AdventureWorks2012_Log'
TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\NewAdvWorks.ldf';
RESTORE LOG AdventureWorks2012
   FROM AdventureWorksBackups
   WITH RECOVERY;

--on secondary

RESTORE DATABASE AdventureWorks2012
   FROM AdventureWorksBackups
   WITH NORECOVERY,
      MOVE 'AdventureWorks2012_Data' TO
'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\NewAdvWorks.mdf',
      MOVE 'AdventureWorks2012_Log'
TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\NewAdvWorks.ldf';
RESTORE LOG AdventureWorks2012
   FROM AdventureWorksBackups
   WITH NORECOVERY;

--on primary

ALTER AVAILABILITY GROUP MyAG ADD DATABASE AdventureWorks2012;

--on secondary

ALTER DATABASE AdventureWorks2012 SET HADR AVAILABILITY GROUP = MyAG;