SQL Server Migration

https://console.aws.amazon.com/cloudformation/home?region=us-east-1#/stacks?filteringText=&filteringStatus=active&viewNested=true&hideStacks=false

Remote Desktop Host: <EC2HostName>  

Username: developer
Passowrd: <EC2DeveloperPassword>

Server Name: localhost  
Server port: <SourceSQLServerPort>  
Instance name: <leave this blank>
Authentication: SQL Server Authentication
Username: <SourceSQLServerUser>  
Password: <SourceSQLServerPassword>  
DriverPath: C:\DMS Workshop\JDBC\sqljdbc_7.4\enu\mssql-jdbc-7.4.1.jre8.jar

Servername: <RedshiftClusterEndpoint>
Port: <RedshiftClusterPort>
Database: <RedshiftClusterDatabase>
Username: <RedshiftClusterUser>
Password: <RedshiftClusterPassword>

Driver: Amazon Redshift
URL: jdbc:redshift://<RedshiftClusterEndpoint>:<RedshiftClusterPort>/<RedshiftClusterDatabase>
Username: <RedshiftClusterUser>
Password: <RedshiftClusterPassword>
Autocommit: tick mark

select t.table_schema, t.table_name,i.tbl_rows
from svv_tables t left join svv_table_info i
on t.table_schema = i.schema and t.table_name = i.table
where t.table_type = 'BASE TABLE' and t.table_schema='dms_sample_dbo'
order by 1,2

Server type: Database Engine  
Server name: localhost  
Authentication: Windows Authentication  

use dms_sample;

SELECT SCHEMA_NAME(t.[schema_id]) AS [table_schema],
OBJECT_NAME(p.[object_id]) AS [table_name],SUM(p.[rows]) AS [row_count]
FROM [sys].[partitions] p
INNER JOIN [sys].[tables] t ON p.[object_id] = t.[object_id]
WHERE p.[index_id] < 2
GROUP BY p.[object_id],t.[schema_id]
ORDER BY 3

select t.table_schema, t.table_name,i.tbl_rows
from svv_tables t left join svv_table_info i
on t.table_schema = i.schema and t.table_name = i.table
where t.table_type = 'BASE TABLE' and t.table_schema='dms_sample_dbo'
order by 1,2

select * from dms_sample_dbo.sport_type order by 1;

use dms_sample;
select * from dms_sample.dbo.sport_type order by 1;

use dms_sample;

insert into dbo.sport_type(name,description)
values
('hockey','A sport in which two teams play against each other by trying to more a puck into the opponents goal using a hockey stick'),
('basketball','A sport in which two teams of five players each that oppose one another shoot a basketball through the defenders hoop'),
('soccer','A sport played with a spherical ball between two teams of eleven players'),
('volleyball','two teams of six players are separated by a net and each team tries to score by grounding a ball on the others court'),
('cricket','A bat-and-ball game between two teams of eleven players on a field with a wicket at each end');

use dms_sample;
select * from dms_sample.dbo.sport_type order by 1;

select * from dms_sample_dbo.sport_type order by 1;

drop table if exists dms_sample_dbo.person cascade;
drop table if exists dms_sample_dbo.ticket_purchase_hist cascade;
drop table if exists dms_sample_dbo.sporting_event_ticket cascade;
drop table if exists dms_sample_dbo.sporting_event cascade;
drop table if exists dms_sample_dbo.seat cascade;
drop table if exists dms_sample_dbo.sport_type cascade;
drop table if exists dms_sample_dbo.sport_location cascade;
drop table if exists dms_sample_dbo.sport_team cascade;
drop table if exists dms_sample_dbo.sport_league cascade;
drop table if exists dms_sample_dbo.sport_division cascade;
drop table if exists dms_sample_dbo.seat_type cascade;
drop table if exists dms_sample_dbo.player cascade;
drop table if exists dms_sample_dbo.name_data cascade;
drop table if exists dms_sample_dbo.nfl_stadium_data cascade;
drop table if exists dms_sample_dbo.nfl_data cascade;
drop table if exists dms_sample_dbo.mlb_data cascade;

delete from dms_sample_dbo.sport_type where name in ('hockey', 'basketball', 'soccer', 'volleyball', 'cricket');