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!
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!
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!
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.
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
From Server properites to to Permissions.
2. T-SQL
GRANT VIEW SERVER STATE TO AppMIS
Cheers!
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
First, let’s run through the Replication components before we setup Peer-to-Peer replication.
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.
I always use SQL profiler
After listening to the presentation, I was inspired to use Extended Events.
Below is how I use it to capture Query Performance.
1. Create extended event to capture query performance. Indicate the username and DatabaseName that you want to monitor.
CREATE EVENT SESSION [QueryPerformance] ON SERVER
ADD EVENT sqlserver.rpc_completed(SET collect_statement=(1)
ACTION(sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[username],N'username') AND [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'DatabaseName') AND [duration]>=(500000))),
ADD EVENT sqlserver.sql_batch_completed(
ACTION(sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[username],N' username ') AND [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'DatabaseName') AND [duration]>=(500000)))
ADD TARGET package0.event_file(SET filename=N'D:\DBA\ExtendedEvents\QueryPerformance\QueryPerformance.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO
2. Start extended event (Session Name: QueryPerformance)
To start extended event session via T-SQL:
ALTER EVENT SESSION [QueryPerformance] ON SERVER STATE = START
3. Stop Extended Events.
Via T-SQL:
ALTER EVENT SESSION [QueryPerformance] ON SERVER STATE = STOP
Note:
Now, a file has been generated in your server.
4. Load the data captured from extended events to a table. Preferrably to a utility database. Execute the script below on your utility database. You may change the name of the target table where you want to load the data captured.
WITH xEvents
AS (SELECT object_name AS xEventName,
CAST (event_data AS XML) AS xEventData
FROM sys.fn_xe_file_target_read_file('D:\DBA\ExtendedEvents\QueryPerformance\QueryPerformance*.xel',
NULL, NULL, NULL)
)
SELECT xEventName,
xEventData.value('(/event/data[@name=''duration'']/value)[1]','bigint') Duration,
xEventData.value('(/event/data[@name=''physical_reads'']/value)[1]','bigint') PhysicalReads,
xEventData.value('(/event/data[@name=''logical_reads'']/value)[1]','bigint') LogicalReads,
xEventData.value('(/event/data[@name=''cpu_time'']/value)[1]','bigint') CpuTime,
CASE xEventName
WHEN 'sql_batch_completed'
THEN xEventData.value('(/event/data[@name=''batch_text'']/value)[1]',
'varchar(max)')
WHEN 'rpc_completed'
THEN xEventData.value('(/event/data[@name=''statement'']/value)[1]',
'varchar(max)')
END AS SQLText,
xEventData.value('(/event/data[@name=''object_name'']/value)[1]','varchar(max)') Object_Name
INTO Table_Name
FROM xEvents;
5. Execute the script below to get the Stored Procedure names and their run duration and total execution count.
SELECT Object_name, MIN(st.Duration)/1000 AS MIN_Duration_ms, AVG(st.Duration)/1000 AS AVG_Duration_ms, MAX(st.Duration)/1000 AS MAX_Duration_ms, COUNT(*) AS TotalExecutions FROM Table_Name AS st GROUP BY Object_name ORDER BY AVG(st.Duration) DESC;
Note: The duration captured from extended events is in microseconds. That is why in my script, I divided it by 1000 to convert it to milliseconds (ms). It is easier to read in ms.