Best solution to keep a big table synched in 2 DB I manage a few tables with more than 1 billion records. Daily, their ETLs run, reading data from flat files and inserting a few thousand records on each of them.

Now I need to keep these same tables also on another DB, and of course they need to have the same data. Data will be transferred using Linked Server.

The easiest way would be to truncate the destination tables, select on source inserting to destination, and then rebuild its index. But I don't like the idea of copying the same data every day.

Another solution I see is to query both source and destination tables, distincting by date dimension, and then copy only records from dates that are missing. I'd then create a monitor to see if the amount of records per day are equal and highlight not matching days.

Is this second solution good enough, or is there a best practice for this situation?

If I had to manage this, I'd look at what, if anything, modifies the data in the tables outside of the ETL process.

If nothing else besides your ETL modifies the data, I would simply update the ETL process to insert the finished data at both locations (and likewise carry out whatever index maintenance you're doing in both places).

If something else updates this data, but only one one server, then transactional replication is probably the most lightweight way to get the data to the secondary server. Even if the data isn't being modified outside of the ETL, then this wouldn't be a terrible alternative to modifying the ETL process to update two targets. It sounds like a relatively small percentage of data that's being inserted daily.

If the data is being modified on both servers, then you'll probably want to consider merge replication. The simplicity of this will largely depend on if the table has any identity columns.


What should I consider when deciding between passing a comma-delimited string to a stored procedure instead of calling it individually per record? I have to run the same stored procedure for a lot of records (hundreds or thousands, depending on user selection). The procedure is fairly basic: insert a record in two separate tables with some calculated values, and optionally update two columns on a 3rd table.

My application has a List<T> of records, and I'm trying to decide if I should loop through the list and run the procedure on each record, or combine the id values into a single string and make a single database call. If I make a single database call, then SQL would be responsible for converting the comma-delimited string to a temp table and running the scripts on all records at once.

I'm leaning towards passing a comma-delimited string of Ids to the procedure, but I am not sure if this is an ideal way of doing this kind of mass-update, or if I am missing some key factor that would affect this decision.

What should I consider when deciding if I should pass a comma-delimited string of ids to a stored procedure instead of calling it individually per record? Or is there a more efficient way to do this kind of update from an application?

I am using SQL Server 2005 and Entity Framework

Personally I would choose to pass a list of id's in as a table parameter to the stored procedure this would then allow you to do a set-based update instead of a row by row one which is less efficient.

I have never personally used the EF but a good artical on performing the above using ADO is below (ignore the fact it says it is for SQL 2008 as it also works on 2005). The same strategy would work better for you in this situation but you may need to adapt the implementation based on the fact you are using the Entity Framework.

http://www.mssqltips.com/sqlservertip/2112/table-value-parameters-in-sql-server-2008-and-net-c/

EDIT

As you rightly pointed out I am wrong about the fact this works on 2005 - sorry about that!

However, I have some alternate suggestions.

As SQL Server 2005 does support table variables (just not as parameters to stored procedures as you pointed out) you could parse the delimited string and insert the id's into a table variable. You could then use the table variable to perform a set-based update.

Alternatively the link below provides a different take on the same problem by persisting the values to a table first thereby avoiding serialization and de-serialization of the id values:

http://weblogs.sqlteam.com/jeffs/archive/2007/06/26/passing-an-array-or-table-parameter-to-a-stored-procedure.aspx

I hope this helps you.


SQL Server : huge log file, we know why, but what can we do about it We have this SQL Server database that is fully backed up daily. Recently we noticed that the LOG file for this database is huge (> 50 GB). I did some research and I found out that it is because we weren't taking transaction logs.

So I know why my log is so big, but I'm wondering what I can do about it.

Clarification: we want to use the 'full' recovery option with regular transaction log backups. We realize we aren't right now and this will be fixed as soon as we can get the transaction log to a reasonable size.

The ideal route would be to take a log backup and then do a DBCC Shrink to get the file down to a normal size. However, if I do this on a full backup of the database, this takes a few seconds and creates a log backup of 5 GB and shrinks the transaction log way down to a few MB.

If I do this on the production environment, it takes really long and it seems to create a file far bigger. I estimate this will be about 50 GB. Is there anything I can do about this, because the server just doesn't have another 50 GB, so taking the log backup in this way isn't an option.

Since we weren't taking regular log backups, we only have one recovery point per day I suppose (the full backup taken every night), so is there a way to delete the log and free up the 50 GB of the log file in another way?

Or are there any other options you can think of that might help me in this situation?

You've said that it is in FULL recovery mode currently, and you want to keep it that way, but you haven't said whether you might need to roll back to any point-in-time prior to today.

Here are your two choices:

If there is any possibility you might need to restore to a past point-in-time, you need to fully back up the existing (huge) tran log (and keep the backup file). So go ahead and run that tran log backup. Yes, it will take a very long time, and yes, it will require a lot of disk space. But once that is done, each new tran log backup will be small and fast, and will only contain transactions from the last successfully log backup. You will be able to shrink the log down to something reasonable.
If you don't anticipate ever needing to roll back to a prior point-in-time (like if a disaster happened, you'd just restore one of the prior nightly fulls anyway), and you only care about future possible point-in-time recoveries, then switch to SIMPLE, shrink the log, switch back to FULL, run a full backup, and start your log backups. This will clear the huge backlog of completed transactions.

To be crystal clear, both SIMPLE and FULL recovery mode would allow you to restore to any prior full (or differential) backups. FULL recovery mode allows you to restore to an intermediate point between two full backups.

So let's say your nightly full backups happen at 8pm, and your database was irretrievably corrupted or data was accidentally deleted at 10am the next day. With SIMPLE mode (or without available tran log backups), your only choice is to restore the 8pm full backup from the prior night. With FULL recovery mode (if you've taken and kept tran log backups), you could restore the database to immediately before the problem occurred, at 9:59:59am.

Of course if you would never need point-in-time recovery at all (it's a reporting or processing database where you could simply restore the full and then reimport/reprocess all subsequent data from other sources), then you could just switch to SIMPLE mode permanently and forget about log backups altogether (although SQL still uses the tran log for in-progress transactions, then marks the space for re-use when they are complete).

But this is a business decision, not a tech decision. Talk to your managers or business stakeholders.


sync all tables between 2 databases I need some information on how to synchronize all tables and views in sql server. What I wanted to do is basically I want all production server has the same tables and views structures as the staging server.

Can anyone point me to the right direction?

Take a look at SQL Server Data Tools (SSDT).  The command-line SqlPackage utility can be scheduled to Extract and Publish schema.  See https://msdn.microsoft.com/en-us/library/hh550080(v=vs.103).aspx.  Some operations cannot be done automatically, such as refactoring a single table into two separate ones.  Those operations will require custom scripts for data migrations.

There is also a schema compare tool from RedGate, SQL Compare, that provides similar functionality.  They also provide an automation API product.


Reducing the I/O in a drive I have a drive named J drive, where all temp db files(log and data files), main database log files and another database log and data files(which holds intermediate processing tables) exist.This drive has 1.5 TB space, and above mentioned files occupy 1.2 TB space and fluctuates extremely(as it contains log and temporary processing tables).

Main db database files(primary and secondary) exist on another drive( K drive) which has 2 TB space, and occupy around 1 TB space.

J drive has high I/O, as a  lot of calculations and processing exist in our DB.
I am planning to separate data and log files for each DB (Main DB,Temp DB and temp_data_processing db ) to J and K drive to reduce I/O.Will it help reduce I/O in the drives ?

Will it be better to add another drive ( e.g., P drive) and place temp DB log and data files in that drive ?

Thanks in advance for your kind reply !!!

Yes, separating data files and logs files will help to reduce I/O.
The best practices for SQL Server are separated log, data, temp, backup files on several disks.
Example :

data db files (.mdf) : drive D
log db files (.ldf) : drive L
tempDB files (.mdf & .ldf) : drive T
backup files (.bak) : drive B

You can also improve performances by separating tempDB in x differents files (on x differents drives) where x is the number of your CPU cores.
Use SSD drives for tempDB files and db log files. Put indexes in different file on another drive.

Be aware that if you're using virtualized environment you need physically separated disks, or where your vdisks have fixed IOPs throttles vdisks with their own IOPs quota, to have full advantage.

  Placing both data AND log files on the same device can cause contention for that device, resulting in poor performance. Placing the files on separate drives allows the I/O activity to occur at the same time for both the data and log files.

https://docs.microsoft.com/en-us/sql/relational-databases/policy-based-management/place-data-and-log-files-on-separate-drives?view=sql-server-2017

https://searchsqlserver.techtarget.com/tip/Optimize-disk-configuration-in-SQL-Server

Don't forget to perform maintenance tasks as rebuild/reorganise indexes...


Can you use CDC on a subscriber for transactional replicaiton Is there a way with SQL Server to have a limited number of tables replicated to another server using transactional replication and then use CDC or Change Tracking ONLY in the server replicated to?

You do not mention whether you are using SQL Server 2012 or 2014, however the documentation on CDC and Change Tracking do not show much difference.

Both make the following statement: "CDC configuration is always performed on the current or intended primary replica."

http://msdn.microsoft.com/en-us/library/hh403414(v=sql.120).aspx#CDC

The above link is for SQL Server 2014 and offers many more details.


sp_BlitzIndex performance concerns in production No real concern but I'm looking for some information of possible impact of running it on a busy server.  Should I Be concerned?

Would there be any loss of fidelity in the results by running it on a restored instance?

Thank you.

When you run it on a restored instance, you won't get any data about which indexes are getting used, and which indexes SQL Server wishes it would have had.

So it's not really all that useful - however, it IS useful to understand how long sp_BlitzIndex will take to run. If you're worried about performance impacts, you can run it against a restored instance on similar hardware, and that'll tell you about the performance impact - but just don't take any actions based on the output.


Unable to get a join to pull all records from another table in MySQL I've got this working in MSSQL but I've fallen off and been run over when it comes to MySQL.  I'm trying to search for all records in a group after a certain date in an employee info table.  I can get the records in the employee info table but I also need to return all employee numbers in another table that are part of the same group.  The purpose being to see which employees in the group have submitted some paperwork. Since the table has data from multiple years, it needs to filtered so I can search after a certain date each year.

Here are my two tables

empInfo
--------------------------------------
id             | int
date           | datetime
empNumber      | varchar
grpNumber      | int
active         | int

empReports
--------------------------------------
id             | int
date           | datetime
empNum         | varchar
grpNumber      | int
empFName       | varchar
empLName       | varchar
dateReceived   | varchar
active         | int

The idea is to search for a group number and return all employee IDs from the empInfo table and show which ones have records from the empReports table. The current query is:

SELECT r.dateReceived as Date,
t.empNumber AS Emp,
r.grpNumber AS Grp,
r.empFName,
r.empLName
FROM empInfo t
LEFT JOIN empreports r
ON t.empNumber = r.empNum
WHERE t.grpNumber = 100
AND t.active = 1
AND r.dateReceived > 1587574487
ORDER BY CAST(t.empNumber as UNSIGNED INTEGER) ASC

This returns a single record

    Date    |  Emp  |  Grp  |  empFName  |  empLName
------------------------------------------------------
1589561752  |   1   |  100  |  Fred      |  Derf

What I need to see is more like

    Date    |  Emp  |  Grp  |  empFName  |  empLName
------------------------------------------------------
1589561752  |   1   |  100  |  Fred      |  Derf
    NULL    |   2   |  100  |  NULL      |  NULL
    NULL    |   3   |  100  |  NULL      |  NULL
    NULL    |   4   |  100  |  NULL      |  NULL
    NULL    |   5   |  100  |  NULL      |  NULL

I've tried different joins, swapping the query around and just about anything else I can think of with no joy, so any assistance any MySQL gurus could offer would be greatly appreciated.

Thanks in advance.

The problem is this:

If r.dateReceived is NULL, the WHERE r.dateReceived > 1587574487 is never satisfied

What you need to do is check for r.dateReceived in the WHERE clause as well

SELECT r.dateReceived as Date,
t.empNumber AS Emp,
r.grpNumber AS Grp,
r.empFName,
r.empLName
FROM empInfo t
LEFT JOIN empreports r
ON t.empNumber = r.empNum
WHERE t.grpNumber = 100
AND t.active = 1
AND (r.dateReceived > 1587574487 OR r.dateReceived IS NULL)
ORDER BY CAST(t.empNumber as UNSIGNED INTEGER) ASC

GIVE IT A TRY !!!


Is this the correct way of moving MySQL data directory, (on Centos)? I need to move my data directory to a bigger drive on the same server.

# stop mysql
/etc/init.d/mysql stop

# copy the data over
cp -R -p /var/lib/mysql /home/mysql

#edit my.cnf
# and add a 'datadir' section with the new path.

#restart mysql
/etc/init.d/mysql restart

The copying of the data will take ages, (I suspect), but at least it will be safe.

I might also want to add a temp directory somewhere.

Do I need to do anything else to move the data.

Will I need to rebuild indexes or something else?

Ideally you would keep that folder, and instead of changing it you'd either mount another folder or partition on it prior to starting mysql, or create a symlink elsewhere (there are answers for doing this here and here).

You won't need to rebuild anything for moving your files, but it is a good idea to always make a backup before attempting something like this that may damage your data if anything goes wrong.


Troubleshooting Failed SQL login from WMI service Every 5 minutes I have a couple of failed login attempts from a Biztalk server to the SQL server.

  Login failed for user 'DOMAIN\SERVER01$'. Reason: Token-based
  server access validation failed with an infrastructure error. Check
  for previous errors

I traced the failed logins to back to a service on the Biztalk server. The failed PIDs were pointing to a wmiprvse.exe that was using a LOCAL SERVICE.

I know I could either grant the Biztalk server access to the SQL server or I could change that service to run with a different account BUT I'd like to know what is using the WMI service before doing this.

How can you tell what is using the WMI service?

tracing WMI activity

http://msdn.microsoft.com/en-us/library/windows/desktop/aa826686%28v=vs.85%29.aspx

Logging WMI activity

http://msdn.microsoft.com/en-us/library/windows/desktop/aa392285%28v=vs.85%29.aspx


Profiling parameters I was trying to fine the parameters of a query I suspect to be causing unnecessary logical read and eventually captured it in profiler, however parameters @p3 and @p4 are not listed.  Is anyone able to explain was SQL server is doing here?

Many thanks

declare @p1 int
set @p1=1073764508
declare @p2 int
set @p2=180527133
declare @p5 int
set @p5=16
declare @p6 int
set @p6=1
declare @p7 int
set @p7=0
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 int,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int',N'SELECT A.POPUP,A.EMAIL,A.CREATEDBY,A.BATCHJOBID,101090,B.EMAIL,B.EVENTPOPUPDISPLAYWHEN,B.EVENTEMAILALERTSWHEN,B.ID,101090,C.LANGUAGE,C.ID,101090,D.RECID,D.STATUS,D.CAPTION,D.COMPANY,D.BATCHJOBID,D.STARTDATETIME,D.STARTDATETIMETZID,D.ENDDATETIME,D.ENDDATETIMETZID,D.ORIGSTARTDATETIME,D.ORIGSTARTDATETIMETZID,D.CANCELEDBY FROM BATCHJOBALERTS A,SYSUSERINFO B,USERINFO C,BATCHJOBHISTORY D WHERE (A.CREATEDBY=B.ID) AND (C.ID=B.ID) AND (((D.ALERTSPROCESSED=@P1) AND (D.BATCHJOBID=A.BATCHJOBID)) AND ((((D.STATUS=@P2) AND (A.BATCHJOBENDED=@P3)) OR ((D.STATUS=@P4) AND (A.BATCHJOBERROR=@P5))) OR ((D.STATUS=@P6) AND (A.BATCHJOBCANCELED=@P7))))',@p5 output,@p6 output,@p7 output,1,4,1,3,1,8,1
select @p1, @p2, @p5, @p6, @p7

This is problematic the program is declaring a query whose results will be spooled on the server. The the program will call for one record at a time from the result set. The literal variables (p1,p2,p5,p6,p7) should be related to the configuration of the system stored procedure. The stringed variables (p1-p7) are represented by the last 7 terms of the procedure call.

    exec sp_cursorprepexec
        @p1 output, -- plan handle for statement after its prepared
        @p2 output, -- cursor, the the program can retrieve one row at a time from the result set
        N'@P1 int,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int',--parameters for execution
        N'<<query text params here reference the list above>>', --statement, parameters here are the ones form the string list above
        @p5 output, --
        @p6 output, -- configuration terms see the docs
        @p7 output, --
        -- sql server will compile the query with unknown values for @P1 - @P7
        -- these values will be substituted for @P1-@P7 after its compiled
        1, --@P1
        4,
        1,
        3,
        1,
        8,
        1 --@P7

docs for this http://msdn.microsoft.com/en-us/library/Ff848775%28v=sql.105%29.aspx


Handling External DB without PK How can I handle a table given to me from an outside DB that does not have a PK? Or even an ID field that marks the record specifically. I am using SQL Server.
I have a table that has come to me that has information on an event. I need to be able to pull the company information that I can. There is an event_id field that I can use to keep track of the rows, but there is no company_id.
Here are the rows I think are useful for this discussion:

event_id
company_name

I can do a grouping by company_name and get multiple counts, so I know the name is consistent. The end goal of this is to connect this to my larger DB that DOES have company_id columns.
The external DB is not willing to change their export at all.
Should I create a separate key table for the companies in this table and simply keep my own set of keys? Is that good practice? If so, how can I do that? If not, what should I consider doing?
Forgive me if this question has been answered before. I looked but could not find it. I'm not even sure how to word this question. I would love to be pointed in the right direction.

If the external company is not willing to correct their export, then you can set up your own lookup table.

CREATE TABLE dbo.Companies
(
  CompanyID int IDENTITY(1,1) PRIMARY KEY,
  CompanyName nvarchar(255)
);

Now, when you get a new import, I assume this goes into some temporary ETL table. So you do:

INSERT dbo.Companies(CompanyName)
  SELECT company_name
  FROM dbo.ETL_Table AS etl
  WHERE NOT EXISTS
  (
    SELECT 1
      FROM dbo.Companies
      WHERE CompanyName = etl.company_name
  )
  GROUP BY company_name;

Now you can join against your lookup table on the name, and that join can produce the IDs you need to use in your own referential integrity.

This does not handle the case when a company name changes, and I don't know how you can detect that.


Importing a large mysql dump while replication is running So we have a simple master/slave mysql replication running between two CentOS Servers.

The master has multiple databases. eg..
Database1
Database2
Database3

The issue is we have a mysql dumpfile of a new 60GB database (Database4).

What's the best way to import Database4 without breaking replication?

I was thinking we could stop replication, and import the mysqldump onto both master and slave. Then restart replication, but was hoping there was an alternate way that would minimize downtime.

I don't think there needs to be any downtime on the Master.  See how this feels.

Configure it so that you have Master-Master.  That is, turn on the binlog and a few flags on the Slave so that it also acts like a Master; plus do CHANGE MASTER on the Master so that it is also a slave.  (But no traffic will yet flow from the Slave to the Master.)
Load the new database on the Slave.  It will replicate to the Master (and clog up replication in that direction).

I think that is all.  You could turn off M-M and leave it just M-S, but I prefer keeping "M-M, but writing only to one M".

(Caveat:  I have not tried anything close to this.)


Migrating Oracle to MySQL: MySql Workbench Not Available I am currently trying to migrate information from a query of a remote Oracle database to a local MySQL database. I have poked around and have found it has been frequently suggested to use the Migration tool in MySQL Workbench. However, I am developing through ssh and only have control of the linux cloud environment I'm working in through the terminal.

Any suggestions would be greatly appreciated.

You can use ssh to securely forward the MySQL port remotely. For example, if you execute:

ssh -L 3307:localhost:3306 <ssh-user>@<remote-server>

You will be able to connect to the local port 3307 as if MySQL was running there. All connection information will be tunneled though ssh, so it will be sent securely. That way you can connect using Workbench using localhost:3307

I cannot say if Workbench would be the best tool for the migration, though. Here you have some pointers and tools: http://database-scalability.blogspot.com.es/2014/04/porting-from-oracle-to-mysql.html


Is this an appropriate forum for help with beginners dba questions? I've installed MAMP and Wordpress on a local machine to learn about databases.

Can anyone recommend a 'hello world' type tutorial, where I can create my own database within this environment?

I would like to make a database to track who I loan things to.. so I imagine starting with two tables:

users
    userId
    name
    phone
    email address

assets
    assetId
    make
    model
    serial
    borrowers_id

If there is a more appropriate forum for these, please let me know!  Thank you in advance, this is my first post on stackexchange.

From your tag I assume that you are into mySQL. Do you like video tutorials?

There are plenty of very good ones on YouTube:

Playlist with simple mySQL tutorials:

https://www.youtube.com/watch?v=g883j_5JsUc&list=PLvVojG9m055L_khQ4BhO5yYbrcEB23jOQ

Another one:

https://www.youtube.com/watch?v=mdGKgasqKCs&list=PLyTQS5V4iATQTdd8DqI5-NQ3HYUOMYtZE

My reputation doesn't allow me to post more links, but as soon as it goes up I'll add many more.


Data Sync between servers with different schema in SQL Server I have two servers 2008 R2 and 2005. The e-commerce site has been running with 2005 Server. I have migrated 2005 server data with modified data schema to 2008 R2 from yesterday's date of data. My client wants to migrate the complete DB to 2005 R2 whenever he will gets the new site from developers but I need to update changes from 2005 to 2008 R2 with modified schema everyday.

How can I sync the data having different data schemas of different servers?

It is not quite clear what you are asking. It seems you are looking for a solution that would replicate data changes as well as schema changes from the 2005 server to the 2008R2 server.

This can be achieved with SQL Server Replication. Transactional Replication is able to move all data changes as well as all schema changes from a single source system to one or more target systems. The systems do not need to be at the same version of sql server for this to work (http://technet.microsoft.com/en-us/library/ms143241(v=sql.105).aspx)

For an introduction to transactional replication check out my Replication Stairways article series.

If this does not answer your question, please try to clarify what you are looking for.


AlwaysEncrypted error when inserting via stored proc lets start with the error first:

Encryption scheme mismatch for columns/variables . The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '3' expects it to be (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_P_H', column_encryption_key_database_name = 'Payments_Hub') (or weaker)

I'm trying to insert into a column with AlwaysEncrypt encryption on using a stored procedure:

The column 'PARAMETER_VALUE' is the encrypted column

CREATE PROCEDURE dbo.SP_UI_Parameter
(
@ID int OUTPUT,
@OPERATION_ID int,
@PARAMETER_NAME varchar(100),
@PARAMETER_METRIC varchar(100),
@PARAMETER_VALUE varchar(100)
)
AS
BEGIN

IF @ID IS NULL
BEGIN

INSERT INTO [dbo].[TOKEN_PARAMETER] (OPERATION_ID,
PARAMETER_NAME, PARAMETER_METRIC, PARAMETER_VALUE)

SELECT @OPERATION_ID, @PARAMETER_NAME, @PARAMETER_NAME, @PARAMETER_VALUE

SET @ID = SCOPE_IDENTITY()

END
ELSE
BEGIN

UPDATE [dbo].[TOKEN_PARAMETER] SET
OPERATION_ID = @OPERATION_ID,
PARAMETER_NAME = @PARAMETER_NAME,
PARAMETER_METRIC = @PARAMETER_METRIC,
PARAMETER_VALUE = @PARAMETER_VALUE

END

END
GO

Why am I getting the error for the query ?

Is there something I must include within the STORED PROCEDURE ?

I have included "Column Encryption Setting=Enabled " in my connection settings
I have also enabled Parameteterization for AlwaysEncrypted
The key is stored on the local machine
I have created DETERMINISTIC encryption
SQL Server 2016 database Engine using SSMS 17 - can this be an issue ?

SQL Server Version Info: (EDITED)

Microsoft SQL Server 2016 (SP1-CU7-GDR) (KB4057119) - 13.0.4466.4 (X64)   Dec 22 2017 11:25:00   Copyright (c) Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0  (Build 14393: ) (Hypervisor)

SSMS Stored Procedure Call: (EDITED)

DECLARE @return_value int,
@ID int

EXEC    @return_value = [dbo].[SP_Insert_Parameter2]
 @ID = @ID OUTPUT,
 @OPERATION_ID = 3,
 @PARAMETER_NAME = N'algorithm',
 @PARAMETER_METRIC = N'algorithm',
 @PARAMETER_VALUE = N'99999'

 SELECT @ID as N'@ID'

 SELECT 'Return Value' = @return_value

 GO

Thanks In Advance

So I figured out what I was doing wrong
AlwaysEncrypted requires the variables to be defined in line:

DECLARE @OPERATION_ID int = 4
DECLARE @PARAMETER_NAME varchar(100) = 'algorithm'
DECLARE @PARAMETER_METRIC varchar(100) = 'algorithm'
DECLARE @PARAMETER_VALUE varchar(100) = 'TestValue3'

INSERT INTO [dbo].[TOKEN_PARAMETER] (OPERATION_ID, PARAMETER_NAME,
PARAMETER_METRIC, PARAMETER_VALUE)
SELECT @OPERATION_ID,
       @PARAMETER_NAME,
       @PARAMETER_METRIC,
       @PARAMETER_VALUE

This allows for successful insert into the table.
When using a stored procedure you cannot use the same method to insert.

So in essence, there is nothing wrong with how to stored procedure works.
The application needs to change the way it populates the columns, using the same stored procedure

Please see below link for reference:

https://social.technet.microsoft.com/wiki/contents/articles/37979.working-with-the-always-encrypted-feature-in-sql-server-2016.aspx#Add_Data_to_Always_Encrypted_Column

Still waiting on applications team to come back with results but i'll mark the question as solved if this fixed the issue


SQL TDE and Certificate Handling Recommendations I've seen the information on this post: But I don't think it answers this particular question/concern:

I have multiple SQL servers with multiple databases. We want to use TDE on a few of them.

Should I use one certificate for all TDE use, or one certificate for each database? Or maybe another way?
What would be the reasons for choosing one over the other?

If using one cert for all databases, if the cert is 'compromised' then all databases that use it could be decrypted by an attacker? And if we use one certificate per database, would that gives us a level of isolation?

I just have in the back of my mind, that if using one cert for all databases, that if the cert is 'compromised' then all databases that use it could be decrypted by an attacker?

I assume that (compromised means), when "someone can steal database files .mdf and .ldf or backups and TDE Certificate along with it's Private key and could know the Password that been used to encrypt Certificate backup".  Do you think that can happen?

  My question is, should I use one certificate for all TDE use, or one certificate for each database?

I don't think that having multiple certificate per each database can prevent it (above mentioned scenario). Because, there is possibility that someone who can steal one certificate can also steal multiple certificates.

  Answer (in my opinion)

However, in my opinion, database encryption alone cannot be full solution on security, you could also consider following:

Infra level: Building strong firewall around the database servers
OS level: Avoiding multiple users as builtin\administrators in database servers etc..

Also, the physical security (which is out of scope technical scope)

I believe, if you could implement TDE as per Standard TDE Architecture, would be fine and focus on surrounding security hiccups that may arise (as mentioned above).

...

Edit Post:

How strong your password (Source)?

To check the strength of your passwords and know whether they're inside the popular rainbow tables, you can convert your passwords to MD5 hashes on a MD5 hash generator, then decrypt your passwords by submitting these hashes to an online MD5 decryption service.

For instance, your password is 0123456789A, using the brute-force method, it may take a computer almost one year to crack your password, but if you decrypt it by submitting its MD5 hash C8E7279CD035B23BB9C0F1F954DFF5B3 to a MD5 decryption website, how long will it take to crack it? You can perform the test yourself.


What does @RC mean in SSDT SQL Server Unit Testing? In Sql Server Unit Testing, SSDT, what does @RC stand for? Does it mean Row Count, or Row Column? Team wants to understand, we have been using it for long time, good tool which works, just curious.

@RC = Return code. The boilerplate unit test code will assign the stored procedure return code value to this variable, where it can later be used in a test case assertion.

Assuming there's a subsequent SELECT @RC after the proc execution in the unit test code, the scalarValueCondition1 assertion in your screen image will verify the return code value is 1 in order for the test to succeed.


How to find out the oldest full backup still existing on disk? one of the things I like to find out is what is the oldest full backup file still living in my servers.

it is the custom to delete or move those files to a different place after a number of days. How far can we go back in time, with what we have on the server?

In this test I am checking the backups for a database called JunoReporting

first I create a function to test the physical existence of the file

USE MASTER
GO

create FUNCTION dbo.fn_FileExists(@path varchar(8000))
RETURNS BIT
AS
BEGIN
     DECLARE @result INT
     EXEC master.dbo.xp_fileexist @path, @result OUTPUT
     RETURN cast(@result as bit)
END;
GO

then I use it to
get the latest Full backup still on Disk

--=============================================
-- get the latest Full backup still on Disk
--=============================================

select
  a.backup_set_id AS [LATEST Full backup still on Disk]
  ,a.media_set_id
  ,a.backup_start_date
  ,a.backup_finish_date
  ,a.type
  ,backup_size=a.backup_size/1024.00
  ,a.is_copy_only
  ,compressed_backup_size= a.compressed_backup_size
  --,b.media_set_id
  ,b.physical_device_name
  ,[File Exists]= CASE WHEN master.dbo.fn_FileExists(b.physical_device_name) = 1 THEN 'Yes' ELSE 'No' END
  FROM msdb.dbo.backupset a
  INNER JOIN msdb.dbo.backupmediafamily b
         ON a.media_set_id = b.media_set_id

  WHERE 1=1
  and a.backup_set_id = (
                            SELECT MAX(f.backup_set_id)
                                FROM msdb.dbo.backupset f
                          INNER JOIN msdb.dbo.backupmediafamily b
                                  ON f.media_set_id = b.media_set_id
                               WHERE 1=1
                                 AND f.[type] = 'D'
                                 AND f.DATABASE_NAME='Junoreporting'
                                 AND master.dbo.fn_FileExists(b.physical_device_name) = 1
)

and also to
get the OLDEST Full backup still on Disk

--=============================================
-- get the OLDEST Full backup still on Disk
--=============================================

select
  a.backup_set_id AS [OLDEST Full backup still on Disk]
  ,a.media_set_id
  ,a.backup_start_date
  ,a.backup_finish_date
  ,a.type
  ,backup_size=a.backup_size/1024.00
  ,a.is_copy_only
  ,compressed_backup_size= a.compressed_backup_size
  --,b.media_set_id
  ,b.physical_device_name
  ,[File Exists]= CASE WHEN master.dbo.fn_FileExists(b.physical_device_name) = 1 THEN 'Yes' ELSE 'No' END
  FROM msdb.dbo.backupset a
  INNER JOIN msdb.dbo.backupmediafamily b
         ON a.media_set_id = b.media_set_id

  WHERE 1=1
  and a.backup_set_id = (
                            SELECT MIN(f.backup_set_id)
                                FROM msdb.dbo.backupset f
                          INNER JOIN msdb.dbo.backupmediafamily b
                                  ON f.media_set_id = b.media_set_id
                               WHERE 1=1
                                 AND f.[type] = 'D'
                                 AND f.DATABASE_NAME='Junoreporting'
                                 AND master.dbo.fn_FileExists(b.physical_device_name) = 1
)

and also
Get all the backups after the latest backup

--=============================================
-- Get all the backups after the latest backup
--=============================================

select
  a.backup_set_id
  ,a.media_set_id
  ,a.backup_start_date
  ,a.backup_finish_date
  ,a.type
  ,backup_size=a.backup_size/1024.00
  ,a.is_copy_only
  ,compressed_backup_size= a.compressed_backup_size
  --,b.media_set_id
  ,b.physical_device_name
  ,[File Exists]= CASE WHEN master.dbo.fn_FileExists(b.physical_device_name) = 1 THEN 'Yes' ELSE 'No' END
  FROM msdb.dbo.backupset a
  INNER JOIN msdb.dbo.backupmediafamily b
         ON a.media_set_id = b.media_set_id

  WHERE 1=1
  and a.backup_set_id >= (  -- the last full backup

                            SELECT MAX(f.backup_set_id)
                              FROM msdb.dbo.backupset f
                             WHERE 1=1
                               AND f.[type] = 'D'
                               AND f.DATABASE_NAME='Junoreporting'
                               AND master.dbo.fn_FileExists(b.physical_device_name) = 1

                        )

  --and a.[type] = 'L'
  AND a.DATABASE_NAME='Junoreporting'
  AND b.[Device_Type] = 2
  AND a.is_copy_only = 0
order by backup_set_id desc


MySQL - how to store "other"? How to store "other" text?

In summary, I have too many of these kinds of fields (like hundreds?):

http://i.stack.imgur.com/0H81O.png

I don't know an appropriate way to handle this.

If I store only text of these options, then I will have data redundancy and no foreign key. Maybe foreign key does not matter since I am allowing users to store "other" anyway.

If it was a radio list where only one option could be specified then I'd use one column and store the selected value if something other than "other" were picked and the value from the free-text box otherwise.

As it appears that your application allows selecting multiple item (which may or may not include "other") then you need to store a list of possible options per form. A boolean per check box may be inefficient, but do avoid a comma separated list (i.e. a field called "foods" in which you store "rice,soup" in the example you give) as that can make reporting (amongst other things) difficult.

How I would go about storing this data would depend on the amount on the rest of the data set: there are a couple of methods that are optimal for different circumstances. Could you update the question to give more details about the application and its data?


Database-agnostic stored procedure call passing array of name/value pairs Background

Looking to pass a set of name/value pairs into a stored procedure in a database-agnostic way, using JDBC. A database structure is defined as follows:

CREATE TYPE array_parameters AS (
  v_name VARCHAR2(255),
  v_value CLOB
);

This structure, which can have equivalent definitions in most modern relational databases, is being proposed as a way to pass an arbitrary number of name/value pairs into a stored procedure. The stored procedure call resembles:

SELECT rxm( '...map...', array_parameters );

Where the ...map... can include any number of variable references, taking the following form:

account.id = $id &&
person.last_name = $surname && ...

The array_parameters, in theory, could be populated as:

array_parameters[0].v_name = "$id";
array_parameters[0].v_value = "123456789";
array_parameters[1].v_name = "$surname";
array_parameters[1].v_value = "O'Malley, The \"Great\"";

Problem

JDBC4 defines a method called createArrayOf, which is the New South China Mall of APIs:

Unsupported by Oracle
Unsupported by MySQL
Unsupported by Microsoft SQL Server
Unsupported by Apache Derby
Unsupported by Sybase

Without the ability to create the name/value pair array, I can see no obvious way to pass in the values without resorting to database-specific implementations (such as using Oracle's ARRAY, or obtuse contortions to support MySQL).

Question

How would you define and then call a stored procedure that can take an arbitrary number of name/value pairs in a database-agnostic fashion?

Idea #1

One idea would be to define two string arrays, rather than an object array structure, and call the stored procedure as follows:

SELECT rxm( '...map...', array_names, array_values );

The two arrays would be index-linked, but this likely depends on createArrayOf(), as well.

Idea #2

It might be possible to pass the pairings as comma-separated strings. However, the values could contain commas themselves, which makes parameter encoding using comma-separated strings tricky. (Generally speaking, any separator can appear as a character somewhere in the values, which includes escaped separators as well, such as \,.)

This seems like the most database-agnostic solution, but implementing a CSV decoding routine across multiple databases in PL/SQL is neither trivial nor efficient.

Idea #3

Use Hibernate as an abstraction layer, then implement a JPQL routine that passes in the array of name/value pairs. For example, calling query.setParameterList, which might only work for IN clauses, rather than stored procedure parameters.

Pass the values as XML (in a VARCHAR). The following database all support shredding XML from the query language:

Postgres
MySQL
SQL Server
Oracle
DB2 UDM

Obviously, not every obscure database on the planet supports it. But the ones above are realistically the only ones you need to care about to hit 99.9% of the database market.


Sqlserver: Get Meta-Info about TRN file Is there any way to get the timespan of the data contained in a TRN-File?

What I have:

A backup of a transaction-log

What I need:

The timestamp of the first and the last entry inside the log

How do I get this information?

Short answer: you can't. Not with the file only, at least.

Long answer: what you can do is query msdb.dbo.backupset on the source instance to extract this information. The backup start/end date of the previous T-LOG backup is approximately the beginning timestamp of the log records conteined in each .TRN file. The end of the log chain is the backup begin/end date of that .TRN file.


What could cause waiting for table metadata lock? I am using a MySQL 5.6 DB with MyISAM tables under FreeBSD 8.3. The main part of code is in PHP, but the problem arises, when I launch the self-written on C++ chat-server. I use the mysql.h for DB connections and libwebsockets for both HTTP and websockets interaction. The previous version was written in C, no locks happened.
The chat server has a websocket regime and the refresh regime. It also uses the database for user authentication and loading/saving of settings.

Now when I run this server, after a certain period, the database gets flooded with connections with different queries, all with the status "Waiting for table metadata lock". When I kill the chat, the queries start being processed.

I am 100% sure, there are no ALTER TABLE, CREATE TABLE or similar queries being executed at any time, so they are not the reason.

So I don't understand what in this situation may be the reason for database to hang, and how can I trace and solve it. I would appreciate any help or ideas! Thanks ahead!

Thanks to jynus for advising on working with general log, the problem has been solved!

As in most of the cases, the error was quite simple, here is the description:

On certain occasions, a function was invoked, that performed the following:

Lock several tables in the DB
Do its queries
Send a message to chat with the results
Unlock tables

The message to the chat was sent via a socket with connection timeout of 10 seconds. Sometimes, after step 1, at step 3, the chat was busy logging on the new user, and was trying to access the tables, that are locked. So the loop was closed - the chat was waiting for the unlock and the function was waiting for processing its connection request.

10 seconds was enough for the other requests to reach the max_connections limit and lock the database.

The only thing that puzzles is me, is why the state of all queries was "Waiting for table metadata lock".

After switching the places of steps 3 and 4, the problem was no more.

Thanks for help!


Add Row into SQL Server Log How can I programmatically insert a row into either the SQL Error or SQL Server logs on a SQL Server?

I wrote a Dot Net application to do a data import job and would like to write log entries into the SQL Server's log on a failure.

In SSMS, the log can be found Management-SQL Server Log.

I can pull the contents of the error and SQL Server log by the stored procedure xp_readerrorlog.

You can write to the SQL Server error logs via the t-sql RAISERROR command.

See this Technet article.


Unable to add replica set to shard I was trying to add a new replica set to my mongodb sharded cluster but I keep getting the following error:

{
    "ok" : 0,
    "errmsg" : "Could not verify that config servers were active and reachable before write",
    "code" : 25
}

I checked all the config servers and they are up and running.The problem is new as I can previously add shards without any issues.

The MongoDB version I am using is 3.2 running on Ubuntu 16.02.

How can I fix this?

Managed to find the error source.

First, check the log file (eg. [Path to log)/mongod.log)

Turns out that the authentication has failed on one of the config servers. This is fixed by including " --auth --keyFile [Path to Key]" when starting up the config server mongod.


Dynamic quorum and Dynamic witness in Always On AG 2016 I have a Always On AG 2016, configured on Windows Server 2012 R2 – 2 node cluster. Quorum configured as 2 nodes + Fileshare witness, totally 3 votes.

Scenario 1:
Now, either 1 node fails – remaining vote = 66.66 (majority) and cluster is up.

Scenario 2:
Now, fileshare witness fails – remaining = 66.66 (majority) and cluster is up

Kindly help me understand the dynamic quorum and dynamic witness on scenario 2.

My doubts, post scenario 2:

Will it automatically take away 1 vote from either one of the nodes? If yes, remaining vote will be 33% not majority and single point of failure.

What will be the role of dynamic witness in this scenario?

Thanks in advance!.

Will it automatically take away 1 vote from either one of the nodes? If yes, remaining vote will be 33% not majority and single point of failure.

Yes correct. Dynamic witness works in such a way that it tires to keep total number of votes odd. In your case when the FS witness is down the Dynamic quorum will toggle one vote from one of the node such that its vote becomes zero and total votes now voting would be one.

The dynamic quorum and witness are very much interrelated. In above case the process of adapting quorum after FS witness failed is actually dynamic quorum. It adjusted itself after the failure.

I would strongly suggest you to read Understanding cluster and pool quorum


Migrate DB2 UDB (Iseries) to SQL Server I'm looking for a tools that allows migration of DB2/UDB databases to SQL Server.
It's quite easy migrate schema and data using  linked server or import export with SISS.

But what about stored procedures? We have a lot complex stored procedures.
There are some tools like the ispirer one -- which is too expensive and not so mature --
other one the swissql one is no more maintained.

What about other tools? Anyone have experience about? My focus is on stored procedure migration.

This is not an easy task to translate from one flavor of SQL to another. That's either (kind of) expensive or difficult to do for yourself.

No database language flavor is really easy to digest and translate into another, because all have lots of system procedures, data types, functions and others that are really system specific.

Each SQL flavor implements a subset of the SQL 92, 99 or 2003 Standard, and then goes further with local features.


How to enable CLR on a remote SQL Server? I have been created a dll for SQL Server 2014 to add some custom functions for my stored procedures for my asp.net websites.

I don't have any problem in the local server but can't enable the CLR features on a remote host database; trying to do so throws an error that User does not have permission to perform this action.

So I call host administrative and they told me I can use only virtual servers or personal server to enable this feature. But virtual servers monthly charges and personal servers are very expensive.

So I want to know to are there anyways exist to add dll to the remote host and using them somehow?

SQLCLR, or technically the "CLR enabled" server configuration option, is a Server-level option, not Database-level. Since it sounds like you are using shared hosting with no server-level permissions, then the answer is just what the sysadmins told you: No.

Now, if you can convince them to enable it for everyone on the server, then you would be able to do this.

Or, you could possibly consider using SQL Server on Amazon RDS which allows for SQLCLR (SAFE Assemblies only -- I have successfully installed and used SQL# on Amazon RDS) and should have options that are not cost-prohibitive (though I don't know your specific requirements, but worth taking a quick look as they do have a "free" tier).


SQL Server 2012: Give rights to read ddl but not modify? What rights would I need to be give a user for them to be able to view all DDL code (such as stored procedure code) but not be able to edit any of it?

The only possibility seems to be DDLadmin, but that would allow a user to edit as well.  Is there some other options, or perhaps a way to grant and then revoke part of DDLAdmin?

You can give VIEW DEFINITION at the object, schema or database level.

GRANT VIEW DEFINITION ON dbo.objectname TO [user/role];

GRANT VIEW DEFINITION ON SCHEMA::schemaname TO [user/role];

GRANT VIEW DEFINITION ON DATABASE::databasename TO [user/role];

http://msdn.microsoft.com/en-us/library/ms173848.aspx


Oracle to SQL Server migration We have a very big Oracle db and want to archive whole of the oracle db to SQL Server. We are trying to weigh between SSIS v/s SSMA. Can the complete migration be achieved using SSMA? We do not have any schema/tables setup in sql. We need to migrate whole of the oracle db to sql.

Is there any cons using SSMA in this kind of migration?

Conceptually, yes, SSMA is well equipped to handle large Oracle DB migrations.  However, every environment is different and depending on what idiosyncrasies your Oracle DB holds, this may or may not be a simple process.  Don't expect to see a success screen after a few clicks with any approach.

First, I suggest you give a quick read of Michelle Li's answer to a similar question here.  She outlines some key items you need to be aware of before you start.  I will also draw your attention to the Migration Engine option.  You'll want to ensure this is set to Server Side Data Migration Engine otherwise your data will go through whatever client SSMA is being executed on, and this is just a waste of bandwidth.

Most importantly, plan for failure.  It will likely take a few iterations to work out all the bugs, and you may even have to roll in some custom processes to accommodate things SSMA just can't handle, such as External C libraries/functions, SQL Loader Tasks, complex PL/SQL statements, any external scripts being called via cron, etc.  I think SSMA will take a stab at conversion of many of these things, but that doesn't mean it will work 100%.  Testing is going to be critical, so plan accordingly.  I will also suggest you try migrating a copy of your Prod environment (e.g. Dev or some other pre-prod area) first to work out the bugs.  This will ensure you're not killing your production Oracle environment while you're fine tuning things.

In closing, the complexity of your end solution will likely reflect the complexity of the Oracle database.  If it's just data, this could go swimmingly well, but if you've got a lot of logic tied into PL/SQL routines, etc. this can be time consuming.  Use SSMA if possible as it'll hopefully cover 80% or more of the work, and then manage the remaining components how best you see fit, be it SSIS or otherwise.


What is the best database design for multi-language data? I am designing a multi-language database for a piece of software with the following features:

data used in every country differs from other countries
data used in every country will be in 2 languages: English, and the native language
there will be a central place to control updating and deleting all data in all countries

I have 2 choices for these multi-language databases:

Tables of each entity for each country will have a column for English and native language

I think that way makes it easy and fast to update every country's data, and I can separate its own tables only to be used with software there.
But that means if I have 15 countries, I have to repeat tables 15 times.

A table for all languages as a reference, linked to a table for each entity that contains data for all countries in all languages

It will make my database simpler, with a smaller number of tables, and simpler for retrieving data.
But it will be dangerous to have all data for one entity for all countries in one table. So any attack or mistake can destroy my work for all these countries.
Can I arrange data in the same table to make every row of the same languages together?
Can I isolate data required for every country from central tables to be used with software ONLY there?

The primary disadvantage of your first option is that you must create/maintain a similar table structure for n different languages and your application must switch between them.  When a new language is added you will have to add table and modify your application.
Your second option is preferable of the two.  I will address some of your concerns.

If your security relies on the separation of data into different tables, then your security needs work.  Sure, separating them would provide some degree of additional security, but if you invest the time you save by not maintaining multiple tables into verifying/increasing your other security measures you will have far greater benefit.
When you query the data you can sort by language.
A WHERE clause on the country would retrieve data only for a particular country.

I recommend you store the primary details including the English names in simpler tables and then each table that has other language equivalents can have a language specific table containing every other language.  Something like this:

Languages
   ID
   Name

Drugs
   ID
   Price
   Name
   Interactions
   Mechanism
   Uses

DrugLanguages
   DrugID
   LanguageID
   Name
   Interactions
   Mechanism
   Uses

Companies
   ID
   Name
   Address
   Activity

CompanyLanguages
   CompanyID
   LanguageID
   Name
   Address
   Activity

You should know that I have never done multilingual design, so my approach may have limitations that I do not fathom.


Hostgator php code error while creating a new database table Error is
Error creating database: Access denied for user ''@'localhost' to database 'my_db'

Code is

{

<?php

$link=mysql_connect ("localhost", "cpuser_dbuser", "password") or die
 ('I cannot connect to the database because: ' . mysql_error());
 mysql_select_db ("cpuser_db");

$sql = 'CREATE DATABASE my_db'; if (mysql_query($sql, $link)) {
    echo "Database my_db created successfully\n"; } else {
    echo 'Error creating database: ' . mysql_error() . "\n"; } ?> }

I have replaced all the credentials here.

Please help me solving this error.

This is not a PHP error, but a MySQL error. Basically, as the error message says you're either not using the correct username, password or the user privileges haven't been flushed since you added the user. That is, I'm assuming that you've removed the username from the error message, and not trying to log in with an empty parameter.

You need to check that the selected user has access to the database in question, and that the privileges has been flushed. Using the MySQL CLI client you can do this by the following commands:

USE mysql;
SELECT host,db,user,password FROM db;
SELECT host,user,password FROM user;
FLUSH PRIVILEGES;

The latter should only be used after you've added, deleted or changed the permissions of any MySQL users in the above tables. If you don't have access, look at the MySQL manual for help on GRANT. Alternatively, search the web.


How do I pass a variable into -job on Copy-DbaAgentJob? I'm trying to query one the source instance and copy across any missing jobs into the destination instance. Everything seems to work except passing the name into -job. Can anyone suggest a fix or point me in the right direction?

$dest = "Dest\Instance"
$Source = "Source\Instance"
$MissingJob = Invoke-DbaQuery -SqlInstance $Source -ReadOnly -Query "
use msdb

select name
from dbo.sysjobs
where name COLLATE Latin1_General_CI_AS not in (select name
from [Dest\Instance].[msdb].[dbo].[sysjobs]
)"

foreach ($Name in $MissingJob){
Copy-DbaAgentJob -Source NCCSQLDEVW2K162\NCCSQL2017DBA01 -Destination $dest
-Job $Name -SourceSqlCredential $cred -Force
}

No need to reinvent the wheel with the linked server query. dbatools also provides the Get-DbaAgentJob function to enumerate jobs on both agents. As well, the $Job parameter accepts an array as input, meaning you don't need the for-loop. Assuming the job name is the keying attribute as in your sample, you could try something like the following.

$sourceJobs = Get-DbaAgentJob -SqlInstance '[Source\Instance]'
$destJobs   = Get-DbaAgentJob -SqlInstance '[Dest\Instance]'

$missingJobs = $sourceJobs | Where {
    $_.Name -notin $destJobs.Name
}

Copy-DbaAgentJob `
    -Source '[Source\Instance]' `
    -Destination '[Dest\Instance]' `
    -Job $missingJobs

Although strictly speaking, Copy-DbaAgentJob already checks for duplicates, so you could slim it down even further by just omitting the -Job parameter entirely and just run something like...

Copy-DbaAgentJob `
    -Source '[Source\Instance]' `
    -Destination '[Dest\Instance]' `

...which will show you which jobs were skipped because they already exist.


Master-Master Replication in MySQL Good day everyone!

I want to clarify something about the behavior of master-master replication in MySQL, with respect to the logs i.e. bin-log and relay-log, it use in performing replication.

I assume that in this kind of setup, only one of the 2 db servers is processing DML operations i.e. 'active' server. Read requests are distributed to both of the servers.

So when db1 is the active server, DML operations to it are recorded in its bin-log, and db2 would have to fetch it in, placed it in its relay-log and also process it as a part of the replication process.

Question 1: Does the DML operations committed by db2 during the replication process gets included in its own bin-log?

Question 2: Would the resulting bin-log in db2 be exactly the same with the bin-log of db1, to the letter?

Question 3 What happens to the entries in db2 relay-log once they are committed to the database during the replication process, are they discarded? What role does the relay-log info log has in this?

The reason I am asking this because I could not get the whole scenario yet when db1 goes down and db2 becomes the active db server.

Question 4: How does db1 know where in the bin-log of db2 (somehow dependent on the answer of Question 2), it will start the replication process?

UPDATE 1:

Question 4.1: If you enable log-slave-updates on both databases i.e. dB1 & dB2, then that would mean all items from the binary log of dB1, which was successfully replicated by dB2 will be written into dB2's binary log and vice-versa. Would not this result to some sort of infinite circular replication or duplications of entries on both databases, if it's possible at all, considering the possible key-collision issues that would arise? What I'm trying to say is, How would dB1 know once it checks on the binary log of dB2 that, "I should not replicate those entries in there because they all just came from me"?

Question 5: On INSERT queries on the master, what form of the query is written into the binary log? Is it the 'raw' form of the query, or the one which already has the auto-generated value of the auto-increment key?

UPDATE 2:

Question 5.1: So you're saying that on raw INSERT queries, the actual query itself is the one written on the bin-log. So in essence, when such queries are replicated and committed on the slaves, it is the case that the same tuple would be associated with or will have, different values on its auto_incremented field, across the different databases i.e. slaves?

Example:
Insert this tuple ('Uncle Sam', 'Male', 26), on a table with auto-increment field say, pki

Then, it follows therefore from your answer that pki may have different values for this tuple across the different databases i.e. master and slaves.

Question 5.2:Assuming above is right, would not this be an issue when load-balancing read queries across databases?

Thank you very much!

Question 1

Does the DML operations committed by db2 during the replication process gets included in its own bin-log?

Answer to Question 1

Yes it will, provided you have this in /etc/my.cnf on both db1 and db2

[mysqld]
log-slave-updates

If you do not have this, add it and restart mysql

Question 2

Would the resulting bin-log in db2 be exactly the same with the bin-log of db1, to the letter?

Answer to Question 2

Yes. Make sure the clocks on both DB servers are synchronized

Question 3

What happens to the entries in db2 relay-log once they are committed to the database during the replication process, are they discarded? What role does the relay-log info log has in this?

Answer to Question 3

In MySQL Replication, the IO Thread of a Slave will read its Master's bin-log entries and store them in a FIFO queue. For each relay log in a slave, when every entry in the currently processed relay is executed it is rotated out and discarded. If relay logs are piling up, this quickly indicates that the SQL thread died because of any SQL error. Just do SHOW SLAVE STATUS\G to find out what stopped the SQL thread. The IO Thread would conitnue collecting completed SQL statements from its Master.

Question 4

How does db1 know where in the bin-log of db2 (somehow dependent on the answer of Question 2), it will start the replication process?

Answer to Question 4

When you do SHOW SLAVE STATUS\G, look for the following lines:

Master_Log_File : The latest binary log whose most recently command was copied to the Slave
Read_Master_Log_Pos : The latest position of the latest binary log whose most recently command was copied to the Slave
Relay_Master_Log_File : The latest binary log whose most recently command was executed on the Slave
Exec_Master_Log_Pos : The latest binary log whose most recently command was executed on the Slave
Relay_Log_Space : The sum total (in bytes) of all relay logs. By default, each relay log is the default size of a binary log (1G). If Relay_Log_Space starts to significant exceed 1G, this indicates one of two things:

SQL thread died due to SQL Error
SQL thread is busy with a long-running query

Question 4.1

If you enable log-slave-updates on both databases i.e. dB1 & dB2, then that would mean all items from the binary log of dB1, which was successfully replicated by dB2 will be written into dB2's binary log and vice-versa. Would not this result to some sort of infinite circular replication or duplications of entries on both databases, if it's possible at all, considering the possible key-collision issues that would arise? What I'm trying to say is, How would dB1 know once it checks on the binary log of dB2 that, "I should not replicate those entries in there because they all just came from me"?

Answer to Question 4.1

You must have log-slave-updates available on both DB servers in order to have an audit trail that the SQL executed on on DB server made it to the other. If you don't, you would have to do your due diligence to compare the data explicitly. Such ways would include:

Running CHECKSUM TABLE on every table you have in both DB servers to compare their contents.
Using pt-table-checksum, which is an automated version of running CHECKSUM TABLE between Master and one or more Slaves

You need not worry about infinite circular replication unless you are dealing with more that two masters. There have been rare times when someone with, let's say four Masters, removes one of the four servers from circular rep cluster. Let's suppose the the server_id is 13. It is remotely, but still, possible for binary log entries whose server_id belongs to the server that removed to be inside the relay logs on other servers. Only in such a scenario would you worry about infinite circular replication.

To circumvent such situations, MySQL 5.5 has a new option for the CHANGE MASTER TO command called IGNORE_SERVER_IDS. You would do the following to repair things on all the remaining servers:

STOP SLAVE;
CHANGE MASTER TO IGNORE_SERVER_IDS = (13);
START SLAVE;

In fact, here is what the MySQL Documentation says on this:

  IGNORE_SERVER_IDS was added in MySQL 5.5. This option takes a comma-separated list of 0 or more server IDs. Events originating from
  the corresponding servers are ignored, with the exception of log
  rotation and deletion events, which are still recorded in the relay
  log.

  In circular replication, the originating server normally acts as the terminator of its own events, so that they are not applied more
  than once. Thus, this option is useful in circular replication when
  one of the servers in the circle is removed. Suppose that you have a
  circular replication setup with 4 servers, having server IDs 1, 2, 3,
  and 4, and server 3 fails. When bridging the gap by starting
  replication from server 2 to server 4, you can include
  IGNORE_SERVER_IDS = (3) in the CHANGE MASTER TO statement that you
  issue on server 4 to tell it to use server 2 as its master instead of
  server 3. Doing so causes it to ignore and not to propagate any
  statements that originated with the server that is no longer in use.

Question 5

On INSERT queries on the master, what form of the query is written into the binary log? Is it the 'raw' form of the query, or the one which already has the auto-generated value of the auto-increment key?

Answer to Question 5

Whichever form is presented. Here is what I mean: The raw form would usually not include the auto_increment column expressed explicitly. On the other hand, it you import a mysqldump into a DB server with binary logging, the rows being inserted would explicitly be given. Either version of INSERT would be allowed execution in mysqld. In like fashion, either version of INSERT would be recorded AS IS...


Combining GROUP BY and JOIN This seems like a really simple request, but I can't find the answer. I'll admit that I only have a very limited understanding of JOINs, so it could just be that I just don't fully understand the concept. I'm using a MS Access .mdb database, and running the query in MS Access 2010.

I need to SUM 'BalanceDue' and GROUP BY 'CustomerID', then I need to replace the 'CustomerID' with the 'Company' name from the Customers table.

Orders table:

OrderNumber CustomerID  BalanceDue
1000        1           250.00
1001        2           100.00
1002        2           50.00
1003        3           100.00
1004        1           200.00

Customers table:

CustomerID  Company
1           ABC Inc
2           XYZ Inc
3           Widgets LLC

And the expected result:

Company     Total
ABC Inc     450.00
XYZ Inc     150.00
Widgets LLC 100.00

Here's the query I came up with, but the results don't appear to be accurate:

SELECT Customers.Company, Sum(Orders.BalanceDue) AS Total
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID
GROUP BY Customers.Company;

To check the accuracy, I just ran the following "simple" query (one I fully understand/trust):

SELECT Orders.CustomerID, Sum(Orders.BalanceDue) AS Total
FROM Orders
GROUP BY Orders.CustomerID;

When I compare the results from the two queries above, the results don't match. The first query has less rows, so it must be "skipping" some customers?

What am I doing wrong?

It also depends on what the end result needs to be.

If you want "Balance due per Customer", then you have the right query.

If you want "How much money do we still have to collect overall", then you need to also grab the money where there are no CustomerID, or where the CustomerID is not in the Company table. (Theses can be over-the-counter customers in a small store, for example).

So, try this: add a new row in Orders

INSERT INTO Orders (OrderNumber, CustomerID, BalanceDue) VALUES (1005, 5,999)

Now change your "INNER JOIN" for a "LEFT JOIN". This will get you all the values of the left table, even if there is no corresponding record in the right table.

SELECT Customers.Company, Sum(Orders.BalanceDue) AS Total
FROM Orders
LEFT JOIN Customers
ON Orders.CustomerID = Customers.CustomerID
GROUP BY Customers.Company;

You will see that you have 999$ assigned to the Customer "NULL". But at least you have all the money listed.

As with alot of things SQL and Reporting: it's all a matter of knowing exactly what you are looking for. Keep exploring, and keep asking questions.


How can a Cassandra node see another node as down? I'm running Cassandra on three nodes. Here's their nodetool status output:

ubuntu@ip-10-0-8-8:~$ nodetool status
Datacenter: us-east
===================
Status=Up/Down
|/ State=Normal/Leaving/Joining/Moving
--  Address    Load       Tokens  Owns    Host ID                               Rack
UN  10.0.9.8   2.07 MB    256     ?       c8d574b9-540c-410f-9326-789eb75d3d14  1c
UN  10.0.8.8   2.06 MB    256     ?       d9454056-a358-4428-ab5f-c03e8042167e  1d
UN  10.0.10.8  2.01 MB    256     ?       3617643d-b0a8-4b72-a9d4-feded4445292  1a

ubuntu@ip-10-0-9-8:~$ nodetool status
Datacenter: us-east
===================
Status=Up/Down
|/ State=Normal/Leaving/Joining/Moving
--  Address    Load       Tokens  Owns    Host ID                               Rack
UN  10.0.9.8   2.07 MB    256     ?       c8d574b9-540c-410f-9326-789eb75d3d14  1c
UN  10.0.8.8   2.06 MB    256     ?       d9454056-a358-4428-ab5f-c03e8042167e  1d
DN  10.0.10.8  2.09 MB    256     ?       3617643d-b0a8-4b72-a9d4-feded4445292  1a

ubuntu@ip-10-0-10-8:~$ nodetool status
Datacenter: us-east
===================
Status=Up/Down
|/ State=Normal/Leaving/Joining/Moving
--  Address    Load       Tokens  Owns    Host ID                               Rack
UN  10.0.9.8   2.07 MB    256     ?       c8d574b9-540c-410f-9326-789eb75d3d14  1c
UN  10.0.8.8   2.06 MB    256     ?       d9454056-a358-4428-ab5f-c03e8042167e  1d
UN  10.0.10.8  2.01 MB    256     ?       3617643d-b0a8-4b72-a9d4-feded4445292  1a

Everything looks fine except one thing (last line in second block):

DN  10.0.10.8  2.09 MB    256     ?       3617643d-b0a8-4b72-a9d4-feded4445292  1a

The D in the start of the line indicates the node being down. How can it be that 10.0.9.8 is seeing the node as down while the other nodes are seeing it just fine? Does this lead to inconsistencies?

Using Cassandra version 2.1.1 by the way.

Running nodetool enablegossip on the host that appeared down to other nodes fixed it for me and for now. However, it appeared as down to all other nodes I checked. Running in a non-cloud environment.

I was curious what my other nodes said and I found one that had the same issue (like your 10.0.9.8, showing 10.0.10.8 as down). Only running nodetool enablegossip on 10.8 didn't help. But running disablegossip first and then enablegossip again did!


List primary keys for all tables - Postgresql Is there a query that will do that?

I found some queries that can do this for one table, but I wasn't able to modify it so I can see:

tablename | column | type

This is more accurate answer:

select tc.table_schema, tc.table_name, kc.column_name
from
    information_schema.table_constraints tc,
    information_schema.key_column_usage kc
where
    tc.constraint_type = 'PRIMARY KEY'
    and kc.table_name = tc.table_name and kc.table_schema = tc.table_schema
    and kc.constraint_name = tc.constraint_name
order by 1, 2;

You missed the and kc.constraint_name = tc.constraint_name part, so it lists all constraints.


MySQL: How to calculate potential wait time? I'm trying understand the relationship between WAIT_MS and COUNT_STAR. Is this saying the average wait time is 3.2s and occurred 11,543 times for a total of approximately 10.5hrs of aggregate wait time?

Your response is greatly appreciated.

| EVENT_NAME                           | WAIT_MS   | COUNT_STAR |
+--------------------------------------+-----------+------------+

| wait/io/file/innodb/innodb_data_file | 3294.3345 | 11543      |

UPDATE: Sorry! It was late. Per Mark Leith's blog: http://www.markleith.co.uk/2010/09/24/tracking-mutex-locks-in-a-process-list-mysql-55s-performance_schema/

SELECT
   EVENT_NAME, SUM_TIMER_WAIT/1000000000 WAIT_MS, COUNT_STAR
FROM
   performance_schema.events_waits_summary_global_by_event_name
ORDER BY
   SUM_TIMER_WAIT DESC,
   COUNT_STAR DESC;

Is this saying the average wait time is 3.2s and occurred 11,543 times
  for a total of approximately 10.5hrs of aggregate wait time?

No, this is saying the wait occurred 11,543 times for a total aggregated wait of 3.2s.
The average wait time is then 3.2s / 11,543, or 0.285 milliseconds.

Note that every file io call, including file open, read, seek, close, read, write, etc is counted, and that what is measured is FILE io (at the OS level), not DISK io (at the hardware level).

Disclosure: I happen to work at MySQL, and I am the author of the performance schema.


Risk in evicting node from SQL Server cluster without prior uninstalling of SQL Server binaries I have to perform upgrade of a SQL Server failover cluster with several SQL Server 2005 instances running on two Windows Server 2003.

Since the plan is to have servers running Windows Server 2008 and SQL Server 2008, two additional nodes were added to cluster, SQL Server binaries installed and cluster resources moved to temporary nodes.

At the moment I have 4 nodes in cluster and need to evict original two nodes so that it can be re-installed with Windows Server 2008, form cluster and migrate data back from temporary nodes to original nodes.

Question is: do I need to "properly uninstall" the SQL Server binaries from original nodes before evict? SQL Server resources will be running on temporary nodes after that for about two weeks and original cluster will be thus "destroyed". I mean nodes will not be again connected back to cluster, but can you think of any other risk in evicting cluster nodes other than possible issues with connecting server back to cluster?

Decided to take a risk and test it in production.
Took out 2 nodes from 4 node cluster without uninstalling SQL from those and I can confirm for the two weeks of being in this situation I haven't receive any complaint from users.

Hope this helps others potentially dealing with same question


PostgreSQL: ERROR: cannot execute CREATE DATABASE in a read-only transaction I installed PostgreSQL and and its GUI, pgAdmin III.
Setup already had a default database postgres.

When I am trying to create a new table or database, it is showing an error:

  ERROR:  cannot execute CREATE DATABASE in a read-only transaction

I uncommented the line default_transaction_read_only = off in postgresql.conf and executed the command pg_ctl start -D /home/jaison/progresData -Z datanode -l logfile but it is still showing the same error.

please help me to resolve this issue.

You could always double check your problem by issuing a command such as

/usr/bin/pgsql/bin/createdb test

You can frequently get better/clearer error messages and/or other information from the command line than from GUI tools such as pgAdmin III.

But, in order to change a system parameter, you have to reload the postgresql.conf file after making changes. You can do this by either

rebooting the server or
reloading the .conf file or
change on the client side

From here, some parameters always need a reboot and some can be dynamically changed by simply reloading.

I tried to find a clear list of those which required reboot and which required reload, but failed (grateful for input on this!).

For a system reboot,

pgctl restart

To reload, you could also try these commands (from here - or check the documentation page given above).

Option 1: From the command-line shell

su - postgres /path/to/pg_ctl reload

Option 2: Using SQL

SELECT pg_reload_conf();

You can verify that the setting has "taken" from the table pg_settings. Also of interest is this page.

For the default_transaction_read_only parameter, you can also set it directly from the client (see here). Check the links here for how to set all parameters - reboot, reload or client.

test=# SELECT name, setting FROM pg_settings WHERE name = 'default_transaction_read_only';
             name              | setting
-------------------------------+---------
 default_transaction_read_only | on
(1 row)

test=#
test=# `SET default_transaction_read_only = OFF;`
SET
test=#
test=# SELECT name, setting FROM pg_settings WHERE name = 'default_transaction_read_only';
             name              | setting
-------------------------------+---------
 default_transaction_read_only | off
(1 row)

test=#
test=# SET default_transaction_read_only = ON;
SET


How can copy one column data of one table1 into other table2 column and other clumns table2 empty? Here is my problem.I have two tables Stdd and result.

stdd columns:

id
fname
lname
gender
phonenum
city

result columns:

physics
chem
math
english
urdu
bio
total
id

I want that in result table the column id copy data from stdd column id, and other columns of result columns remain empty or null.

How can i do this please help. I try this but nor works for me

       update result set  id= (select id from stdd)

following error come

  Msg 512, Level 16, State 1, Line 1
             Subquery returned more than 1 value. This is not permitted when  the subquery follows =, !=, <, <= , >, >= or when the subquery
  is used as an expression.
             The statement has been terminated.

I want this kind of output.Here is 'result' table

I'm using microsoft sql server 2008

You could use INSERT with a SELECT.

INSERT INTO result (id) SELECT id FROM stdd

With this your SQL Server adds per row in stdd one row in result with the id from stdd.

If you can use multiple columns, too.

INSERT INTO result (id, col2, col3) SELECT id, s_col2, s_col3 FROM stdd


AlwaysOn Availability Groups - Proper Quorum across WAN I am evaluating the usage of AlwaysOn Availability groups with one node located in one data center, and another node located in a DR site. Both nodes are running in vmware on the same domain.

I've been reading about the dangers of using a DFS file share as the "file share" in a Node + File Share Majority. However, vmware's high availability guide says to use Node + File Share for any 2-node cluster. What should I be using across a WAN for that if not a DFS Share?

For this you would (hopefully) be using AGs in an asynchronous fashion (latency will kill performance otherwise). This means that you will not get automatic failover to your DR site, that's just something to be aware of. I would also, typically, not recommend using less than 3 nodes, but if that is not an option for you...

As regards quorum. You would want to use a file share witness + node majority configuration. Keep the file share witness in your primary site, on a server that is not a part of the WSFC (Windows Server Failover Cluster).

In the event that you need to move to your disaster recovery site, adjust your file share witness to be local to the server there. This will allow you to maintain a voting quorum in your primary site at all times.

Just be very careful when performing patching of your witness server not to end up losing quorum.


reset sql server to default? I am trying to learn to mirror a test database and in the course of doing this, was typing in some commands into a query box.  While trying to create a database mirroring endpoint I submitted a command with a typo.  I was returned an error.  I tried to resubmit a corrected command, but then was told that the endpoint already existed. So then I figured I'd just delete my test database and restart. I deleted the database in my primary SQL Server, but now my mirror SQL Server shows my test database as restoring, and I am unable to delete that one.

Is there a way to undo everything that I have done, going back to a installation default of my SQL Server?  There is nothing I need to save, I'm just trying to learn.

To completely reset everything would involve uninstalling/reinstalling SQL Server.  But this is overkill.  Instead it sounds like you have two things you need to do.

Thing 1

First thing you should do is clean up your endpoints.  I would do this on both your primary and mirror server.  The DROP ENDPOINT command is straightforward.  If you need to see what endpoints are currently out there, you can select from sys.endpoints.

Thing 2

Drop your old database.  If it's stuck in recovery, the first thing to check is your SQL Error Log on the mirroring target and make sure it isn't actually doing anything.  You want to look for messages containing the word "Recovery" and "Step n of 3".  If these are there, it's still in the process of restoring.  Otherwise, you just need to complete the recovery with:

RESTORE DATABASE [foo] WITH RECOVERY;

Once you have completed recovery and the database is online, you should be able to drop it.


archive_command: Can hardlink (ln) be used instead of cp? We use Postgres 9.6, with 10+TB, with a multi-node cluster setup, managed by Patroni. The WAL archives and backups are managed by home grown tool pgrsync.
The archive_command was initially set to cp %p /archives/%f. There is a background job (pgrsync) that pushes the archives to S3 periodically. The volume of WAL archives was higher (avg around 200 WAL files/min, with the peak being 500/min). The cp also adds to the Disk IO bandwidth, which is precious for us in a cloud environment.
We are looking to optimise this in the application. Also, I noticed that in pg_xlog folder that several files were hard link to other WAL files. (This part is not understood fully, how could Postgres internally have one WAL archive being a link to another -- it is unlikely that so many transactions could be repeated exactly after some time).
Anyway, as an optimisation exercise, we set the archive_command to ln %p /archives/%f. This reduces the disk IO, we are just adding one more link to the same file. When we are done copying to S3, the link is removed and the OS manages deleting the actual file, when Postgres also frees it. Looks good on paper. Except one problem: If Postgres writes to the same file (with the same inode) after completing the archive_command, then we are in a mess. Please refer postgres: WAL ends before end of online backup where we are seeing random WAL corruption and we don't know if using ln caused this.
Question: Is it safe to use ln (hardlink) instead of cp in archive_command?

WAL files are default 16MB. We have a plan to increase it, but I suspect it will reduce the number of files, but the size and IO will likely remain the same. Not likely to help, isn't it?
Backup WAL from a standby node is a good idea - or the home grown consumer for archiving. Or even simply scp to another server, to reduce disk write IO on Postgres server. Since anything could be standby or master at any time, managing who will actually push files could be slightly complex. So, I would either go for archiving consumer or scp to non-Postgres server.
Reg not invoking pgrsync directly, We did this initially. pgrsync would then have to handle each file individually in-series (compress and push to S3). This was slowing down the ability to handle 500 WAL files (or 500*16MB WAL bytes). Copying to another location gives pgrsync the ability to push files in parallel and thus the ability to handle 500 WAL files per min. I was avoiding the need for another server (rsync / scp to), but it looks like that is a better approach now.

No, that is not an option and will lead to trouble.
The first and most obvious reason is that the WAL segments exists only once, even if there is a hard link to it in another directory. If anything happens to the disk with the file, you have no copy and cannot recover.
The second reason is more subtle and will lead to data corruption. When a WAL segment is archived and older than the latest checkpoint, it is not necessarily deleted. If PostgreSQL determines that it needs new WAL segments (max_wal_size or – in older versions – checkpoint_segments has not been exceeded and there is much data modification activity going on), it will recycle the WAL segment rather than deleting it.
If that happens, and PostgreSQL starts writing to the recycled WAL segment before you have managed to copy it to safety, the original WAL entries will be overwritten and are lost.  If you recover using such a WAL segment, PostgreSQL will notice that there are unexpected data in the WAL and will stop recovering.


My cluster disappeared, but data is still there I was tasked with recovering data from an old, unmaintaned server, where its main purpose application was no longer working. Basically just want to get a postgres db dump before burning the server to the ground.

I ssh into the instance, but cannot access the database.

psql: FATAL:  database "foobar" does not exist

That's weird. I do pg_lscluster

Version Cluster   Port Status Owner    Data directory                     Log file
9.1     main      5433 online postgres /var/lib/postgresql/9.1/main       /var/log/postgresql/postgresql-9.1-main.log

Okay, something is running. I check the data dir ls /var/lib/postgresql/

8.4  9.1

Huh, okay then. looking at the contents of the two dirs, it is pretty clear that all of the data is in the 8.4 folder, and the 9.1, I can see only holds default data, when I look using psql for the running cluster.

So somehow the cluster that ran 8.4 was removed. No idea why or how (I'm not the only one with access to the server, but I cannot find anything supicious in bash history).

So how would I go about restoring access to the data, so I can pull out a dump?

Alright, I managed to solve it.

I tried to create the cluser

pg_createcluster 8.4 main

It complained that 8.4 was not an installed version. Alright then, this confirms my suspicion, that at some point postgres was upgraded to the latest version in the debian repos, and the data left behind.

So I went to reinstall 8.4 (Remember to backup your data dir, if you want to follow these steps. Don't hold me accountable for any dataloss)

sudo touch /etc/apt/sources.list.d/pgdg.list
sudo echo "deb http://apt.postgresql.org/pub/repos/apt/ wheezy-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-8.4
pg_lsclusters

The output of pg_lsclusters showed that the 8.4 main cluster was created during the installation. I was afraid that it would have nuked the data dir with a fresh installation, but it didn't. Running psql revealed all the old data was still there


Problem in starting MySQL Cluster I have installed MySQL Cluster necessary packages and I am now using Auto installer to config the nodes properly. when I try to start the Cluster In Deploy Configuration part, all the nodes (1 management node, 4 data nodes) starts correctly except SQL node and I got below error.

Command `/usr/local/mysql/bin/mysqld --defaults-file=/home/debian/MySQL_Cluster/49/my.cnf', running on 192.168.120.107 exited with 1:
2016-04-03 16:32:49 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-04-03 16:32:49 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.6.28-ndb-7.4.10-cluster-gpl) starting as process 11472 ..

you see all the nodes are started. I use the command it recommended with --explicit_defaults_for_timestamp option like below.

 /usr/local/mysql/bin/mysqld --defaults-file=/home/debian/MySQL_Cluster/49/my.cnf  --explicit_defaults_for_timestamp
2016-04-03 16:42:06 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.6.28-ndb-7.4.10-cluster-gpl) starting as process 11503 ...

you can see with --explicit_defaults_for_timestamp  option service start correctly but still when I use management node to see the status I see the cluster has a problem see management node :

ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 4 node(s)
id=1    @192.168.120.111  (mysql-5.6.28 ndb-7.4.10, Nodegroup: 0, *)
id=2    @192.168.120.117  (mysql-5.6.28 ndb-7.4.10, Nodegroup: 0)
id=3    @192.168.120.118  (mysql-5.6.28 ndb-7.4.10, Nodegroup: 1)
id=4    @192.168.120.76  (mysql-5.6.28 ndb-7.4.10, Nodegroup: 1)

[ndb_mgmd(MGM)] 1 node(s)
id=50   @192.168.120.79  (mysql-5.6.28 ndb-7.4.10)

[mysqld(API)]   1 node(s)
id=49 (not connected, accepting connect from 192.168.120.107)

as you can see the SQL node still has not been started correctly :

[mysqld(API)]   1 node(s)
id=49 (not connected, accepting connect from 192.168.120.107)

I don't understand the problem.

Answering my own question, I could say the MySQL in the SQL node was running so when the MySQL Cluster auto installer was trying to start the the SQL node it failed so by stopping the MySQL in the SQL node and starting the cluster the problem solved.


How to represent NULL in relational algebra? In the 3rd part of the question below:

  Study the tables given below and write relational algebra  expressions for the queries that follow.

STUDENT(+ROLLNO, NAME, AGE, GENDER, ADDRESS, ADVISOR)
COURSE(+COURSEID, CNAME, CREDITS)
PROFESSOR(+PROFID, PNAME, PHONE)
ENROLLMENT(+ROLLNO, +COURSEID, GRADE)

  Primary keys are preceded by a +. ADVISOR is a foreign key referring to PROFESSOR table. ROLLNO and COURSEID in ENROLLMENT are also foreign keys referring to THE primary keys with the same name.

  Names of femal students
  Names of male students along with advisor name
  Roll Number and name of students who have not enrolled for any course.

How do I represent NULL marks in relational algebra?

I projected STUDENT NAME and ROLLNO after assigning COURSEID from ENROLLMENT table as

COURSEID = NULL

Is this valid?

There are no NULL values in relational algebra. Here you can use the antijoin operator ▷:

  πROLLNO, NAME(STUDENT ▷ENROLLMENT)

that selects all records from STUDENT where the ROLLNO is not in ENROLLMENT.

In SQL there are NULL values. You may use a left outer join and select the rows where the courseid is NULL:

select STUDENT.ROLLNO, STUDENT.NAME
from STUDENT left outer join ENROLLMENT
    on(STUDENT.ROLLNO=ENROLLMENT.ROLLNO)
where ENROLLMENT.COURSEID is null


Should I split these fields or leave as one? I want to create a database listing characters in each book of the A Song of Ice and Fire series, listing the name of the character and the page in which they appear. I could just use three fields (not including the primary key,) Name, PageIntroduced, and BookIntroduced, but the Name field isn't atomic; I could be subdivide it quite a bit. Some characters have titles (Maester, Lord, Ser,) some characters have house names (Stark, Lannister, Baratheon,) plenty have nicknames (Bull, Ned, Arry,) and there are quite a few kings differentiated by regnal numbers (there are five Aegon's!) So my table might look like the following:

CHARACTER (
ForeName
HouseName
Title
NickName
RegnalNumber
PageIntroduced
BookIntroduced
)

The question is: is this complete overkill, or should I use a single "Name" field encompassing all five attributes, even if it's non-atomic? I'm sure it's neater, especially for "King Aegon V "the Fortunate" Targaryen", but it would lead to a lot of fields not being used - many characters have only one name and nothing else, such as "Rorge", "Biter" and "Hot Pie". Is this a problem in databases? Does it matter if fields are left blank in databases?

Splitting given names from surnames is not normalisation.  What would be normalisation would be if you created a series of tables that contained additional details about some of the columns in your table.

For example, you could create a HOUSE table that contains information about the house other than the name, such as it's catch phrase or home town etc.  You might do something similar with TITLE and BOOK.

Don't worry about nulls in your case.  Some people are concerned about too many nulls because it can be a sign that your design is not correct or because they are worried about wasting disk space.  In your case neither of these concerns is germane.


How to find out SQL Server options updated Two days ago, we started having problems on a production server executing stored procedures.
No problems on any other servers, only production.
Our investigations found out that the server connection properties XACT ABORT were set to ON, and this option is ON only on the production server.
Now we want to understand when, and possibly who, changed this option. We are looking at the system tables but till now without any luck.
Is there someone who knows if there is a way?

View errorlog or default trace if is ON

sp_configure'default trace enabled'

DECLARE @trc_path VARCHAR(500)
SELECT @trc_path = CONVERT(VARCHAR(500),value) FROM fn_trace_getinfo(DEFAULT)
WHERE property=2

SELECT @trc_path
SELECT
    TEXTData,
    HostName,
    ApplicationName,
    DatabaseName,
    LoginName,
    SPID,
    StartTime,
    EventSequence
FROM fn_trace_gettable(@trc_path,1) fn
WHERE TEXTData LIKE '%configure%'