Posts

Showing posts from September, 2018

Error: 983, Severity: 14, State: 1;Unable to access availability database 'xxxx' because the database replica is not in the PRIMARY or SECONDARY role.

There is an error occurred on Always On High availability Environment   today. There are two nodes One primary replica and another is Secondary replica. Assume here A is Primary replica and B is a secondary replica. Windows team has a plan to update NET BIOS. So they want do the update on Secondary node(B) which is acting as secondary replica now.   So every database is in Synchronized State in secondary replica(B) because   AG configured as Synchronous Commit.   After NET BIOS update windows team started the services. Being a DBA, we need to monitor and do the fail over. Once services are started in Secondary replica(B) databases started coming to Synchronized state one by one. There is a database called Orders is taking time as its size is near 1 TB.   After an hour of waiting even this database come to Synchronized state and as usual it Secondary replica now(B) because we hit the secondary replica first. Now we want to do the same update in Prim...

Cluster File Server resource can not bring online.

Image
In one of my cluster environments i got the below error saying "Cluster fail server resource xxx can not be brought online.The resource failed to create DOJ". For full error please the below message. So what i did was i have created a folder name as DOJ in the respective shared cluster disk.Here it is D drive.  Creating folder with above mentioned name has been resolved the issue. 

Msg 5120, Level 16, State 101, Line 13 Unable to open the physical file_Operating system error 5: "5(Access is denied.)

Image
I got the below error while i am trying attach .mdf file of AdventureWorks database. I am the member of sysadmin group. And the path in which i placed .mdf file is also correct. But when i try to attach the database i am getting this below error. First i thought this was permission issue, later i realized i have not opened SQL Server management studio "Run as Administrator mode". Once i did that, now i am able to restore the database successfully.

SSIS Transformations(Transforms)

Transformations are a key component to the Data Flow that change data to a format that we like it. Below are the couple of Transforms. ➤➤Aggregate—Aggregates data from a transform or source similar to a GROUP BY statement in T-SQL. ➤➤ Conditional Split—Splits the data based on certain conditions being met. For example, if the State column is equal to Florida, send the data down a different path. This transform is similar to a CASE statement in T-SQL. ➤➤ Data Conversion—Converts a column’s data type to another data type. This transform is similar to a CAST statement in T-SQL. ➤➤ Derived Column—Performs an in-line update to the data or creates a new column from a formula. For example, you can use this to calculate a Profit column based on a Cost and SellPrice set of columns. ➤➤ Fuzzy Grouping—Performs data cleansing by finding rows that are likely duplicates. ➤➤ Fuzzy Lookup—Matches and standardizes data based on fuzzy logic. For example, this can transform the name Jon to John. ➤➤ ...

Unable to connect to the Integration Services.

Image
I am unable to connect to the Integration Services and i am getting below error.   Though my account has sysadmin privileges i am getting above error. The reason is we need to connect SQLServer Management studio run as administrator (right click on ssms and run as Administrator). Then only it will get connect. Don't look for your service accounts.

Change Data Capture(CDC) in SQL Server

USE CDC GO CREATE TABLE Customer ( CustomerId INT PRIMARY KEY ,FirstName VARCHAR ( 30 ) ,LastName VARCHAR ( 30 ) ,Amount_purchased DECIMAL ) GO INSERT INTO dbo.Customer ( CustomerId, FirstName, LastName, Amount_Purchased) VALUES ( 1 , 'Frank' , 'Sinatra' , 20000 . 00 ), ( 2 , 'Shawn' , 'McGuire' , 30000 . 00 ), ( 3 , 'Amy' , 'Carlson' , 40000 . 00 ) GO SELECT * FROM dbo.Customer -- Now enable CDC at the Database Level EXEC sys.sp_cdc_enable_db GO -- Enable on the table level EXEC sys.sp_cdc_enable_table @ source_schema = N 'dbo' , @ source_name = N 'Customer' , /* Main parameter to remember @capture_instance. If you dont mention this parameter jobs will not create other functions will also get affect */ @ capture_instance = N 'Customer' , @ role_name = NULL , @ filegroup_name = N 'Primary' , @ supports_net_changes = 0 GO INS...

Basic T-SQL Queries.

IF OBJECT_ID ( 'dbo.Employees' , 'U' ) IS NOT NULL DROP TABLE dbo . Employees ; GO CREATE TABLE dbo . Employees ( EmpID INT NOT NULL, FirstName varchar ( 30 ) not null, LastName VARCHAR ( 30 ) NOT NULL, HireDate DATE NOT NULL, MgrID INT NULL, ssn varchar ( 20 ) not null, Salary MONEY not null ) ALTER TABLE dbo . Employees ADD CONSTRAINT PK_Employees PRIMARY KEY ( EmpID ) GO ALTER TABLE dbo . Employees ADD CONSTRAINT UNQ_Employees_ssn UNIQUE ( ssn ) GO IF OBJECT_ID ( 'dbo.Orders' , 'U' )IS NOT NULL DROP TABLE dbo . Orders CREATE TABLE dbo . Orders (   OrderID INT NOT NULL,   EmpID INT NOT NULL,   CustID VARCHAR ( 10 ) NOT NULL,   OrderTs DATETIME2 NOT NULL,   Qty INT NOT NULL,   CONSTRAINT Pk_Orders PRIMARY KEY ( OrderID ) ) GO ALTER TABLE dbo . Orders ADD CONSTRAINT Fk_Orders_Employees FOREIGN KEY ( EmpID ) REFERENCES dbo . Employ...