SELECT*

Creating root certificate and client certificate to establish a VPN connection to cloud from On premises. We can run the below commands from power shell $cert = New-SelfSignedCertificate -Type Custom -KeySpec Signature ` -Subject "CN=ROOTCERTIFICATE" -KeyExportPolicy Exportable ` -HashAlgorithm sha256 -KeyLength 2048 ` -CertStoreLocation "Cert:\CurrentUser\My" -KeyUsageProperty Sign -KeyUsage CertSign <#   We are generation a Client certificate from root certificate. After you are creating this go to certmgr.msc(run this command from command propmpt) #> New-SelfSignedCertificate -Type Custom -DnsName REBELCLIENT -KeySpec Signature ` -Subject "CN=CLIENTCERTIFICATE" -KeyExportPolicy Exportable ` -HashAlgorithm sha256 -KeyLength 2048 ` -CertStoreLocation "Cert:\CurrentUser\My" ` -Signer $cert -TextExtension @( "2.5.29.37={text}1.3.6.1.5.5.7.3.2" ) Get-ChildI...

I got this below error while i am working in Merge Publication in sql server. I have configured merge publication as pull publication in the environment,so obviously the merge agent job created under subscriber server, but suddenly it stopping rolling forward changes to the subscriber and throwing the below mentioned error.     If i try to click on the Start button the above error is coming up. The issue came up because some added a new step in the job properties of steps section and deleted that later, afterwards this issue occuring. Workaround: Delete the exising Merge agent job from subscriber server before you are running the below query. --run this in subscriber server under pull subscription exec sp_addmergepullsubscription_agent   @publisher = 'RAMESH' ,--> Publisher server name here   @publisher_db = 'sample' ,-->Publisher database name here   @publication = 'MergePub_Sample' ,-->Publication name her...

To day i got an error in one of my clients environment. One of the SQL Server Agent jobs got failed with the below error message. Msg 2732, Sev 16, State 1, Line 472 : Error number 3013 is invalid. The number must be from 13000 through 2147483647 and it cannot be 50000. [SQLSTATE 42000]  Job 'JobNameHere' : Step 2, 'StepNamehere' : Began Executing 2019-04-25 02:06:07 When i first go through the error, i thought it could be code issue which is written in a particular stored procedure of job. What this job is doing it is restoring the backups from source. So wherever it is restoring in that server in whichever drive the log file is placing(.LDF) , the free space in that particular drive is very less, increasing the log file drive(wherever LDF file is placing) space resolved the issue.

<#You can pass multiple computer names or server names to the computer parameter name. But  dont provide values in between ""#> Invoke-Command  ` -ComputerName   SERVER1 , SERVER2 , SERVER3  ` -ScriptBlock { Get-ClusterGroup | Format-Table   -AutoSize Get-ClusterResource | Where-Object  {( $_ . ResourceType   -like   "*SQL SERVER*" )   -or  ( $_ . ResourceType   -like   "*Generic*" )} | Format-Table   -AutoSize } | Format-Table   -AutoSize

The below Powershell query will extract the folder and sub folder info in the given path. I just mention here folder  names -path showing as "MainFolder" and "SubFolder". In this place you provide your needed folders. -Exclude:   This is a parameter to which we need to pass extension of files, which we are expecting not to show in the result set. Clear Get-ChildItem ` -Path \\MainFolder\Sub\Sub1 ` -Exclude *.dtsx,*.sql*,*.onepkg*,*.docx*,*.doc*,*.log* -Recurse| Where-Object {$_.Name -like "*Full_*"} #Will provide DIFF info #clear Get-ChildItem ` -Path \\MainFolder\Sub\Sub1 ` -Exclude *.dtsx,*.sql*,*.onepkg*,*.docx*,*.doc*,*.log* -Recurse| Where-Object {$_.Name -like "*Diff_*"} # The below query will handle above both the queries. clear Get-ChildItem ` -Path E:\SQLBackups\Backups  ` -Exclude *.dtsx , *.sql* , *.onepkg* , *.docx* , *.doc* , *.log* -Recurse | Where-Object {( $_ . Name -like ...

SQL Server backups are resource intensive operation that strain on I/O and CPU(with backup compression). Always On Availability Group(AOAG) has a mechanism to offload SQL Server Backups and read operations from primary replica to reduce such workloads and utilize secondary replica in a better way. 1. Regular FULL BACKUPS are not allowed from secondary replicas.   But  COPY_ONLY  full backups are allowed on secondary replicas. That means you cannot run regular Full backup  from any of the secondary replica.  Remember that copy-only backups do not impact the log chain or clear the differential bitmap. 2. DIFF BACKUPS(Differential backups) are not supported on secondary replicas . If you need Differential backups for your availability databases don't chose backup preference as SECONDARY ONLY 3. Only regular log backups are supported on secondary replicas. COPY_ONLY log backups are not supported on secondary replicas. 4. Secondary replica must be in S...

Recently i have configured Transactional replication on Always on availability group databases. And i came across below errors . My configuration is like below NODE1-->Orginal Pubisher and Primary replica NODE2-->Secondary replica NODE3-->Distributor server and not part of any replica NODE4--> Subscriber server. I got this error when i am running the below query after remotely login in into NODE1 server which is primary replica server and from there i connected to NODE3 server which is a distributor and i have ran the query and got the below error. So this time i have logged into the NODE3 which is a distributor server remotely and ran the below query and again i got the below error. So again i ran the sp_adddistpublisher command from the distributor server which is NODE3 and i got the below error After that i have observed one thing on the environment, if you connect to the NODE2(Original Secondary replica) right click on replication tab, you w...

sp_validate_replica_hosts_as_publishers (Transact-SQL) The below procedure need to run in THE DISTRIBUTOR server alone, login into DISTRIBUTOR server remotely(mstsc) and run this command. Otherwise this will lead to unnecessary confusion while making the secondary replica is eligible for publisher.

If the services did not come online before you go for REBUILD database try to bring the server online by using the command below using TRACE FLAG. If this did not work  follow the rebuild process. First check with below command. If the above command does not work please add  "-f" parameter which is case sensitive  and try once. If you are rebuilding NAMED INSATNCE of sql server please use provide the server name like below whether you start in single user mode or whether you start normally. If you are  using domain account first image is the use ful image. Sometimes this REBUILD database(above script or immediate image below) will not bring back existing databases back which are there in previously, so we need to address this in a different way. So go through the below links for reference. Whether it is windows authentication or mixed mode authentication providing value to 'sa' account is compulsory. moving system databases MSSQLSERVER_17204 After RE...

The below powershell script brings information about SQL Server Agent jobs and its status from more than one SQL Server instance. CLEAR $HOSTNAME = 'COMPUTERNAMEHERE' Import-Module sqlps -DisableNameChecking $servernames = @( "computername\namedinstancename1" , "computername\namedinstancename2" , "computername\namedinstancename3" , "computername\namedinstancename4" , "computername\namedinstancename5" ) $servernames | ForEach-Object {     $computername = $_     #Get a server object which corresponds to the default instance     $srv = New-Object `     -TypeName 'Microsoft.SQLServer.Management.Smo.Server' `     -ArgumentList $computername     $srv . JobServer . Jobs |     Select-Object OriginatingServer , Name , LastRundate , LastRunOutcome , NextRunDate , CurrentRunstatus |     Where-Object LastRunOutcome -EQ "Failed"...

Import-Module SQLPS -DisableNameChecking $MyComputer = $env:COMPUTERNAME #Provide sql server instance name here $instanceName = $MyComputer + '\TEST' $instanceName #This information will give you all SMO'S of SQL Server $server = New-Object ` -TypeName Microsoft.SQLServer.Management.Smo.Server ` -ArgumentList $instanceName $server | Get-Member -MemberType "Property" | Where-Object Definition -like "*SMO*"  The below Powershell query will provide us the information about SQL Server Assemblies  clear [ appdomain ]:: CurrentDomain . GetAssemblies() | Where { $_ . FullName -match "SQLServer" } | Select FullName 

The below query will tell you which SQL Server Agent Operator is internally calling which mail id's. These operators we can assign to every sql server agent job. So whenever these jobs gets failed it will route to those mail id's USE msdb  GO   EXEC dbo.sp_help_operator       @operator_name = N'Provide Operator name here'; 

SELECT  j . [name]  AS   'JobName' , Enabled   =   CASE   WHEN  j . Enabled   =  0  THEN   'No' ELSE   'Yes' END , l . [name]  AS   'OwnerName' FROM  MSDB . dbo . sysjobs j INNER   JOIN   Master . dbo . syslogins  l ON  j . owner_sid  =  l . sid ORDER   BY  j . [name]

USE msdb; GO -- Disable Multiple SQL Jobs at once: DECLARE @dynSql NVARCHAR(MAX) = '' SELECT @dynSql += N'exec msdb.dbo.sp_update_job @job_name = ''' + name + N''', @enabled = 0;' + CHAR(10) + CHAR(13) FROM msdb.dbo.sysjobs WHERE enabled = 1 ORDER BY name; PRINT @dynSql;

Recently i have installed SQL SERVER 2016 RTM version on my machine and started working on it. One day  i have configured Mirroring and found the below error. The error showing as "Database sample is in the middle of restore".  First i though i am doing wrong. Later i realized that this is bug with Microsoft SQL Server Management Studio 18. Try the same steps with SQL Server Management Studio 2014, this works perfectly and you are able to configure database successfully

---To Start MongoDB service run below command in command prompt with Admin privileges "C:\Program Files\MongoDB\Server\4.0\bin\mongod.exe"  ---To connect to the MongoDB environment run below command n command prompt with Admin privileges "C:\Program Files\MongoDB\Server\4.0\bin\mongo.exe" If you see below first i ran the command prompt with Administrator account but i have installed mongodb with 'Hi' account . So first i changed the user from Administrator account to Hi account by using CD Going inside of mongodb, open another command prompt and run the below command

Recently in one my Always On Availability replica environment I got an issue where one of the availability replica database log file has grown completely, and we got a request to shrink the issue. When we run the below command we found that “log_reuse_wait_desc” showing as “Availability_Replica What this means is this indicates that the logged changes in the availability database at the primary replica that have not arrived and that these changes were applied to the availability database at one of the secondary replicas. Until logged changes arrive and are applied, the changes cannot be truncated from the availability database log at the primary replica. Troubleshooting: "Log Send Queue" and "Redo Queue" are measurable data points during availability database synchronization. You can monitor these data points in order to determine whether an availability database log cannot be truncated because of the log uses type AVAILABILITY_REPLICA. ·     Log Send...