Log Shipping in lieu of replication I have two SQL Server 2008 R2 systems that, due to network restrictions, cannot use SQL Server replication. However, data needs to be shared between these systems. It may be possible to send a file from one system to another, however.

What I am thinking is possible setting up Log Shipping. I know that to use Log Shipping,the secondary database must be in a NORECOVERY state, so it cannot be used for live OLTP.

What I am thinking is maybe on the secondary server, I could have the secondary database, then have that database replicate to a database on the same local server, and that database would then be the live OLTP database.

I assume replication can't happen while in NORECOVERY mode. Would it be possible to run Log Shipping restores say, every 5 minutes, first changing to NORECOVERY, then doing the restore, then going back to recovery, allowing replication to continue? Is there a TSQL command to change the status to NORECOVERY, and then back?

I'm no SSIS guru by any means, but maybe that's a better way to go (flat file)? Would that be possible to set up in a database with 150+ tables, complete with FK dependencies, where all changes need to be reflected on the target database on a pretty frequent basis

You could do log shipping with the standby option rather than norecovery.  The database would be read-only, and the users would get kicked off during each restore, but it might suit your needs better.


A join that uses ordered phyical records is more efficient than others? If i execute a join that uses ordered phyical records, it will be more efficient than a join that does not have the physycal records ordered?
References are welcome.

Most database platforms have different algorithms when joining tables. How tables (or streams of data, really) are joined depends on a number of factors, including the number of rows, the ordering of the rows and the type of join (for instance, if it's a so-called "equi-join" or not).

Assuming the following example:

SELECT a.col1, a.col2, a.col3, b.col4, b.col5, b.col6
FROM table_a AS a
INNER JOIN table_b AS b ON a.x=b.x AND a.y=b.y;

If both table_a and table_b are indexed on the columns (x, y), a highly optimal strategy would be to join those two could be to merge those two datastreams (in SQL Server and other platforms known as a Merge Join). This is particularly true if you add ORDER BY a.x, a.y;, in which case you would want to maintain the ordering from the underlying table.

As a general answer: imagine if your query was two decks of cards in your hands. How would you join them? Would you lay them out in order and merge them (a merge join), perhaps go through one card at a time from one deck and look for matching cards in the other deck (a nested loop join), or maybe subdivide all the cards into 10-15 smaller heaps and join those (more like a hash join)? How would that strategy change if one or both of your decks was ordered or not?


How to copy view from MS SQL to PGSQL? I have a MS SQL Server running in a different country (hence VPN, hence long round trip delay) and delveloping a PowerQuery model on a view located on that MS SQL Server.
Everytime I change something in the model, it reloads the view which is consuming a lot of time.
I'd like to:

setup a local PostGreSQL Server on my Mac (done already)
mirror the MS SQL View data into a PostgreSQL Database
connect the powerQuery to the local PostgreSQL Server (easy)

I'm struggling with "2", mirror the content of a view into the PostgreSQL server without using direct SQL (TCP1433) which I cannot use.

How can I mirror the data to the PostgreSQL Server using ODBC only?

BTW: I have no direct access to the MS SQL Server apart from the ODBC connection

EDIT: If required, I can also install a MS SQL Express Edition if that helps in any way. Thanks

Yes, mirror the content of the view to a PGSQL. But never mind, I was able to do it with a local SQL express installation very easily.


How should I set up these tables in my relational database? I'm working on a project where a person can create a survey with custom questions and display the questions in whatever order they choose. They can break the questions into sections. The problem I'm having is that there are 2 or more groups of people. Each group can have the same question in different sections and/or in a different order. My tables right now are:

Group: key(gId), gName
Question: key(qId), qName
Section: key(sId), sName
QOrder: key(gId, qId), sId, qOrder

Is this okay or is there a better/more efficient way of doing this? Any help would be appreciated.

Looks pretty good to me.

I'd also put unique index on qOrder, and all the *Name fields.

http://dev.mysql.com/doc/refman/4.1/en/constraint-primary-key.html
http://dev.mysql.com/doc/refman/5.6/en/create-index.html


Database revision needed for sports events database This is not a code review but more of a database schema review.

I have a sports events website that I am doing in my spare time. I need assistance with the database design. I am about 80% completed. It sounds simple, but it’s more than just the event name and start and end dates.

Let me give you the scenario. I am busy developing a website where I want to display different types of events on this website like mountain biking, road cycling, running, trail running, triathlon, biathlon, etc on it. The reason behind me wanting to do this is to keep my skills up to date and let me experiment with technologies that I don’t normally use at work. I am a mountain biker myself and sometimes it becomes a mission looking for events because every event has its own website. So I thought about creating a centralised database and website to list all these types of events.

The more detail I want to display per race the more complex the database becomes. I created the first table and called it Events. And event has only a name, like:

Die Burge MTB Challenge
Resolution Health Ride the Rhino

The Die Burger MTB Challenge is a mountain bike race that takes place on 1 day. There are different distances that a rider can enter, for example 75km, 60km, 50km, etc. Only individuals can enter, no teams are allowed. Each distance has its entry fee. The start and end venue are at the same venue.

The Resolution Health Ride the Rhino takes place over 3 days. There are different categories of difficulty available for a rider to enter, namely the Black Rhino, White Rhino and Junior Rhino. All 3 categories takes place over 3 days. The Black Rhino consists of the following distances, 80.7km, 89km and 90km. The White Rhino also consists of 3 shorter distances as well as the Junior Rhino. Individuals and teams can enter for the Black Rhino and White Rhino. Only teams can enter for the Junior Rhino. The fees are per individual or team, for example R3800 for an individual and R7600 for a team. There are early bird entry fees and late entry fees. There are also different packages that you can choose, like All Inclusive package that contain a lot of stuff like accommodation, food, showers, etc and a self-catering package where you need to organise your own accommodation. The start and end venues are different each day. Day 1 point start at Venue A and end at Venue B. Day 2 might start at Venue B and end at Venue B, and Day 3 might start at Venue B and end at Venue C.

So I broke it down to create a another table called Races. This would include something like “75km Route” for the one day event or “Black Rhino” for the multi day event. I broke it down even further and added a disciplines table. The disciplines table just tells you what you will be doing on the day. For example, the race type might be triathlon, and triathlon consists of swimming, cycling and running.

This is what I currently have. I know I am missing lots, but if you could maybe guide me on the right path then I would appreciate it. Please feel free to chop and change as you like.

Clearly you need a "Competitor" table if you wish to represent them, which I assume you do! Competitor Name, Age, Sex, Contact etc.

Personally I would create a new table called "Discipline Type" which would differ from "Race Type".  The two are functionally independent - race type could be "Triathlon" while "Discipline Type" could be "Swimming"/"Running"/"Road Cycling".

Edit:
Sorry, since you wish to include teams, a team will represent a group or a single competitor. Therefore you probably want an "Entry" table which represents a person or Team entering a race Entry would have "Race ID", "Competitor ID" (these two your PK, required), "Team ID", "Team Order", "Team Captain" (binary field, last 3 fields not required). This would then be able to represent individual entries AND team entries. The "Team ID" would link to a "Team" table and would have "Team Name", "Support person contact" etc. Note that the team info is NOT required, so that you can have the same Entry table represent a single entrance from a team or a single entrance from 1 person.


SSIS Package Not Being Deployed I have an issue with an SSIS package, that when deployed via VS2017 to the integration services catalog is not actually being deployed. This is an individual package deployment, and not a project deployment. There are no error messages, and the deployment appears to go through fine. Checking the initial packages and projects tables indicates that although the deployment has happened (project timestamp gets updated), the latest version of the package code has not really been deployed as the version number on the packages table does not match the latest version from within Visual Studio.

In fact, the issue is exactly as described in this previous post by another member, only there was no update on the resolution on that post. I'm hoping that someone else may have come across this situation.

Why is my SSIS package not getting updated?

Some additional information that may assist:

Right click, Project deployment - this works (we've tried this with another    project, although for the project we're really interested in we cannot do that as not all the packages are ready for deployment at a project level).

Right click, Package deployment - this doesn't work (deployment is reported as a success, but evidently the package has not been deployed).

Calling the ISDeploymentWizard from the command line for the package in question doesn't work either. Again, no errors reported, but nothing deployed.

When using the wizard via Visual Studio, the XML you can save post-deployment reports 'success' at all stages.

Thank you.

Package deployment model has nothing to do with SSISDB. Instead when Package deployment model deploys to SQL Server it ends up in the msdb database in the sysssispackages table. Follow these instructions to view deployed packages. Or you can query that table.


MySQL query WHERE IN + ORDER BY, which EXPLAIN is better and how to avoid filesort? This is my table definition, without any special index for now:

CREATE TABLE `filter` (
  `field_name` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `category_id` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `position` smallint(6) NOT NULL,
  `options` longtext COLLATE utf8_unicode_ci COMMENT '(DC2Type:json_array)',
  PRIMARY KEY (`field_name`,`category_id`),
  KEY `IDX_702C956612469DE2` (`category_id`),
  CONSTRAINT `FK_702C956612469DE2`
      FOREIGN KEY (`category_id`) REFERENCES `category` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Sample data:

+------------+-----------------------+-------------+----------+
| field_name |        options        | category_id | position |
+------------+-----------------------+-------------+----------+
| color      | {"label": "Color"}    |        1895 |        1 |
| material   | {"label": "Material"} |        1895 |        2 |
| color      | {"label": "Color"}    |        1896 |        1 |
| color      | {"label": "Color"}    |        1897 |        1 |
+------------+-----------------------+-------------+----------+

Most of the time I do a query with WHERE IN and ORDER BY position and selecting only field_name and options:

EXPLAIN SELECT
  field_name
  options
FROM
  filter
WHERE
  category_id IN ('1895', '1896', '1897')
ORDER BY
  position ASC

This gives me Using index condition; Using filesort, quite bad uh?

{
   "query_block":{
      "select_id":1,
      "ordering_operation":{
         "using_filesort":true,
         "table":{
            "table_name":"filter",
            "access_type":"range",
            "possible_keys":[
               "IDX_702C956612469DE2"
            ],
            "key":"IDX_702C956612469DE2",
            "used_key_parts":[
               "category_id"
            ],
            "key_length":"98",
            "rows":5,
            "filtered":100,
            "index_condition":"(`filter`.`category_id` in ('1895','1896','1897'))"
         }
      }
   }
}

With an index on category_id, position Using where; Using index; Using filesort:

{
   "query_block":{
      "select_id":1,
      "ordering_operation":{
         "using_filesort":true,
         "table":{
            "table_name":"filter",
            "access_type":"range",
            "possible_keys":[
               "IDX_702C956612469DE2",
               "IDX_CATEGORY_POSITION"
            ],
            "key":"IDX_CATEGORY_POSITION",
            "used_key_parts":[
               "category_id"
            ],
            "key_length":"98",
            "rows":5,
            "filtered":100,
            "using_index":true,
            "attached_condition":"(`filter`.`category_id` in ('1895','1896','1897'))"
         }
      }
   }
}

So which explain is better? I would say the first because of using index condition (there is no "where" part). Is there any way to avoid the filesort?

Given the structure of multicolumn B-tree index it is not viable to do a sort on position when using IN on category_id. But as the test data suggest the position is not "global" but seems to have a meaning only for given category_id. So as I suggested in comments, it is instead possible to ORDER BY (category_id, position) - that can use two-column index on those columns to get it without filesort. http://sqlfiddle.com/#!9/aabaa/7

Because of the longtext column it is not possible to turn this into an index-only scan (text columns can be only indexed on prefix, not on entire value, as index key length is limited).

Using varchar fields as primary key has some drawbacks, but it is not "wrong" by itself so just a little suggestion - would it make sense in your desing to use some integer ids instead?


Restore accidentally truncated table I truncated the wrong table, but I have a backup made an hour before the incident.

I am trying to restore but at 30% I get the error:

  Restore of database 'msdb1740' failed.
  (Microsoft.SqlServer.Management.RelationalEngineTasks)

  System.Data.SqlClient.SqlError:
  Write on "C:\...\Backup\msdb1740_LogBackup_2016-03-19_09-50-09.bak"
  failed: 112 (There is not enough space on the disk.)
  (Microsoft.SqlServer.SmoExtended)

I have freed up some extra disk space but had no luck. I have transferred the .bak file to another computer and tried it there but that gives me a permissions error.

How can I restore this database, or even better, can I restore just one table in the database?

It is not possible to restore a single table from an earlier point in time directly into an existing database.

You can restore all or part of the database as a new database, then copy the required data across. Or, you can restore using the REPLACE option, but the whole database will be reverted to the earlier point in time, not just the table you are concerned about.

If all your data is stored in the PRIMARY filegroup (the default), you have no choice but to restore the whole thing. If you have multiple filegroups, you may be able to restore just some of the filegroups, reducing the space needed. The details depend on the database recovery model as well as how any filegroups are arranged, see:

Piecemeal Restores (SQL Server 2012)

You can determine in advance how much space will be needed for the restore by running the following command and inspecting the output:

RESTORE HEADERONLY (Transact-SQL)

The paths to the files that will be written are all from the perspective of the SQL Server instance, not the user performing the restore. You will need to ensure that enough space is available for all the files that will be restored. Note that the size of the file may be larger than the amount of data stored in the file, and larger than the size of the backup file. SQL Server only writes the data actually stored to a backup, which may also be compressed, but a restore must create the full file size.

The process of creating the data files will be much faster if Instant File Initialization is enabled on the restore target.

Backup and restore is a large topic, well covered in the documentation, see:

Back Up and Restore of SQL Server Databases

The T-SQL RESTORE command offers more flexibility and control than using the SQL Server Management Studio UI.

If you have differential and/or transaction log backups as well as a full database backup, you may be able to apply one or more of these to get closer to the point in time when the accidental truncation occurred.


How to get a new pid file of Mysql and restart Mysql Successfully? Due to lost of pid file of my server, I can't stop mysql using normal commands like

service mysql stop

which will show

ERROR! MySQL server PID file could not be found!

So I try to stop all mysql processes and restart mysql mannually

I've searched the Internet and found one solution, but it doesn't work for me.

I run

ps aux | grep mysql

and the result shows

root      6490  0.0  0.2 112720  2332 pts/0    S+   03:48   0:00 grep --color=auto mysql

So I type

kill -9 6490

but the result is

-bash: kill: (6490) - No such process

It seems every time I try to kill it, the pid changes.

How can I really stop all mysql processes? Then be able to get a new pid file? New to linux and mysql, thank you in advance!

Update:

Thanks to Henryk Gerlach's answer, it seems I have shut down all Mysql processes, but after I restart my server the pid file doesn't auto-generate. How should I do to really make my Mysql back to normal??

The result you are showing:

root      6490  0.0  0.2 112720  2332 pts/0    S+   03:48   0:00 grep --color=auto mysql

is actually the pid and process for the  ps aux | grep mysql command. It is not actually a process for MySQL.

A running process for MySQL would look similar to this:

mysql     1894  0.8  9.8 1865804 1192032 ?     Sl   Jul01 300:53 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/DBNAME.err --pid-file=/var/lib/mysql/DBNAME.pid

You can use the command pkill to kill the running MySQL processes by name. However, it's not recommended to kill MySQL in a forceful manner.

 pkill -f mysql

This will match the filename pattern for any part of the command line

To start/stop the MySQL services, you can run:

service mysqld start
service mysqld stop
service mysqld restart

or

service mysql start
service mysql stop
service mysql restart


Performance Monitor % Processor Time metric difference between _Total and sqlservr.exe I have SQL Server running on 32-core CPU, and I use Performance Monitor (perfmon.msc) to collect data on Processor: % Processor Time (Instance - _Total) and Process: % Processor Time (Instance - sqlservr.exe)

So for example for day 2019/07/21 (Sunday), it shows average values:

Processor: % Processor Time (Instance - _Total) - 3.959
Process: % Processor Time (Instance - sqlservr.exe) - 107.312

Why is such a difference ?

I expected sqlservr.exe to show values like 2.900 or 3.100 - something like this, but not 107.312

How to correlate _Total to sqlservr.exe ?

I am using MaxDOP limited to 8 server level setting on SQL Server, if this is any help

The Process: % Processor Time (Instance - sqlservr.exe) counter takes into account all of your cores. If you divide by the cores you get an answer much similar to what you expected.

This TechNet article explains it in far more detail but it uses this calculation to get the number you see:
(No of Logical Cores * 100),  So this is going to be a calculated over a baseline of more than 100.


How to avoid the SORT operation in SQL query that contains ORDER_BY condition? I am optimising a PostgreSQL query which involves three tables and a ORDER_BY condition. It seems that PostgreSQL prefer to arrange a SORT operation to perform the ORDER_BY condition, rather than using the index.

The raw SQL looks like this:

SELECT activity.provider, activity.provider_id, activity.crawled_at, activity.raw, activity.account_provider_id, activity.created_at
FROM activity
JOIN friendship ON (activity.provider, activity.account_provider_id) = (friendship.provider, friendship.friend_provider_id)
JOIN token ON (token.provider, token.account_provider_id) = (friendship.provider, friendship.provider_id)
WHERE token.uid = 1
ORDER BY created_at desc NULLS LAST
LIMIT 10 OFFSET 10000;

I have already created an index provider, account_provider_id, created_by on the activity table.

CREATE INDEX activity_provider_account_provider_id_created_at_idx
  ON activity
  USING btree
  (provider COLLATE pg_catalog."default", account_provider_id COLLATE pg_catalog."default", created_at DESC NULLS LAST);

However, the query plan returned by PostgreSQL requires an expensive SORT operation.

This query would take more than 10 seconds to finish. I also tried set enable_sort = false, but it didn't work. How should I optimise this query?

EDIT: I have added the execution time graph. The SORT operation is the most expensive part of the query. Without the ORDER_BY, the query returns pretty fast.

ORDER BY is not that expensive by itself. The problem here is that it is used together with OFFSET. So postgresql does sorting of all data before it can take needed 10 rows.

You are probably implementing paging using ORDER BY + OFFSET. This is a known postgresql limitation and there is postgresql way to handle this.


When is it appropriate database design to use a separate schema? I am developing a database which has data sourced from many different applications.  In my first pass at design, I placed the staging tables each in a schema named for their source application.

As several of the source applications have similar data and similar table names, I use the schema name to differentiate the source application.  The alternative I am considering would be using a single schema and including the source application in the table name.

I wanted to look into the design rules pertaining to when to use a different schema and the pros and cons of doing so and I could not find anything.

Is the schema purely for permissioning and security?

Does it make sense from an organizational point of view to create objects in separate schemas beyond what is required for application development or is this just needlessly adding complexity to queries?

Are there any other repercussions of this decision which I have neglected to consider?

I am going to mention both logical and physical design here.

I know IBM recommends placing staging tables into their own schema(s) and even into their own table spaces (database implementation specific to DB2 in this case) for reasons of management. Schemas allow you to manage better via separation of concerns and security. The tablespaces (physical design) allow you to better handle maintenance issues regarding REORGs, RUNSTATS, and backups.

While you may not be implementing IBM DB2, I think their philosophy is something worth thinking about and implementing.


How to commit transaction on an after update event trigger? I'm new here in dba.stackexchange.com, so if I were to make any mistake, my apologies in advance.

I kinda confused on how trigger work. So I have a trigger called updcredaccess on table A that updates table B on after update event. Then I need the currently updated value on table A for other table to inspect, so I used autonomous transaction trigger. But then when I update, the value on table A doesn't seems to be updated with the current value.

Here is my table

Table A
id varchar(10)
val varchar(10)

Table B
id varchar(10)
val varchar(10)

and here is my trigger
Table A trigger

create or replace trigger updA
after update on A
for each row
declare
pragma autonomous_transaction;
cek varchar2(5);
begin
commit;
select val into cek from A where id = :new.id;
dbms_output.put_line('Cek cek: ' || cek);
update B set val = :new.val where id = :new.id;
commit;
end;
/

And for table B
 Table B trigger

create or replace trigger updB
after update on B
for each row
declare
v varchar2(5);
begin
select val into v from B where id = :new.id;
-- Some other pl/sql code --
end;
/

I then input 2 records like

 insert all into A values ('a', 'false') into A values ('b', 'false') select * from dual;

Then i tried this code

 update A set val = 'true'

The query works fine. But I was so confused because at the log console it says

  Cek cek: false (The output log at trigger A)

The value still hasn't changed.

So, what I need is the current new val value at the 2nd trigger, so It can be processed. Can anyone help?

Why do you expect B to be updated, when you inserted a row with a different id? Actually, your INSERT ALL does not insert anything into B.

The above works fine after fixing your trigger and sample data:

create or replace trigger updB
after update on B
for each row
declare
v varchar2(5);
begin
  v := :new.val;
end;
/

This is how you reference the new val in the trigger, not with a select. Your original trigger would run into the infamous ORA-04091 "table is mutating" error.

SQL> insert all into A values ('a', 'false') into b values ('a', 'false') select * from dual;

2 rows created.

SQL> commit;

Commit complete.

SQL> update A set val = 'true';
Cek cek: false

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from a;

ID         VAL
---------- ----------
a          true

SQL> select * from b;

ID         VAL
---------- ----------
a          true

For the output:

Your updA trigger runs in an autonomous transaction. When you select val from A, your original update is still not finished (commited), you will see the old value. Fixing your trigger for A:

create or replace trigger updA
after update on A
for each row
declare
pragma autonomous_transaction;
cek varchar2(5);
begin
commit;
  cek := :new.val;
dbms_output.put_line('Cek cek: ' || cek);
update B set val = :new.val where id = :new.id;
commit;
end;
/


Audit succesfull logons , loging every 2 seconds I created an audit to audit only succesfull logons. But the problem is, I did it in a test environment and it's loging a connection every second

Did I do something wrong ? I only selected SUCCESFULL LOGIN GROUP in the specification.
also is there a way to know how many audits for each file created?
and, is there a way to reduce the ammount  of fields being audited?
EDIT: damm 2 weeks and I didnt find an answer for this.

I feel like this is normalish behavior but can't find any information on why. The most I see is that Microsoft's docs state that specification also logs connection reuse from the connection pool:
Events in this class are raised by new connections or by connections that are reused from a connection pool.

It does seem to be a common issue for people trying to audit logins in the same sense as you are, e.g. this Reddit post.
You may want to try Tony Hinkle's accepted answer on this StackOverflow as a workaround and see if you stop getting redundant logs in your audit. (The drawback with this solution is you have to specify each Login name you want to audit, but maybe this is a plus depending on your end goal.)


Stored Procedure stuck at waitfor delay '00:00:02' I have a stored procedure that's supposed to run every night.  The procedure deletes data in batches of 10k rows, and between each batch issues a WAITFOR DELAY '00:00:02' with the intention of letting other processes do things in between batches (I didn't write this, this is the intention I was given).

The job ran and has been running for a while now.  I ran sp_WhoIsActive and it shows this sp is suspened with the waitfor statement the sql text.  Why would sql server keep this from processing further?

Additional information:

It's been running for over a day.  It should have deleted 400k records, and has deleted 10k of them.

sp_whoIsActive output:

  dd hh:mm:ss.mss 01 06:32:28.126

  session_id 58

  sql_text

  login_name [redacted]

  wait_info (131ms)WAITFOR

  CPU 42,655,358

  tempdb_allocations 7,981,282

  tempdb_current 130

  blocking_session_id NULL

  reads 1,862,017,930

  writes 26,792,391

  physical_reads 273,169

  locks

  used_memory 4

  status suspended

  open_tran_count 0

  percent_complete NULL

  database_name [redacted]

  program_name SQLAgent - TSQL JobStep (Job
  0xA887960BA5034D4F89EEBDA2CB934921 : Step 2)

This could be expected behaviour especially if you're deleting based on an index (aka where [primarykeyfield] < targetID)

since that delete will take in the ms of time you'll almost always catch the process in the wait time between the two batches

Say for example the delete takes 10ms (i imagine it will potentially take less than this but its just an example), the wait time is 2000ms

So for every time you run the usp_whoisactive command there is a 10/2010 chance of hitting that in the period of time the delete is running (less than 0.5%) and a 2000/2010 chance of catching it during the wait for delay.

If you want to observe it directly find the spid that its running over and run the profiler over it checking for the SP:StmtCompleted search and you'll see the delete occurring every 2s and going into the waitfor (note if you wan to see the start of the waitfor as well check the SP:StmtStarting but the completed one will give you the duration of how long it takes to run each part.


Moving a collection to a new database (v2.2) To take advantage of the improved concurrency in Mongodb 2.2--namely database level locking--we would like to move a sharded collection to a new database.

I have been unable to find any documentation which will help with this particular scenario.

I know I cant just "rename" this sharded collection: https://groups.google.com/d/topic/mongodb-user/lajAcp3HkZ4/discussion

The only thing I can think of is mongodump the entire collection and mongorestore into a new database. What are my other options?

A mongodump and mongorestore (or a similar script to accomplish the same thing) to backup and re-import the data is the most straight forward way of doing this at the moment until SERVER-6063 is implemented.

The only other option I can think of would be to move all chunks to a single shard, wipe out the sharding config for the collection (effectively unsharding it) and then do the rename on that shard.  Once that was complete, you could re-shard the collection.

This would be:

Dangerous (messing with the config meta data is always a bad idea)
Unsupported (if you get into trouble, it's probably up to you to fix it)
More complicated (since the first recommended thing to do would be to backup your data via mongodump)

As one other alternative - can you move everything else (perhaps unsharded) out of this database?  Obviously this would not be helpful if you have other large sharded collections in this DB, but just wanted to make sure it was considered.


Should "Long Running with Low CPU" be expected for an SPROC that performs millions of INSERTS? Brent Ozar's sp_BlitzCache flags the sproc below as the most "expensive" one in my system ("Frequent Execution, Long Running With Low CPU").  The sproc just checks a table for a row corresponding to a pair of input parameters which together define the table's PK.  If the row exists, the sproc does nothing; otherwise it INSERTS a row into the table and exits.  The table currently has about 5.3 million rows.

The table's columns and indexes look like this:

This sproc is called by an XML shredder app I've coded to shred millions of XML files.  For every XML file at least one row and sometimes several rows get inserted into the table.  So "Frequent Execution" makes sense, but I don't understand the "Long Running With Low CPU" part, and I have no idea what to do about it if it's a problem.

Is my sproc poorly designed?  If so, how can I improve it?

/****** Object:  StoredProcedure [dbo].[checkAdminStatusDocXref]    ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[checkAdminStatusDocXref] (
    @documentID         INT,
    @adminStatusID      INT,
    @adminStatusDate    INT)
AS
BEGIN
    SET NOCOUNT ON;
    IF EXISTS (SELECT 1 FROM tblAdminStatusDocXref
                WHERE documentID = @documentID
                AND adminStatusID = @adminStatusID)
    BEGIN
        RETURN;
    END
    ELSE
    BEGIN
        SET NOCOUNT ON;
        INSERT INTO tblAdminStatusDocXref (documentID, adminStatusID, adminStatusDate)
        VALUES(@documentID, @adminStatusID, @adminStatusDate)
    END
END;

The calcuation of the "long running query with low cpu" metric in sp_BlitzCache at the time of writing is

long_running_low_cpu = CASE WHEN AverageDuration > AverageCPU * 4 AND AverageCPU < 500. THEN 1 END

With AverageCPU = (total_worker_time / 1000.0) / execution_count

And  AverageDuration  = (total_elapsed_time / 1000.0) / execution_count

Both from the dmv sys.dm_exec_query_stats;

So this means that your average cpu time for this query is lower than 0.5 seconds while your AverageDuration is higher than 4 times your AverageCPU for this query.

The difference between these two metrics would be a next step to look at to see if there is an actual problem at hand.

The reason for the difference between the two is something for you to figure out by conducting workload monitoring.

One of these common reasons include blocking, such as the insert being blocked by another query, causing low cpu time with a higher duration.

Another reason to look into would be resource limits on the database engine or the application server.


Added a new listener to my 'listeners.ora' file. How can I connect directly to it with SQLPlus? (skipping tnsnames.ora for now) I updated my listeners.ora file to include a new listener on port 1523. I then started the listener with lnsrctl start. I'd now like to connect to the database on that port via SQLPlus without specifying an entry in tnsnames.ora but I'm struggling.

So far, using the 'ezconnect method' I'm getting the following error:

SQL> connect system/Pa33w0rd@//localhost:1523/db2
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

And my listener.ora file looks like this:

# listener.ora Network Configuration File: C:\app\benfi\virtual\product\12.2.0\dbhome_2\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\benfi\virtual\product\12.2.0\dbhome_2)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\benfi\virtual\product\12.2.0\dbhome_2\bin\oraclr12.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
  )

LIST2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1523))
    )
  )

By default, PMON process registers with one listener and considers that "good enough."

This article describes how to set up two listeners and then how to manually register your database with the second listener.

You need to register the listener with the database:

alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=<IP address or hostname>)(PORT=1523))' scope=both;

You may need to issue this statement to list BOTH listeners. Otherwise, it may default to only the new, manually configured, one upon restart.

alter system set local_listener='Listener 1' scope=both;

See the Oracle Docs, where it shows how to add two listeners via comma-separation.

ALTER SYSTEM SET LOCAL LISTENER=["]listener_address["][,...];

Note that this link lists several other methods depending on the environment and what you're trying to achieve, so you may want to read that link if this first method fails to get what you want.


SELECT first value from 3 tables that is not null Having 3 tables with name(varcahr) value(varchar) pairs how do I select the value for a given name from one of the table with respect to the following conditions

select value from tab_1 if it exists for name else select it from tab_2 if it exists in tab_2 else from tab_3
do it in one giant select if possible

There are couple ways of get what you want. From the top of my head I can think about version with UNION, FULL JOIN, and LEFT JOIN. The latter seems to me as most preferable from readability and performance point of view :

SELECT NVL(t1.value,NVL(t2.value,t3.value)) as value
-- Or use coalesce instead of NVLs :
--SELECT COALESCE(t1.value,t2.value,t3.value) as value
FROM
(SELECT 'name_to_search' as name FROM DUAL)a
LEFT JOIN table1 t1 ON (t1.name= a.name)
LEFT JOIN table1 t2 ON (t2.name= a.name)
LEFT JOIN table1 t3 ON (t3.name= a.name)


Issue with Auto increment in MYSQL Current Structure in MYSQL:

CREATE TABLE `JOB_ONPREM` (
       `JOB_ID` bigint(20) NOT NULL AUTO_INCREMENT,
       `JOB_NAME` varchar(255) NOT NULL DEFAULT '',
       `SANDBOX` varchar(100) NOT NULL DEFAULT '',
       `JOB_SUB_TYPE` varchar(500) DEFAULT NULL,
       `PARENT_JOB_NAME` varchar(255) NOT NULL DEFAULT '',
       PRIMARY KEY (JOB_ID,`JOB_NAME`,`SANDBOX`,`PARENT_JOB_NAME`)
     ) ENGINE=InnoDB AUTO_INCREMENT=10493 DEFAULT CHARSET=utf8

  I want to keep JOB_NAME,SANDBOX,PARENT_JOB_NAME as Primary KEY and
  JOB_ID as Auto Increment because I am using "ON DUPLICATE KEY UPDATE"
  and because of auto-increment it is inserting new rows and creating
  duplicate in table.

  And while removing job_id from primary key. I am getting error as
  "Incorrect table definition; there can be only one auto column and
  it must be defined as a key"

The CREATE TABLE documentation says much the same as what your error message says:

  There can be only one AUTO_INCREMENT column per table, it must be
  indexed, and it cannot have a DEFAULT value.

You need to index the JOB_ID column.

ALTER TABLE JOB_ONPREM
ADD INDEX `dba269102` (`JOB_ID`)

Try that and see if afterwards you can alter the primary key. You could also make it a unique index instead, to help avoid duplicates.

ALTER TABLE JOB_ONPREM
ADD UNIQUE INDEX `dba269102` (`JOB_ID`)


How to easily bring 80 MySQL users into ProxySQL? I am setting up ProxySQL in front of a Percona MySQL server with 88 user accounts.  I don't even know most of their passwords.  Passwords are stored in MySQL as hashes but are stored in ProxySQL in plain text.  How can I easily bring in all 88 accounts into ProxySQL at once?  Or is there a pass-through authentication switch I can turn on?

Since you're using ProxySQL with Percona, there's a nifty tool made by the good people at Percona that does exactly what you're looking for (sync's mysql users to proxysql).

If you install ProxySQL using the Percona RPM, it comes with a nifty script that helps with managing your ProxySQL instance called "proxysql-admin". That script has the option to "--syncusers". Here's an excerpt of the help info for the script:

[root@localhost ~]# /usr/bin/proxysql-admin
Usage: [ options ]
Options:
  --config-file=<config-file>        Read login credentials from a configuration file (overrides any login credentials specified on the command line)
  --quick-demo                       Setup a quick demo with no authentication
  --proxysql-datadir=<datadir>       Specify proxysql data directory location
  --proxysql-username=user_name      Username for connecting to the ProxySQL service
  --proxysql-password[=password]     Password for connecting to the ProxySQL service
  --proxysql-port=port_num           Port Nr. for connecting to the ProxySQL service
  --proxysql-hostname=host_name      Hostname for connecting to the ProxySQL service
  --cluster-username=user_name       Username for connecting to the Percona XtraDB Cluster node
  --cluster-password[=password]      Password for connecting to the Percona XtraDB Cluster node
  --cluster-port=port_num            Port Nr. for connecting to the Percona XtraDB Cluster node
  --cluster-hostname=host_name       Hostname for connecting to the Percona XtraDB Cluster node
  --cluster-app-username=user_name   Application username for connecting to the Percona XtraDB Cluster node
  --cluster-app-password[=password]  Application password for connecting to the Percona XtraDB Cluster node
  --without-cluster-app-user         Configure Percona XtraDB Cluster without application user
  --monitor-username=user_name       Username for monitoring Percona XtraDB Cluster nodes through ProxySQL
  --monitor-password[=password]      Password for monitoring Percona XtraDB Cluster nodes through ProxySQL
  --without-check-monitor-user       Configure ProxySQL without checking/attempting to create monitor user
  --enable, -e                       Auto-configure Percona XtraDB Cluster nodes into ProxySQL
  --disable, -d                      Remove any Percona XtraDB Cluster configurations from ProxySQL
  --node-check-interval=3000         Interval for monitoring node checker script (in milliseconds)
  --mode=[loadbal|singlewrite]       ProxySQL read/write configuration mode, currently supporting: 'loadbal' and 'singlewrite' (the default) modes
  --write-node=host_name:port        Writer node to accept write statments. This option is supported only when using --mode=singlewrite
                                     Can accept comma delimited list with the first listed being the highest priority.
  --include-slaves=host_name:port    Add specified slave node(s) to ProxySQL, these nodes will go into the reader hostgroup and will only be put into
                                     the writer hostgroup if all cluster nodes are down.  Slaves must be read only.  Can accept comma delimited list.
                                     If this is used make sure 'read_only=1' is in the slave's my.cnf
  --adduser                          Adds the Percona XtraDB Cluster application user to the ProxySQL database
  --syncusers                        Sync user accounts currently configured in MySQL to ProxySQL (deletes ProxySQL users not in MySQL)
  --version, -v                      Print version info
[root@localhost ~]#

Notice the "--syncusers" option down there at the bottom. That's what you're looking for, yes? ... and it's made and distributed by Percona. :)

Hope this helps!


Query Governor Cost Limit relationship with Time Based on Technet, Query Governor Cost Limit refers to the estimated elapsed time, in seconds. This meets this excerpt from the Microsoft SQL Server 2008 Administrator's Pocket Consultant book.

Walking to the book Microsoft SQL Server 2012 Unleashed, pages 30 and 31, the cost limit is proportional to the estimated time, and one should apply a factor (K), to take into account the system's specifics, so it would be K x time(s).

Now, if I take this (somehow older) article, it feels like establishing a relationship between cost limit and time is not doable.

Can I say that in the real world, despite some more rare exceptions, the 2nd approach could be taken as a rule of thumb?

Based on my notes from  SQL Server internals (by Kalen Delaney).... below is my understanding

Query Governor Cost limit is the max no. of seconds that a query can run.

So, essentially :

a non-zero or a negative value will disallow execution of any query that has an estimated cost exceeding that value.
Zero which is default - turns off the query governor and all the queries are allowed to run without any time limit.

  Note that the value set for seconds isn’t clock-based. It corresponds to seconds on a specific hardware configuration used during product development, and the actual limit might be higher or lower on your machine.

Also, it should be noted that changing the Query Governor Cost limit will flush the entire plan cache resulting in generating a new plan afterwards.


How do I grant all privileges to local db2admin with DB2 9.7 on Windows? I have as my developer station a laptop with OS Windows XP Professional Edition, Service Pack 3.

I have downloaded and installed IBM DB2 UDB 9.7 fix pack 4, of the Express-C edition.

I have a local Windows account called db2admin that I am using as my local database administrator for my local install of DB2 (developer purposes only).

I can run the following without issues when running the Command Window:

db2 attach to db2 user db2admin using xxxxxxxxxx

That allows me to attach to my instance called DB2.

I can run my create database commands.

I then attempt to connect to the database to grant all privileges for my db2admin account in DB2.

db2 CONNECT TO MYDB;
SET SCHEMA DB2ADMIN;
db2 GRANT DBADM,CREATETAB,BINDADD,CONNECT,CREATE_NOT_FENCED_ROUTINE,IMPLICIT_SCHEMA,LOAD,CREATE_EXTERNAL_ROUTINE,QUIESCE_CONNECT,SECADM ON DATABASE TO USER DB2ADMIN;
CONNECT RESET;

However when I run that, it DB2 tells me that my actual windows user account (synprgcma) does not have authority to grant authority to user db2admin.

So if I change the second script to the following:

db2 CONNECT TO MYDB USER db2admin USING xxxxxxxx;
SET SCHEMA DB2ADMIN;
db2 GRANT DBADM,CREATETAB,BINDADD,CONNECT,CREATE_NOT_FENCED_ROUTINE,IMPLICIT_SCHEMA,LOAD,CREATE_EXTERNAL_ROUTINE,QUIESCE_CONNECT,SECADM ON DATABASE TO USER DB2ADMIN;
CONNECT RESET;

Then DB2 tells me that db2admin cannot revoke or grant authority to itself (actually it tells me that an id cannot revoke or grant authority to itself).

So I am stumped. I did not have this problem with my previous install of DB2 (9.5, and I don't remember which fix pack I was at).

How do I grant the necessary authorities to the local admin account?  I believe I need this in order to run a bind command that I need to do next:

db2 CONNECT TO MYDB;
db2 bind @db2cli.lst blocking all grant public sqlerror continue CLIPKG 20;
db2 CONNECT RESET;

Any help would be appreciated.

  Edit: I've found some of the following links related to this. Still not sure yet how to get it working, but at least I've found some
  documentation.

  http://www.db2teamblog.com/2009/06/whats-new-in-db2-97.html
  http://lpetr.org/blog/archives/simplify-the-db2-9-7-security-model
  http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.ref.doc%2Fdoc%2Fr0000958.html
  http://www.ibm.com/developerworks/forums/thread.jspa?messageID=14288459
  http://publib.boulder.ibm.com/infocenter/tivihelp/v4r1/index.jsp?topic=%2Fcom.ibm.tpc_V422.doc%2Ffqz0_t_preparing_to_install_db2_windows.html
  http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.sec.doc%2Fdoc%2Fc0055206.html
  http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.sec.doc%2Fdoc%2Fc0005479.html

Based on the last above links in my edit to my question I found my answer. I cannot tweak the system account since our logons to our system are from an LDAP and I cannot control the groups and what not.

So I did the following:

Opened the DB2 GUI (was easiest to do this way).
Connected to the desired database as db2admin.
Added my logon to the database as a user.
GRANT all authorities to that id (my id).
Disconnect.
Connect to the desired database as my id.
GRANT all authorities to db2admin.
Disconnect.

Voila! The db2admin logon now as all authorities.

  EDIT: I'm going to leave the above as it helped me learn how to do some interesting stuff in DB2. However, I have learned that the
  DBADM/SECADM with DATAACCESS and ACCESSCTRL authorities granted the
  instance owner (in my case the db2admin id) have all the authority
  needed to interact with the database. I could have actually just
  commented out those grant lines above in the script. Those were left
  over from a script which ran against an older version of DB2. I have
  also found if I need to have the instance owner DBADM after doing a
  restore to database A from database B, it is easiest just to set the
  registry variable DB2_RESTORE_GRANT_ADMIN_AUTHORITY to YES (available
  in Fix Pack 2 and above). Then I don't have to try to grant instance
  owner DBADM. It automatically is granted that to any database restored
  into the instance. If you are not at Fix Pack 5, you have to bounce
  the instance for this to take affect.


How to merge two columns with incomplete information? I have the following schema in my MySQL database, where I have read access:

table1 (customersid*:int, zip1:int)

table2 (customersfid*:int, zip2:int)

Where customersid references some customers and zip1 and zip2 corresponds to zip code area of that particular customers . Ideally the tables have similar information. However, in my tables zip code is sometimes incomplete:

customersfid is the foreign key of customersid:

Minimal Example:

       table1                      table2
customersid  zip1          customersfid      zip2
1            1001                 1          1001
2            700                  2          700
3            20                   3          20
4            60                   4          60
5            0                    5          30
6            NULL                 6          10
7            33                   7          NULL
8            25                   8          0
9            55                   9          66
10            0                   10         0

I would like to merge them into one column with the following idea (always in favor to zip1):

customersid  zip1                          customersfid  zip2
1            1001                           1           1001
2            700                            2           700
3            20                             3           20
4            60                             4           60
5            30           <-30              5           30
6            10           <-10              6           10
7            33                             7           NULL
8            25                             8           0
9            55         always keep zip1    9           66
10            0                             10          0

You can merge them in a SELECT, no problem. Something like:

SELECT CASE WHEN t1.zip1 in (NULL, 0) then t2.zip2 ELSE t1.zip1 END merged_zip
FROM table1 t1
  INNER JOIN table2 t2 on t1.customersid = t2.customersfid


Connection problem different errors .net application We have a SQL Server with some relational databases. We have another webserver with applications which communicate with the databases.
We have an issue with one of our customers. We get some errors every night, and we have a problem identifying what the problem is. There is a web/wcf service.
We have traced the database with SQL Server Profiler and identified a lot of Connections with the following:
Audit login
RPC:Completed
Audit logout

Same SPID more than ten times. Is that normal behavior?
Errors

Timeout expired.The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)

Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated.

Connection Timeout Expired.  The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement.  This could be because the pre-login handshake failed or the server was unable to respond back in time.  The duration spent while attempting to connect to this server was - [Pre-Login] initialization=21033; handshake=0;
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
The wait operation timed out

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
The specified network name is no longer available

Exception has been thrown by the target of an invocation.The wait operation timed out

An error occurred on the server, but the error message and default error messages is missing, or could not be retrieved.   System.UnauthorizedAccessException: UnauthorizedAccess

We need some advice how to do to identify the cause of the problem. We think it is several: network, queries performance etc.

I think you may be experiencing TCP Port Exhaustion on the Web Server. Avoiding TCP/IP Port Exhaustion is a good starting place for understanding the issue. This is generally an application design/implementation issue.

https://msdn.microsoft.com/en-us/library/aa560610%28v=bts.20%29.aspx?f=255&MSPPError=-2147217396


Database with only text fields I started working on an existing SQL Server Database System in which most of the fields are stored as text. Except some IDs all fields are varchar (phone numbers, zip codes, dates, addresses, monetary values etc.).

This is not how I learned to build a database. When asked collegues said that it is easier this way. Is it bad practice to keep all fields in varchar? How could I argue that it should be changed?

Size is one consideration.  An int can hold up to -2,147,483,648 in four bytes.  A char will need 11 bytes to hold the same value.

There are built-in functions to manipulate the various data types.  DATEADD() and DATEDIFF() are two examples.  This will not be possible with date-stored-as-text.  Constantly CASTing back and forth will not make for efficient processing, or legible code.

Automatic validation is another foregone benefit with the all-text approach. You may think that a column contains dates but there will be nothing to stop someone entering the value '2014-13-97'.

Sorting is unlikely to give the intended result with columns which are "really" numbers. For example, if a column contained integers '1' through '100', and the query sorted by this column, one would expect the result to be

1, 2 .. 10, 11 .. 20, 21 .. 100

However, the actual result is more likely to be

1, 10, 100, 11, 12, 2, 20, 21 ...

There may be similar concerns for dates, depending on the chosen character representation. Again type casting can cure this with the costs mentioned previously.

Occasionally you will find strings which contain digits only.  Examples are national identity numbers, bank account numbers, phone numbers and such like.  The common characteristic of such values is that it does not make sense to perform calculations on them.  Other things being equal it may be OK to store these as char() columns, especially if they have optional embedded alpha characters, but the above considerations still apply.


SQL Server Timeout Expired in ASP.NET C# WebForms application I have the following issue on my database which is used by a c# ASP.NET application:

  Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

I've tried sp_who and sp_who2 to check the cmd's visible there, which shows there is an average connectioncount of 10 users with AWAITING COMMAND and one CHECKPOINT.

The only way I can fix this database for several days is to take the application offline and take the database offline. Afterwards I can put the database back online and it will function without problems for a few days.

This is not a solid solution because the problem keeps coming back. But I'm out of thoughts what I can try.

What are the next steps for me to find the cause of this issue?

Sounds like a poor performing query that is crossing over the default 30 second timeout for .net connections.  SQL Server will process a query for as long as it takes, but the application is deciding to stop the show after 30 seconds.

You should fire up SQL Profiler and capture long running transactions (ones that have duration > 1 second) and transactions that require more than a few thousand reads.

Once you have a few of these queries isolated, you can begin to examine them for poor performance (look at indexes, joins, etc.)


Create constraint for stopping mass update to a table I want to create a constraint so that if more than 5 rows in a table are getting updated with an UPDATE statement it should warn the user / block the query.

One way to implement this would be with a trigger updating a temporary table in parallel (that is only visible inside the same session).

Create the temp table with ON COMMIT DROP, so that it lives for the duration of a transaction. This way, all updates during the same transaction count against the maximum.

Demo table:

CREATE TABLE foo(id serial, txt text)

Trigger function:

CREATE OR REPLACE FUNCTION trg_foo_maxup()
  RETURNS trigger AS
$func$
DECLARE
  _ct int;
BEGIN

IF EXISTS (
   SELECT 1 FROM pg_catalog.pg_class
   WHERE  relname = 'foo_maxup'
   AND    relnamespace = pg_my_temp_schema()
   AND    relkind = 'r'
   ) THEN

   UPDATE foo_maxup SET ct = ct + 1
   RETURNING ct INTO _ct;

   IF _ct > 5 THEN           -- your maximum here
      RAISE EXCEPTION 'Cannot update more than 5 rows in one transaction!';
   END IF;

ELSE
   CREATE TEMP TABLE foo_maxup ON COMMIT DROP AS
   SELECT 1 AS ct;
END IF;

RETURN NEW;

END
$func$
  LANGUAGE plpgsql VOLATILE SET search_path = pg_temp;

Trigger:

CREATE TRIGGER maxup
BEFORE UPDATE ON foo
FOR EACH ROW
EXECUTE PROCEDURE trg_foo_maxup();

If you want a maximum for the whole session, just omit ON COMMIT DROP.

I added SET search_path = pg_temp to the function definition to prevent mischief with the search_path. This trigger only sees temporary tables in its search_path. That's why pg_catalog.pg_class has to be table-qualified.

-> SQLfiddle demo.


Failover Cluster Instance work without quorum I have a environment with three nodes with installed SQL Server on them. All three nodes are parts of FCI and quorum setting is Node Majority.

In start, first node is role owner, and all three nodes is online. When I intentionally shut down first node, then second node takes a role. But when I check votes, I saw that third node is voting with 0, from some reason.

What I expect is that all environment is shutted down because quorum is not achieved (I have only one node online), but that is not happening. In fact, everything worked as on start.

What am I missing?

I agree with @Spörri but to add to his answer :
You have 3 nodes, and then you shut one down leaving you with 2. With 2 nodes(votes), there is no sure way the cluster can maintain a failure.If both nodes vote for themselves it will mess things up. So to avoid this, it sets the one node to 0 votes.
That way, only one node can vote. This does however mean that if the active node fails, you will have the entire cluster fail because the inactive node is not participating in voting at this point in time.
The easy resolution for this, is to add a file share or disk witness.
This will resolve a situation like this and is recommended for a fail over configuration.
With Dynamic quorum, having all 3 nodes active, the witness will be set to 0, as you already have 3 votes, a odd number. However when a node is shutdown/fails etc, the witness will gain a vote, allowing you to still have a odd number voting, meaning that you can loose 2 full nodes before your cluster is in trouble, whereas with your current configuration, once one node goes down your cluster is at risk of failing.
See this table for more info : Understanding cluster and pool quorum


Multiple Database in One Server Problems Our company has 5 different databases. None of the multiple databases relate or utilize cross database joins.
We have ample resourcing and budget.

Our company is debating between:

5 databases on 1 server instance,
or 5 databases on 5 different servers.

Question: What are some specific problems, obstacle issues which can occur, if we place everything on 1 single server? My background is not DBA, so would like to learn examples I can discuss with team.

Background:

The argument for 1 server instance at company: We have "Resource governor" and "AlwaysOn Availability Groups" with Failover clustering.
Resource governor will handle Memory, Cpu, io workloads between databases. So along with AlwaysOn DR, it can handle runaway queries, bad design, memory issues.

Databases are 100GB each, critical company information, 50 million rows minimum

We process 300 transactions/sec

RTO = 10 min

The answer is classic -"It depends !"

Sit down with your stake holders and present then the pros and cons of the 2 options. Being in the business of client hosting, I have faced this situation and below is my view point :

  None of the multiple databases relate or utilize cross database joins. We have ample resourcing and budget.

This means that the dbs are decoupled or independent. I would say, club the dbs depending on the criticality and uptime SLAs and then distribute them between 2-3 servers (VM would be another option as well). This way, you dont put all your eggs in the same basket.

Why ?

Putting all 5 dbs on one server will be a single point of failure. An issue with a single DB puts all your other DBs at risk. Isolate your risk as much as possible but that will cost you additional licensing and maintenance overhead which you have by saying - ample resourcing and budget.
Even if you have Resource Governor enabled, it does not govern buffer pool. It only governs "working memory of a query e.g. sort, hash etc". In 2014 and up, you can limit IOPS using Resource Governor.
You have one solution for a given server hosting all your dbs to do backups - full and log, integrity checks, index maintenance. If there is an issue with your server and that job fails midway, other dbs will be impacted except you have inbuilt mechanism to detect and fix the issue and continue with the rest.
An external issue with OS affects all your estate being in one place.
Even with AGs, you have a fix amount of worker threads and they get shared with all your dbs on a single server. Having dbs spread out depending on the criticality will alleviate that problem.
Tempdb is one per server. So imagine all your dbs being on the same server might be a bottleneck.
Problem isolation will be difficult if you have one server serving all your dbs.

I have written a sort of similar answer Which is better: one database per application, or just one database?


Efficient way to keep a count of entries within a timeframe? e.g. How many actions did user do in the last 7 days I track user events - say, clicks - and would like to show them how often they've clicked in the last week, last 30 days, etc. So of course, as time progresses, that number needs to be updated.
Here is my naive attempt at just computing the number in a query:
Table format:
table_name
timestamp | timestamp with time zone
user_id | integer

Each row in the table represents another instance of the user event being logged.
The query I have right now:
SELECT count(*) FROM table_name WHERE user_id = 1
  AND timestamp >= NOW() - '7 days'::interval;

This works fine, especially with a bit of caching thrown in, but as someone who doesn't understand much about database internals, I have to wonder if there isn't a more efficient way than having to do a SELECT with a date comparison filter every time. Neither column here is indexed. So I'm curious, is there a better way? Like some special data type or something. Are there any problems with running a query like this frequently as the number of entries increases dramatically? Or is this actually the most common way to do it?

That's a pretty common way of doing things, and PostgreSQL is designed to handle aggregative queries for large datasets very well. Though adding an index on the user_id and timestamp columns would improve performance, if you have that ability.


Where all can the /*+ PARALLEL */ hint be used? It works with SELECT and UPDATE statements.

Are there some more statements with which it works?

I was able to use it with create, but am not sure if the hint was actually considered.

SQL> create /*+ PARALLEL */ table t1
( id number,
name varchar2(40));

Table created.

SQL>

Except INSERT and UPDATE, when all is the PARALLEL hint considered?

The Data Warehousing Guide has a chapter on parallel operations that goes over what operations can be parallelized.

You can't parallel-ize a simple CREATE TABLE statement (nor would you want to-- there wouldn't be enough work to be able to parcel it out to multiple worker threads).  You can, however, parallel-ize a CREATE TABLE AS SELECT statement either by specifying the PARALLEL hint in the SELECT or by using the PARALLEL keyword in the CREATE.


Problem with MySQL Cluster in practice I am researching MySQL cluster. For the purpose, I do a demo with 4 virtual machines. I have some problems in practice.

Topology

My topology contains four nodes
Node 1: management node, ip address 192.168.56.205
Node 2: sql node, ip address 192.168.56.206
Node 3: data node 1, ip address 192.168.56.207
Node 4: data node 2, ip address 192.168.56.208

Other information

Linux OS: Centos 6.4, 32 bits
Linux kernel: 2.6.32-358.el6.i686
MySQL Cluster version: MySQL-Cluster-gpl-7.3.6-2.el6.i686.rpm-bundle.tar
RAM 128MB
Turned off firewall iptables
Disabled selinux

Installation

On all four nodes, I install MySQL-Cluster-server-gpl-7.3.6-2.el6.i686.rpm and MySQL-Cluster-shared-compat-gpl-7.3.6-2.el6.i686.rpm
Except Node 2 - sql node, I install more MySQL-Cluster-client-gpl-7.3.6-2.el6.i686.rpm

Configuartion

On node 1 - management node

Make directory /var/lib/mysql-cluster, assign owner:group is mysql:mysql
Make /var/lib/mysql-cluster/config.ini:

[ndbd default]
NoOfReplicas=2
DataMemory=80M
IndexMemory=18M

[tcp default]
portnumber=2202

[ndb_mgmd]
hostname=192.168.56.205
datadir=/var/lib/mysql-cluster

[ndbd]
hostname=192.168.56.207
datadir=/usr/local/mysql/data

[ndbd]
hostname=192.168.56.208
datadir=/usr/local/mysql/data

[mysqld]
hostname=192.168.56.206

On node 2 - sql node

[mysqld]
datadir = /var/lib/mysql
port = 3306
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
ndbcluster

[mysql_cluster]
ndb-connectstring=192.168.56.205

On node 3 and node 4 - data nodes

Make directory /usr/local/mysql/data, assign owner:group is mysql:mysql
Config /etc/my.cnf:

[mysqld]
ndbcluster

[mysql_cluster]
ndb-connectstring=192.168.56.205

Start up

On management node, I run:
    ndb_mgmd -f /var/lib/mysql-cluster/config.ini
On data node 1 và 2, I run:
    ndbd
Finally, on sql node, I run:
    mysqld_safe &

Result

ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=2 (not connected, accepting connect from 192.168.56.207)
id=3    @192.168.56.208  (mysql-5.6.19 ndb-7.3.6, starting, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @192.168.56.205  (mysql-5.6.19 ndb-7.3.6)

[mysqld(API)]   1 node(s)
id=4 (not connected, accepting connect from any host)

Error:

Node 2: Forced node shutdown completed. Occured during startphase 0. Initiated by signal 11.

There is ndb_2_error.log in /usr/local/mysql/data of node 2

The content of ndb_2_error.log:

Time: Monday 28 July 2014 - 09:45:01
Status: Temporary error, restart node
Message: WatchDog terminate, internal error or massive overload on the machine running this node (Internal error, programming error or missing error message, please report a bug)
Error: 6050
Error data: Job Handling
Error object: /export/home/pb2/build/sb_0-12598553-1404293345.9/rpm/BUILD/mysql-cluster-gpl-7.3.6/mysql-cluster-gpl-7.3.6/storage/ndb/src/kernel/vm/WatchDog.cpp
Program: ndbd
Pid: 1709
Version: mysql-5.6.19

Warning for three nodes: node 2, node 3 and node 4

2014-07-28 09:47:23 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 192.168.56.208. Returned error: 'No free node id found for mysqld(API).'
2014-07-28 09:47:23 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 192.168.56.207. Returned error: 'No free node id found for mysqld(API).'
2014-07-28 09:47:23 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 192.168.56.206. Returned error: 'No free node id found for mysqld(API).'

Thanks for your time.

Without looking in detail to your configuration, I would bet that your problem is the kernel doing an Out of memory (OOM) kill: killing some of your ndbd, etc. processes. Those are usually logged to /var/log/messages, so you can confirm it easily.

  128MB of ram, 32-bits

I am not 100% sure if you can run an NDB cluster with those spects, but I can tell you that, in practice, you do not want. Here it is a guide on how much memory consumption you can more or less expect from NDB. Remember that NDB preallocates most of its memory at first execution, ant that it is a memory-focused transactional engine. Increase its available ram and/or reduce some of its configuration parameters.


Can I create a table and database on a slave without breaking replication? I have One Mysql master server and Slave server running on ec2. The replication is working fine. Now I need to make some changes.  I need to add new slave server to the current replication loop.

Is it possible to add new tables and database which is not existing on master server on newly created slave.
Is this affect master server ? or broke the replication ?

We are not using this slave server for fail-over. Need to analyses live data and store to another database.

Server version: 5.5.48 MySQL Community Server

If you're not planning to switch master and you're sure the tables / databases are never going to exist in the master, you can have them without breaking replication. I have a setup like that running for some years now and working just fine.


How do I create/publish a report in SQL Server 2014? I installed SQL Server 2014 with Reporting Services but I don't how understand how to create and publish a report so that users can run it on the report server on IIS.

These are the programs that have been installed when I search 'SQL':

SQL Server 2014 Management Studio
SQL Server 2014 Reporting Services
Configuration Manager

Are these the only required programs to be able to create and publish reports? Which do I use to create a report?

Publishing a report has not changed much in SQL Server since 2005. Developing a report changed when SQL Server 2012 came out just with the tool that you would use. I would suggest you start out by going through the Stairway To SQL Server Reporting Services by Jessica M. Moss. After reading through that series I would suggest getting a book on SSRS, anything by Stacia Misner on the subject is a good start.


Webapp & MySQL: Row Level Security I am trying to emulate row level security on a webapp I am developing using MySQL.

Using this method:
Creating a database with the the required tables where the data pertaining to all the users will be stored with proper indexing of columns of the tables.

Creating mysql 'views' to specific users based on the user-id.

To achieve row level security I will also have to create mysql account for every user to and set 'grant' permissions on the views.

For the web interface a PHP based MVC framework will be used.

But, according to my research:

1] Having separate mysql account per user "make the webapp less secure".
2] Having separate mysql account per user "increases the disk I/O".

Questions:
1] How does creating mysql user per webapp user make the webapp less secure?
2] Does the disk I/O increase considerably?
3] Is there a better way to implement row-level-security in MySQL?
4] What are the pros/cons of implementing row-level-security by the above method?

Why am I looking at Row Level Security?
I need row level security because there are rows which will be shared between multiple users & have 1 or 2 owners to it. Only these owners can delete/modify them.

I am not able to add tags like 'row-level-security' 'disk-io' as my reputation points are less. If anyone else can, please do.

How does creating mysql user per webapp user make the webapp less secure?

All you have to do is run SHOW GRANTS; within the app's DB Connections and all grants for the connected user is exposed. That may include seeing host IPs, subnets. Grants for specific tables, views and columns also become visible.

Does the disk I/O increase considerably?

It would definitely do so. Although grants and views are stored in the information_schema database (in RAM), each view would be stringently checked to make sure the underlying tables exist for using the view definition. That would requiring accessing the .frm for every table involved. Any grants on tables or columns are also checked against the underlying .frm files.

Is there a better way to implement row-level-security in MySQL?

This may be a little crazy, but you could probably use triggers

Example
delimiter $$
CREATE TRIGGER check_row_priv BEFORE UPDATE ON t1
FOR EACH ROW
BEGIN
 IF (USER()='user1@localhost') && (OLD.is_editable=0)
 THEN
    -- Make Trigger Fail Here
 END IF;
END; $$
delimiter ;

I wrote another post that show how to abort stored procedures and triggers. Other answers in the question may give you insght into aborting triggers or, at least, makikng data immutable.
What are the pros/cons of implementing row-level-security by the above method?

IMHO In a nutshell, it would be the complexity of management.


Most efficient way to insert rows into a temp table in a stored procedure I am working in SQL Server 2008.  I have a stored procedure with 5 queries inside of it that select rows from very large fact tables (on the order of millions of rows) into another table (call it table_B).  (Actually, the 5 queries used to be lots of queries, but I compressed them down to 5 because each query was doing a LEFT JOIN from the very large fact tables against very small dimension tables, i.e., each very large table was being scanned lots of times.)  I need this other table (table_B) to persist, i.e., every time the stored procedure is executed, rows just keep getting added to it.  table_B can become very large itself.

Currently, for each of the 5 queries, I insert rows into it via the INSERT INTO table_B SELECT ... method.  A colleague recommended to me to de-couple my inserts and selects to gain some performance.  He recommended to first insert the rows into separate temp tables, export the temp tables as flat files, and then insert the flat files into my table_B via SSIS.  That way, we can get the performance of SSIS for inserts.

What is the best way for inserting rows into these temp tables?  (I can either pre-define them or create them on-the-fly.  So, I'm open to using either INSERT INTO or SELECT INTO methods.)  The number of rows returned in each SELECT query will be large, in general.  So, I need it to be as fast as possible.

The tables are very simple, i.e., no indexes, constraints, or primary keys.  (Those may get implemented in the future, but I am not designing for it.)

Also, is there a way to automate the end-to-end process?  I don't want to have users first execute the stored procedure, query the temp tables, export them, and then run SSIS to import them into the final table_B.

Since this seems to be a data warehouse, is / can the recovery model be bulk logged (or simple)? That in addition with using TABLOCK hint on table_b could help a lot since the inserts could be minimally logged. I found a blog post by Itzik Ben-Gan on minimally logged inserts that could help.


Automatically add new dbo objects in SQL Publication Is it possible to automatically add new dbo objects to SQL transactional replication? Is there some out of the box solution for that or that can be achieved by T-SQL?
Thanks

I was kinda intruiged by your question, since we use transactional replication a lot. So I tried to come with a script to answer your question (run inside the publication db):

-- your publication name
DECLARE @PubName SYSNAME = 'publication name'

-- your subscriber
DECLARE @subscriber SYSNAME = 'target server name'
DECLARE @subscriberDatabase SYSNAME = 'target db name'

 -- Get publication id
DECLARE @PubId INT
SELECT @PubId = pubid
FROM   syspublications
WHERE  name = @PubName

-- Find tables in dbo schema, not in current publication
DECLARE tablecursor CURSOR fast_forward FOR
  SELECT TOP(2) o.name  -- for testing I used a TOP clause
  FROM   sys.objects o
  WHERE  type = 'U'
         AND schema_id = SCHEMA_ID('dbo')
         AND object_id NOT IN (
            SELECT objid
            FROM   sysarticles
            WHERE  pubid = @PubId
         )

DECLARE @tablename AS sysname

OPEN tablecursor

FETCH next FROM tablecursor INTO @tablename

WHILE @@FETCH_STATUS = 0 BEGIN
    -- Adding the transactional article
    EXEC sp_addarticle
            @publication = @PubName
        ,@article = @tablename
        ,@source_owner = N'dbo'
        ,@source_object = @tablename
        ,@type = N'logbased'
        ,@schema_option = 0x08030F3--0x80030F3
        ,@vertical_partition = N'true'
    -- All columns
    EXEC sp_articlecolumn
        @publication = @PubName,
        @article = @tablename;

    FETCH next FROM tablecursor INTO @tablename
END

CLOSE tablecursor

DEALLOCATE tablecursor

EXEC sp_dropsubscription
  @publication = @PubName,
  @article = N'all',
  @subscriber = @subscriber;

exec sp_addsubscription
     @publication = @PubName
    ,@subscriber = @subscriber
    ,@destination_db = @subscriberDatabase
    ,@subscription_type = N'Push'
    ,@sync_type = N'automatic'
    ,@article = N'ALL', @update_mode = N'read only'
    ,@subscriber_type = 0

exec sp_addpushsubscription_agent
    @publication = @PubName
    ,@subscriber = @subscriber
    ,@subscriber_db = @subscriberDatabase
    ,@job_login = null
    ,@job_password = null
    ,@subscriber_security_mode = 0
    ,@subscriber_login = N'some login'
    ,@subscriber_password = 'some (matching:)) password'
    ,@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'

-- Re init subscription(s)
--EXEC sp_reinitsubscription
--   @publication = @PubName
--  ,@subscriber =  @subscriber
--  ,@invalidate_snapshot = 1

-- Start snapshot agent
EXEC sp_startpublication_snapshot @publication = @PubName

Now I found out the hard way, there are some things that can go wrong:

First of all, its obvious to restart the snapshot agent to pick up new articles. However, on my test machine the snapshot agent didn't pick up newly added articles. I did some googling and found simular issues (Snapshot not picking up new articles in SQL Server 2012 transaction replication when added through script). I ended up with dropping and re adding the subscription.
The @schema_option parameter for sp_addarticle is a bit of obscure to me. I used some tables that had timestamp columns and these were propagated to the subscriber as BINARY(8) and the rows could not be inserted at the subscriber ("Field Size too large"). See https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b56433c1-7f6f-4d2c-9593-3a11035fb1fe/cannot-update-a-timestamp-column-on-subscriber-using-transactional-replication?forum=sqlreplication and https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addarticle-transact-sql?view=sql-server-ver15


MongoDB Shard distribution issue I am facing an issue in the shard distribution.

MongoDB Version: 3.0.15

I have a collection that is sharded. I have two shards.

Output of db.getCollection('event').stats()

{
 "sharded" : true,
 "capped" : false,
 "ns" : "coll.event",
 "count" : 386631514,
  size" : NumberLong(4178195857267),
 "storageSize" : 293516279808.0,
 "totalIndexSize" : 391203631104.0,
 "shards" : {
    **"shard0000" :** {
    "ns" : "coll.event",
    "count" : 182463940,
    "size" : NumberLong(2187685928765),
    "avgObjSize" : 11989,
    "storageSize" : 148825059328.0,
    "capped" : false,
    },
**"shard0001"** : {
    "ns" : "coll.event",
    "count" : 204167574,
    "size" : NumberLong(1990509928502),
    "avgObjSize" : 9749,
    "storageSize" : 144691220480.0,
    "capped" : false,
    }
},
"ok" : 1.0
}

Output of Shard Distribution

db.getCollection('event').getShardDistribution()

Shard shard0000 at localhost:27010

Even though there are two shards, the shard distribution shows only one. Now only one of the sharded folder is getting full.

Why is it so?
What do I have to do to make the shard distribution common?

As per MongoDB documentation here which prints the data distribution statistics for a sharded collection. You must call the getShardDistribution() method on a sharded collection, as in the following example:

db.myShardedCollection.getShardDistribution()

In the following example, the collection has two shards. The output displays both the individual shard distribution information as well the total shard distribution:

Shard <shard-a> at <host-a>
 data : <size-a> docs : <count-a> chunks : <number of chunks-a>
 estimated data per chunk : <size-a>/<number of chunks-a>
 estimated docs per chunk : <count-a>/<number of chunks-a>

Shard <shard-b> at <host-b>
 data : <size-b> docs : <count-b> chunks : <number of chunks-b>
 estimated data per chunk : <size-b>/<number of chunks-b>
 estimated docs per chunk : <count-b>/<number of chunks-b>

Totals
 data : <stats.size> docs : <stats.count> chunks : <calc total chunks>
 Shard <shard-a> contains  <estDataPercent-a>% data, <estDocPercent-a>% docs in cluster, avg obj size on shard : stats.shards[ <shard-a> ].avgObjSize
 Shard <shard-b> contains  <estDataPercent-b>% data, <estDocPercent-b>% docs in cluster, avg obj size on shard : stats.shards[ <shard-b> ].avgObjSize

For your further ref here, here


Perf of query of large table by date? I have a table with some sensor data in it which grows every minute with a new measurement for that specific sensor. Something like:

entry_id (pk), sensor_id (fk), measurement_value, created_at

I need to perform some analytics on this data, at hour intervals. Basically the query will look something like:

SELECT measurement_value FROM above_table
WHERE sensor_id = x
AND created_at BETWEEN time_a AND (time_a + 1 hour)

1) First of all, I'm wondering if this design is reasonable long term. Each sensor will rack up 500k readings x year. If I have enough sensors, the table will get real sizeable real fast. I'd like analytics to be pretty snappy and not take longer than a second, so perhaps I'll need to be pre-computing these results every few insertions somehow. Alternatively I perhaps be looking for NoSQL solutions, or would that not really address the core issue?

2) What's the best performance I can get for querying that table? I'm thinking that an index on (sensor_id, created_at) would be hard to beat.

Thanks!

We do 500 to 3000 inserts per second on a table, but in partitions of 1 month, and are monitoring the last 2 hours of activity. An index on the timestamp does the trick, most queries run less than a few milliseconds on our hardware.

But, we do have fast storage, many GB's of RAM and descent processors. On a single 7200rpm disk, this would be impossible.

Use EXPLAIN ANALYZE to see how your queries are executed and try to find the bottleneck. Storage is the slowest part of your system, and databases love lots of RAM.


Migrating DB, SSIS packages, SSRS Reports from 2008 R2 to 2012 I have been given the task of planning our BI department's infrastructure upgrade from SQL 2008 R2 to SQL 2012. This process will include updating our data warehouse (database), reporting services/reports (native mode), and SSIS packages. This is my first ever attempt at planning/executing a SQL migration or upgrade. I have found some information regarding upgrading/migrating each of these individually, but not specifically in a context where all three are dependent on each other and all three will be upgraded.
Goals are:

Minimal down time.
Testability and fall back in the event of a failure or the unforeseen.
Ideally we’d like to be able to handle this migration in manageable chunks over time, with some parts going live before others.
When everything is successfully migrated/tested/live a final cutover occurs
and then the old environment goes away.

We have opted to migrate everything rather than doing any in place upgrades. A new server has been setup for us with SQL 2012 installed, incl. a fresh instance of Reporting Services in native mode, SSDT, etc.
My first question is: Given the goals above, what is the correct order to approach the migration of each for example Database > Reporting services reports > Integration services packages?
My second question is: Can I use Mirroring to facilitate this process, is it worth the extra complications? I saw it mentioned here (https://dba.stackexchange.com/a/58696/45140) as part of an answer, but the OP already had mirroring setup, we don’t. Also if this is a good idea, does anyone have a link to a good guide of setting up mirroring specifically to assist with a migration?

You have a lot of reading and testing to do.  There isn't a magic bullet per say but lots of ways of hitting this issue.  Firstly setup the 2nd environment as if it were prod, but restore the databases in a test environment and restore the SSIS packages.  If reporting services are not dependent on this then focus on this part first.  Give your SSIS package test data to feed and test the validity of the data.  You can even have it write to a test table using your business logic, and have the real process do the same thing so you can ensure you get accurate numbers and figures.

Mirroring is a fine solution assuming you're fully up to date, but there's 2 modes to mirroring and high performance is only available in Enterprise.  Thus your network has to be fast when you setup mirroring since all transactions must commit on the mirrored side first.

Are you following all the 2012 upgrade best practices, best practices analyzer, upgrade wizard for code issues, researched potential codes, have a well written test plan, and acceptance tests?

User Kin answered a very similar question, so please do research this site for info or duplicate questions, you'll often find a lot:

For SSRS depending on the complexity, you might want to just rebuild it.  If you wish to migrate, the SSRS Scripter is a great resource. Kevin Klines "how to" and "how to migrate reports" are also included in the link.


Return document that has $max value in specific field using mongodb I have a json file that has some football results. I need to calculate which team won the most away games, and how many wins there were.

My query is this:

db.football.aggregate(
{"$unwind": "$rounds"},
{"$unwind": "$rounds.matches"},

{"$project":
    {
        //"rounds.matches.team2.name":1,
        away_team: "$rounds.matches.team2.name",
        winner_away:
            { $cond: {if: {$gt:["$rounds.matches.score2","$rounds.matches.score1"] }, then:1,else :0} },
        _id:0
    }
},
{"$group": {
    _id: {team_name:"$away_team"},
    total_wins: {$sum:"$winner_away"}
    }
}
)

and what I get is this:

{ "_id" : { "team_name" : "Stoke City" }, "total_wins" : 6 }
{ "_id" : { "team_name" : "Newcastle United" }, "total_wins" : 2 }
{ "_id" : { "team_name" : "Chelsea" }, "total_wins" : 7 }
{ "_id" : { "team_name" : "Everton" }, "total_wins" : 5 }
{ "_id" : { "team_name" : "Bournemouth" }, "total_wins" : 6 }
{ "_id" : { "team_name" : "Manchester United" }, "total_wins" : 7 }
{ "_id" : { "team_name" : "Liverpool" }, "total_wins" : 8 }
{ "_id" : { "team_name" : "Manchester City" }, "total_wins" : 7 }
{ "_id" : { "team_name" : "Leicester City" }, "total_wins" : 11 }

If I add this to get just team's with more wins name, and number of wins

{"$group": {
        _id: "$team_name",
        max_wins: {$max: "$total_wins"}
        }
    }

I get this

{ "_id" : null, "max_wins" : 11 }

Can someone tell me how to get the name as well? I can do it using sort, limit but this will not work in case of two or more teams having equal number of wins.

what I did was this:
exported to a new collection temp and then, grouped by total_winds, sorted by ID and took first elements. It returns not only the very first, but all those being equal.

  db.temp.aggregate([
  {$group: {
      _id: "$total_wins",
      my_winner: {$push: "$$ROOT"}
  }},
  {$sort:{_id:-1}},
  {$limit:1}  ]).pretty()


How can I pass a list of databases to sp_MSforeachdb? I have the following script that when run gives me a comma delimited list of database names.

        SET NOCOUNT ON
        declare @db_list NVARCHAR(MAX)
        DECLARE @command varchar(1000)

        SELECT @COMMAND = 'SELECT DB_NAME()'

         SELECT @db_list  = STUFF((
                                    SELECT ', ' + name FROM sys.databases
                                    WHERE name like '__Order'
                                    FOR XML PATH(''),
                                    TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')

         -- in case there are no DBs to show, then show no DB '***'
         select @db_list = COALESCE(@db_list,'***')

         SELECT @db_list = RTRIM(LTRIM(REPLACE(@DB_LIST,',', ',''')))

         select @db_list

this gives me the following result:

UKOrder,' ATOrder,' AUOrder

1) this is giving the wrong result set, I would like it to be

'UKOrder','ATOrder','AUOrder'

2) how can I pass @db_list to sp_MSforeachdb?

suppose I would like to run

  select db_name()

inside each database in @db_list

UKOrder

ATOrder

AUOrder

You can't. Also you shouldn't use this undocumented, unsupported, buggy procedure in situations where it seems to work, either. I have written a better replacement here and here.

In fact, the procedure I wrote does support a parameter @database_list, in the form 'db1, db2, db3' (note no need to fuss with embedded single quotes at all), so adopting it for your needs - rather than reinventing the wheel - seems logical.

If you need help with syntax that builds a delimited string from a set for other purposes, please create a different question. If you really need to have each database name enclosed in its own quotes, the last string element in your REPLACE command should be ''',''' and you need to surround the end result with a single quote at beginning and end too.


What permissions do you need to take a mysql database backup via SSH? are 'SELECT' and 'LOCK' privileges enough for a 'mysql database user' to take a full database backup via SSH? I am able to take a backup using these privileges, but unsure if I am backing up all the files properly. So my question is, are these two privileges enough or does the user need more privileges in order to take a proper backup?

Depending on the options you use with the dump you may need more (or less). The one you definitely need is SELECT.

If you use triggers you need TRIGGER
If you use views you need SHOW VIEW
If you don't use --single-transaction in the dump you will need LOCK TABLES

For more info on the subject you can go to: http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html


Multiple foreign keys referencing a single table in one statement I have written the following statement:

CREATE TABLE ORDEN (
    `numero` INT UNSIGNED ZEROFILL NOT NULL,
    `fecha` DATE NOT NULL,
    `cliente` INT(6) UNSIGNED NOT NULL,
    `nombre_cliente` VARCHAR(64) NOT NULL,
    `direccion_cliente` VARCHAR(64) NOT NULL,
    `email_cliente` VARCHAR(64),

    /* Se utiliza ON DELETE NO ACTION porque probablemente (al igual que cualquier otro documento del mismo tipo) las órdenes se quieran conservar */
    FOREIGN KEY `fk_ORDEN_CLIENTE_ID`(`cliente`) REFERENCES CLIENTE(`id`) ON DELETE NO ACTION ON UPDATE CASCADE,
    FOREIGN KEY `fk_ORDEN_CLIENTE_NOMBRE`(`nombre_cliente`) REFERENCES CLIENTE(`nombre`) ON DELETE NO ACTION ON UPDATE CASCADE,
    FOREIGN KEY `fk_ORDEN_CLIENTE_DIRECCION`(`direccion_cliente`) REFERENCES CLIENTE(`direccion`) ON DELETE NO ACTION ON UPDATE CASCADE,
    FOREIGN KEY `fk_ORDEN_CLIENTE_EMAIL`(`email_cliente`) REFERENCES CLIENTE(`email`) ON DELETE NO ACTION ON UPDATE CASCADE,
    PRIMARY KEY(`numero`)
);

The problem is that I'm getting this error when trying to execute the statement:

  Error Code: 1215. Cannot add foreign key constraint

I've been thinking that maybe referencing many columns from a single table with foreign keys in one statement is not allowed.

Here is the definition of the CLIENTE table as well:

CREATE TABLE CLIENTE (
    `id` INT(6) UNSIGNED ZEROFILL NOT NULL,
    `email` VARCHAR(64), /* Dirección electrónica */
    `nombre` VARCHAR(64) NOT NULL,
    `direccion` VARCHAR(64) NOT NULL,

    PRIMARY KEY(`id`),
    UNIQUE(`direccion`)
);

How can I resolve my issue? I've been searching for a solution but had no success.

Foreign Keys can be added in the Child Table with the reference to Unique or Primary keys of Parent Table. The columns nombre and email are NOT defined as Primary or Unique key in Cliente Table. Hence you are not able to add FKs to these columns in the table ORDEN.


In Powershell Retrieving Printed Message From SSMS Our DBA team doesn't verify the backups using the below TSQL (which can easily be done post-backup and takes almost no time, so I don't get why not):

RESTORE VERIFYONLY
FROM DISK = 'D:\Backups\LOCATION'

They've had issues in the past, so even though we'd think they'd learn from it, they haven't.  I created a Powershell script to do this because we have about 100+ servers and I just want to run this script against all the backups just to ensure they're valid.  The below script runs correctly (in that it doesn't break or throw errors), I am curious if there's a way to get the printed message back in Powershell that we would normally get in SSMS where it states The backup set on file 1 is valid as verification.

$SqlCon = New-Object System.Data.SqlClient.SqlConnection
$SqlCon.ConnectionString = "SERVER=SERV\INST;Integrated Security=true;DATABASE=master"

$baks = Get-ChildItem "D:\Backups\" -Filter *.BAK

foreach ($bak in $baks)
{
    $SqlCon.Open()
    $cd = New-Object System.Data.SqlClient.SqlCommand
    $cd.Connection = $SqlCon

    $cd.CommandText = "RESTORE VERIFYONLY FROM DISK = @f"
    $cd.Parameters.Add("@f", $bak.FullName)
    $cd.ExecuteNonQuery()
    $SqlCon.Close()
}

Capturing InfoMessage Output (PRINT, RAISERROR) from SQL Server using PowerShell

The trick, as Jonathan points out, is that you need to have a listener attached to the connection manager listening for the print or error actions.

$cd.Parameters.Add("@f", $bak.FullName) | Out-Null
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) Write-Host $event.Message }
$SqlCon.add_InfoMessage($handler)
$SqlCon.FireInfoMessageEventOnUserErrors = $true

I also piped the output of adding a parameter to Out-Null as that was noise I didn't need.


HOW does ROWNUM work with this "optimization" I'm trying to optimize a query in which I have some requirements where I need to be able to arbitrarily select a row ROWX from a query.  Why is it that

SELECT XML_DATA FROM
    (Select ROWNUM AS MYROWNUM , EXTRACT(COL1,'expresion').getStringVal() AS XML_DATA
    From "Table1" Where Col2 = 'val' AND ROWNUM < ROWX+1)
WHERE MYROWNUM = ROWX ;

Runs much faster than

SELECT XML_DATA FROM (Select ROWNUM AS MYROWNUM , EXTRACT(COL1,'expresion').getStringVal() AS XML_DATA
    From "Table1" Where Col2 = 'val') AND MYROWNUM = ROWX

Obviously as ROWX grows there is some performance degradation with the first query but the second query has a constant run time (outside of the case where ROWX=1) of about 4 minutes where the base runtime for the first query is non-existent and growth is more acceptable than having to wait 4 minutes per iteration.

Version:
Oracle Database 11g Enterprise Edition  11.2.0.3.0  64bit Production

Sorry I'm a software developer (not a DBA!) that primarily works with MySQL/SQL Server so I'm not familiar with Oracle's internal workings feel free to point me in an entirely different direction,

Thanks.

Based on Justin Cave's answer I wrote the query to be deterministic and a bit cleaner (less hacky)

SELECT EXTRACT(COL1,'expresion').getStringVal() AS XML_DATA
FROM (Select ROWNUM AS MYROWNUM , CLUSTERED_ID From "Table1" Where Col2 = 'val' ORDER BY CLUSTERED_ID) t1
JOIN table1 ON (t1.CLUSTERED_ID = table1.CLUSTERED_ID)
WHERE MYROWNUM = ROWX

This has the benifit of running in constant time (where n = rowx rather than count()) and is much easier to understand.

The rownum pseudocolumn is assigned as rows are processed.  The predicate rownum = <<x>> will evaluate to false if <<x>> is anything other than 1.

Conceptually (and I emphasize that this isn't how Oracle actually works, it's just a useful abstraction), the second query does something like

Read a row from table1
Evaluate the col2 = 'val' expression
If col2 = 'val', evaluate the extract
Assign the rownum to the row that has been returned.  Since no other rows have satisfied all the criteria yet, the rownum will be 1
Evaluate the rownum = <<x>> predicate. If <<x>> is anything other than 1, the row is
rejected
Go back to step 1 and read the next row

In this loop, every row will be read from table1.  And every row will be rejected (assuming <<x>> is something other than 1) because of the rownum = <<x>> predicate.  So you'll incur the cost of reading every row from the table, evaluating the extract for every row, and you'll never return any data.

Your first query appears to be working because it is filtering out <<x>> + 1 rows in the subquery.  Of course, since there is no ORDER BY, your first query always returns an arbitrary row.  It would be entirely legal for Oracle to return the same row for every value of <<x>> in your first query.  It probably won't, of course, but it is entirely possible that you'd get different rows over time, that some rows would never be returned, that other rows would be returned multiple times, etc.  If you want a deterministic result (prior to 12.1 which has some simpler syntax options), you'd need to do two levels of nesting

SELECT *
  FROM (SELECT b.*,
               rownum rn
          FROM (SELECT a.*
                  FROM some_table a
                 ORDER BY some_column) b
         WHERE rownum <= <<upper_limit>>) c
 WHERE rn >= <<lower_limit>>

For a pretty thorough discussion of the various issues, take a look through this askTom thread.