Explain PostgreSql ssl_cipher values: ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH In the postgresql.conf file, one can configure authorized values for ssl_cipher. Yet I could not find documentation explaining what the ALL, ADH, LOW, EXP, MD5 and STRENGTH values correspond to.

MD5 refers to the corresponding MD5 algorithm I guess, but what about the other values? I believe LOW should not be used in a production environment.

This is a parameter that is processed by the OpenSSL library. The format and options are documented in the ciphers(5) man page.

For the ones you mention:

  ALL

  all cipher suites except the eNULL ciphers which must be explicitly enabled; as of OpenSSL, the ALL cipher suites are reasonably ordered by default

  ADH

  anonymous DH cipher suites, note that this does not include anonymous Elliptic Curve DH (ECDH) cipher suites.

  LOW

  ``low'' encryption cipher suites, currently those using 64 or 56 bit encryption algorithms but excluding export cipher suites.

  EXP, EXPORT

  export encryption algorithms. Including 40 and 56 bits algorithms.

  MD5

  cipher suites using MD5.

You can combine them and specifically include or exclude classes of ciphers by using prefixes:

  Each cipher string can be optionally preceded by the characters !, -
  or +.

  If ! is used then the ciphers are permanently deleted from the list.
  The ciphers deleted can never reappear in the list even if they are
  explicitly stated.

  If - is used then the ciphers are deleted from the list, but some or
  all of the ciphers can be added again by later options.

  If + is used then the ciphers are moved to the end of the list. This
  option doesn't add any new ciphers it just moves matching existing
  ones.

@STRENGTH is a bit special:

  The cipher string @STRENGTH can be used at any point to sort the current cipher list in order of encryption algorithm key length.


Problems with connecting to a remote Oracle 12c database tl;dr
RMAN failing to connect to standby database with correct credentials.

RMAN-04006: error from auxiliary database: ORA-01017: invalid username/password; logon denied

Password file is correct, password is correct.

The long version.

I'm trying to set up a dataguard pair of servers at the moment and I'm running into problems with passwords.

I start with a fresh install of Oracle 12c. Global database name is orcl.domain and SID=orclprmy

I've checked the ORACLE_SID set in .bash_profile and it matches with orclprmy

I then go in and do all my settings to prepare it as my dataguard primary.

From that instance I create a password file using the following:-

rm $ORACLE_HOME/dbs/orapworclprmy
orapwd file=$ORACLE_HOME/dbs/orapworclprmy password=<password>

I've checked on on the primary REMOTE_LOGIN_PASSWORDFILE = 'EXCLUSIVE' so it should be using that password file.

I also sort out the TNSNAMES and LISTENER configuration files to recognise both servers.

I then install oracle in the same way on a separate standby server, the only difference being the database SID being set to orclstby. Again, I've check the ORACLE_SID on that server and it'a also correct.

I scp the password file over from primary to standby (orclstby) and rename it to orapworclstby. I also copy over the TNDNAMES and LISTENER files from primary to standby and edit them as required.

So far, so good. Everything appears to be running properly.

Then comes the part where I prepare the standby to be recovered with RMAN to be the standby database.

vi $ORACLE_HOME/dbs/initorclstby.ora
   db_name=orclstby
slqplus / as sysdba
   SHUTDOWN IMMEDIATE
   STARTUP NOMOUNT pfile='/u01/app/oracle/product/12.0.1/db_home1/dbs/initorclstby.ora'
   QUIT

That should put the standby database into a state where it's ready to take the rman connection.

I then go back to the primary server and start rman

rman
CONNECT TARGET sys/<password>@orclprmy
CONNECT TARGET sys/<password>@orclstby

And then I'm in trouble. RMAN comes back with

RMAN-04006: error from auxiliary database: ORA-01017: invalid username/password; logon denied

I've checked an on standby REMOTE_LOGIN_PASSWORD='EXCLUSIVE', so as far as I can tell it should be using the same password file as primary, and I'm using the same password for everything anyway (it's a proof of concept piece).

Trying sqlplus sys/password@orclstby  from primary fails as well:-

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86-64 Error: 2: No such file or directory

What's really annoying is that I did have this working a while ago, but I must have changed something and I can't work out what it is.

Where should I be looking to see why the standby isn't using the password file (or if it is, why I can't use the password from primary).

I've been working on this for a couple of days now, so it could be I'm missing something obvious.

Many thanks,

David

The problem was down to the TNSNAMES.ORA referring to the SERVICE rather than the SID.


Finding active customers each year I have a database with the following:

|Customer number| |Sales date| |Cancel date|

From this I want to count the number of active customers each year for lets say 10 years back.
If I want to do this for a single year I can do this (I think):

SELECT
    customernumber
FROM
    customers
WHERE
    Datepart(year, salesdate)<2015
    AND (Datepart(year, CancelDate)>2015 or Datepart(year, CancelDate) IS NULL)

But how do I do this for all years available?

You need a tally table or calendar table that includes all years (that you want to report on).  Call the table Allyears or something like that.  As a field, you could put something like yearstart datetime and populate it with January 1 of each year (consider yearend as well).  Then simply join the Allyears table to TestDates and display the years you want.  Note, you have to decide if they were a customer on January 1, do you count them for the year?  Or did they have to be a customer on Dec 31st to be counted.  That will determine how you should create the table and join it.

Apparently, I'm the only one who has trouble posting Sql code, the site or my company's security doesn't allow the edits when they contain code (sometimes).


How to remove xmlns from child elements with FOR XML I have a TSQL query that I use to generate a SOAP document. I am using two namespaces. Namely:
- http://schemas.xmlsoap.org/soap/envelope/ for "packaging" the soap document
- A custom one that describes the data contained within the body

Currently every single element including those in the body contain the soapnamespace reference which I don't need and want in there I only want it in the root element.

So my XML now looks like

<soapenv:Envelope xmlns="soapenv" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:pas="foobar">
    <soapenv:Header xmlns="soapenv" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:pas="foobar" />
            <soapenv:Body xmlns="soapenv" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:pas="foobar">
                    <pas:SomeObject xmlns="soapenv" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:pas="foobar">

and I want it to look like

<soapenv:Envelope xmlns="soapenv" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:pas="foobar">
    <soapenv:Header/>
            <soapenv:Body>
                    <pas:SomeObject>

In the query I am currently using

;WITH XMLNAMESPACES ( 'http://foobar' as pas,
      'http://schemas.xmlsoap.org/soap/envelope/' as soapenv
      )
SELECT (SELECT '' FOR XML PATH('soapenv:Header'),type ),
etc...
FOR XML PATH('soapenv:Envelope'), ELEMENTS

Vote on this connect item for a change of behavior.

You can use for xml explicit to build your XML instead.

Something like this:

;with DummyTable(id) as
(
  select 1 union all
  select 2
)

select 1 as tag,
       0 as parent,
       'http://schemas.xmlsoap.org/soap/envelope/' as [soapenv:Envelope!1!xmlns:soapenv],
       'http://foobar' as [soapenv:Envelope!1!xmlns:pas],
        null as [pas:Child!2!pas:ID]
union all
select 2,
       1,
       null,
       null,
       id
from DummyTable
for xml explicit

Result:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:pas="http://foobar">
  <pas:Child pas:ID="1" />
  <pas:Child pas:ID="2" />
</soapenv:Envelope>


How to make an existing column autoincrement in Oracle I have created a table in oracle database which I named USERS and I have created a column USER_ID with type NUMBER which i set as primary key.

How can I modify  the column USER_ID to make it auto increment by 1 ?

Any help please ?

If you insist on using IDENTITY, it is not be possible  - you cannot modify non-identity column to identity.  However,  Oracle lets you use sequence.nextval as default, so you may get similar functionality  :

CREATE SEQUENCE SEQ_USER START WITH 1000; --assuming max(USERS.user_id) =999
ALTER TABLE USERS MODIFY (USER_ID DEFAULT SEQ_USER.NEXTVAL);

It will work as GENERATED BY DEFAULT AS IDENTITY.


Rollback database to old one without backup I accidentally restored an SQL Server database from the wrong backup using the following statement:

RESTORE DATABASE  [" + conn.Database + "] FROM DISK='" + path_backup + "'"

This is the exact query I used. I'm not an expert, so I don't really know why this worked, but it actually did.

I did not use REPLACE. Is there any way that I can rollback to previous state of the database? I've read that REPLACE overwrites the database, does this mean that if I don't use it, there is a copy somewhere?

The short answer is "No".

If you have restored from backup then you have overwritten your database.

The only way to get your database back is to restore from a suitable backup.

If your database is in full recovery mode and you have the last full backup and subsequent transaction logs you may be able to do a point in time recovery to  a point more acceptable than when the last full backup was taken.

The REPLACE option is just a safeguard against accidentally overwriting the existing database with a backup taken from a different database. The restore will not be allowed in that case otherwise.


Merging 2 queries together I have 2 query results that need merged together where periods are alike, adding a the column 'seats', using a '0' the period doesn't exist. Before:

tdate      period   staff                      tdate     period   seats
-------------------------                    ---------------------------
20141001     9       1                       20141001      9        2
20141001     10      2                       20141001      10       4
20141001     11      7                       20141001      11       2
20141001     12      20                      20141001      14       16
20141001     13      22                      20141002      9        3
20141001     14      15                      20141002      10       7
20141002     8       1                       20141002      11       9
20141002     9       4                       20141002      12       20
20141002     10      9
20141002     11      12
20141002     12      18
20141002     13      14

With this:

DECLARE @max_hr INT =(SELECT Max(CAST(LEFT(StartingTime, 2) AS INT))
  FROM   view_TimeRecords_COMBINED);

WITH cte
     AS (SELECT Min(CAST(LEFT(EndingTime, 2) AS INT)) [period]
         FROM   view_TimeRecords_COMBINED a
         UNION ALL
         SELECT [period] + 1
         FROM   cte
         WHERE  [period] < @max_hr)

SELECT convert(char(8), StartingDate, 112) AS tDate, [period], Count(t.TimeKeeperStaffCode) AS Staff
        FROM cte a
        JOIN view_TimeRecords_COMBINED t
        ON a.[period] BETWEEN CAST(LEFT(StartingTime, 2) AS INT) AND CAST(LEFT(EndingTime, 2) AS INT)
        WHERE convert(char(8), StartingDate, 112) BETWEEN 20141002 AND 20141002 AND TimeKeeperJobFunctionCode NOT IN ('offic', 'maint', 'mgr', 'laun')
        GROUP  BY [period], t.StartingDate

SELECT convert(char(8), POSChitDate, 112) AS tDate, LEFT(POSChitTime, 2) AS period, SUM(Covers) AS seats
        FROM tblPOSChits s
        WHERE convert(char(8), POSChitDate, 112) BETWEEN 20141002 AND 20141002
        GROUP BY LEFT(POSChitTime, 2), POSChitDate

I need to get to this:

tdate      period   staff  seats
----------------------------------
20141001     9       1       2
20141001     10      2       4
20141001     11      7       2
20141001     12      20      0
20141001     13      22      0
20141001     14      15      16
20141002     8       1       0
20141002     9       4       3
20141002     10      9       7
20141002     11      12      9
20141002     12      18      20
20141002     13      14      0

I eventually will also need to group by the day,week & month if you need to take that in consideration as well. It tracks the number of people working -vs- people served by hour.
Using MSSQL 2014.

I was able to figure this out by just using multiple CTE's, then selecting what I needed to combine.


How do you change the location of the data files in SQL Server 2000? I have an old SQL Server 2000 machine in a clustered environment. The data files are on Disk S: which is running out of space.  I have another Disk B: in the same disk array which has lot of space.  I want SQL Server to use the Disk B: instead of Disk S:

How do I setup SQL Server to store the data files on Disk B?

Any help would be greatly appreciated.

Thanks.

If you can plan some downtime, I would unattach the database from the server, move the physical files and then reattach the database.

Right click on the database --> all tasks --> detach database.
Move files
Right click on 'Databases' --> all tasks --> attach database.

The other option you have is to add another data file on the b: drive (right click on database --> properties -- > data files). Once you do that, you can move some of your tables over there. Easiest way to do that is to do 'select into' on the second drive, then do a rename of the first and second table.


SQL Server: why to use a backup device? When taking backups from databases, is it "good practice" to use logical backup device for backup location? What is the benefit of using backup devices?

I prefer one file per backup

DB_FULL_yyyymmddhhnn.bak
DB_DIFF_yyyymmddhhnn.bak
DB_LOG_yyyymmddhhnn.bak

I don't want to:

query a backup device to see what is stored there
copy an entire backup device to get one database
FTP or otherwise shift more I need around the place

If you have TB size database, partitions, filegroups and want to do partial backups/restores then these problems are multiplied. In a DR situation, I want everything simple and clear.

Most (large and small) shops I've been in do this: it is simpler to deal with self describing files.

The "device" concept goes back to Sybase, SQL Server 6.5 and earlier. Here you had disk devices too Separate MDFs and LDFs were introduce with SQL Server 7


Increased size of database dump even though LESS consumed storage For various reasons I've migrated my database schema from 4 tables with some additional indexes to keep integrity and stuff to 1 table only. That made some of the former used indexes obsolete and resulted in overall less consumed storage:
The old schema consumed ~42 GiB, while the new is ~16 GiB without the formerly available indexes and ~25 GiB with the same logical indexes. Though, a created dump of the new schema has increased from ~5,52 GiB
to 6,38 GiB. Of course I'm using the same settings to create both dumps:

pg_dump.exe "--username=%USERNAME%" "--encoding=UTF-8" "--compress=9" "--format=c" "--dbname=%DB_NAME%" > "%DMP_PATH%"

My expectation was that the dump would be smaller as well, because the data itself is the same, while lots of duplicate IDs, obsolete indexes etc. in not available tables anymore have been removed.
Any explanation for the increased dump size? Thanks!
The tables themself were changed like the following:
CREATE TABLE clt_rec
(
  id          bigserial                 NOT NULL,
  oms_rec     bigint                    NOT NULL,
  captured_at timestamp with time zone  NOT NULL,
  rssi        smallint                  NOT NULL
);

CREATE TABLE oms_rec
(
  id        bigserial NOT NULL,
  meter     integer   NOT NULL,
  encrypted bytea,
  decrypted bytea
);

CREATE TABLE clt_rec_src
(
  id          bigserial NOT NULL,
  real_estate integer   NOT NULL,
  collector   integer   NOT NULL,
  clt_rec     bigserial
);

CREATE TABLE meter_status_history
(
  id      serial  NOT NULL,
  oms_rec bigint  NOT NULL,
  status  smallint
);

vs.
CREATE TABLE datagram
(
  id            bigserial                 NOT NULL,
  src_re        integer                   NOT NULL,
  src_clt       integer                   NOT NULL,
  src_meter     integer                   NOT NULL,
  captured_at   timestamp with time zone  NOT NULL,
  captured_rssi smallint                  NOT NULL,
  oms_status    smallint                  NOT NULL,
  oms_enc       bytea,
  oms_dec       bytea
);

While I don't have a detailed explanation myself, there's the following hint in the docs:

For the custom archive format, this specifies compression of individual table-data segments[...]

I've changed the layout of my tables entirely and most likely have changed the "individual table-data segments" as well. So I did some further testing and got the following numbers. It's always OLD vs. NEW schema for the schema documented earlier:
--compress=0: 25  vs. 20  GiB
--compress=?: 5,6 vs. 6,5 GiB
--compress=9: 5,5 vs. 6,4 GiB

? means default settings, meaning --compress has been REMOVED from the shell command.
As can be seen, pretty much as soon as compression comes into play, the new table layout is less efficient, even though in OLD and NEW schema exactly the same bytea were used, have been placed at the end of their individual tables and alike. But things become more interesting with another version of my schema, which as well indicates a strong relationship of dump size, compression and table layout/size.
I've changed the new table datagram to be a partitioned one with partitions containing rows per year and per half-year. Each partition contains far less rows than before this way and while I only tested with --compress=9 this time, the numbers are quite interesting:
unpartitioned:     6,4 GiB
half-yearly parts: 4,8 GiB
yearly parts:      4,8 GiB

The interesting part this time is that the table layout for all partitions is the same like before, only the number of rows per table is different. Though, the number of rows overall is the same like before, the same data, IDs etc. Though, this time the dump really is smaller than with the OLD schema containing far more data because of duplicate IDs and stuff.
I wouldn't have expected table layout to be that important.


How to store many smallish time-series in a relational DB? Input Data

Multiple Tests Beds generate measurement data of various complexity.

In it's most basic form, not considering any meta-data, one measurement on a Test Bed will be a small (1 - a few thousand samples) time-series with a couple of dozen channels/signals/attributes per sample.

Measurements across time and Test Beds will have a similar set of signals, but not always the same as sensors are added and removed for the test setups.

Data volume

Currently we estimate our data rate at 6 testbeds x 4 test per hour x 12 hours a day x 4000 samples per test == 1,152,000 samples per day x 365 == 420,480,000 samples per year

_ x 48 columns per sample (currently 32 bit floats, mostly) ~~~ 75 GB per year

(columns in this case refers to channel/signal)

If/When more testbeds are added the data volume might increase accordingly.

Data Input

The test beds generate the data locally and the data is then imported asynchronously into the db. (A few thousand samples might be generated in a the time of one second, then reviewed locally and then either scratched or imported.)

Queries

We expect queries to be mostly on aggregates of the single measurements. I.e., you like to find all measurements (each having 4k samples) where e.g. the mean of channel_output_voltage is within a certain range.

Database layout?

What is a good way to set up tables for this? What factors have to be taken into account?

Theoretically I could go with one table per measurement generating 100,000 tables per year, but it doesn't strike me as a good idea.

Or I could stick everything into one big table (with hundreds of columns) that has room for all channels and channels get added as needed: One row per sample. Unused channels remain NULL.

MEASUREMENTS
------------
measurement_id } PK
time_stamp     }
channel_1 (may be NULL for a certain measurement_id ...)
channel_2
...
channel_n(+1)

Or I could go with an approach of having one table for the samples (timestamps) and one table containing all the values: (one row per sample in MEASUREMENTS table and n rows per sample in the SAMPLE_VALUES table)

SAMPLES            SAMPLE_VALUES
------------       -------------
measurement_id     sample_id
time_stamp         channel_id (links to a channels table where there is a name etc.)
sample_id          channel_value

What other options are there? How to further investigate which option we should choose?

Database products

Due to customer constraints we would like to put this in MS SQLS or Oracle.

From one answer:

  Don't store the raw data, only store aggregates. Seriously.

This assumes that there is a meaningful way to determine ex antes what queries the customer is going to want to run against their data. No way :-)

Are your queries supposed to collect data for each month/year?

You can use partitions to store your information in different physical files.  Partitions can increase the speed of SELECT statements when you only need information about a specific period. http://msdn.microsoft.com/en-us/library/ms345146%28v=sql.90%29.aspx

When creating a partitioned table in Microsoft SQL Server you can also create different file groups on different physical locations and back those up separately.

With regards to your question about the database design, you may want to read about normalization here: http://en.wikipedia.org/wiki/Database_normalization


What's the reason for using double inner join in this SQL statement? I am looking at this legacy SQL query. The bit I am not able to get is why it's inner joining same table twice on the same columns. I am talking about Table1 and Table1 joined with alias "Table1Alias",

SELECT DISTINCT othercolumns,
                Table1Alias.columna
FROM   maintable
       INNER JOIN secondarytable
               ON maintable.id1 = secondarytable.a_id1
       INNER JOIN table1
               ON secondarytable.id2 = table1.id3
       INNER JOIN table1 Table1Alias
               ON secondarytable.id2 = Table1Alias.id3
       INNER JOIN thirdtable
               ON table1.id4 = thirdtable.id5
       INNER JOIN fourthtable
               ON thirdtable.id6 = fourthtable.id7
       INNER JOIN fivetable
               ON thirdtable.id8 = fivetable.id9
       INNER JOIN sixthtable
               ON Table1Alias.columna = sixthtable.id10
       LEFT JOIN seventhtable
              ON thirdtable.id11 = seventhtable.id12
WHERE  LEFT(secondarytable.type123, 2) BETWEEN '01' AND '09'
       AND secondarytable.type456 = 'cate'
       AND table1.type = '0'
       AND Table1Alias.columna = 'conn'

It might help to rewrite the query like this, so it is obvious that the 2 joins are different, i.e. the joins are to different subsets (of the same table):

FROM   maintable
       INNER JOIN secondarytable
               ON maintable.id1 = secondarytable.a_id1
       INNER JOIN table1
               ON secondarytable.id2 = table1.id3
              AND table1.type = '0'
       INNER JOIN table1 Table1Alias
               ON secondarytable.id2 = Table1Alias.id3
              AND Table1Alias.columna = 'conn'
       INNER JOIN
       ...
WHERE  LEFT(secondarytable.type123, 2) BETWEEN '01' AND '09'
       AND secondarytable.type456 = 'cate'


What's the least risky way to Select on production environment? My goal is to use selects on production without affecting anything else.

When I was new to SQL, I just wrote simple selects, but later I learned this can cause locks.

If I don't care about dirty reads, I can use nolocks on every select or set transaction isolation level to read uncommited.

But I do care about dirty reads, so I went with isolation level snapshot.
Isolation level snapshot writes into tempdb and I don't know if that's a problem or not (extra writing to database means slower DB and less space?)

So what is the least intrusive way to write a select on DB, where I cannot afford slowing it down or creating shared locks? Should I go back to isolation level read uncommited on every query?

This is asked from developer point of view. I don't know details about DB settings, but want to use selects in a way that will cause the least trouble.

The correct answer will depend on a number of things:

The size of your database.
The size of your buffer pool compared to your database.
The volume and nature of the background (ie: not you) query activity.
The nature of the queries you want to run.
What will you bottleneck on first (CPU, I/O, tempdb throughput).
How much extra latency you are willing to tolerate in your normal workload.

You should always get an estimated query plan and look for intensive operations, such as scans of large tables/indexes, or sorts of a large number of rows. One of the most important considerations when working with a live, heavily used database is to avoid driving pages out of the buffer pool by running a query that needs to bring a lot of data into cache.

If you are concerned about your select queries going parallel and consuming excessive CPU, you can limit your query to a single core by adding OPTION (MAXDOP 1) to the end of your query.

If you don't have Read Committed Snapshot enabled on your database, see if you can get that turned on. That will give you snapshot-like behaviour on your selects, so that you will not block writers. However if you are running long ad-hoc queries and there is a lot of write activity, you'll get a lot of data written to tempdb.

You could also investigate using Resource Governor to limit yourself.


Having trouble starting my Oracle database I'm a programming student and I just started learning databases in college. However, everytime I try connecting to my databse in Oracle SQL Developer I have this message:

  ORA-12505, TNS:listener does not currently know of SID given in
  connect descriptor.

After posting here, we came to the conclusion that the databse wasn't properly started. When I try to start it from the cmd I get those errors:

When I type "startup" in the SQL command prompt

  ORA-01078: failure in processing system parameters ORA-00600: internal
  error code, arguments: [kspifisp:ksfdread_resilver], [0x000A9F388],
  [0x000A9F000], [512], [], [], [], [], [], [], [], []

When I type "select instance_name, status from v$instance;" in the SQL command prompt

  ERROR at line 1: ORA-01034: ORACLE not available Process ID: 0 Session
  ID: 0 Serial number: 0

I've seen that I could go into the net manager in "Programs > Oracle - HOME_NAME" but I don't have this in my programs list. Of course I tried uninstalling and reinstalling the database several times.

I've done the same installation on my PC at home and everything works fine I just need to make it work on my school laptop. I'm currently on Windows 10 installed on a Macbook. The database I installed is Oracle Database Express Edition (XE) Release 11.2.0.2.0 (11gR2).

Here is the 'init.XE.ora' file

SPFILE='C:\oraclexe\app\oracle\product\11.2.0\server\dbs/spfileXE.ora'

If you are running XE on windows the folder delimiters have to be consistent
Instead of

SPFILE='C:\oraclexe\app\oracle\product\11.2.0\server\dbs/spfileXE.ora'

Use

SPFILE='C:\oraclexe\app\oracle\product\11.2.0\server\dbs\spfileXE.ora'

The best suggestion is from thatJeffSmith. Use the prebuilt VM's from Oracle in their VM product Virtual Box

Also you can see if your Oracle is successfully installed by finding services.msc and looking for a windows service with Oracle in the name.  There should be a listener and a database service. The database service has to be running before you can connect from your windows vm and the listener has to be up to connect from other devices.


use DBCC SHRINKDATABASE after rebuild / reindex I have little free disk space.

Why shouldn't you use DBCC SHRINKDATABASE (DBName, TRUNCATEONLY) after every rebuild/reindex operation?

Because your database will grow again. You are not fixing anything by shrinking, because as you stated, it will grow out after the next rebuild operation anyway.

What are you going to do when you run out of free space before your next rebuild operation? Or you cannot run your rebuild / reindex anymore because of the limited disk space?

Shrinking use cases are very limited, and is considered a bad practice, not only because your db will grow out again.

Take a look at this Question for more info why you should not do this.

I suggest adding space to your disk / adding a new disk and moving your data to that disk if you can't add more space.

Also, there are some valid reasons as to not defrag your indexes:

https://groupby.org/conference-session-abstracts/why-defragmenting-your-indexes-isnt-helping/


Add autoincrement to existing PK I created a table in a DB that already exists in another DB. It was initially populated with the old DB data. The table's PK had to receive the values that already exist on those records, so it couldn't be autoincrement.

Now I need the new table to have its PK as autoincrement. But how can I do that after the PK already exists and have data?

The way I understand your question is that you have an existing table with a column that has up until now been populated with manual values, and now you want to (1) make this column an IDENTITY column, and (2) make sure that the IDENTITY starts from the most recent value in the existing rows.

First off, some test data to play with:

CREATE TABLE dbo.ident_test (
    id    int NOT NULL,
    xyz   varchar(10) NOT NULL,
    CONSTRAINT PK_ident_test PRIMARY KEY CLUSTERED (id)
);

INSERT INTO dbo.ident_test (id, xyz)
VALUES (1, 'test'),
       (2, 'test'),
       (5, 'test'),
       (6, 'test'),
       (10, 'test'),
       (18, 'test'),
       (19, 'test'),
       (20, 'test');

The goal is to make the table's primary key column, id, an IDENTITY column that will start at 21 for the next record that gets inserted. For this example, the column xyz represents all of the table's other columns.

Before you do anything, please read the warnings at the bottom of this post.

First off, in case something goes wrong:

BEGIN TRANSACTION;

Now, let's add a temporary work column, id_temp and set that column to the existing id column's values:

ALTER TABLE dbo.ident_test ADD id_temp int NULL;
UPDATE dbo.ident_test SET id_temp=id;

Next, we need to drop the existing id column (you can't just "add" an IDENTITY to an existing column, you have to create the column as an IDENTITY). The primary key also has to go, because the column depends on it.

ALTER TABLE dbo.ident_test DROP CONSTRAINT PK_ident_test;
ALTER TABLE dbo.ident_test DROP COLUMN id;

... and add the column again, this time as an IDENTITY, along with the primary key:

ALTER TABLE dbo.ident_test ADD id int IDENTITY(1, 1) NOT NULL;
ALTER TABLE dbo.ident_test ADD CONSTRAINT PK_ident_test PRIMARY KEY CLUSTERED (id);

Here's where it gets interesting. You can enable IDENTITY_INSERT on the table, which means that you can manually define the values of an IDENTITY column when you're inserting new rows (not updating existing rows, though).

SET IDENTITY_INSERT dbo.ident_test ON;

With that set, DELETE all the rows in the table, but the rows that you're deleting are OUTPUT right into the very same table - but with specific values for the id column (from the backup column).

DELETE FROM dbo.ident_test
OUTPUT deleted.id_temp AS id, deleted.xyz
INTO dbo.ident_test (id, xyz);

Once, done, turn IDENTITY_INSERT back off again.

SET IDENTITY_INSERT dbo.ident_test OFF;

Drop the temporary column that we added:

ALTER TABLE dbo.ident_test DROP COLUMN id_temp;

And finally, reseed the IDENTITY column, so the next record's id will resume after the highest existing number in the id column:

DECLARE @maxid int;
SELECT @maxid=MAX(id) FROM dbo.ident_test;
DBCC CHECKIDENT ("dbo.ident_test", RESEED, @maxid)

Checking the example table, the highest id number is 20.

SELECT * FROM dbo.ident_test;

Add another row and check its new IDENTITY:

INSERT INTO dbo.ident_test (xyz) VALUES ('New row');
SELECT * FROM dbo.ident_test;

In the example, the new row will have id=21. Finally, if you're happy, commit the transaction:

COMMIT TRANSACTION;

Important

This is not a trivial operation, and it carries quite a few risks that you should be aware of.

Do this in a dedicated test environment. Have backups. :)
I like to use BEGIN/COMMIT TRANSACTION because it prevents other processes from messing with the table while you're in the middle of changing it, and it gives you the possibility to roll everything back if something goes wrong. However, any other process that tries to access your table before you've committed your transaction will end up waiting. This may be pretty bad if you have a large table and/or you are on a production environment.
OUTPUT .. INTO won't work if your target table has foreign key constraints or any of a number of other features that I can't remember off the top of my head. You could instead off-load the data into a temporary table instead, and then insert it back into the original table. You might be able to use partition switching (even if you don't use partitions).
Run these statements one by one, not as a batch or in a stored procedure.
Try to think of other things that may depend on the id column that you're dropping and re-creating. Any indexes will have to be dropped and re-created (like we did with the primary key). Remember to script every index and constraint that you will need to recreate beforehand.
Disable any INSERT and DELETE triggers on the table.

If re-creating the table is an option:

If re-creating the table is an option for you, everything is a lot simpler:

Create the empty table, with the id column as an IDENTITY,
Set IDENTITY_INSERT ON for the table,
Populate the table,
Set IDENTITY_INSERT OFF, and
Reseed the identity.


MySQL 5.6 showing wrong "second_behind_master" mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.7.98
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000032
          Read_Master_Log_Pos: 277431436
               Relay_Log_File: mysqld-relay-bin.000039
                Relay_Log_Pos: 143474118
        Relay_Master_Log_File: mysql-bin.000031
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 963696099
              Relay_Log_Space: 530952469
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 98
                  Master_UUID:
             Master_Info_File: /mnt/vol1/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: updating
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

Here second_behind_master is showing 0 even currently slave is having Relay_Master_Log_File: mysql-bin.000031 and master is having Master_Log_File: mysql-bin.000032.

The Seconds_Behind_Master may be zero, but you should be looking at other parts of the SHOW SLAVE STATUS\G because it can tell you something about why it's zero. Let's discuss three(3) aspects that will shed some light on Replication's Seconds_Behind_Master.
ASPECT #1
There are occasions when a group of SQL commands are executed individually in the same one-second timeframe. Why does this happen on a Slave?
Keep in mind that the Master may have executed many INSERTs, UPDATEs, and DELETEs, in parallel. However, the binary logs records them as serialized events. An event will include the TIMESTAMP of the SQL command followed by the SQL Command.
If 30 DML commands each took 1 second to complete on a Master at 1399212601 (2014-05-04 10:10:01), they get recorded in the binary logs with the timestamp 1399212601.
On the Slave, when each command is read one-by-one, the timestamp will be 1399212601 for each command. Yet, real-time clock on the server will be steadily increasing. Seconds_Behind_Master is nothing more than UNIX_TIMESTAMP(NOW()) - 1399212601. This is the manifestation of Replication Lag.
ASPECT #2
There are occasions when a group of SQL commands are executed as series of row changes.
This could easily happen with row based replication (binlog_format is set to ROW instead of STATEMENT). Individual row changes bloat binary logs and result in same replication lag as stated in ASPECT #1
ASPECT #3
Here are three posts where I discuss how Replication handles LOAD DATA INFILE

Apr 18, 2013 : Loading data in mysql using LOAD DATA INFILE, replication safe?
Apr 02, 2013 : MySQL slave stuck in "Reading event from the relay log"?
Jan 22, 2012 : MySql shell command not replicated to slave

When you execute LOAD DATA INFILE on a Master, the Master will copy the entire CSV file as binlog events into the Binary Logs. The next binlog event recorded is the LOAD DATA INFILE command itself.
The Slave will see a binlog event in its relay logs that will alert the Slave to read the entire CSV file. Then, when it see the LOAD DATA INFILE, it executes it against the temp CSV file in created.
During the manifestation of the temp CSV file, the Seconds_Behind_Master will read 0. Then, when the entire CSV file is made and the LOAD DATA INFILE begins execution, the Seconds_Behind_Master will just increase by 1 or 2. It will do a punctuated jump to hundreds or even thousands of seconds.
SUMMARY
Binary Logs are are to handle group commits for better performance. Notwithstanding, you should looking at the following

Master_Log_File : Binary Log of the Last Binlog Event recorded on the Master read on the Slave
Read_Master_Log_Pos : Position of Last Binlog Event that was read from the Master and written to Relay Log on the Slave
Relay_Master_Log_File : Binary Log of the Last Binlog Event that was read from Master and was executed on Slave
Exec_Master_Log_Pos : Position of the Last Binlog Event that was read from Master and was executed on Slave
Relay_Log_Space

If the Relay_Log_Space is changing, then the IO Thread is reading just fine from the network. You can also see that by watching either Master_Log_File or Read_Master_Log_Pos  or both changing.
If the Relay_Master_Log_File and/or Exec_Master_Log_Pos is changing, then the command are begin executed on the Slave.
Therefore, once Seconds_Behind_Master is 0 for a long time, look for changes in these five fields in the SHOW SLAVE STATUS\G.


Replicate a database to one secondary replica, but not the other secondary replica using Availability Group I have a 3 nodes cluster in an availability group.  There are two databases on the primary node.  One database I want to replicate to the 2 secondary nodes.  Another database I just want to replicate it to 1 secondary replica, not the other.  In the wizard, you have to select both nodes.  Is there a way to set it up using T-SQL so that one database only replicate to one secondary node, but not the 3rd node?

Thanks!

You can setup 2 different availability groups and add databases accordingly, so that way you can manage it separately.


Factors to consider while Rebuild/Reorganize of index in SQL Server 2012 I am getting confused with choosing Index Reorganize/Rebuilding of indexes based on avg_fragmentation_in_percent returned by sys.dm_db_index_physical_stats dm function.

Msdn says:

And also, avg_fragmentation_in_percent value says percentage of logical fragmentation (This is the percentage of out-of-order pages in the leaf pages of an index) existed in index.

Reorganize of index will always fixes physical ordering of pages. so even avg_fragmentation_in_percent is greater than 30% also I can consider Reorganize of index. It will be decreasing the percentage of fragmentation in index.

Please look below example

SELECT
    database_id, object_id,
    avg_fragmentation_in_percent,
    avg_page_space_used_in_percent,
    page_count,
    avg_fragment_size_in_pages
FROM
    SYS.Dm_db_index_physical_stats (Db_id('BPIGTN_GAL_APP_TST'), Object_id('NM_PPA_PROJECTION_MASTER'), NULL, NULL, 'SAMPLED')

Result

 Database_id Object_id  avg_fragmentation_in_percent  avg_page_space_used_in_percent    page_count  avg_fragment_size_in_pages
    37       913490383        99.36                           60.15                      314           1.003

In above example, We can see 99 % of average fragmentation it means index is having more logical fragmentation(Unordering of pages is more).

So I am going to reorganizing of index which we have seen in above image.

Please look below example for further.

ALTER INDEX PK_NM_PPA_PROJECTION_MASTER_PROJECTION_DETAILS_SID_RS_CONTRACT_SID ON NM_PPA_PROJECTION_MASTER REORGANIZE
GO

SELECT database_id,
       object_id,
       avg_fragmentation_in_percent,
       avg_page_space_used_in_percent,
       page_count,
       avg_fragment_size_in_pages
FROM   SYS.Dm_db_index_physical_stats (Db_id('BPIGTN_GAL_APP_TST'), Object_id('NM_PPA_PROJECTION_MASTER'), NULL, NULL, 'SAMPLED')

Result

Database_id Object_id   avg_fragmentation_in_percent    avg_page_space_used_in_percent  page_count  avg_fragment_size_in_pages
37           913490383           4.18                                       98.91           191       9.55

Above, we have re-organized the index so after reorganize average_fragmentation reduced to 4 percent and also space used is increased from 60 percent to 98 percent.

Msdn Suggesting Avg_Fragmentation_in_percent value to be consider for Reorganize/Rebuild of index.

But Avg_Fragmentation_In_Percent value says only logical fragmentation not amount of space which is not used in index.

So, I am a little bit confused about choosing between Re-organize/Re-build of index.

Can we consider both Avg_fragmentation_in_percent and Avg_page_space_used_in_percent values for choosing Rebuild an index or Reorganize an index?

Any one please suggest me the exact parameters to be consider for choosing Rebuild or Re-Organize of an index?

I'm going to go in a different direction than other answers and ask: After your defragmentation routine has run for however many minutes or hours, and used all sorts of resources (CPU, memory, disk, probably tempdb), how do you quantify and compare the expenditure of time and server resources to defragment indexes to any improvements you've gained from doing so?

I realize this doesn't answer your question, but I want you to think carefully about what you're doing rather than get yourself all worked up and your server all beat up about a non-issue.

There's a weird obsession (and believe me, I used to have it, too) with SQL Server users around index fragmentation. It's usually based on advice from Microsoft from 15 years ago and lots of hysterical responses on Q&A forums (this one included).

People have had the numbers you reference (5% to reorg, 30% to rebuild) pounded into their head with religious fervor forever. So they keep doing it. I've seen it cause all sorts of problems, too. Blocking and deadlocks, corruption, interruptions to production workload (long running maintenance), and worst of all: performing index maintenance at the exclusion of more important maintenance, like DBCC CHECKDB.

The one good thing that index rebuilds do is update statistics, which (simplifying greatly) will usually give the optimizer much better information about the data it's working with, and (again, simplifying greatly) can chase a bad execution plan out of the cache.

People will also do counterproductive things in maintenance plans like rebuild all their indexes, reorganize all their indexes, and then update statistics. I realize this likely isn't you, just adding it for completeness.

There are also more considerations than just fragmentation percent to rebuild or reorg:

Do queries even use this index?
Do I really want to reorg and LOB compact a 50+ GB index (single threaded, ew)?
Am I on Standard Edition where rebuilds are OFFLINE and can cause blocking?

For a bit of background, check out the blog posts here. Full disclosure; it's the company I work for, though people other than me have written a lot about it. There's even a post in there about when index fragmentation does matter.

Other database platforms have had better advice about this topic for a long time.

EDIT: The best way to have index fragmentation never ever in a million billion years matter: cache all your data in RAM.


Can I install SQL Server 2012 on a machine with 2008 R2 already? I have been using SQL Server 2008 R2 for quite a while, and it is working great. Now I need to use SQL Server 2012. Will this effect my existing databases? How much risk is involved in installing SQL Server 2012 on a machine that has SQL Server 2008 R2 running?

You can simply install a new, named instance, and the two instances can live side-by-side with no risk - just be sure that your connections strings etc. are clear and correct (many problems arise from connecting to LOCALHOST when you meant to connect to LOCALHOST\OtherInstance).

Depending on resources you may want to be sure to cap both servers using the max server memory configuration option, or perhaps even only running one instance at a time. I assume this is on your local development environment.

I'd suggest abandoning the 2008 R2 version of the client tools and using the 2012 version consistently. It has much better functionality and can manage both versions seamlessly.


Compare two columns in two databases to insert the content of a third column into a forth one I have two tables in  different databases:
b.dbo.table1
ID
Code

w.dbo.table2:
ID
Code2
ID2

I am in need of code that helps me to compare (Code) in table1 with (Code2) in table2. If they are the same then insert (ID) from table1 into (ID2) in table2.

It's not clear what's the link between your 2 tables.

If the link between your tables is through Code and Code2:

UPDATE T2 SET
    ID2 = T1.ID
FROM
    b.dbo.table1 AS T1
    INNER JOIN w.dbo.table2 AS T2 ON T1.Code = T2.Code2

If the link is through ID and the comparison by Code:

UPDATE T2 SET
    ID2 = T1.ID
FROM
    b.dbo.table1 AS T1
    INNER JOIN w.dbo.table2 AS T2 ON T1.ID = T2.ID
WHERE
    T1.Code = T2.Code2

I'm assuming that you meant an UPDATE of the column ID2 when the codes match and not creating a new row just with the ID2 value (which would require an INSERT operation).


Variable for element name I have this tSQL code which works OK:

SELECT
c.logguid,
a.b.value('./PropertyValue', 'varchar(max)') asd
  FROM [dnn].[dbo].[EventLog2] c

cross apply sss.nodes('/LogProperties/LogProperty[PropertyName=sql:variable("@x") and PropertyValue=sql:variable("@y")]') as a(b)

However, what I want to be able to do is pass in a dynamic list of many pairs of values which are OR'd between each one, i.e.

SELECT
c.logguid,
a.b.value('./PropertyValue', 'varchar(max)') asd
  FROM [dnn].[dbo].[EventLog2] c

cross apply sss.nodes(
'/LogProperties/LogProperty[PropertyName=sql:variable("@x") and PropertyValue=sql:variable("@y")
or
PropertyName=sql:variable("@a") and PropertyValue=sql:variable("@b")
]'
) as a(b)

Is there a way of doing this?

Any parameter that needs a variable list or array is probably a good candidate for a user-defined table type. I would create the type as:

CREATE TYPE [PropertyVariableTableType] AS TABLE (
    PropertyName nvarchar(255),
    PropertyValue nvarchar(255) )

Table types can be used as parameters to stored procedures just as any other type. You can then either join on the user-defined table or iterate the rows to dynamically build the query.


Is it possible to add Postgres extensions to AWS Aurora? I am trying to futureproof a Postgres database so it can be transferred to AWS Aurora.

I am using the guide extension uuid-ossp.

Is it possible to add PostGres extensions to AWS Aurora?

In particular, I am interested in whether the extension uuid-ossp can be used in Aurora.

Yes, you can create Postgres extensions in AWS Aurora Postgres. Yes, the extension uuid-ossp can be used in Aurora.

To get supported extension list in your AWS Aurora Postgres version, run this query:

SHOW rds.extensions;

or

select * from  pg_available_extensions;

And also check out PostgreSQL Extensions and Modules Supported on Amazon RDS  in the Amazon Relational Database Service User Guide for the list of extensions supported in AWS Aurora Postgres based on Postgres versions.

There is no difference in the extension usage for both community Postgres and Aurora Postgres if extension installed on the server. For uuid-ossp usage, please refer to F.44. uuid-ossp in the PostgreSQL documentation.


TNS_ADMIN / TNSNAMES location failover Is there a way to have multiple tnsnames.ora locations defined? We currently use a server path like TNS_ADMIN=\\192.168.1.100\infosys\oracle\tnsnames but are in a situation this week where that server had to be taken offline for a couple days.

I've tried stacking locations similar to PATH variable but it doesn't work:

> set TNS_ADMIN=z:\Common\oracle;\\192.168.1.100\infosys\oracle\tnsnames
> mctnsping ora-server

McTnsping Utility by Michel Cadot: Version 2018.08.17 on 18-OCT-2018 11:16:33
Copyright (c) Michel Cadot, 2016-2018. All rights reserved.

*** TNS-03514: Failed to find tnsnames.ora file

Single location is fine:

> set TNS_ADMIN=Z:\Common\oracle
> mctnsping ora-server

Used parameter files:
Z:\Common\oracle\tnsnames.ora
...snip...
Attempting to contact ora-server:1521
OK (201 msec)

How to have an alternate location without touching every client to change TNS_ADMIN?

[oracle@o71 ~]$ echo "TNS1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=o71.balazs.vm)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=TNS1)))" > ~/tnsnames1.ora
[oracle@o71 ~]$ echo "TNS1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=o72.balazs.vm)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=TNS2)))" > ~/tnsnames2.ora
[oracle@o71 ~]$ cat tnsnames1.ora
TNS1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=o71.balazs.vm)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=TNS1)))
[oracle@o71 ~]$ cat tnsnames2.ora
TNS1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=o72.balazs.vm)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=TNS2)))

I changed the HOST and SERVICE_NAME values for demonstrating how name resolution still works after making one of the files inaccesible.

Now include them with IFILE:

[oracle@o71 ~]$ echo -e "IFILE=/home/oracle/tnsnames1.ora\nIFILE=/home/oracle/tnsnames2.ora" > ~/tnsnames.ora
[oracle@o71 ~]$ cat ~/tnsnames.ora
IFILE=/home/oracle/tnsnames1.ora
IFILE=/home/oracle/tnsnames2.ora
[oracle@o71 ~]$ export TNS_ADMIN=/home/oracle

Test:

[oracle@o71 ~]$ tnsping tns1

TNS Ping Utility for Linux: Version 18.0.0.0.0 - Production on 18-OCT-2018 21:37:00

Copyright (c) 1997, 2018, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=o72.balazs.vm)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=TNS2)))
OK (10 msec)

tnsping picked up the last occurence of the same TNS alias. Now make this second file inaccessible:

[oracle@o71 ~]$ mv /home/oracle/tnsnames2.ora /home/oracle/tnsnames2.ora.corrupt

Test again:

[oracle@o71 ~]$ tnsping tns1

TNS Ping Utility for Linux: Version 18.0.0.0.0 - Production on 18-OCT-2018 21:37:19

Copyright (c) 1997, 2018, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=o71.balazs.vm)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=TNS1)))
OK (0 msec)

TNS resolution still works, but from the first file (HOST, SERVICE_NAME).

With this method you basically enumerate multiple files containing TNS aliases using the IFILE parameter, which was not designed for this, but it is something.


Find direct connections to database server instead of SQL Server Always On Listener We recently created a SQL Server Always On Availability Group. I have told developers to change their application's connection strings from main server to the listener. I want to check which applications (or servers) connect to main server and which applications connect to the listener. How can i do it?

I covered this through two main posts. The first gives you an idea how to see what is being used, currently. The second tells you how to find which connections are read only routed by exploiting the fact that SQL Server can listen on multiple ports and that read only routing accepts whatever endpoint url you give it which is directly given back to the client - this means you can setup specific items just for read only routing and report back on it.

It is much more accurate and no need to go through extended events. You're specifically looking for the second post, but they are related and will give you a better picture when combined together.

Finding What Availability Group Listeners Applications Are Using To Connect
Finding Which Connections Have Been Read Only Routed


Inconsistency between object explorer and available database dropdown I am working with SQL Server 2008R2.  I am able to see a database in the "available database" drop down, but not in the object explorer. I am able to query it, for example   select * from INFORMATION_SCHEMA.TABLES  is able to run within the database.

Has anyone seen this before?  I have tried right clicking on the SQL server within object explorer, and hitting refresh, but I am not seeing the database within the object explorer.  It happens to be the "distribution database" as mentioned here:

Is that something that ever shows up in the object explorer?

Any information would be greatly appreciated.

(follow-up from my comment to the OP)

This is what you should see:

Filter icon is grayed out because "Databases" is not selected.


Questions about Free Space Map of PostgreSQL Let's create a temporary table (I choose temporary table because autovacuum don't run for this kind of tables):
CREATE TEMP TABLE test (
  id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  value int
);
INSERT INTO test (value) SELECT 0 FROM generate_series(0, 250);
SELECT ctid, * FROM test;

We will see that the table consists of two pages:
(0,1)   1   0
(0,2)   2   0
...
(0,226) 226 0
(1,1)   227 0
...
(1,25)  251 0

Now if we update one row
UPDATE test SET value = -1 WHERE id = 1;
SELECT ctid, * FROM test WHERE value <> 0;

we will see that the new row version was inserted at the end of the table (in the second page, which has enough free space for this operation) and this is a standard PostgreSQL behavior (the old row verison from (0,1) was marked as dead)
(1,26)  1   -1

Let's check the free space of pages:
CREATE EXTENSION pg_freespacemap;
SELECT * FROM pg_freespace('test');

we get
0   0
1   0

i.e. no free space; documentations says that the Free Space Map (FSM) is updated after vacuum run.
Now if we update another row from the first page:
UPDATE test SET value = -1 WHERE id = 2;
SELECT ctid, * FROM test WHERE value <> 0;

we will see
(0,227) 2   -1
(1,26)  1   -1

The new row version was not added to the 2nd page but was added to the current page, because we made free space when we updated the record with id 1. But this approach does not correspond to my understanding of FSM. Here are my questions:

How PostgreSQL knew at the second UPDATE that there was free space in the first page if pg_freespace('test') returned zero? I thought that this function gives you the FSM which in turn is used in case of UPDATEs in order to decide which page has enough free space to store the new row version.
Generally I was thinking that even if first row was marked as dead it however occupies some space in the 1st page and this space will be freed only after vacuum. Thus I was expecting that the second UPDATE will also add the new row version to the 2nd page.

This will contain some PostgreSQL internals; sorry, you asked for it.
Autovacuum doesn't run on temporary tables (because they are invisible outside your session), and at any rate, one or two updates won't trigger autovacuum. Now it is VACUUM that creates and maintains the free space map, so there is no free space map in your case. pg_freespacemap does not error out, but reports that as 0.
Lacking a free space map, PostgreSQL has to look at the individual blocks to find a block with free space in it. That leaves the riddle why the second update could find space to create a tuple in block 0.
To answer the riddle, let's discuss your statements and their effect. You can verify all this using the pageinspect extension.

the first UPDATE:
UPDATE test SET value = -1 WHERE id = 1;

That marks tuple (0,1) as updated (it sets xmax) and creates the new tuple (1,26), just as you expected.

the following query:
SELECT ctid, * FROM test WHERE value <> 0;

This query can get a page lock on block 0 and performs heap pruning, that is a “micro-vacuum” that frees the space occupied by dead tuples. Note that it cannot remove (0,1), because it is still referenced in an index, but line pointer (0,1) becomes a “stub” marked LP_DEAD that does not occupy any space. The space occupied by that dead tuple is freed.
Note that if you don't run that SELECT, the following UPDATE will perform heap pruning on block 0, so the effect will be the same.

the second UPDATE:
UPDATE test SET value = -1 WHERE id = 2;

Lacking a free space map, the statement looks a block 0 and notices that there is enough free space, so it performs a HOT update and puts the new tuple into block 0.


filter table rows within range of related column I have a postgres database with the following table:
    CREATE TABLE data (
      id integer PRIMARY KEY,
      group_id integer NOT NULL,
      value double
    );

the data contained in it is like the following:
| id  | group_id | value |
| --- | -------- | ----- |
| 1   | 1        | 0     |
| 3   | 1        | 0.6   |
| 4   | 1        | 1     |
| 2   | 1        | 2     |
| 33  | 1        | 5     |
| 7   | 2        | 0.0   |
| 11  | 2        | 0.1   |
| 6   | 2        | 0.33  |
| 8   | 2        | 1.5   |
| 20  | 2        | 2     |

I need to get all the rows in a group_id between two id's values.
For example for the group_id 2 and ids 11 and 8 I should get:
| 11  | 2        | 0.1   |
| 6   | 2        | 0.33  |
| 8   | 2        | 1.5   |

I was looking for a cleaner solution than:
SELECT
  *
FROM
  data
WHERE
  group_id = 2 AND
value BETWEEN (SELECT value FROM data  WHERE id = 11)
      AND (SELECT value FROM data  WHERE id = 8)

and that possibly worked if the ids are reversed, like for the group_id 2 and ids 8 and 11 returned:
| 8   | 2        | 1.5   |
| 6   | 2        | 0.33  |
| 11  | 2        | 0.1   |

Fiddle
with user_defined_params as (
  select 2 as target_group_id,
  8 as first_id,
  11 as second_id
),
target_value_bounds AS (
  select min(value) as minval, max(value) as maxval
  from data where group_id=(select target_group_id from user_defined_params)
  AND ((id=(select first_id from user_defined_params))
       OR (id=(select second_id from user_defined_params)))
)
select * from data
where group_id=(select target_group_id from user_defined_params)
           AND value >= (select minval from target_value_bounds)
           AND value <= (select maxval from target_value_bounds);

If you're using this from a programming language, you'll definitely want to do away with the user_defined_params CTE and just use named query parameters.


Is there a way to set different destination order with mysql-router? I have innodb cluster setup with 3 mysql instances. Each instance is on different server and each server has mysql-router also.
I would like each mysql-router on each server to try to connect to the mysql instance  on the same server. Is this possible?
Currently all mysql-router will check the "destinations=metadata-cache://mycluster/default?role=PRIMARY" option and try to connect to the top server. Is there a way so each mysql-router trys to first try to connect to different instances? (the instance on the same server as mysql-router)

using different specific host order, rather than using metadata-cache in mysql-router config "destination" option on each mysql-router seems to do it.
ie.
destination=server1,server2,server3


Best way to use insert statement on Oracle I need to store addresses from customers and companies into different tables with the same columns. And I would like to know what is the best approach to use the same insert statement, but only by changing the name of the table.

Example:

CASE CPF
 WHEN NULL
  INSERT INTO COMPANIES_ADDRESS (ZIPCODE, STREET, NUMBER) VALUES (V_ZIPCODE, V_STREET, V_NUMBER)
 ELSE
  INSERT INTO CUSTOMERS_ADDRESS (ZIPCODE, STREET, NUMBER) VALUES (V_ZIPCODE, V_STREET, V_NUMBER)
END

insert first
when CPF is null
then into COMPANIES_ADDRESS (ZIPCODE, STREET, "NUMBER")
else into CUSTOMERS_ADDRESS (ZIPCODE, STREET, "NUMBER")
select  V_ZIPCOD, V_STREET, V_NUMBER
from    dual

P.s.
Using reserved word as identifiers (in this case NUMBER as a column name) is a really bad practice.


SQL Server 2008 R2: Restore latest backup to new database from script We have a database for each major version of our system, for each customer, for both development and test. Like so:

WHATEVER44_CUSTOMERA_DEV
WHATEVER44_CUSTOMERA_TEST
WHATEVER44_CUSTOMERB_DEV
WHATEVER44_CUSTOMERB_TEST
WHATEVER44_CUSTOMERC_DEV
WHATEVER44_CUSTOMERC_TEST

Once in a while, we restore the latest backup of each of these to new databases with a higher version number:

WHATEVER45_CUSTOMERA_DEV
WHATEVER45_CUSTOMERA_TEST
WHATEVER45_CUSTOMERB_DEV
WHATEVER45_CUSTOMERB_TEST
WHATEVER45_CUSTOMERC_DEV
WHATEVER45_CUSTOMERC_TEST

We do this in Management Studio by right-clicking on the existing database, Task > Restore.
It automatically selects the latest backup set and all we need to do is change the name of the database to restore to.

We want to automate this with a script that given a list of existing database names creates these new versions of the databases, without user interaction (apart from running the script). Is it possible?

This Is a Striped down version of the script I use to restore a great many databases at the same time. Hopefully I took out all of the parts that make it work for my scenario and left them with appropriate hints as to what to place in your version.

    -- Remove old DBs:
use [master]
go

DROP Database [YourDBName]
DROP Database [YourNextDBName]
DROP Database [AndSoOn]
DROP Database [AndSoForth]

GO

-- Create Restore List

Insert into [dbo].[BackupList] (DBName) Values ('YourDBName')
Insert into [dbo].[BackupList] (DBName) Values ('YourNextDBName')
Insert into [dbo].[BackupList] (DBName) Values ('AndSoOn')
Insert into [dbo].[BackupList] (DBName) Values ('AndSoForth')

GO

declare @counter [int]
declare @maxcounter [int]
declare @DBName [varchar](50)
declare @SQL1 [varchar](4000)
declare @SQL2 [varchar](4000)
declare @SQL3 [varchar](4000)
declare @SQL4 [varchar](4000)
set @counter = 0
select @maxcounter = max(id) from [dbo].[BackupList]

while @counter <= @maxcounter
BEGIN
                select @DBname = DBname from  .[dbo].[BackupList] where ID = @Counter

                IF @DBname LIKE '%WhateverYoursIsLike'
                BEGIN
                SET @SQL1 = 'RESTORE DATABASE '+ QUOTENAME(@DBName) + ' FROM  DISK = ''YourBack-UpLocation' + @DBName + '.bak'' WITH  FILE = 1,
                MOVE N''YourFileName'' TO ''YourRestoreLocation' + @DBName + '.mdf'',
                MOVE N''YourFileName_log'' TO ''YourRestoreLocation' + @DBName + '.ldf'''
                exec(@sql1)
                print(@sql1)
                END

                set @counter = @counter + 1
END

GO
Use [em_support]
GO
truncate table [em_support].[dbo].[BackupList]
GO
DBCC CHECKIDENT (BackupList, RESEED, 0)
GO

--> Modify DB properties
DECLARE @DBName SYSNAME
DECLARE @Login SYSNAME
DECLARE @SQL NVARCHAR(4000)
DECLARE curModifyProperties CURSOR FOR
                SELECT [name]
                FROM sys.databases
                WHERE [name] LIKE '%WhateverYoursIsLike'

OPEN curModifyProperties
FETCH NEXT FROM curModifyProperties
INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN

                --> BEG - Set trustworthy property
                SET @SQL = 'ALTER DATABASE '+QUOTENAME(@DBName)+' SET TRUSTWORTHY ON'
                EXEC (@SQL)
                --> END - Set trustworthy property

                --> BEG - Set database owner
                SET @SQL = QUOTENAME(@DBName)+'.[dbo].[sp_changedbowner] ''sa'''
                EXEC (@SQL)
                --> END - Set database owner

FETCH NEXT FROM curModifyProperties
INTO @DBName
END
CLOSE curModifyProperties
DEALLOCATE curModifyProperties
GO

I hope that helps or at least points you in the right direction.


Add Login NT AUTHORITY\NETWORK SERVICE to SQL Server I installed a SQL Server 2014 Developer Edition on a VM, because the Express Edition is limited to just 10GB. Now I've tried to conncet to that server remotely from my VM with SSMS. But I get an error that I cannot connec:

  (provider: SQL Network Interfaces, error: 28 - Server doesn't support
  requested protocol) (.Net SqlClient Data Provider)

On the Express Server there is an NT AUTHORITY\NETWORK SERVICE login. So my conclusion was to add NT AUTHORITY\NETWORK SERVICE to the logins, so I can access the server and databases with SSMS from a different PC, hoepfully.

I do find a NETWORK SERVICE user when searching for it. It then shows up as VM-THIS\NETWORK SERVICE. So no NT AUTHORITY.

When I try to add the VM-THIS\NETWORK SERVICE, on hitting OK, I get the following error:

  Windows NT user or group 'VM-THIS\NETWORK SERVICE' not found. Check
  the name again. (Microsoft SQL Server, Error: 15401)

Having a look at the error code couldn't help me solving this.

So on the one side I can find the user, but adding causes an error? What am I doing wrong?

The account will show up as just NETWORK SERVICE when you are looking at the local accounts via the UI (I recall). I'm not sure why you see one with the hostname but that would not be a system account on the local machine.

You can always just use T-SQL to add the login:

CREATE LOGIN [NT AUTHORITY\NETWORK SERVICE] FROM WINDOWS;

Then assign it whatever permissions you would like.


Select total count and percent Say have table like:

user_uuid | value | state
------------------+------+-------
uuid_1    |  111 | t
uuid_1    |  111 | f
uuid_1    |  111 | f
uuid_1    |  111 | f
uuid_2    |  333 | t
uuid_2    |  444 | t
uuid_2    |  555 | f
uuid_3    |  666 | t
uuid_4    |  777 | f
uuid_1    |  222 | t
uuid_1    |  222 | t

Task is select total count for each user_uuid and percent from total count to count with some value and state.
For example:
total count for uuid_1 is 6.

We need to get percent with value = 111 and status 'true'.

For 'uuid_1' count is 1. So need to calculate percent from 1 to 6

For 'uuid_2' and others counts are 0 so percents are 0 too.

I tryid this SQL:

 SELECT tq.user_uuid
     , COUNT(1) AS total
     , t.cnt
  FROM test_query tq
 CROSS
  JOIN (SELECT COUNT(user_uuid) AS cnt FROM test_query ) AS t
 GROUP
    BY tq.user_uuid, t.cnt;

But i cannot get how to select in JOIN part count for some conditions (like state=true, value=111)

You can use a SUM(CASE... for this purpose.

SELECT
    user_uuid,
    COUNT(*) cnt,
    ROUND (100.0 * (SUM(CASE WHEN value = '111' AND state = true THEN 1 ELSE 0 END)) / COUNT(*), 1) AS percent
FROM
    t
GROUP BY
    user_uuid;

user_uuid | cnt | percent
:-------- | --: | ------:
uuid_2    |   3 |     0.0
uuid_3    |   1 |     0.0
uuid_1    |   6 |    16.7
uuid_4    |   1 |     0.0

As a_horse_with_no_name has pointed out, you can use COUNT(*) FILTER(WHERE...) syntax too.

SELECT
    user_uuid,
    COUNT(*) cnt,
    ROUND (100.0 * (COUNT(*) FILTER (WHERE value = '111' AND state = true)) / COUNT(*), 1) AS percent
FROM
    t
GROUP BY
    user_uuid;

db<>fiddle here


How to test encryption on Amazon RDS MySQL? Our company is creating a MySQL RDS with encryption enabled. Now, as we are new to RDS, we have couple of questions related to RDS.

Once we create a MySQL RDS with encryption enabled, how will we able to confirm that the RDS is actually encrypted?
Whether encrypted RDs is disk/file level encrypted or db level encrypted? I have done some searching, but could bot able to get a clear answer.

Whether encrypted RDs is disk/file level encrypted or db level encrypted? I have done some searching, but could bot able to get a clear answer.

The encryption is done at the file/disk level. From this page, emphasis mine:

  Amazon RDS encrypted instances provide an additional layer of data protection by securing your data from unauthorized access to the underlying storage.

Also to highlight exactly what is encrypted, we have this quote:

  All logs, backups, and snapshots are encrypted for an Amazon RDS encrypted instance. A Read Replica of an Amazon RDS encrypted instance is also encrypted using the same key as the master instance.

To answer your question about confirming that the RDS is encrypted, because you do not have access to the OS that RDS runs on the only method you have is to verify the backups/snapshots are encrypted.

To download a snapshot, you can use the console (or the rds-copy-db-snapshot tool).

The keys used to encrypt/decrypt them should be found in the KMS section of the console.


Database corruption: QueryStore internal table This morning, the following email alert was received:

  DATE/TIME:    2/28/2018 9:26:42 AM

  DESCRIPTION:  Attempt to fetch logical page (1:3948712) in database 9
  failed. It belongs to allocation unit 72057594045857792 not to
  72059184917512192.

  COMMENT:  (None)

  JOB RUN:  SQL Sentry 2.0 Alert Trap

Looking in the event log of the secondary replica there are three occurrences of the same message:

  Source        spid138

  Message Attempt to fetch logical page (1:3948712) in database 9
  failed. It belongs to allocation unit 72057594045857792 not to
  72059184917512192.

Running the following on the secondary replica (2 node synchronous Availability Group):

DBCC TRACEON(3604)
dbcc page (9, 1,3948712,3)
go
DBCC TRACEOff(3604)

Snippet of the results from either replica:

Page @0x00000070DAB8C000

m_pageId = (1:3948712)              m_headerVersion = 1
m_type = 3 m_typeFlagBits = 0x0                m_level = 0
m_flagBits = 0x8200 m_objId (AllocUnitId.idObj) = 129   m_indexId
(AllocUnitId.idInd) = 256  Metadata: AllocUnitId = 72057594046382080
Metadata: PartitionId = 72057594040811520
Metadata: IndexId = 1 Metadata: ObjectId = 197575742
m_prevPage = 0:0)                  m_nextPage = (0:0) pminlen = 0
m_slotCnt = 2                       m_freeCnt = 1634 m_freeData = 6568
m_reservedCnt = 0                   m_lsn = (46041:1506360:18)
m_xactReserved = 0                  m_xdesId = (0:0)
m_ghostRecCnt = 0 m_tornBits = -99702035              DB Frag ID = 1

Running the following on the primary replica:

select OBJECT_NAME (197575742)

plan_persist_plan

Questions

Am I right in saying that I have a clustered index corruption of the plan_persist_plan table which is part of Query Store?
Is the best/only fix to run the following:

ALTER DATABASE MyDatabase SET QUERY_STORE CLEAR;

If #2 is the best fix, is there any good way of preserving the data in Query Store that would be deleted?
Does this kind of corruption indicate a problem with the IO subsystem?

Other info

I have QueryStore enabled obviously, it has a capacity of 350MB, is in Read-Write mode currently, flush interval 15 minutes, stats collection hourly, Capture mode ALL, Auto size based cleanup, 5 day stale query threshold.
DB id 9 is a business critical user database
The error details are Error: 605, Severity: 21, State: 3.

I have checked the Windows System Event log as per the guidance. This has yielded only "Informational" events, no errors.

DBCC CHECKTABLE ('sys.plan_persist_plan');

results:

DBCC results for 'sys.plan_persist_plan'.
There are 12562 rows in 240 pages for object "sys.plan_persist_plan".
DBCC execution completed.
If DBCC printed error messages, contact your system administrator.

I cannot establish the correct command to rebuild the index, the following does not work:

ALTER INDEX PK_plan_persist_plan_cidx ON sys.plan_persist_plan REBUILD;

As noted in my comment above I had a similar corruption issue with a query store internal table.

As you yourself have suggested I used ALTER DATABASE MyDatabase SET QUERY_STORE CLEAR; to attempt to fix the issue and that did work fine.  In SQL Server 2017, Microsoft added a repair procedure that can be attempted prior to clearing the data: sp_query_store_consistency_check (source)

If you want to preserve the data then probably the only method is to copy the tables - I can't find anyone who has created a script for that.

Usually with corruption I too would be worried about my disks, but in this case I'm a little suspicious that the issue is with query store itself.


Issue after moving the ib_logfile1 and ib_logfile0 files I wanted to increase the innodb_log_file_size for better performance. And I increased it from the default value to 256 MB. As usual I got the error saying InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes. So I moved these files to different location and restarted the MySQL server. It worked. Before shutting down the MySQL, server I also made innodb_fast_shutdown  =  0.

But I have a doubt, after MySQL shutdown can we expect mysql is an consistent state and all the data that were in the ib_logfile0  and ib_logfile1 have been written to data files. So that I can delete these files or should I do something else also.

And if the answer is MySQL will be in consistent state than why these files contain some data even after graceful shutdown and start up with innodb_fast_shutdown  =  0.

[EDIT details]  giving the steps i have done

  Steps i followed:

  SET GLOBAL innodb_fast_shutdown = 0
  /etc/init.d/mysqld stop
  rm -f /var/lib/mysql/ib_logfile0
  rm -f /var/lib/mysql/ib_logfile1
  /etc/init.d/mysqld start

Is the above step is fine? if yes then how come after the clean shutdown and start up the log files contains some data. Ideally it should be empty.

How are you coming to the conclusion that "these files contain some data even after graceful shutdown"?

Even if it's been fully flushed and uncommitted transactions cleared, etc., by using innodb_fast_shutdown = 0, the "flushing" probably means that the file is "empty" as far as MySQL is concerned, but it won't physically be full of zeroes.

If you start MySQL up again, it will probably zip through the files to the latest Log Sequence Number and realise that there's no more work to be done (someone familiar with the source code that handles the innodb logs would be able to confirm/deny this).

Your steps should be absolutely fine, but I'd take Baron Schwartz's suggestion and move the ib_logfiles somewhere instead of deleting them, just in case it turns out that the server doesn't shut down cleanly for some reason.


Does this work for you? I am testing in MySQL.

SELECT LatestStock.*
FROM daily_stock as LatestStock
JOIN (
    SELECT warehouse, product, MAX([date]) as MAXdate
    FROM daily_stock
    GROUP BY warehouse, product
    ) as LatestDate on LatestStock.warehouse = LatestDate.warehouse AND LatestStock.product = LatestDate.product AND LatestStock.date = LatestDate.MAXdate
ORDER BY LatestStock.product, LatestStock.warehouse


SQL Server Availability Group Design Question/RFC We are in the early stages of planning an upgrade/redesign of our SQL environment and I am seeking advice/feedback.

The scenario:

Current SQL Server 2008R2 EE on Windows Server 2008 R2, blade servers with databases on SAN, some mirroring
Target SQL Server 2012 R2 EE (should I consider 2014?) on Windows Server 2012R2, blade server with SAN storage
SQL Server 1 to be primary Infrastructure database server (the IT databases, vCenter, AV, SCOM etc.)
SQL Server 2 to be primary user database server
SQL Server 3, secondary server in availability group configuration for Servers 1 & 2
SQL Server 4 secondary mirror of AGs in a different data center, 10GBs or more between datacenters
SQL servers 1 & 2 should not fail over to each other

Migration process will be a database at a time, the existing environment will be available until transition is complete

Can this be done?  Is there a better way to do it?  Is more information needed to determine if there is a better way, if so, what?  Where can I find some reliable documented guidance?

Thank you!

Yes, other than the 2012R2 bit what you are describing can be done, but to correct some of the terminology I think you are describing the following:

A 4 server Window Geo cluster with 3 servers in Data Centre 1 (DC1) and one server in Data Centre 2 (DC2).
Instance of SQL 2012 lets call it INST01 on server SQL1 in DC1.  This instance will be used for your infrastructure dbs.
Instance of SQL 2012 lets call it INST02 on server SQL2 in DC1.  This instance will be used for your user dbs.
Instance of SQL 2012 lets call it INST03 on server SQL3 in DC1.  This instance will be used to run your local AOG replicas in normal running.
Instance of SQL 2012 lets call it INST04 on server SQL4 in DC2.  This instance will be used to run your DR AOG replicas in normal running.

From an always on perspective you will have:

AOG group called ITDBS.  This will exist across INST01, INST03 AND INST04.  Always On Group ITDBS on INST01 would be the primary holding your infrastructure dbs and INST03 would be synchronous read only replicas (handy for reporting/scaling out) and INST04 would be asynch replicas for your DR scenario.
AOG group called USERDBS.  This will exist across INST02, INST03 AND INST04.  Always On Group USERDBS on INST02 would be the primary holding your infrastructure dbs and INST03 would be synchronous read only replicas (handy for reporting/scaling out) and INST04 would be asynch replicas for your DR scenario.

The above Always On Group configuration assumes 1 AOG for each db type on each server, you will have to consider if you need to subdivide dbs if so just create more Always on groups.  An AOG is basically just a way of grouping databases so they fail over as a logical consistent unit (unlike mirroring where there is no consistency across dbs in terms of failing over).

The above scenario gives you high availability with automatic failover if you wish due to synchronous replicas in DC1.  It also gives you DR capability albeit with a manual failover to your SQL4 server in DC2.

If SQL1 and SQL2 are big enough you could use each as the others synchronous replicas thus avoiding the need for SQL3 (ie SQL1 sync reps would live on SQL2 and vice versa) but SQL1 and SQL2 would need to be big enough hardware to cater for the loads in the event of failure.  This may save you some money.  You would need to work out the costs of having SQL3 (hardware and SQL licenses) against the extra CPUs and memory you would need in SQL1 and SQL2 to run the extra load during failure.

From a licensing perspective and storage perspective quite expensive and all 4 servers need full Enterprise SQL licenses and all non shared storage.

One other possible way to save a bit on storage and everything is have a 3 server windows cluster with 2 in DC1 and 1 in DC2.  Have SQL1 and SQL2 as a SQL failover active/passive cluster.  This would mean in SQL2014 as long as you have Software Assurance for the license you get 28 days free failover to the passive node.  This would save a lot on licensing but also storage as the 2 server SQL cluster would be using shared storage.  The down side of this option is that failover is not instant as it is in your solution, there is a 20 second delay or so for the cluster service to failover (depends on disks etc).  Your Always On Group would then include the FCI and the SQL3 in DC2. Also another downside of having an FCI in an AOG is you lose automatic failover, but the dbs in DC2 are  asynch replicas so you wouldnt have feature anyway.

Also think about your quorum design, 3 would be a bit trickier you would need a vote from a witness disk and to use the DR server you would need to force quorum if both DC1 servers were down but once documented it is easy for your ops/dbas to follow.

Lots of info to consider, hope this helps.


Squirel SQL uses wrong command for MySQL when renaming a column I installed Squirel SQL on Ubuntu and a downloaded and put a MySQL driver into the lib folder of the Squirel folder. Then I chose this driver in the "extra class" tab when configuring Squirel MySQL driver. Connecting to the database and viewing data works but when I try to rename a column in Squirel it suggests Oracle-specific code to do it instead of a MySQL-specific variant. What did I do wrong? Doesn't Squirel's refactoring tools support MySQL or how do I configure it to properly rename columns? PhpMyAdmin automaticaly solves the task just fine.

This is the SQL that is shown when I click "show SQL" in the "modify column" menu:

ALTER TABLE some_column ALTER COLUMN "one_name" RENAME TO "another_name";

This doesn't work. mySQL answers with incorrect syntax error.

Exact order of clicks to reproduce is:
right-click on a table in the left panel, then in the context menu "refactoring"->"column"->"modify column". Then you choose the column and edit the name. Button "execute" leads to an incorrect syntax error and "show sql" gives me this sql.

OK - the syntax for changing a column name in MySQL is

ALTER TABLE table_name CHANGE old_col_name new_col_name COL_DATA_TYPE;

However, it appears that the syntax generated by SQuirreL SQL is

ALTER TABLE table_name ALTER COLUMN "old_col_name" RENAME TO "new_col_name";

I tried this with both the MySQL jdbc driver (from mysql.com) and the MariaDB driver (from mariadb.org) and it fails on both counts.

Indeed using the MySQL contextual menu (just above the Refactoring one you tried to use), it is also impossible to modify the column name.

So, indeed, it appears that you have discovered a bug in SQuirreL SQL. Congratulations!

You can, of course, use the SQL window to perform this operation (tested).

All I can say is that I'm surprised that SQuirreL SQL has such a flaw for a server as popular as MySQL.


MySQL slow select query Have this query that is awfully slow:

SELECT bdp.id
FROM bd_products bdp JOIN bd_productwords bdpw
ON bdp.id = bdpw.productid JOIN bd_words bdw
ON bdpw.wordid = bdw.id
WHERE bdp.price > 0
AND bdw.word = 'nike'
ORDER BY bdpw.productid DESC

It returns 18217 rows and takes around 1,4 seconds to run :(

Removing the ORDER BY doesn't change the execution time. It stays around 1,4 seconds.

But if I remove the bd_products part like below, execution time improves to just 0,005 seconds.

SELECT DISTINCT bdpw.productid
FROM bd_productwords bdpw JOIN bd_words bdw
ON bdpw.wordid = bdw.id
WHERE bdw.word = 'nike'

It's not the bdp.price part that's the problem. Removing it like below doesn't change the execution time. It stays around 1,4 seconds.

SELECT bdp.id
FROM bd_products bdp JOIN bd_productwords bdpw
ON bdp.id = bdpw.productid JOIN bd_words bdw
ON bdpw.wordid = bdw.id
WHERE bdw.word = 'nike'
ORDER BY bdpw.productid DESC
LIMIT 100

The table bd_products contain 2,0 million rows. bd_productwords contain 16,1 million rows and bd_words contain 0,4 million rows.

Any ideas for why execution time is so slow when I include the bd_products part?

Table columns:

bd_words: id (int), word (varchar)
bd_productwords: id (int), wordid (int), productid (int)
bd_products: id (int), name (varchar), description (varchar), price (int)

Indexes for bd_products:

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
bd_products 0   PRIMARY 1   id  A   1837288 \N  \N      BTREE
bd_products 1   Price   1   price   A   55675   \N  \N  YES BTREE
bd_products 1   Imageuploaded   1   imageuploaded   A   2   \N  \N  YES BTREE
bd_products 1   BrandId 1   brand   A   9669    \N  \N  YES BTREE
bd_products 1   BrandId 2   id  A   1837288 \N  \N      BTREE
bd_products 1   Id  1   id  A   1837288 \N  \N      BTREE

Indexes for bd_productwords:

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
bd_productwords 0   PRIMARY 1   id  A   15757529    \N  \N      BTREE
bd_productwords 1   WordidProductid 1   wordid  A   1313127 \N  \N  YES BTREE
bd_productwords 1   WordidProductid 2   productid   A   15757529    \N  \N  YES BTREE
bd_productwords 1   ProductidWordid 1   productid   A   5252509 \N  \N  YES BTREE
bd_productwords 1   ProductidWordid 2   wordid  A   15757529    \N  \N  YES BTREE
bd_productwords 1   WordId  1   wordid  A   1125537 \N  \N  YES BTREE
bd_productwords 1   ProductId   1   productid   A   5252509 \N  \N  YES BTREE

Indexes for bd_words:

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
bd_words    0   PRIMARY 1   id  A   395359  \N  \N      BTREE
bd_words    1   Word    1   word    A   395359  \N  \N  YES BTREE

Explain for the awfully slow query:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  bdw ref PRIMARY,Word    Word    603 const   1   Using where; Using index; Using temporary; Using filesort
1   SIMPLE  bdpw    ref WordidProductid,ProductidWordid,WordId,ProductId    WordidProductid 5   dreammodels local.bdw.id    12  Using where; Using index
1   SIMPLE  bdp eq_ref  PRIMARY,Price,Id    PRIMARY 4   dreammodels local.bdpw.productid    1   Using where

SHOW CREATE TABLE is more descriptive than DESCRIBE.  And easier to read.

(I am removing the "bp", which I find as being distracting clutter:)

SELECT  p.id
    FROM  words w
    JOIN  productwords pw  ON pw.wordid = w.id
    JOIN  products p  ON p.id = pw.productid
    WHERE  w.word = 'nike'
      AND  p.price > 0
    ORDER BY  pw.productid DESC
    LIMIT  100

If the tables are not InnoDB, convert to such.

productwords seems to be a many:many mapping table; see here for advice on improving its performance .

18K rows after a moderately complex query could reasonably take 1.4 seconds.  Some more things to ask:

How much RAM?
Value of innodb_buffer_pool_size?
Disk subsystem (HDD/SSD, etc)?
What will you do with 18K ids or even 100?  Turn around and ask for more data from some table?  That will be inefficient.

The complexity prevents scanning fewer than 18K rows, even with the LIMIT 100.  OTOH, if you get rid of all references to products would probably speed it up some.


Approach to Handling Errors/DataCorrections in Aggregated Data We're in the process of building a vNext Data/Reporting Platform for capturing Web & Sales Traffic.

At a very high level we're capturing Impressions, Clicks & Sales Data which then needs to be reported on.

Impressions are on the order of Millions per day
Clicks: 100,000's per day
Sales: 1000's per day

My first pass design for the platform essentially aggregates data into Hourly, Daily, Weekly & Monthly structures. Each being grouped on a number of other dimensions (Client, Brand, Product, Country, Retailer etc...) with Impression Counts, Click Counts, Total Value etc...

So each hour, source data gets aggregated -> Hourly
And then each day:

the prev 24h of Hourly Data -> New Daily
the prev 24h of Hourly Data -> Added to existing Weekly or create new Weekly
the prev 24h of Hourly Data -> Added to existing Monthly or create new Monthly

This gives us a lot of flexibility for reporting efficiently as well as monitoring trends and identifying potential problems.

One thing I'm not sure on is how to "Fix" data in the event a "discrepancy" gets into our aggregated data.

e.g. Every once in a while a customer will provide incorrect pricing information. When that occurs, the "Potential Value" on each click and impression will be wrong.

So that source data ends up in the Hourly aggregates.

If unnoticed, for even one day, that hourly data pollutes the daily, weekly & monthly aggregates. And because those aggregates are being built and added to cumulatively, you can't easily undo it. (You can't pour the blue water out of the jug of purple water).

The naive solution here would seem to be to throw away the subset of aggregated data that's been polluted, fix the source data, and then regenerate each of those aggregates that's been removed. Which one day later might mean throwing away 24 Hourly's, 1 Daily, 1 Weekly & 1 Monthly.

Or is there another approach here that I'm not thinking of?

In accounting practice values are never altered once written. Instead secondary compensating transactions are created. In your circumstances this would mean one new row with a value that is the negative old, incorrect value and a second new row with the new, correct value.

To work each data row will need its own datetime column so these compensating transactions can be post-processed into the correct buckets.

Averages cannot be altered in post processing. Instead the sum and count are stored and average calculated at run time.


Can I safely delete .tmp files from a FoxPro 2.5? Can I safely delete .tmp files from a FoxPro 2.5 application?

There are currently over 71k .tmp files. They are located in the root directory and the \TEMP directory.

You can safely delete them, if they are in use you won't be able to delete them because they will be open exclusively. Foxpro usually creates these temp files when creating temporary cursors during program execution. Normally it should clean them up when the program terminates, but if it crashes these files will be left over.


Tuning Mongo (TokuMX) for fastest bulk reads What options do I have for tuning MongoDB / TokuMX for reading the results of queries with large result sets? I'm currently on a single server (no sharding) with the data on an SSD drive. My queries are simply {field: value} with an index on field. The collection has ~1B records, of which I'm retrieving ~50M.

Looking at top, it seems like there are alternating spikes of mongod CPU (~200% CPU, i.e., 100% of 2 cores) and times when it appears that mongod is waiting on IO.

What kinds of things can I try to improve query time?

The first thing to look at is db.serverStatus().ft.  This has a bunch of metrics that may be helpful, to figure out where you're spending time.  These are documented here: http://docs.tokutek.com/tokumx/tokumx-server-status.html

Usually the way to improve query time is to make sure you have the right index for your query.  You might be doing a query on {field: value} and have an index on {field: 1}, but if you're not projecting in your query, you'll still be doing lots of point queries back to the primary key index to get the full document, which is very inefficient.  You may want to add other fields to the index to make it covering, and then project to those fields, e.g. index on {field: 1, other: 1, fields: 1} and then make sure you project in your query like db.foo.find({field: "val"}, {field: 1, other: 1, fields: 1}).

Alternatively, you can use a custom primary key of {field: 1, _id: 1} if these are your most common queries, or instead just make the secondary key on {field: 1} a clustering key.

If you want more help, it would be great if you could provide your server configuration, collection schema (db.foo.getIndexes() is fine) and stats (db.foo.stats()), realistic sample queries and cardinality, and output of db.serverStatus().ft.


Grant User Permission To Restore Database And Access that database in sql server? I want to create a user having db_create, db_restore server roles and will be db_owner of that database. But there is only 1 server role available, that is db_creator, which can create database  and is also the db_owner of that. But when I restore another database backup that gets disconnected, i.e. not accessible.

In sql server is there any way that can create and restore db and will be dbowner of that database. I don't want to grant sysadmin server roles to that user.

Please help me out.

But when I restore another database backup that gets disconnected, i.e. not accessible.

You have to add the user to the db_owner role after the restore. You can use

ALTER ROLE [db_owner] ADD MEMBER [frank]

or EXEC sp_addrolemember 'db_owner', 'frank';

Note:

ALTER ANY DATABASE - You dont want to give the user too much rights since this will allow the user rights for all the databases.

  Grants or denies the ability to drop or modify existing databases as well as create new databases. The dbcreator fixed server role has this permission granted implicitly.


How to delete and create user remotely on SQL Server 2000 My boss wants to make sure that no one runs a job accidentally so we are setting up lots of safeguards to ensure that no one sleepwalks, log into the system and run a job. We are updating the subscription at corporate in a merge replication so that it will populate back to the publisher which are spread out across 9 states. That process works great but if it is during business hours, the publisher will override some of the changes that we made to the database.

So to make sure that no one makes any changes to the database, we have decided to delete the user account at the remote locations so that the users can not make changes at 3 am, many, many hours after business hours but some of our managers have been known to come rather early.

I am running into security issues when I try to delete and then after the job is finished, create the user back again.   Using a fully qualified name, it will delete the user but throw an error but won't even think about creating a user.

EXEC [server].databaseName.dbo.sp_revokedbaccess N'<user>'
GO

results in "User has been dropped from current database.

  Msg 0, Level 11, State 0, Line 0
  A severe error occurred on the
  current command.  The results, if any, should be discarded.

but the user really has been dropped.

My create user statement and sp_addrolemember just throws up an error and doesn't run at all.

Any ideas on how I can accomplish this? All of our SQL Server 2008R2 don't have any issues and this is not required but we still have just over 100 SQL Server 2000 machines in the wild that I need to do this to.

I found a workaround

To change the permissions of the user to where it can't write

EXEC [Server].Database.dbo.sp_droprolemember N'db_owner', N'User'
EXEC [Server].Database.dbo.sp_addrolemember N'db_denydatawriter', N'User'

After I am done, change it back

EXEC [Server].Database.dbo.sp_droprolemember N'db_denydatawriter', N'User'
EXEC [Server].Database.dbo.sp_addrolemember N'db_owner', N'User'

Just tested it in my lab environment and it does the trick. Can't wait to get rid of all of our SQL Server 2000 databases! Just a few more months and we will be rolling 120 servers into one SQL Server 2012


I want to find out in SQL SERVER 2012, who is creating SQL Logins and on which date I was trying to find out in sql server 2012, who created login and which date. Can any body help on this thanks,

SQL server wont generally keep a track of this. So you have to make use of the default trace to gather the information for EventClass 109.

Make sure to check that default trace is running using:

SELECT* FROM sys.configurations WHERE configuration_id = 1568

If not enabled, then enable it using:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'default trace enabled', 1;
GO
RECONFIGURE;
GO

Then you can run the query as mentioned here to gather the information for the added SQL logins:

SELECT  TE.name AS [EventName] ,
        v.subclass_name ,
        T.DatabaseName ,
        t.DatabaseID ,
        t.NTDomainName ,
        t.ApplicationName ,
        t.LoginName ,
        t.SPID ,
        t.StartTime ,
        t.RoleName ,
        t.TargetUserName ,
        t.TargetLoginName ,
        t.SessionLoginName
FROM    sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1
                                                              f.[value]
                                                      FROM    sys.fn_trace_getinfo(NULL) f
                                                      WHERE   f.property = 2
                                                    )), DEFAULT) T
        JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
        JOIN sys.trace_subclass_values v ON v.trace_event_id = TE.trace_event_id
                                            AND v.subclass_value = t.EventSubClass
WHERE   te.name IN ( 'Audit Addlogin Event', 'Audit Add DB User Event',
                     'Audit Add Member to DB Role Event' )
        AND v.subclass_name IN ( 'add', 'Grant database access' )

Note * There are 5 default trace files and these are rolled over,hence its possible to capture recent information related to objects and not related to old events. Therefore to save the information for future use please read Collecting the Information in the Default Trace