SQL Server Timeout: Gathering server side information We use SQL Server 2008 for our database. We use a combination of Classic ASP and .NET to connect to said database. We get random timeouts on queries that are extremely simple and when we run said queries they appear to be quick and have the correct indexes.

What I'm wondering is if there is a way to have SQL Server gather/keep data on these timeouts and send it to us. I've read in the past that you can do this for deadlocks.

Perhaps (at least some of) these "random timeouts" might be network related?

You should definitely consider performance monitor.  You can view statistics in real time, collect statistics to generate a baseline, and configure triggers to send alerts.

PS:
Here's a good link to get you started:

Free Microsoft Tools to Help Setup and Maintain PerfMon for SQL Server


How does one investigate the performance of a BULK INSERT statement? I am mainly a .NET developer using Entity Framework ORM. However, because I don't want to fail in using the ORM, I am trying to understand what happens within the data layer (database). Basically, during the development I start the profiler and check what some parts of code generate in terms of queries.

If I spot something utterly complicated (ORM can generate awful queries even from rather simple LINQ statements, if not carefully written) and/or heavy (duration, CPU, page reads), I take it in SSMS and check its execution plan.

It works fine for my level of database knowledge. However, BULK INSERT seems to be a special creature, as it does not seem to produce a SHOWPLAN.

I will try to illustrate a very simple example:

Table definition

CREATE TABLE dbo.ImportingSystemFileLoadInfo
(
    ImportingSystemFileLoadInfoId INT NOT NULL IDENTITY(1, 1) CONSTRAINT PK_ImportingSystemFileLoadInfo PRIMARY KEY CLUSTERED,
    EnvironmentId INT NOT NULL CONSTRAINT FK_ImportingSystemFileLoadInfo REFERENCES dbo.Environment,
    ImportingSystemId INT NOT NULL CONSTRAINT FK_ImportingSystemFileLoadInfo_ImportingSystem REFERENCES dbo.ImportingSystem,
    FileName NVARCHAR(64) NOT NULL,
FileImportTime DATETIME2 NOT NULL,
    CONSTRAINT UQ_ImportingSystemImportInfo_EnvXIs_TableName UNIQUE (EnvironmentId, ImportingSystemId, FileName, FileImportTime)
)

Note: no other indexes are defined on the table

The bulk insert
(what I catch in profiler, one batch only)

insert bulk [dbo].[ImportingSystemFileLoadInfo] ([EnvironmentId] Int, [ImportingSystemId] Int, [FileName] NVarChar(64) COLLATE Latin1_General_CI_AS, [FileImportTime] DateTime2(7))

Metrics

695 items inserted
CPU = 31
Reads = 4271
Writes = 24
Duration = 154
Total table count = 11500

For my application, that's ok, although the reads seems rather large (I know very little about SQL Server internals, so I comparing to the 8K page size and the small record information I have)

Question: how can I investigate if this BULK INSERT can be optimized? Or it does not make any sense, since it is arguably the fastest way to push large data from a client application to SQL Server?

As far as I can tell you can optimize a bulk insert in a very similar way that you'd optimize a regular insert. Typically, a query plan for a simple insert isn't very informative so don't worry about not having the plan. I'll go over a few ways of optimizing an insert but most of them probably don't apply for the insert you specified in the question. However, they could be helpful if in the future you need to load larger amounts of data.
1. Insert data in clustering key order
SQL Server will often sort data before inserting it into a table with a clustered index. For some tables and applications you can improve performance by sorting the data in the flat file and letting SQL Server know that the data is sorted through the ORDER argument of BULK INSERT:

ORDER ( { column [ ASC | DESC ] } [ ,... n ] )
Specifies how the data in the data file is sorted. Bulk import performance is improved if the data being imported is sorted according to the clustered index on the table, if any.

Since you are using an IDENTITY column as the clustered key you don't need to worry about this.
2. Use TABLOCK if possible
If you are guaranteed to have only one session inserting data into your table you can specify the TABLOCK argument for BULK INSERT. This can reduce lock contention and can lead to minimal logging in some scenarios. However, you are inserting into a table with a clustered index that already contains data so you will not get minimal logging without trace flag 610 which is mentioned later in this answer.
If TABLOCK is not possible, because you can't change the code, not all hope is lost.  Consider using sp_table_option:
EXEC [sys].[sp_tableoption]
    @TableNamePattern = N'dbo.BulkLoadTable' ,
    @OptionName = 'table lock on bulk load' ,
    @OptionValue = 'ON'

Another option is to enable trace flag 715.
3. Use an appropriate batch size
Sometimes you will be able to tune inserts by changing the batch size.

ROWS_PER_BATCH = rows_per_batch
Indicates the approximate number of rows of data in the data file.
By default, all the data in the data file is sent to the server as a single transaction, and the number of rows in the batch is unknown to the query optimizer. If you specify ROWS_PER_BATCH (with a value > 0) the server uses this value to optimize the bulk-import operation. The value specified for ROWS_PER_BATCH should approximately the same as the actual number of rows. For information about performance considerations, see "Remarks," later in this topic.

Here is the quote from later in the article:

If the number of pages to be flushed in a single batch exceeds an internal threshold, a full scan of the buffer pool might occur to identify which pages to flush when the batch commits. This full scan can hurt bulk-import performance. A likely case of exceeding the internal threshold occurs when a large buffer pool is combined with a slow I/O subsystem. To avoid buffer overflows on large machines, either do not use the TABLOCK hint (which will remove the bulk optimizations) or use a smaller batch size (which preserves the bulk optimizations).
Because computers vary, we recommend that you test various batch sizes with your data load to find out what works best for you.

Personally I would just insert all 695 rows in a single batch. Tuning the batch size can make a big difference when inserting lots of data though.
4. Make sure that you need the IDENTITY column
I don't know anything about your data model or requirements, but don't fall into the trap of adding an IDENTITY column to every table. Aaron Bertrand has an article about this called Bad habits to kick : putting an IDENTITY column on every table. To be clear, I'm not saying that you should remove the IDENTITY column from this table. However, if you determine that the IDENTITY column is not necessary and remove it that could improve insert performance.
5. Disable indexes or constraints
If you're loading a large amount of data into a table compared to what you already have then it may be faster to disable indexes or constraints before the load and to enable them after the load. For large amounts of data it is usually more inefficient for SQL Server to build an index all at once instead of as data is loaded into the table. It looks like you inserted 695 rows into a table with 11500 rows, so I would not recommend this technique.
6. Consider TF 610
Trace Flag 610 allows minimal logging in some additional scenarios. For your table with an IDENTITY clustered key, you would get minimal logging for any new data pages as long as your recovery model is simple or bulk-logged. I believe this feature is not on by default because it may degrade performance on some systems. You would need to test carefully before enabling this trace flag. The recommended Microsoft reference still appears to be The Data Loading Performance Guide

I/O Impact of Minimal Logging Under Trace Flag 610
When you commit a bulk load transaction that was minimally logged, all of the loaded pages must be flushed to disk before the commit completes. Any flushed pages not caught by an earlier checkpoint operation can create a great deal of random I/O. Contrast this with a fully logged operation, which creates sequential I/O on the log writes instead and does not require loaded pages to be flushed to disk at commit time.
If your load scenario is small insert operations on btrees that do not cross checkpoint boundaries, and you have a slow I/O system, using minimal logging can actually slow down insert speeds.

As far as I can tell this doesn't have anything to do with trace flag 610, but rather with minimal logging itself. I believe the earlier quote about ROWS_PER_BATCH tuning was getting at this same concept.
In conclusion, there probably isn't much that you can do to tune your BULK INSERT. I wouldn't be concerned about the read count that you observed with your insert. SQL Server will report reads any time you insert data. Consider the following very simple INSERT:
DROP TABLE IF EXISTS X_TABLE;

CREATE TABLE X_TABLE (
VAL VARCHAR(1000) NOT NULL
);

SET STATISTICS IO, TIME ON;

INSERT INTO X_TABLE WITH (TABLOCK)
SELECT REPLICATE('Z', 1000)
FROM dbo.GetNums(10000); -- generate 10000 rows

Output from SET STATISTICS IO, TIME ON:

Table 'X_TABLE'. Scan count 0, logical reads 11428

I have 11428 reported reads but that is not actionable information. Sometimes the number of reported reads can be reduced by minimal logging, but of course the difference cannot be directly translated into a performance gain.


See other schema's tables in SqlDeveloper When I connect to an Oracle DB with user XXX I see the tables, packages, etc. in schema XXX.
Is there a way so that I can connect with user XXX and see things in another schema YYY?

When I browse objects in SQLDeveloper I can see in the statement log that it sends XXX as parameter like "SCHEMA"="XXX", so I would just need to trick sqlDeveloper to use "SCHEMA"="YYY".

If you have the correct privileges to see the objects in other schemas you can see them by expanding the connection in the connections tab and looking for Other Users and you will be able to see all other users objects.

You can also start with the schema prefix and use that to see and access objects in other schemas.


Why might Sql Server Agent's Next Run Time value not be what I expect? And is this definitely the next time my job will run? At times, I'm seeing some inconsistencies with sql server agent's value in the sysjobschedules.next_run_date and time fields.

I have a web interface that interacts with the sql scheduling system.

I've created a test job that started on March 16th 2011 at 9:45am.
I've set it up to happen every 4 weeks on a Wednesday.

From today, this should put a next run date and time on January 18th at 9:45 but the Next Run Date/Time value for my job is saying January 4th.

The values in the job properties look good.
The start date says March 16th 2011. The frequency values are all correct. But it seems SQL is failing to land this occurrence properly.

Here is some relevant info for the job in question from the sp_help_jobschedule proc

freq_type   8
freq_interval   8
freq_subday_type    1
freq_subday_interval    1
freq_relative_interval  0
freq_recurrence_factor  4
active_start_date   20110316
active_end_date 99991231
active_start_time   154500
active_end_time 55959
date_created    2011-12-29 20:37:05.737
schedule_description    Every 4 week(s) on Wednesday at 154500
next_run_date   20120104
next_run_time   154500

if the start date is March 16 2011 at 9:45, it doesn't make sense that the next run date would fall on January 4th 2012 based on the properties of the job.

I guess my question is, how much can I rely on this Next Run Date data?

When did you check this data?  The msdb.dbo.sysjobschedules table refreshes every 20 minutes.  So if you had it set and then changed it, then ran the sp_help_jobschedule stored procedure, the underlying data might not be updated yet.

What do you get for next_scheduled_run_date when you execute this query?

exec sp_help_jobactivity @job_name = 'YourJobScheduleName'


Postgresql wal saving for standby servers that go offline I use postgresql 9.1 replication to a lot of workstations that have postgresql on hot standby mode. However sometimes these workstations go offline and disconnect from the master server. If they go offline for more than say a day they stop replicating, because the wal segments are no longer available. My question is how do I properly configure the master and the standby servers so that they can still replicate even after being disconnected from each other for several days.

Set up WAL archiving. If the master no longer retains enough WAL your replica will fall back to its archive_command.

See the documentation and this post I wrote earlier.


Database modelling : Creating a model for restaurant application which gives statistics I am working on a server-side application in which users will be able to give ratings and comments to different factors of a 'Restaurant'. What the webapp does is takes all the ratings and creates statistics out of it. The statistics are based upon day, week, month and year. As well as other things like gender ratio, age ratio, etc.

I am free to design an optimal DB model for this, and I have done some of it, but would like to ask if this is optimal, and if not, how can optimize it so its easier to maintain, understand, error-free.

The model I have right now for statistics seems a bit complicated to calculate, maintain. I am looking for some easier options.

Here is the information the visitor will give us :

CREATE TABLE rating
(
  rating_id integer NOT NULL, // primary key
  age_range integer DEFAULT 0,
  comment_text character varying,
  friendly_stars numeric DEFAULT 0,
  gender character varying,
  other_stars numeric DEFAULT 0,
  overall_feedback boolean DEFAULT false,
  relative boolean DEFAULT false,
  quality_stars numeric DEFAULT 0,
  save_time timestamp without time zone
)

Here is the Statistics and monthly statistics I have. Monthly statistics will actually save daily computed statistics with month code, so it's easier to isolate months also from that was something I thought :

CREATE TABLE monthly_statistic
(
  mstat_id integer NOT NULL, // Primary key
  daily_value timestamp without time zone,
  female_ratio character varying,
  friendly_quality numeric DEFAULT 0,
  high_age_ratio character varying,
  less_age_ratio character varying,
  man_friendly numeric DEFAULT 0,
  man_ratio character varying,
  mid_age_ratio character varying,
  other_quality numeric DEFAULT 0,
  service_quality numeric DEFAULT 0,
  woman_friendly numeric DEFAULT 0,
  statistics_id integer NOT NULL, // assocication with Statistics
)

And finally, here is Statistics :

CREATE TABLE statistics
(
  statistics_id integer NOT NULL,
  less_age_month character varying, // Less age means 18-24
  less_age_week character varying,
  less_age_year character varying,
  mid_age_month character varying, // Mid_age means 24-60
  mid_age_year character varying,
  mid_age_week character varying,

 more_age_month character varying, // More age means 60+
  more_age_week character varying,
  moreageyear character varying,
  month_friendly numeric DEFAULT 0,
  month_man character varying,
  month_other numeric DEFAULT 0,
  month_quality numeric DEFAULT 0,
  month_woman character varying,

  week_friendly numeric DEFAULT 0,
  week_man character varying,
  week_other numeric DEFAULT 0,
  week_quality numeric DEFAULT 0,
  week_woman character varying,
  year_friendly numeric DEFAULT 0,
  year_man character varying,
  year_other numeric DEFAULT 0,
  year_quality numeric DEFAULT 0,
  year_woman character varying,
  restaurant_branch_id integer NOT NULL, // Association with resto branch
)

So as you can see I have created values for week, month and year. But if the user wishes to see last week or this weeks statistics, then I need something more fine-grained, that's why I created monthly-statistics, with which I can save information based on per day.

I have to also show information like, within 1 week or 1 month or 1 year, how many of them were males or females, how many were with age 18-24, etc. I hope you guys get the idea. Kindly let me know if this data-model sounds suitable or if you guys have any idea for a better one.

Please note, that statistics are calculated only in night, not real-time and I am using PostgreSQL.. Have a good day.

Save all of the raw ratings (i.e. individual user inputs) with the date on which the rating was recorded.

Then you can calculate all of your weekly, monthly and yearly statistics based on the raw data each night.  These rolled up statistics should also be dated (with a date range).  In this way you can also keep multiple weeks, months and years so that you can look at trends if you like.

This is often called a data warehouse.  It is a common way of pre-calculating rolled-up statistics for quick and easy reporting.

When you get past the longest of your roll-ups (e.g. 1 year) you can start to throw away your raw transactional data if you're worried about how much space it takes up.


Design consideration regarding state handling: how to store multiple, variable states for one entity First I have to admit that I'm not a database professional neither are my colleagues. For a new project my colleagues and me came to a design question we couldn't really solve easily. And all the ideas had some disadvantages, so we could't figure out what's the best way to go.
We have a main entity "Transaction" which should be processed by "ProcessingRules". The processing rules can be configured by the users in the Web application (each rule has a different execution scheduling. One might be running every hour, whereas the others might run nightly).
Lets say Transaction gets 10'000 new records a day.
This would lead to a DB design where I need to keep the State "Processed YES/NO" for each "ProcessingRule" and "Transaction".
I thought the proper way is to have a relation table between the "ProcessingRoles" and the "Transaction". If no record present, the record has not been processed by this role yet.
Transaction [0..1] ------ [*] TransactionRuleProcessing [*] ------- [0..1] ProcessingRule
But when I think of the Query, this would lead into a WHERE NOT EXISTS (SELECT 1 FROM TransactionRuleProcessing...) query for the rule to identify new or unprocessed records.
If we have a large amount of rows in Transaction, I think this will affect performance because the NOT EXISTS will have to join the whole table to the state table. If I'm not mistaken, this might cause a performance issue.
On the other side, if we had only one state directly on the Transaction table, we could add an index and there would be no join between the large Transaction table and the state table.
Question:
Is it true that such a NOT EXISTS query would have to join the whole Transaction table with the TransactionRuleProcessing table to identify non existing (processed) rows? How could this affect performance of the database with a large Transaction table? What would be other recommendations to flag a record by a various amount of states?
Any ideas very much appreciated

Using the cross reference table between Transaction and ProcessingRule is going to be a very efficient way of returning Transaction records that need to be processed.

You can get a list of Transactions that need processing by:

USE Test;

CREATE TABLE Transactions
(
    TransactionID INT NOT NULL PRIMARY KEY CLUSTERED IDENTity(1,1)
    --extra Transaction fields here as necessary
);

CREATE TABLE Rules
(
    RuleID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1)
    --extra Rules fields here as necessary
);

CREATE TABLE TransactionRuleProcessing
(
    TransactionID INT NOT NULL
    , RuleID INT NOT NULL
);

DECLARE @RuleID INT = 123; --123 is the rule being processed

SELECT T.*
FROM Transactions AS T
    LEFT JOIN (
        SELECT *
        FROM TransactionRuleProcessing AS TRP1
        WHERE TRP1.RuleID = @RuleID
    ) AS TRP ON T.TransactionID = TRP.TransactionID
WHERE TRP.TransactionID IS NULL;


Postgresql arbitrary queries I have pgAdmin 4 and PostgreSQL 10.

I can't execute a query without quote marks i.e. I want to excute this

select * from table

but it only works if I use

select * from databasename."table"

How can I remove this restriction?

The name of the table is Complexitie. I was using postgresql the last year and I remember I could write queries in a simple way without mandatory syntax things like case senstive, mandatory quotes.

I read something about to setup arbitrary SQL queries en Postgres, but I can not find steps to configure it. thanks

You probably used a keyword as name of your table. You will find the complete list of those keywords in that page of the documentation.

From my experience, the most frequent ones are columns named end or start.

Have a nice day


Why does connecting to a database use tempspace? Earlier today we had a problem logging into a database that had just been created automatically.

When on the actual box (Linux) sqlplus schema/pw, i.e. a local connection, was fine, though, it was occasionally impossible to do anything once connected. Otherwise, whether using a session on the box or not sqlplus schema/pw@db did not work at all. tnsping and DNS testing revealed nothing and there was no problem in listener.ora.

The Listener was accepting the connection but not handing it off to the database. This was confirmed by using tail -f on the listener log and watching it accept the connection. There is a logon trigger on the database that inserts the user details / time etc into a table, but this doesn't have any indexes and there cannot have been any locks.

We finally traced the issue. There was a conflict in the file that creates the temporary tablespaces for the database. It resulted in there only being 5MB of tempspace instead of the normal 25GB. Disabling the logon trigger and then adding the additional temporary tablespaces, sorted everything out. There were some sessions already running that would have been using some of this tablespace.

Apparently Oracle requires some temporary tablespace in order to accept an incoming connection. The specific version used on this DB was 9i, though I think this applies to all.

Why and what is it used for?

During the connect process Oracle builds information on what access you have.  This remains static for the length of the connection.  (If you need to access newly granted privileges, you need to reconnect.)  Creating this data as temporary tables would seem the appropriate thing to do.  This would require temporary tablespace and could trigger the problems you were encountering.

The lack of locks, does not mean there isn't in-flight information floating around.  If the transaction inserting the data doesn't commit, then the database will need to track the block containing the row until the transaction is committed.  You can check for this by opening two connections and seeing if both can see the information from the login trigger before doing a commit.

There are views which will show what locks exist, and a wide variety of other information which is useful in managing the database.


MySQL performance tuning + queries stuck on "Copying to tmp table" The latter part of the question's title (queries stuck on "Copying to tmp table") has been addressed many times, and I have spent a fair amount of time researching this. I would appreciate it if you guys could help me come to a conclusion - especially with my particular server set up in mind.

Quick overview of server:
 - Dedicated server with 2 cores and 64 GB RAM
 - Only runs MySQL

The setup is in no way tweaked, so current config is to some extent way off. Hopefully your scolding could provide knowledge.

The web application running on the server is a Magento site with 25.000+ products. The query giving the most headache is the one generating sitemaps.

Currently, the following query has been stuck for a little over an hour on "Copying to tmp table":

  Note: I do not really need input on how to increase performance by
  optimizing this query, I would rather see how much I can shave query
  time off the queries that already exist.

SELECT DISTINCT `e`.`entity_id`, `ur`.`request_path` AS `url`, `stk`.`is_in_stock` FROM `catalog_product_entity` AS `e`
INNER JOIN `catalog_product_website` AS `w` ON e.entity_id=w.product_id
LEFT JOIN `core_url_rewrite` AS `ur` ON e.entity_id=ur.product_id AND ur.category_id IS NULL AND ur.store_id='1' AND ur.is_system=1
INNER JOIN `catalog_category_product_index` AS `cat_index` ON e.entity_id=cat_index.product_id AND cat_index.store_id='1' AND cat_index.category_id in ('2', '3', '68', '86', '145', '163', '182', '196', '198', '214', '249', '252', '285', '286', '288', '289', '290', '292', '549') AND cat_index.position!=0
INNER JOIN `cataloginventory_stock_item` AS `stk` ON e.entity_id=stk.product_id AND stk.is_in_stock=1
INNER JOIN `catalog_product_entity_int` AS `t1_visibility` ON e.entity_id=t1_visibility.entity_id AND t1_visibility.store_id=0
LEFT JOIN `catalog_product_entity_int` AS `t2_visibility` ON t1_visibility.entity_id = t2_visibility.entity_id AND t1_visibility.attribute_id = t2_visibility.attribute_id AND t2_visibility.store_id='1'
INNER JOIN `catalog_product_entity_int` AS `t1_status` ON e.entity_id=t1_status.entity_id AND t1_status.store_id=0
LEFT JOIN `catalog_product_entity_int` AS `t2_status` ON t1_status.entity_id = t2_status.entity_id AND t1_status.attribute_id = t2_status.attribute_id AND t2_status.store_id='1' WHERE (w.website_id='1') AND (t1_visibility.attribute_id='102') AND ((IF(t2_visibility.value_id > 0, t2_visibility.value, t1_visibility.value)) IN(3, 2, 4)) AND (t1_status.attribute_id='96') AND ((IF(t2_status.value_id > 0, t2_status.value, t1_status.value)) IN(1))

Relevant config:

Server buffers:

max_connections = 1500;
key_buffer_size = 22G;
innodb_buffer_pool_size = 16G;
innodb_additional_mem_pool_size = 2G;
innodb_log_buffer_size = 400M;
query_cache_size = 64M;

Per thread buffers:

read_buffer_size = 2M;
read_rnd_buffer_size = 16M;
sort_buffer_size = 128M;
thread_stack = 192K;
join_buffer_size = 8M;

  Question: Does any of these variables seem way off to any of you?

The above memory limits will allow me to use 130% more RAM than is physically installed on my system. Obviously, something must change.

According to the MySQLTuner Perl script, I am currently in danger of using:
Total buffers: 40.7G global + 28.2M per thread (1500 max threads)
Maximum possible memory usage: 82.0G (130% of installed RAM)

  Questions: What would give the most performance increase of the above variables, or: will it be more useful to increase the total server buffer limit or the per thread buffer limits?

The two variables that seem to affect "Copying to tmp table" the most are:
 - tmp_table_size
 - max_heap_table_size

Mine are both set to 0.25G

  Question: Any recommendations specifically for those to variables?

There are two suggested fixes that seem to get mentioned more that others:
 - Kill process. Do flush table.
 - Kill process. Do repair / optimize tables.

  Question: Which of the two above suggested solution do you think is the most viable?

This query definitely needs to be optimized since the "DISTINCT" part is forcing creation of temporary tables.

If you really don't want to touch the query (which is a mistake) - then your other option would be to create a RAM disk (tmpfs) for your tmp files. The query will still be creating temp tables, however it will avoid the disk I/O part.

Hope this helps.


Can we move SQL Server installed bit from default location to other drive My client gave me command line installer for SQL Server. Since it is command line there is no option to select the installation path! So installer automatically chooses the default location C:\Program Files (x86) drive for installation.

I would like to move all the installed bit from C:\Program Files (x86) drive to to other drive. Can I cut and paste the SQL folders from drive and map the new location in Management Studio?

The below link says that SQL Server is dependent on C: drive.
Installing SQL Server on not the default path

Requirement: I am using Azure VMs and attached extra drives. I want to make sure my SQL Server to be stable in case OS drive get corrupted.

The easiest method is (probably) to uninstall and this time run the command line installer again with a different set of flags and it will install to a different location unless you are talking about components not listed below.

From: https://msdn.microsoft.com/en-us/library/ms144259.aspx

Proper Use of Setup Parameters

Use the following guidelines to develop installation commands that have correct syntax:

   /PARAMETER
   /PARAMETER=true/false
   /PARAMETER=1/0 for Boolean types
   /PARAMETER="value" for all single-value parameters. Using double quotation marks is recommended, but required if the value contains a space
   /PARAMETER="value1" "value2" "value3" for all multiple-value parameters. Using double quotation marks is recommended, but required if the value contains a space

SQL Server Setup Control

/INSTALLSHAREDDIR

Specifies a nondefault installation directory for 64-bit shared components.
Default is %Program Files%\Microsoft SQL Server
Cannot be set to %Program Files(x86)%\Microsoft SQL Server

/INSTALLSHAREDWOWDIR

Specifies a nondefault installation directory for 32-bit shared components. Supported only on a 64-bit system.

/INSTANCEDIR

Specifies a nondefault installation directory for instance-specific components.


Can we assign mysql Key_buffer_size value for particular table? In some documents I found that we can assign key_buffer_size to myisam table. This also includes the following steps:

SET GLOBAL myisam_table.key_buffer_size = 4194304;

CACHE INDEX test.myisam_table IN myisam_table;

LOAD INDEX INTO CACHE test.myisam_table;

This command worked fine.

But how can I see the MyISAM table that is taking the key_buffer_size not the global one?

Is there any other MySQL engine that supports something similar?

You can't give this value for a particular table this value indicates Index blocks for MyISAM tables are buffered and are shared by all threads. key_buffer_size is the size of the buffer used for index blocks. The key buffer is also known as the key cache.

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_key_buffer_size

InnoDB engine type does support similar option called innodb_buffer_pool_size

The size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables. The default value is 8MB. The larger you set this value, the less disk I/O is needed to access data in tables. On a dedicated database server, you may set this to up to 80% of the machine physical memory size. However, do not set it too large because competition for physical memory might cause paging in the operating system.


Unable to Mask data after applying mask function in Azure Synapse table I have created table prd.CustomerDimension

CREATE TABLE [prd].[CustomerDimensions]
(
    [WWI Customer ID] [int] NOT NULL,
    [Customer] [nvarchar](100) NOT NULL,
    [Bill To Customer] [nvarchar](100) NOT NULL,
    [Category] [nvarchar](50) NOT NULL,
    [Buying Group] [nvarchar](50) NOT NULL,
    [Primary Contact] [nvarchar](50) NOT NULL,
    [Postal Code] [nvarchar](10) NOT NULL,
    [Valid From] [datetime2](7) NOT NULL,
    [Valid To] [datetime2](7) NOT NULL,
    [CreditLimit] [decimal](18, 2) NULL,
    [PhoneNumber] [nvarchar](20) NULL,
    [FaxNumber] [nvarchar](20) NULL
)
WITH
(
    DISTRIBUTION = REPLICATE,
    CLUSTERED COLUMNSTORE INDEX
)
GO

Trying to apply Mask on column PhoneNumber using below query:

ALTER TABLE  [prd].[CustomerDimensions]
ALTER COLUMN [PhoneNumber] ADD MASKED WITH (FUNCTION = 'default()');

The query gets executed successfully but when i fetch data from the table it shows original data only no masking is applied.

Even applied mask function while creating table but still not able to mask column.

Please help me to figure this out.

It seems that the user has full rights to the database or at least she/he can get unmasked data.

Make sure that this user is not one of the 'SQL users excluded from masking'.

Quoted from docs:

Dynamic data masking permissions
Dynamic data masking can be configured by the Azure SQL Database admin, server admin, or SQL Security Manager roles.

Dynamic data masking policy

SQL users excluded from masking - A set of SQL users or Azure AD identities that get unmasked data in the SQL query results. Users
with administrator privileges are always excluded from masking, and
see the original data without any mask.
Masking rules - A set of rules that define the designated fields to be masked and the masking function that is used. The designated
fields can be defined using a database schema name, table name, and
column name.
Masking functions - A set of methods that control the exposure of data for different scenarios.


IntelliSense not working but it is enabled This problem is really proving to be a tricky one (and quite annoying).

In SQL Server Management Studio 2008, up until a few days ago my IntelliSense was working great.  Then all of a sudden it stopped.  The icon as it as enabled on the toolbar menu, and under Tools -> Options -> Text Editor -> T-SQL -> IntelliSense it says it is enabled there.

I have tried refeshing the IntelliSense cache with Ctrl-Shft-R but that doesn't work either.

Any ideas what happened to my IntelliSense and what I need to do to get it back?

There are number of possibilities where IntelliSense not working:

There are cases where the local cache used by IntelliSense becomes stale. Refreshing the cache is easy but not necessarily obvious.

There are two ways to refresh the cache:

Hit Ctrl+Shift+R
Go to Edit -> IntelliSense -> Refresh Local Cache

If IntelliSense is still not working, then make sure you have IntelliSense enabled. To check this from the T-SQL Query editor window of the current database:

Go to Tools -> Options -> Text Editor -> Transact-SQL -> General -> IntelliSense
Go to Query -> IntelliSense should be selected
Also verify that the T-SQL Editor does not launch in SQLCMD Mode. To check:

Go to Tools -> Options -> Query Execution -> SQL Server -> General
Make sure "By default, open new queries in SQLCMD mode" is unchecked.
or Go to Query ->  SQLCMD Mode should not be selected

Read More


Is it possible to install SQL Server 2014 Enterprise on Windows 7? I'm planning to install SQL Server 2014 Enterprise Edition on a 64-bit HP workstation.

I read the software and hardware requirements on:

https://msdn.microsoft.com/en-us/library/ms143506.aspx

And according to that page, it doesn't seem that Enterprise Edition supports Windows 7. Is the reference right?

Yes, in spite of what that document says, you can install SQL Server 2014 Enterprise Edition on Windows 7. I just did it, and there were no warnings, errors, or blockers of any kind:

  click to enlarge

Now, whether it's supported or not is a different issue (you really should contact your licensing rep), as is whether it makes any sense whatsoever to install Enterprise Edition on a workstation when Developer Edition (which you can typically find for $59 at retail) behaves the exact same way - literally the only differences are (a) what @@VERSION returns and (b) what the EULA states about terms of use.

Note: Developer Edition is now free in SQL Server 2014, you just have to sign up for Visual Studio Dev Essentials (I blogged about this here). SQL Server 2016 Developer Edition will also be free once it's released.

If the issue is that this workstation is going to be in production use, why is it a workstation? You're going to spend thousands and thousands of dollars on SQL Server license, then save a few bucks on a proper server, or spend even less on a workstation operating system? Tread lightly here, because if this configuration isn't supported (or you're using MSDN or grandfathered server/CAL Enterprise licenses and that isn't valid), an audit is going to be very consequential. Please make sure you discuss this with your company's bean-counters and the people who will fire you if this goes south.


Unable to login after db upgrade I upgraded postgresql from 8.4.2 to 12.3. I created a dump of the db from the old version, and restored into the new version.
The port for the new version is 5532. The old one uses 5432. After I restore the dump, I check that the service is running for the new version, and the server is listening on port 5532 for all interfaces.
I even copy the pg_hba.conf entries from the old version to the new version. But instead of "password" I change all the entries to "md5" because this is a newer version. After changes, I reload pg_hba.conf.
However, when we test access to the new db now, it does not work. It fails with error :

Your login attempt was not successful, try again. Reason : Could not
get JDBC Connection; nested exception is
org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for
host "192.168.130.209", user "app_support", database "bosupportdb"

Postgres ports :
[root@reportv2 data]# ss -tulpn | grep postgres
tcp    LISTEN     0      128       *:5432                  *:*                   users:(("postgres",pid=3454,fd=3))
tcp    LISTEN     0      128       *:5532                  *:*                   users:(("postgres",pid=4763,fd=3))
tcp    LISTEN     0      128      :::5432                 :::*                   users:(("postgres",pid=3454,fd=4))
tcp    LISTEN     0      128      :::5532                 :::*                   users:(("postgres",pid=4763,fd=4))
[root@reportv2 data]#

These are my pg_hba.conf entries :
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               md5
# IPv4 local connections:
host   all              all             127.0.0.1/32            md5
host    all             all             192.168.130.0/24        md5
#host   all             all             192.168.8.0/24          md5
host    all             all             192.168.90.8/32         md5
host    all             all             192.168.8.175/32        md5
host    all             all             192.168.112.0/23        md5

Please help me resolve this. I am clueless.

Make sure that the HBA rules that PostgreSQL is actually using are the ones you expect:
select *
from pg_hba_file_rules
where type = 'host'
order by line_number ;

Check the error column for anything amiss with the file syntax.


How to insert default values to a table with not null columns? I have several tables with different number of columns, each can be either real or int, both not null (I can define them all real if required).

I would like to write a query that inserts a row with all zeros.

INSERT [TablesName] DEFAULT VALUES works good for nullable columns.

I can't use INSERT INTO table1 (field1, field2) VALUES (0, 0.0); as I don't know the number of columns.

Is there a query that would insert a new row with default values for not null tables?

Put defaults on the columns, so that your DEFAULT VALUES works.

In your table definitions just put DEFAULT 0 after each column definition - should be easy.


What are the pros/cons of Oracle's Grid Control compared to DB Control? Database control is a web based tool for managing a single Oracle database that is installed on the same box as the database itself.  Grid Control is a web based tool for managing multiple Oracle databases that can be installed on a separate server and has its own database.

We are currently using the 10g version of Grid Control and are considering the pros and cons of upgrading to the 11g version of Grid Control vs. abandoning it for DB Control.  As such we would like to quantify what features/benefits we would loose and what features/benefits we would gain with each route.  We are interested in the obvious as well as the obscure/smaller/intrinsic/potential.

In Oracle Grid Control you can make a global view of your entire enterprise.
This is an advantage above DB Control.
It is slightly easier to authorize employees for certain targets in Grid Control.
If your mail gateway changes, you have the advantage of only one location to change.
When making clones, Grid Control has the advantage of not having repository parts in the target databases.

On the other hand,
DB Control is closer to the database and most of the times better support for new features.
Oracle Grid Control Requires quite some resources to run and in many cases at least one dedicated dba to keep it running.
You have no single point of failure for management.
Oracle DB Control is very easy to setup.
With a little smart thinking you can build a central page with links to all DB Consoles that you want, complete with links to the documentation.
Grid Control easily gets mixed up when making clones, even without a local repository in the target.

I think it is a personal choice. At the moment, after having wrestled with many GC installations, I think having an own repository for historic data and links to Oracle DB Consoles is not a bad idea.


Difference between Replication and Erasure coding techniques I hope I am asking in the right community, If not, any suggestion will be appreciated.

I am doing a survey paper where I am doing a comparison between erasure coding and replication techniques. At this stage, I am comparing them regarding specific parameters as below.

The table that I am trying to construct is dealing with parameters that differentiate which technique is better in: storage efficiency, availability, durability, encoding time, latency of failure and cost of reconstruction.

Since replication is faster in terms of read performance when failure occurs, is it correct when I say that replication technique has Higher latency on failure? and the same for encoding time, is it correct to say replication has a high encoding time since it has better performance time in writing?
Does the reconstruction cost of failure in erasure coded system is higher than replication? does it involves more disk I/O ? will it be different if the failure is transient or permanent?
Will it be more informative if I compared all the above parameters according to transient and permanent failures?

is it correct if I compare them as below?

Erasure code: Higher ( Durability, Storage Efficiency, Availability ) and Lower (Encoding time, latency on failure, cost to reconstruct)

Replication : * Higher ( Encoding time, latency on failure, cost to reconstruct) and Lower ( Durability, Storage Efficiency, Availability)

Erasure coding, commonly used in RAID parity stripes, consists of high-availability through computation of parity and striping the resulting data across multiple storage subsystems (or disks).  Replication, more commonly known as mirroring, simply writes multiple copies of the data to more than one storage subsystem or disk.
Erasure coding incurs added latency for all writes because the incoming data must have its parity computed and written to disk.  Reads under a failure mode are going to be slower since the missing data must be reconstructed on-the-fly by comparing the non-missing data against the parity blocks.
Writes under replication are typically not affected as much as under erasure coding since the replicated data can be passed through to the underlying storage without requiring a parity calculation, and is typically written to two or more disks simultaneously in parallel.  Reads of replicated data under a failure mode are typically the same speed as reads under a non-failure mode since the data is read from the non-failed disk in it's entirety without requiring any calculation be performed to "make up" missing data.
Erasure coding typically requires less storage space than replication because the only redundant data is the parity code, whereas in replication all the data is duplicated.
Arguably the most commonly storage redundancy scheme used in SQL Server storage systems is RAID-10, which uses a combination of RAID-1 (replication or mirroring in the more common vernacular), and RAID-0 striping without parity.  A typical RAID-10 array might consist of 8 disks configured into 4 mirroring pairs, where data is striped across the 4 pairs of disks.  This enables both fast reads, and fast writes, while maintaining redundancy.  More than a single disk can fail without data loss as long as any two disks in a single mirroring pair don't fail at the same time.

Logical Disk:            C:\
                          |
Striping:    ============================
             |        |        |        |
Mirroring: =====    =====    =====    =====
           D   D    D   D    D   D    D   D
           i   i    i   i    i   i    i   i
           s   s    s   s    s   s    s   s
           k   k    k   k    k   k    k   k
           1   2    3   4    5   6    7   8

In Raid 10, up to 4 disk failures could occur in the above setup as long as no two failures occur in the same mirror pair.  With RAID-50, using erasure coding at the striping level, up to 5 disks can fail as long as only a single mirror pair has two disks fail simultaneously.  For instance, Disk1, Disk2, Disk3, Disk5, and Disk8 could fail simultaneously without data-loss.
To answer your question, any I/O that incurs extra operations over-and-above writing the data to a single disk in a non-redundant manner is going to add apparent latency to the system.  Whether one system is slower or faster than another depends greatly upon the actual implementation, with the optimizations made by each manufacturer varying greatly with target audience.
It's likely safe to say that parity (erasure coding) is slower, requires more I/O, and has a lower cost-per-GB than replication.  Replication is likely faster to rebuild, has lower write-latency, and costs more per GB.  Again, these assumptions are broad generalizations and depend on implementation details that vary by vendor and target system.


Creating two sub queries using WITH clause I am struggling with really complex queries involving two sub-queries using WITH clause as described here.

But it appears to me I cannot use one sub-query inside my main query. I here is my query statement:

WITH trip_labelled AS
(
    SELECT DISTINCT ON (trips_1.trip_id) trips_1.trip_id,
            sum(segments.length::double precision) AS segments_length,
            sum(travelmode_profile.foot * segments.length)::real / GREATEST(sum(segments.length), 1::real) AS foot,
            sum(travelmode_profile.bike * segments.length)::real / GREATEST(sum(segments.length), 1::real) AS bike,
            sum(travelmode_profile.car * segments.length)::real / GREATEST(sum(segments.length), 1::real) AS car,
            sum(travelmode_profile.bus * segments.length)::real / GREATEST(sum(segments.length), 1::real) AS bus,
            sum(travelmode_profile.metro * segments.length)::real / GREATEST(sum(segments.length), 1::real) AS metro
           FROM trips trips_1
             JOIN segments ON segments.session_id = ANY (trips_1.session_ids)
             JOIN travelmode_profile USING (session_id, segment_id)
             LEFT JOIN session USING (session_id)
             LEFT JOIN ( SELECT DISTINCT ON (session_1.user_id) session_1.user_id,
                    response.response AS role
                   FROM session session_1
                     LEFT JOIN response USING (session_id)
                  WHERE response.question_id = 24) demography USING (user_id)
          WHERE segments.movement = true
          GROUP BY trips_1.trip_id
),
   trip_mode AS
(   SELECT trips.trip_id AS trip_id,
       trips.seconds_start,
       trips.seconds_end,
       trips.distance,
         CASE
            WHEN GREATEST(trip_labelled.car, trip_labelled.bike, trip_labelled.bus, trip_labelled.metro, trip_labelled.foot) = trip_labelled.car THEN 'car'::text
            WHEN GREATEST(trip_labelled.car, trip_labelled.bike, trip_labelled.bus, trip_labelled.metro, trip_labelled.foot) = trip_labelled.bike THEN 'bike'::text
            WHEN GREATEST(trip_labelled.car, trip_labelled.bike, trip_labelled.bus, trip_labelled.metro, trip_labelled.foot) = trip_labelled.bus THEN 'bus'::text
            WHEN GREATEST(trip_labelled.car, trip_labelled.bike, trip_labelled.bus, trip_labelled.metro, trip_labelled.foot) = trip_labelled.metro THEN 'metro'::text
            WHEN GREATEST(trip_labelled.car, trip_labelled.bike, trip_labelled.bus, trip_labelled.metro, trip_labelled.foot) = trip_labelled.foot THEN 'foot'::text
            ELSE NULL::text
        END AS travel_mode
   FROM trips
     JOIN trip_labelled USING (trip_id)
  )
  SELECT  session_id,
         COALESCE(gps.gpstime, gslocation.gpstime) AS gpstime,
         COALESCE(gps.lat, gslocation.lat) AS lat,
        COALESCE(gps.lon, gslocation.lon) AS lon,
        COALESCE(gps.alt, gslocation.alt) AS alt,
        COALESCE(gps.speed, gslocation.speed) AS speed,
        COALESCE(gps.acc, gslocation.acc) AS acc
        trip_mode.travel_mode
FROM raw_data.gslocation
FULL JOIN raw_data.gps
    USING (session_id, seconds, millis);

Error:

ERROR:  syntax error at or near "trip_mode"
LINE 53:   trip_mode.travel_mode

I cannot use travel_mode or trip_mode.travel_mode from second sub-query trip_mode in main query. But works on commenting out travel_mode/trip_mode.travel_mode. Where do I go wrong?

There are two problems with your query:

You are missing a comma before trip_mode.travel_mode.
If you want to reference the CTEs in the main query, you have to add them to the FROM clause.


mongodump failing to connection replica set I'm able to connect to secondary node from arbiter node of the replica set.
mongo -u backup02 -p backup02_password --port 8133 --host p1dummy:8133,p2dummy:8133,drdummy:8133 --authenticationDatabase admin --ssl --sslAllowInvalidCertificates

mongo8133:SECONDARY>

when I try to connect the secondary node form arbiter node using mongodump command which is not happening . with mongodump command I'm passing the same parameter as mongo command.
I tried enabling verbose log but that's not giving me the complete log information.
I'm able to connect specifying p1dummy as the parameter however I should also be able to connect using connection string like I used for mongo for host argument:
/usr/bin/mongodump -u backup02 -p backup02_password --port 8133 --host p1dummy:8133,p2dummy:8133,drdummy:8133 --authenticationDatabase admin --out /tmp --db mydbname --ssl --sslAllowInvalidCertificates --excludeCollectionsWithPrefix=system --verbose=5

2021-04-01T12:48:43.899+0000    will listen for SIGTERM, SIGINT, and SIGKILL
2021-04-01T12:48:47.400+0000    Failed: error connecting to db server: no reachable servers

Try to put "" round --host string... And you don't need --port parameter, because your --host string have port information.
/usr/bin/mongodump -u backup02 -p backup02_password --host "p1dummy:8133,p2dummy:8133,drdummy:8133" --authenticationDatabase admin --out /tmp --db mydbname --ssl --sslAllowInvalidCertificates --excludeCollectionsWithPrefix=system --verbose=5


Upgrade MongoDB Community to Enterprise I have a server using MongoDB Community but my client wants additional features that are only able using the Enterprise version.

We're about to buy it, however, I was wondering what should I do to upgrade from Community to Enteprise, and guarantee that all additional features are available.

Is there a license file to add? Should I install from scratch and restore backup? How should I proceed?

Thanks,

Vinicius

Just remove community version (packets) and install enterprise version. Simple, nothing else needed. All those enterprise version features have been compiled into.

If you have replica set, you can do this without down time, just do change one (secondary) node at time.


What's the fastest way to execute a MySQL Dump of a large Database? I currently have a rather large database that I create dumps from the our websites Live Server to my Test Server (and vice versa).

Creating the dump is fairly quick (takes about 30 seconds). However, executing the dump file on my Test Server (or Live Server) in order to setup a copy of the dumped database takes AGES (roughly 2-4 hours).

Is there anyway to make this faster? I currently generate the MySQL Database Dumps using Navicat, and then execute them again using Navicat.

Set up the OS with LVM Snapshots.  Preferably have MySQL in a separate partition or drive that is to be snapshotted.  Then to take a dump:

Stop mysqld
take snapshot (a minute or so, regardless of dataset size)
start mysqld

Copy (suggest rsync) the snapshot to the other machine (this takes time) into an identical snapshot area.

start mysqld

(Caveat:  I have never tried this, so there could be some rough edges.)


Query that was working on MySQL is not working on PostgreSQL I had the following query (with bin_number being a string) which was working on MySQL 5.5:

Postgres - was working on MySQL and doesn't work on PostgreSQL

SELECT "menu_items".*
FROM "menu_items"
WHERE ((header like '%red%'
         or detail like '%red%'
         or bin_number like '%red%')
  and location_id='37'
  and is_deleted=false and bin_number<>'')
ORDER BY bin_number asc;
> 0 results   # should be 17

Postgres - works so issues with and bin_number<>''

SELECT "menu_items".*
FROM "menu_items"
WHERE ((header like '%red%'
        or detail like '%red%'
        or bin_number like '%red%')
and location_id='37'
and is_deleted=false)
ORDER BY bin_number asc;
> 17 results

I tried changing bin_number<>'' to bin_number!='' but that didn't make a difference.
Why is this not working?

edit 1

The bin_number in menu_items is declared as:

  bin_number | character varying(255) |  | extended |   |

My educated guess (while proper information is missing): You declared bin_number as character(n), char(n) or just char data type.

character(n) is an outdated, inefficient, weird data type, but defined in the SQL standard. char (without double quotes!) or char(1) are aliases for character(1).

Typically you do not want to use it. It's a blank-padded data type where all strings containing only blanks or nothing (i.e. the empty string '') are considered identical. Effectively all of these are treated as empty strings because trailing blanks are considered insignificant.

SELECT '  '       <> '';  -- TRUE
SELECT '  '::text <> '';  -- TRUE (identical, because text is default
SELECT '  '::char <> '';  -- FALSE (!!)

Use varchar or text instead.

SQL Fiddle with a matrix of comparisons.

More:

Compare varchar with char
Any downsides of using data type “text” for storing strings?
Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

It's either that, or you have NULL values in the column where you had empty strings in MySQL. Either way, the table definition (\d tbl in psql) in the question and some sample values would allow answers instead of speculation.


query a JSON type column in Postgres I have a table in Postgres and trying to query a JSON type columns data_meta & source_data
request_id               | bbb1-bdfa3ae765a2
job_type                 | Incoming
data_meta                | {"data_type": "Insurance", "technology": "XYZ", "timestamp_column_name": "JOBTIME"}
source_data              | [{"vendor": "ABCD", "name": "MAPPING_FILE", "data_type": "Insurance"}]
status                   | OK

SELECT * FROM   job_request_table p, json_array_elements(p.data_meta) as element
where element->'data_type' ='Insurance';

and getting this:
ERROR:  operator does not exist: json = unknown
LINE 2: where element->'data_type' ='Insurance';
                                   ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

will there be a change in query if I have to search for vendor='ABCD' in another JSON type column "source_data"

You need to extract the value as text which is done using the ->> operator (-> returns a json value).
SELECT *
FROM job_request_table p
  cross join json_array_elements(p.data_meta) as d(element)
where d.element ->> 'data_type' = 'Insurance';

However, given your sample data, this would result in the error "cannot extract elements from an object" as the content of data_meta is not an array.
If you want to test if a JSON value contains a specific key/value pair, use @> - but that can only be used with jsonb, so you need to cast the column:
SELECT *
FROM job_request_table p
where data_meta::jsonb @> '{"data_type": "Insurance"}';

You can use this also to check inside the array in source_data (but again a cast to jsonb is neeed):
where source_data::jsonb @> '[{"vendor": "ABC"}]'

In the long run, I would recommend to change both columns to jsonb


Get lines of pg_hba.conf with trust method from user with minimal privileges I try on PostgreSQL 9.3 to get the lines of pg_hba.conf where the authentication method is trust from user with minimal privileges. I wrote a function, but it doesn't work:

create function public.get_pghba_with_trust()
  returns TABLE (
    TYPE            text
  , DATABASE        text
  , PGUSER            text
  , ADDRESS         text
  , METHOD          text)
as
$$
  SELECT *
  FROM regexp_split_to_table(pg_read_file('pg_hba.conf'),'\n') t(a)
  WHERE a ~ '^[^#].*trust\s*$';
$$
language sql
security definer;

This returns an error:

ERROR:  return type mismatch in function declared to return record
DETAIL:  Final statement returns too few columns.
CONTEXT:  SQL function "get_pghba_with_trust"

Grant execute on function

grant execute on function get_pghba_with_trust() to redcheck;

Try other function

CREATE OR REPLACE FUNCTION get_pghba_with_trust() RETURNS text AS $$
DECLARE
  trust text;
BEGIN
  SELECT * INTO trust from regexp_split_to_table(pg_read_file('pg_hba.conf'),'\n') t(a) WHERE a ~ '^[^#].*trust\s*$';
  IF NOT FOUND THEN
    RETURN 'not used';
  ELSE
    RETURN trust;
  END IF;
END;
$$ LANGUAGE plpgsql security definer;

Grant execute on function

grant execute on function get_pghba_with_trust() to redcheck;

Try run function get_pghba_with_trust

SELECT * FROM get_pghba_with_trust();

Error:

ERROR: must be superuser TO READ files
CONTEXT: SQL STATEMENT "SELECT * from regexp_split_to_table(pg_read_file('pg_hba.conf'),'\n') t(a) WHERE a ~ '^[^#].*trust\s*$'" PL/pgSQL FUNCTION get_pghba_with_trust() line 5 AT SQL STATEMENT

How get lines of pg_hba.conf on PostgreSQL 9.3 with trust method from user with minimal privileges?

You are nearly there, just have to process the output from pg_read_file() as it returns each line of pg_hba.conf as a single value.  The following query will do the trick:

WITH file (line) AS (
    SELECT regexp_split_to_array(a, '\t+')
      FROM regexp_split_to_table(pg_read_file('pg_hba.conf'), '\n') AS t(a)
     WHERE a ~ '^[^#].*trust\s*$'
)
SELECT line[1] AS type,
       line[2] AS database,
       line[3] AS username,
       CASE WHEN array_length(line, 1) = 4 THEN NULL ELSE line[4] END AS range,
       CASE WHEN array_length(line, 1) = 4 THEN line[4] ELSE line[5] END AS method
  FROM file;

This might not work in all cases that are possible in pg_hba.conf, though - you can tweak this query if you need to.

As you already noticed, pg_read_file() can be run only by superusers.  This is the very reason why you need SECURITY DEFINER on your function.  In order to make it work, you have to create the function as a superuser.  (Note that changing owner after a non-superuser created the function doesn't work.  If this is your case, drop the function first an create it anew, as a superuser this time.)


Management tool for managing MongoDB sharded cluster Is there a database management software for MongoDB that makes it easy to create a cluster, add shards, backup shards and all the complicated stuff. There are some many MongoDB clients but they are suited for single database only, and no advanced functionality like creating and managing a MongoDB sharded cluster.

There is no current software for managing clusters and sharding for MongoDB using a GUI but you can use the likes of mtool's mlaunch from the command line to launch clusters.


What circumstances could a SQL DateTime column hold an invalid date? I have been asked to write a series of tests within SQL.
Many of them are to test if a certain DateTime columns held in the system is a valid date.

Given that the all the fields in question are Datetime column and any changes made to them by end-users are through a GUI front end that already has date validation tests, not to mention that the SQL connector would through up an error if an invalid was passed through anyway.

My question is that under what circumstances could a DateTime column end up holding an invalid date, i.e 20/40/2018.

We are running SQL Server 2008

Kind regards

Matt

SQL Server will not allow an invalid date. Internally, the date part of a DateTime column is just a number/four bytes that represents dates between January 1, 1753, through December 31, 9999.  SQL Server does not know anything about and will not use invalid dates. An attempt to save an invalid date will result in an error.

If you want to confirm this, write some code that attempts to store all possible number combinations for the digit groups in a column.

It is possible, however, to inadvertently store the wrong date, due to SQL Server DateTime supports multiple formats, however that should be handled by your business logic layer. For example, someone may enter 07/06/2018, intending the date to be June 7. In most cases this can be avoided by using ISO 8601 DateTime format when a DateTime needs to be represented as a string.

https://en.wikipedia.org/wiki/ISO_8601


is high cpu a bad thing I had a heated discussion today with some colleagues about high cpu.

We db guys are conditioned to believe that high cpu is a bad thing. I think we spend too much time worrying about cpu and not enough worrying about our real nemesis, those d#mn SANS. When did SANs become more important than a companies data? That's another rant for another day. Back to my cpu question.

I disagree. If the instance is properly tuned and the big three are properly satiated then I don't see anything wrong with a box running at 70%.

Now, PR has weighed in on our discussion.

"up to DBA to know what's right for the system, and constant high cpu is probably a bad sign. I'd be concerned with your scenario but better than constant"

My scenario was a box properly tuned running at 65% with spikes to 90%. You won't hear me say this often but I disagree with Paul on this one.

So, I'd love to hear from some of the more experienced players here.

It depends (as with all interesting questions).  Much of the disagreement on this point tends to come from people talking past each other because they have different concerns, different assumptions, or different time horizons.

On the one hand, a consistently high CPU load is a problem for most systems because it indicates that you likely have relatively little room for growth before CPU becomes your bottleneck and before your CPU run queue starts growing exponentially.  If my system is constantly running at 65% CPU and I'm adding users and functionality every month or expecting that end-of-month/ end-of-quarter/ some other event is going to periodically cause load to increase 20 or 25%, I'm uncomfortably close to exhausting my available CPU and causing my query response times to soar as everyone ends up queued waiting for CPU.  Since adding CPU to a machine is typically difficult-- unless you're running in a virtual environment, it almost always involves buying a brand new server-- an impending CPU bottleneck is generally particularly concerning.

On the other hand, a consistently high CPU load indicates that right now you're probably using your resources effectively.  If you're building a data warehouse, you'd ideally be close to maxing out both CPU and available I/O during your nightly loads, for example, balancing the amount of parallelism you use to get the most efficient loads.  If you assume that tomorrow's load is going to be basically identical to today's load, you'd welcome a high CPU load.  After all, it's not like you can bank your unused CPU cycles for a rainy day.

As with any metric, it's also important to have real benchmarks to compare to.  Unfortunately, lots of shops don't know what their performance profile is when things are running smoothly.  No one calls the DBA to let them know that the system is performing perfectly and under the appropriate amount of load to let her know to capture a baseline.  If you have a real baseline from when the system was performing well under a similar amount of load and that baseline had high CPU consumption, it's safe to assume that your system functions well at relatively high CPU load.

Without those real baselines, though, many DBAs have developed rules of thumb that (often inaccurately) give them some context for what an average system looks like and will compare that average to the current load.  That sort of intuition is better than nothing when you don't have real data to compare against but it often fails when looking at specific systems.  If you look at the body mass index (BMI) of a population, it's pretty good at determining which populations are under- and over-nourished.  If you look at the BMI of specific individuals, however, the metric is much less useful-- professional athletes will almost all be "overweight" or "obese" when looking at BMI despite being in excellent shape.  A DBA's (or a monitoring tool's) rule of thumb is like the BMI in that it's reasonably accurate for a population but much less accurate for an individual.


UPDATE column to MIN of a SELECT result set - what went wrong? I've got the following scenario:

I've got

a table of assets with standard columns
a table of hardware assets with extension columns
a history table where all changes to an asset are stored with asset ID, field name, date, new value
a contact table

I want to set a field in hardware assets called "first_usage" to the earliest date of change of the contact field (called "resource_contact") when the new value was a contact with a user-ID (i.e. no technical user or group).

One difficulty was that no ID is stored in the "new value" but  a combined string of first, middle and last name (in case change of contact).

My first try of a query looks like this:

UPDATE  dbo.asset_z_hardware
SET     first_usage = (
        select min(history.com_dt)
from    dbo.nr_com              history,
        dbo.ca_contact          contact
where   history.attr_name = 'resource_contact' AND
        history.com_par_id = own_resource_uuid AND
        contact.userid IS NOT NULL AND
        (history.new_value = '' +
            case when contact.last_name     is not null then        contact.last_name   end +
            case when contact.first_name    is not null then ', ' + contact.first_name  end +
            case when contact.middle_name   is not null then ' '  + contact.middle_name end
        )
)

This seemed to work quite OK, though only some assets were updated with a date (probably I need to check the string-comparison and contacts which changed meanwhile).

But then I realized that I only should update only active assets! The "inactive" flag is in the main asset table called "ca_owned_resource".
Unfortunately, after adding the new condition, the first usage values were all set to the same date!!! How come? I did not find my mistake.

This is the second query with "inactive" flag and link between hardware and main asset:

UPDATE  dbo.asset_z_hardware
SET     first_usage = (
        select min(history.com_dt)
from    dbo.nr_com              history,
        dbo.ca_contact          contact,
        dbo.ca_owned_resource   res
where   history.attr_name = 'resource_contact' AND
        res.inactive = 0 AND res.own_resource_uuid = own_resource_uuid AND
        history.com_par_id = own_resource_uuid AND
        contact.userid IS NOT NULL AND
        (history.new_value = '' +
            case when contact.last_name     is not null then        contact.last_name   end +
            case when contact.first_name    is not null then ', ' + contact.first_name  end +
            case when contact.middle_name   is not null then ' '  + contact.middle_name end
         )
 )

I'd appreciate any help and suggestions for the only-one-date-mistake, also for improving the rest, if any ideas.

Your problem is because it thinks that own_resource_uuid in your sub-query is res.own_resource_uuid, not asset_z_hardware.own_resource_uuid. If you specify it completely, it should be unambiguous and SQL will know what you intended.

Before you introduced res there was nothing else it could be. SQL will assume you mean a column in the local sub-query if there’s one there.


What's the official successor for sp_resetstatus in case of a SUSPECT database? I have a database that has been marked SUSPECT because the volume that both the data and the log file were on suddenly disappeared (an accident - this is a developer workstation). I tried bringing the database back online:

ALTER DATABASE D SET ONLINE

That did nothing. On the web the procedure sp_resetstatus is being recommended but it appears to be obsolete.

I do not wish to run DBCC CHECKDB, at least not right now. The database is very big. What is the official way to bring a SUSPECT database online as quickly as possible?

As of SQL Server 2012, sp_resetstatus is still supported.  However, Paul Randal has some excellent counsel, including do not detach that database.

The first and best answer is to restore a good backup.

If that does not work and you can retrieve the files from somewhere (where?), check out Paul's posts at:

http://www.sqlskills.com/blogs/paul/creating-detaching-re-attaching-and-fixing-a-suspect-database/
http://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-emergency-mode-repair-the-very-very-last-resort/

Since these are detailed discussions, I will not rehash them. But read carefully.


Error Correction: Conversion Failed when converting the nvarchar value 'ALPHANUMERICVALUE' to data type int I am running into this error whenever I run my query.

I believe to fix this issue I have to Format the column which holds those values into a number by itself. I have already tried the Format() and Replace() functions, but they did not work for me. Any other suggestions?

SELECT
   e.accountingcode AS [Vehicle],
   Concat(e.year + ' ', e.make + ' ', e.model + ' ', Cast( e.[Description] AS VARCHAR(80))) AS NAME,
   Iif(cff.stringtype = '', cff1.stringtype, cff.stringtype) AS 'Operator',
   fta.fluid,
   so.odometer,
   CASE
      WHEN
         COALESCE(Lag(e.accountingcode) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, so.odometer), 0) <> e.accountingcode
THEN
   0
ELSE
   Lag(so.odometer) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, so.odometer)
   END
   AS 'Previous Odometer', so.odometer - Iif((
   CASE
      WHEN
         COALESCE(Lag(e.accountingcode) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, so.odometer), 0) <> e.accountingcode
THEN
   0
ELSE
   Lag(so.odometer) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, so.odometer)
   END
) = 0, so.odometer,
   CASE
      WHEN
         COALESCE( Lag(e.accountingcode) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, so.odometer), 0) <> e.accountingcode
THEN
   0
ELSE
   Lag(so.odometer) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, so.odometer)
   END
) AS 'Miles Traveled', sh.hour,
   CASE
      WHEN
         COALESCE(Lag(e.accountingcode) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, sh.hour), 0) <> e.accountingcode
THEN
   0
ELSE
   Lag(sh.hour) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, sh.hour)
   END
   AS 'Previous Hours', sh.hour - Iif((
   CASE
      WHEN
         COALESCE(Lag(e.accountingcode) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, sh.hour), 0) <> e.accountingcode
THEN
   0
ELSE
   Lag(sh.hour) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, sh.hour)
   END
) = 0, sh.hour,
   CASE
      WHEN
         COALESCE(Lag(e.accountingcode) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, sh.hour), 0) <> e.accountingcode
THEN
   0
ELSE
   Lag(sh.hour) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, sh.hour)
   END
) AS 'Hours Used' , fta.fifototalcost / fta.totalgallons AS [Avg Price], fta.totalgallons AS [Total Gallons], fta.fifototalcost AS [Total Cost]

The e.AccountingCode column contains the value that is at error.

The problem seems to be on the Coalesce's.

  Returns the data type of expression with the highest data type
  precedence. If all expressions are nonnullable, the result is typed as
  nonnullable.

Source

For example:

select coalesce('101R',0)

Gives the error:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '101R' to data type int.

This works fine:

select coalesce('101R','0')

Result:

(No column name)
101R

If the lag function on e.accountingcode can't be NULL, then you could use ISNULL(), but you should look into the differnces with coalesce.

  Returns the same type as check_expression. If a literal NULL is
  provided as check_expression, returns the datatype of the
  replacement_value. If a literal NULL is provided as check_expression
  and no replacement_value is provided, returns an int.

Source

Or in other words, if the first value is not a NULL value, return that datatype, if it is NULL and a replacement value, in our case 0 is passed,  then it will be that datatype. If all are NULL then it will be an integer.

Solution #1, changing 0 to '0'

SELECT
   e.accountingcode AS [Vehicle],
   Concat(e.year + ' ', e.make + ' ', e.model + ' ', Cast( e.[Description] AS VARCHAR(80))) AS NAME,
   Iif(cff.stringtype = '', cff1.stringtype, cff.stringtype) AS 'Operator',
   fta.fluid,
   so.odometer,
   CASE
      WHEN
         COALESCE(Lag(e.accountingcode) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, so.odometer), '0') <> e.accountingcode
THEN
   0
ELSE
   Lag(so.odometer) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, so.odometer)
   END
   AS 'Previous Odometer', so.odometer - Iif((
   CASE
      WHEN
         COALESCE(Lag(e.accountingcode) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, so.odometer), '0') <> e.accountingcode
THEN
   0
ELSE
   Lag(so.odometer) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, so.odometer)
   END
) = 0, so.odometer,
   CASE
      WHEN
         COALESCE( Lag(e.accountingcode) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, so.odometer), '0') <> e.accountingcode
THEN
   0
ELSE
   Lag(so.odometer) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, so.odometer)
   END
) AS 'Miles Traveled', sh.hour,
   CASE
      WHEN
         COALESCE(Lag(e.accountingcode) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, sh.hour), '0') <> e.accountingcode
THEN
   0
ELSE
   Lag(sh.hour) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, sh.hour)
   END
   AS 'Previous Hours', sh.hour - Iif((
   CASE
      WHEN
         COALESCE(Lag(e.accountingcode) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, sh.hour), '0') <> e.accountingcode
THEN
   0
ELSE
   Lag(sh.hour) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, sh.hour)
   END
) = 0, sh.hour,
   CASE
      WHEN
         COALESCE(Lag(e.accountingcode) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, sh.hour), '0') <> e.accountingcode
THEN
   0
ELSE
   Lag(sh.hour) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, sh.hour)
   END
) AS 'Hours Used' , fta.fifototalcost / fta.totalgallons AS [Avg Price], fta.totalgallons

Solution #2, changing coalesce to ISNULL()

Educate yourself on the differences with coalesce, for example only do this when the LAG() function on e.accountingcode can't return NULL, which kind of defeats the purpose of the isnull

SELECT
   e.accountingcode AS [Vehicle],
   Concat(e.year + ' ', e.make + ' ', e.model + ' ', Cast( e.[Description] AS VARCHAR(80))) AS NAME,
   Iif(cff.stringtype = '', cff1.stringtype, cff.stringtype) AS 'Operator',
   fta.fluid,
   so.odometer,
   CASE
      WHEN
         ISNULL(Lag(e.accountingcode) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, so.odometer), 0) <> e.accountingcode
THEN
   0
ELSE
   Lag(so.odometer) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, so.odometer)
   END
   AS 'Previous Odometer', so.odometer - Iif((
   CASE
      WHEN
         ISNULL(Lag(e.accountingcode) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, so.odometer), 0) <> e.accountingcode
THEN
   0
ELSE
   Lag(so.odometer) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, so.odometer)
   END
) = 0, so.odometer,
   CASE
      WHEN
         ISNULL( Lag(e.accountingcode) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, so.odometer), 0) <> e.accountingcode
THEN
   0
ELSE
   Lag(so.odometer) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, so.odometer)
   END
) AS 'Miles Traveled', sh.hour,
   CASE
      WHEN
         ISNULL(Lag(e.accountingcode) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, sh.hour), 0) <> e.accountingcode
THEN
   0
ELSE
   Lag(sh.hour) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, sh.hour)
   END
   AS 'Previous Hours', sh.hour - Iif((
   CASE
      WHEN
         ISNULL(Lag(e.accountingcode) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, sh.hour), 0) <> e.accountingcode
THEN
   0
ELSE
   Lag(sh.hour) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, sh.hour)
   END
) = 0, sh.hour,
   CASE
      WHEN
         ISNULL(Lag(e.accountingcode) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, sh.hour), 0) <> e.accountingcode
THEN
   0
ELSE
   Lag(sh.hour) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, sh.hour)
   END
) AS 'Hours Used' , fta.fifototalcost / fta.totalgallons AS [Avg Price], fta.totalgallons


How to use RESTRICT for Foreign Key in mysql? In the database structure of

  CREATE TABLE Country (
  name varchar(40) NOT NULL,
  PRIMARY KEY  (name)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE City (
  name varchar(40) NOT NULL,
  PRIMARY KEY  (name)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE Map (
  country varchar(40) NOT NULL,
  city varchar(100) NOT NULL,
  PRIMARY KEY  (country,city),
  FOREIGN KEY (country) REFERENCES Country (name) ON DELETE CASCADE,
  FOREIGN KEY (city) REFERENCES City (name) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I expect to delete parent from City by leaving the corresponding value in child intact by these three equal commands

  FOREIGN KEY (city) REFERENCES City (name) ON DELETE NO ACTION
  FOREIGN KEY (city) REFERENCES City (name) ON DELETE RESTRICT
  FOREIGN KEY (city) REFERENCES City (name)

But when using NO ACTION OR RESTRICT or omitting ON DELETE. MySQL does not allow me to delete from parent column with this error:

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
('test'.'Map', CONSTRAINT 'Map_ibfk_2' FOREIGN KEY ('city') REFERENCES 'City'('name')
 ON DELETE RESTRICT

Where am I wrong? Isn't it the responsibility of the SQL's NO ACTION to delete the parent and leave the child orphan?

According to the MySQL Documentation on DELETE RESTRICT

  • RESTRICT: Rejects the delete or update operation for the parent table.
  Specifying RESTRICT (or NO ACTION) is the same as omitting the ON
  DELETE or ON UPDATE clause.

As for NO ACTION

  • NO ACTION: A keyword from standard SQL. In MySQL, equivalent to
  RESTRICT. InnoDB rejects the delete or update operation for the parent
  table if there is a related foreign key value in the referenced table.
  Some database systems have deferred checks, and NO ACTION is a
  deferred check. In MySQL, foreign key constraints are checked
  immediately, so NO ACTION is the same as RESTRICT.

DELETE RESTRICT protects the parent from deletion, not the children.


How to difference a column name on the select statement which derive from diferent ancestor tables? I need the name of a doctor and the name of a patient, both derive in the same last child table which is Person, and both have ancestor a Register table, so something like:
         Patient
       /            \
Register
       \                   Person(idPerson, Name)
          Doctor     /

So on the select statment how do I differentiate the Name from Person that stems from Patient versus the one that stems from Doctor?
Query would be something like this, but does not work.:
SELECT
    Person.Name AS DOCTORNAME
,   Person.Name AS PATIENTNAME
FROM
    Register
    INNER JOIN
        Doctor
        ON Register.Doctor_idDoctor = Doctor.idDoctor
    INNER JOIN
        Person
        ON Doctor.Person_idPerson = Person.idPerson
    INNER JOIN
        Patient
        ON Register.Patient_idPatient = Patient.idPatient
    INNER JOIN
        Person
        ON Patient.Person_idPerson = Person.idPerson;

So how do I get the select to make this difference out?

Something along these lines. I need to link to the same table more than once so I need to give unambiguous table aliases. I favor minimal character aliases but you'll be needing to reference the doctor person, DP, or the patient person, PP.
SELECT
    DP.Name AS DOCTORNAME
,   PP.Name AS PATIENTNAME
FROM
    Register AS R
    INNER JOIN
        Doctor AS D
        ON R.Doctor_idDoctor = D.idDoctor
    INNER JOIN
        Person AS DP
        ON D.Person_idPerson = DP.idPerson
    INNER JOIN
        Patient AS P
        ON R.Patient_idPatient = P.idPatient
    INNER JOIN
        Person AS PP
        ON P.Person_idPerson = PP.idPerson;


Deploying dacpac through sqlpackage.exe with NETWORK SERVICE user I have a database release pipeline in Azure DevOps Server which deploys our dacpac files through Powershell commands that run the sqlpackage.exe commands to publish the database. I am using the NETWORK SERVICE user to publish the databases to our DEV and QA environment as I wanted to avoid putting the username and password on the release pipeline. We will be deploying these dacpac files to Production soon and the production server is outside our network. Are there security concerns with using the network service user to deploy the dacpac files on a production environment? What alternatives are there besides putting the username and password in secret variables?

In the situation you describe, I can't think of anything to do other than add the username / password as secret variables in the pipeline (as you described).
An approach I've used to avoid the username and password thing is to have the Azure DevOps agent service run as a minimally-privileged Windows service account (rather than NEWTWORK SERVICE).  You just have to add that Windows account as a login on the SQL Server instance, and grant it the appropriate permissions for creating and deploying databases with SSDT.
This lets you use Windows auth to connect and deploy to the remote server - but only if that server also "knows" the Windows user (which is probably not the case, as you mentioned the prod server is outside your network - which I assume means it's not on the same Active Directory domain).


PostgreSQL query suddenly very slow after new column addition I have a query that's performing terribly after adding a new column. Here's the slow version:

SELECT COUNT(*)
FROM activities
INNER JOIN "users" ON "users"."id" = "activities"."user_id" AND "users"."api_user_for" IS NULL
WHERE "activities"."department_id" = 123456789
AND "users"."api_user_for" IS NULL

The new column is the api_user_for column. It's a nullable string type. The SQL itself is generated from Ruby on Rails. We've added a "default scope", and that's why the api_user_for seems to appear twice in the query (on the join and in the where clause).

If I remove either one of those api_user_for checks, the query returns to its former speed. By including them both, however, the query moves from taking less than 100ms to taking close to 10 seconds.

I've compared the query plans using PEV, and here's the fast and slow queries compared:

The "fast query" in this case is the same query with one of the (unnecessarily duplicated) api_user_for checks removed. For example, both of these queries are fast:

SELECT COUNT(*)
FROM activities
INNER JOIN "users" ON "users"."id" = "activities"."user_id"
WHERE "activities"."department_id" = 123456789
AND "users"."api_user_for" IS NULL

and

SELECT COUNT(*)
FROM activities
INNER JOIN "users" ON "users"."id" = "activities"."user_id" AND "users"."api_user_for" IS NULL
WHERE "activities"."department_id" = 123456789

Obviously there's a huge disparity in the number of loops performed, but why are these loops taking place? The scan seems otherwise very similar.

I would love some insight into what could be going on as this fairly innocuous query is now bringing performance to its knees!

Postgres Version: 9.6.8

It looks like we needed to run ANALYZE users!

I had run ANALYZE activities, but that wasn't the problematic table. I guess the query planner thought that the api_user_for column would be instructive to help it remove rows, but the column was almost entirely filled with NULL values, so it would not have helped much at all.

Obviously running a simple ANALYZE would also have solved the problem.


How to exporting a databse structure (or many tables) for import In TSQL: When right click a table you have the option to Script table as > Create

which creates the table script including the constraints
If you want to port the database or  large portion of the table:

is it possible to have a script create the tables in 1 file and the  FK constraints in another file. The 2 files can then be used to recreate the tables and relationships in another DB

You can use the Generate Script feature to output what you need.

Right click on the database and select Task -> "Generate Scripts..."
Choose "Select specific database objects" and check the "Tables" box to script out all tables (and any other necessary objects, like schemas or database roles)
On the next dialogue, click the "Advanced" button and ensure that "Script Foreign Keys" is set to True.
Finish the wizard and output your script to a new window, clipboard, or file.

This will automatically order the object creation in the proper order so that you can run the entire script once to generate all tables/constraints/views/etc. that you need (tables, then constraints, then foreign keys, then extended properties, and so on).


Is it possible to take the table level backup in mysql using any of the script? I am new to MySQL and I am in need of the script which is taking backup of  multiple tables from the multiple databases in single file in MySQL?

To dump only specific tables from a database, name them on the command line following the database name:

mysqldump my_database table1 table3 table5 > dump.sql

To dump only specific tables from multiple database, use mysqldump again, but append to the end of the previously created file:

mysqldump my_database2 table1 table3 table5 >> dump.sql Note the >> sign

Reference:

https://dev.mysql.com/doc/refman/5.7/en/mysqldump-sql-format.html
https://stackoverflow.com/questions/8228116/mysqldump-tables-from-different-databases


Ways to stop access to a SQL express database I have a SQL express database and was wondering what options I have for securing it?

More specifically it is a replicated database using merge replication. I want to stop a user with a replicated copy of the database opening the database, modifying it and running queries against it.

UPDATE:

Just wondering though, what happens if I have a database which is secured, but I then take it offline, copy the database, and attach it to a different database server. Does the security go with it, and can I not break into it like that?

There is no way you can secure a SQL Express database against access from a computer administrator. In the typical SQL Express scenario this means that the user of your application (which is usually a also local administrator) can see everything in the database and it can also modify anything, no matter what grants or permissions you establish.

If you want to protect the IP in the database (which is the usual reasoning behind this ever repeating question) you need to prevent access to the physical database which you can only do by no releasing the database to start with. In other words, use a web service instead (hosted by you), or a cloud based database like SQL Azure.


Table with relation to two others (three-way relation?) I stumbled across a weird situation that one table has relation to two others but record in it can only have a relation to one of the tables. To clarify things, I have tables: attribute, visit, visit_action. visit is a parent of visit_action - on one visit a user can perform multiple actions. Both visits and actions have attributes that are made of name and value. One attribute can be related either to visit or visit_action but never to both at the same time. One visit can contain of multiple attributes and so does action. How should I keep those relations in database?

Option 1
Create two connecting tables like visit_attribute and visit_action_attribute both with relations to attribute but one with relation to visit and one to visit_action. The problem I have with this solution is that a attribute can end up being tied up to both visit and visit_action at the same time and I do not want that.

Option 2
Create columns with id_visit and id_visit_action directly in attribute table. It seems like a good fit because attribute can't be related to many visits or actions but there is still problem that it can end up being tied up to one visit and one action at the same time. It feels bad that too one column in attribute table will always be NULL (either id_visit or id_visit_action).

There is a third option:

Create tables visit_attribute and visit_action_attribute with name and value columns in each (and drop the attribute table altogether).

This removes the possibility of a given attribute being linked to both a visit and a visit_action, with no need for id columns with NULL values.


Run a script as a sheduled job using sql developer scheduler GUI I have a script

Alter Trigger  TEST.LIVE_DELETE Disable;
Alter Trigger  TEST.LIVE_UPDATE Disable;

----------Running the comparison script----------------

var scanid number;
SET SERVEROUTPUT ON
DECLARE
consistent   BOOLEAN;
scan_info    DBMS_COMPARISON.COMPARISON_TYPE;
BEGIN
consistent := DBMS_COMPARISON.COMPARE(
           comparison_name => 'COMPARE_TEST',
           scan_info       => scan_info,
           perform_row_dif => TRUE);
:scanid:=scan_info.scan_id;
DBMS_OUTPUT.PUT_LINE('Scan ID: '||scan_info.scan_id);
IF consistent=TRUE THEN
  DBMS_OUTPUT.PUT_LINE('No differences were found.');
ELSE
  DBMS_OUTPUT.PUT_LINE('Differences were found.');
END IF;
END;
/
COLUMN OWNER HEADING 'Comparison Owner' FORMAT A16
COLUMN COMPARISON_NAME HEADING 'Comparison Name' FORMAT A20
COLUMN SCHEMA_NAME HEADING 'Schema Name' FORMAT A11
COLUMN OBJECT_NAME HEADING 'Object Name' FORMAT A11
COLUMN CURRENT_DIF_COUNT HEADING 'Differences' FORMAT 9999999
SELECT c.OWNER,
c.COMPARISON_NAME,
c.SCHEMA_NAME,
c.OBJECT_NAME,
s.CURRENT_DIF_COUNT
FROM DBA_COMPARISON c, DBA_COMPARISON_SCAN_SUMMARY s
WHERE c.COMPARISON_NAME = s.COMPARISON_NAME AND
c.OWNER           = s.OWNER AND
s.SCAN_ID         = :scanid;
SET SERVEROUTPUT ON
DECLARE
scan_info    DBMS_COMPARISON.COMPARISON_TYPE;
BEGIN
DBMS_COMPARISON.CONVERGE(
  comparison_name  => 'COMPARE_TEST',
  scan_id          => :scanid,
  scan_info        => scan_info,
  converge_options => DBMS_COMPARISON.CMP_CONVERGE_REMOTE_WINS);
DBMS_OUTPUT.PUT_LINE('Local Rows Merged: '||scan_info.loc_rows_merged);
DBMS_OUTPUT.PUT_LINE('Remote Rows Merged: '||scan_info.rmt_rows_merged);
DBMS_OUTPUT.PUT_LINE('Local Rows Deleted: '||scan_info.loc_rows_deleted);
DBMS_OUTPUT.PUT_LINE('Remote Rows Deleted: '||scan_info.rmt_rows_deleted);
END;
/

--------------End Of Comparison Script-----------------

----------------Enable the Triggers--------------------

Alter Trigger  TEST.LIVE_DELETE enable;
Alter Trigger  TEST.LIVE_UPDATE enable;

----------------END OF SCRIPT--------------------------

I am trying to set this script as a repeated job.
Using SQL Developer>Scheduler>New Job (Wizard)

I get the below error,
"ORA-06550: line 1, column 754:
PLS-00103: Encountered the symbol "ALTER" when expecting one of the following:

( begin case declare exit for goto if loop mod null pragma
   raise return select update while with
     <<
   continue close current delete fetch lock insert open rollback
   savepoint set sql execute commit forall merge pipe purge
"
Is there a better way to do this or fix this!

You can not issue DDL commands (including ALTER) directly in PL/SQL. Use execute immediate instead.

execute immediate 'Alter Trigger  TEST.LIVE_DELETE Disable';

Replace DBMS_OUTPUT.PUT_LINE calls with logging to a table, as the output of the former will not appear anywhere in case of a scheduled job.


Centralize Oracle RMAN scripts in one server I want to centralize all my Oracle's DB backup shell scripts in one server, and all of them are in different servers, architectures and DB versions. For this purpose I'm going to use the same server where the Recovery Catalog is installed, a rhel6 64bit with 11.2.0.3 DB, and schedule the backup shell scripts via crontab.

This is easily done executing, from the Recovery Catalog server, something like RMAN TARGET SYS@RemoteDBNetIdentifier CATALOG catalogowner@rmanCatalog

Problem is that, according with the compatibility matrix, RMAN client version must match DB target connected version. http://docs.oracle.com/cd/E11882_01/backup.112/e10643/compat003.htm#i634479

$ rman target sys@db_prod_1 catalog rman@rcat

Recovery Manager : Release 11.2.0.3.0 - Production on Jue Ago 22 13:27:52 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Contraseña de la base de datos destino:
el paquete PL/SQL SYS.DBMS_BACKUP_RESTORE versión 11.02.00.01 de la base de datos TARGET no es actual
el paquete PL/SQL SYS.DBMS_RCVMAN versión 11.02.00.01 de la base de datos TARGET no es actual
conectado a la base de datos destino: DB_PROD_1 (DBID=943768957)
Contraseña de la base de datos del catálogo de recuperación:
conectado a la base de datos del catálogo de recuperación

RMAN> exit

But I don't want to install every single Oracle DB version in the recovery catalog server.
So I tried to copy JUST the rman executable from the Oracle DB installations in the others servers to the Recovery Catalog server, but it didn't worked

$ scp oracle@SRV_PROD_1:/usr/oracle/product/11.2.0/bin/rman /usr/oracle/product/rman_bin/11.2.0.1/
rman                                    100%   14MB 209.6KB/s   01:06

$ cd product/rman_bin/11.2.0.1/
$ ./rman target sys@db_prod_1 catalog rman@rcat

Recovery Manager : Release 11.2.0.1.0 - Production on Jue Ago 22 13:51:27 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: fallo al inicializar el paquete del gestor de recuperación interno
RMAN-03000: fallo al inicializar el componente compilador del gestor de recuperación
RMAN-06035: versión incorrecta de recover.bsq; se esperaba 11.2.0.1 y se ha encontrado 11.2.0.3

It seems that I'd also need the recover.bsq file (which is in $ORACLE_HOME/ADMIN), but I want to be sure that I wont miss more files or face new problems when we'll have to perform a disaster recovery or something in the future.

What can I do?

How can I only install the different versions of RMAN client?
There's another method to centralize all the backups work (I think that EM Cloud Control can achieve this, I didn't read about it, just figuring out, but we dont want to rely on a GUI web-based application to manage our backup strategy.)

Regards

Forget copying binaries around. I would integrate remote RMAN executions into your normal backup application/solution (i.e. filesystem backup to fire a script) for the sake of consistency. Especially scheduling of backups would be easier.

Re: "In this case parameterfile.par must exist in SRV_PROD_1". Not a problem if you dig into RMAN's own CREATE SCRIPT syntax - you can store the run{ } script inside your catalog.

Re: "rman.log will be created also on SRV_PROD_1". Not a problem if you take a look at catalog schema contests - surely there is one large table per each catalog that holds the logs anyway (I can't remember if it's ROUT or RMAN_OUT?).


Online index operation blocking inserts I am trying to create a non clustered index on a table which is around 500GB.  The table already has a clustered index on it. Since we do not want other transaction to be blocked we tried using ONLINE=ON, however at the beginning of the index build it blocked other OLTP transactions.

When I checked what locks were being held I noticed exclusive table lock on the table and other transactions are getting blocked by this.

Screen shot:

I look at Guidelines for Online Index Operations for exclusions and its not applicable for this table.

Any idea why this is happening?

Rebuilding an index online doesn't allow you to completely avoid locking - when the process completes the table is locked for a brief period and the new index replaces the old one.

Online index operations need to take two very short-term table locks. Shared)table lock at the start of the operation to force all write plans that could touch the index to recompile, and a SCH-M (Schema-Modification) table lock at the end of operation to force all read and write plans that could touch the index to recompile.

And the article you provided has some info on this as well: