Author: Enrique

No comments

MySQL – Backup Stored Procedures, Functions and Triggers

  By

By default, mysqldump will backup all the triggers but NOT the stored procedures and functions.

There are 2 parameters that control this action:

--routines (FALSE by default)
--triggers (TRUE by default)

If you want to include the stored procedures and triggers, you need to add the –routines in your backup command as follows.

This command will backup the entire database including stored procedures.

mysqldump -u USERNAME -p --routines DBName > outputfile.sql

But if you wanna backup just the stored procedures and triggers (excluding table and data), use the following command.

mysqldump -u USERNAME -p --routines --no-create-info --no-data --no-create-db --skip-opt lm_cia > outputfile.sql

You can also put routines=true in the [mysqldump] section of your my.cnf

Cheers!

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
2 Comments

MySQL – Restore a Single Table from a FULL mysqldump file

  By ,

Use the sed command on your bash shell to separate the data of the table that you want to restore. For example, if we want to restore only the “film_actor” table to “sakila” database we execute the script below.


sed -n -e '/DROP TABLE.*`mytable`/,/UNLOCK TABLES/p' mydump.sql > tabledump.sql

You can now import the newly created table dump file into MySQL database.

mysql -u root -p sakila < film_actor.sql

Cheers!

No comments

SQL Server – Get Database Owner Names via T-SQL

  By , ,

Below is a simple script to get the database owner names.

SELECT 
    name AS [Database Name], 
    suser_sname( owner_sid ) AS [Database Owner Name]
FROM 
    sys.databases

Cheers!

No comments

SQL Server – Get All Column Names from a Particular Table

  By , ,

There are two techniques to achieve this: 1) Using Sys Schema; 2) Using INFORMATION_SCHEMA.COLUMNS

Technique 1: Using Sys Schema

SELECT OBJECT_SCHEMA_NAME (c.object_id) SchemaName,
        o.Name AS Table_Name, 
        c.Name AS Field_Name,
        t.Name AS Data_Type,
        t.max_length AS Length_Size,
        t.precision AS Precision
FROM sys.columns c 
     INNER JOIN sys.objects o ON o.object_id = c.object_id
     LEFT JOIN  sys.types t on t.user_type_id  = c.user_type_id   
WHERE o.type = 'U'
-- and o.Name = N'TableName'
ORDER BY o.Name, c.Name

Technique 2: Using INFORMATION_SCHEMA.COLUMNS

SELECT *
FROM SVS.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'TableName'

Cheers!

No comments

SQL Server – How to Check if SSL is Enabled

  By ,

Run the query below to immediately detect if SSL is configured on your SQL Server.

SELECT session_id, encrypt_option
FROM sys.dm_exec_connections

This will show the session_ids currently connected to SQL Server. If the value of encrypt_option is TRUE, then it is using a secured connection.

Cheers!

No comments

SQL Server – syspolicy_purge_history Job Failed to Run

  By ,

I just installed an Enterprise Editon of SQL Server 2016 on Windows 2012 R2. I noticed the syspolicy_purge_history is failing.

 

Below is the error message. I observed the line set-ExecutionPolicy RemoteSigned -Scope process -Force

I tried to set the execution policy to RemoteSigned in Powershell as what was suggested in the error message but was not successful even when I run powershell as Administrator.

What is syspolicy_purge_history?

This job removes policies evaluation history. When a policy runs, it stores the results in MSDB. Over a period of time, this may will cause unnecessary data getting piled up in MSDB. The job syspolicy_purge_history prunes the data older than the the days defined in HistoryRetentionInDays property of Policy Management.

CAUSE:

The ‘Execution Policy’ at the Machine Policy scope is currently set to Unrestricted. This may override the configuration at the ‘Process’ scope.

SOLUTION:

This issue may be resolved by setting the ‘ExecutionPolicy’ to ‘RemoteSigned‘. But I could not change the ‘ExecutionPolicy’ in Powershell. So what I did was to change it directly in the registry.

I verified the change I made in Powershell.

The job syspolicy_purge_history now runs successfully.

No comments

SQL Server – VIEW SERVER STATE

  By ,

VIEW SERVER STATE is a server level permissions that allows a user to view Dynamic Management Objects.

Users will encounter the error below if they don’t have this permission.

 Msg 300, Level 14, State 1, Line 1
VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’.
Msg 297, Level 16, State 1, Line 1

The user does not have permission to perform this action.

There are 2 ways to grant this permission to a user: T-SQL and SSMS

  1. Via SSMS.

From Server properites to to Permissions.

2. T-SQL

GRANT VIEW SERVER STATE TO AppMIS

 

Cheers!

No comments

MySQL – Solution to Dependency Issues During MySQL Installation Using RPM

  By ,

For complete steps on how to install MySQL 5.7 on Redhat, you may refer to this link

In this blog, I will show you specifically how simple it is to resolve the dependency issues that you might encounter while installing MySQL 5.7 on RedHat 6 using RPM.

Below are the dependency errors I encountered during installation.

The solution is simple. Just put the option –nodep at the end of the rpm command.

# rpm -ivh mysql-commercial-embedded-devel-5.7.21-1.1.el6.x86_64.rpm --nodeps

That’s it.

Cheers

No comments

SQL Server – How To Configure Peer-to-Peer Replication in SQL Server 2016

  By ,

First, let’s run through the Replication components before we setup Peer-to-Peer replication.

  • Article – the object we want to replicate
  • Publication – Group of articles
  • Publisher – Server that contains publication
  • Subscriber – Receives publication
  • Distributor – Controls agent and tracks subscribers
  • Agent – Tasks to move data

What is Peer-to-Peer Replication?

In Peer-to-Peer, what it does is that it takes the one-way street where everything that happens in the publisher gets pushed off to the subscriber and turns it into a two-way street (bi-directional replication). Everything that happens in the subscriber can also be pushed to the publisher. In Peer-to -Peer, we only have conflict detection not conflict resolution. The key with peer to peer is  to avoid conflict entirely.

Steps To Setup Peer-to-Peer Replication:

1. Create distribution on all instances that are participating in Peer-to-Peer.

You must configure a distributor for each node that will be participating in peer-to-peer else you will encounter the error below.

To set up distributor, follow the steps below.

right-click on Replication > Configure Distribution…

Click Next.

Choose the first option. Click Next.

Specify a snapshot folder path. Click Next.

Click Next.

Click Next.

Click Next.

Click Finish.

Click Close.

Note: Do the same for all the instances that will be participating in Peer-to-Peer Replication

2. Backup the databases to replicate then restore it in the subscriber node (data needs to be synchronized first before we configure peer-to-peer)

3. Create publication.

Right-click on Local Publication > New Publication…

Click Next.

Choose the database where the articles to be published are located. Click Next.

Choose Peer-to-Peer Publication. Click Next.

Choose the object/s to publish. Click Next.

Click Next.

It is a best practice to specify the account that you will use to run the Log Reader Agent process. However, for the purpose of this Demo, I will just use the SQL Server Agent service account. Click OK.

Click Next.

Optionally, you can generate a script file with steps to create the publication. I will not choose to in this demo.

Click Next.

Give your publication a name then click Finish.

Click Close.

4. Configure Peer-to-Peer topology.

Right-click on your publication > Configure Peer-to-Peer Topology.

Choose the newly created publication. Click Next.

Hover your cursor on the first node. Take note of the Peer originator id. As you can see, it is 100.

Now, on the topology Wizard,  right-click on the design surface> Add a New Peer Node.

Connect to subscriber node.

Select the database on your subscriber. This is the database that we restored from your publisher. If you recall, at the beginning of this tutorial, that we backed up the database that we want to replicate and restored it in the subscriber node.

The Peer Originator ID of the first node is 100 remember? This is now the 2nd node and we will set the Peer Originator ID to 101. If we want add more nodes to the topology, then just set the Peer Originator ID of the next node to 102, the next node to 103, etc. You get the idea.

Tick Connect to ALL displayed nodes and choose Use Push subscription (optionally you can choose Pull subscription)

Click OK.

Now we can see the 2nd node that we just added visually on the design surface.

Click Next.

Click the ellipsis…

It is a best practice to specify an account that you will use to run the Log Reader Agent process. However, for the purpose of this Demo, I will just use the SQL Server Agent service account. Click OK.

Click Next.

Click the ellipsis…

It is a best practice  to specify an account that you will use to run the Distribution Agent process. However, for the purpose of this Demo, I will just use the SQL Server Agent service account. Click OK.

Tick the “Use the first peer’s security settings for all other peers”. This is useful especially if you have many peers configured.

Click Next.

In the next page of the wizard,  we have to specify how we want the new peer(s) to be initialized. Choose the first option if you know that there are no changes made since the last the backup was taken. However, if there were data modification after the backup was taken, choose the second option.

Click Next.

Review the choices that you have made, then click Finish.

Click Close.

In this blog post,  we have learned how to setup Peer-to-Peer Replication. Everything that happens in the subscriber will also be published to the publisher. Just take note that in Peer-to-Peer, we only have conflict detection not conflict resolution. We have to avoid conflict entirely.