Review of the SQL file system

databases case study and need a sample draft to help me learn.

Database Systems
COMP1225
Portfolio 1 – SQL Server Review
Overview
Now we are going to explore a few parts of the SQL file system.This will require some reading and research on your part, so allow yourself sufficient time.
Be very careful read the messages on the screen and choose the right options.
Document this fully in your lab book. Screen captures are helpful to explain, especially if you highlight the key point in the screen shot and provide a description that indicates what is important in the particular screen shot. This is also useful for rebuilds and trouble shooting.
PLEASE NOTE: While I provide detailed SQL coding, it may not necessarily reflect your specific installation. You MUSTread the provided code completely, understand it and modify it to reflect your environment. This of course must be documented in your lab book.
Steps
1. We are going to create a new file group
–Need to add a filegroup first
ALTER DATABASE AdventureWorks2012
ADD FILEGROUP ReadOnlyFG1;
GO
— Add a file to the Filegroup
ALTER DATABASE AdventureWorks2012
ADD FILE
( NAME = AdventureWorks2012_ReadOnlyData,
FILENAME = ‘C:\SQLdata\AdventureWorks2012_ReadOnlyData.ndf’,
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB) TO FILEGROUP ReadOnlyFG1;
Go
2. Look at the windows explorer view of the new database file. How much space is it taking up? Why? Where is the new log file?
3. And now add a table to the new filegroup. Then we’ll add some data to this new table
–Create a table on the filegroup
CREATE TABLE AdventureWorks2012.dbo.MyReadOnlyTable
( FirstName varchar(50),
LastName varchar(50),
EMailAddress char(1000) )
ON ReadOnlyFG1;
–Insert some data into the new Filegroup
insert AdventureWorks2012.dbo.MyReadOnlyTable
select LastName, FirstName, ‘xxx’
from AdventureWorks2012.person. person;
4. Look at the windows explorer view of the new database file again. How much space is it taking up? Why? Where did the data go?
5. Now let’s make this filegroup read only.
–Make the filegroup readonly
ALTER DATABASE [AdventureWorks2012] MODIFY FILEGROUP [ReadOnlyFG1] READONLY;
6. Look at the windows explorer view of the new database file again What changes happened to the files?
7. Read about Partitioning tables online in MS SQL Server documentation (https://docs.microsoft.com/en-us/sql/relational-da…). In your own words what is the use of partitioning
8. Now let’s explore it. We are going to create a few new file groups
USE AdventureWorks2012;
GO
— Adds four new filegroups to the AdventureWorks2012 database
ALTER DATABASE AdventureWorks2012
ADD FILEGROUP test1fg;
GO
ALTER DATABASE AdventureWorks2012
ADD FILEGROUP test2fg;
GO
ALTER DATABASE AdventureWorks2012
ADD FILEGROUP test3fg;
GO
ALTER DATABASE AdventureWorks2012
ADD FILEGROUP test4fg;
— Adds one file for each filegroup.
ALTER DATABASE AdventureWorks2012
ADD FILE
(
NAME = test1dat1,
FILENAME = ‘C:\DATA\t1dat1.ndf’,
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)
TO FILEGROUP test1fg;
ALTER DATABASE AdventureWorks2012
ADD FILE
(
NAME = test2dat2,
FILENAME = ‘C:\DATA\t2dat2.ndf’,
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)
TO FILEGROUP test2fg;
GO
ALTER DATABASE AdventureWorks2012
ADD FILE
(
NAME = test3dat3,
FILENAME = ‘C:\DATA\t3dat3.ndf’,
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)
TO FILEGROUP test3fg;
GO
ALTER DATABASE AdventureWorks2012
ADD FILE
(
NAME = test4dat4,
FILENAME = ‘C:\DATA\t4dat4.ndf’,
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)
TO FILEGROUP test4fg;
GO
9. Now we are going to create the Partition Function
— Creates a partition function called myRangePF1 that will partition a table into four partitions
CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000) ;
GO
10. And create the Partition Scheme
— Creates a partition scheme called myRangePS1 that applies myRangePF1 to the four filegroups created above
CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (test1fg, test2fg, test3fg, test4fg) ;
GO
11. And create the table itself
— Creates a partitioned table called PartitionTable that uses myRangePS1 to partition col1
CREATE TABLE PartitionTable (col1 int PRIMARY KEY, col2 char(10))
ON myRangePS1 (col1) ;
GO
12. Then we’ll add some data to this new table
–Insert some data into the new table
insert AdventureWorks2012.dbo.PartitionTable
select BusnessEnntityId, LastName
from AdventureWorks2012.person.person;
13. Look at the windows explorer view of the new database files. How much space is each taking up? Why?
14. Now let’s use some of the built-in views to examine the partitioned table. First a query that will some rows if the table is partitioned, none if it is not.
SELECT *
FROM sys.tables AS t
JOIN sys.indexes AS i
ON t.[object_id] = i.[object_id]
AND i.[type] IN (0,1)
JOIN sys.partition_schemesps
ON i.data_space_id = ps.data_space_id
WHERE t.name = ‘PartitionTable’;
GO
–Make the filegroup readonly
15. And see if we can pick up the boundary values for the partitioning?
SELECT t.name AS TableName, i.name AS IndexName, p.partition_number, p.partition_id, i.data_space_id, f.function_id, f.type_desc, r.boundary_id, r.value AS BoundaryValue
FROM sys.tables AS t
JOIN sys.indexes AS i
ON t.object_id = i.object_id
JOIN sys.partitions AS p
ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.partition_schemes AS s
ON i.data_space_id = s.data_space_id
JOIN sys.partition_functions AS f
ON s.function_id = f.function_id
LEFT JOIN sys.partition_range_values AS r
ON f.function_id = r.function_id and r.boundary_id = p.partition_number
WHERE t.name = ‘PartitionTable’ AND i.type <= 1 ORDER BY p.partition_number; 16. To help with the understanding of how automation is done, this part of the assignment will create a small job that will do weekly maintenance on a database. Do all work on the Adventure Works database. I want you to create a multiple step job to do some basic maintenance on Adventure Works. When doing this assignment, you need to show the final script either through the GUI screen shots or attaching the t_SQL script. Here are the basic requirements: 1. Create a table BACKUPTABLE to be used by the weekly job 2. Schedule to job to be run weekly on Sunday at 1am 3. Have the report written to C:\databasejobs\adventureworks 4. If any step fails abort the job 5. Send an alert to Joe@myhouse.com for failure of any step and completion of the full job 6. Check the database integrity 7. Reorganize at least 2 indexes (whether they need it or not) (you choose) 8. Rebuild at least 2 indexes (whether they need it or not) (you choose) 9. Do a full backup of the database 10. Copy the contents of the PERSON.PERSON table into the new table Questions: 1. Why would you want to check the integrity of the database before doing a backup? 2. Why would you reorganize/rebuild indexes before you do a backup? 3. How did you decide what the new table would look like? Requirements: Database Systems COMP1225 Portfolio 1 – SQL Server Review Overview Now we are going to explore a few parts of the SQL file system. This will require some reading and research on your part, so allow yourself sufficient time. Be very careful read the messages on the screen and choose the right options. Document this fully in your lab book. Screen captures are helpful to explain, especially if you highlight the key point in the screen shot and provide a description that indicates what is important in the particular screen shot. This is also useful for rebuilds and trouble shooting. PLEASE NOTE: While I provide detailed SQL coding, it may not necessarily reflect your specific installation. You MUST read the provided code completely, understand it and modify it to reflect your environment. This of course must be documented in your lab book. Steps We are going to create a new file group --Need to add a filegroup first ALTER DATABASE AdventureWorks2012 ADD FILEGROUP ReadOnlyFG1; GO -- Add a file to the Filegroup ALTER DATABASE AdventureWorks2012 ADD FILE ( NAME = AdventureWorks2012_ReadOnlyData, FILENAME = ‘C:\SQLdata\AdventureWorks2012_ReadOnlyData.ndf’, SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) TO FILEGROUP ReadOnlyFG1; Go Look at the windows explorer view of the new database file. How much space is it taking up? Why? Where is the new log file? And now add a table to the new filegroup. Then we’ll add some data to this new table --Create a table on the filegroup CREATE TABLE AdventureWorks2012.dbo.MyReadOnlyTable ( FirstName varchar(50), LastName varchar(50), EMailAddress char(1000) ) ON ReadOnlyFG1; --Insert some data into the new Filegroup insert AdventureWorks2012.dbo.MyReadOnlyTable select LastName, FirstName, ‘xxx’ from AdventureWorks2012.person. person; Look at the windows explorer view of the new database file again. How much space is it taking up? Why? Where did the data go? Now let’s make this filegroup read only. --Make the filegroup readonly ALTER DATABASE [AdventureWorks2012] MODIFY FILEGROUP [ReadOnlyFG1] READONLY; Look at the windows explorer view of the new database file again What changes happened to the files? Read about Partitioning tables online in MS SQL Server documentation (). In your own words what is the use of partitioning Now let’s explore it. We are going to create a few new file groups USE AdventureWorks2012; GO -- Adds four new filegroups to the AdventureWorks2012 database ALTER DATABASE AdventureWorks2012 ADD FILEGROUP test1fg; GO ALTER DATABASE AdventureWorks2012 ADD FILEGROUP test2fg; GO ALTER DATABASE AdventureWorks2012 ADD FILEGROUP test3fg; GO ALTER DATABASE AdventureWorks2012 ADD FILEGROUP test4fg; -- Adds one file for each filegroup. ALTER DATABASE AdventureWorks2012 ADD FILE ( NAME = test1dat1, FILENAME = 'C:\DATA\t1dat1.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ) TO FILEGROUP test1fg; ALTER DATABASE AdventureWorks2012 ADD FILE ( NAME = test2dat2, FILENAME = 'C:\DATA\t2dat2.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ) TO FILEGROUP test2fg; GO ALTER DATABASE AdventureWorks2012 ADD FILE ( NAME = test3dat3, FILENAME = 'C:\DATA\t3dat3.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ) TO FILEGROUP test3fg; GO ALTER DATABASE AdventureWorks2012 ADD FILE ( NAME = test4dat4, FILENAME = 'C:\DATA\t4dat4.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ) TO FILEGROUP test4fg; GO Now we are going to create the Partition Function -- Creates a partition function called myRangePF1 that will partition a table into four partitions CREATE PARTITION FUNCTION myRangePF1 (int) AS RANGE LEFT FOR VALUES (1, 100, 1000) ; GO And create the Partition Scheme -- Creates a partition scheme called myRangePS1 that applies myRangePF1 to the four filegroups created above CREATE PARTITION SCHEME myRangePS1 AS PARTITION myRangePF1 TO (test1fg, test2fg, test3fg, test4fg) ; GO And create the table itself -- Creates a partitioned table called PartitionTable that uses myRangePS1 to partition col1 CREATE TABLE PartitionTable (col1 int PRIMARY KEY, col2 char(10)) ON myRangePS1 (col1) ; GO Then we’ll add some data to this new table --Insert some data into the new table insert AdventureWorks2012.dbo.PartitionTable select BusnessEnntityId, LastName from AdventureWorks2012.person.person; Look at the windows explorer view of the new database files. How much space is each taking up? Why? Now let’s use some of the built-in views to examine the partitioned table. First a query that will some rows if the table is partitioned, none if it is not. SELECT * FROM sys.tables AS t JOIN sys.indexes AS i ON t.[object_id] = i.[object_id] AND i.[type] IN (0,1) JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id WHERE t.name = 'PartitionTable'; GO --Make the filegroup readonly And see if we can pick up the boundary values for the partitioning? SELECT t.name AS TableName, i.name AS IndexName, p.partition_number, p.partition_id, i.data_space_id, f.function_id, f.type_desc, r.boundary_id, r.value AS BoundaryValue FROM sys.tables AS t JOIN sys.indexes AS i ON t.object_id = i.object_id JOIN sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id JOIN sys.partition_schemes AS s ON i.data_space_id = s.data_space_id JOIN sys.partition_functions AS f ON s.function_id = f.function_id LEFT JOIN sys.partition_range_values AS r ON f.function_id = r.function_id and r.boundary_id = p.partition_number WHERE t.name = 'PartitionTable' AND i.type <= 1 ORDER BY p.partition_number; To help with the understanding of how automation is done, this part of the assignment will create a small job that will do weekly maintenance on a database. Do all work on the Adventure Works database. I want you to create a multiple step job to do some basic maintenance on Adventure Works. When doing this assignment, you need to show the final script either through the GUI screen shots or attaching the t_SQL script. Here are the basic requirements: Create a table BACKUPTABLE to be used by the weekly job Schedule to job to be run weekly on Sunday at 1am Have the report written to C:\databasejobs\adventureworks If any step fails abort the job Send an alert to for failure of any step and completion of the full job Check the database integrity Reorganize at least 2 indexes (whether they need it or not) (you choose) Rebuild at least 2 indexes (whether they need it or not) (you choose) Do a full backup of the database Copy the contents of the PERSON.PERSON table into the new table Questions: Why would you want to check the integrity of the database before doing a backup? Why would you reorganize/rebuild indexes before you do a backup? How did you decide what the new table would look like?