Unzip files within the SQL Server I need to unzip some files. I can do it with xp_cmdshell using 7z (since it is free) but I need this procedure to be able to run on multiple machined and I cannot guarantee that all of them will have 7z. Can I include the installation of 7z into the SQL Server?

Generally, running compression/decompression on the SQL Server itself is a bad idea.

SQL Server is licensed by the CPU core, so it's the most expensive place in the shop to do CPU-intensive things like compression & decompression. Instead, consider offloading that to an application server.

Now, having said that: SQL Server doesn't support the installation of programs and doesn't have a way to bundle 3rd party apps with the install. You'll want to manage the installation of other apps with a software deployment product or an installer script.


Is there a tool to check if my database is normalized to the third normal form? I learned about normalization recently, and understand how important it is when implementing a new schema.

How can I check if my database is 2NF or 3NF compliant ?

Manual review is a sure option, but I'm looking for an automated tool here.

I'm not looking for a point-and-click tool, more something that would highlight possible optimizations to make a table 3NF compliant. I guess it might use statistics based on good sample data and/or semantic analysis of columns names.

Normalization absolutely is used in the real world... and hopefully you know that 3NF is only the third one of... what is is now, 8?  But 3NF should be an easy target.

However... I would venture to say that there could not be such a tool.

Normalization, technically, is an attribute of each table.  Within a given database, different tables may have different levels of normalization.

Each table represents facts... facts about instances of a certain type of thing (person, account, order, shipment, item, location) including, sometimes, foreign keys which lead you to other kinds of facts about that thing.

Normalization has to do with how accurately and efficiently facts are represented in the tables as well as the ability of table's design to prevent ambiguous and redundant data patterns.

Thus, an understanding of the actual facts is required... which is outside the scope of automated tools.

Q: Is a table with { student, subject, instructor } in 3NF?
A: What are students, subjects and instructors?

In a world where all instructors taught all subjects and each student could take any combination but not more than one course on each subject from each instructor, this table could indeed be said to be in 3NF.  In the real world, making the claim of 3NF for this table is absurd.

To understand that is isn't in 3NF requires an understanding of the nature of the facts it represents.  In our reality, this table is not going to be 3NF since (among other reasons) the subject and the instructor are associated together in ways that have nothing to do with the student.  If we have the courses where instructors teach subjects stored elsewhere in our database, why would we copy both values here instead of a foreign key from the other table indicating that the student was signed up for the course?  If the instructor is replaced, we have to change multiple records in multiple places.

The more normalized a database is, the more intrinsically consistent it is with the real world and with itself, and the more difficult it is for the database's facts to be inadvertently untrue.  Database design is an art, but it is most definitely a science as well.

Even though I do not see eye-to-eye with everything he writes, I would recommend Chris Date's book, Database Design and Relational Theory: Normal Forms and All That Jazz which goes into excruciating detail about the underlying theory of the relational model.


Accurate PG database size for comparing representations I have a Java/JDBC program that takes a sample file and imports the data into the database, shredding it across multiple relations. The program does this multiple times for several different representations, one of which uses Large Objects. I can share more details of these representations but they are quite long and aren't relevant to this question since I'm looking for something generic.

I would like to compare the sizes of these different representations by examining the size of the database after each import. The database is on a PostgreSQL 9.4 local Windows server instance, with no other users and default configuration. Its only purpose is to conduct this test.

My initial plan was as follows:

for each representation {
  call VACUUM ANALYZE
  record old DB size with SELECT pg_tablespace_size('pg_default');
  import data into database
  call VACUUM ANALYZE
  record new DB size with SELECT pg_tablespace_size('pg_default');
  store storage cost as new DB size - old DB size
}

Obviously there are limitations to this approach, but my expectation is that for large files (~100MB) the reported storage costs should be reasonable approximations. Note that I use pg_tablespace_size in order to include the contribution of data outside of the main schema, such as large objects (in pg_catalog.pg_largeobject and pg_catalog.pg_largeobject_metadata).

I'm wondering whether this is a correct approach, and whether there is a better approach. I'm unsure whether VACUUM ANALYZE properly updates the stats used by pg_tablespace_size, even though it is called in the same session. It would also be better if I could avoid calling VACUUM ANALYZE, since this requires connecting as the superuser in order to run on the pg_catalog relations.

Any thoughts?

Just call pg_database_size(dbname) to know the size of the database.

VACUUM (without the FULL clause) does not free any space, it only marks it as reusable, and thus will not change the database's size (except in a rare boundary case, see Routine Vacuuming).

ANALYZE does statistical sampling and would be useful if you needed the row counts, but for the global db size, it's not necessary.


Track price changes on a daily basis I am trying to track price changes on a daily basis. I have 2 tables Stores and Prices.

Table Stores columns:

s_id
company
address
city
state
zip
phone

Table Prices columns:

ref
storeid
price
mgr
datetime

NOTE: storeid in Prices is a reference to s_id in Stores

What I would like to do is run a query to find all prices from yesterday and compare them to today to see if they have changed. Please understand that I am filing in for the database admin for any advice you can offer.

Would be something like:

select  p1.ref, p1.storeid, p1.price, p2.price
from    prices p1
join prices p2 on p1.ref = p2.ref
where   trunc(p1.datetime) = trunc(sysdate)
and     trunc(p2.datetime) = trunc(sysdate-1)
and     p1.price <> p2.price;

You can add Stores to get store information if you need. I do not know which DB you use so I gave a solution for Oracle. The trunc function gives you the date part of a date.


Should I create a separate table for geo-location points? I am developing the backend for a service that resembles Uber in some aspects (just a simplification to give you the general context). Users will book rides and as such users will define the pickup and drop-off locations. These locations will be addresses in a city (with the possibility of multiple cities in the future). The locations I will receive from the frontend/app will be in the form of latitude, longitude, and google-maps-verified address.

I am wondering whether I should store the locations as a separate table, or whether I should embed the locations in whatever table I need them. For example, the bookings table will have a pickup location and a dropoff location. Should these be just references to a locations table, or should I add more columns to store the location right there? I will need at least 3 columns per location (lat, long, address string), with the possibility to have more columns if we decide to break the street address into more components.

The way I see it the two approaches trade-off memory and time performance:

A separate table will save some space, as we avoid copying the same
information multiple times. But I am not sure how much space will be
saved as we do not have enough real data yet. My ballpark estimate is that a medium-sized city has tens of thousands unique addresses. So if we take 100 ride bookings there is little chance for overlap. If we have 100,000 ride bookings there is bound to be considerable overlap. However, space is not really a limiting factor in our design, even with millions of bookings. It does not really matter if we spend 100MB more in our database.
A separate table will make processing slower. First of all, each time we get a booking request we will have to search if the locations in the request already exist in our database. If not, we add them, otherwise we use the existing reference. With potentially tens of thousands addresses in our database this will take some time. Is this extra time significant compared to our other time consuming tasks? An example of another time-consuming task is a call to the google-maps api to find routes from one location to another and get a travel time estimate. I believe this operation will take longer that searching in our database. So it might make little sense optimising the database access when we have other bottlenecks. How about other operations? Say we want to find bookings within a bounding [lat, long] box. This can become more time-consuming with a separate table as we'll need a full table join. Or do a cumbersome two-step search where we find the locations IDs in our table that are within the bounding box and then search the bookings table with these IDs. If we have hundreds or thousands locations in a bounding box this might not be a good way to do it. But again, I am not sure about the utility of trying to optimise this operation. I am not sure how often will I need to search by location. It seems not that often, so I will not gain/lose much one way or the other.

It seems to me that there are no strong arguments based on the space-time tradeoff. Are there other aspects I should consider? For example: clarity of design, ease of development, flexibility and expandability. The separate table approach seems to offer more structure and the opportunity for more flexibility if the model needs to change. For example, it might be beneficial to break the street address in multiple components (street, city, state). We are less likely to do this if we are to use location columns in other tables (we need to use at least 3 columns already, and breaking the address in 3 components, brings this number to 5). About flexibility: in the future, we might have multiple-point rides. Having a location table will help with that.

I am leaning towards a separate table, but I do not see strong arguments for or against it. Are there other arguments that I am missing?

Not sure if the following details are relevant but I will be using postgresql as the RDBMS and sqlalchemy as an ORM to query the database.

General recommendation

I am in favor of this:

  whether I should embed the locations in whatever table I need them

Do not create a separate table for storing geo-location points only. Geo location points doesn't really make sense themselves alone.

I would say keep the geo location points to the tables about events. Events such as pickup, drop-off, meet up, dine, and so on...

Albert Einstein once said it's about space-time. It's not about location only (space); also it's not about time only. It should be the binding of space and time. Consider these:

Where will the user be picked up? and when?
Where will the user be dropped off? and when?

There is always a question about when every time you talk about location. One good direction is to always think of it as an event.

Besides, would there be a part of your application where you would pin all the locations (pickups-and-drop-offs) on a single map and homogeneously as if they are all just locations? I think most likely, that will not be the case.

Instead, what you may want is to pin all the pickups, pin all the drop-offs, etc.

Again, for me, it would be better to store the locations with a mindset about events.

Specifically for your purpose, this would require you to hook your bookings table to pickups and dropoffs event tables. With this, you can have as many pickup locations and dropoff locations. Thus, extendable as what you've said.

Alternative

In favor of this:

  whether I should store the locations as a separate table

Unless though if you use the What 3 Words (W3W) service/application. W3W split the world into 3-meter blocks. Each block then was given a unique ID. The ID is in format word1.word2.word3.

With w3w though, you don't need to store geo-location points on a separate table. They already did that for you. All you have to do is use their IDs. I'm not so sure though if 3-meter resolutions are enough for most applications.

Credentials

By the way, I work on staple crop research. We have lots of GIS applications and we also use PostgreSQL.


openssl s_client does not indicate that SQL Server is using TLS, however the connection is secure. How is that possible? I am connecting to SQL Server 2017 using ODBC and MSSMS.  The connection is not using TLS; I checked it using openssl s_client.  However, the DBA assures me that the connection is secure.

What other common or typical methods/protocols could be used in making such a connection secure if not TLS?  The connection is not being made through a SSH tunnel or VPN.

Have the DBA run the following query to see if native SQL Server client-server connection encryption is being used.

SELECT @@SERVERNAME AS 'sql_server', a.host_name AS client_host,
    a.login_name, client_interface_name, b.encrypt_option
FROM sys.dm_exec_sessions a
JOIN sys.dm_exec_connections b ON a.session_id=b.session_id
WHERE login_name = '<login_name>'

If the encrypt_option column is false, then it's not using the native encryption.  At this point, any encryption that is wrapped around the connection would have to be at the networking layers, either Windows IPSec (or something similar), or encryption implemented by the network devices.  So you'll have to ask the DBA exactly how the connection is encrypted in that case.


All My Databases Are Gone? Today I changed my username and password in MySQL and I logged into phpmyadmin to see that all my databases were gone.

I've tried changing the datapath but then I realized I didn't see any of my databases in the data folder either!

Can I get back my databases? Are they stored somewhere else?

Data is present in data folder of MySQL which is usually in ProgramData for windows installations. look into "C:\ProgramData\MySQL\MySQL Server 5.6"..as programdata usually have the files on by default


Are there any problems with deploying an SQL Server database to a production server by taking a backup? This is a slightly loaded question in that I have already assumed that the described scenario is wrong.

A DBA is deploying an application I have written that includes an MS SQL Server 2008 database. He has asked me to take a database backup from my development machine so he can restore it to the production server, thus deploying it. This is a greenfield deployment so there is no existing data to be migrated. I was expecting to provide a DDL script, which I have diligently tested and ensured that it contains everything required. If I execute it in SSMS, the database is created in one click.

To me, using the backup facility for deployment does not seem right, but without being an expert in SQL server I can't think of a solid reason not to do it. I would have thought, for example, that there would be some 'contamination' of the database from the development machine - perhaps the computer name, directory structure or user names stored in there somewhere. Is this the case, or is backup and restore a valid deployment technique?

Everything is wrong with using backup files as deployment. But the burden is not on the DBA to provide the DDL, is on development. Your design and development artifacts should had been database installation and upgrade scripts. Never change anything in the database manually, everything should be modified using your app. Rails gets this in spades with he whole migrations infrastructure and you should try to adopt it too. I have for long advocated using similar techniques, see Version Control and your Database.

First let me make the case why source code based deployment/upgrade is superior to binary based deployment (.bak, or diff tools):

source code can be checked in into source control. This alone should settle the whole argument. Source control gives yo history, a future you can look back and read the check in notes, understand the reasoning behind current state.
source code can be quickly inspected at a glance. You look at it and read it. binary databases require to be attached and require extensive knowledge of the metadata catalogs to read even the basic properties
source code is clean. You see CREATE TABLE Foo (...), which clearly conveys the intent. Binary distribution, if you want to extract the object, showers you in a plethora of default properties. You loose the original intent.
source code can be peer reviewed at check in.
source code integrates in contiguous deployment

And I also have arguments why deploying by backup is bad (very bad):

you only postponed the issue. The very first update of the app will face the issue of deploying an update without loosing the data. This can occur the next day after deployment, when an issue is noticed in production, and you will be left facing the void: how to modify the production DB to match the dev DB?
Databases are not self contained. Deployment invokes objects outside DB (logins, SQL Agent jobs, maintenance plan etc) none of which can be deployed with a backup.
You never know what you deployed. Forgotten tables left over during dev? Test data? Is very difficult to cleanup a database, but keeping your source code up to date and correct is natural.


Unable to install pl/python for Postgresql12 To get the obvious out of the way, my question is posted after reading many other questions like this one
https://stackoverflow.com/questions/60683776/install-pl-python-on-windows-for-postgresql-12
But I ve been stuck for about a month and half now when trying to use pl/python.I'm using Postgresql 12 with the enterprisedb package including pgadmin4 as a gui and the language pack  . So PL python is supposed to be included.When i ran "CREATE EXTENSION plpython3u;"
I got the following message ERROR: ライブラリ"C:/Program Files/PostgreSQL/12/lib/plpython3.dll"をロードできませんでした: The specified module could not be found. SQL state: 58P01which is  the classic "ERROR: could not load library "C:/Program Files/PostgreSQL/12/lib/plpython3.dll": The specified module could not be found."
My understanding, based on reading multiples posts and articles, is that 2 elements are required
1. the proper python interpreter
Python runtime needs to match the PostgresSQL version The documentation within the PostgreSQL folder indicates The procedural languages pl/Perl, pl/Python and pl/Tcl are included in this distribution of PostgreSQL. The server has been built using the LanguagePack community distributions of those language interpreters. To use any of the these languages from within PostgreSQL, download and install the appropriate interpreters and ensure they are included in the PATH variable under which the database server will be started. The versions used are shown below - newer minor (bugfix) releases may also work, but have not been tested:
* Python 3.7

Also when running plpython3.DLL found in C:\program files\postgresql\12\lib through the software "dependency walker" I get the following result :

Which would mean that
-it's indeed python 3.7 which is required as mentioned in the doc.
-the dependency  was found.
2. setting the environment variables PATH and PYTHONHOME
Now both the documentation
https://www.enterprisedb.com/edb-docs/d/edb-postgres-advanced-server/user-guides/language-pack-guide/12/EDB_Postgres_Language_Pack_Guide.1.09.html#
and a forum help administrator
https://postgresrocks.enterprisedb.com/t5/PostgreSQL/unable-to-install-pl-python-extension/td-p/4059/
mentions the needs to change environment variables PATH and PYTHONHOME.
something along the lines of
set PYTHON_HOME=c:\ edb\languagepack\v1\Python-3.7

and set PATH=C:\edb\languagepack\v1\Python-3.7;C:\edb\languagepack\v1\Perl-5.26\bin; C:\edb\languagepack\v1\Tcl-8.6\bin;%PATH%
the forum mentions you need to change those with the command prompt.
the doc isn't specific as to how you re supposed to do that but i figured it meant the python interpreter itself
either way it doesnt work.
when I use the command prompt there is no answer: no error, no nothing. It just  starts new on another line.
in python interpreter, the syntax is invalid.
and when I use the query in pgadmin I have the same error.
I saw other possible methods like a batch file or windows settings  but it seems completely different.
I really want to move forward after being stuck so long and would much appreciate some insight.

Using set at the command prompt does not give feedback upon success.  To verify it was actually set, you would run echo %PYTHON_HOME%.  But it looks like you have a space after c:\ that should not be there.
Also, this only sets the variables for the particular cmd session you run it in, so it works only if you are starting PostgreSQL from that command prompt.  That isn't how you usually run PostgreSQL on Windows, you normally run it as a service.  So you have would have to use the GUI interface to "edit the system environment variables", not use set from the command line.


How to create function(table_name) that returns a set of the table I have a set of tables that share the same fields (some timestamps and users names used for version control). I need to create a function with dynamic sql that uses a table_name and a timestamp as arguments and returns a set of the same table.

The query I need is a bit more complicated, but for my question the following example should be enough.

This is how I'm trying to create the function:

CREATE OR REPLACE FUNCTION "vsr_versioning_at_time"(_t regclass
                                                  , _d timestamp without time zone)
RETURNS SETOF _t AS
$$
BEGIN
    EXECUTE 'SELECT DISTINCT ON (gid) *
        FROM '|| _t ||
        'WHERE vrs_start_time <= '|| _d ||
        'ORDER BY gid, vrs_start_time DESC';
END
$$
LANGUAGE plpgsql;

The creation of this function fails since _t is not recognized by the RETURN SETOF _t.

You cannot use a parameter name (_t) as return type.
Use polymorphic types instead:

CREATE OR REPLACE FUNCTION vsr_versioning_at_time(_t anyelement, _d timestamp)
  RETURNS SETOF anyelement AS
$func$
BEGIN
   RETURN QUERY EXECUTE format('
       SELECT DISTINCT ON (gid) *
       FROM   %s
       WHERE  vrs_start_time <= $1
       ORDER  BY gid, vrs_start_time DESC'
     , pg_typeof(_t)
     )
   USING _d;
END
$func$  LANGUAGE plpgsql;

And use RETURN QUERY EXECUTE to actually return the results.
Call (important!):

SELECT * FROM vsr_versioning_at_time(NULL::mytable, '2014-04-18 12:00');

A lot more details in this related answer on SO (last chapter):
Refactor a PL/pgSQL function to return the output of various SELECT queries


Connecting to SQL Server database from a .net application I need to add a new form to an application which requires access to SQL Server to retrieve data from a single view and display it to the user. This application is an enterprise level application with ~1000 users.

I assume the best practice is to ask DBA to provide a system account with non-expiring password so I can use it to connect to the SQL Server.

Is there any other alternative to using SQL Database account?

How should I write the connection string in my code?

There are different ways to connect to Database.

1. ADO.NET

using System;
using System.Data;
using System.Data.OleDb;

class Sample
{
  public static void Main()
  {
    OleDbConnection nwindConn = new OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");

    OleDbCommand catCMD = nwindConn.CreateCommand();
    catCMD.CommandText = "SELECT CategoryID, CategoryName FROM Categories";

    nwindConn.Open();

    OleDbDataReader myReader = catCMD.ExecuteReader();

    while (myReader.Read())
    {
      Console.WriteLine("\t{0}\t{1}", myReader.GetInt32(0), myReader.GetString(1));
    }

    myReader.Close();
    nwindConn.Close();
  }
}

2. Web.Config Connection String

 <connectionStrings>
    <add name="ConnStringDb1" connectionString="Data Source=localhost;Initial Catalog=YourDataBaseName;Integrated Security=True;" providerName="System.Data.SqlClient" />
  </connectionStrings>

3. Connect to a SQL Database and Use the LINQ to SQL Designer

This process involves multiple thing, you can read more here

https://blogs.msdn.microsoft.com/charlie/2007/11/19/connect-to-a-sql-database-and-use-the-linq-to-sql-designer/


Can SQL Server use multiple indexes for the same query? This question might have been asked before, but I tried searching with this term "can SQL Server use two indexes for same query" and it yielded no results.

Assume you have the below query:

select col1a,colb
from #ab
where col1a in (
Select col1a from #ab
group by col1a
having count (distinct colb)>1)

And you have the below index:

create index nci on #ab(colb)
include(col1a)

This is the execution plan, and Paste The Plan link as well.

Bottom part of plan is for below query:

Select col1a from #ab
group by col1a
having count (distinct colb)>1

Table scan/top part of plan is for below query:

select col1a,colb
from #ab
where col1a in (

Question:

If I have an index like below:

create index nic_toppart on #ab(Col1a,colb)

Will it be chosen for top part of plan?

In summary I meant:

Can SQL Server use index nci for grouping part/bottom part of plan  and use index nic_toppart for top part of the plan

Is this possible?

My tests show it can only choose one.

Below is test data. Please let me know if I am unclear.

create table #ab
(
col1a int,
colb char(2)
)

insert into #ab
values
(1,'a'),
(1,'a'),
(1,'a'),
(2,'b'),
(2,'c'),
(2,'c')

select col1a,colb
from #ab
where col1a in (
Select col1a from #ab
group by col1a
having count (distinct colb)>1)

create index nci on #ab(colb)
include(col1a)

create index nci_p on #ab(col1a,colb)

Yes, but not with the indexes you've chosen.

If I create these indexes:

CREATE INDEX ix_top ON #ab (col1a) INCLUDE (colb);

CREATE INDEX ix_bottom ON #ab (colb, col1a);

I get back this plan:

And that's without having to use multiple index hints.

With a slightly different index:

CREATE INDEX ix_mindyourbusinessypercube ON #ab (col1a, colb);

The plan changes to use just that one index, though performance ramifications are difficult to surmise given the limited test data. I leave that as an exercise to ypercube the reader.


Bi-directional synchronization between local SQL Server 2005 and a SQL Azure using SQL Data Sync I need to synchronize an Sql Server 2005 database in an SQL Azure database. In other posts I've read that I can do this using Sql Data Sync but I don't know if SQL Data Sync can make the synchronizations that I need without exceeding their limitations. This is the exact process I need to do:
First: synchronize 1 table from Azure to Sql Server.
Second: Execute some Store procedures in the SQL Server 2005 instance.
And third: Synchronize various tables from Sql Server to Azure sequentially.

Thanks!

First: synchronize 1 table from Azure to Sql Server. Second: Execute some Store procedures in the SQL Server 2005 instance. And third: Synchronize various tables from Sql Server to Azure sequentially.

You can use the Azure data sync, but its buggy - since I never managed to get it running flawlessly for even a week. It is still in Preview mode with limitations.

Also, Data sync IMHO is suitable for database tables that deal with smaller volumes of changes per sync cycle.

If you dont want real time data sync, then I would suggest you to look into SSIS. Its much more flexible. I still use SSIS to move data from on-premise to Azure.

Alternatively, you can look into Sync Framework - Replicating, Distributing, and Synchronizing Data


Behavior of PostgreSQL COPY FROM Let me preface this question by stating that my experience with DBA is limited to an introductory undergraduate university course, and that I'm just getting started with PostgreSQL.

I would like to programmatically import data from an incredibly large (~4GB) flat CSV (I know, not the best format, but it's what I have to work with) into a PostgreSQL database on a regular basis. I believe I can use COPY FROM to accomplish this task. What occurs when a CSV line that matches or conflicts with an existing record is encountered? Does it update the record, ignore it, or throw an error? Is this behavior changeable?

The documentation states, in part:

  COPY FROM will invoke any triggers and check constraints on the
  destination table. However, it will not invoke rules.

My interpretation of this statement is that rather than updating an existing record, this statement would treat the CSV entry as a new record, which would in turn violate constraints that may be placed on the columns. Is this correct? If so, it seems like my only course of action would be to issue DELETE on the tables before importing the data each time. That strikes me as highly inefficient.

UPDATE

A sample of the data-set that I'm working with may be found here. I would like to import and normalize the data from this highly de-normalized source. As you can see, each license record may be duplicated for each related antenna that may exist.

The COPY FROM will not violate integrity of database.

When a constraint fails, an error is throw, and none of data are copied (it is rolled back).

This behavior is unchangeable. You can do a script in a programming language to help you in a copy.


SQL Server best option for low-cost high-availability and queryable secondary? We have a heavily used database server (SQL 2012 Web edition) which is currently a single point of failure (nightly backups and no additional hardware provisioned!)

The server has a constant stream of data being added and periodic heavy-duty read operations to extract data for applications.

We're going to upgrade to SQL 2017 and then:

1) Split out the reads onto one server and the writes onto another

2) Have a better Disaster recovery situation - i.e. not have to wait a day to provision new hardware and restore backups. It's ok to lose a few minutes of data if we have to.

I've spent a while reading up on options and it seems the Always On High Availability groups are the best option.... however they require the Enterprise edition of SQL server (which for two 8 core servers is about 20x more money than the "Server-5 CAL" option we need and just not justifiable!) - I'm aware there are also Basic Availability Groups with Standard edition, but they don't support querying the secondaries, which is no use to us.

Log shipping is not a bad option, except the fact that it either disconnects users or queues restores is a problem - we'll quite likely have users connected and they won't want to have their long-running queries terminated!

Transactional replication seems to be a bit too granular - table level, requiring tables to have primary keys etc. it feels like it's going to be a maintenance problem and not the appropriate solution

So, I'm left with Database mirroring - which Microsoft have deprecated! (although it's still there in SQL 2017, probably because there isn't a good, cheap alternative!)

I'd appreciate any thoughts/suggestions?

Log shipping is what we end up using. However licensing dictates that we pay for additional licenses if we use the secondary in any way at all (even to take backups from)...


Change MySQL database name in SQL file I have a 2GB mysql backup file that has several databases in it and would like to change the name for one of the databases. What approaches  can I use to tackle this issue ? I have used sed but it ends up making changes where it should not.

sed 's/CREATE DATABASE \/*!32312 IF NOT EXISTS*\/ `mydb`/`mydb2`/g' all_databases.sql > out.sql

Why do you want to complicate it?

1). vi the backup.sql file.

2). /mydb -> find for line 'mydb'

3). For sure the first two line will be this.

 CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mydb` ;
    use `mydb`;

4). Just change mydb from both the lines to mydb2 and save the file
and import it.


SQL Linked Server Passing Date Parameters I'm using SQL Server 2008 R2 and have a linked SQL 2012 Express database.  On the 2008 box I am creating a stored procedure to be used in a report.  I am wanting to pass a date variable so the user can enter a to and from date in reporting services.  However, I have been trying to get the openquery parameter code correct and it's just not happening.  Here is my code:

@Custvar VARCHAR(1000),
@fromperiod datetime,
@toperiod datetime

@fromperiod varchar(8),
@toperiod varchar(8)

AS

SET NOCOUNT ON

DECLARE @sqlQuery VARCHAR(8000)
DECLARE @finalQuery VARCHAR(8000)

DECLARE @fromperiodvar VARCHAR(8)
DECLARE @toperiodvar VARCHAR(8)

--SET @fromperiodvar=(CONVERT(CHAR(8), @fromperiod, 112))
--SET @toperiodvar=(CONVERT(CHAR(8), @toperiod, 112))

SELECT * FROM OPENQUERY
([ServerName\SQLEXPRESS],'
SELECT
    (DateDiff(DAY ,otrs.dbo.ticket.create_time , otrs.dbo.ticket.change_time) ) AS Open_to_close_days_duration ,
    otrs.dbo.ticket.create_time ,
    otrs.dbo.ticket.change_time,
    otrs.dbo.queue.name
FROM
    otrs.dbo.ticket
    INNER JOIN otrs.dbo.queue
        ON otrs.dbo.ticket.queue_id = otrs.dbo.queue.id
WHERE
    ((((DateDiff(DAY ,otrs.dbo.ticket.create_time ,otrs.dbo.ticket.change_time))) > 0)
        AND((otrs.dbo.ticket.ticket_state_id) = 3 OR(otrs.dbo.ticket.ticket_state_id) = 2 OR(otrs.dbo.ticket.ticket_state_id) = 10))
        AND otrs.dbo.ticket.create_time >= ''''+ @fromperiodvar + '''' and otrs.dbo.ticket.create_time <=''''+ @toperiodvar + ''''
ORDER BY
    otrs.dbo.ticket.queue_id;
')
GO

So where am I going wrong?  I am unable to have SQL prompt me for the to and from dates?

Any help would be greatly appreciated.

Regards,

Michael

For an ad hoc batch, you need to type the date values and assign them to the variables:

DECLARE @fromperiod varchar(8),
        @toperiod   varchar(8);

SELECT @fromperiod = '20130101',
       @toperiod = '20130131';

An ad hoc T-SQL batch can't prompt you; it's not interactive like VB in a Windows Form. You could do this by using the Debug button on the toolbar, and then editing the values in the Locals tab, but this seems to be a pretty cumbersome way to avoid a little typing:

There are probably also ways you could use templates or snippets to have a slightly easier way to input values, but I don't find the above that much of a blocker in the first place.

For Reporting Services, I know there are ways to allow users to enter data and have those passed at parameters, but someone else is going to have to give you the skinny on that (the documentation might be helpful as well). I can spell Reporting Services but not much more than that. If this is a stored procedure it probably makes things a lot easier.


Capture very expensive queries I have an Sql servers of 2008/2012. Some users run very expensive queries, such as updating 1G rows in one transaction or cross join several very big tables, and it causes the server no response from time to time.

What's the good way to trace/trigger the server to save the running queries when it happens?

You can identify problematic queries through DMVs using this query:

USE [master]
GO
SELECT  session_id,
blocking_session_id,
wait_time,
wait_type,
last_wait_type,
wait_resource,
transaction_isolati,
on_level,
lock_timeout
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0
GO

If you want to collect historical data or automate the process, you should consider 3rd party solutions. I’m using ApexSQL Monitor, but there are many others on the market. WindRaven mentioned some but there are also Idera Diagnostic, Nagios, SQL Sentry, Quest Spotlight.


How to backup live mysql database? I have 12 GB MySQL InnoDB Database with around 800 queries per second. No sooner I start 'mysqldump' over it to make backup I start getting '503' on my website.

How do I make backup of my live database without affecting the site performance?

try the following command since the database size is not small:

mysqldump -u USER -p --single-transaction --quick --lock-tables=false --all-databases (or) DATABASE | gzip > OUTPUT.gz


Deadlocks With Trigger Based Replication A table ORIGINAL exists with the following structure:

ID    VARCHAR Length 10 (key)
VALUE VARCHAR Length 10 (non-key)

A table REPLICATION exists with the following structure:

ID               VARCHAR Length 10 (key)
CHANGE_TIMESTAMP NUMBER  Length 15 (non-key)

I want to log every changed primary key to the REPLICATION table, with the latest change time stamp.

Therefore I have created this trigger in oracle:

CREATE OR REPLACE TRIGGER REPLICATION_TEST
  AFTER INSERT OR UPDATE ON ORIGINAL
  FOR EACH ROW

  DECLARE timestamp DEC(15);
  BEGIN
    SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'yyyymmddhh24miss'))
      INTO timestamp
      FROM dual;

    INSERT INTO REPLICATION
      FIELDS ("id", "change_timestamp")
      VALUES (:NEW."id", timestamp);
    EXCEPTION WHEN dup_val_on_index THEN
      UPDATE REPLICATION
        SET "change_timestamp" = timestamp
        WHERE "id" = :NEW."id"
END;

Functionally, this works just fine. But in a productive environment with multiple sessions where arbitrary data changes can happen at any time, this infrequently leads to deadlocks. Presumably because of the UPDATE statement.

An alternative approach would be to add the CHANGE_TIMESTAMP field as additional key field to do only INSERTS into the REPLICATION table and skip the UPDATE in case of duplicates. This would work functionally just fine, but would obviously result in much more data being produced which I'd like to avoid.

What else can I do?

Before rows  are updated in the REFERENCE table the corresponding rows must be updated in the ORIGINAL table. If a session waits for a lock of a row of the REFERENCE table to update it must get the lock of the corresponding ORIGINAL table row lock. But this is hold by the session that holds the REFERENCE table row lock.
I can't see any problem with this design, but I am not 100% sure. Nevertheless I think it i easier to read if you create an update statement that is triggered for updates and an insert statement that is triggered for inserts. But maybe this is a matter of taste.


'ERROR: text search dictionary "unaccent" does not exist' during CREATE INDEX? I'm running PostgreSQL 9.3 on Mac OS X Yosemite.

I try to create an unaccent lowercase trigram index. To achieve it I did this:

mydb=# CREATE EXTENSION pg_trgm SCHEMA public VERSION "1.1";
       CREATE EXTENSION unaccent SCHEMA public;
       ALTER FUNCTION unaccent(text) IMMUTABLE;
CREATE EXTENSION
CREATE EXTENSION
ALTER FUNCTION

Then I tried to create the index:

mydb=# CREATE INDEX author_label_hun_gin_trgm ON address
       USING gin (public.unaccent(lower(label_hun)) gin_trgm_ops);
ERROR:  text search dictionary "unaccent" does not exist

... and got this error. If I try to list the available text search dictionaries the unaccent dictionary seems to be there:

mydb=# \dFd
                             List of text search dictionaries
   Schema   |      Name       |                        Description
------------+-----------------+-----------------------------------------------------------
 pg_catalog | danish_stem     | snowball stemmer for danish language
 pg_catalog | dutch_stem      | snowball stemmer for dutch language
 pg_catalog | english_stem    | snowball stemmer for english language
 pg_catalog | finnish_stem    | snowball stemmer for finnish language
 pg_catalog | french_stem     | snowball stemmer for french language
 pg_catalog | german_stem     | snowball stemmer for german language
 pg_catalog | hungarian_stem  | snowball stemmer for hungarian language
 pg_catalog | italian_stem    | snowball stemmer for italian language
 pg_catalog | norwegian_stem  | snowball stemmer for norwegian language
 pg_catalog | portuguese_stem | snowball stemmer for portuguese language
 pg_catalog | romanian_stem   | snowball stemmer for romanian language
 pg_catalog | russian_stem    | snowball stemmer for russian language
 pg_catalog | simple          | simple dictionary: just lower case and check for stopword
 pg_catalog | spanish_stem    | snowball stemmer for spanish language
 pg_catalog | swedish_stem    | snowball stemmer for swedish language
 pg_catalog | turkish_stem    | snowball stemmer for turkish language
 public     | unaccent        |

Any idea what could be wrong here?

I recommend this alternative approach to get an IMMUTABLE unaccent() function:
CREATE OR REPLACE FUNCTION f_unaccent(text)
  RETURNS text AS
$func$
SELECT public.unaccent('public.unaccent', $1)  -- schema-qualify function and dictionary
$func$  LANGUAGE sql IMMUTABLE;

Use that function for the expression index and in all queries. Detailed explanation:

Does PostgreSQL support “accent insensitive” collations?

And do not alter the original function:
ALTER FUNCTION unaccent(text) IMMUTABLE;
.. which also solves any possible issues with the search_path: The fact that you had to schema-qualify public.unaccent() in your index definition suggests that you might not have public in your search_path.
Then, your index:
CREATE INDEX author_label_hun_gin_trgm ON address
USING gin (f_unaccent(label_hun) gin_trgm_ops);

I did not include lower(). You can, but trigram indexes support case insensitive patterns out of the box:

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

Troubleshooting
If you are running all of this in the same session with the same search_path and one call works while the next reports function ... does not exist, then something is seriously broken in your database.
First make sure you are testing with a valid search_path setting:
SHOW search_path

Details in this related answer

How to create table inside specific schema by default in Postgres?

If you diagnose breakage, switch into alarm mode and take all the necessary steps to avoid data loss (like drawing a backup before you do anything else).
A broken index for the system table pg_proc might be the cause. To reindex all system tables from the shell:
reindexdb [connection-option...] --system mydb


Always Encrypted after restoring an old database backup using C# I have a Windows Form application. It allows users to restore a SQL Express database.

Latest version of SQL Server in app now is SQL Server 2017. Customer can restore an old backup of a SQL Server 2008 R2 database.

I want to enable Always Encrypted (AE) after performing the restore using C#.

Could anyone please help me to achieve this?

There is no issue enabling AE for a new database. I would like to have AE after restoring an old database into the application. The restore happens via a T-SQL command.

I tried to enable AE using PowerShell. I am getting sqlserver module errors. Is there any way to add AE using T-SQL?

The error is:

  System.Management.Automation.CommandNotFoundException:
  'The term 'Get-SqlDatabase' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.'

This errors in PowerShell; however, I really want to do AE without PowerShell in C#.

I tried:

Alter table test3
    alter column C3 varchar(50) COLLATE Latin1_General_BIN2
    ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1],
    ENCRYPTION_TYPE = Deterministic,
    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL

This command doesn't work when there is data in the table.

I could achieve always encrypted using c#.
generate powershell script using management studio.

Add below import modules in script at top.

Import-Module -Name "SqlServer" -MinimumVersion "21.0.17262"

Then run PS script using below code

 using (PowerShell powerShellInstance = PowerShell.Create())
            {
                powerShellInstance.AddScript(
                    "Set-ExecutionPolicy -Scope Process -ExecutionPolicy ByPass");
                powerShellInstance.AddScript(scriptFileText, true);
                var psOutput = powerShellInstance.Invoke();

                foreach (PSObject outputItem in psOutput)
                {
                    if (outputItem != null)
                    {
                         //Log output if error
                    }
                }
                success = true;
            }


SQL stored procedure to produce custom XML text I have around 30 tables from which I need to generate a specific XML format, which is consistent through the tables.

With my current solution I have a stored procedure for each 30 tables where I do:

select '<TOP (id column)="' + id column + '"/>'
+ '<FIELD fieldname="(fieldname)" value="' + fieldname + '"/>'
+ '<FIELD fieldname="(fieldname1)" value="' + fieldname1 + '"/>'
+ '<FIELD fieldname="(fieldname2)" value="' + fieldname2 + '"/>'
+ '<FIELD fieldname="(fieldname3)" value="' + fieldname3 + '"/>'
+ '<FIELD fieldname="(fieldname4)" value="' + fieldname4 + '"/>'
+ '<FIELD fieldname="(fieldname5)" value="' + fieldname5 + '"/>'
+ </TOP>
from dbo.table

where the parantheses values are hardcoded for each table. So for example in one table (id column) is CUSTOMER_ID. While in a different table (id column) is STORE_ID. and (fieldname) is for example CUSTOMER_NAME. So they are all hardcoded column names in the <"FIELD"> brackets and primary key in <"TOP">.

So my stored procedure with all 30 tables is around 1000 lines long, as every column and primary key is hardcoded for each table.

I'm certain there is a dynamic way to achieve this functionality where columns/rows are somehow iterated over, but I can't figure it out.

This seems to at least approach a solution for you.  You might need to add in some data type detection and conversions to make it work for you though.  It also makes an assumption that the first column for the table is the ID column...

DECLARE @TableName NVARCHAR(100) = 'dbo.MyTableName'
DECLARE @SQLCommand NVARCHAR(4000)

;WITH CTE_Initial AS
    (
    SELECT CONCAT('<', CASE WHEN C.column_id = 1 THEN 'TOP (' + C.name + ')' ELSE 'FIELD fieldname="(' + C.name + ')"' END, ' value="', '''', ' + ', '[T].', QUOTENAME(C.name), ' + ', '''', '"/>') AS XMLColumnName
            , *
    FROM sys.columns AS C
    WHERE C.object_id = OBJECT_ID(@TableName)
    )
SELECT @SQLCommand = 'SELECT ' + STRING_AGG(CONCAT('''', XMLColumnName, ''''), '+') + ' FROM ' + @TableName + ' AS T' FROM CTE_Initial

PRINT @SQLCommand

--EXEC sp_executesql @SQLCommand


Which Log should I start backups with? We have log backups running every 15 minutes and nightly fulls/diffs (fulls are once a week per DB, but they are spread across the whole week for load balancing). Lets say for a particular DB I have a diff that starts at 10:05pm and finishes at 10:18pm and I have log backups at 10:00pm, 10:15pm, and 10:30pm. If I want to restore to 10:24pm that night, I know I need to restore the last full and the diff from that night, but then I'm not sure which transaction log I should start with after that.

You need to restore last FULL backup, the Differential, the 10:15 transaction backup and the 10:30 one with the STOPAT = '2015-05-22 22:24:00' clause.


Designing a platform: one database or multiple databases? We are building a web platform that incorporates multiple services, each with its own underlying data.  These services are being built independently following the principles of Service-Oriented Architecture, but they transact against potentially related data.  We are considering whether these services should share one big database or each have their own database.  (We are planning to use SQL Server 2008 Enterprise on a Windows 2008 cluster.)

Some of the advantages to each approach we have already considered include:

Single Database

Relating data from different services can be bound together by foreign key constraints
Analytic extracts are simpler to write and faster to execute
In the event of a disaster, restoring the platform to a consistent state is easier
For data that is referenced by multiple services, data cached by one service is likely to be used soon after by another service
Administration and monitoring is simpler and cheaper up front

Multiple Databases

Maintenance work, hardware problems, security breaches and so forth do not necessarily impact the whole platform
Assuming each database is on separate hardware, scaling up multiple machines yields more performance benefits than scaling up one big one

From an operational perspective, is it more advantageous that each service in this platform get its own database, or that they all go in the same database?  What key factors inform an answer to this question?

In my opinion, the key differentiator of true SOA systems (over the pseudo SOA, ntier/distributed systems that are becoming ubiquitous) is that there should be zero interaction between discrete services. Where this is achieved, any application you compose from these services can and should be built to tolerate the failure of any consistuent part. A failure reduces functionality but service is maintained.

In this scenario it's logical, or required, to separate the underlying database for each service. If however you have services which are interdependent, there is little (perhaps nothing) to be gained from a split.

I'd recommend reading sites such as HighScalability.com which dig into the architectures adopted by the never-fail type websites. One of my favourites of late was the story of the Netflix Chaos Monkey which was mentioned on Coding Horror.

Addressing a couple of the points in your question:

  In the event of a disaster, restoring the platform to a consistent
  state is easier.

This is true but you should perhaps be thinking about how to better decouple these services so this stops being an issue. Alternatively, there are methods to ensure synchronisation across multiple databases, transaction marks in SQL Server for example.

  For data that is referenced by multiple services, data cached by one
  service is likely to be used soon after by another service.

Distributed cache solutions (memcached et al) could help here but you'd be violating the service independence principles. This would be comparable to having two services communicating with each other directly, or worse having a service access anothers data store, bypassing the service interface altogether. Inevitably data will be related and will be handed between services by the calling platform, the tricky decisions tend to be around which service will own which pieces of data. StackOverflow or Programmers sites might be better placed to help with the more general SOA issues.

  Assuming each database is on separate hardware, scaling up yields more
  performance benefits.

Certainly it can be cheaper to scale out across multiple lower spec machines than to scale up a single machine. Although, the lower hardware costs may be dwarfed in the total cost of ownership when the soft costs of additional development effort and operational complexity are factored in.

If this isn't SOA and you just have a case where the component services of this platform are being built by different teams/suppliers for logistical reasons, stick with a single database and completely ignore everything above! :)


Oracle drop database - Can images stored in RMAN catalog be retained? It seems the default behavior when an Oracle database is dropped / deleted (entire server retired) that any RMAN entries for that system are also dropped.

Is it possible for RMAN to retain records for the dropped system until the "retention" is reached (removing backup pieces as they expire when stored on separate media) until all images are deleted (both from the RMAN catalog and media)?

Or does the original database have to be alive for this to happen?

The goal being to act like a backup product (NetBackup, CommVault, NetWorker, etc) where a catalog is retain independent of the state of the database.  Though doing this all natively with Oracle tools.

It sounds like you need to set up a recovery catalog in a central database, which you can read up on how to set up a Recovery Catalog and maintain it from the Oracle Database 12c Docs.

Once you have the recovery catalog, then dropped database backup records are still maintained with their respective retention policies:

Connect to the recovery catalog:

sqlplus rman/ @catdb

Get the dbid for the database in question:

select dbid, name from rc_database order by name;

Point the recovery catalog to the database using the DBID from the previous query:

call dbms_rcvman.setdatabase(null,null,null,<DBID>,null);

List the backups for that database:

select * from rc_backup_files;


Performance issues on large CSV import with before INSERT trigger I am doing import csv file using load into file. The files are big and contains minimum 500K+ lines of records and table extend day by day. Each line is just a phone number and nothing else. My table size has now grown for records more than 200+ Mil. I am also using a trigger to update a few items before INSERT. A bulk import takes a very long time naturally because of the trigger.

To import records query

load data local infile '/tmp/file.csv' IGNORE into table contacts FIELDS TERMINATED BY ',' (contact) set phonebook_id = 1

Trigger

CREATE TRIGGER `contacts_before_insert`
BEFORE INSERT ON `contacts`
FOR EACH ROW
BEGIN

   DECLARE vTimezone varchar(4);

   SELECT IF((timezone = '' OR count(timezone) = 0), 'ERR', timezone)
     INTO vTimeZone
     FROM timezones
    WHERE area_code = (SELECT LEFT(NEW.contact , 3));

   SET NEW.timezone = vTimezone;

timezone table structure

  CREATE TABLE `timezones` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `state` varchar(45) NOT NULL,
    `area_code` varchar(3) NOT NULL,
    `timezone` varchar(4) NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=327 DEFAULT CHARSET=latin1

Contacts table structure

 CREATE TABLE `contacts` (
   `id` bigint(20) NOT NULL AUTO_INCREMENT,
   `phonebook_id` int(11) NOT NULL,
   `campaign_id` int(11) DEFAULT NULL,
   `contact` varchar(45) NOT NULL,
   `status` tinyint(1) unsigned NOT NULL DEFAULT '1',
   `admin` tinyint(1) unsigned NOT NULL DEFAULT '0',
   `call_status` tinyint(1) unsigned NOT NULL DEFAULT '1',
   `active_call` tinyint(1) unsigned NOT NULL DEFAULT '0',
   `test_call` tinyint(1) unsigned NOT NULL DEFAULT '0',
   `v_node_name` varchar(45) DEFAULT NULL,
   `timezone` varchar(4) DEFAULT NULL,
   `updated_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `created_date` timestamp NULL DEFAULT NULL,
    `contact_name` varchar(45) DEFAULT NULL,
     PRIMARY KEY (`id`),
     UNIQUE KEY `phonebook_id` (`phonebook_id`,`contact`),
     KEY `idx_001` (`active_call`,`campaign_id`,`test_call`),
     KEY `idx_002` (`phonebook_id`,`call_status`,`test_call`,`campaign_id`,`active_call`,`dnc_contact`),
     KEY `idx_003` (`phonebook_id`,`test_call`,`call_status`,`dnc_contact`),
     KEY `FK_campaign_id_idx` (`campaign_id`),
     KEY `idx_testcall` (`test_call`),
     KEY `idx_004` (`campaign_id`,`call_status`,`test_call`),
     KEY `idx_active_call` (`active_call`)
 ) ENGINE=InnoDB AUTO_INCREMENT=136600117 DEFAULT CHARSET=latin1

My question is: if I break the CSV file into smaller pieces, say 2 Meg each or something like that, is that going to speed up the INSERT? Or, should I create some sort of stored procedure which runs after the import and updates the data in contacts table where my contacts are being imported. Not to mention, I absolutely want to avoid row/table locking because at the same time the table has heavy SELECT/UPDATES.

What would be the best practice here? How to speed up large CSV imports avoiding table locking etc .. ?

My suggestions are roughly in order of the performance benefit they will provide.

(1) (Similar to what a_vlad says) Rethink the use of a Trigger.  I would load the csv into a temp table, then do the timezone lookup in a single query.  Only then copy the data into the main table.  Other tips on High speed ingestion .  Tips on chunking .  Yes, 500K rows at once is likely to cause some kind of trouble.

(2) Add to timezones:

INDEX(area_code, timezone)

(3) This is inefficient:

WHERE area_code = (SELECT LEFT(NEW.contact , 3));

change it to

WHERE area_code = LEFT(NEW.contact , 3);

(4) The first of these is redundant and can be dropped:

 KEY `FK_campaign_id_idx` (`campaign_id`),
 KEY `idx_004` (`campaign_id`,`call_status`,`test_call`),

(5) What is the distinction between contact and contact_name?

(6) Low-cardinality indexes (eg, idx_active_call) are essentially never chosen by the Optimizer, so could be dropped.


Having different Table space for different indexes of same table, will causes any performance impact? Table Master_Table is created in App1_Schema1.
Index_I01 is created in default TABLE SPACE.
Index_I02 is created in TABLE SPACE "APP2_INDEX".

Having different Table space for different indexes of same table, will causes any performance impact ?

Placing the indexes for a table in the same or different tablespaces from the tables and other indexes on the same table should have no impact on performance unless you have done something very wrong with your disk farm.


Why Regenerate Snapshots For Merge Replication I am in the process of implementing merge replication in SQL 2012 with web sync.

I am wondering two things,

Why is it suggested to regenerate the snapshots every 14 days by
default?
With web sync you are an anonymous subscriber, so how can replication know when to clean up metadata for a user who never
syncs

So the first question there. If a person syncs every day, and they are pulling back about the same amount of changes specifically why would we have to regenerate the snapshot? I'm not sure why a sync such as this would become slower?

Is it a case of knowing when it can clean up metadata? The user with the oldest snapshot has just regenerated a new snapshot, so it means we can clean up the metadata up to the next oldest snapshot?

This leads to the second question. If I have a user who I have given a merge replication solution to as a demo. It turns out that they never use the system and have only synced it once at the start to test it out. They may have even removed it from their computer.

If their shapshot job has been turned off and they never sync does it mean we get stuck with a whole bunch of metadata that replication cannot clean up? Does replication at some point conclude that that person is not using the system and block them out?

The reason I ask this is I am using anonymous subscribers. When we have normal subscribers and we delete the subscription the server is connected to directly and the subscription is removed from the publication. This does not happen for web sync.

The assumption I think with the default setting it that you'll be pushing out new subscriptions on a regular basis.  You don't need to do this, however when you push out a new subscription you'll need to manually run the snapshot agent (which really isn't that big a deal).

When the subscription is created the distributor will store information about the subscriber so that the distributor has this information available for cleanup and data distribution.


Can I split lines into Elements with For XML? In this example I'm cribbing from a previous question, My source table has a varchar(max) field that I want to split by line into individual elements with FOR XML as I query it. Given this data-

CREATE TABLE Example
(
    [ID] INT
  , [Body] VARCHAR(Max)
);

INSERT INTO Example ([ID], [Body])
VALUES
        (1, 'This is a test'),
        (2, 'This is another
         test with
         two line breaks'),
        (3, 'This is a test
         with one line break');

I want to produce this XML   -

<row>
    <ID>1</ID>
    <Body>
        <Line>This is a test</Line>
    </Body>
</row>
<row>
    <ID>2</ID>
    <Body>
        <Line>This is another</Line>
        <Line>test with</Line>
        <Line>two line breaks</Line>
    </Body>
</row>
<row>
    <ID>3</ID>
    <Body>
        <Line>This is a test</Line>
        <Line>with one line break</Line>
    </Body>
</row>

Can I do this with FOR XML alone, or do I need to get xslt involved?

Use a split string function of your choice and split on CRLF.

select E.ID,
       (
       select S.Item as Line
       from dbo.SplitStrings(E.Body, char(13)+char(10)) as S
       for xml path(''), type
       )
from dbo.Example as E
for xml path('row');

Have a look at the blog post Split strings the right way – or the next best way by Aaron Bertrand for a couple of versions to choose from.


Why can MySQL handle multiple updates concurrently and PostgreSQL can't? Let's imagine you have a table with this definition:

CREATE TABLE public.positions
(
   id serial,
   latitude numeric(18,12),
   longitude numeric(18,12),
   updated_at timestamp without time zone
)

And you have 50,000 rows in this table. Now for testing purposes you will run an update like this:

update positions
set  updated_at = now()
where latitude between 234.12 and 235.00;

That statement will update 1,000 rows from the 50,000 (in this specific dataset).

If you run such a query in 30 different threads*, MySQL innodb will succeed and PostgreSQL will fail with lots of deadlocks.

Why?

* I'm comparing the latest version of MySQL innodb vs Postgres, this is a concurrent update case. Production cases: imagine 5000 stocks being updated with the latest price available constantly.

Rolando already more or less described the reason why the deadlocks happen.  Let me add that PostgreSQL has to take locks on the rows to be updated, in order to maintain consistency.  (now() is fixed to the beginning of the transaction, meaning that two concurrent updates will want to update a given row to a different timestamp, which has to be resolved somehow - taking locks on the rows is the PostgreSQL solution.)

The locks are assigned to the transaction on a first come - first served basis.  Deadlocks occur when two transactions try to obtain locks on the same rows in different order.  The way to do this is - now not surprisingly - requesting the locks in a predictable order.  This is done by

SELECT id -- could be any column, even a constant
FROM position
WHERE latitude between 234.12 and 235.00
ORDER BY id
FOR UPDATE;

Here the crucial part is ORDER BY id and FOR UPDATE.  The latter takes the necessary locks of the same type the subsequent UPDATE needs.

The SELECT ... FOR UPDATE statement might looks superfluous otherwise (especially when you don't need the selected data), and it also means disk writes even before actually changing the data.  This has to be considered when using it.


Not find the table of the oid I have a database running under PostgreSQL which normally has to have a size of 300 GB. Unfortunately it is now 3,5 TB in size.

I noticed that the directory of this database , the pgdata path is /base/45075466. There are about 3000 files in that directory each 1 GB in size.

I launched a query to check on all the objects of the database, but I didn't detect any large objects:

 SELECT *, pg_size_pretty(total_bytes) AS total
    , pg_size_pretty(index_bytes) AS INDEX
    , pg_size_pretty(toast_bytes) AS toast
    , pg_size_pretty(table_bytes) AS TABLE
  FROM (
  SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
      SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
              , c.reltuples AS row_estimate
              , pg_total_relation_size(c.oid) AS total_bytes
              , pg_indexes_size(c.oid) AS index_bytes
              , pg_total_relation_size(reltoastrelid) AS toast_bytes
          FROM pg_class c
          LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE relkind = 'r'
  ) a
) a;

I checked the size by schema, but didn't find any size irregularities:

    SELECT schemaname,
     sum(pg_total_relation_size(schemaname||'.'||tablename)) as size
     from pg_tables   group by schemaname
     order by size desc;

With the OID of these files 21037129 and using oi2dname I did not find the name of the table:

-rw-------  1 999 docker  1,0G févr. 11 02:30 121037129.1045
-rw-------  1 999 docker  1,0G févr. 11 02:34 121037129.1046

I think it's orphaned files, because if I use another OID, the name of the table is detected.

I have only 350 GB space on my computer's drive and I think it's a risk to launch a VACUUM FULL (risk of having free space reduced to zero).

Before I launch the VACUUM FULL, is there any other method to resolve this problem? If I delete the database in question, is it possible that my orphaned files will be eliminated and what is the delay? Is there any other method to avoid having to launch VACUUM FULL?

Before launch the VACUUM FULL, is there a another method for the
  resolution of this problem?

VACUUM FULL does not solve that problem, in the sense that if the files you found inside the database directory do not corresponding to anything in the catalog, they will be ignored by VACUUM FULL and their space will not be reclaimed.

Such files are left out when a backend is stopped abrubtly (fast shutdown or crash) during the transaction that creates the objects, and the crash recovery sequence is not able to clean up.
In that case, only manual deletion will reclaim that space.

Before removing the files you want to be absolutely sure of your query. Right now, it's based on relkind='r', and altough it gets back to indexes and toast tables indirectly, it seems to miss materialized views or sequences.

See How to reclaim space taken by an index that partially built and was terminated by a power outage
for an approach that worked. That question was about an index, but it applies to other kind of objects.


Different behaviour between oracle and postgres regarding alter table primary key command In Oracle 11g, when you write

alter table abc add constraint abc_pk primary key (one, two, three);

Automatically an index for one, two, three is added.

In PostgreSQL 9.5, when you do the same, no index is added.

Is there anything I can add to the command and have the same behaviour as with Oracle?

An index is added in PostgreSQL, too, when a PRIMARY KEY or a UNIQUE constraint is created, as it is clearly stated in the docs. See CREATE TABLE:

  PostgreSQL automatically creates an index for each unique constraint and primary key constraint to enforce uniqueness. Thus, it is not necessary to create an index explicitly for primary key columns. ...

Tested:

Version:

x=# select version();
                                             version
-------------------------------------------------------------------------------------------------
 PostgreSQL 9.5.2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit
(1 row)

Create the table and show the structure:

x=# create table abc (one int not null, two int not null, three int not null);
CREATE TABLE

x=# \d abc
      Table "public.abc"
 Column |  Type   | Modifiers
--------+---------+-----------
 one    | integer | not null
 two    | integer | not null
 three  | integer | not null

Add the primary key constraint  and show the structure again:

x=# alter table abc add constraint abc_pk primary key (one, two, three);
ALTER TABLE

x=# \d abc
      Table "public.abc"
 Column |  Type   | Modifiers
--------+---------+-----------
 one    | integer | not null
 two    | integer | not null
 three  | integer | not null
Indexes:
    "abc_pk" PRIMARY KEY, btree (one, two, three)

As for PgAdmin, looks like it doesn't show these automatically created indexes in the Indexes list. That's a decision its developers took. See an old discussion (from 2004!) about the exact same issue:

[pgAdmin-Support] Indexes generated for primary key are not shown by PgAdminIII.


Oracle 12c Installation (Grid) on RHEL 7.1 CLSRSC-214: Failed to start 'ohasd' root.sh script execution failing with following error please advice

./root.sh
Performing root user operation for Oracle 12c

The following environment variables are set as:
    ORACLE_OWNER= grid
    ORACLE_HOME=  /u01/app/grid/product/12.1.0/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u01/app/grid/product/12.1.0/grid/crs/install/crsconfig_params
LOCAL ADD MODE
Creating OCR keys for user 'grid', privgrp 'oinstall'..
Operation successful.
LOCAL ONLY MODE
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
CRS-4664: Node lrma086 successfully pinned.
2015/10/22 01:39:44 CLSRSC-330: Adding Clusterware entries to file '/etc/inittab'

2015/10/22 01:41:50 CLSRSC-214: Failed to start 'ohasd'

Failed to start the Clusterware. Last 20 lines of the alert log follow:
2015-10-22 01:39:34.970:
[client(21726)]CRS-2101:The OLR was formatted using version 4.
2015-10-22 01:39:39.368:
[client(21753)]CRS-1001:The OCR was formatted using version 4.

2015/10/22 01:41:50 CLSRSC-318: Failed to start Oracle ohasd service

Died at /u01/app/grid/product/12.1.0/grid/crs/install/crsinstall.pm line 2985.
The command '/u01/app/grid/product/12.1.0/grid/perl/bin/perl -I/u01/app/grid/product/12.1.0/grid/perl/lib -I/u01/app/grid/product/12.1.0/grid/crs/install /u01/app/grid/product/12.1.0/grid/crs/install/roothas.pl ' execution failed

Add following:
cat /etc/systemd/system/ohasd.service

[Unit]

Description=ohasd daemon

[Service]

ExecStart=/etc/init.d/init.ohasd run >/dev/null 2>&1

Reload the deamon:
systemctl daemon-reload

Enable the ohasd:
systemctl enable ohasd.service
systemctl start ohasd.service

Check if it is running:
ps -ef|grep ohas

Run the root.sh script again


Get count of relationship rows between three tables that are not duplicates I have a (local) MySQL database, which is a Drupal 6 export. It has these relevant tables:

node - Contains articles, plus also information about their department and sections. I'm interested in nid and title columns.
content_field_department - Has the relationships between article nodes and department nodes. I'm interested in nid (the article node) and field_department_nid (the department node) columns.
content_field_section - Has the relationships between article nodes and section nodes. I'm interested in nid (the article node) and field_section_nid (the section node) columns.

I'm trying to get an accurate COUNT for how many times a given article node is related to each section or department node (performance is not relevant for me at this time, since it's local and it can take as long as it needs to without causing problems). That is, I'd like data like this:

+-----------------+-------------------+
| Section Name    | Count of Articles |
+-----------------+-------------------+
| Department Name | Count of Articles |
+-----------------+-------------------+

The issue

One problem is when the interface was originally built, a department choice for an article also included all of the sections. A section choice, though, never included the departments. So in cases where an article node is related to another node twice, both as a section and as a department, I would like to COUNT that only once.

My current try

My current attempt is like this:

To get a department COUNT:

SELECT DISTINCT d.field_department_nid as tid, n.title as name, (
        SELECT COUNT(DISTINCT nid, field_department_nid)
        FROM content_field_department d2
        WHERE d2.field_department_nid = d.field_department_nid
        AND nid NOT IN (
            SELECT s.nid from content_field_section s
            LEFT OUTER JOIN node n2 ON s.nid = n2.nid
            WHERE field_section_nid IS NOT NULL
        )
    ) as drupal_department_count
FROM content_field_department d
LEFT OUTER JOIN node n ON d.field_department_nid = n.nid
ORDER BY drupal_department_count DESC

To get a section COUNT:

SELECT DISTINCT s.field_section_nid as tid, n.title as name, (
    SELECT COUNT(DISTINCT nid, field_section_nid)
        FROM content_field_section s2
        WHERE s2.field_section_nid = s.field_section_nid
        AND nid NOT IN (
            SELECT d.nid from content_field_department d
            LEFT OUTER JOIN node n2 ON d.nid = n2.nid
            WHERE field_department_nid IS NOT NULL
        )
    ) as drupal_section_count
FROM content_field_section s
LEFT OUTER JOIN node n ON s.field_section_nid = n.nid
ORDER BY drupal_section_count DESC

I had also tried without the NOT IN additions, but the COUNT is too high, I think because the same node combination can be COUNTed in both tables.

What direction do I need to go with this?

I would need more information about your business restrictions to write an ideal query, but here's my advice given my interpretation of what you've said so far.

First thing you need to do is more clearly define what you are trying to achieve.

  So in cases where an article node is related to another node twice, both as a section and as a department, I would like to COUNT that only once.

Since your final result set will be grouped by departments and sections, "counting it only once" means you need to pick where they should be counted.  For example:

   content_field_department         content_field_section
+-----+----------------------+   +-----+-------------------+
| nid | field_department_nid |   | nid | field_section_nid |
+-----+----------------------+   +-----+-------------------+
|  1  |          2           |   |  1  |         3         |
+-----+----------------------+   +-----+-------------------+

should nid 1 be counted in the list for department nid 2 or counted for section nid 3?  Based on your question, you may want to only count it for departments.  In your current query drafts, you are trying to exclude the issue case from both counts, meaning you are effectively counting it 0 times instead of 1 (your goal) or 2 (what you are trying to avoid).

Here's a draft query as a starting point:

select
    DeptOrSect,
    nid,
    title,
    count(1)
from (
    select
        'DEPT' as DeptOrSect,
        dept.nid,
        dept.title,
        link.nid as article_nid
    from node dept
    join content_field_department link on link.field_department_nid = dept.nid
    union all
    select
        'SECT' as DeptOrSect,
        sect.nid,
        sect.title,
        link.nid as article_nid
    from node sect
    join content_field_section link on link.field_section_nid = sect.nid
    where not exists (
        select 1
        from content_field_department dept
        where dept.nid = link.nid
    )
) as x
group by DeptOrSect,
    nid,
    title;


Table with Tags associations So I have a table with tags and the videos they belong to. Both columns are primary.

..and so on

I need to make a list of the videos that have certain tags (multiple) associated with them.

So if I need all videos that have both tags 123 and 124, it would give me videos 14962 and 14963, etc.

I made the table structure assuming this would be easy, but have been staring at this for an hour and I'm still not seeing it.

SELECT group_concat(tag) AS tags
  FROM table
 WHERE tag IN (123,124,234)
 GROUP BY video
 ORDER BY video
;


How to read MongoDB .mdmp files My MongoDB 3.6.2 craches randomly.

It provides .mdmp files in the location where I installed MongoDB. The only problem is I don't know how to read these binary files.

mongod.2017-12-21T08-01-49.mdmp

Does anyone knows how to read these files?

You might want to consider opening an issue on MongoDB's Issue Tracker and providing the *.mdmp file in the issue.

*.mdmp files are considered to be Microsoft Dump Files.

  Compressed file created by Windows after a program error or crash; contains data "dumped" from the program's memory space; saves information about the error, which can be used for debugging; commonly created along with an uncompressed .HDMP file and can be sent to Microsoft as part of a bug reporting process.

Reference: .MDMP File Extension (fileinfo.com)

If you have a Microsoft debugger and the symbols for the relevant progams, then you can have a look at the dump file yourself:

  A dump file is a snapshot of an app at the point in time the dump is taken. It shows what process was executing and what modules were loaded. If the dump was saved with heap information, the dump file contains a snapshot of what was in the app's memory at that point in time. Opening a dump file with a heap in Visual Studio is like stopping at a breakpoint in a debug session. Although you cannot continue execution, you can examine the stacks, threads, and variable values of the app at the time the dump occurred.

But be aware:

  Debugging dump files of optimized code can be confusing. For example, compiler inlining of functions can result in unexpected call stacks and other optimizations might change the lifetime of variables.

Debugging a kernel-mode dump file requires you to...

  To debug kernel-mode dump files in Visual Studio 2013, download the Windows 8.1 Version of Debugging Tools for Windows. See Kernel Debugging in Visual Studio.

Reference: Using Dump Files (Microsoft MSDN)

There are some third-party tool from NirSoft that might help in finding the culprit.

And then there is the following list that tells you which tools could possibly open the *.mdmp file for you:

.mdmp Extension - List of programs that can open .mdmp files (Extension NirSoft)

  Disclaimer:
  I am in no way affiliated with NirSoft. I only use their software.


Database Exceptions with Jetty 8.1.0 with BTM We are running Jetty 8.1.0 with BTM 2.1.2.

We are using Postgres as our DB.

Everything works great except that when we run any query with a condition where sometable's primary=null, the server loses its connection to the DB. The following is the exception  we see:

  org.postgresql.util.PSQLException: ERROR: invalid input syntax for
  integer: "null"at......long stack dump ensues... ...Jun 15, 2012
  12:20:19 PM org.eclipse.jetty.server.AsyncHttpConnection handle INFO:
  EndPoint making nolong progress:stack 100dump
  SCEP@162b333{l(/74.93.245.209:60506)<->r(/10ensues.99.21.87:80),d=true,open=true,ishut=false,oshut=false,rb=false,wb=false,w=false,i=5r}-{AsyncHttpConnection@18fc7ca,g=HttpGenerator{s=3,h=-1,b=20909,c=-1},p=HttpParser{s=0,l=16,c=0},r=1}
  AsyncHttpConnection@18fc7ca,g=HttpGenerator{s=3,h=-1,b=20909,c=-1},p=HttpParser{s=0,l=16,c=0},r=1
  Jun 15, 2012 12:20:19 PM org.eclipse.jetty.server.AsyncHttpConnection
  handle WARNING: Closing EndPoint making no progress: 200
  SCEP@162b333{l(/74.93.245.209:60506)<->r(/10.99.21.87:80),d=true,open=true,ishut=false,oshut=false,rb=false,wb=false,w=false,i=5r}-{AsyncHttpConnection@18fc7ca,g=HttpGenerator{s=3

Any query after this incident breaks with the following exception

It's happening on our Production server! What is causing this problem?

UPDATE:

Here is the code!

I am aware that we shouldn't query with primarykey = null as primary can never be null. But its a parameter and null validations were missed!

Surprisingly, Jetty 6 works great with it! Even after running this query, it has no problem at all. Its just Jetty 8-1-0 thats lossing DB connectivity permanently upon running such query. Until I restart the ser

String query = "select * from table where primarykey="+parameter;
Statement lStatement   = null;
Connection lConnection  = null;
ResultSet lResultSet   = null;
try  {
   lConnection = DBAccess.getConnection();
   lStatement  = lConnection.createStatement();
   lResultSet  =  lStatement.executeQuery( query );
   while ( lResultSet.next() )    {
       mLogger.info("The result set is : "+lResultSet.toString());
   }
}  catch( Exception e )  {
    e.printStackTrace();
    mLogger.error("Exception occurred while trying to runQuery : "+e.getMessage());
}  finally  {
    DBAccess.closeResultSet( lResultSet );
    DBAccess.closeStatement( lStatement );
    DBAccess.closeDBConnection( lConnection );
}

UPDATE

can Someone please help me with this. I am still looking for an answer. I am running into different problems day by day with Jetty 8-1-0.

I removed the Transaction management config in Jetty.xml and deployed with default JNDI config. Things were smooth for sometime.

Last week, the server started crying with "Too Many open files" issue. It got fixed once I increase the number of files that can be open on the server.
Today, Jetty is dead again with OutOfMemoryError.
Whats noteworthy here is the Memory leaks happen at the same place where the "Too May Open Files" error occurs. I am guess its the HTTPClient thats causing the problem. (Well, I will post the same in stackoverflow. I figure its not a DBA issue. But all these started at the same place. so, I want to keep a record of it here if that makes sense.

It looks like the null in the parameter variable is single quoted, like 'null'. In the context of SQL query nulls are going without quotation.

This is how it should be:

=# select 1 from t where i = null;
 ?column?
----------
(0 rows)

And this is what you have:

=# select 1 from t where i = 'null';
ERROR:  invalid input syntax for integer: "null"
LINE 2: select 1 from t where i = 'null';

So you either need to remove any quotation from the parameter variable or to use parameter binding with the database adapter. The second option is preferable.


How to setup daily SQL server export to csv text file? We are exporting 100,000+ rows from a SQL Server 2005 DB to a text file. We have a web script setup to read all rows and then write the text file and it takes over 5 minutes to complete that script.

How can we set it up to export directly from SQL server to a csv file and schedule that daily?

Yes, no problem.  Use BCP (which is a command line tool) to export the data.  Once you have the parameters the way you like, schedule this as a SQL Agent job.


How to Store Trees on Disk I am wondering how to store trees on a physical disk, for perhaps a tree-oriented database like an XML database.

Wikipedia states about XML databases:

  ...custom optimized data structures are used for storage and querying. This usually increases performance both in terms of read-only queries and updates. XML nodes and documents are the fundamental unit of (logical) storage, just as a relational database has fields and rows.

But doesn't explain what the actual data structure is for storing the tree on disk. Wondering if one could explain how that works. It seems you could store each "document" as its own file such as this as a file:

<a>
  <b>
    <c>
      <d>Hello</d>
    </c>
    <c>
      <x>World</x>
    </c>
    <e>
      <f ref="b:0/c:0/d"></f>
    </e>
    <g>
      <f ref="b:0/c:0/x"></f>
    </g>
  </b>
  <b>
    <c>
      <d>Hello2</d>
    </c>
    <c>
      <x>World2</x>
    </c>
    <e>
      <f ref="b:1/c:0/d"></f>
    </e>
    <g>
      <f ref="b:1/c:0/x"></f>
    </g>
  </b>
</a>

And then you would have a query "give me all b/c/x where b/c/d matches Hello", or just "give me all nodes below c". The ref="b:0/c:0/d" is a pointer to a specific node, something like that.

Basically I'm wondering, in order to accomplish these sorts of things, the data structure(s) to use to store XML or any tree structure on disk.

Maybe instead of storing the whole XML document in a file, you end up storing something like this:

a/b/c/d Hello
a/b/c/d World
a/b/e/f ref=b:0/c:0/d
a/b/g/f ref=b:0/c:1/d
...

That's pretty much what I'm wondering, how the data would look in a file on disk at a physical level. Not really sure what to search for. XML database data structures and such doesn't return much.

Every XML database will have its own solutions to these problems. You could do worse that explore the source code of an open-source XML database.

The main aspects of any solution will be (a) the ability to navigate from a node to its children or parent by following some kind of pointer structure rather than by parsing raw XML and searching sequentially, (b) the availability of indexes that enable you to find nodes quickly given properties of the node, e.g. indexing elements by name.


where in (sub query) vs join I just want to ask about how to optimize my query. I am curious about the efficiency of using join instead of where in sub query. Can someone explain the difference?

SELECT  C.Customer_id,
        C.tran_id,
        t.date
    FROM CUSTOMERS C
    INNER JOIN TRANSACTIONS T ON c.Customer_id = t.Customer_id
    WHERE C.Customer_id IN (
        SELECT Customer_id FROM TRANSACTIONS_DATE WHERE tran_Date <= '2013-01-01'
    )
    AND C.Customer_id NOT IN(1,2,3)

I think you can move your subquery onto the join condition, as you're just looking for customers' transactions before or on 1st Jan 2013.

SELECT  C.Customer_id,
        C.tran_id,
        t.date
    FROM CUSTOMERs C
    INNER JOIN TRANSACTIONS T ON c.Customer_id= t.Customer_id
    AND t.tran_Date <= '2013-01-01'
  WHERE  C.Customer_id NOT IN(1,2,3)


Optimise Large Record Deletion Procedure I'm creating a database that mirrors our production database, but is lighter and anonymised - for local development purposes.

Ensuring we've enough data to operate on as an engineering team, I'm removing all customers that have their updated_at date set to over a year ago. Simple process is to keep new users, but bin old or inactive users.

For this, I've created a stored procedure.

DELIMITER //
CREATE PROCEDURE delete_old_customers()
BEGIN
    SET @increment = 0;
    customer_loop: LOOP

        DELETE FROM customers
        WHERE id BETWEEN @increment AND @increment+999
        AND updated_at < DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR);

        IF @increment > (SELECT MAX(id) FROM customers) THEN
            LEAVE customer_loop;
        END IF;

        SET @increment = @increment + 1000;

    END LOOP customer_loop;
END //
DELIMITER ;

CALL delete_old_customers();

DROP PROCEDURE delete_old_customers;

So this procedure batches the removal into groups of 1000, and runs until there are no more customers to process.

I run the procedure like this:

mysql "$MYSQLOPTS" devdb < ./queries/customer.sql

Where the $MYSQLOPTS refers to a my.cnf file with the following options:

[mysqld]
innodb_buffer_pool_size = 6G
innodb_log_buffer_size = 256M
innodb_log_file_size = 1G
innodb_thread_concurrency = 0
innodb_write_io_threads = 64
innodb_flush_log_at_trx_commit = 0
query_cache_size = 0

The problem is that due to the FKs and references this table has, this process can take up to 3 hours to remove ~800k users; and of course, as time goes on, this is only going to grow.

This is running on a Quad-core, 8GB RAM, Digital Ocean Droplet; so I've only limited means to work within.

So given this, I'd love the opportunity to begin optimising this procedure to improve its speed, but I'm unsure of where to start. I'm also open to alternative methods to achieve the same aim.

I prefer the next strategy: the stored routine that fill up the table on each inserted record also delete few expired ones. This look like that:

BEGIN
-- lot of code --
INSERT INTO table ...
-- lot of code --
DELETE FROM table AS w WHERE w.expire < NOW() LIMIT 3;
END

Insertion/deletion ratio is set to 1:3 just to ensure I get the reasonable  removal rate even when incoming data rate become low due to daily/weekly/monthly oscillations. It is acceptable for established bases with low expired records count. If you want to perform initial cleanup, then you have to set the LIMIT to the value that do not insult your server performance.

If you have low incoming data rate then you can create the special routine ad hoc:

CREATE PROCEDURE table_cleanup()
BEGIN
main: REPEAT
  DELETE FROM table AS w WHERE w.expire < NOW() LIMIT 1000;
  UNTIL row_count() = 0 END REPEAT main;
END

Huge DELETE will be splitted into the series of small ones that can't lock the tables for a while.


SSIS Expressions Date Format get just YY for year I found many articles on date formatting expressions in SSIS but I couldn't find one that specified how to get just the Year such as 13 for 2013 or 12 for 2012... I tried

DATAPART("yy",GETDATE())

but it gives me the same result as

DATAPART("yyyy",GETDATE())

I was thinking of a dirty/hack fix which would be something like

DATAPART("yyyy",GETDATE()) - 2000

but it just doesn't feel right.... what if someone runs my package 1000 years from now? lol. Any suggestions?

I am trying to add a time stamp to a file name.. so today's file should be Auth040113.txt. I am creating the file name in a variable using expressions.

Use the modulo operator %: http://technet.microsoft.com/en-us/library/ms141815(v=sql.100).aspx

DATEPART("yyyy",GETDATE()) % 100

Your next problem may be that this will still be be a number and thus will not have leading zeros.

What is your underlying problem?  What are you trying to format a date as?

You would then just use (DT_WSTR, 2) DATEPART( "YY", GETDATE()) % 100 as part of your expression.


How to connect UDL file to a Msaccess DB 2007 (Protected with password)? The UDL file was connected to MS Access DB 2007 successfully. However, when I have protected the DB with a user the connection failed.
Can any one show us how to solve this problem ?

I found to ways to solve this problem.
First, I have opened the 'UDL' file using a text editor and change it as follows :

[oledb]
; Everything after this line is an OLE DB initstring
Provider=Microsoft.ACE.OLEDB.12.0;
Data Source='Your_DB_Full_Name';
Persist Security Info=False;
Jet OLEDB:Database Password='Your_Database_Password'

Second, I have viewed the UDL file properties (mouse right click) then:

At the Provider, tab select the driver.
At the All tab, double click on the Data Source property and write the full name of your DB (with full path).
At the all tab, double click on Jet OLEDB:Database Password property and write your DB password.
At the All tab, double click on the User ID property and write your DB user if it was different from Admin.
Finally, click OK.


SSMS Object Explorer Details causes deadlock while job is running I have a problem with one of my SQL Server jobs.  I've found that while the job is running, SQL Server Management Studio locks up.  It always locks up while I'm looking at tables and sprocs within the "Object Explorer Details" screen.

I am using version 10.0.1600.22 of the SSMS.  Incidentally, version 9.00.4035.00, which I also have installed, has no problem with this--I open this one to find the suspended mangagement studio process and kill it.

The job runs a stored procedure which does the following things:

-truncates a table
-pulls a ton of data from various select statements into the truncated table
-inserts & updates data in second table with records from the first table

All of this occurs within a Transaction, and i have set the DEADLOCK_PRIORITY to HIGH.  I tried removing this setting but it didn't solve the problem.

The procedure is much to involved to post here, but what should I look for to resolve this problem?

In my case, I was able to solve the problem by reducing the size of the transaction in the stored procedure, ala the comment provided by @Aaron Bertrand.  The entire stored procedure was wrapped in one begin/end transaction.  As the stored procedure progressed, my management studio instance would lock up.  Reducing the transaction to just the critical inserts/updates, and adding some error handling, eliminated the problems I was having with SSMS.


DBCC CHECKDB - Corrupted Pages On my understanding, regular run of DBCC CHECKDB is a best practice in order to check integrity of the database.

If there comes a time where a corrupted page existed, and no good back up is available, in general, what are the consequences can occur? Nothing? Corruption will spread?

DBCC CHECKDB will also run DBCC CHECKALLOC, DBCC CHECKTRABLE, and DBCC CHECKCATALOG.

Running this regularly is extremely important in an effort to detect data corruption. If availability is important, you should consider running this daily. It is possible for backups to contain corrupted data as well. This can easily go unnoticed if the corrupted page(s) isn't accessed. Corruption can occur due to several factors including: storage configurations, power faults, poor cache, etc.

It is possible for corruption to spread, so be prepared! Your system and user databases should be backed up frequently with CHECKSUM and the databases be configured with the Page Verify setting set to CHECKSUM.

I would take a look at Kendra Little's post on the issue.

Brent Ozar also has a post regarding what to do when DBCC CHECKDB reports corruption.

Steve Stedman also has an excellent Weekly Database Corruption Challenge if you wish to attempt repairs.


SQL Server symmetric key & certificates games by Hacker? 1)

CREATE SYMMETRIC KEY SecureSymmetricKey
    WITH ALGORITHM = DESX
    ENCRYPTION BY PASSWORD = N'StrongPassword';

I'm trying to figure out about the SQL Server encryption.

once I've executed the code above, is there any way to find out later what is the password value for the SecureSymmetricKey ?

2)

If now I'm doing this with Certificates : I`m the admin and created:

CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'DB Master key password!'
GO

only I know the password.

later I :

CREATE CERTIFICATE MyCertificate
WITH SUBJECT = 'My Certificate Subject'

CREATE SYMMETRIC KEY MySymetricKey
WITH ALGORITHM = TRIPLE_DES ENCRYPTION
BY CERTIFICATE MyCertificate

until now , Its all ok.

Now, when a hacker comes to the computer, all he has to do is :

OPEN SYMMETRIC KEY MySymetricKey DECRYPTION
BY CERTIFICATE MyCertificate

and then :

SELECT
       convert( NVARCHAR(max), decryptbykey(namePAss))
FROM  tbl1

so where is the protection in certificates? No one asked him for a password (like in password encryption (as in my first question)...? He only needed to know the certificate name

OPEN SYMMETRIC KEY MySymetricKey DECRYPTION
BY CERTIFICATE MyCertificate

and its not a problem to find out what is the certificate name ...So where is the protection in decypher data from the hacker ?

The purpose of a certificate in SQL Server is to protect the symmetric keys, but it's more for protecting your offline data: .mdb files and backups.  It's not really designed for preventing access to the data once an attacker has access to the running SQL instance (with enough permission to access the data and run decryption.)

If you use a password for your symmetric key, then someone who knows or can guess the password can take a copy of your data and load and decrypt it.
If you use a certificate, then the attacker needs a copy of the data, and a backup of the certificate, and the password for the certificate.


SQL Server - Automatically full backup for new databases which are auto-created with EntityFramework-Migrations I have an ASP.NET MVC Application with a Multi-Tenant architecture (using SQL-Server). I'm using EntityFramework DB-Migrations which automatically generates a database per customer.
In SQL-Server I have set up 3 maintenance plans: full, diff and transaction backups. The full-backup happens only once a day.
Now if a new customer-database is auto-generated, the next transaction/differential-backups will fail because there isn't any full-backup available.
I have found following thread with a similar problem: full-backup-on-database-creation
But in this case a full-backup is created for all databases - which could lead to a longer run. I just want to do a full-backup for the new database (which is empty and should be finished within seconds).
I have tried this by extracting the code from the maintenance plan:
ON ALL SERVER
FOR CREATE_DATABASE
AS

SET NOCOUNT ON;
    DECLARE @EventData XML = EVENTDATA();
    DECLARE @db sysname = @EventData.value(N'(/EVENT_INSTANCE/DatabaseName)[1]', N'sysname');
    DECLARE @SubDir nvarchar(1024) =  N'PATH TO BACKUP-LOCATION' + @db;
    DECLARE @BackupName nvarchar(1024) = @db + N'_backup_initial';
    DECLARE @BakFile nvarchar(1024) = @SubDir + N'\' + @BackupName + N'.bak';
    DECLARE @ErrMessage nvarchar(1024) = N'Verify failed. Backup information for database ' + @db + ' not found';
    declare @backupSetId as int

    EXECUTE master.dbo.xp_create_subdir @SubDir

    exec('BACKUP DATABASE ' + @db + ' TO  DISK = ''' + @BakFile + ''' WITH NOFORMAT, NOINIT,  NAME = ''' + @BackupName + ''', SKIP, REWIND, NOUNLOAD, COMPRESSION, ENCRYPTION(ALGORITHM = AES_256, SERVER CERTIFICATE = [BackupCert]),  STATS = 10, CHECKSUM, CONTINUE_AFTER_ERROR')

    select @backupSetId = position from msdb..backupset where database_name=@db and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=@db )
    if @backupSetId is null begin raiserror(@ErrMessage, 16, 1) end
    --RESTORE VERIFYONLY FROM  DISK = @BakFile WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND

GO

Executing the backup-script manually is working, but within the trigger it fails with an error message:

cannot perform a backup or restore operation within a transaction

Does anyone know how to get this working?

my suggestion is to replace your script with one that is using ola hallengren backup solution with the option ChangeBackupType='Y'
https://ola.hallengren.com/sql-server-backup.html

ChangeBackupType
Blockquote

Change the backup type if a differential or
transaction-log backup cannot be performed.
DatabaseBackup checks differential_base_lsn in
sys.master_files to determine whether a differential backup can be
performed. If a differential backup is not possible, then the database
is skipped by default. Alternatively, you can set ChangeBackupType to
Y to have a full backup performed instead.
DatabaseBackup checks last_log_backup_lsn in
sys.database_recovery_status to determine whether a transaction log
backup in full or bulk-logged recovery model can be performed. If a
transaction log backup is not possible, then the database is skipped
by default. Alternatively, you can set ChangeBackupType to Y to have a
differential or full backup performed instead.

Basically, if you have this set to Y, then when the scheduled tlog for all user_databases runs, then the script will take a full backup instead of just a tlog.