texts: Upgrade SQL Server 2000 database to 2008 R2 and enable new features

texts:
I have recently upgraded SQL Server 2000 database to 2008 R2.

What I did was:

Shutdown SQL Server 2000 (express) service on old machine,
Move datafiles (mydatabase.mdf and mydatabase.ldf) to new machine,
Run SQL Server Management Studio 2008,
Connect to local database engine,
Attach datafiles to database.
Change the compatibility level of database to SQL 2008 (100).

Question: What else should I do to make migration complete?

I want to:

use new features like checksumming and full recovery model,
make this database to be exactly as it was created in SQL 2008 R2,
make this database to be fully compatibile, correct and be perfect suited for new, SQL 2008 R2 database engine.

In other words: I just want to know how to correctly and completly convert old SQL 2000 database to new 2008 R2 database, be calm that everything is done right and be happy with all new features.

I'm asking this question, because I've found a lot of sites on the Internet that says so many different things that makes me confused: some say that it is required to rebuild indexes, another says to do other things... and now I know nothing so I want to hear experienced person opinion and clear, step-by-step instructions. I work for very small company, I'm on my own and I don't want to screw things up.

Sir, I'm really impressed with your answer, I wasn't expecting so much.

So some comments:

The database is now in production. As I said, it was upgraded using deattach-attach method as I desribed in first post and as described on MSDN: http://msdn.microsoft.com/en-us/library/ms189625.aspx
It had to be done quickly, so I was forced to do it that way. Let's forget about how inappropriate could it was and focus on current situation.
The users/persmission is not a problem here - there are only few and permissions are simple.
The application which use database is compatibile with SQL 2000 till 2012 so this is not a problem either.
The database file (MDF) isn't big - only about 1GB.

Few more questions:

You recommend to use backup/restore method, but I did as written above, so can I encounter any problems now? Everything worked without any problem.
About checksumming and full recovery model: it wasn't available/enabled on SQL 2000, so I want to use them now. You said that the only thing I need to do is to enable those options in database properties? I've read somewhere, that it isn't enough and I should also rebuild indexes or something. I really don't know, I just asking.
I'm prepairing to migrate this database to SQL 2012 - so first it was from SQL 2000 to 2008 R2, now it will be from 2008 R2 to 2012 (it was impossible to do this directly because of lack of support of SQL 2000 databases in SQL 2012). So I understand that I should follow your guide: backup it in 2008 R2 and restore in 2012, then do the rest of your tips, right?
Please explain me the backup/restore method: Is it like a dump of database to SQL queries and then restoring it by executing a bunch of queries? Will this method by the way "defragment" my database? If not, how to defragment/optimize it manually?
As we were using SQL 2000 Express for years (no management interface), we were doing backups simply by stopping engine and RAR the DATA directory. For now, as we are on SQL 2008, isn't this still better than using backup function in Management Studio?
Full recovery mode with frequent Transaction log backups - Where is the Transaction log stored - is it the LDF file? How sould I backup it properly?

I know that my questions may sound silly, I'm not professional database admin, but I am the only person here who can do such "hard core" task like upgrading the database engine. I'm also sure that your knowleadge will help a lot to other people like me.

Thank you very much for your time and knowleadge, I really appreciate this.


texts: Automate - recover database using backup controfile

texts:
I am trying to automate a periodic clone of a database from one environment into another changing the database name.  I have done this many times manually by doing a begin/end backup on the source system, copying the storage shares and cloning them into the new location.  My hangup comes in automating the recovery piece.  After recreating the control file, I do the following command (generated from the backup of the control file to trace):

recover database using backup controfile

The problem is that this command has a prompt.  Because of the way I am doing the process, the archive logs needed are all present, so all that is necessary is to type AUTO.  Then after waiting for it to complete, do the alter database open resetlogs.

My question is, how can I automate this last piece?  I have a few ideas, but I'm not fond of any of them.  Hopefully, there is something obvious I am missing.

Update:
I've tried using the CONTINUE DEFAULT syntax, but it doesn't seem to work.

SQL> RECOVER DATABASE UNTIL TIME '2015-10-20:13:54:16' USING BACKUP CONTROLFILE CONTINUE DEFAULT;
ORA-00274: illegal recovery option CONTINUE

SQL> RECOVER DATABASE UNTIL TIME '2015-10-20:13:54:16' CONTINUE DEFAULT USING BACKUP CONTROLFILE;
ORA-00274: illegal recovery option CONTINUE


texts: Patterns for setting up sharding for SQL Server 2008 R2, in order to handle large datasets?

texts:
I want to handle a large dataset (> 1 billion rows) in SQL Server 2008 R2. I heard that if you set up "sharding" or "horizontal partitioning", it makes it quicker to handle large datasets as it breaks the tables up into multiple files.

Have you used sharding or horizontal partitioning before? If so, could you suggest any patterns that, in your experience, achieves better results compared to a single massive table?


texts: MYSQL - How to make a table with a column that will sum columns from parent tables?

texts:
I have a table called FOODS. FOODS has two foreign keys: IDsize and IDflavor.

The parent tables FLAVOR and SIZE each have a column price.

The table FLAVOR has columns IDflavor, flavor and price with an example row (1, 'Beef', 10). The table SIZE has columns IDsize, size and price with an example row (3, 'BIG', 15).

Is it possible to make a column in FOODS that will automatically sum the values of the price columns of the corresponding rows in the parent tables FLAVOR and SIZE (using the foreign keys)? So if FOODS has IDsize = 3 and IDflavor = 1, the column should automatically get 25 (10+15).


texts: Will transactional replication push a CREATE INDEX immediately? Or do I need a snapshot?

texts:
I have transactional replication set up on SQL 2008, and the article in question has "Copy nonclustered Indexes" set to TRUE.

If I add a non-clustered index on the publisher (using CREATE INDEX), will that push to the subscriber immediately?

Or do I need to run a snapshot?


texts:
Can I force a user's queries to always run with the hint NOLOCK? e.g. they type

select * from customer

But what is executed on the server is

select * from customer with (nolock)

This question is not:
About the various pros and cons of NOLOCK, respectfully. I know what they are, this is not the place to discuss them.


texts: Oracle - How to find out ADVM file system type?

texts:
Is there some built-in command from Oracle to find out ADVM file system type? I was looking for it in advmutil command or acfsutil command. But I don't find anything.
In Linux usually I use file -s
[root@server1 ~]# file -s /dev/asm/advmv1-246
/dev/asm/advmv1-246: Linux rev 1.0 ext4 filesystem data (needs journal recovery) (extents) (large files) (huge files)

or mount (when its mounted)
[root@server1 ~]# mount |grep advm
/dev/asm/advmv1-246 on /acfsmount type ext4 (rw)
[root@server1 ~]#

to find out, but I'd like to know if is there some way using Oracle commands.


texts: Database replication/synchronization using multiple source nodes for postgresql

texts:
What would be the approach for synchronization at DB level for a scenario where data from multiple sources (DB nodes) needs to be integrated at a destination DB node when using PostgreSQL?

The PostgreSQL XDB replication server supports single master replication and multi master replication, but this scenario requires syncing multiple sources at one destination.


texts:
My box is RHEL 6.2, and file system BLOCK SIZE is 1024.

# tune2fs -l /dev/sda1
...
Block size:               1024
...

My Oracle is 11g, and database block size is 8192.

SQL> show parameters db_block_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

I create a tablespace, set block size 16K.

CREATE BIGFILE TABLESPACE "C_IDX" DATAFILE '/u01/oradata/weixin/c_idx1.dbf'
SIZE 4096M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT
MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO blocksize 16K;

then  i got error ora-29339.

ORA-29339: tablespace block size 16384 does not match configured block sizes

It seems the reason is  DEFAULT 16K buffer cache is 0.

SQL> select component, current_size from v$sga_dynamic_components;

    COMPONENT       CURRENT_SIZE
    shared pool 402653184
    large pool  16777216
    java pool   16777216
    streams pool    16777216
    DEFAULT buffer cache    1308622848
    KEEP buffer cache   0
    RECYCLE buffer cache    0
    DEFAULT 2K buffer cache 0
    DEFAULT 4K buffer cache 0
    DEFAULT 8K buffer cache 0
    DEFAULT 16K buffer cache    0
    DEFAULT 32K buffer cache    0
    Shared IO Pool  0
    ASM Buffer Cache    0

Now the problem is how large  should i set it, or according to what i set the value?

SQL> alter system set db_16k_cache_size= ?

Thanks.


texts: Recently i tried to increase the memory_max_target in Spfile as well through cmd.But After restart the instance it shows "ORA-47500: XE edition memory parameter invalid or not specified".I couldn't start the instance,but later i started by create SPfile from pfile.why this error comes after change the memory?Should i need enterprise edition??


texts:
I am creating a table wich will contain user-provided URLs. I want those to be unique, so when the user gives me a URL I will first check if the URL exists and if so return the ID for the entry. If not create a new row with this URL.

Obviously I want this to be fast. What is the best option?

Make the actual URL a varchar that is UNIQUE and look by this url?
Make a hash of the URL and use it as a primary key of sort?
Other ideas?


texts: How to involve table index in filtering of 3 conditions?

texts:
I have table with 30 mln records and I want to get data by some query very fast. I have these fields: id (int), field1 (int), field2 (int), field3 (int). So I want to query data by this condition

SELECT *
FROM mytable
WHERE field3 = 1
AND (field1 < field2 OR field2 < 0)
LIMIT 1

Query plan I got

explain (analyze, buffers) select * from mytable mt where (mt.field1 < mt.field2 OR mt.field2 < 0) and mt.field3 = 1 limit 1;
                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.37 rows=1 width=13) (actual time=525.050..525.051 rows=1 loops=1)
   Buffers: shared hit=245 read=35166
   ->  Seq Scan on mytable mt  (cost=0.00..359373.97 rows=983846 width=13) (actual time=525.048..525.048 rows=1 loops=1)
         Filter: ((field3 = 1) AND ((field1 < field2) OR (field2 < 0)))
         Rows Removed by Filter: 4006175
         Buffers: shared hit=245 read=35166
 Planning time: 0.163 ms
 Execution time: 525.077 ms

I have index set on field3 but it used by postgres only with

SELECT *
FROM mytable
WHERE field3 = 1
LIMIT 1

I tried to set index on fields (field3, field1, field2), then on the (field3, field2, field1), but it only works without field1 < field2.

Then I tried to set index only for condition field1 < field2. Indexes (field1, field2) and (field2, field1) don't work too.

What index do I need to use it with condition field1 < field2?

Update.
I add partial index by condition

CREATE INDEX idx_mytable_condition_1 ON mytable (field3) WHERE (field1 < field2 OR field2 < 0);

but anyway there's no using index, only seq scan

explain (analyze, buffers) select * from mytable mt where mt.field3 = 1 and (mt.field1 < mt.field2 OR mt.field2 < 0) limit 1;
                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.37 rows=1 width=37) (actual time=492.787..492.787 rows=1 loops=1)
   Buffers: shared hit=277 read=35134
   ->  Seq Scan on mytable mt  (cost=0.00..359373.97 rows=983846 width=37) (actual time=492.785..492.785 rows=1 loops=1)
         Filter: ((field3 = 1) AND ((field1 < field2) OR (field2 < 0)))
         Rows Removed by Filter: 4006175
         Buffers: shared hit=277 read=35134
 Planning time: 0.110 ms
 Execution time: 492.806 ms


texts: Is this relation in third normal form (3NF)?

texts:
I created a relation called Customer and defined it as:

I just wanted to know if it is in 3NF?

My concern is regarding address, since multiple customers may have the same address.

I thought, since there are duplicate values for address, it is not in 2NF. How can I solve this?

Should I separate name as firstName, lastName, and address as (Country, City,...)?


texts: Should I use Index or Unique Key for a non PK Uniqueidentifier?

texts:
Using Sql Server 2014, I have several tables where the PK field is an int, but I also have a UniqueIdentifier field that I need to query by.  This field will always be unique.

I am adding indexes for these and I have the option of choosing Index or Unique Key.

Is there any benefit to choosing one or the other?


texts: What is the appropriate SQL method for "Safe to SUM"?

texts:
In SQL Server 2008 R2+ we, unfortunately, have some nvarchar columns storing numeric data.

In some cases we want to SUM the data when it can be summed. The problem is that ISNUMERIC() returns 1 for a number of entries with are invalid parameters for SUM.... I understand what's going on here but I am looking for a workaround.

For example ISNUMERIC('10,1') = 1 but I cannot include that value in a SUM so I would like that value excluded from the SUM... and any data points, in fact, which cannot be parameters to SUM without being massaged...

So I'm wondering what is the preferred way to determine whether or not the contents of an alphanumeric column can be summed?

EDIT: It's worth noting that this is a take-off from this answer on SO:

https://stackoverflow.com/a/15833014/369775

but I am not convinced that the methods provided in that answer are optimal. I do agree with HLGEM:

https://stackoverflow.com/a/338292/369775

  The best solution would be to stop storing integers in a varchar column. Clearly there is a data issue where the data is interpretable as a numeric but cannot be cast as such.


texts: CTE Query to CONCAT text (over 10,000 rows)

texts:
I am trying to construct a CTE query to loop through rows of data and concatenate text into one line for each SampleUserNumber.

Below is what I am working with:

CREATE TABLE #SampleTable
(
SampleRowID INT IDENTITY(1,1),
SampleUserNumber INT,
SampleLineNumber INT,
SampleTextLine VARCHAR(1000)
)

Here is my CTE Query:

Here is the Output:

The CTE Query works and the result is correct, but my main issue is the performance and the amount of records I have to process. Right now I am dealing with 100,000 records at a bare minimum.

Questions:

Have I written the query incorrectly to handle 100,000 records? Is there a more efficient way to write the CTE?
Is there another way more efficient and better way to do this? [looping would be out of the question unless I have no other choice]

Sorry for all the images. I figured it would be much easier to show everything rather than write it out here.

Thank you.


texts: Non Clustered Index Scan Properties window - Predicate order different than query order

texts:
SQL 2012 Data Warehouse type environment...

I have a Non Clustered Index on a Type 2 Dimension table, which I'll just call DimStudent.

The index is defined as below, with data types and other info in parentheses for context:

(

 [StudentID] ASC (VARCHAR(10))

,[EffectiveStartDate] ASC (SMALLDATETIME)

,[EffectiveEndDate] ASC (SMALLDATETIME)

)

INCLUDE [StudentKey] (INT, IDENTITY, PK).

The query in question is being run during an SSIS ETL package, and is defined as below. It is specified in a Lookup Transformation using a partial cache, in the Advanced tab using the Custom query section, which is mostly auto-generated by Visual Studio (which put the [refTable] alias and SELECT * into the query) with some modifications to the query for SCD2 lookup usage:

select * from (SELECT

 LTRIM(RTRIM(StudentID)) AS StudentID_Trimmed

,StudentKey

,EffectiveStartDate

,EffectiveEndDate

FROM dbo.DimStudent

)
AS [refTable]

where [refTable].[StudentID_Trimmed] = @P1

and [refTable].[EffectiveStartDate] <= @P2

and [refTable].[EffectiveEndDate] > @P3

Note that the order of the columns in the query is the student ID (@P1), then the Start date (@P2) and then End Date (@P3).

Also note that I am currently TRIMMing the StudentID. I have determined that this is no longer necessary (it was necessary at some point for some reason), and I'm already working on changing the ETL to remove that TRIM aspect.

In the query plan for this query (using Adam Macahanic's sp_WhoIsActive), I've confirmed it's doing a Non Clustered Scan using this index. When I view Properties of the scan operator, it lists the Predicates as: (db and schema removed)

[DimStudent].[EffectiveStartDate] <= **[@P2]**

AND [DimStudent].[EffectiveEndDate] > **[@P3]**

AND ltrim(rtrim([DimStudent].[StudentID])) = **[@P1]**

My questions are:

Why are the predicates in the query plan in a different order (@P2, @P3, @P1)than how the query was written? (@P1, @P2, @P3) Caused by the TRIM?
Why is it a scan and not a seek as the index was designed to do for this query? Caused by the TRIM (not SARGable)?


texts: Reduce a huge number of possibilites to something manageable?

texts:
I have this problem, which seems like it should have an easy solution, but I can't figure it out.

Say we have pizza_toppings table where a chef enters available sets of toppings. There are four toppings: pepperoni, olives, pepper, and basil. The chef makes four different pizzas with the above ingredients:

pizza_toppings
--------------
pizza 1 has only pepperoni
pizza 2 has pepperoni and olives
pizza 3 has olives or basil
pizza 4 has ( pepperoni and pepper) or only basil

How would I record all these 'and', 'or', 'and + or', 'and + and', '(and + or) + and ...', etc. relationships between table entries?

It seems like a problem from some difficult textbook that should have a clever solution, which I can't figure out. My program doesn't actually deal with pizza and there will be countless "toppings" created by users and infinite possible combinations, which will also be combined by users. So I can't really make a table with pre-defined combinations. Is there a solution to such a problem?


texts: Is there a system primary key associated with each row?

texts: Does a system primary key or unique identifier for each row in a table exist? Like @@ROWID, @@IDENTIFER, or some other identifier that uniquely identifies each row regardless of how it was created? Using SQL Server 2000 through SQL Server 2012.


texts: SQL Server Management Studio: The columns in table do not match an existing primary key or unique constraint

texts: I have two tables:  Students and Courses.  I want to set a foreign key in the Students table from the couseId column to the courseId column in the Courses table.  I have set primary keys to both courseId columns but Management Studio is still giving me the error The columns in table 'Courses' do not match an existing primary key or unique constraint.'  What am I doing wrong?


texts: Postgresql function equivalent to Excel's NORMSINV function?

texts:
I need Excel's NORMSINV equivalent function for PostgreSQL, preferably plpgsql function. Can somebody help?
For reference, here is Excel's normsinv function explained:
https://support.microsoft.com/en-us/help/826772/excel-statistical-functions-normsinv


texts: innodb_buffer_pool_size, optimize and mysqltunner

texts:
I'm trying to understand how to properly size innodb_buffer_pool_size and when to optimize my tables. Questions are:

When sizing the innodb_buffer_pool_size, should I take into account the size of the tables on disk (du -sh /folder_with_db_data) or the size reported by a tool like mysqltunner.pl
When running mysqltunner.pl, it says that my tables are 0% fragmented, yet, the space in disk is much higher than the space reported by mysqltunner.pl. After running an optimize for all-databases I can see that the space on disk of the data shrinks until it matches the space previously reported by mysqltunner.pl. Question is, why if there is "no fragmentation" does the data on disk shrinks?
Could you help me understand what the "optimize" really does?
Setting the innodb_buffer_pool_instances parameter so I end up with 1 instance per each GB of innodb_buffer_pool_size, is a mariadb good practice. Does this setting could involve any risk? I ask this because of the application I'm using (Zimbra), does not even mention this setting and I'm curious about it.

Thank you very much!!!


texts: Is there any way to delete from this postgres 9.6 view?

texts:
Here is the view:

    CREATE OR REPLACE RECURSIVE VIEW employees_recursive(id, name, child_category_id,parent_category_id, level,topic_id, type_name, inserted_at, updated_at) AS
(
    SELECT
        ee.id,
        ee.name,
        child_category_id,
        parent_category_id,
        1 as level,
        ct.id,
        ct.name,
        ee.inserted_at,
        ee.updated_at
    FROM category_hierarchy cth
    LEFT JOIN categories ee ON cth.child_category_id = ee.id
    INNER JOIN category_types ct ON ee.topic_id = ct.id
    WHERE
        parent_category_id IS NULL
    UNION ALL
    SELECT
        ee.id,
        ee.name,
        c.child_category_id,
        c.parent_category_id,
        ct.id,
        ct.name,
        ee.inserted_at,
        ee.updated_at
    FROM employees_recursive p
    INNER JOIN category_hierarchy c ON p.child_category_id = c.parent_category_id
    LEFT JOIN categories ee ON c.child_category_id = ee.id
    INNER JOIN category_types ct ON ee.topic_id = ct.id
)

I've been trying to write a function to delete rows from the view by id using functions and triggers following this post.  The Postgres docs say that views with "WITH" or "UNION" keywords at the top level are not updateable.

does that mean I can't delete rows from this view?

If that's the case, is there a way to delete rows from the actual table and somehow refresh the view?


texts: compare/sync databases from different computers

texts:
I have a server online and I have installed MySQL and database A server on it.   I also have mysql installed on my local computer and have a copy of database A on it.  I want to compare the contents of the two databases, and possibly sync them using federated tables.

But after trying out federated table, it seems that I can’t even connect to the server.
The federated table that's supposed to be connected to (an online, not-empty) table from the server is empty.

Am I doing this right? Is this even possible?

If not, can you please suggest any tools/applications that can do this.

Thanks!


texts: Understanding postgres reason for picking this query plan

texts:
I ran a query:

select * from orderline where orderid = 12345 and status > 0

that gave me the following plan:

Bitmap Heap Scan on orderline  (cost=286370.00..531410.78 rows=76372 width=155)
  Recheck Cond: ((orderid = 12081976) AND (itemstatus > 0))
  ->  BitmapAnd  (cost=286370.00..286370.00 rows=76372 width=0)
        ->  Bitmap Index Scan on orderline_orderid_index  (cost=0.00..4239.65 rows=229117 width=0)
              Index Cond: (orderid = 12081976)
        ->  Bitmap Index Scan on orderline_status  (cost=0.00..282091.91 rows=15274484 width=0)
              Index Cond: (status > 0)

I wanted to understand why postgres decided to run two separate index scans rather than the filter on the forign key (orderline_orderid - which returns about 5 rows out of millions) then filtering status from these results.

How can I work out what I've set up wrong here? (I'm assuming index fragmentation or maybe status shouldn't have an index?)

EDIT: I tried some of the things in the comments and the discovered the following:

I thought the cause of the issue was the index as I removed it and the problem went away:

Bitmap Heap Scan on orderline  (cost=4258.75..597209.93 rows=76372 width=155)
  Recheck Cond: (orderid = 12081976)
  Filter: (status > 0)
  ->  Bitmap Index Scan on orderline_orderid_index  (cost=0.00..4239.65 rows=229117 width=0)
        Index Cond: (orderid = 12081976)

However, I removed and added then index and then ran analyse and the the follow occured:

Index Scan using orderline_orderid_index on orderline  (cost=0.00..14.87 rows=4 width=63)
  Index Cond: (orderid = 12085242)
  Filter: (status > 0)

So is this because I rebuilt the index, or because I ran analyse?


texts: Breaking up large transaction - worth it?

texts:
I'm in charge of monitoring the performance of an industrial chemistry process. Every hour, a process gathers 50 million rows from various subsystems and feed into the central database. The whole thing takes about 30 minutes.

A database consultant did some analysis on the database and saw the log size spike to 50GB during those load operations. His recommendation was to break the 50 million rows insert into smaller batches. Make sense until I think about our database: it's set to Full Recovery Mode.

Assuming that I do break the operation into smaller batches, their total log sizes would still add up to 50GB. That 50GB will just sit there until the transaction log is backed up. In effect, there will be no difference between the smaller batches and the big batch approach.

Am I missing something here?


texts: Error in WSFC Role while applying SQL 2016 CU7 in Multiple SQL instance environment

texts:
Kindly advise on following error:

Cluster resource 'AG1_NAME' of type 'SQL Server Availability Group' in clustered role 'AG1_NAME' failed
Based on the failure policies for the resource and role, the cluster service may try to bring the resource online on this node or move the group to another node of the cluster and then restart it.  Check the resource and group state using Failover Cluster Manager or the Get-ClusterResource Windows PowerShell cmdlet.

Environment:

AG1: (Primary = SRV01\DEV1, Secondary= SRV02\DEV1, SRV03\DEV1)
AG2: (Primary = SRV02\DEV2, Secondary= SRV03\DEV2, SRV01\DEV2)

The error occurred while updating AG2 Replicas with SQL SERVER 2016 SP2 CU7 which was SQL Server 2016 SP2 CU4.
Updating order as follows, and there was no error during the update wizard

Set fail-over to manual on SRV01\DEV2
Updated SRV01\DEV2   -- Noticed WSFC error (above mentioned)
Set fail-over mode to Auto on SRV01\DEV2
Set fail-over mode to manual on SRV03\DEV2
Updated SRV03\DEV2
Set fail-over mode to Auto on SRV03\DEV2
Manually Fail-over from SRV02\DEV2 (Primary) to SRV03\DEV2
Updated SRV02\DEV2
Manually Fail-back to SRV02\DEV2 (Primary) from SRV03\DEV2

Is it normal while updating 2nd instance of SQL Server the 1st instance get interrupted while server involved in Availability Groups, or should we follow any particular method in this case to avoid any error as such.
Fortunately, particular AG1 and all resources of WSFC was working normal when i look back immediately (after the error) into Roles page of WSFC manager. also PowerShell  Get-ClusterResource. but i'm concerned about production update and future updates. Any suggestions would be appreciable. Thanks!


texts: Apply Definition of MVD (multivalued dependency) to Example?

texts:
Supposedly this multivalued dependency (MVD) and this functional dependency (FD):

  {W} ↠ {N,D} and {W} → {N}

hold in this table:

  V   W   N   D
=================
| v | w | n | 1 |
-----------------
| v | w | n | 2 |
-----------------

How do I show that this relation has those two dependencies via the following definition?

  Let R be a relational schema and let A ⊆ R and B ⊆ R (subsets). The multivalued dependency
  A ↠ B
  (which can be read as A multidetermines B) holds on R if, in any legal relation r(R), for all pairs of tuples t1 and t2 in r such that t1[A] = t2[A], there exist tuples t3 and t4 in r such that
  t1[A] = t2[A] = t3[A] = t4[A]
  t3[B] = t1[B]
  t3[R - B] = t2[R - B]
  t4[B] = t2[B]
  t4[R - B] = t1[R - B]


texts: Protect a table against concurrent data changes while allowing to vacuum it

texts:
I am running PostgreSQL 9.5. In a transaction, I check if data in the table satisfy some conditions:

IF condition1 THEN
  -- do this
ELSE
  IF condition2 THEN
    -- do that
  ELSE
    ...
  END IF;
END IF;

Since data in the table may change between the IFs, I want to protect the table against concurrent data changes before checking the condition1. The minimal table lock mode that protects data against concurrent modification is SHARE ROW EXCLUSIVE. However, it also prevents the table from being vacuumed and, worst of all, any vacuum (or even autovacuum) process prevents me from locking the table and checking the conditions.

I am not in control of queries that modify the table, so I cannot use advisory locks. The transaction isolation level is READ COMMITTED. Also, I would prefer not to use triggers.

What are possible solutions?


texts: How to check how the table structure effects the performance of my query

texts:
I want to check how the table structure of a certain table effects the performance of a delete query.

This is not a generalised question, I have a table, with currently 2.2 million records approximately, i want to run delete query, I want to see how InnoDB stores data, in my disk, so i can change the structure of the table.

Is there any tool or command to check the structure of the table.

CREATE TABLE `chat_server` (
  `id` int(10) unsigned NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `host` varchar(100) NOT NULL,
  `is_active` int(10) unsigned NOT NULL,
  `number_of_clans` int(10) unsigned NOT NULL,
  `weight` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DELETE QUERY

DELETE c FROM `chat_history` c inner join ( select message_id from `chat_history` where     clan_id=1005 ORDER BY message_id DESC limit 30, 30000  ) x where c.`message_id` <= x.`message_id` AND c.clan_id = 1005

thanks in advance.


texts: Unable to perform queries through mongos

texts:
I have setup a minimal replicated configuration with mongodb 3.6.3,it seems to be everything fine but when I connect to the mongos (which as far as I understand acts like a kind of load balancer in front of sharded clusters) and try to perform a query it raises this error: Cannot accept sharding commands if not started with --shardsvr (code: 193, codename: NoShardingEnabled)

This is how I've setup the servers:

3 servers: mongoserver1, mongoserver2 and mongoserver3

Launch the config server:

mongod --configsvr --replSet data --bind_ip localhost,${HOSTNAME}\
    --port 25000 --logpath /var/lib/mongodb/shardedcluster/log.cfg\
    --logappend --dbpath /var/lib/mongodb/shardedcluster/cfg --fork

Log in the config server and initiate the replica:

rs.initiate(
  {
    _id: "data",
    configsvr: true,
    members: [
      { _id : 0, host : "mongoserver1:25000" },
      { _id : 1, host : "mongoserver2:25000" },
      { _id : 2, host : "mongoserver3:25000" }
    ]
  }
)

Launch the sharded replica:

mongod --shardsvr  --replSet data --bind_ip localhost,${HOSTNAME}\
    --port 26000 --logpath /var/lib/mongodb/shardedcluster/data.log\
    --logappend --dbpath /var/lib/mongodb/shardedcluster/data --fork

Log in the sharde replica and initiate the replica:

rs.initiate(
  {
    _id : "data",
    members: [
      { _id : 0, host : "mongoserver1:26000" },
      { _id : 1, host : "mongoserver2:26000" },
      { _id : 2, host : "mongoserver3:26000" }
    ]
  }
)

And finally launched the mongos:

mongos --configdb data/mongoserver1:25000,mongoserver2:25000,mongoserver3:25000\
   --bind_ip localhost,${HOSTNAME}\
   --logpath /var/lib/mongodb/shardedcluster/mongos.log --fork

And inside the mongos:

sh.addShard("data/mongoserver1:26000,mongoserver2:26000,mongoserver3:26000")
sh.enableSharding("cmdb")

After adding some data on the cmdb database through the mongod running or port 26000 I've checked after stopping primary node I can continue working with date on the new primary node but I'm not able to do it connecting to mongos even it seems the cmdb database is replicated as shown by sh.status():

databases:
    {  "_id" : "cmdb",  "primary" : "data",  "partitioned" : true }


texts: SSMS Database Node Refresh causes Restore to Fail

texts:
I have confirmed via testing that clicking on the refresh icon (one or more times) for the database node of a SQL Server instance being observed in SQL Server Management Studio (SSMS 2012) causes my 3rd-party database restore software to fail. If the refresh icon is not clicked, then the restore succeeds.

My question: what occurs in the SQL Server engine (or the msdb database) when the refresh icon is clicked?

Update (2:36pm)
When the failure occurs, one database is left in a restoring state. The specific error is "The database cannot be recovered because the log was not restored."

Update (2:48pm)
The 3rd-party database restore software is GUI-driven. A restore script is not available to post. I will capture the commands via Profiler.

Update (3:30pm) - restore script captured from Profiler (with no SSMS refresh)

select name, physical_name from model.sys.database_files
select name from master..sysdatabases where name = 'DC_EARTH001'
select DatabaseProperty( 'DC_EARTH001', 'IsInLoad' )
select database_id from master.sys.databases where name ='DC_EARTH001'
select DatabaseProperty( 'DC_EARTH001', 'IsSuspect' )
select DatabaseProperty( 'DC_EARTH001', 'IsShutdown' )
select name, physical_name from model.sys.database_files
RESTORE DATABASE [DC_EARTH001] FROM VIRTUAL_DEVICE='MVD_SQLAgent-DC_EARTH001__a6d9fb41_c3fc_4b00_a78e_596dd32fc149_' WITH SNAPSHOT, NORECOVERY, MOVE 'DC_EARTH001' TO 'X:\DC_EARTH001.mdf', MOVE 'DC_EARTH001_log' TO 'Y:\DC_EARTH001_log.ldf'

select name, physical_name from model.sys.database_files
select name from master..sysdatabases where name = 'DC_EARTH002'
select DatabaseProperty( 'DC_EARTH002', 'IsInLoad' )
select database_id from master.sys.databases where name ='DC_EARTH002'
select DatabaseProperty( 'DC_EARTH002', 'IsSuspect' )
select DatabaseProperty( 'DC_EARTH002', 'IsShutdown' )
RESTORE DATABASE [DC_EARTH002] FROM VIRTUAL_DEVICE='MVD_SQLAgent-DC_EARTH002__584bf879_215d_4c15_b7ad_230ecab60488_' WITH SNAPSHOT, NORECOVERY, MOVE 'DC_EARTH002' TO 'X:\DC_EARTH002.mdf', MOVE 'DC_EARTH002_log' TO 'Y:\DC_EARTH002_log.LDF'

select name from master..sysdatabases where name = 'DC_EARTH003'
select name, physical_name from model.sys.database_files
select DatabaseProperty( 'DC_EARTH003', 'IsInLoad' )
select database_id from master.sys.databases where name ='DC_EARTH003'
select DatabaseProperty( 'DC_EARTH003', 'IsSuspect' )
select DatabaseProperty( 'DC_EARTH003', 'IsShutdown' )
RESTORE DATABASE [DC_EARTH003] FROM virtual_device = 'DC_EARTH003_00__416c5ed9_54e9_4ad1_9adc_3c7401c3fd57_' WITH NORECOVERY , MOVE 'DC_EARTH003_data' TO 'X:\DC_EARTH003.mdf', MOVE 'DC_EARTH003_log' TO 'Y:\DC_EARTH003.ldf'

exec xp_msver
select convert( char(100), ServerProperty('Edition') )
select is_srvrolemember( 'sysadmin', 'NT AUTHORITY\SYSTEM' )
exec sp_helpsort
select name, source_database_id from master.sys.databases order by name

select DatabaseProperty( 'DC_EARTH003', 'IsTruncLog' )
select status from sysdatabases where name='DC_EARTH003'
RESTORE DATABASE [DC_EARTH003] WITH RECOVERY

select DatabaseProperty( 'DC_EARTH001', 'IsTruncLog' )
select status from sysdatabases where name='DC_EARTH001'
RESTORE DATABASE [DC_EARTH001] WITH RECOVERY

select DatabaseProperty( 'DC_EARTH002', 'IsTruncLog' )
select status from sysdatabases where name='DC_EARTH002'
RESTORE DATABASE [DC_EARTH002] WITH RECOVERY

Update (2015/04/07 @ 9:25am)
Solution proposed by Microsoft: SQL Server 2012 SP1 CU1

Slow performance or deadlock when you restore a database and execute statements at the same time in SQL Server 2012 (Scenario 2 http://support.microsoft.com/en-us/kb/2725950

Update (2015/04/07 @ 9:25am)
Solution implemented: SQL Server 2012 SP1 CU15 http://support.microsoft.com/en-us/kb/3038001

The issue is resolved


texts: Non-Domain Account Error while Creating Availability Group

texts:
I am creating Availability Group with two node cluster. Both servers are using same service account for SQL. But, while creating Availability Group, I getting that the SQL service account is using non-domain account error.
Why Am I getting that error?
Can I ignore the error and proceed to create Availability Group?
Thanks


texts:
I googled for the solutions of installing MySQL in CentOS stream, all the pages I got told me to add the repository of MySQL.
The problem is, why do I have to add MySQL's repository instead of directly installing MySQL from CentOS' official repository? I don't need the cutting edge version of MySQL, isn't CentOS's repository more stable?


texts: SQLite3 'OR IGNORE' leaves empty primary key IDs

texts:
I use 'INSERT OR IGNORE INTO' in my main table which works fine but leaves empty primary key IDs when records are ignored.
This leads to big jumps in my primary key numbering because I re-scan part of the same input data every time.
Can I avoid this and re-set the ID to the last used value before next call?
thanks


texts:
I am attaching a SSAS database that I have just moved to a different drive following the below link:

Attach and Detach Analysis Services Databases

First I had to grant permissions on the new drive to the ssas services account:

Second when I attach the database I get the following error:

  TITLE: Microsoft SQL Server Management Studio

  Errors in the metadata manager. The operation cannot be executed since
  the database folder '\?\F:\SSAS\Data\Boden BI DEV 9_02.0.db' for the
  database with the name of 'Boden BI DEV 9_02', ID of 'Boden BI DEV
  9_02' already exists.  (Microsoft.AnalysisServices)

As you can see on the picture below

It says the database already exist, but I have detached it from the engine, as you can see on the picture below.


texts: I want to make a new column based on another column

texts:
I don't want to make a new table; rather, I want my query to produce the desired output.

I have a query that produces a bunch of columns, and then a PROGRAM_ID column which only has 666 or 667. I want to make a select on top of this select that makes a new column, PATTERN, that has a 0 if the PROGRAM_ID is 666 and a 1 if it's 667. I know I need to use cases but I'm unsure of the syntax.


texts: Steps involved in configuring SQL Server and its components after server is renamed

texts:
I have a cloned (duplicate) server with SQL Server on it and the server name had to obviously change once it was copied. This article describes two actions to perform:

using below code to manually rename SQL Server:

sp_dropserver <old_name>;
GO
sp_addserver <new_name>, local;
GO

some changes in SSRS.

My questions are:

are there other things I need to consider/change?
Despite following all of those items, I still get the following issue:
in SSRS Configuration Manager, under Scale-out Deployment, I still have a reference to the old server.
Isn't that article incomplete given it does not speak of SSAS, SSIS, and  Linked servers?

Assumptions:

SQL Server in question has just one instance (default)
There exists no mirroring, failover cluster, replication, etc. It's a simple case.


texts: Possible to make MySQL use more than one core?

texts:
I've been presented with some dedicated MySQL servers that never use more than a single core. I'm more developer than DBA for MySQL so need some help

Setup

The servers are quite hefty with an OLAP/DataWarehouse (DW) type load:

Primary: 96GB RAM, 8 cores + single RAID 10 array
Test: 32GB RAM with 4 cores
The biggest DB is 540 GB, the total is around 1.1TB and mostly InnoDB tables
Solaris 10 Intel-64
MySQL 5.5.x

Note: The biggest DB is the replicated one from the OLTP DR server and the DW is loaded from this. It isn't a full DW: just last 6 months to 6 weeks so it is smaller than the OLTP DB.

Observations on a test server

3 separate connections
each has a concurrent (and different) ALTER TABLE...DROP KEY...ADD INDEX
the 3 tables have a 2.5, 3.8 and 4.5 million rows
CPU usage goes up to 25% (one core is maxed out) and no higher
the 3 ALTERs take 12-25 minutes (a single on the smallest takes 4.5)

Questions

What setting or patch is required to allow more than one core to be used?
That is, why doesn't MySQL use all cores available? (like other RDBMS)
Is it a consequence of replication?

Other notes

I understand the difference between an RDBMS "thread" and an OS "thread"
I'm not asking about any form of parallelism
Some of the system variables for InnoDB and threads are sub-optimal(looking for a quick win)
Short term, I'm unable to change the disk layout
OS can be tweaked if needed
A single ALTER TABLE on the smallest table takes 4.5 minutes (shocking IMO)

Edit 1

innodb_thread_concurrency is set to 8 on both. Yes, it's wrong but won't make MySQL use multiple cores
innodb_buffer_pool_size is 80GB on primary, 10GB on a test (another instance is shut down). This is OK for now.
innodb_file_per_table = ON

Edit 2

innodb_flush_log_at_trx_commit = 2
innodb_use_sys_malloc = ON
innodb_flush_method should be O_DIRECT (but SHOW VARIABLES doesn't show this)
innodb_doublewrite = OFF
File system = ZFS (And my sysadmin found this: http://blogs.oracle.com/realneel/entry/mysql_innodb_zfs_best_practices)

To test

innodb_flush_method isn't showing as O_DIRECT when it should be
will follow RolandoMySQLDBA's settings

Let me know if I've missed anything important

Cheers

Update

Changed innodb_flush_method + 3 x thread settings in RolandoMySQLDBA's answer
Result: > 1 core used for the tests = positive result


texts: How to represent Hierarchical data in Redshift when using OBT denormalised table design

texts:
We are loading a fairly simple dataset with 50-60 columns into Redshift in a flat One Big Table denormalised design (with just time dimension and one other split away).   It will represent an immutable store of business events, so works well with the Redshift style of MPP and this approach reduces the complexity of managing the surrogate keys, SCDs and joins which slow Redshift down.
There are a number of hierarchies in the data.. not a huge distinct number of values and no branch should ever go beyond 4 levels and the data is largely very static.
With limited datatypes designed for this compared to other RDBMS and no recursive datatypes, what is the best way to store the hierarchical data in Redshift ?
Early thoughts - Have each reference value have 2 extra columns for parentId and hierarchy level ?
configure in views or push downstream to the BI tools to configure the hierarchies? (RStudio, PowerBI)


texts: A Customized Table with Chronological order

texts:
Goal:
My request is the retrieve the return result from sp_Test as 8, 2, 4, 1 ,3 (take a look at picture 1) based on the chronological list from User-Defined Table Type dbo.tvf_id.

Problem:
When I execute the stored procedure I sp_Test I retrive the list that is from 1 to 8.

I don't know how to do it?

Information:
I'm using SQL server 2012.
The data in the UDT will be random for every database request.

Picture 1:

Picture 2:

create table datatable (id int,
                        name varchar(100),
                        email varchar(10),
                        phone varchar(10),
                        cellphone varchar(10),
                        none varchar(10)
                       );

insert into datatable values
  (1, 'productname', 'A', 'A', 'A', 'A'),
  (2, 'cost', '20', 'A', 'A', 'A'),
  (3, 'active', 'Y', 'A', 'A', 'A');

insert into datatable values
  (4, 'productname', 'A', 'A', 'A', 'A'),
  (5, 'cost', '20', 'A', 'A', 'A'),
  (6, 'active', 'Y', 'A', 'A', 'A');

insert into datatable values
  (7, 'productname', 'A', 'A', 'A', 'A'),
  (8, 'cost', '20', 'A', 'A', 'A'),
  (9, 'active', 'Y', 'A', 'A', 'A');

CREATE TYPE [tvf_id] AS TABLE
(
    [id] [int] NULL
)
GO

CREATE PROCEDURE [sp_Test]
    @pID tvf_id READONLY
as
begin
    set nocount on
    SELECT a.*
    FROM datatable a inner join @pID b on a.id = b.id
end

GO

--------------------------------------------------------

DECLARE @data tvf_id INSERT INTO @data([id])
VALUES (8), (2), (4), (1), (3);

exec sp_Test @pID = @data


texts: How can I rename an auto-named constraint when I get this error 15248?

texts:
Method:

Open SSMS. Find table. Open constraints. Find constraint name. Right click, rename. Give new name.

EXPLODE. (well, it just fails, at least)


texts: shrink after archiving, is this that bad too?

texts:
I have a database which grow to 20 GB, after archiving some data, the real size of tables is just 5GB.
Backup script does save a copy of the backup to a different location.
Moving 20GB or 5Gb does make a difference, so I would like to reduce the physical size, but everywhere I read to do not shrink if I don't want to affect performance.

But it this case of (periodically/quarterly/annually) archiving, is it still recommended not to shrink?


texts: Configure wallet location in sqlnet.ora and listener.ora

texts:
I have a host containing multiple DBs and the connection to the DBs need to be SSL enabled. Since each DB has a unique wallet, I specify the wallet location in sqlnet.ora as:

WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=wallet_folder/$ORACLE_UNQNAME)))

This way the listener can use the correct wallet depending on the value of ORACLE_UNQNAME variable. But I have two issues with this method.

Since ORACLE_UNQNAME is a shell environment variable a client has no
control over it.
Even when connecting from the same machine I have to bounce the listener after exporting ORACLE_UNQNAME value.

What is the solution for such a scenario ?


texts: I was wondering if there was a method of receiving some sort of notification when a SQL trace begins - be it e-mail or some other method? The circumstances of needing this we currently have a substantial amount of developers working within the same dev database and we sometimes find that a performance hit is due to one of them leaving the trace open instead of properly closing it. I understand the message needs to be spread to limit SQL trace usage to small timeframes, but alas, we are faced with this issue and must find a way to better administer it. Any insight would be much appreciated.


texts:
I don't know why, all of a sudden I cannot open anymore MySQL. I get the following error:

bomble@ChemAlive:~/ChemAliveCode/SABIC$ mysql -h localhost -u bomble

  ERROR 2002 (HY000): Can't connect to local MySQL server through
  socket '/var/run/mysqld/mysqld.sock' (2)

I tried to restart MySQL doing:

bomble@ChemAlive:~/ChemAliveCode/SABIC$ sudo /etc/init.d/mysql restart
[sudo] password for bomble:

But it failed:

  * Stopping MySQL database server mysqld [ OK ]
  * Starting MySQL database server mysqld [fail]

I had a look at my error.log file. Here's what it says:

  InnoDB: Unable to lock ./ibdata1, error: 11
  InnoDB: Check that you do not already have another mysqld process
  InnoDB: using the same InnoDB data or log files.

Don't know what that means?


texts:
I have the following table and I just added the ord column because I need to ORDER BY ord all of my tasks selected for a user/group. I want them to always be returned in the order the user added the tasks. Otherwise the id column is useless since I already have a primary key.

I see a lot of answers saying why would you ever have an auto increment that isn't a primary key so I'm wondering if I'm somehow doing something wrong here. This seems like it would be commonly necessary.

CREATE TABLE IF NOT EXISTS UserTasks(
    ord int unsigned auto_increment,
    user_id int unsigned,
    group_id  smallint unsigned,
    task_id int unsigned,
    INDEX (ord),
    INDEX (user_id, group_id),
    PRIMARY KEY (user_id, group_id, task_id)
)


texts: How to change all connections to SQL Server without investigating each hard-coded connection to serverName?

texts:
We've got a server upgrade scheduled for the near-future and we're looking to move to a new server. Our main issue is that we cannot have two servers with the same name on our domain. So is there a command which corrects all of my connections to '08Server1' from '08Server2' to instead use '16Server1' from '16Server2'?

All our applications also reference '08Serverx' - other than a mass find&replace - is there a better method available?


texts: Unable to connect to an AWS RDS read replica of MySQL

texts:
I'm not able to connect to an AWS RDS MySQL read replica.
$ mysql -h my-db-read-replica.qprdbd0pavr5.us-east-1.rds.amazonaws.com -umyname
ERROR 2003 (HY000): Can't connect to MySQL server on 'my-db-read-replica.qprdbd0pavr5.us-east-1.rds.amazonaws.com' (60)

Here're the configurations I've checked:

The security group allows me to connect from my local computer to the remote RDS.

The Public accessibility  of the read replica is Yes

I can use nmap to detect that port 3306 on the RDS is open.

$ nmap -Pn -p 3306 my-db-read-replica.qprdbd0pavr5.us-east-1.rds.amazonaws.com
Host discovery disabled (-Pn). All addresses will be marked 'up' and scan times will be slower.
Starting Nmap 7.91 ( https://nmap.org ) at 2021-05-18 17:12 CST
Nmap scan report for my-db-read-replica.qprdbd0pavr5.us-east-1.rds.amazonaws.com (3.208.xxx.xxx)
Host is up.
rDNS record for 3.208.xxx.xxx: ec2-3-208-xxx-xxx.compute-1.amazonaws.com

PORT     STATE    SERVICE
3306/tcp filtered mysql

Nmap done: 1 IP address (1 host up) scanned in 6.46 seconds

I've also rebooted the read replica, but I still can't connect to it.
The connection worked normally yesterday.
What could be the reason for failure of the connection?


texts: Script/tool to use to check when the server is not being heavily used and configuring backup on Availbility Groups

texts:
If I were to backup 50 databases in SQL server, I want to make sure that backup is taking place when the server is not that busy. Is there any way or script/tool to use to check when the server is not being heavily used?
With respect to backing up in always on availability groups, If I do my backups in my secondary replica, the log files in my primary replica will continue to grow big since it doesn't get truncated coz the log is not backed up in the primary replica. How can we avoid that issue with the log backup on the primary replica? Is it possible to do the full backups on the secondary replica and log backups on the primary replica to avoid the log in the primary replica growing too big? Will it work if we restore the database?


texts: AlwaysOn Async read takes more time when compared to that of Sync secondary (by a small fraction). Is this expected behaviour? I was under the impression that ASYNC must be faster than the SYNC.


texts: Changing data source on Access front end

texts:
I feel bad asking this, as I am only using an Access front end temporarily, but I have been asked to migrate an access 2007 db to mysql with a php front end.  I have moved the data to mysql and am working on the ~400 forms, reports, etc, but want the users to have access to the system while I am developing the new one.

I have little experience with access and although I have been able to connect to the mysql tables, the forms don't seem to update their data sources.

Is there a way to update the entire access front end to the new tables?  Or am I missing something really simple and straightforward.  I've googled extensively and I've read all the "similar questions" presented on the page where I am typing this and can't an answer which seems to apply.

Thanks for any help or links offered.


texts: A minimum of a 3 Node Galera Cluster is Recommended, but is it better to have 5 nodes?

texts:
Right now I have a 400GB database, with a 5 node Galera Cluster. They are all RAID 10 SSDs.

I've read the following:

  If the node goes missing due to a network problem or otherwise leaves
  without telling the rest of the cluster, then problems can arise. For
  the cluster to function, it needs a quorum, a majority of nodes active
  in the cluster. The two other nodes will continue to function normally
  since their partition has more than half of the known nodes but the
  node that left will stop accepting queries when it realizes that it is
  no longer in contact with the active partition. In this case, assuming
  an application can access the two active nodes, the failure can go
  mostly unnoticed.

I am trying to reduce my cost and make things more optimal. My cluster is handling a few thousand queries per minute. Is it safe to have a 3 node cluster?

What happens if 1 or 2 of the nodes go down, would there be a total outage of the database?

Is it recommended to have a 5 node cluster over a 3 node cluster?

Should I put them on RAID 1, RAID 0, or RAID 10? What is suffice?


texts: How can I reduce the memory usage of a tiny MEMORY table in MySQL?


texts: What is Clustered index data page behavior on inserts if all columns make up the index?

texts:
What really happens at the data page level if you have, for example, a 5 column table with all 5 of those columns being part of the primary key and you cluster the PK so every column is part of the index?

You expect 1000’s of inserts on the table and you arbitrarily set fill factor to 80%.  Since the pages have 20% free space for the inserts, what happens during the inserts to the records that already exist on the page and the new record would fits in between them?  Does a page split occur or does the rest of the records move on the page or something else?


texts: Run Mongodb Server as Service and mongod instance

texts:
I am using ubuntu and can run mongodb server by the following command

systemctl start mongodb

But, if i want to run Mongodb by mongod

mongod --dbpath /var/lib/mongodb

it gives me Permission Denied error.
Adding sudo before the mongod command fix the error and the Server starts.
But now, earlier command

systemctl start mongodb

won't work and says too many files open

So, I change the owner of the folder /var/log/mongodb and /var/lib/mongodb by the following commands.

sudo chown -R  mongodb:mongodb /var/log/mongodb
sudo chown -R  mongodb:mongodb /var/lib/mongodb

Now, MongoDb server started by systemctl command but again mongod command gives me error.
So, Is there any way to run Mongodb Server by both ways i.e, by using systemctl and mongod command.

Thanks


texts: Postgresql - Get Oid and relation name for relations created by the query

texts:
Consider the following TPC-DS benchmark query 50.

with ss as
(select ca_county,d_qoy, d_year,sum(ss_ext_sales_price) as store_sales
from store_sales,date_dim,customer_address
where ss_sold_date_sk = d_date_sk
and ss_addr_sk=ca_address_sk
group by ca_county,d_qoy, d_year),

ws as
(select ca_county,d_qoy, d_year,sum(ws_ext_sales_price) as web_sales
from web_sales,date_dim,customer_address
where ws_sold_date_sk = d_date_sk
and ws_bill_addr_sk=ca_address_sk
group by ca_county,d_qoy, d_year)

select /* tt */
    ss1.ca_county
   ,ss1.d_year
   ,ws2.web_sales/ws1.web_sales web_q1_q2_increase
   ,ss2.store_sales/ss1.store_sales store_q1_q2_increase
   ,ws3.web_sales/ws2.web_sales web_q2_q3_increase
   ,ss3.store_sales/ss2.store_sales store_q2_q3_increase
from
    ss ss1
   ,ss ss2
   ,ss ss3
   ,ws ws1
   ,ws ws2
   ,ws ws3
where
   ss1.d_qoy = 1
   and ss1.d_year = 2000
   and ss1.ca_county = ss2.ca_county
   and ss2.d_qoy = 2
   and ss2.d_year = 2000
   and ss2.ca_county = ss3.ca_county
   and ss3.d_qoy = 3
   and ss3.d_year = 2000
   and ss1.ca_county = ws1.ca_county
   and ws1.d_qoy = 1
   and ws1.d_year = 2000
   and ws1.ca_county = ws2.ca_county
   and ws2.d_qoy = 2
   and ws2.d_year = 2000
   and ws1.ca_county = ws3.ca_county
   and ws3.d_qoy = 3
   and ws3.d_year =2000
   and case when ws1.web_sales > 0 then ws2.web_sales/ws1.web_sales else null end
     > case when ss1.store_sales > 0 then ss2.store_sales/ss1.store_sales else null end
and case when ws2.web_sales > 0 then ws3.web_sales/ws2.web_sales else null end
     > case when ss2.store_sales > 0 then ss3.store_sales/ss2.store_sales else null end
order by web_q2_q3_increase;

While executing this query inside the postgres code, I want to be able to get the relation name from relid or Oid.
I am able to do that for relations web_sales, date_dim, customer_address and store_sales, i.e. I have their relids, from which I am able to get Oid and relation names.

But for relations ss and ws, which are created in the query, I have a relid, but I get their Oid as zero and thus relation name as null.

is there some way for ss and ws also I can get their names from the relids?
is ther no RangeTblEntry for ss and ws ?


texts: How to maintain visibility of all new transactions in append-only PostgreSQL DB without scanning the whole table

texts:
Situation

PostgreSQL v11

I have a database with a dozen tables. No rows are ever DELETEd or UPDATEd. A bulk of data is INSERTed to all the tables in a 'few' (up to 1,000) transactions every day. Some tables can add tens of GBs of data during the INSERT (the largest has has almost 2 billion rows as of now).

Problem

I have noticed that at some point SELECT queries I use to read the data from the DB stop using index only scans. After some digging it became apparent this is due to visibility map becoming out-of-date. This is confirmed by running VACUUM as it reverts back to using index only scans. However, VACUUM is very expensive in my case (can take over 10 hours for the largest table) and AUTOVACUUM is never triggered as there are no DELETE or UPDATE operations.

I have looked at running VACUUM FREEZE after each transaction but it seems it will need to scan the whole table after each transaction, which again is going to take ages.

Question

What is the best way to mark all the new transactions as visible for append-only PostgreSQL without scanning the whole table every time?


texts: I am upgrading from 11.1.0.7.0 to 11.2.0.3 with R12.1.3 while pre upgrade/catupgrd.sql erroring out ORA-00600

texts:
While upgrading from 11.1.0.7.0 to 11.2.0.3 with R12.1.3, during running the pre upgrade script catupgrd.sql, it is erroring out

ORA-00600: internal error code, arguments: [qcisSetPlsqlCtx:tzi
 init], [], [], [], [], [], [], [], [], [], [], []

I applied patch 10272702, the patch got applied successfully but still the persist.


texts: SQL Server service account Windows privileges and rights

texts:
My question is, if you create a new domain user account for each of the SQL Server processes, what permissions should be set for each account? Or does the SQL configuration manager actually take care of this, and I just had an unforeseen issue?

I quite often have to setup Microsoft SQL Server and wondered if anyone can provide advice on configuring the accounts the services should run as. IMO this has been vaguely documented by Microsoft, while they point you in the right direction I have never been able to find any concrete examples.

To summarize what I've seen so far:

For simple deployments\development environments it is OK to use the virtual account defaults the installer uses:
e.g. NT SERVICE\MSSQLSERVER

Avoid using the SYSTEM account, this is not secure.

For production and in domain environments it's recommended to use either a Managed Service Account, or create a domain user account (not an admin) for each service. Allegedly if you use a domain account at installation time the installer will set any required permissions for you.

If changing the service account on an existing install from a virtual account to a domain account the recommendation is to use the SQL Server configuration manager to set the new service accounts. Allegedly this will set any required permissions for you.

I just tried changing the service account in an existing install to a domain account and it would give me a logon failure until I granted the account log on as service permission, which contradicts the part where the SQL Server configuration manager will set any required permissions. (Although I'm not sure if a GPO may have interfered with setting this local security policy)

Microsoft does provide a list of the permissions that the SQL Server Setup grants on this page.

But it's not clear to me if that is something I should be doing manually for the user I create to run the service as, or whether using the SQL config manager should automatically set these permissions.

SQL Server 2014, Domain Controller is on Windows Server 2008 R2.


texts: Merge multiple InfluxDB 1.8 dbs into one + add tags with names of dbs

texts:
I have multiple InfluxDB 1.8 databases with same schema but different names of databases.
I need to merge them all into one database + add a tag with name of original database the data came from so I can query all that data using those tags.
Using what tools & exactly how to achieve that?
I've read about Kapacitor + tick scripts or Telegraf processors or dump data into backup file,process it somehow & restore but I'm not familiar with any of those paths so please advise.
Thanks.


texts: Do decimal separators behave differently for Money and Decimal data types?

texts:
I've recently been troubleshooting some problems with a third party application around regional settings and decimal separators. The detail of these problems isn't too relevant except to say that the application is a web-service reading to a SQL Server column with the data type money and that it appears to multiply the value because a decimal separator is being interpreted as a thousand separator.

This leads me to the question: In the course of my investigations I noticed that if I open SSMS and execute the following I get "1,00" returned:

declare @money money;
set @money = 1;
select @money;

However, if I execute the following against the same server then I get "1.00" returned:

declare @decimal decimal(18,2);
set @decimal = 1;
select @decimal;

My Windows regional settings here are configured to use a comma as the decimal separator, so on the face of it the money value is displayed correctly and the decimal is not.

What's going on here? It's unclear why the behaviour is different for these two data types. Is this down to SQL Server or is this caused by the .Net framework? (The application utilizes the .Net framework and presumably SSMS is doing so in the same way...)

I found the following ticket which seems to refer to the same issue but doesn't seem to provide an answer: https://connect.microsoft.com/SQLServer/feedback/details/636074/some-datatypes-doesnt-honor-localization


texts: The state of the MSSQL Options set during query after it ends

texts:
I am running a transactional query in SQL Server, and I want to turn XACT_ABORT on, so I write: SET XACT_ABORT ON.

First, in such case, would I have overwritten other settings done by another query?  I mean, are those options scoped at the query level or at the connection level? Perhaps at the server level?


texts: To what extent do the "non-durability features" affect COPY's performance?

texts:
COPY is documented as not being WAL-logged if it's loaded on the same transactions..

  In minimal level, WAL-logging of some bulk operations can be safely skipped, which can make those operations much faster (see Section 14.4.7). Operations in which this optimization can be applied include: CREATE TABLE AS,
  CREATE INDEX,
  CLUSTER,
  COPY into tables that were created or truncated in the same transaction

With that said, do any of the non-durability features speed up a pg_restore -1?

  -1 --single-transaction Execute the restore as a single transaction (that is, wrap the emitted commands in BEGIN/COMMIT). This ensures that either all the commands complete successfully, or no changes are applied. This option implies --exit-on-error.

COPY is a single transaction, will synchronous_commit do anything? Or does that only speed up batch-INSERTS?
Does making the initial table UNLOGGED matter if COPY ignores the WAL log anyway?
What about max_wal_size and checkpoint_timeout? I assume they would do nothing without WAL-logging?
To what extent does turning off fsync do anything other than playing with fire? Are we likely to see a speed up by turning it off?

question inspired by @Craig Ringer's answer on SO


texts:
If I want get Ubuntu system time, I have two options:

$ cat /etc/timezone
US/Eastern

$ date
Sun Sep 15 14:45:02 EDT 2013

How can I find out if PostgreSQL is using utc or a utc offset, such as US/Eastern?


texts: "Wide" vs "Long" Database Structure - Is there a logic when to use which?

texts:
I'm currently setting up a database to house records for an application I'm writing and am trying to come up with the most efficient DB structure to use... Of course I know that there are as many answers to this as there are people with opinions, but there has to be some kind of logic as to what to do for different situations.

So, as a simple example of my situation. Suppose I was creating the DB in, say SQL Server, to store data about people. Currently there are is a set of, say, 100 characteristics I am storing per person (Some static such as, say, name, sex, etc - Some RELATIVELY static such as age, address, height - and some variable, sich as, say, weight, last_worn_pice_of_clothing... whatever) and I also want the architecture to be flexible enough to be able to accommodate any future characteristics that may come about (this won't happen "too often", but will happen).

So, my logic is that I could go with one of the following storage ideas:

(1)

People_Chars_Table:

Person_Id:    RunDate:    Name: ...<all the other chars> ... Char100:
1             2014-01-01  John Johnson ....                  Val100

In other words, store all the characteristics in a very wide table and every time I have a new characteristic to add, I simply add on a new column.

(2)

Person_Table:

Person_Id:

Characteristic_Table:

Char_Id:    Char_Name:    Char_ValueType:
1           Name          String
2           Age           Integer
....

People_Table:

Person_Id:    RunDate:     Char_Id:     Char_Value:
1             2014-01-01   1            John Johnson
1             2014-01-01   2            23
....

What (2) seems to allow for is for characteristics to be added very quickly and easily, but it seems like querying out the data will always involve some kind of pivot operation that might slow things down (The 2 things that might also be important to know, by the way, is that this data will be queried fairly often and that the table would have about 100000 new people records added monthly - each with about 100-150 characteristics).

And, then, of course, there is a hybrid option of the person table having some of the pre-set characteristics and then using the rest from option (2).

Overall, I'm just wondering if there is any logic about what is the best practice or, aside from these 2 options I'm seeing, is there another way of handling this completely?

Any advice would be greatly appreciated!!!
Thanks!!!


texts: SSIS buffer size and table with many columns

texts:
I have an Oracle view with around 10 million rows, each row has about 130 columns, I wrote a SSIS 2012 package which uploads the data of this view into a SQL Server table. The SSIS package fails each time without giving specific error message, if I try to upload fewer columns from this view then I succeed uploading it.

I guess it's related to buffer size or memory issues.
which parameter in the SSIS i should modify to upload the whole view?


texts: Percona MySQL 5.6 databases ignoring key length at constrait creation

texts:
We have a 3 servers running Percona MySQL 5.6 in a Master/Master configuration that are all reporting errors on key length, either too long or not specified. The structure of the databases is all generated from a mysqldump on existing standalone MySQL 5.6 databases that do not report these errors.

Key too long error:

2015-04-13 11:00:29 26692 [ERROR] Slave SQL: Error 'Specified key was too long; max key length is 767 bytes' on query. Default database: 'dataBaseName'. Query: 'alter table UserGroup add constraint UK_f6x7vpmw83jptft2toy0wsfk6 unique (groupName)', Error_code: 1071

Table structure:

CREATE TABLE `UserGroup` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `groupName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
 `groupType` int(11) NOT NULL,
 `clientAccountSettings_id` bigint(20) DEFAULT NULL,
 `timestamp4support` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 UNIQUE KEY `groupName` (`groupName`(191)),
 KEY `FK8A5BE1545E3F3DC` (`clientAccountSettings_id`),
 CONSTRAINT `FK8A5BE1545E3F3DC` FOREIGN KEY (`clientAccountSettings_id`) REFERENCES `ClientAccountSettings` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Although the column used in the unique constraint reported in the error is 255 chars long using utf8mb4 (1020 bytes) we are only trying to use 191 chars (764 bytes) which works on the standalone system but not on the Percona system.

Unspecified key length error:

2015-04-13 10:56:25 26692 [ERROR] Slave SQL: Error 'BLOB/TEXT column 'name' used in key specification without a key length' on query. Default database: 'dataBaseName'. Query: 'alter table ISP add constraint UK_tfe1hfqbgydnfe2siovm1i80k unique (name)', Error_code: 1170

Table structure:

CREATE TABLE `ISP` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `name` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
 `abuseEmail` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
 `timestamp4support` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 UNIQUE KEY `name` (`name`(191))
) ENGINE=InnoDB AUTO_INCREMENT=465 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Again, as you can see we are trying to specify the index length (working in standalone MySQL) but it is getting ignored.

In order to try and get around the key too long error, I have turned on innodb_large_prefix but restarting the first instance caused MySQL to remove all data and resync all 2.7TB again, this is due to all the replication errors, of which there are quite a few, all the same as above. I still have to apply this to the other 2 servers.

Is there anything that could change the behaviour so it utilises the specified key length? At the moment this is only a test service so I can make changes but with the way things stand it will take days to get the changes across the cluster.

Does anybody know why the service ignores the key length parameter? Is this a bug?


texts: Storing Configuration for bespoke maintenance plans

texts:
I got fed up with creating maintenance plans on several SQL servers. So I've devised a script that generate a series of SQL Agent Jobs (with notifications) that utilises Ola Hallengren's SQL Server Maintenance Solution (http://ola.hallengren.com). It has a few configuration options like time of full backups and how often to run the transaction log backups etc.

I've tried to write it in a way that means I can update fairly easily (damn you typos!!) and then reapply to SQL instances whilst retaining the existing configuration from when it was first run. It's basic but it works.

I'd like to take it up a notch though and store that config, perhaps in a table. That however generates more sql in my script in maintaining it e.g. creating, updating values etc, but I think it would help in the long run.

Does anyone have any tips or suggestions? I'm wary of storing things in master/msdb but these seem to be the most sensible place. Is it really that bad? Indeed the default for Ola's Maintenance Solution is the master db.

Thanks in advance :)

EDIT: I guess the questions I am asking here are:

Should I be using the master or msdb to store configuration for my bespoke maintenance solution?
If not, where is best place to store the configuration? In a separate database? A config fie?

I'm keen that this works on an instance level rather than something central so that it can be used in a number of different scenarios.


texts: How to make a copy from a Emergency Database?

texts:
I've a database on a very old Computer (Windows xp, SQL server 8.0). Yesterday, the database has been marked as suspected, I've put it to the emergency state. So now the database is Read-Only. The only way to make it functional again is make a copy of its to another database. Previously, I use Backup to save the database to a File and restore to other database but now, the database is in Emergency mode so that this way is not working. I've tried to import/Export to other database but there is one problem, some key in some the table of the old Database was mark as Identity Specification and it will automatically generate. But now the new database is not mark as Identity Specification and the Null error is generate.

So why the import/export data doesn't make a exact copy of the old database to the new one? And how to make a exact copy to the database?


texts:
Is it possible to delegate responsibility for specific indexes for a collection to specific shards in a mongoDB database?

For instance, say I have a collection called 'books':

{ title: <string>, pages_in_book: <number>, price: <number>, author: <string> }

And say I want 3 indexes for this collection: one for the price, one for pages_in_book, and one for author.

Also say I have 3 shards.

Can I have each of these shards be responsible for updating one index each when a new document is inserted into the 'books' collection?

I want to do this because I plan to make a write-heavy application. User actions would initiate the creation of multiple documents at once and each document would need to be added to multiple indices like above (but five or six indices in my real case). I could distribute the writes using a shard key (with luck here, the documents' writes and it's writes to the collection's indices would be distributed evenly to the different shards but I could get unlucky if no good shard keys) or distribute the writes so that each shard is assigned an equal amount of indexes to be responsible for (extremely more likely for there to be a near even distribution of writes this way???)


texts: Input requested on localization setup for DB

texts:
I am building an ASP.Net site that will pull dynamic data from SQL server. The site will be built with localization/globalization foundations in anticipation that in a year or so the site will start holding translations for non-English speaking users. I already know how accomplish this in ASP.Net for the static content but I would like some suggestions on a DB design for the dynamic data. I have toyed with the following 3 ideas and would like some input on them and any thoughts on other ways.

Create a unique database for each language set and use ASP.Net to dynamically change which DB the data comes from.
Create copies of each table appending the local code to hold the translated data (i.e. Table-en, Table-fr, Table1-en, Table1-fr)
Leave DB and table design intact (only adding a single column to hold the local code) and insert translated data as a new row entry.


texts: select rows with min values of multiple columns


texts: Is it possible to enforce unique values in a column, excluding nulls?

texts:
I have a business requirement to have a FK field on TableA referencing TableB.  If there is a value in TableBID, it should be unique, but it's not mandatory, so there could be multiple records with null values.

Is it possible somehow to enforce this type of uniqueness on the DB level, using either an index or a constraint?


texts: user can select all structure from pg_catalog.*, but can't make a dump -s

texts:
Given: Postgres RDS 9.3, pg_dump 9.3, RDS user (not SU)

I have:

pg_dump: [archiver (db)] query failed: ERROR:  permission denied for relation rel
pg_dump: [archiver (db)] query was: LOCK TABLE public.rel IN ACCESS SHARE MODE

When I run :

-bash-4.2$ pg_dump -s --section=pre-data -h hostname -d db -U not_su

(tried both --schema-only and --section=pre-data in desperate hope they would differently not require a lock, but no success)

Yet:

When I login to db with same user, I can \dt+ rel and see the structure, so I can see definition from pg_attributes and pg_class for this rel and all related relations...

Is there any way to overcome this limitation?.. Or I have to build DDL based on pg_catalog?.. (seems not reasonable that my user can select all structure, but can't make a structure only dump)

Update:

I must be very unclear In my question. Here is example:

As superuser:

t=# create table so15(i bigserial primary key);
CREATE TABLE
t=# create user so15;
CREATE ROLE

As user without permissions:

postgres@vao-VirtualBox:~$ psql -U so15 -h localhost -d t
psql (9.6.1)

t=> \d+ so15
                                            Table "public.so15"
 Column |  Type  |                    Modifiers                     | Storage | Stats target | Description
--------+--------+--------------------------------------------------+---------+--------------+-------------
 i      | bigint | not null default nextval('so15_i_seq'::regclass) | plain   |              |
Indexes:
    "so15_pkey" PRIMARY KEY, btree (i)

So I can see the structure of so15 relation without read permissions, now as same user I pg_dump -s:

postgres@vao-VirtualBox:~$ pg_dump -s -t so15 -U so15  -h localhost -d t
pg_dump: [archiver (db)] query failed: ERROR:  permission denied for relation so15
pg_dump: [archiver (db)] query was: LOCK TABLE public.so15 IN ACCESS SHARE MODE


texts: Couchbase backup and restote through copy data files

texts:
I was exploring couchbase backup and restoring procedures then we found standard procedures. But, if I can take data directory backup in the form of .tar and untar data directory whenever restore required, will it work?

tar -cvf /opt/couchbase/var/lib/data  -----backup

tar -xvf xxx.tar  ----restore


texts: Export list of all tables in PostgreSQL server, showing schema and db to CSV

texts:
I'm looking at tidying up my PostgreSQL server as I currently have several databases and more schemas within them.

I'm hoping there is an easy way to export a list of all tables, with their respective schema and database to a csv (or similar). This would allow me to see an entire list of what is on the server and use it to see what's no longer needed.

I've tried looking around for an answer on stack but possibly don't know how to word it, so haven't found anything yet.

Is this possible and if so, how?

(I use PGAdmin and am running PostgreSQL 12 on a Windows environment)


texts: Select statement to table with percentage column names

texts:
I am selecting data from one table that have percentage sign in some column names like:

| name | val% | vale2% |

This table is ofted use with select * from table, but in this case I need only the field names with percentage sing, when I run a select in query tool of pgadmin this notice this error:

ERROR:  syntax error at or near "as"
LINE 1: SELECT val% as value, val2% as value2 from table

How I can run this query and get only val% and val% without the rest of fields.

NOTE: I can change the database structure, only query it data


texts: Counting Rows based on values in other columns

texts:
I'm trying to count the rows of an entire table (approx 3000 rows) based on the content of a column. In the column in question, there can be a single text value or a concatenation of text values separated by commas.

To use the student/class analogy, the table could be a list of students, with the column containing one or more classes to which they are assigned.

Thus "Student1" may belong to "class1, class2, class3" (etc..), while "Student2" may only belong to "Class2".

I simply want the student counts of all classes, thus:

Classes Students
Class1    nn
Class2       nn

and so on.

Please note that this is how the data is received (as a csv file) and I don;t really want to manipulate it unless I have to.  I would prefer to simply execute SQL to produce the results, if possible.

Can anyone help?

Thanks,
Geoff


texts: Restoring a Database Using Full Backup and Transaction Logs in SSMS

texts:
I have a full backup file (.bak file) of a database for the 25/10/2019. However I want to restore the database to the 21/10/2019.

My problem is that I do not have the full backup files for the 21/10/19. The last full backup for the database I have is 25/10/19. What I do have are transaction logs going back to the 21/10/19.

In SSMS I have tried using the full backup file for 25/10/2019 and using the preceding transaction log files to restore the database to 21/10/19; however, it does not work and says unable to create restore plan due to break in LSN chain.

Please note a full backup of the database is made each night and every 30 minutes for the transaction logs.

The full backups prior to 25/10/2019 have been deleted.

Is it still possible to restore the database to a point in time using transaction logs? Either within SSMS or using a T-SQL command?


texts: Does the spec permit column-count mismatch on INSERT?

texts:
I'm just wondering if the spec permits excluding the column-list clause in an INSERT statement if you're inserting in fewer columns than the table provides. I see PostgreSQL does, and MySQL and SQL Server do not.

With PostgreSQL, (works)

# CREATE TABLE t (a int, b int);
CREATE TABLE
# INSERT INTO t VALUES (42);  -- (b is set to DEFAULT)
INSERT 0 1

With SQL Server, I get

Msg 213 Level 16 State 1 Line 1
Column name or number of supplied values does not match table definition.

With MariaDB, I get

Column count doesn't match value count at row 1

What's the right behavior if there is a column-count mismatch?

Question inspired by the response and comments to this question here


texts: Postgres performance testing, caching and flushing

texts:
I been doing some testing on various postgres SQL queries.

Testing often involves changing the syntax of queries, altering table joins, or occasional completely re-writing the query.

I've noticed that I sometimes get an 'apparent' big performance increase. I'll run a query, it'll take (say) 60 seconds to run, I'll make a minor change and it'll then take (say) 5 seconds to run.

At first I though that this was because my minor tweak had improved the performance. I've since realised that actually there must be some caching going on, (to see this, try running a 60 second query, and then running it again a few seconds later - it will always run quicker the 2nd time), I assume this is because the data has been cached locally somewhere so when the data needs reading a 2nd time it's already to hand.

I'm sure this a useful performance feature, but it does make it very hard to spot genuine performance improvements when tweaking a query. Is it possible to flush the cache before each execution to ensure that each test starts from the same position?

Thanks


texts: 'Cleanse' a SQL Server database file created with Instant File Initialization enabled?

texts:
I have Instant File Initialization enabled on our SQL Server, so the 'empty' part of the database file is not zeroed before being allocated.  My understanding is that this means that the file could contain 'deleted' data.

So now I want to send a copy of a database (probably a backup file) outside of the company.  But there's all that potentially sensitive 'deleted' data sitting around inside the file.  Now I would like to zero the unused portion of the file.

Is that possible?  I imagine I could create a new database and copy everything over, or perhaps restore a copy of the database to another server without Instant File Initialization enabled and then be aggressive with a ShrinkFile command to remove most or all of the unused portion of the database file, but is there a less manual and time consuming method?  Ideally a command to tell SQL to zero the file as it would have done if Instant File Initialization was not enabled.


texts:
Suppose I have the following three tables: SupportTicket, Device, and Link. I've created these in SQL Fiddle. I want to display a list of support tickets within a time range that meet specified criteria and show a count of the types of devices linked to it. The problem is that my current query is duplicating Support Tickets rather than counting all the devices linked to it on one row. Here is my query:

SELECT
  st.SupportTicket_ID,
  st.Submitting_User,
  st.Submitting_Department,
  CASE
    WHEN d.Type = 'Cellphone'
    THEN COUNT(d.Device_ID)
    ELSE 0
  END AS [Mobile Devices],
  CASE
    WHEN d.Type = 'Computer' OR d.Type = 'Disc'
    THEN COUNT(d.Device_ID)
    ELSE 0
  END AS [Computers],
  CASE
    WHEN d.Type = 'Camera' OR d.Type = 'Video Cassette'
    THEN COUNT(d.Device_ID)
    ELSE 0
  END AS [Audiovisual]
FROM
  SupportTicket st
  JOIN Link l
    ON st.SupportTicket_ID = l.Entity_ID1
    OR st.SupportTicket_ID = l.Entity_ID2
  JOIN Device d
    ON l.Entity_ID1 = d.Device_ID
    OR l.Entity_ID2 = d.Device_ID
WHERE
  l.Link_ID LIKE 'CAS%'
  AND st.Type = 'Tech Support'
  AND st.Date_Submitted BETWEEN '20140201' AND CURRENT_TIMESTAMP
GROUP BY
  st.SupportTicket_ID, st.Submitting_User, st.Submitting_Department, d.Type
ORDER BY
  st.SupportTicket_ID

Here is the SQL Fiddle instance where you can see what is currently returned by this query.

A support ticket should only occupy one row with a count of how many devices (by type, as shown) were linked to it in the columns on that row.

Below is the desired output:


texts: SELECT with equal chance between same IDs - MySQL

texts:
How can I SELECT data with equal chances for same IDs in MySQL (for php). For example, assume below data structure:

 id  value
 1   alfa
 1   beta
 1   gamma
 2   hello
 2   bye
 3   sky

I want (alfa, beta, gamma), (hello, bye) and (sky) have the same chances. Here is the probability I need for every row:

alfa: 1/3 * 1/3
beta: 1/3 * 1/3
gamma: 1/3 * 1/3
hello: 1/3 * 1/2
bye: 1/3 * 1/2
sky: 1/3

Thank you.


texts: Where can I get best resources to learn sql?

texts:
I am very new to the databases just have basic knowledge about it.

I want to learn sql language and dont know where to start. Please help me to start with it.
And what is the difference between sql and PostgreSQL? What are the different languages to maintain databases.

Any help will be appreciated.


texts:
As I understand it, if you select json_value or json_query specifying a key or object that does not exist in your json document, then in strict mode, you will receive an error. This allows you, for example, to confirm whether the key was specified in the document.

If an example key-value pair is included in the document thus:

"Test":null

... this will, in strict mode, return 'NULL'. In other words, you now know the key is defined in the document and its value is null.

Now imagine your document contains:

 "Test":"Some string"

And you query:

 select json_modify(@json, '$.Test', null);

This will return a json string lacking the "Test" key.

How do you properly set a key value to null, as opposed to removing it from the json document?

(The above example may seem nonsensical, but I can imagine setting a value to null before passing the json document on to the next query or system, and requiring that your "Test" key exist in the document.)


texts:
I followed below steps after SQL Server and Integration Services, but I am still getting an error.
I added SQL service account in Microsoft SQL Server Integration Services 13.0 properties which is under DCOM Config, and granted remote permissions to both service account and dba group account under Launch & Activation Permission & Access Permissions but still getting an error:

connecting to the Integration Services service on the computer SERVER failed with the following error:
Access is denied.
By default only administrators have access to the Integration Services service. On Windows Vista and later, the process must be running with administrative privileges in order to connect to the Integration Services service.

Windows 2016 Standard and SQL Server 2016 Standard Edition.


texts: MySQL - CSV UPDATE (not INSERT) into existing table?

texts:
I have a table with the following columns:

id | name | city

The table has 1000 entries in it.

I have a CSV file with a limited number of lines like this:

id,city
34,Denver
45,Kansas City
145,New York

Can I use LOAD DATA INFILE to import my CSV into my table but instead of inserting new entries, it updates rows based off the id column?


texts:
I have run the following commands found here.

shell> mysql
mysql> FLUSH PRIVILEGES;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass'

But when I go to login as root, I can type any string as a password, and it will still let me login.

How can I enforce a password for root, and not allow anonymous users by simply typing mysql


texts: Clustered columnstore indexes - Do I need to create a non clustered index on a column that's used in a join?

texts:
I have two very large tables that need to be joined to produce a data extract. Both tables have clustered column store indexes and are partitioned.

DW Sentry advises me in the plan view that it thinks a NC index should be added, but without compression, it's likely to be a very large index. Does anyone know about the join characteristics for a columnstore?


texts: Separate table by often changed / not often changed data?

texts:
I have a application which querys different gameservers, and stores for example if the server is online, and how many players are connected. But of course there is also data which is changed rarely.

Currently I have all this in one table.

So my question is: does it make a performance difference if I split this table up, so that often changed data is in one table, and things like the description in another?

(I have to add that I always only select the data I need, so not like Select *). But when I select the data I often have to select the often changed data + the rarely changed data at once.


texts: Transfer data from database1 to database2 using task queue

texts:
We have a SQL Server legacy database, db1, to put data of Project1. With change of certain tables(insert/update) in db1, we need to transfer the changes in another postgres database, db2 (used by Project2).

Presently the system is adhoc. With any change of table1 in db1, we keep the record (table1, id, actiontype) in a 'Queue' table, upon which a cron job runs every 5 minutes, which transfers data to db2 using the information in db1.Queue.

Problem is, following this way, we can't see real-time reflection in db2, i.e. data from db1 wouldn't immediately propagate to db2. We have to wait for the cron job to run.

Any suggestion to improve the solution highly appreciated.

Is distributed async task queue (e.g. Celery) a good option here?


texts: Is it legal for SQL Server to fill PERSISTED columns with data that does not match the definition?

texts:
I'm following up on this question about strange values in a PERSISTED computed column. The answer there makes a few guesses about how this behavior came to be.

I'm asking the following: Is this not an outright bug? Are PERSISTED columns ever allowed to behave this way?

DECLARE @test TABLE (
    Col1 INT,
    Contains2 AS CASE WHEN 2 IN (Col1) THEN 1 ELSE 0 END PERSISTED) --depends on Col1

INSERT INTO @test (Col1) VALUES
    (ABS(CHECKSUM(NEWID()) % 5)),
    (ABS(CHECKSUM(NEWID()) % 5)),
    (ABS(CHECKSUM(NEWID()) % 5)),
    (ABS(CHECKSUM(NEWID()) % 5)),
    (ABS(CHECKSUM(NEWID()) % 5))

SELECT * FROM @test --shows impossible data

UPDATE @test SET Col1 = Col1*1 --"fix" the data by rewriting it

SELECT * FROM @test --observe fixed data

/*
Col1    Contains2
2   0
2   0
0   1
4   0
3   0

Col1    Contains2
2   1
2   1
0   0
4   0
3   0
*/

Note, that the data appears "impossible" because the values of the computed column do not correspond to its definition.

It is well known that non-deterministic functions in queries can behave strangely but here this seems to violate the contract of persisted computed columns and, therefore, should be illegal.

Inserting random numbers might be a contrived scenario but what if we were inserting NEWID() values or SYSUTCDATETIME()? I think this is a relevant issue that might practically manifest itself.


texts: Import Sybase SQL Anywhere 8 DB into SQL Server without access to Sybase

texts:
This is my first post here and its a doosy. I am facing what seems to be a unique situation at the moment. I am in the process of moving my client to a new environment which includes decommissioning super old equipment. One of the last remaining pre-2008 servers is Windows 2000 IBM server running a Sybase SQL Anywhere 8 database with Jaguar. Since this server is so old and still in light use, my primary concern was taking a full backup of data in the event of failure.

The OS on this server is in bad shape and running any exe causes it to hang, so I could not install any third-party backup. I took a full drive and state backup with the native windows backup utility (in safe mode) into a bkd file. After taking a backup now the Jaguar service always hangs on start, so its essentially broken. Rather than fix a 2000 server I want to find a way to get this old database onto a new server.

I restored that backup to a temp 2008 VM by copying the dlls for NTBackup off of the 2000 server so it could hand bkd. This way I could experiment on a non-prod copy of the data.

As one would expect the software doesn't work on a restored copy, partly due to 2000 using WINNT in place of system32, so my focus has shifted to converting the Sybase db file to SQL Server. I did find "Stellar Converter for Database" and ran the db file through that on a trial as a proof of concept and the data preview is in fact readable, but unfortunately I don't think my client will pay the $200. It seems well worth it, but my hands are tied on that front.

I also found the Microsoft "SQL Server Migration Assistant for Sybase" and it seems promising, however it requires I connect to the Sybase db itself, which I cannot do since it is broken. I also tried searching the internet for a download of SQL Anywhere 8 or 9 with no luck. I did find 12, but it was client only.

The previous IT also did not document the logins for this database at all, so its looking grim.

Does anyone have any pointers on options to get this Sybase 8 .db file into a Microsoft SQL Server 2017 (I have ISOs of older ones on hand) .mdf file without access to the actual old db server, and without any credentials?

I know I recently reset an SA account on another of our old SQL Servers by putting the db in offline mode and running some sqlcmd magic, but I am no way familiar with Sybase.

I do not have high hopes and I am fully aware that the answer may be "its dead Jim". I appreciate any input, even if its not what I want to hear.


texts: Unable to start SQL Server on Ubuntu 16.04

texts:
I had SQL Server v.Next public preview on my Ubuntu 16.04 machine installed and working yesterday. But it's not working today.

Getting below error:

root@OraServer:/var/opt/mssql/log# systemctl status mssql-server
● mssql-server.service - Microsoft(R) SQL Server(R) Database Engine
   Loaded: loaded (/lib/systemd/system/mssql-server.service; enabled; vendor preset: enabled)
   Active: inactive (dead) (Result: exit-code) since Sun 2016-11-27 13:34:23 IST; 18s ago
  Process: 6368 ExecStart=/opt/mssql/bin/sqlservr (code=exited, status=1/FAILURE)
 Main PID: 6368 (code=exited, status=1/FAILURE)

Nov 27 13:34:23 OraServer systemd[1]: mssql-server.service: Unit entered failed state.
Nov 27 13:34:23 OraServer systemd[1]: mssql-server.service: Failed with result 'exit-code'.
Nov 27 13:34:23 OraServer systemd[1]: mssql-server.service: Service hold-off time over, scheduling restart.
Nov 27 13:34:23 OraServer systemd[1]: Stopped Microsoft(R) SQL Server(R) Database Engine.
Nov 27 13:34:23 OraServer systemd[1]: mssql-server.service: Start request repeated too quickly.
Nov 27 13:34:23 OraServer systemd[1]: Failed to start Microsoft(R) SQL Server(R) Database Engine.

I couldn't see anything in SQL server error logs. You can say no logs at all after the last reboot.


texts: When to Disable and Recreate Indexes in Data Warehouse ETL?

texts:
We are moving data from Staging Database into Kimball Data Warehouse DB.
I am hearing different ideas regarding when to disable and rebuild indexes.
Stage tables are truncated every day for each new load. Lets assume we have a customer transaction table, 20 million rows, 5GB. Initial load is 20 mil, and incremental is 100,000 rows everyday (less than 5%)

I am reading its best to disable indexes on new batch fresh loads.

(a) If stage tables are truncated everyday, for Stage: Should I disable index and reenable everyday?

Alter index all on dbo.[StageTable] disable

Insert Into StageTable values (CustomerId, SalesDate, SaleAmount)...

Alter index all on dbo.[StageTable] rebuild

In the actual data warehouse,

(b) Should I reenable and rebuild index for initial load. Should I do this for incremental loads also which are minor?

Alter index all on dbo.[DWTable] disable

Insert Into StageTable values (CustomerId, SalesDate, SaleAmount)...

Alter index all on dbo.[DWTable] rebuild