Skip to main content

​
Prerequisites

Before setting up the TechCore database, ensure you have:
  • SQL Server 2016 or later (Express, Standard, or Enterprise)
  • SQL Server Management Studio (SSMS) or Azure Data Studio
  • Appropriate permissions to create databases and tables
  • .NET 8.0 SDK (for running the application)

​
Installation Methods

You can set up the TechCore database using either of these methods:

SQL Script

Execute the provided SQL script for manual setup

Entity Framework Migrations

Use EF Core migrations (if available in your deployment)

​
Method 1: SQL Script Installation

This is the recommended method for initial setup and production deployments.
1

Locate the SQL Script

The database creation script is located at:
Script SQL SERVER/TechCoreQuery.sql
This script contains all necessary DDL statements to create the complete database schema.
2

Open SQL Server Management Studio

Launch SSMS and connect to your SQL Server instance:
  • Server name: . (local instance) or your server address
  • Authentication: Windows Authentication or SQL Server Authentication
  • Click Connect
3

Execute the Database Creation Script

  1. Click File → Open → File
  2. Navigate to TechCoreQuery.sql and open it
  3. Review the script contents
  4. Click Execute (F5) to run the entire script
  5. Check the Messages tab for any errors
4

Verify Database Creation

Confirm the database and all objects were created successfully:
USE TechCore
GO

-- List all tables
SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME

-- List all views
SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.VIEWS
ORDER BY TABLE_NAME

-- List all triggers
SELECT name, parent_class_desc
FROM sys.triggers
WHERE parent_class = 1
You should see:
  • 12 tables (abonosVentas, categoria, clientes, compras, comprasDetalle, planPagos, productos, proveedores, rol, users, ventas, ventasDetalle)
  • 3 views (vw_CuotasPorVencer, vw_CuotasVencidas, vw_EstadoCuenta)
  • 2 triggers (TR_ActualizarSaldo, TR_DisminuirStock)
5

Verify Indexes

Check that all performance indexes were created:
SELECT 
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType
FROM sys.indexes i
WHERE i.object_id IN (
    SELECT object_id 
    FROM sys.tables
)
AND i.name IS NOT NULL
ORDER BY TableName, IndexName
You should see 30+ indexes across all tables.

​
Method 2: Entity Framework Migrations

If your deployment includes EF Core migrations: 
# Navigate to the project directory
cd TechCore

# Apply migrations to create the database
dotnet ef database update
The SQL script method is preferred as it includes all indexes, triggers, and views that may not be fully represented in EF migrations.

​
Configuration

​
Connection String Setup

After creating the database, configure the connection string in your application.
1

Locate appsettings.json

Open the configuration file:
TechCore/appsettings.json
2

Configure Connection String

Update the DefaultConnection string based on your environment:
{
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=.\\;Database=TechCore;Trusted_Connection=True;TrustServerCertificate=True;"
  }
}
3

Test Connection

Run the application to verify the database connection:
cd TechCore
dotnet run
Check the console output for any connection errors.

​
Initial Data Population

After database creation, you’ll need to populate initial data:

​
Create Default Roles

USE TechCore
GO

INSERT INTO rol (nombreRol, habilitado) VALUES
('Administrador', 1),
('Vendedor', 1),
('Almacenero', 1),
('Contador', 1)
GO

​
Create First User

Always use hashed passwords in production. The example below uses plain text for demonstration only.
INSERT INTO users (code, nombre, username, pwd, phone, idrol, email) VALUES
('USR-001', 'Administrator', 'admin', 'HASHED_PASSWORD_HERE', '555-0100', 1, '[email protected]')
GO

​
Create Sample Categories

INSERT INTO categoria (codigo, nombre, descripcion, estado) VALUES
('CAT-001', 'Electrónica', 'Dispositivos y componentes electrónicos', 1),
('CAT-002', 'Herramientas', 'Herramientas manuales y eléctricas', 1),
('CAT-003', 'Oficina', 'Suministros de oficina', 1)
GO

​
Database Maintenance

​
Backup Recommendations

1

Configure Automatic Backups

Set up SQL Server backup jobs for:
  • Full backup: Daily at 2:00 AM
  • Differential backup: Every 6 hours
  • Transaction log backup: Every 15 minutes (if using Full recovery model)
2

Manual Backup

To create a manual backup:
BACKUP DATABASE TechCore
TO DISK = 'C:\\Backups\\TechCore_Full.bak'
WITH FORMAT, INIT, 
NAME = 'TechCore Full Backup',
COMPRESSION
GO

​
Index Maintenance

Rebuild indexes monthly to maintain performance: 
USE TechCore
GO

-- Rebuild all indexes
EXEC sp_MSforeachtable @command1="DBCC DBREINDEX ('?')"
GO

-- Update statistics
EXEC sp_updatestats
GO

​
Monitor Database Size

SELECT 
    DB_NAME() AS DatabaseName,
    SUM(size) * 8 / 1024 AS SizeMB
FROM sys.database_files
GO

​
Troubleshooting

If you need to recreate the database:
-- Disconnect all users first
ALTER DATABASE TechCore SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

DROP DATABASE TechCore
GO
Then re-run the creation script.
Verify your user has access:
USE TechCore
GO

CREATE USER [YourWindowsUser] FOR LOGIN [DOMAIN\\YourWindowsUser]
GO

ALTER ROLE db_owner ADD MEMBER [YourWindowsUser]
GO
This occurs when using IIS. Grant the application pool identity access:
CREATE LOGIN [IIS APPPOOL\\YourAppPoolName] FROM WINDOWS
GO

USE TechCore
GO

CREATE USER [IIS APPPOOL\\YourAppPoolName] FOR LOGIN [IIS APPPOOL\\YourAppPoolName]
GO

ALTER ROLE db_datareader ADD MEMBER [IIS APPPOOL\\YourAppPoolName]
ALTER ROLE db_datawriter ADD MEMBER [IIS APPPOOL\\YourAppPoolName]
GO
Increase the timeout in your connection string:
Data Source=.;Database=TechCore;Trusted_Connection=True;Connect Timeout=30;TrustServerCertificate=True;

​
Security Hardening

Apply these security measures before deploying to production.

​
Create Application User with Limited Permissions

-- Create login
CREATE LOGIN techcore_app WITH PASSWORD = 'StrongPassword123!'
GO

USE TechCore
GO

-- Create user
CREATE USER techcore_app FOR LOGIN techcore_app
GO

-- Grant only necessary permissions
ALTER ROLE db_datareader ADD MEMBER techcore_app
ALTER ROLE db_datawriter ADD MEMBER techcore_app
GO

-- Grant EXECUTE on stored procedures (if any)
GRANT EXECUTE TO techcore_app
GO

​
Enable Transparent Data Encryption (Optional)

For sensitive data protection: 
-- Create master key
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourMasterKeyPassword'
GO

-- Create certificate
CREATE CERTIFICATE TechCoreCert WITH SUBJECT = 'TechCore TDE Certificate'
GO

-- Create database encryption key
USE TechCore
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TechCoreCert
GO

-- Enable encryption
ALTER DATABASE TechCore SET ENCRYPTION ON
GO

​
Next Steps

Entity Reference

Learn about all database entities and their relationships

Database Overview

Understand the database architecture and design decisions

Build docs developers (and LLMs) love

Get started for freeTalk to us