Hi, My name is Jimmy Sam, If you need DBA help, please email to jimmy_sam001@yahoo.com for further discussion. Thanks.
mssql  


top   prev   next  

PaaS SaaS DBaaS IaaS

Cloud Service Models:
IAAS: Infrastructure as a Service  ( Microsoft Azure ): can select OS, middleware, run time

PAAS: Platform as a Service  ( Microsoft SQL Azure ): can select data and applicationa
SAAS: Software as a Service: like outlook and servicenow, just use it through http or https
DBaaS: Database As A Service: provide specific database ( db vendor or db type )

Azure SQL Database is a managed database platform as a service (Paas) 




top   prev   next  

Agile Methodology

Waterfall vs. Agile ( Iteractive, continuous, heavily used in DevOPS ): 

Processes: Agile, Scrum, CMMI
Scrum and Kanban are two of the most widely used Agile methodologies.

Scrum: 
strictly assigned time and person for each task ( Scrum Master )

Kanban: 
Task time limit and assigned person are not very strict, 
only ensure go to next step only after current step completed.

1):
Scrum process framework requires the use of development cycles called Sprints, 


2):
A sprint is a set period of time during which specific work has to be completed 
and made ready for review.



Azure URL Name Conventions

-- portal.azure.com
http://portal.azure.com
-- create azure sql database through portal

https://manage.windowsazure.com

-- Azure DB Server Naming Convention
mydatabaseserverid.database.windows.net:1433
eg: gardening.database.windows.net,1433
-- select @@VERSION;
-- check server property: SELECT SERVERPROPERTY(N'Collation');

-- Azure Storage, Azure Blob storage, Microsoft BLOB storage account
http://myaccount.blob.core.windows.net/mycontainer/myblobname
eg 1: BACKUP DATABASE AdventureWorks2016 TO URL = 'https://.blob.core.windows.net//AdventureWorks2016.bak;

eg 2: BACKUP DATABASE AdventureWorks2016
TO URL = 'https://.blob.core.windows.net//AdventureWorks2016.bak'
WITH FILE_SNAPSHOT;

-- report server URL
http://myvmname.cloudapp.net/reportserver
http://myvmname.cloudapp.net/reports

-- ADF: Azure Data Factory
datafactory.azure.com

-- DevOps
https://dev.azure.com
https://dev.azure.com/myid/project01

-- Setup Firewall Rules
1): IP firewall rules
2): Virtual network firewall rules
-- to change firewall rules: sp_set_firewall_rule

-- Azure web cloud
winecloudweb.azurewebsites.net




Azure Cloud Shell ( bash or powershell)

azure cloud shell: powershell in cloud

azure portal -- Cloud Shell -- Bash or Powershell

1): powershell
PS Azure:\> get-command -module PSCloudShellUtility

CommandType     Name                                               Version    Source
-----------     ----                                               -------    ------
Function        Disable-AzVMPSRemoting                             0.9.3      PSCloudShellUtility
Function        Dismount-CloudDrive                                0.9.3      PSCloudShellUtility
Function        Enable-AzVMPSRemoting                              0.9.3      PSCloudShellUtility
Function        Enter-AzVM                                         0.9.3      PSCloudShellUtility
Function        Export-File                                        0.9.3      PSCloudShellUtility
Function        Get-AzCommand                                      0.9.3      PSCloudShellUtility
Function        Get-CloudDrive                                     0.9.3      PSCloudShellUtility
Function        Get-CloudShellTip                                  0.9.3      PSCloudShellUtility
Function        Get-Help                                           0.9.3      PSCloudShellUtility
Function        Invoke-AzVMCommand                                 0.9.3      PSCloudShellUtility

2): bash
Type "az" to use Azure CLI
Type "help" to learn about Cloud Shell

-- ------------------------
The following az command works in both bash and powershell

az --version

get-module -ListAvailable

get-command -module AZ*

Get-AzVM 

get-help get-azvm
help get-azvm

Get-PSDrive

cd Azure

cd $home

azure cli

az -version
az webapp list
az webapp list --output table
az login
az account set --subscription 
az group list --output table
az group create --name myResourceGroup --location eastus



Azure SQL Database Types

Azure Database Service Tiers
basic
standard
Premium

Azure SQL Database: Single database
Azure SQL Managed Instance

-- DTUs
http://dtucalculator.azurewebsites.net

DTU vs. vCore 


DTUs: 
a blended measure of CPU, memory, and data I/O and transaction log I/ 
( Database Transaction Unit,  similar to CPUs )

-- roughly 10 Reads/sec = 1 DTU

Reads/sec
Writes/sec 	DTUs 	Service Tier
2000 	        250 	Premium P2
4000-6000 	500 	Premium P4
8000-12000 	1000 	Premium P6
14000-22000 	1750 	Premium P11
24000-30000 	4000 	Premium P15

-- ---------------------------------------

roughly 1MB flushed/sec = 50 DTUs

Log 
Megabytes 
flushed
/sec 	DTUs 	Service Tier
5 	250 	Premium P2
10 	500 	Premium P4
15-25 	1000 	Premium P6
30-40 	1750 	Premium P11
45-75 	4000 	Premium P15



Azure SQL Database Limitations

azure sql server database:
1. don't have server option, replication, alwayson option
2. cannot create linked server on azure sql database
   but can create an odbc data source on on-premise DB server, then create linked server on on-premise db. 
3. don't have agent option ( but can use Azure automation: run powershell to the Azure sql server database )


Azure SQL Database Users and Related Security

When create an Azure sql databases
1): Need to fill out or select a): subscription information b): Resource group information 

2): there is no sa account, but it will create a server level principal account, similar to sa account

3): must set firewall rules at server level or database level, otherwise outsiders will not be able to access
-- at server level
use master
go
select * from sys.firewall_rules;
go

exec sp_set_firewall_rule @name=N'MyFirewallRule1',
   @Start_ip_address = '192.168.100.10',
   @end_ip_address = '192.168.100.10'

exec sp_delete_firewall_rule @name=N'MyFirewallRule1'

-- at database level
select * from sys.database_firewall_rules;
go

exec sp_set_database_firewall_rule @name=N'MyDBFirewallRule1'
   @Start_ip_address = '192.168.100.20',
   @end_ip_address = '192.168.100.20'
go

/* master */

CREATE LOGIN WineCloudDbLogin WITH PASSWORD='p@ssw0rd'
GO
--
CREATE USER WineCloudDbUser FROM LOGIN WineCloudDbLogin
GO
EXEC sp_addrolemember 'loginmanager', 'WineCloudDbUser'
EXEC sp_addrolemember 'dbmanager', 'WineCloudDbUser'
GO

--
loginmanager: permission to create login user at server level
dbmanager: permission to create database at server level
--
CREATE LOGIN WineCloudDbReadonlyLogin WITH PASSWORD='p@ssw0rd'
GO

/* WineCloudDb */

CREATE USER WineCloudDbReadonlyUser FROM LOGIN WineCloudDbReadonlyLogin
GO
EXEC sp_addrolemember db_datareader, WineCloudDbReadonlyUser
GO

GRANT SELECT ON SCHEMA::Test to ApplicationUser

SELECT r.name role_principal_name, m.name AS member_principal_name
FROM sys.database_role_members rm
JOIN sys.database_principals r
ON rm.role_principal_id = r.principal_id
JOIN sys.database_principals m
ON rm.member_principal_id = m.principal_id
WHERE r.name IN ('loginmanager', 'dbmanager');

-- Contained Users:
USE MYDB
create user MyUser1 with password = 'Password123'
ALTER AUTHORIZATION ON SCHEMA::[db_owner] to [MyUser1];
ALTER ROLE [db_owner] ADD MEMBER [MyUser1];

option 3): create active directory login

create user [myusername@xxx.onmicrosoft.com] from external provider;


-- tuning DMVs

sys.dm_exec_cached_plans
sys.dm_exec_query_plan
sys.dm_exec_query_stats

sys.dm_os_performance_counters

SELECT * FROM sys.dm_os_schedulers;

SELECT * FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE';

-- restrictions

-- cannot make cross database calls in sql azure unless you are using elastic pool;
-- even if you are using elastic pool, you still cannot make cross database calls to master database
-- cannot use "use master" or "use mydb"

-- cannot use "sql profiler" in sql azure database
-- use querystore to mimic ( cached sql )


Querying Azure SQL Database

SELECT * FROM [sys].[database_firewall_rules];

select @@version;

select @@servername;


select database_id, db_name(database_id) db_name, 
edition, service_objective,elastic_pool_name
from sys.database_service_objectives;

--Edition, Standard, Business

-- ALTER DATABASE MyDb MODIFY (EDITION='business', MAXSIZE=10GB);

1): Azure Database Service Tiers
basic
standard
Premium

-- --------------------------

-- ALTER DATABASE [db1] MODIFY  (EDITION = 'Premium', MAXSIZE = 1024 GB, SERVICE_OBJECTIVE = 'P15');

-- ALTER DATABASE [DBName] MODIFY(EDITION='Standard', SERVICE_OBJECTIVE='S2');

SELECT Edition_Or_Tier = DATABASEPROPERTYEX('Garden', 'Edition');

Select ServiceObjective_ComputeGen = DATABASEPROPERTYEX('Garden', 'ServiceObjective');

SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS Collation;
-- SQL_Latin1_General_CP1_CI_AS

SELECT * FROM sys.fn_helpcollations();

SELECT  d.name,
s.database_id,
s.edition,
s.service_objective,
(CASE WHEN s.elastic_pool_name  IS NULL
  THEN 'No Elastic Pool used'
  ELSE s.elastic_pool_name
  END) AS [Elastic Pool details]
FROM sys.databases d JOIN sys.database_service_objectives s ON d.database_id = s.database_id;

SELECT * FROM sys.dm_operation_status ORDER BY start_time DESC;

-- Rename a database:
-- ALTER DATABASE  mydb1 Modify Name = mydb2 ;

-- ---------------------------------------------------------------------


select * FROM sys.databases;

select schema_name(schema_id) schema_name,
 a.* FROM sys.objects a;

select * FROM sys.objects WHERE schema_id = SCHEMA_ID('sys');

select name, type_desc from sys.objects 
WHERE type in ( 'C', 'D', 'F', 'L', 'P', 'PK', 'RF', 'TR', 'UQ', 'V', 'X' ) 
union
select name, type_desc from sys.indexes
order by name;

SELECT DISTINCT
       o.name AS Object_Name,
       o.type_desc
  FROM sys.sql_modules m
       INNER JOIN
       sys.objects o
         ON m.object_id = o.object_id
 --WHERE  '.' + m.definition + '.' LIKE '%[^a-z]employeeid[^a-z]%'
 order by type_desc, object_name;

SELECT (SUM(reserved_page_count) * 8192) / 1024 / 1024 AS DbSizeInMB FROM sys.dm_db_partition_stats;





top   prev   next  

Export/Import or Backup/Restore


In Azure Portal -- Select Database -- Click "Export" --- (Enter your storage account information )

SQL Data-Tier Applications (DACs) : a single DAC package (a .dacpac file)

SQL Data-Tier Applications (BACPAC): DACPAC with data
BACPAC is similar to DACPAC, 
but in addition to the database objects (schema),
it also includes the actual data from the database in the package.

Also in SQL Server Management Studio:
SSMS -- Export Datatier Application
SSMS -- Import Datatier Application

SQL Server Bulk Copy (bcp): bulk copy (bcp)

-- -----------------------

REM bcp export:
bcp MyDb.dbo.MyTableName out MyTableName.dat -S .\mysqlserverhost -T -n -q

-T: Windows Authentication
-U sql_login -P sql_password
-n : between sql servers
-q : Allow to use database name, owner, table name, 
     view name contains a space or Single quoation mark
    Similar to SET QUOTED_IDENTIFIERS ON

-- -----------------------
REM bcp import:
bcp myclouddb.dbo.MyTableName in MyTableName.dat `
-S tcp:mycloudservername.database.windows.net `
-U login_id@myserver -P my_password -n

-b batch_size : number of rows to process
-F first_row in the batch file
-L last_row in the batch ile
-h hints  ( eg: -h "TABLOCK" )


Powershell For AzureDevOPS

-- The newst module called az
--  Install-Moduel Az

Get-Command -Module Az*

-- the old powershell module is called: AzureRM, 
# AzureRM: Azure Resource Manager Module AzureRM 6.13.1 

# To check which version(s) of AzureRM you have installed 
Get-InstalledModule -Name AzureRM -AllVersions

# Enable AzureRM compatibility aliases
Enable-AzureRmAlias -Scope CurrentUser

# Disable AzureRM compatibility aliases
Disable-AzureRmAlias

# To login to Azure Resource Manager
Login-AzureRmAccount



Powershell For Azure SQL Databases

-- powershell
https://www.windowsazure.com/en-us/downloads

-- ----------------------------------------
Get-Module -ListAvailable

Import-Module Azure

$env:PSModulePath

Get-AzurePublishSettingsFile

Import-AzurePublishSettingsFile <.publishsettings file>

-- Create New Server
New-AzureSqlDatabaseServer -Location "East US" -AdministratorLogin "" -AdministratorLoginPassword ""

-- setup firewall rule
New-AzureSqlDatabaseServerFirewallRule -ServerName  -RuleName  -StartIpAddress  -EndIpAddress

-- Create New Database
$creds = new-object System.Management.Automation.PSCredential ("", ("" | ConvertTo-SecureString -asPlainText -Force))
$context = New-AzureSqlDatabaseServerContext -ServerName  -Credential $creds

1):
--  -Edition, -MaxSizeGb, and -Collation
New-AzureSqlDatabase -Context $context -DatabaseName MyNewDb

2):
New-AzureSqlDatabase -Context $context -DatabaseName MyBigDb -Edition Business -MaxSizeGB 150

2a):
New-AzSqlServer -ResourceGroupName $resourcegroupname `
    -ServerName $servername `
    -Location $location `
    -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminlogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))

3): or change database edition
Set-AzureSqlDatabase -Context $context -DatabaseName MyNewDb -Edition Business -MaxSizeGB 20

4): delete database 
Remove-AzureSqlDatabase -Context $context -DatabaseName MyNewDb

5):
-- List all databases available on the server
Get-AzureSqlDatabase -Context $context
$database = Get-AzSqlDatabase -ResourceGroupName myResourceGroup -ServerName mynewserver-20171113 -DatabaseName mySampleDataWarehouse
$database | Select-Object DatabaseName,Status

6):
Suspend-AzSqlDatabase -ResourceGroupName "myResourceGroup" `
-ServerName "newserver-20171113" -DatabaseName "mySampleDataWarehouse"

-- 
SELECT (SUM(reserved_page_count) * 8192) / 1024 / 1024 AS DbSizeInMB FROM sys.dm_db_partition_stats

6): suspend all databases, output a list

$database = Get-AzSqlDatabase -ResourceGroupName "myResourceGroup" `
-ServerName "newserver-20171113" -DatabaseName "mySampleDataWarehouse"
$resultDatabase = $database | Suspend-AzSqlDatabase
$resultDatabase

7): resume a database
Resume-AzSqlDatabase -ResourceGroupName "myResourceGroup" `
-ServerName "newserver-20171113" -DatabaseName "mySampleDataWarehouse"

8): resume all database

$database = Get-AzSqlDatabase -ResourceGroupName "ResourceGroup1" `
-ServerName "Server01" -DatabaseName "Database02"

$resultDatabase = $database | Resume-AzSqlDatabase

$resultDatabase




top   prev   next  

Azure DEVOPS

1): Project Management: requirement, tasks, bugs, progress and reporting

2): Source Control: 1): Change history 2): team development environment

3): Build and Deployment: Managing release cycles


Azure DevOps Tutorial For Beginners | Azure DevOps CI/CD Pipeline | Edureka
https://www.youtube.com/watch?v=MOZMw5_fBFA


Azure Pipeline Tutorial | Azure Pipeline Deployment | Azure DevOps Tutorial 
Azure DevOps  ( continuous deploy, integrate ) ( Developers and Operators )

Azure Pipeline  ( continuous deploy, integrate )

devops:  https://dev.azure.com

combine: project (progress), itsm, service now, source control, code promotion

Azure DevOps:Developers, Operators
components:
1. Azure Boards
2. Azure Artefacts (Artifacts)
3. Azure Pipelines ( YAML or .yml file: configuration )
4. Azure Repos
5. Azure Test plans
-- -----------------------------------

dev.azure.com --- dev ops

https://dev.azure.com/myid/project01
https://dev.azure.com

project

overview

summary

Dashboards

Wiki

Boards: Project Management: 1): Documenting requitements/tasks and bugs 2): Monitoring progress and Reporting

Backlog: Work needs to be done, a backlog is a collection of work items which will be used for furture development
         A list of set tasks that must be completed before the product is released.

Product backlog: Product backlog is an ordered list of everything that is known to be needed in the product.

Sprint backlog: Sprint backlog is the collection of work items which are in (delayed?) state
                this list finalizes and defines what the development team will complete during the sprint.

Repos: Repository

Piplelines: 
Azure Pipelines can deploy artifacts that are produced 
by a wide range of artifact sources, 
and stored in different types of artifact repositories.
https://docs.microsoft.com/en-us/azure/devops/pipelines/release/artifacts?view=azure-devops

Test Plans


Artifacts: An artifact (Artefact ) is a deployable component of your application, it has various code types
https://azure.microsoft.com/en-us/services/devops/artifacts/

https://www.azuredevopslabs.com/labs/azuredevops/packagemanagement/

-- -----------------------------------

Deploying Databases using Azure DevOps Pipelines
DACPAC and BACPAC
MERGE STATEMENT
STORED PROCEDURE
-- Confirm Your Script Is Pre- Or Post-Deploy
https://www.danylkoweb.com/Blog/deploying-databases-using-azure-devops-pipelines-NQ

https://dzone.com/articles/deploying-databases-using-azure-devops-pipelines

Continuous Integration and Deployment for SQL Database using SSDT and VSTS
https://mohitgoyal.co/2018/01/10/continuous-integration-and-deployment-for-sql-database-using-ssdt/

-- --------
Azure Pipelines | Azure DevOps Server 2019 | TFS 2018 | TFS 2017
https://docs.microsoft.com/en-us/azure/devops/pipelines/targets/azure-sqldb?view=azure-devops&tabs=yaml


eg 1:
- task: SqlAzureDacpacDeployment@1
  displayName: Execute Azure SQL : DacpacTask
  inputs:
    azureSubscription: ''
    ServerName: ''
    DatabaseName: ''
    SqlUsername: ''
    SqlPassword: ''
    DacpacFile: ''
-- --------------------------

eg 2:
variables:
  AzureSubscription: ''
  ServerName: ''
  DatabaseName: ''
  AdminUser: ''
  AdminPassword: ''
  SQLFile: ''

steps:
- task: AzurePowerShell@2
  displayName: Azure PowerShell script: FilePath
  inputs:
    azureSubscription: '$(AzureSubscription)'
    ScriptPath: '$(Build.SourcesDirectory)\scripts\SetAzureFirewallRule.ps1'
    ScriptArguments: '$(ServerName)'
    azurePowerShellVersion: LatestVersion

- task: CmdLine@1
  displayName: Run Sqlcmd
  inputs:
    filename: Sqlcmd
    arguments: '-S $(ServerName) -U $(AdminUser) -P $(AdminPassword) -d $(DatabaseName) -i $(SQLFile)'

- task: AzurePowerShell@2
  displayName: Azure PowerShell script: FilePath
  inputs:
    azureSubscription: '$(AzureSubscription)'
    ScriptPath: '$(Build.SourcesDirectory)\scripts\RemoveAzureFirewallRule.ps1'
    ScriptArguments: '$(ServerName)'
    azurePowerShellVersion: LatestVersion

-- --------------------------------
sqlpackage.exe /Action:Script /?

-- ---------------------------------

DevOps using SQL Server ( Architecture )
Build
CI/CD Integration
Container-based Deployment
https://www.microsoft.com/en-us/sql-server/developer-get-started/sql-devops/




top   prev   next  

Docker

www.docker.com

https://docs.docker.com/engine/tutorials/dockervolumes/

Docker in Two Hours 

Image: A definition. Defines what software is included and how it runs

Container: A running instance based on the image

docker pull: download an image from a Docker repository
eg: docker pull db-prod:latest

docker run: create a container from an image
eg: docker run -name db-prod-v2 db-prod:lastest

docker ps: list all locally running containers

docker images: list all locally cached images

docker stop db-prod-v1

-- ---------------------------------

a Jenkins project



top   prev   next  

git and github


git and github

which git

-- ---------------------

RStudio

-- ---------------------

rpm -q git

# to install git,

1):
#yum install curl-devel expat-devel gettext-devel \
#  openssl-devel zlib-devel perl-devel asciidoc xmlto

2):
$ tar -zxf git-1.7.2.2.tar.gz
$ cd git-1.7.2.2
$ make prefix=/usr/local all
$ sudo make prefix=/usr/local install

# git clone git://git.kernel.org/pub/scm/git/git.git
# yum install git

-- ---------------------------------------------

$EDITOR 
export EDITOR=vim
export EDITOR=emacs

/etc/gitconfig
~/.gitconfig

cat .gitignore

git config --global core.editor

git config --list
git config user.name

git config --global user.name "Roger Smith"
git config --global user.email "rsmith@abc.com"

git help config

# git help 
# git  --help
# man git-

# git diff --cached

# git help config

# git status

# git log
# git log --pretty=oneline

# git log --pretty=format:"%h - %an, %ar : %s"

# git log -p -2
# git log -U1 --word-diff

# git log --stat

# initializing

# git init

# git add *.c
# git add README

# git rm README
# git rm --cached readme.txt

# rename a file
# git mv README README.txt

# git commit -m 'initial project version'
# notes: -m: the -m flag means message

# git clone git://github.com/schacon/grit.git
# git clone git://github.com/schacon/grit.git mygrit

# git commit

# git commit --amend
# to commit again

# git last
# to see the last commit

# git diff
# See the difference between staged uncomitted changes and the most recent commit
$ diff --git a/benchmarks.rb b/benchmarks.rb

# git checkout -- benchmarks.rb

# git tag

# git show v1.4

# --------------------------

# git alias
# git config --global alias.co checkout
# git config --global alias.br branch
# git config --global alias.ci commit
# git config --global alias.st status

# ---------------------------

# git branch
# git branch -v
# git branch -a

# git checkout -b patch1 
# Create a branch called "patch1" from the current branch and switch to it.

# git branch testing
# to create a branch

# git checkout testing

# ---------------------------

# git fetch

# --------------------------

git stash:  	Save uncommitted changes in a temporary version and revert to the most recent commit


Home  
top