Walkthrough Guide: Video Store DB Production Project

When I decided to bring the concept of a Video Store Database to life, the journey was filled with new learnings, challenges, and successes. Here’s my personal walkthrough:

1. Laying the Infrastructure:

a. VM Setup:

I began by setting up a Windows Server 2022 VM in VMWare named VM-DC1, with the intent of promoting it to a domain controller. With the infrastructure need in mind, I went on to create VM-SQL1. My goal was to install SQL Server 2022, alongside SSMS and SSRS on this VM, and, of course, to add a database instance.

2. Initial SQL Server Configuration:

Upon installing SQL Server 2022, I methodically set up the necessary accounts, instances, and dependencies. It wasn't long before I had added the ISDev domain and finalized my configurations. I took a moment to verify everything, ensuring that the service was recognized in the SQL Server Configuration Manager. I spotted two instances: the default SQLEXPRESS and a custom one named ISWOU.

3. Enhancing Network & Connection Settings:

To get the SQL Server ready for external connections, I made sure TCP/IP was enabled under the SQL Server Network Configuration. As a best practice, I also created an inbound firewall rule to allow port 1433, ensuring my db instance remained accessible.

4. Database Initialization & Injection:

Next, I was eager to import the VideoStore database. I opened SSMS and right-clicked on the MovieStoreDB to select New Query. With the SQL code in hand, I proceeded to inject it into VideoStoreDB. The process was swift and soon enough, my tables, columns, and data were all set.

5. Venturing into SSRS Integration:

With my database now ready, it was time to dip my toes into SSRS. I connected to the SQL server VM-SQL1 using the SSRS Configuration Manager. For the service account, I toggled between using sqldev and ISDev.

After setting up the database instance and testing the connection, I meticulously set up the Web Service URL and the Web Portal URL. I felt a sense of achievement seeing the process complete successfully. Before long, I had both URLs ready, making sure I could connect without any hitches.

6. The Dive into Visual Studio (VS):

Next, I set up a Windows 10 Education VM with Visual Studio Enterprise 2022 installed. I didn't stop there; I also made sure to install SQL Server Data Tools in Visual Studio on the W10 host. This was instrumental in connecting to the SSRS database reports and configuring their properties.

7. Reporting in VS:

Starting a new Report Server Project in VS was exciting. I began by setting up the data source, named it ISProjectDB, and connected it to the SQL Server. I authenticated using the sa account, selected VideoStoreDB, and tested the connection. With my data source ready, I moved on to creating a dataset and embedding it in my report. I conducted a simple test to ensure it all worked perfectly.

8. Bringing the Report to Life:

I started with a straightforward query, selecting movies. I found the thrill of setting up the report parameters and tables captivating. I ensured I could search for movies using the report. It was a proud moment when I selected "Into The Wild" from the movie list and viewed the report.

9. Deployment:

With everything ready, it was deployment time. I saved all my files and moved to the SSRS server on the SQL virtual machine to get the URL. After setting the deployment paths, I deployed my report, named MovieList.