!!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
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?
SELECT ' CREATE NONCLUSTERED INDEX [ NIDX_' + SYSCOLUMNS.NAME + '] ON ' + SYSOBJECTS.NAME + .............................GO
---To get the complete code please Contact Me
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
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—
Add SQL Logic here
SELECT ERROR_LINE () AS LINE
,ERROR_NUMBER() AS ERROR_NUMBER
,ERROR_MESSAGE() AS MESSAGE
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
#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
#delay 1 sec, give server some time to import records
#sleep helps us avoid race conditions
Start-Sleep -s 2
Get-PersonCount $instanceName $dbName
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...
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.
Zahid Ahamed (Sagar)