In this tutorial, I’ll show you how to draw a histo-graph using your computer’s CPU usage as the values.
Alter Schema
Schema’s are use to help isolate users to a specific schema, ETL purposes, reusing a table name that already exists, and etc. reasons. In this video, I’ll show you how to import data into a table. Once the data is ready an Alter Schema is performed to prevent any downtime of the table.
OraOLEDB.Oracle Linked Server
Do you want to use Oracle’s driver to directly connect to an Oracle database instead of using ODBC (DSN)? Is it any faster? You be the judge!
Step 1
Make sure you have Oracle 12g drivers installed on the server. This could be any version of Oracle
Step 2
Make a note as to where your Oracle installation path is. You’ll need a few information from the TNSADMIN.ora file. For example, my path is C:\Oracle\Ora12\Network\Admin\tnsadmin.ora
Inside tnsadmin.ora, you’ll need the HOST, PORT, and SERVICE_NAME
Step 3
In SSMS, navigate to the OraOLEDB.Oracle providers and open up the properties
Step 4
Create a Linked Server
- Linked Server: Call this Linked Server whatever you want
- Provider: Select Oracle Provider for OLEDB
- Product name: Call this whatever you want
- Data source: This information comes from Step 2 in this format HOST:PORT/SERVICE_NAME i.e.(My_Host.afterowl.com:1571/My_Service_Name.afterowl.com)
- Click on the Security tab on the left and use Be made using the security context. Then click OK to create the Linked Server
Step 5
This is how you would use your new shiny Oracle Linked Server
SELECT * FROM OPENQUERY(“ORACLE”, ‘SELECT * FROM MyTable’) AS MyTable
In Recovery Status
Sometimes a very large database (VLF) stalls and or takes forever and a day to perform a 2 minutes task. In these fun times we restart the server. It gets funner when the server is back online and your database is IN RECOVERY.
This generally happens prior to the server restart, you still have large transactions running (i.e. millions/billions or records being DELETE/UPDATE/INSERT).
To check on the status of your database simply run
EXEC sys.sp_readerrorlog 0, 1, ‘Your_Database_MDF_Name’
At this point, you can’t use sp_helpfile because your database is inaccessible therefore navigate to where the mdf file is located. Most of the time, the mdf file is the same name as your database.
When you run the command above, in your message tab you’ll see the percentage of completion and how many seconds to go.
WAITFOR it …
There are times when you want to delay a script such as sending yourself a reminders or to run a job/stored procedure/etc.
Wait by Time
BEGIN
WAITFOR TIME ’01:10′;
PRINT ‘This message will print at 1:10 am’;
END;
Wait by Delay
BEGIN
WAITFOR DELAY ’01:00′;
PRINT ‘This message will print in one hour’;
END;
SQL Back Door – DAC (Dedicated Administrator Connection)
There will be a time or more in your life where SQL Server becomes unresponsive and stops working. You can try to remote desktop into the server with no luck– or take the Rambo route and hard restart the server or have IT do it. This is where DAC (Dedicated Administrator Connection) comes into play. DAC allows one back door SA connection into the SQL Server. To use DAC, you first have to turn it on and here’s how you do it.
GUI Method
First open up SQL Management Studio
Step 1. Right click on your server
Step 2. Click on Facets
Once the Facets windows show up do the following
Step 1. Under the Facet drop-down-list, pick Surface Area Configuration
Step 2. Select RemoteDacEnabled and from the drop-down-list pick True
Step 3. Click OK
That’s all to it
If you prefer the T-SQL method
USE master
GO
sp_configure ‘remote admin connections’, 1
GO
RECONFIGURE WITH OVERRIDE
GO
How to log onto the server using DAC
Step 1: Open up SQL Server Management Studio (SSMS)
Step 2: On the tool bar, click on Database Engine Query
Step 3: In the Server Name box, type in ADMIN: before your server name.
Step 4: Click Connect
Once connected, you can then troubleshoot your SQL Services. If you can’t log in, it means two thing.
- You don’t belong in the SysAdmin group
- A SysAdmin is already logged onto the server
As always, I hope this helps.
