Granting access to the Job Activity Monitor I'm trying to grant access to the Job Activity Monitor. On my model office server I can create a new login and add it to the SQLAgentReaderRole role in msdb, and the id has access to the monitor. However in my production environment the exact same process gives me the following error:

Both are 2008 R2, but production is SP2, and MO is SP3. I'm making the assumption this doesn't matter but I could be wrong.

I checked and the associated permissions (SQLAgentReaderRole, SQLAgentUserRole, PUBLIC in msdb and master) and they appear to be the same.

In order to give some privileged users access to xp_sqlagent_enum_jobs we did the following.

Create a Database Role in master named something like SQLAgentJobManager.
Granted that role EXECUTE rights on xp_sqlagent_enum_jobs.

Then via Logins or a Domain group, granted users a membership in the SQLAgentJobManager role in master.

EDIT: The way we implement this permission is through Domain Group login(s) such as (SQLAgentJobManagerGroup). Then add the group login to master's SQLAgentJobManager role (for running xp_sqlagent_enum_jobs) and to msdb's SQLAgentReaderRole or SQLAgentUserRole.

After that we always add or remove logins from the SQLAgentJobManagerGroup to control the group membership for those rights.


Mysql, linux, autocompletion I need autocompletion for mysql console in Linux.

I found this solution.

Add this to .my.cnf. file in home folder.

[mysql]
auto-rehash

But it doesn't work, for ubuntu 19.04 and mysql 14.14.

And just one question, is it possible to do it for small characters? I mean 'select' instead 'SELECT', etc.

Thank you.

auto-rehash is database, table and column completion. It isn't a generic syntax completion aid.


What is a good way to query data stored in multiple identical tables split by date/time I have a 3rd party piece of software which records data samples from industrial equipment.  This data logging software stores data in sample data tables with approximately one day's worth of samples for a particular piece of equipment. There are many pieces of equipment being monitored so the database sample tables end up looking like this:

Equipment1_SampleData_1
Equipment1_SampleData_2
Equipment1_SampleData_3
...
Equipment1_SampleData_31

So those 31 tables contain sample data for one piece of equipment over the most recent rolling 31 day period.

There is a set of those tables for each equipment type. This makes it difficult to query for a specific sample value, given a specific date/time.

I was thinking of creating a view which unions all the equipment sample tables into one big view so that I can write a function which returns a particular sample given an equipment name and date/timestamp. Is that a bad way to do this? Can you suggest other or better ways of doing this? I do not have any control over the way the 3rd party data logging/sample tool stores data.

The end result of this is that I want to be able to say: "Give me the sample value recorded for equipment N, nearest (>=) the specified date/time" and get a value back.

This is on SQL Server 2008.

Personally, I'd consider using triggers to load a proper single table and query that.
Then you have control of partitioning (if needed), defining good indexes etc

Depending on volumes, you could use service broker to decouple the app and your tables.

Basically, anything to avoid massive UNIONs and dynamic SQL...


Generate Dates between Date Ranges in mysql Is there a way to generate dates between date ranges. After looking on SO I found out there is a way to use CTE, another option is to use Union All from 0 to 9.
Is there an inbuilt function which I can use to generate dates between date range?

We are using MySQL 8.0.

I tried this solution :

WITH recursive Date_Ranges AS (
    select '2018-11-30' as Date
   union all
   select Date + interval 1 day
   from Date_Ranges
   where Date < '2018-12-31')
select * from Date_Ranges;


Enforcing existence of records while avoiding circular references I'm having issues designing a satisfactory solution for the following problem.

There are two main entities:

Customer
User

A customer can have many users associated to it. A user can be associated with many customers. Definitely a many-to-many relation for which a junction table should exist.

A customer must have at least one admin user. This is absolutely critical.

For several reasons it would be nice to enforce that the combination of customer & corresponding admin account exists in the junction table. For example: when listing the customer's users, it would be nice to not have to manually include the admin user.

In the following design it would be possible for the combination of customer & corresponding admin to not exist in the junction table:

(ignore the erroneously placed crow's foot at 'User')

Is there a good way to solve this problem?

The solutions I came up with so far involved introducing circular references, which I think are undesirable. Another option might be to go with this design and use a VIEW for the user listing. Feels like a workaround.

Is there a flaw in my design or is this a limitation of relational databases?

I think that your problem could be solved by imposing a new Foreign Key constraints in your tables:

User (UserId, UserOther) PK(UserId)
Customer (CustomerId, CustomerOther, UserAdminId) PK(CustomerId)
Customer_User (CustomerId, UserId), PK(CustomerId, UserId)

In Customer_User:
  FK(CustomerId) REFERENCES Customer
  FK(UserID) REFERENCES User

In Customer:
  FK(UserAdminId) REFERENCES User
  FK(CustomerId, UseAdminId) REFERENCES PK(Customer_User)

In this way you are guaranteed that a Customer has a valid User as admin, and that there is a tuple in Customer_User that corresponds to a the admin of a certain Customer.


Creacte Mysql Database copy I need copy full database in MySql but cant to do this.
I try
mysqldump db_name | mysql new_db_name

Its work, but DB so big and this comand need 5Gb+ in max_allowed_packed
Maybe you know best solution?
Task: Full copy Database with data
Thanks!

You can do it in two ways :

1 Backup & Restore :

Take backup using mysqldump & restore backup with new name.

2 Manual Create Table in New Database :

create schema new_schema;

select concat("create table new_schema.`",table_name,"` as select * from `",table_schema,"`.`",table_name,"`;")
from
information_schema.tables
where table_schema='old_schema';

/* Execute the above statement output simultaneously*/

-- i.e
create table new_schema.`XXX` as select * from `njindiainvest`.`XXX`;
create table new_schema.`YYY` as select * from `njindiainvest`.`YYY`;
create table new_schema.`ZZZ` as select * from `njindiainvest`.`ZZZ`;


Why is the First Transaction Log backup of the day huge I noticed that the first transaction log backup of the day, at 0700 is like 3GB. We run a full backup each night at 22:00. We are a 9-5 shop so there is no real activity overnight. We take TLog backups hourly during the day and they average around 30 MB. How can I find out why this first log backup is so big?
Thanks!

That depends on what is happening in the night.

If you have additional maintenance jobs (index rebuilds, index reorganizations, statistics updates) then you possibly have a lot of changes occurring.
If you are also importing large amounts of data, then that is another possible source of changes.
Deleting data is also a modification and will be logged.

A database that is truly dormant will normally not have huge transaction log backups.

You may also want to consider either taking a second full backup and a transaction log backup after the nightly maintenance, and use that backup as your base for the day's log backups solely to avoid the massive post-maintenance log file, or to just postpone the full backup and subsequent transaction log backups till after the maintenance.


Open Source tools to visualise MySQL/MariaDB performance metrics (show global status with interval) I'm searching for an Open Source tool to visualise MySQL/MariaDB performance metrics (output of show global status in nice graphs).

In MongoDB world there is the Closed Source Ops Manager and the Open Source mtools. I'm searching for something similar.

In official Oracle training for MySQL 5.6 there are diagrams like

While searching the internet I also found some RRDtool based graphs.

So my question is how to generate ad hoc nice visualisations from MariaDB performance metrics? Is there some Open Source project which helps in this task? How do other DBAs do this?

Get MonYog through MariaDB.  https://www.webyog.com/product/monyog

Note: anything that works with MySQL, MariaDB, or Percona, will probably work well with the others.

I looked at lots of tools, but came away thinking that most graphs were measuring either stuff that never changes.  My favorite tool metric is the number of slowlog entries.  If that goes up, there are problems.  At that point, sometimes the other graphs lead to useful info, but usually fixing a query is the real solution to a performance problem.


History Question: DB2 Nomenclature I realize this is a little random, but DB2 (circa V8) had a feature to define tables from the CREATE VIEW syntax.  Basically, the view was saved to disk to save the cost of recreating the dataset each time the view is called.

All I really need to know is what was this feature called?  I was thinking it was Federated Views, but that seems to be about incorporating external data into the database.  Would this work for saving aggregated data from within a database or is there another name for this feature?

Please help an old guy out.

You're thinking of Materialized Query Tables or MQTs.  They are still part of the product, see this documentation.


Generate a table that shows a monthly series and the number of tasks done per month I'm trying to generate a table that will show the number of tasks that were completed in each month.
The table has the following columns:

id
job_name
job_status
date_completed

My goal is to query the number of completed jobs per month between two values (e.g. 2021-02-01 [Feb 2021] to 2022-02-28 [Feb. 2021]), with the condition that it will only count those with status value '1' (1 = complete, 0 = ongoing) with the query producing this table:
Months   | Jobs Completed
---------+-----------------------
Feb 2020 | 0
Mar 2020 | 2
Apr 2020 | 0
...
Jan 2022 | 1
---------+------------------------

I managed to generate the series and have values to it, but the said table has the following problems:

The order of the months are not arranged in ascending order (From Feb 2021 - Jan 2022, the generated series was random)
The value of the months (the "jobs_completed") copies the highest value, rather than show their own. (e.g. if there are 3 jobs done in March 2021, and 0 on other months, all of the months will register their "jobs_completed" value as 3)

I have made a db-fiddle to show my problem: https://www.db-fiddle.com/f/gH7C7R6udMucjSATpw8Erh/0
SELECT to_char(generate_series('2021-02-01'::DATE, '2022-01-31'::DATE, '1 month'), 'Mon YYYY') AS month,
            COUNT(tbl.job_status) FILTER(WHERE job_status = 1) as jobs_completed
FROM monthly_tasks tbl
WHERE tbl.date_completed BETWEEN '2021-01-01' AND '2021-12-01'
GROUP BY month;

Thank you in advance!

I think I understand why you used generate_series() now. If you know your monthly_tasks table will always have all months in it, then you can write your query this way instead:
SELECT to_char(date_completed,'MON YYYY') AS month, SUM(CASE WHEN job_status = 1 THEN 1 ELSE 0) AS jobs_completed
FROM monthly_tasks tbl
WHERE tbl.date_completed BETWEEN '2021-01-01' AND '2021-12-01'
GROUP BY month;

If there's a chance your monthly_tasks table could be missing months, then you might want to create a dates table that has the all the months, and then left joining on (by month) to ensure you always have all the months. A dates table can be useful in a lot of future situations too.
Here's a pretty good boilerplate date's dimension table script you can use.


how can I update some table rows using data from existing rows? I have created a table where it stores some multilingual store data with this structure

create table store_locator_store_lang
(
    id_store int(11) unsigned not null,
    lang_id int not null,
    partner varchar(100) null,
    address varchar(255) null,
    city varchar(100) null,
    occupation varchar(100) null,
    primary key (id_store, id_lang)
);

id_lang can be 1 or 2

In there I have some incomplete data meaning that there are all the rows for lang_id = 1 but for lang_id = 2 many rows contain empty values.

I would like to populate the empty rows for lang_id = 2 with data from the rows with lang_id = 1.

The store entity is identified by id_store so the id_store is common for the two languages of the same store.

There is always a pair stored even if one row has empty columns. There is no chance there is a row missing for lang_id=2

Is it possible to accomplish this task with an update query in the database or do I have to export the data, edit and reinsert?

The Database server is  10.1.44-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

Here's a dump of some example data

INSERT INTO store_locator_store_lang (id_store, lang_id, partner, address, city, occupation) VALUES (543, 1, 'TEST', 'TEST, Τ.Κ. 52100', 'TEST', 'TEST');
INSERT INTO store_locator_store_lang (id_store, lang_id, partner, address, city, occupation) VALUES (543, 2, '', '', '', '');

In general (if "many rows contain empty values" means "they contains NULL"):

UPDATE store_locator_store_lang t1
  JOIN store_locator_store_lang t2 ON t1.id_store = t2.id_store
                                  AND t1.lang_id = 1
                                  AND t2.lang_id = 2
SET t2.partner = COALESCE(t2.partner, t1.partner),
    t2.address = COALESCE(t2.address, t1.address),
    t2.city = COALESCE(t2.city, t1.city),
    t2.occupation = COALESCE(t2.occupation, t1.occupation);

  The empty value seems to be zero-length string and not null.

UPDATE store_locator_store_lang t1
  JOIN store_locator_store_lang t2 ON t1.id_store = t2.id_store
                                  AND t1.lang_id = 1
                                  AND t2.lang_id = 2
SET t2.partner = CASE WHEN t2.partner = ''
                      THEN t1.partner
                      ELSE t2.partner END,
    t2.address = CASE WHEN t2.address = ''
                      THEN t1.address
                      ELSE t2.address END,
    t2.city = CASE WHEN t2.city = ''
                   THEN t1.city
                   ELSE t2.city END,
    t2.occupation = CASE WHEN t2.occupation = ''
                         THEN t1.occupation
                         ELSE t2.occupation END
/* WHERE '' IN (t2.partner, t2.address, t2.city, t2.occupation) */
    ;


Temporarily splitting a SQL Server cluster I currently have a pair of database servers up and running separately, but with the same structure and (hopefully) the same data on both of them. The applications that utilize the data from these servers have extra behavior set up in them such that every statement is run against both servers; this has (so far as we know) ensured that the data on the two servers continues to match up correctly, with some minor issues here and there when a configuration is changed incorrectly.

Currently, we have the two servers specifically for the case of hardware failure, and for disaster recovery; working out of Florida, we pack up half of our department's hardware whenever a hurricane starts to look like it's headed our way. Unfortunately, the half that leaves the state needs to continue functioning, and is not reconnected via VPN (we're trying to get that to happen).

So with that all in mind, is there any way with SQL Server (current release or otherwise) that we can set up a cluster that allows half of the nodes to just vanish and begin acting as a separate cluster? And more importantly, is there any known sane way for the two clusters to resolve any inconsistencies once they're both back on the same network? With certain tables exempted from this and requiring manual intervention, our current strategy is to truncate everything on the server that left, trusting the on-site server to hold the accurate data.

It sounds (from the extra comments) more like you want a way to keep two databases in sync after one is completely cut-off for a small or medium amount of time.

I'm not fully sure on how your DR scenario is going to work, and honestly at this point I'd suggest looking into the following:

Setup a cloud hosted server through something such as windows azure, Amazon AWS, etc. Then choose what fits best for you.

Move your infrastructure to the cloud - you won't have the issue of moving servers around, DR for hurricanes (I also live in FL so I get it), etc. Pick different regions (if applicable) to make sure you're covered. This is not as easy as it sounds but may pay-off for things such as this.

1.1. Have a dedicated DR site at co-lo facilities such as Rackspace, sungard, etc.
Availability Groups - This would require a lot more infrastructure than I believe you are willing to invest due to the nature of AD integration with AGs and Clustering. While Windows Server 2012R2 gives dynamic quorum and ad-detached failover clustering, I wouldn't advise using it in this situation.
Mirroring - Since AD, etc, would be an issue, it would be possible to use a local account on the hosted platform and setup the endpoints to use certificates. It also doesn't have to be part of your domain. You may have some latency issues that would need to be worked out or potentially not be acceptable. We don't know your SLAs.
Replication - There are so many ins and outs to replication, but a simple transactional setup could work for you, assuming your database meets the requirements. There are many gotchas just like the other technologies.
Log Shipping - A very simple DR method that may work for you close enough to when the server may possibly go down. This could be completed without being domain joined, but there would be a few extra hoops to jump through.

This is not, by far, an exhaustive list but more a guide to give you something to look at and think about. You'll need to meet with your business owners and decide what is and is not acceptable. Maybe they don't need seamless integration, maybe they just need best effort. We don't know, but this is a start.


Mongo DB sharded cluster fails when one shard is down I am using Mongo Sharded cluster with two shards.
There was an issue on my one shard and and it was down for around 30 minutes.
It stopped my writes to other shards also.
Logically if one shard is down other shard must be able to take part of writes but allow rites got failed in that duration.

Command failed with error 133: 'could not find host matching read     preference { mode: "primary", tags: [ {} ] } for set firstset' on server xxxx . The full response is { "code" : 133, "ok" : 0.0, "errmsg" : "could not find host matching read preference { mode: \"primary\", tags: [ {} ] } for set firstset" }

Could you please help me why this happened.

Mongo version : 3.2.9
Shard Key contentID : alphanumeric value

https://docs.mongodb.com/manual/sharding/#high-availability

Thanks
Virendra Agarwal

You have understood concept cluster and sharding wrong or you are mixing it with ideas of replica set. If cluster misses part of it (shard(s)), it is not whole and cannot function.

Replica set can function when minority of nodes are missing and that's why (sharded) cluster is normally made from replica sets. So, that every shard in the cluster is replica set, at least three nodes. So, in two shard cluster, you have actually six data nodes (2x3) and top of that you have three node config server replica set.


Access Database that is shared with my company on a network drive..When I copy and paste it to my desktop there linked some how? I have an Access Database that is shared with my company on a network drive..When I Click on the drive and copy and paste it to my desktop and make changes to it the database that is on the drive also gets the changes but I dont want to change that one yet.

When I change data in the database on my desktop it changes the database on the drive?
I have tried changing the name, I have checked the source file, and I also checked to see if the program had some kind of link but still can not find any solution?

When you copy the complete access database to another place and modify it there, then the data in your source database would not be changed.

The most likely explanation is, that not all data is stored in the database you copied, but instead some of the tables are links to another access database or even ODBC connections to a completely different database system.

You can usually quickly recognize those tables by having another symbol in your tables list.


TSQL: Find Queries causing too many SQL Compilations and SQL ReCompilations seperately I want to find out what is causing the high SQL Compilations (not re-compilations) I am seeing in performance monitor counters.
Here is my take on it:
If I am seeing a lot of SQl compilations, then it means that the queries on our system are not getting cached for following reasons:

Many adhoc queries

Running queries which SQl doesn’t cache e.g. :
UPDATE table1
SET col1= 'String longer than 8000 characters .....'
WHERE key_column = some int

Plans are timing out and being removed from the cache because: Cache is running out of space or plans are not being used long enough.

The only thing which goes near capturing cache inserts in profiler is Stored Procedures->SP:CacheInserts but it only looks after stored procedure cache.
So I tried the following to get adhoc queries:
SELECT [cp].[refcounts] -- when Refcounts becomes 0, plan is excluded from cache.
    , [cp].[usecounts]
    , [cp].[objtype]
    , st.[dbid]
    , st.[objectid]
    , st.[text]
    , [qp].[query_plan]
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text ( cp.plan_handle ) st
CROSS APPLY sys.dm_exec_query_plan ( cp.plan_handle ) qp ;

I thought queries that caused the compiles should be the ones with objtype= Adhoc but this could also relate to re-compilations. Now I have to run profiler, capture queries causing re-compilations and then exculde it from the above list.
Am I going in the right direction?
Is there a single query can I can use to achive just SQL compilations without too much work?
Resources which helped me in achiving the above knowledge:
http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/954b4fba-3774-42e3-86e7-e5172abe0c83
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=143946
http://technet.microsoft.com/en-nz/library/cc966425(en-us).aspx
http://www.sqlservercentral.com/Forums/Topic914951-360-1.aspx
Any help is really appreciated.

I don't think you can find this by an easy way but it is possible anyway to get through this. Profiler offers many event class types that can be used in analyzing the performance of a query. Start a new Profiler session and check following events:

Performance: Performance statistics
Stored Procedures: RPC:Completed
TSQL: SQL:BatchCompleted
TSQL: SQL: BatchStarting

Check to Show all columns and select each one of the columns under Performance: Performance statistics event only. The rest of events can be left with default setting.

Next, Select Column Filters and filter by DatabaseName and/or LoginName/ApplicationName/HostName etc.., if you know them. The purpose is to limit the number of rows dispalyed in Profiler and concentrate only on your needs.

Next, press Run and let it run for a while (2-3 min as long as you need).
Analyse the results dispalyed looking primarily at: Performance statistics event.

If Performance Statistics will occur often it means that the plan of a query was cached for the first time, compiled, re-compiled or evicted from PlanCache. From my knowledge if a query does not have its query plan in Plan Cache - you will see 2 rows of PerformanceStatistics event and followed by SQL:BatchStarting, then SQL:BatchCompleted. It means that the Query Plan was first compiled, cached and then the query started and completed.

Look at following columns under Performance Statistics event:

SPID - ID of the session on which the event occurred. You can use it to identify the
       row on SQL:BatchCompleted event which will display the SQL Query text and other
       usefull information (Read/Writes, StartTime/EndTime)
Duration - Total time, in microseconds, spent during compilation.
EventSubClass - 0 = New batch SQL text that is not currently present in the cache.
                1 = Queries within a stored procedure have been compiled.
                2 = Queries within an ad hoc SQL statement have been compiled.
                3 = A cached query has been destroyed and the historical performance
                    data associated with the plan is about to be destroyed.
                4 = A cached stored procedure has been removed from the cache and the
                    historical performance data associated with it is about to be
                    destroyed.

                5 = A cached trigger has been removed from the cache and the historical
                    performance data associated with it is about to be destroyed.

Considering the EventSubClass number you can find out what happened with the Query Plan and take specific measures. Additionally you can add other columns to Stored Procedures and TSQL Event Classes if you are interseted in HostName, WindowsUser or other info from Profiler trace.
Also the trace can be stored in a SQL table making the analyse more easy and much more customizable. Here is a link describing more the Performance Statistics Event Class.


Returning the max of a column Table:

SubSection | TradingId  | Year  | Amount
100A           5001       2004  -120
200B           5001       2005  -130
300C           5002       2006  -140
300C           5003       2007  -150

I want to return the MAX Trading Id based on the subsection, I want this:

SubSection | TradingId  | Year  | Amount
100A           5001       2004  -120
200B           5001       2005  -130
300C           5003       2007  -150

I tried selecting MAX with a GROUPBY Subsection, tried HAVING MAX etc but no luck

SELECT t1.*
FROM the_table t1
WHERE t1.trading_id = (SELECT max(t2.trading_id)
                    FROM the_table t2
                    WHERE t2.subsection = t1.subsection)


Take full and differential backup on secondary replica - SQL Server 2014 I do have SQL Server 2014 high availability setup in Prod env.

Server A – Primary, B-Secondary and C -DR.
Server A to B are synchronous commit and automatic failover.
Server A to C are asynchronous commit and manual failover.

I need to setup full-backup and diff backup. Can I do it on Server C?
I have given server C 98% priority in backup preferences – AG.
Running below query on server C but it throwing 1 which means I can't take backup on that server.
SELECT sys.fn_hadr_backup_is_preferred_replica (‘dbname’);
GO

I need to setup full-backup and diff backup. Can I do it on Server C?

You can do a copy only full, but unless C is the primary you cannot do a differential database backup.

  Running below query on server C but it throwing 1 which means I can't take backup on that server.

A return value of 1 means it is "preferred" but doesn't necessarily mean you can run any type of backup on it.


Group by range DATIME of 2h up or down - Mysql Well, I have a table with some columns that some of these are repeated, but the time is not exactly the same, I need to do a group by that has a range of 2 hours.

Notice that all the fields marked in black repeat, but the datetime field may differ by a few minutes, so I need to make a group by joining everyone within that two-hour interval.

It would be something like: SELECT * FROM table GROUP BY sinistro, prefixo, data AND horaIfull_oco range 2h up or down

He would join all those who had a maximum of two hours apart. I have already researched and found nothing, if anyone knows how to do.

Sorry Woton, but your answer has several issues.

First of all, your usage of GROUP BY is not right. With GROUP BY you collapse several rows of a group to one. To get a meaningful result, all columns of your SELECT clause must either be included in the GROUP BY clause, or an aggregate function like MIN() or AVG() or SUM() has to be applied on it. Otherwise a random row of this group is displayed. So a SELECT * FROM foo GROUP BY bar is as good as always wrong. To make sure, you're doing it right, you could execute SET GLOBAL sql_mode = CONCAT(@@global.sql_mode, ',ONLY_FULL_GROUP_BY'); (for further information you can read this part of the manual)

The second issue with your query is, that the HAVING clause is a filter on the result of GROUP BY. Filters on columns that you are grouping by would be in the WHERE clause. Filters in the HAVING clause include an aggregate function, like HAVING SUM(foo) > 5000.
When you don't use an aggregate function, again a random row is used. Your HAVING clause therefore actually filters nothing at all. x is always greater than x - 5 and x is always smaller than x + 5.

A filter that would work in your case (though I don't think this is what you want) would for example be HAVING TIMESTAMPDIFF(MINUTE, MIN(horaIfull_oco), MAX(horaIfull_oco)) <= 150. This isn't what you want, right?

If I understood you correctly, you want to build a group when consecutive rows are within a certain timerange, right? For this we would have to generate a column first, which we can later use to group on.

Let's do that.

Here's your query, explanation follows:

SELECT sinistro, prefixo, data
/*whatever else you need*/
, MIN(horaIfull_oco), MAX(horaIfull_oco), MIN(id_oco) AS id_oco, GROUP_CONCAT(inclusao_oco) AS incl
FROM (
    SELECT
    t.*
    , @group_number := IF(
        @prev_sinistro != sinistro
        OR @prev_prefixo != prefixo
        OR @prev_data != data
        OR TIMESTAMPDIFF(MINUTE, @prev_horaIfull_oco, horaIfull_oco) >= 150
        , @group_number + 1
        , @group_number) AS gn
    , @prev_sinistro := sinistro
    , @prev_prefixo := prefixo
    , @prev_data := data
    , @prev_horaIfull_oco := horaIfull_oco
    FROM your_table t
    , (SELECT @prev_sinistro := NULL, @prev_prefixo := NULL, @prev_data := NULL, @prev_horaIfull_oco := NULL, @group_number := 0) v
    ORDER BY sinistro, prefixo, data, horaIfull_oco
) sq
GROUP BY sinistro, prefixo, data, gn

First thing to notice, in a relational database there is no order of rows unless you specify it. So we ORDER BY the columns we want to use in our GROUP BY in the outer query and then by the timestamp/datetime column. The goal is, that we can use variables to access the previous row.
We don't want to execute the queries SET @prev_sinistro := NULL; SET @prev_prefixo := ... everytime before we execute the main query, so we initialize the variables in a subquery aliased with v here.
Then, every time sinistro or prefixo or data is different from the previous row, or when the difference between the previous rows horaIfull_oco and the value for this column in the current row is greater than 150 minutes, we increment the @group_number variable, if not we don't (we just assign the current value of the variable to the variable).
With @prev_prefixo := sinistro and so on we assign the value of the current row to the variable. It's important, that this is done after the IF() function. The SELECT clause is processed sequentially. When the next row is processed, the variables still hold the value of the previous row.

In the outer query finally, we can group by the generated gn column.

EDIT: To meet your further requirements, you could do this:

SELECT sinistro, prefixo, data, MIN(horaIfull_oco), MAX(horaIfull_oco)
FROM (
    SELECT
    sinistro, prefixo, data, horaIfull_oco
    , @group_number := IF(
        @prev_sinistro != sinistro
        OR @prev_prefixo != prefixo
        OR @prev_data != `data`
        OR TIMESTAMPDIFF(MINUTE, @prev_horaIfull_oco, horaIfull_oco) >= 150
        OR DATE(@prev_horaIfull_oco) != DATE(horaIfull_oco)
        , @group_number + 1
        , @group_number) AS gn
    , @prev_sinistro := sinistro
    , @prev_prefixo := prefixo
    , @prev_data := `data`
    , @prev_horaIfull_oco := horaIfull_oco
    FROM ocorrencia t
    , (SELECT @prev_sinistro := NULL, @prev_prefixo := NULL, @prev_data := NULL, @prev_horaIfull_oco := NULL, @group_number := 0) v
    ORDER BY sinistro, prefixo, data, horaIfull_oco
) sq
GROUP BY sinistro, prefixo, data, gn;

EDIT 2:

So, I excluded data and used this query:

SELECT sinistro, prefixo -- , data
/*whatever else you need*/
, MIN(horaIfull_oco), MAX(horaIfull_oco), GROUP_CONCAT(id_oco) AS id_oco
FROM (
    SELECT
    id_oco,
    sinistro, prefixo, /*data,*/ horaIfull_oco
    , @group_number := IF(
        @prev_sinistro != sinistro
        OR @prev_prefixo != prefixo
        -- OR @prev_data != data
        OR TIMESTAMPDIFF(MINUTE, @prev_horaIfull_oco, horaIfull_oco) >= 150
        OR DATE(@prev_horaIfull_oco) != DATE(horaIfull_oco)
        , @group_number + 1
        , @group_number) AS gn
    , @prev_sinistro := sinistro
    , @prev_prefixo := prefixo
    -- , @prev_data := data
    , @prev_horaIfull_oco := horaIfull_oco
    FROM ocorrencia t
    , (SELECT @prev_sinistro := NULL, @prev_prefixo := NULL, @prev_data := NULL, @prev_horaIfull_oco := NULL, @group_number := 0) v
    -- having id_oco in (254, 277)
    ORDER BY sinistro, prefixo, /*data,*/ horaIfull_oco
) sq
GROUP BY sinistro, prefixo, /*data,*/ gn

Regarding your problems with id_oco 254 and 277, when you include

OR DATE(@prev_horaIfull_oco) != DATE(horaIfull_oco)

in the IF() function, these 2 records are in separate groups, if you don't they are in the same group because they are within the 2.5 hours range.

If you still have problems, please provide the desired result based on your sample data as well, to avoid further confusion.


ps-ef|grep pmon (Instance Name Only) When i issue a "ps -ef|grep pmon", I get the below output.

oracle   230330      1  0 Jan08 ?        00:01:14 asm_pmon_+ASM1
oracle   244018      1  0 Jan08 ?        00:00:59 ora_pmon_vbsenopd
oracle   244020      1  0 Jan08 ?        00:01:01 ora_pmon_vbdrazpd
oracle   244021      1  0 Jan08 ?        00:01:00 ora_pmon_vbrmant
oracle   244042      1  0 Jan08 ?        00:01:25 ora_pmon_dx07123
oracle   244080      1  0 Jan08 ?        00:01:28 ora_pmon_rpasnap1

Now, Is there anyway that i can get only the active database names (for eg. rpasnap1 instead of ora_pmon_rpasnap1) using "ps-ef|grep pmon" or any other method?

Your help is my salvation.

$ ps -ef | grep pmon | grep -v grep
oracle   23955     1  0 Jan16 ?        00:00:33 asm_pmon_+ASM
oracle   27366     1  0 Jan16 ?        00:00:31 ora_pmon_S112

$ ps -ef | grep pmon | grep -v grep | awk '{ print $8 }' | cut -d '_' -f3
+ASM
S112

$ ps -e -o command | grep pmon | grep -v grep | cut -d '_' -f3
+ASM
S112


Migrating reports from SSRS 2005 to SSRS 2014 and SSAS 2005 cube to SSAS 2014? As SQL Server 2005 end of life is getting closer I'm facing inevitable job of migrating databases, SSIS packages, SSRS reports and SSAS cubes. Since newest version of SQL Server is SQL Server 2014 we are probably need to switch to it.
I have read few texts on  migrating from SQL Server 2008 to SQL Server 2014 but i have not found anything about migrating from SQL Server 2005.

Have somebody else done something similar and can it be done in first place?

There is no definitive answer to your question, since it will totally depend on the complexity of SSIS packages, SSRS reports and SSAS Cubes.

Doing in-place upgrade is always risky, since if something goes wrong, it would be difficult to rollback. A side-by-side migration is always a good and reliable method, since if something goes wrong, you already have a system up and running that you can point to.

Always, test, test and test your migration and have a fully tested rollback plan. Use upgrade advisor (this touches DB Engine, SSIS, SSRS and SSAS) and fully address all the issues raised by it.

I have written a thorough answer for database migration and post restore steps
For SSRS migration, I have written it here.
Devin Knight talks about Upgrading Packages to SSIS 2012 and msdn has a whitepaper on 5 Tips for a Smooth SSIS Upgrade to SQL Server 2012
For SSAS, this method should work in your case as well.


Which of these two MYSQL queries is better (and what is the default join type)? My SQL:

select count('1')
 from plays,
      promotion,
      partner
where
  partner.id = 1
  and promotion.partner_id = partner.id
  and plays.promotion_id = promotion.id

SQL generated by Eloquent (a laravel PHP ORM)

select count(*) as aggregate
from `plays`
inner join `promotion` on `promotion`.`id` = `plays`.`promotion_id`
where `promotion`.`partner_id` = 1

Is my version using inner out outer join?
is the inner join of the generated example better, or is it the same as my version?

There are 3 tables in this example:

plays
  int id
  int promotion_id

promotion
  int id
  int partner_id

partner
  int id

The sql is just to count all the plays which belong to a partner (through the promotion table)

The default join type is inner join.

The second query may be better as it avoids an extra join to the partner table.  The required partner id value is available from the promotion table.  The impact will likely be minimal in this case.

I would recommend prefixing the primary key id columns with the table name (singular form); play_id, promotion_id and partner_id.  Most design tools allow this, but may default to the form you have.

Learn to use explain plans to understand how the query will be executed.  This is not foolproof as the execution plan may be influenced by table sizes and other factors.  Different databases may generate different execution plans.


Report Builder Indicator showing incorrect symbol I have a report on which I am using about a dozen indicators as a check-boxes.  The data source for the report is a SharePoint list.  About half of the check-boxes are displaying correctly. The other half, instead, are showing a capital A.  The query I am using is:

=IIF(Fields!Quality_Record.Value="yes",1,0)

The indicator state is set up so that a value of 0 shows no icon and a value of 1 shows the check symbol. I have even deleted this indicator and replaced it with a copy of one of the working indicators and it still will only show a capital A regardless of the value of the field.

Well, as it turns out, someone changed the type of the field in SharePoint without documenting it and instead of yes/no, it was returning true/false and causing an error to show in place of an indicator.


How do you SUM a column without having its name? I'm stuck in a way that how to do sum on a column let's say first column of a table (result from another query) without knowing the column name with something like column position id.
it's something like this
select sum(what?), employID from
    ( select count(*), employID from  table1...
           union all
      select count(*), employID from  table2...
           union all
      select count(*), employID from  table3...
)

Or if it's in single query (single simple select query with using sum() ) like:
select employName, sum(what?), employID from tableX

How do I tell SUM() function to sum based on column position index in table like SUM(2)?
Note: I don't want to use column alias, any possibility of doing SUM not based on column name?
I know I can use column-name or aliases, but I really want to know the possibility of not using only these which is why I'm asking this question, if no possible ways, I will accept "No" as correct answer then.

Many (perhaps, most) other database products will not allow you to define a derived table with a calculated column that has no explicitly assigned alias. In other words, they will not let you have this

SELECT
  ...
FROM
  (
    SELECT
      expression
    FROM
      ...
  )

Instead, you would have to do something like this:

SELECT
  ...
FROM
  (
    SELECT
      expression AS SomeAlias
    FROM
      ...
  )

The reason is, they do not assign a name to an expression automatically. In contrast, Oracle does do that, and that is why Oracle is fine when your derived table has a calculated column without an explicit alias. That column will have a default name. The only problem is, what that name will be.

Basically, the name is the expression itself but Oracle applies certain transformations to the original expression before using it as a name. In particular, all unquoted identifiers (including names of functions) are uppercased and all spaces are removed. Thus, if the expression is count(*), the default name of the column becomes COUNT(*). If the expression is a + 1, the default name will be A+1. Note that the presence of special symbols in both those example ((, *, ), +) prescribes that you enclose the name in quotation marks to be able to use it as a reference. In other words, if you have a derived table like this:

  (
    SELECT
      COUNT(*)
    FROM
      ...
  )

then you must write

SELECT
  "COUNT(*)"
FROM
  (
    SELECT
      COUNT(*)
    FROM
      ...
  )

rather than

SELECT
  COUNT(*)
FROM
  (
    SELECT
      COUNT(*)
    FROM
      ...
  )

if you want to reference the derived table's column; otherwise Oracle will be interpreting the reference not as a reference but as an actual expression (another call of the COUNT function, this time applied to the output of the derived table).

So, in your specific example, you can reference the derived table's first column as "COUNT(*)":

select sum("COUNT(*)"), employID from
    ( select count(*), employID from  table1...
           union all
      select count(*), employID from  table2...
           union all
      select count(*), employID from  table3...
)

That said, why avoid assigning an alias explicitly? Often it is not about how you want to do things but rather about how things are meant to be done. Besides, if using AS name immediately after the expression is for some reason unacceptable, you have other options of assigning a column alias, as suggested in the answer by ypercubeᵀᴹ.


SQL Server TDE with PKI - converting P12/PKX to PVK/DER format To use TDE for SQL Server we are obliged to use a PKI certificate. The certificate has the format .p12(.pkx). Microsoft offeres a certficate converter for SQL Server. Using it - it creates three certificates and one pvk file.

C:\Program Files\Microsoft\PVKConverter>PVKConverter.exe -i D:\path\nameofpkicert.pkx -o mycert -d Password -e Password2
Processed PFX certificate D:\path to generat
e 3 PVK/DER certificate pairs.
Generated PVK/DER certificate pairs:
(mycert_1.cer)
(mycert_2.cer)
(mycert_3.cer, mycert_3.pvk)

How do I import this files to a SQL Server?

It seems to work with mycert_3.cer:

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password';
GO

USE master
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Password';
GO

CREATE CERTIFICATE TestCert
FROM FILE = 'D:\path\mycert_3.cer'
WITH PRIVATE KEY (FILE = 'D:\path\mycert_3.pvk',
DECRYPTION BY PASSWORD = 'Password2');
GO

USE TDE_Test
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TestCert;
GO

ALTER DATABASE TDE_Test
SET ENCRYPTION ON;
GO


Possible to use a field within a query in PostgreSQL? I want to query all events that are going on right now. Each events record has at and minutes fields for the time range of the event. I might query the events table like this:

SELECT * FROM events WHERE at >= NOW() - INTERVAL '2 hours' AND at <= NOW()

This assumes the event is 2 hours long. But I don't know that. The actual length is stored in the minutes field. How can I use that field in my query? Or is there a better mechanism for me? I've unsuccessfully tried things like this:

SELECT * FROM events WHERE at >= NOW() - INTERVAL minutes ' minutes' AND at <= NOW()

One little caveat is that performance does matter, somewhat. There is a large number of events in our database and that is growing fast.

You can use:

WHERE at >= NOW() - minute * INTERVAL '1 minute' AND at <= NOW()

The Postgres documentation page has similar examples: Date/Time Functions and Operators

The above is not going to be very efficient but you could add an index on at + minute * INTERVAL '1 minute' and then modify the query condition:

WHERE at <= NOW() AND (at + minute * INTERVAL '1 minute') >= NOW()

But it would probably be better if you used a gist index on the range (from when the event starts until it finishes), with something like this:

CREATE INDEX event_start_end_idx ON events
  USING gist (tsrange(at, at + minute * INTERVAL '1 minute'));

and then use the "contains" (@>) range operator:

WHERE tsrange(at, at + minute * INTERVAL '1 minute'))  @>  NOW()


SQL Server 2016 issue regarding migration of in-memory table and foreign keys Trying to use table memory optimization advisor on SQL Server 2016, seems I need to drop and re-create the foreign keys of some disk based tables, the size of the table is 300 GB and I'm wondering if there is any risk to drop and re-create the foreign keys before table memory migration?

Risk is in between you drop and recreate the Foreign key constraint any process (application, user etc etc) will be allowed to  insert data in any one of these tables which can break the referential integrity. If that happens you will not be able to recreate the Foreign key constraint (with checking integrity on existing data, which is a good thing) and you will have to clean up the data.

There are ways to tell SQL Server to ignore existing data and enforce the constraint only on new data. Which is BAD or GOOD depends on your business need.

Details here:

Delete Foreign Key Relationships
Create Foreign Key Relationships


Can I pass a single record from a recordset to a module? I am creating a module, RedemptionEstimate, in MS Access 2016 that requires 13 parameters to be passed to it and calculates how much is owed on delinquent property taxes. Some of the parameters can be null and will be handled appropriately within the module. I tried the clunky route of passing all the parameters from a function call within a query, but the null values are resorting in #Error in the returned query field.

I then decided to read up on Recordsets and this seems like a better solution as the module will typically be called from reports and forms based on a query. My understanding is that a recordset can be defined by a query and created within an event call on a report or form. My plan is to create the recordset within an OnLoad Event then parse through the recordset with a Do..Until with a module call within the loop.

Is it possible to pass just one record from the recordset to the module? If not, I'm open to other suggestions.

Other possible solutions I've considered:

Add an additional field to the original table and recalculate the redemption value. - I don't like this solution because the value changes every few months and it's how the old system does it and it's annoying to have the system recalculate 10,000s of records on a regular basis. It also seems unnecessary.
Is is possible to add an empty field to the recordset and just pass the entire recordset to the module then add the calculated value to that field? - Intuitively this feels like I'd have to run through the recordset twice in the OnLoad Event, once to calculate the values and a second time to "print" the data to the report or form.

Direct answer regarding Recordsets:

For Recordset objects used by Access (DOA -the default used by Access, or ADO) the short answer is "No".  Regrettably there is no concept of a "Row" object in these older data models, so you cannot pass all values of a particular row independently of the Recordset.

You could pass an entire Recordset object once it is positioned at a particular record.  A function could retrieve the necessary values (via the Fields collection) then leave the Recordset unaltered so that the calling function could continue looping through all the rows.

UPDATED Recommendation after comments

Honestly, I would try eliminating the stored calculated values if the system proves efficient enough to do all calculations dynamically.  The only way to determine this is to try it.  If it proves much too slow (which can honestly become much more annoying than the current system), then I would revert back to a stored, calculated value with a recalculate button and all.

Since the calculated value is not dependent upon other database or external queries, it should be efficient enough to use a parameterized function as you have already written.  The benefit to such a function is its flexibility: It can be called from another function and method, from within a query, or from a form control element.

(FYI: Consideration of additional database and/or external queries is important, because if it were dependent upon other query services, these can often require proper connection management, and/or take a long time, etc.  This often complicates the solution and can justify the alternatives.)

You could also create another function that takes just the primary key (e.g. ID) for a given record. It then queries the database tables directly just for the given ID value, then calls the original function using values retrieved from the recordset.  Such a function can be useful when the full record is not immediately available or convenient.  But be careful since using this in a query of thousand or records can freeze Access since it is repeatedly opening and closing a recordset for each function call, often much slower than passing parameters.

'* Assume the original function is RedemptionEstimate(p1, p2, ...)
Public Function RedemptionEstimateFromID(vID as Variant)
  If Not IsNumeric(vID) Then
    '* Avoid problems with null (and SQL injection concerns)
    '* Consider other error handling code
    RedemptionEstimateFromID = Null
    Exit Sub
  End If

  Dim db As Database
  Dim rs As Recordset2
  On Error Goto catch

  Set db = CurrentDb '* Not critical for a single recordset, but good habit if performing multiple queries
  Set rs = db.OpenRecordset("SELECT * FROM DataTable WHERE ID=" & vID)
  If rs.EOF Then
    '* Assuming ID is unique primary key, rs has only one record
    '* Call original function, passing values from query as parameters
    RedemptionEstimateFromID = RedemptionEstimateFrom(rs!Col1, rs!Col2, ...)
  Else
    '* ID not found... Consider other error handling code
    RedemptionEstimateFromID = Null
  End If
  rs.Close '* Would be closed automatically, but good habit to close explicitly

  Exit Function
catch:
  '* ID not found... Consider other error handling code
  Debug.Print "Error " & err.number & ": " & err.Description
  RedemptionEstimateFromID = Null
End Function

When displaying on a form with thousands of records, it can be a serious waste to calculate dynamic values for all records in the query.  Instead, the value can be calculated just for the displayed record(s).  One drawback to this can be that such a field can't be used to filter the query.  To calculate the value for the currently-displayed record, add a new control to the form and its Control Source property, add a call to your function passing from values as parameters:  = RedemptionEstimate([Col1], [Col2], ...).

Regarding alternatives:

Indeed, this approach is discouraged for volatile values.  However, it can also be a valid technique if it is known that values seldom change.  In that case, a static table field can be much more efficient and easier to work with than repeatedly recalculating values dynamically.  This technique requires a well-established and reliable update process and schedule.  It might seem annoying now, but if you could automate such a process that runs outside of normal times it could still be a reasonable approach.  Consider answering these questions rather than basing it off the "annoyance" factor :)

Do these values change for all records on the same schedule?
Does recalculating values dynamically slow down queries and related user interfaces?
Could an update process be automated to run outside times that effect other workflows?
Do you often experience outdated values for particular records with the current process?
Do such outdated values have seriously negative effects?

To answer this question fully and properly requires that you first specify the requirements and behavior of the form.  Do you need to update the source-table values or only view them?  Will the form load only a few records at a time or all 10,000 that you mentioned?  How long does it take to calculate the "RedemptionEstimates"--for a single row, for the entire set of records?

Creating a temporary table is a viable alternative, but if you plan on updating values back to the original table, you must ensure proper indexes and relationships are defined, etc. Worst case you need to then write extra code to update the tables.  But I'd wait to recommend details until you answer the other questions.

Other Comments:

Passing values into a function from a query is a legitimate technique.  Perhaps 13 parameters is a lot and it is good that you're exploring possibilities.  However, you should be able to resolve the #Error values with proper null handling and debugging. Don't give up on this technique since it might prove to be the best solution.

Sometimes it is necessary to explicitly loop through each row of a recordset, but don't get caught up in loops.  The proper technique is to bind a record source to a form or report.  There is no need to loop through and explicitly "print" each record on a form.  Access is built to do such tasks for you.  If you specify a Row Source for the form (or report), the form will automatically open the query and create its own Recordset behind the scenes.

(I discourage explicitly creating a Recordset then binding it to the form yourself. Access technically allows this, but it is buggy.  Trying to create and update an empty field in an existing recordset also complicates things and I think requires ADO Recordsets instead of the default DAO.  In other words, I suggest not going down this path.)


Can you use an inserted variable in a default constraint Working in SQL Server, let's say I have a database with two tables: events and competitors.

events has the following columns:

[event_id] [event_name] [winner_id] [loser_id]

competitors has the following columns:

[comp_id] [comp_name]

I wanted to add a default constraint to [event_name] to save me time when inserting a new event. Something similar to:

ALTER TABLE [events]
ADD CONSTRAINT DF_event_name
DEFAULT CAST([winner_id] AS varchar(10)) + ' vs ' + CAST([loser_id] AS varchar(10)) FOR [event_name]

Of course, this is a simplified version, it would also need to implement a join statement with competitors ON comp_id = winner_id and comp_id = loser_id.

When I tried setting the above default constraint, I got the response you are probably expecting:

  The name "winner_id" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

So is there any way to work around what I'm trying to do?
Is there any way to automatically set the value of a cell to a string implementing data inserted from the same row? If that's possible, can it also be done using a join statement? My next idea would be a trigger.

No, default constraints cannot depend on other columns.  Further, neither default constraints nor computed columns can reference other tables (unless you define a scalar udf - not recommended!).  As mentioned, you can achieve this constraint using triggers.

I'd recommend you create and use a view over these two tables.  Select from the tables joined together and compute the data you want.


Best way to speed up requests using date columns Our database is receiving the following requests:

select * from [schema].[table] (nolock) where (Date_A > Date_B and Archive = 0)

This is a normal request that is made by our application. On average, the request is taking between 200ms - 400ms to complete, with about 100ms - 150ms in CPU. Reads normally come in at about 48k. This is at least one example of how this script performs.

What would be the best way (if there is a way) to speed up this type of request?

The table has 240,932 rows and the queries return 0 records.

If there is only a small subset of rows with Archive = 0, you could use a filtered index:

CREATE NONCLUSTERED INDEX IX_Archive_Filtered
    ON [schema].[table] ([Date_A], [Date_B])
    INCLUDE ([Archive])
    WHERE ([Archive] = 0)

Note: I've made the index on the two date fields only because I don't know of any other fields in your table - it might make more sense for this to be on a different field

Additional Note: always take care to include the filtering columns in the filtered index keys or included columns, so here for one example of why: Incorrect row estimation given with a filtered index

This keeps the index small (which means it uses less storage, and is quicker to read from), because only the rows where Archive is 0 are included in it.

If a filtered index won't work for you (and there are a number of scenarios where they don't work, see Filtered Indexes and Dynamic SQL for one example), you could try a normal nonclustered index on the archive field OR the date fields.  You would want to create the index on whichever field is most selective - meaning which one will narrow down the results the most.

For instance, again if only a small subset of your rows have an Archive value of 0, you could create this index:

CREATE NONCLUSTERED INDEX IX_Archive_Includes
    ON [schema].[table] ([Archive])
    INCLUDE ([Date_A], [Date_B])

This will let your query seek to the correct rows (where Archive is 0), and then filter easily on Date_A and Date_B.

Additional comments about your query:

you really shouldn't use select * - enumerate each of the fields you actually need in your results to be more efficient, and allow more effective indexing
you shouldn't use nolock if the accuracy of the results is important

see this post for a quick demo of how results can be wrong with nolock: Using NOLOCK? Here’s How You’ll Get the Wrong Query Results
see this post for a lot more detail about using nolock (which is the same as the read uncommitted isolation level): The Read Uncommitted Isolation Level


Oracle 10XE: Stop schemas seeing the all_users table? I've got an Oracle 10g XE Database that I want clients to be able to view remotely via SQL*PLUS or SQL Developer. Each schema has a name which includes the name of the client, so anyone looking at the all_users table would be able to see the names of all my other clients! Is there any way to restrict permissions on certain tables? And are there any other tables like this which would be a good idea to restrict access to? I'm not particularly concerned about our clients trying to hack things, I just don't want them to see a list of all our other clients!

Thanks!

You have a few options, some of which will make your database unsupportable by Oracle (if you care!) as you need to fiddle with SYS owned objects.

The first option is to revoke select privs from everyone:

revoke select on all_users from public;

Then grant it back to certain users, plus all other internal Oracle users.

The 2nd option is to look at the source for the view, drop it, then recreate an altered version that supports your needs.

Another option that would trick casual users would be to create a table called ALL_USERS in each schema. Unless the logged in person explicitly qualified ALL_USERS (ie: SYS.ALL_USERS) they would get the resultset from the table in their own schema. This trick could also be done with a synonym pointing to a view that does:

select * from all_users where username = (SELECT sys_context('USERENV', 'SESSION_USER') from DUAL);

Not much else you can do with XE I'm afraid.


Moving data into an archive table and reducing the size of the original table On a SQL Server I inherited, there is a 55GB database on a 70GB drive. There are many small tables, and one big table that is 50GB in size (approx 36GB data, 15GB index). The log file is on a different drive. After shrinkfile the log file's drive has about 12GB free. Database is in simple recovery mode.

The table is badly designed, for example there are many columns defined as char(10) that have nothing bigger than 3 characters in them.

There isn't enough free space on the drive to alter these columns and then rebuild the clustered index (when altering a column the database creates a new column then copies the data from the old).

I'm wondering if I can instead do the following:

Create an archive table with a much better space saving design (appropriately sized columns for the data they contain)
Incrementally move old data from the huge table to the archive table.

Will the source table reduce in size when data is deleted from it (after being moved to the archive table)? Or would I have to do a clustered index rebuild for that to happen?  (In which case I cannot do it because there isn't enough space.)

It now occurs to me that column alteration uses space in the log file, but whenever I do a column alteration on a smaller table, the main data file grows as well.

If allocating space or another drive is impossible, then there might be ways you can do this with minimal growth, assuming you can put the database into RESTRICTED_USER mode or otherwise prevent users/applications from trying to modify the data while you fix it.

First off, don't shrink your data or log files at this point. You'll free up space on the disk temporarily, but the files will just grow again - so as with most shrink operations this is pointless.

Next, take one of the char(10) columns. Ensure that you know the largest string contained:

SELECT MAX(DATALENGTH(CONVERT(varchar(10), char10_column)))
FROM dbo.table;

Add a new, nullable, varchar(that length) column. (I haven't supported 2005 in some time, so I forget if this is fully or partially online - in theory, this shouldn't be a size-of-data operation, but I don't know if 2005 was smart enough to not treat it as one.)

ALTER TABLE dbo.table ADD newvarchar10_column varchar(10);

In small chunks, as I describe here for deletes, update that new column to have the value of the old column, and set the old column to NULL. This will add to the table size gradually, but will have minimal impact on the log.

UPDATE TOP (?top?) dbo.table -- not sure what your ?top? sweet spot will be
  SET newvarchar10_column = oldchar10_column,
      oldchar10_column = NULL
  WHERE oldchar10_column IS NOT NULL;

-- CHECKPOINT / BACKUP LOG

Once the data is all located in the new column, dropping the old column should be minimal work for the database, since there isn't any actual data to log. If this column is involved in any indexes or constraints, or schema-bound views/functions, you'll have to deal with those first.

ALTER TABLE dbo.table DROP COLUMN oldchar10_column;

Then rename the new column so that it look like the old column.

EXEC sys.sp_rename N'dbo.table.newvarchar10_column',
       N'oldchar10_column', N'COLUMN';

Don't forget to update any procedures or code that still think this column is a char(10).

This is just a theory. You might want to test it on a non-critical system first to test the effects on data/log files, figure out the sweet spot for ?top?, etc.


WiredTiger - "too many open files error" during resync of a secondary replica set member I'm upgrading a secondary replica set member to wiredTiger. I have upgraded it from MongoDB 2.6.3 to 3.0.4 and changed the storage engine to wiredTiger. Now it is resyncing all data from the primary.
At some point the following error is received, and the process starts all over again:

  2015-07-22T13:18:55.658+0000 I INDEX    [rsSync]         building index using bulk method

  2015-07-22T13:18:55.664+0000 I INDEX    [rsSync] build index done.  scanned 1591 total records. 0 secs

  2015-07-22T13:18:56.397+0000 E STORAGE  [rsSync] WiredTiger (24) [1437571136:397083][20413:0x7f3d9ed29700], file:WiredTiger.wt, session.create: WiredTiger.turtle: fopen:
  Too many open files

  2015-07-22T13:18:56.463+0000 E REPL     [rsSync] 8 24: Too many open files

  2015-07-22T13:18:56.463+0000 E REPL     [rsSync] initial sync attempt failed, 9 attempts remaining

The same machine was previously running 2.6.3 version without any open file limits issues. I'm aware that wiredTiger might be creating much more files, so it must be it, but does it keep them all open simultaneously ?

For reference:

  cat /proc/sys/fs/file-max

  10747371

In /etc/init.d/mongod the configuration is:

  ulimit -n 64000

According to the documentation it seems that mongo holds a file descriptor for every data file. As in wiredTiger this results in a file for each collection + a file for each index, according to a calculation for our usecase, can add up to over 700K.

So I can change the ulimit to 700000 or higher, but I'm wondering whether this is the most correct solution, and what alternatives exist.

checkout your /etc/security/limits.conf file and change soft and hard limits according to your need.

* soft nofile 4096

* hard nofile 4096

in the first column * means this limit is applied for all users except root user.
you also need to edit /etc/pam.d/common-session* file add the following line in the end :

session required pam_limits.so

or else

  ulimit -n 65535 (and no reboot required)

Finally, logout/reboot .


MySQL Cluster - Got error 1601 'Out extents, tablespace full' from NDB I am trying to do some tests on MySQL Cluster.
I am creating my database using following commands:

CREATE DATABASE test1;
use test1;
CREATE LOGFILE GROUP lg1
ADD UNDOFILE 'undofile.dat'
ENGINE = NDB;
CREATE TABLESPACE ts1
ADD DATAFILE 'datafile.dat'
USE LOGFILE GROUP lg1
ENGINE = NDB;
CREATE TABLE `dane` (
  `id` int(11) NOT NULL,
  `data` blob NOT NULL,
  PRIMARY KEY (`id`)
)
TABLESPACE ts1 STORAGE DISK
engine=ndb;

Then I populate table with some data and random blobs 50KB each. But I am getting following error:

mysql> INSERT INTO dane(id, data) VALUES(3790, randomBlob(51200));
ERROR 1114 (HY000): The table 'dane' is full
mysql> show warnings;
+---------+------+--------------------------------------------------------+
| Level   | Code | Message                                                |
+---------+------+--------------------------------------------------------+
| Warning | 1296 | Got error 1601 'Out extents, tablespace full' from NDB |
| Error   | 1114 | The table 'dane' is full                               |
+---------+------+--------------------------------------------------------+
2 rows in set (0,00 sec)

mysql> select count(*) from dane;
+----------+
| count(*) |
+----------+
|     2507 |
+----------+
1 row in set (0,03 sec)

It is not cluster memory usage problem:

ndb_mgm> ALL REPORT MEMORYUSAGE
Connected to Management Server at: localhost:1186
Node 3: Data usage is 6%(164 32K pages of total 2560)
Node 3: Index usage is 7%(178 8K pages of total 2432)

2500*50KB ~= 1GB
I have this error any time I am trying to insert more than 1GB of blobs.
How I can extend size of this tablespace?

First 2500*50kB ~= 128MB. Then if you will check CREATE TABLESPACE syntax default INITIAL_SIZE for the datafile is 128MB!

So if you want to store more data you can either specify INITIAL_SIZE you need while creating tablespace or you can ALTER TABLESPACE ADD DATAFILE.

By specifying STORAGE DISK in CREATE TABLE statement you stated that you want on disk table not in memory. With such tables ALL REPORT MEMORYUSAGE shows nothing meaningful for that table.

If you want "classic" NDB table which is stored in memory just skip TABLESPACE ts1 STORAGE DISK modifiers and then ALL REPORT MEMORYUSAGE will show you how much memory is used for the data and indexes.


Searching for scalable storage solution In one project I save statuses of processing in MySQL table:

`id`  `name`  `email`  `has_apples`  `has_bananas`
 1     Bob     a@b.c    1             0
 2     Mike    c@d.e    0             1
 3     John             1             1

name+email pair should be unique.
For now everything works fine. But the table grows rapidly, and currently it contains about 100m rows.
According to the development needs I have to add, lets say, has_oranges column.

Altering such a big table is painful, requires downtime and it doesn't look like that the MySQL is a right tool for this problem.

Even more, the number of such type of data will grow in the future.
I'm looking for a long-term scalable solution, which will allow:

fast search by id
fast search by name
unique control of the name+email pair
column scalable
storage scalable

I'm not familiar with NoSQL solutions, but it seems that maybe that's the way to go.

I'm not familiar with NoSQL solutions, but it seems that maybe that's the way to go.

I think you're way off in what requires NoSQL and big data. By at least a few orders of magnitude:

100 million is not much, and grows rapidly needs to be quantified.
Schema changes also need to be quantified, do you need schema?
Speaking for PostgreSQL, extending a table does not result in the table being rewritten.

Using PostgreSQL

Following my own advice and creating an email type here is an example,

CREATE EXTENSION citext;
CREATE DOMAIN email AS citext
  CHECK ( value ~ '^[a-zA-Z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$' );

CREATE TABLE contacts (id,name,emailaddress)
AS
  SELECT
    id::int,
    id::text || ' First Last',
    (id::text||'first@last.com')::email
  FROM generate_series(1,100e6) AS gs(id);

Keep in mind, some of that time is spent validating the email addresses against the HTML5 spec. Anyway, that all finishes in 720882.332 ms or 12 minutes on my rusty laptop. Keep in mind, that's without the indexes. Now we add the indexes.

CREATE UNIQUE INDEX ON contacts (name, emailaddress);
ALTER TABLE contacts ADD PRIMARY KEY (id);
ANALYZE contacts;

Tada, test data done.

SELECT * FROM contacts WHERE id = 424242;
   id   |       name        |     emailaddress
--------+-------------------+----------------------
 424242 | 424242 First Last | 424242first@last.com
(1 row)

Time: 4.534 ms

SELECT * FROM contacts WHERE name = '424242 First Last';
   id   |       name        |     emailaddress
--------+-------------------+----------------------
 424242 | 424242 First Last | 424242first@last.com
(1 row)

Time: 5.224 ms

ALTER TABLE contacts ADD COLUMN has_apples bool;
ALTER TABLE
Time: 8.612 ms

ALTER TABLE contacts ADD COLUMN has_bananas bool;
ALTER TABLE
Time: 14.813 ms

Adding columns like that is probably not a good idea, why not use the jsonb type

ALTER TABLE contacts ADD COLUMN fruits JSONB;
ALTER TABLE
Time: 7.957 ms

CREATE INDEX ON contacts USING gin(fruits);
Time: 24928.756 ms

INSERT INTO contacts (id,name,emailaddress,fruits)
VALUES
  (100e6+1, 'Evan Carroll', 'foo@whatever.test', '{"bananas":true}');
INSERT 0 1
Time: 8.630 ms

SELECT * FROM contacts WHERE fruits @> '{"bananas":true}';
    id     |     name     |   emailaddress    | has_apples | has_bananas |      fruits
-----------+--------------+-------------------+------------+-------------+-------------------
 100000001 | Evan Carroll | foo@whatever.test |            |             | {"bananas": true}
(1 row)
Time: 5.188 ms

Etc., Not sure what you mean by "scalable storage", or "unique control of the name+email pair".

Sizes are also manageable. For $70, you can fit the entire index in RAM.

Table: 7301 MB
Index: Pkey: 2.1GB, name/email unique: 6.4GB, gin/json: 102MB.


How to calculate AWS RDS Data Transfer Pricing I'm looking into the possibility of switching my database to Amazon's RDS offering.

At the moment it is just MySQL installed on a normal LEMP server along with everything else the application needs to run.

While the pricing for RDS instances is clear, I can see there is pricing for data transfer out to the internet of $0.090 per GB.

Presumably this includes query results.

But I have no idea how many bytes my query results are, or how many I might use in a day. Therefore I have no idea how much switching to RDS might actually cost.

Is there any way to calculate this?

This may very well be a stupid question but I'd hate to be stung by a huge fee because I didn't fully understand the costs involved.

Based on my experience, AWS does not charge for the query output of RDS Database of MySQL engine, but it charges for Aurora Engine.


How to control blocking sessions while an index maintenance job runs? I would like to know, is there any possibility to control the locks while Ola's index optimize job is running?

I have implemented Ola Hallengren's index optimize job and am running it every day, but it is blocking the user transactions and causing performance issues.

How can I avoid blocking and locks while an index job is running?

Not really controlling solution but you may try new index rebuild options :

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-2017

::=
{
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION =  [ MINUTES ] ,
                          ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )
}

this way you may control behavior of maintenance operation if it is blocking other processes.


Export .MWB to working .SQL file using command line We recently installed a server dedicated to unit tests, which deploys
  updates automatically via Jenkins when commits are done, and sends
  mails when a regression is noticed
   > This requires our database to always be up-to-date

  Since the database-schema-reference is our MWB, we added some scripts
  during deploy, which export the .mwb to a .sql (using python) This
  worked fine... but still has some issues

Our main concern is that the functions attached to the schema are not exported at all, which makes the DB unusable.

We'd like to hack into the python code to make it export scripts... but didn't find enough informations about it.
Here is the only piece of documentation we found.  It's not very clear for us.  We didn't find any information about exporting scripts.
All we found is that a db_Script class exists.  We don't know where we can find its instances in our execution context, nor if they can be exported easily.  Did we miss something ?

For reference, here is the script we currently use for the mwb to sql conversion (mwb2sql.sh).
It calls the MySqlWorkbench from command line (we use a dummy x-server to flush graphical output.)
What we need to complete is the python part passed in our command-line call of workbench.

# generate sql from mwb
# usage: sh mwb2sql.sh {mwb file} {output file}
# prepare: set env MYSQL_WORKBENCH

if [ "$MYSQL_WORKBENCH" = "" ]; then
  export MYSQL_WORKBENCH="/usr/bin/mysql-workbench"
fi

export INPUT=$(cd $(dirname $1);pwd)/$(basename $1)
export OUTPUT=$(cd $(dirname $2);pwd)/$(basename $2)

"$MYSQL_WORKBENCH" \
  --open $INPUT \
  --run-python "
import os
import grt
from grt.modules import DbMySQLFE as fe
c = grt.root.wb.doc.physicalModels[0].catalog
fe.generateSQLCreateStatements(c, c.version, {})
fe.createScriptForCatalogObjects(os.getenv('OUTPUT'), c, {})" \
  --quit-when-done
set -e

note: posted on SO last week, but with 13 views in 4 days (and 1 up!), I doubt it was the right place.
I'll remove it from SO if I get answers here

@Rick James's comment :

MySQLDump was our previous option, but we'd like something which doesn't require an action from the developper anymore.
It doesn't only save the time taken to export / commit the .sql, but more importantly time spent due to mistakes, debugging problems which happen only because one forgot to update the .sql.

An other "alternative" could be to manually export the .mwb via the
GUI, since it features a checkbox to "also export scripts", but
again, it's not mistake-proof, and we know it will cause problems
We might also end-up with an export of the functions in a separate
(manually created) .sql, and import separately both structure and
scripts files, since our functions are rarely modified... but this isn't the safest / more efficient way either

We thought MySQL Workbench would be easier to use with command line.  It's quite disappointing to see that such tool is missing accessibility for features... which are accessible via the GUI !

Switch to mysqldump; it is aimed at scripting, whereas Workbench is aimed at UI.

Use the option --routines in addition to whatever else you need.

--no-data gives you just the schema.


Is it possible to host pbix and rdl files on the same report server instance? Just upgraded an existing instance of SQL Server 2014 to 2016. That instance has an instance of Reporting Services installed. My end goal is to connect Power BI Desktop (March 2018) to my existing report server. I've downloaded and installed Power BI Report Server. Worked through the reporting services configuration manager.

In Power BI Desktop, I try to pass my existing url to the Power BI Report Server Selection pane. The url that i'm passing is pulled straight from the config manager and I can connect to it using IE. Current error reads: "We couldn't find a Power BI Report Server at this address"

I've restarted the the service on my windows box, still no luck. Any ideas?

yes, it is possible and I am using it on my organization.

Just one tip: to deploy reports, we need to provide the Report Server URL (by default /reportserver) but on Power BI, we need to provide the report manager URL (default /reports).


SQL server replication (publisher, subscriber, distributor) on top of AlwaysOn I am assessing the migration from SQL 2008R2 to SQL 2017 AlwaysOn where we have replication (publisher, subscriber, distributor) setup on SQL2008R2.
There are various restrictions if one wants to setup Replication on top of AlwaysOn..For e.g below

Merge replication is not supported.

Transactional replication with immediate or queued updating subscriber is not supported.

Peer to peer replication is not supported.

Bidirectional transactional replication is not supported.

Does anybody have any simple select script which can confirm that my replication setup on SQL 2008R2 is anything out of the above type?

I tried checking "mspublication" table but that isn't providing any guidance to confirm whether my replication setup is out of the above 4 types.
I don't have access on client's SQL instances so want to provide them simple select statement.

This answer has been copied from the accepted answer on this question of Stack Ovweflow:
How to determine the SQL Server replication type by TSQL?

Run the following query on the distribution database:
SELECT
     P.Publication
    ,P.Publication_type
    ,S.Subscriber_ID
    ,S.Update_Mode
FROM
    MSPublications P
INNER JOIN
    MSSubscriptions S
    ON P.Publication_ID = S.Publication_ID;

Publication_type:

0 = Transactional
1 = Snapshot
2 = Merge

Update_mode

0 = Read only
1 = Immediate update
2 = Queued update with message queue
3 = Immediate update with queued update as fail-over using message queue
4 = Queued update with SQL Server queue
5 = Immediate update with queued update as fail-over using SQL Server queue


I have account_id on all of the tables in the database that belong to an account. Could this be why our database is using a lot of memory? The application makes heavy use of data. It is a Real Estate Lead Management System each lead could make our users thousands of dollars, and accidentally returning other people's leads would be a sure-fire way to lose our customers trust. We make heavy use of the entity pattern on the client.  This requires us to get collections of data and store them as collections on the client. My thinking when I was designing the database was that if I had the account id on each table, that would make getting all of the data easier without returning data from other account, with fewer or more performant queries. I realize that there are other ways to handle this, but we were on a very short deadline and had to build a full app with ~50 tables for a beta launch in 3 Months.  That being said, we also have many queries that heavily use joins and group by methods to prevent (n+1) trips to the database.  Is it simply that analyzing lots of data is something that requires a larger database? The big problem is that we only have 45 Active customers currently.  The app is fast and feels great, we are just pushing the limits of the memory of the database.

This is the pattern that I did.  I know it's not normalized for the keys but everything I have read did not seem like it would cause this issue.  But I'm not a database specialist and would appreciate any advice.

Account Table:
id
first_name
...etc

Record Table:
id
account_id
address
...etc

Analysis Table:
id
account_id
record_id
expected_return_on_investment
...etc

Comps Table:
id
account_id
record_id
analysis_id
cost
...etc

Welcome.

There can not be a best and perfect database design (always). It all depends upon your (project) use case, coding simplicity and expandability.

Any best database design will have its own (exclusive) disadvantages too.

Nothing negative about your described design (until you listed - though not all of your ~50 tables). But you shall think of account as a single entity where in the record details goes into the account table itself. Referring

account.first_name, account.address1 account.city

looks good unless your account holders have multiple addresses.


How to get SQLCMD to output errors and warnings only? How can you get SQLCMD, when executing a SQL script file, to just output any errors or warnings it encounters?

I essentially dont want information based messages to be output.

By default, SQLCMD sends all non-error messages and error messages to stdout. Hence, attempting to redirect output won't help.

The first thing you need to do in order to get only one or the other type of messages (error or non-error) is to tell SQLCMD to separate them by sending the error messages (severity level 11 or higher) to stderr. You do this by using the -r command-line switch, specifying 0 as the option for that switch (i.e. -r0). At this point there is no visible difference in terms of running SQLCMD and seeing messages of any type displayed on the screen.

The next part is to filter out the regular messages going to stdout.  This can be done by redirecting the stdout messages to somewhere via >, but where to? You could do > file.txt, but I doubt you want a file of the messages that you didn't want to begin with. Fortunately, DOS has the NUL keyword (that is not a typeo: it has one L, not two) that works like /dev/null in Unix. Meaning you can use the following to redirect output to nowhere: > NUL.

The following will execute the PRINT command and show no output as no errors are generated, and no file is created that would contain the output of the PRINT command:

CD %TEMP%

SQLCMD -E -Q "print 1;" -r0 > NUL

But the following displays an error message as those are not being redirected to NUL:

CD %TEMP%

SQLCMD -E -Q "print a;" -r0 > NUL

Returns:

Msg 128, Level 15, State 1, Server DALI, Line 1
The name "a" is not permitted in this context. Valid expressions are constants,
constant expressions, and (in some contexts) variables. Column names are not permitted.


Are there any in-memory NoSQL databases? Given that NoSQL databases are designed to support horizontal scalability by throwing in more servers, are there any that are designed to do entirely without persistent disk storage?

I'm looking at an application that requires horizontal scalability but that also needs to encrypt any data at rest. That could end up meaning pulling a lot of the data from the NoSQL database into an in-memory cache like memcached. That got me wondering: Is there any single product that had the scale and fault-tolerance characteristics of a NoSQL database but operated solely in memory?

The issue with storing your database in memory is if you have any sort of memoery issue or server has to be restarted or anything of that issue all your memory will get flushed.

That is the reason people don't store their database in memory.

Now, there are caching tools which are in-memory and can work as a very simple database like memcached. That may meet your needs. If you look in to tmpfs and ramfs you can create a folder that exists in memory and move your files in there normally.

So, if you are working with MongoDB, mysql or whatever you work with, you can have the data folder live in the RAM folder. This will give the database super fast read and writes. Everything will be really fast. You will be limited to how much RAM you have minus the size of your OS and other things running.

Also, just be careful: MongoDB likes to store writes in memory until the disk has a chance to write, it so you may want to turn that feature off because it will be the same speed.

My recommendation is to work with memcached and then mix it with a normal database that lives on disk. The concept is done with PHP sessions on some systems.

http://mickeyben.com/2009/12/30/using-nginx-as-a-load-balancer.html

The basic way it works is, if your record is found in memcached, then it will not check the database. If it is not found, then do three(3) things:

check the db
send the data to memcached
send the data to calling function

:)


Mongodb rs.add() new member in STARTUP state I have been running a shell script that starts mongod instances on different machines, initializing them (the first machines) and adding them to a replica set (the others). The following is a summary of what I ran:

  Machine 1 - mongod and initiate as primary by running rs.initiate()

  Machine 2 - mongod and contact primary to run rs.add()

However, when I add the second mongod instance, the response was ok, but running rs.status() on primary it shows Machine 2 in STARTUP state

  {
                          "_id" : 1,
                          "name" : "Machine 2",
                          "health" : 1,
                          "state" : 0,
                          "stateStr" : "STARTUP",
                          "uptime" : 30,
                          "optime" : {
                                  "ts" : Timestamp(0, 0),
                                  "t" : NumberLong(-1)
                          },
                          "optimeDurable" : {
                                  "ts" : Timestamp(0, 0),
                                  "t" : NumberLong(-1)
                          },
                          "optimeDate" : ISODate("1970-01-01T00:00:00Z"),
                          "optimeDurableDate" : ISODate("1970-01-01T00:00:00Z"),
                          "lastHeartbeat" : ISODate("2018-06-14T07:07:11.336Z"),
                          "lastHeartbeatRecv" : ISODate("1970-01-01T00:00:00Z"),
                          "pingMs" : NumberLong(0),
                          "configVersion" : -2

Further investigation using the db.runCommand({getLog:"rs"} reveals the following:

  "2018-06-14T12:23:54.223+0800 I REPL     [replexec-5] Member Machine 2 is now in state RS_DOWN"

Why is my machine on STARTUP when I first add it? What is this RS_DOWN? I'm using MONGO 3.64 for both machines. I always delete local db before trying out the scripts

I figured out what is wrong with my replica set clusters. You need to provide an argument to rs.initiate(), which is the configuration document. If you don't specify the document, it will read the configuration from the /etc/mongod.conf which will has no configuration whatsoever for the replica set. The configuration for rs.initiate() looks something like this:

rs.initiate(
   {
      _id: "myReplSet",
      version: 1,
      members: [
         { _id: 0, host : "mongodb0.example.net:27017" },
         { _id: 1, host : "mongodb1.example.net:27017" },
         { _id: 2, host : "mongodb2.example.net:27017" }
      ]
   }
)

But if you do not know before hand how many replica sets you want to add to the cluster it is ok, just specify the first one and the subsequent ones will be added without any problems:

rs.initiate(
   {
      _id: "myReplSet",
      version: 1,
      members: [
         { _id: 0, host : "mongodb0.example.net:27017" }
      ]
   }
)


Isolating access to tenant data in a multi-tenant database We have a single SQL Server database for all tenants of a web-application. How can we ensure when a support engineer queries the DB directly via SSMS, they only touch data belonging to one tenant?

The web-application reliably isolates data between tenants as all DB queries have an additional clause added automatically to every query: WHERE tenantID = @currentUserTenantId.

The support team have a need to query the database directory and sometimes perform changes to the database. How can we ensure someone doesn't forget to add WHERE tenantID = X when executing a query via SSMS?

Row Level Security (RLS) may be one such solution which would require the support engineer to set the tenantID via SESSION_CONTEXT and reject running the query if no tenantID was specified. Support engineers each have their own SQL User.

Alternatively is there a way, using RLS or otherwise, to ensure that a query ran by a support engineer can only touch a single tenantID? i.e. if they failed to specify WHERE tenantID = X and a query would return data with multiple values in tenantID, then block the query?

Perhaps an ideal solution would be for support engineers not to be able to run any raw SQL and only run predefined stored procedures maintained by the development team. However we don't have the resources to do this at present.

Similarly, Zendesk/Salesforce have the ability for customers to grant access to their respective support teams. I've also seen other solutions which require the customer to share a code with the support team (say by phone). How might that be implemented to protect queries ran via SSMS?

I would probably provision "tenant support identity" (user or role) and have process where a support engineer is provisioned access to a single tenant, through temporary membership in the role, or by giving the support person the password to a temporarily-enabled user.

The developers would add stored procedures that manage the tenant support identity, along with perhaps some auditing.

Then the only difference between a single-tenant and multi-tenant database is that you have to implement RLS for the tenant support identity.


SQL Server and SQL anywhere compatbility I wish to install a SQL server DB on a VM already running a SQL anywhere DB.

May I encounter a compatibility issue ?

There's no reason why you should have a problem running SQL Anywhere and SQL Server on the same machine - so long as you have the appropriate resources to meet the needs of both. That will depend entirely on how busy the various databases are. We have many machines running both.

NB There is no connection between the code bases of SQL Anywhere and SQL Server. SQL Anywhere is a development of WATCOM SQL and although it was owned by Sybase and now SAP, it is an entirely separate product from Sybase (now SAP) ASE - Adaptive Server Enterprise, which did originally have a common origin with MS SQL Server.

If you run into any problems it might be worth posting on http://sqlanywhere-forum.sap.com/questions/ which is a specialist SQL Anywhere forum.


Simple select is taking too long I am facing an issue with the performance. The selection of data from table is taking more than 20 sec to retrieve close to 300k records. Created a clustered index on col1 and col2 but no improvement.

Select Distinct Col1,Col2,Col3 + CHAR(13) + CHAR(10) Col3
From Table
Order by Col1,Col2

Update

Created indexes on col1 and col2 but col3 data is very big, more than 8000 characters, so SQL-server will not allow index on such column.

I am not sure how group by will help in removing the distinct.

As I mentioned it is simple select from table, yes 300k records has to be returned from table.

CREATE TABLE Tablex(
[Col1] [nvarchar](5) NULL,
[Col2] [nvarchar](20) NULL,
[Col3] [nvarchar](max) NULL) ON [PRIMARY]

Create Clustered Index ix_FCL_Tran On Tablex (Col1 Asc, col2 Asc)

Let me know if any more details is required.

Update
No change after adding option fast here is the execution plan

If you only need to know that a row is distinct, and don't need the actual contents of col3, then perhaps returning the hash of col3 would speed up the query?

You could even perhaps pre-compute the hash using a calculated column so that you aren't computing the hash on the fly.

If you do need the contents of col3, but have a lot of duplicates of col1+col2+col3, then it still may be beneficial to work with the hash to remove duplicates as a sub-query, then only return the col3 contents for the distinct rows.


Single or multiple databases? I am in the progress of designing a database (or databases) for somebody and since I am a beginner I would love some advice.

The data is described as:

Customer1 (c1.type1, c1.type2, c1.type3)
Customer2
Customer3
Customer4 (c4.type1, c4.type2)
Customer5
Customer6
...
Customer10

There will be around 10 types of customers, perhaps less.

Not all the customers have different sub-types.  The type for customer1 for example, would be different than for other customers.

All of them would have general info included, for instance address, email, and phone number, along with other info.

Every customer generates an invoice. They pay different amounts since they buy different things.

We are predicting the tables may contain many millions of records.

Should I have multiple databases for each customer type?

Should I have one database with multiple tables for each customer type?

Should I have one database with one table for all customer types?

What about invoices?  Separate database?  Separate table?  One invoices table for all invoices?

As of this moment I am inclined to go with one database with multiple tables for each customer type.  The invoices would be in a separate table.

What do you think?

You seem to use the word "database" when you mean "table."  One database for all data is best.  Most modern databases (PostgreSQL, MySQL, Oracle, DB2, SQL Server, etc.) can handle a large amount of data in one database.  It sounds like you might want to access all customer and/or all invoice information together, so keep them together in one database.

Data should be split into separate tables to normalize it as needed.  Fully normalized data (each piece of information stored only once) is recommended by every database textbook I have read.  Read about (or at least Google) "database normalization" to understand that concept well.

One normalized approach would be to have one customer table indexed by a customer id with all fields common to every customer.  The customer table would also have a field identifying the customer type: customer1, 2, ... 10.  A separate table would hold customer-1-type fields, indexed by customer id.  Another table would have customer-2-type fields, etc.  The application using the database would be designed to look for data in the customer-1 table if the customer-type is customer1, look for data in customer-2 table if customer-type is customer2, etc.

Presumably, the relationship between customer and invoice is one to many.  A invoice table could have a foreign key linking to the customer table.  If invoice data varied by customer-type, then that data could be placed in separate tables: invoice-customer-type-1, invoice-customer-2, etc.  When gathering invoice data, find the customer id for the invoice from the invoice table, look up the customer-type from the customer table, then get data from the appropriate invoice-customer-# table.

Also, the tables in the database should be structured how the data will be used.  For data entry and updating, a normalized structure is good.  For printing bills or other reporting, you might want to create temporary de-normalized tables to speed the process.  Creation of a temporary table can be faster because you avoid making multiple table joins every time a bill is printed.

Get a textbook and study database design, but in the meantime, I hope this helps.


What happens if I can't rebuild indexes after setting PAGE_VERIFY to CHECKSUM? I've come across a production SQL Server 2008 box that has quite a few databases with PAGE_VERIFY option set to NONE. I'm going to set the PAGE_VERIFY option to CHECKSUM. If I understand correctly, I must rebuild the existing indexes for CHECKSUM to affect them. Also, that if I rebuild a clustered index the non-clustered indexes will be rebuilt as well.

What will happen to the existing indexes if I can't rebuild them right away?
Are the existing indexes still usable after setting CHECKSUM without a rebuild?

Thanks!

"Also, that if I rebuild a clustered index the non-clustered indexes will be rebuilt as well." -- if you rebuild a clustered index, the non-clustered indexes won't be rebuild, please see Paul's blog post -- http://www.sqlskills.com/blogs/paul/indexes-from-every-angle-what-happens-to-non-clustered-indexes-when-the-table-structure-is-changed/ -- even though it says 2005, it's true for any version, as far as I know.
1. Nothing will happen
2. Will be usable

Also can read in Paul's blog, Myth a Day -- http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1730-page-checksums/

As you and Jon, added -- there are 2 ways (from Paul's blog):
1. There is no process, background or otherwise, that can put a page checksum on each page. This is a major bummer (technical term :-) as it means you must perform index rebuilds or other size-of-data operations to actually put a page checksum on the pages.
2. A page checksum is only put on a page when it is read into memory, changed, and then written back out to disk.