High on SQL!!!

Jan 01
### Why would i think about DevOps???

!!Automate Operations for the Development process!! As we know 100% is not possible but again it is possible  to automate your daily Server Administrative tasks. We should create a better Sustained Engineering plan to gradually automate some or even more operations or Administration.

Okay...Do not migrate all your operations at once.  I would suggest automate some operations and when you think that the operation is stable, then move to the next phase....

I do manage my Server Administration fully automated way and YES without GUI and all i use SQLCMD, PowerShell and Windows Batch file...

 Let's share what i do and what you do in your environment....

  • Database creation/clonning, multiple database Refresh/Restore/Backup and database migration these are fully automated using custom Stored Procedure, SQLCMD

 

  • Download SQL .bak file from FTP Server and Restore onto the Databse Server using automated WINSCP command!!!

 

  • Deploy code on multiple databses and multiple servers using SQLCMD

 

  • Logging all database deployment process into a central database table using SQLCMD where it keeps all records like who deployed code, who took the backup and who restore the database and when and so many

 

  • Creat/Alter/Drop SQL Logins and alter SQL Login's password multiple servers just a one click

 

  • Install SQL Server in a silent mode, install latest service pack using CMD, add SQL port using powershel and reboot the server using cmd command line and YES ALL THESE ARE WRAPED UP in a single BATCH FILE JUST YOU NEED a CLICK and grabe a coffee!!! CUZ IF DONT LIKE GUI LIKE NEXT NEXT NEXT!!!

 

  • What about Post Configuration??? YES I RUN a PS Script that call 17 SQL Scripts such as Backup job, Index Job, memory settings, Database Mail configuration, Default Alets etc etc .....which will take care your instance ready to go for LIVE Porduction!!!

 

  • Bulk data load into the warehouse databse using BCP CMD utility....

 

 

For more details please contact me

 

Cheers,

Zahid

Sep 13
### Create Non-Cluster Index on multiple columns

There was a requiremnet to create Non-Cluster Index on multiple  columns in 200 tables!!! Crazyyyy!!! Isn't it????

!!! Even 1000 (One Thousands) tables i don't care !!!

Can we automate this process dynmically using SQLCMD &  T-SQL?

***Yes---We Can***

SELECT ' CREATE NONCLUSTERED INDEX [ NIDX_' + SYSCOLUMNS.NAME + '] ON ' + SYSOBJECTS.NAME + .............................GO

---To get the complete code please Contact Me

Cheers,

Zahid

 

 

 

Sep 11
### Convert file type using CMD

Hi All,

I had a request where there were more than a thousands dat file to imported to the SQL Server. First, i convert those dat files to txt files. Here is the following command :--

rename *.* *.txt

Thanks

Zahid

Aug 19
### Error Handling in the SQL Code

​I would suggest developer for coding in couple scenario in order to capture actual error while script deployment especially when we execute a query that modifies portion of data and fails. This requires redoing their works to clean it up when this happens. Also, it potentially breaks data integrity that damages the databases. In order to avoid that I always suggest developers to wrap up their SQL code using template like—

 

BEGIN TRY

BEGIN TRAN

Add SQL Logic here

COMMIT TRAN

BEGIN CATCH

ROLLBACK TRAN

SELECT ERROR_LINE () AS LINE

,ERROR_NUMBER() AS ERROR_NUMBER

,ERROR_MESSAGE() AS MESSAGE

END CATCH

 

Thanks ,

Zahid

 

Aug 19
### Virtualmin on Linux Server

Install Virtualmin, Nginx 1.10, PHP 7, MariaDB 10 on Linux Server

Virtualmin is a powerful and flexible web hosting control panel for Linux and BSD systems. Virtualmin is the cost-effective and comprehensive solution to virtual web hosting management.

If you want to build your web-site and host your mail server please Contact Me

Zahid

Aug 17
### Data import using PowerShell

#let’s clean up the Test.Person table first

Import-Module SQLPS -DisableNameChecking

Truncate-Table $instanceName $dbName

$server = “localhost”

$table = “AdventureWorks2014.Test.Person”

$importfile = “C:\Temp\Exports\Person.Person.csv”

#command to import from csv

$cmdimport = “bcp $($table) in `”$($importfile)`” -S$server -T -c -t `”|`” -r `”\n`” ”

#run the import command

Invoke-Expression $cmdimport

#delay 1 sec, give server some time to import records

#sleep helps us avoid race conditions

Start-Sleep -s 2

Get-PersonCount $instanceName $dbName

 

Jul 18
Welcome to Zahid's SQL Blog!

This is where I'll be sharing my thoughts on SQL topics that matter to me. Who knows... I might even share documentaion, videos and links to other interesting stuff.

If I catch your interest, let me hear from you...

 

Zahid

         About this blog                                                           

My Dear TechyFrndZ,

Welcome to the lunch www.zclouddata.com and imporved web based application and my first blog post High on SQL!!!

I am so excited to share with you MORE of what oderly has to offer! The amount of ideas, how-to's and inspiration whirling around in my head is ready to jump out for critical problem solutions like Microsoft SharePoint Server, SQL Server, Windows Server and Linux Server

I wanted to start by telling you why i created this blog what you will be seeing more of in my High on SQL posts, and my overall experience of cloud based and on-premise SQL Database Administration.

 

Cheers,

Zahid Ahamed (Sagar)